Sidebar

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

0 votes
1.2K views
asked Aug 28, 2014 by rich-c-2789 (16,180 points)
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?

1 Answer

0 votes
 
Best answer

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

 

 
 
answered Aug 28, 2014 by rich-c-2789 (16,180 points)
selected Dec 25, 2014 by sam-s-1510
...