How to shrink MS SQL Server transaction logs for the QIE database?

Question

I ran out of disk space.  QIE is configured to use MS Sql Server and the transaction log is huge.  How to shrink MS SQL Server transaction logs for the QIE database?

Answer 1

For MSSQL the backup and maintenance setting is referred to as the ‘recovery model’.  Your organization’s DBA may want to configure the proper recovery model based on the hardware configuration of the MSSQL server.  MSSQL truncates the transaction log every time you back it up under the full or bulk-logged recovery model.  If you’re using the simple recovery model (which provides no transaction log recovery), SQL Server truncates the log at every transaction checkpoint.

Setting the recovery model to simple on the QIE database may be sufficient.  The alternative is to schedule a recuring backup.  Doing either of these should prevent the database log files from consuming the disk space.

That said, to free the disk space you need to shrink the log files.  See the screen shot below:

 

To do it from a script:

USE qie

DBCC SHRINKFILE (N'qie_log' , 0, TRUNCATEONLY)

GO

For QIE the "Simple" recovery model is usually sufficient so lets add that to the script:

USE qie

ALTER DATABASE model SET RECOVERY SIMPLE;

DBCC SHRINKFILE (N'qie_log' , 0, TRUNCATEONLY)

GO

After running the above script we should no longer need to worry about the transaction logs using up all the disk space.

To just change the recovery model see this question:

https://www.qvera.com/kb/index.php/791/how-to-set-the-recovery-model-to-simple-in-ms-sql-server?show=791#q791

 

 

Also see these related questions

How do I shrink the h2 database file after removing thousands of errors?

How to set the Recovery Model to Simple in MS SQL Server?

How should I resolve an "Out of Disk Space" error?

How to check the current and max size of the databases on SQL Server

How to reclaim disk space after purging messages with a MariaDb/MySql database?