Many times the administrators ask about the transaction log size limit and how to shrink it. Even the log records are not managed properly and at last fill up the disk causing no longer changes to the database. The size of transaction log file will increase due to various reasons like long running transactions, lack of log backups and incorrect recovery model configuration. Thus, it is necessary to reduce the size of transaction log in SQL server. Now, in this blog, we are going to discuss a step-by-step procedure to shrink transaction log in SQL Server in an absolute way.
Shrink Transaction Log in SQL Server – Manual Tricks
There are following methods by which you can easily reduce the size of transaction log file. Please have a look:
- Using SQL Management Studio
a)In the Object Explorer, connect to instance of Microsoft SQL Server Database Engine and expand that instance
b) Expand all Databases and right-click on the database that you need to shrink
c) Choose Tasks, Shrink, and then Files. Then, choose the file type and file name.
d) Alternatively, check the Release unused space check box. If you select this option that causes an unused space in file to be released to operating system and shrink files to the last allocated extent. It will reduces the size of a file without moving any important data.
e) As an alternative, choose this check box Reorganize files before releasing the unused space. If this option is selected, reduce file to value must be identified. Now, the default option is cleared. Select this option causes the unused space in file to be released to operating system and attempt to relocate rows to some unallocated pages
f) Optionally, you need to enter the free space percentage in database file after shrinking the database. A permissible value must be between 0 and 99. This feature is only present when you enabled Reorganize files before releasing unused space
g) Alternatively, choose the Empty file by moving data to other files in similar filegroup check box. Select this option that converts all data from specified file to other files within the filegroup. The empty files can be deleted. This option is same as executing DBCC SHRINKFILE command using EMPTYFILE option.
h) Press OK button
- Using Transaction SQL (T-SQL)
If the database is available in SIMPLE recovery model, thus you can run the following statement to reduce the size of log files:
You must have to replace TestDB_log with logical name of a transaction log file you want shrunk and change 1 to number of MB you wish the transaction log file shrunk to.
If the SQL database is available in FULL recovery model you can set it to SIMPLE, perform DBCC SHRINKFILE cmdlet, and go back to FULL if you do not care about losing data in the transaction log.
You can search the logical name of transaction log file by using a given query:
Another option to reduce the log file size with FULL recovery model is to backup the transaction log for database using BACKUP LOG statement and then the main issue SHRINKFILE to shrink the transaction log in SQL Server:
Time to Conclude
Nowadays, most of the people are aware about how to shrink transaction log in SQL server e., Office 365 Backup. Sometimes, there is an unexpected delay, the log truncation will occur after the checkpoint or after a log backup automatically. Therefore, in this blog, we have explained the manual solution that will help to reduce the size of transaction logs. Make sure you follow each step very carefully.
SQL Canada (SQLCAN) 