Archive

Archive for the ‘MS SQL Server’ Category

SQL Server is running out of disk space due to transaction logs size

April 20, 2014 Leave a comment

In many cases its has been noticed that SQL transaction log may become very large and run out of space or become full. In this post describe how to fix when SQL transaction logs consumed disk space

Scenario

 

SQL server running out of disk space due to transaction logs

SQL transaction logs is not truncating after VERITAS backup

How Truncating the Transaction Log.

Note : Verify database recovery model is full by select the database -> right click ,select properties ->select Options -> view the Recovery Model .
If the database recovery model is full or Bulk logged , then we can reduce the log file size by shrinking the log file as below

Action Plan by using SQL Server Management Studio.

Step 1 Check the log space use
Run DBCC command to find out the database whose log space used percentage is the lowest and log size if the largest.
DBCC SQLPERF(LOGSPACE);
4

Step 2 Do a full backup for the required databases log.

Select the database ->right click, select task ->click backup ->select “Transaction Log ” as Backup type ->click OK.
1

Step 3 Run “CHECKPOINT” command as follows.

2

Step 4 Shrink the log file.

Select database, right click ->tasks ->shrink ->files ->select “log” as file type ->select “Reorganize Pages before releasing unused space”->Shrink file to a target size ->click OK.
The target size should be set to a value that approximately equal Currently allocated space minus Available free space
3
If the target size is smaller than the minimum size that the log file can shrink to ,it will get the minimum size. For example ,we want to reduce the log file size to 1MB, but if the log file requires 20 GB at least, then the log file size will be 20GB after we shrink it.

Reference Read more…

Advertisements
%d bloggers like this: