Showing posts with label Shrinking SQL Server log files. Show all posts
Showing posts with label Shrinking SQL Server log files. Show all posts

Sunday, 4 December 2011

SQL Server-Shrink log file

SQL Server-Shrink log file 


In SQL Server 2005, a shrink operation (DBCC SHRINKFILE) tries to shrink the specified transaction log file to the requested size immediately. For Shrinking SQL Server log files manually under the full recovery model, first back up the transaction log file. Then, use the DBCC SHRINKFILE statement to shrink the transaction log file.

When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size. This log backup operation is in addition to the three steps that you perform to shrink the transaction log file in SQL Server 2000. To shrink a transaction log file that has little free space in SQL Server 2005, follow these steps.




1.) Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.


BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'


2.) Shrink the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.


DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS


3.) If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive.


4.) Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be similar to the target size.


In summary, the log manager's algorithm for picking up the next virtual log file changed in SQL Server 2005. Therefore, shrinking the transaction log file in SQL Server 2005 may differ from shrinking the transaction log file in SQL Server 2000:
a) If a log file has lots of free space, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000.
b) If a log file has no free space, shrinking the transaction log file in SQL Server 2005 is the same as shrinking the transaction log file in SQL Server 2000.
c) If a log file has little free space, you may have to perform an additional log backup operation in SQL Server 2005 than you have to perform in SQL Server 2000.

Hence we have seen Shrinking SQL Log files can be done to reclaim the space.