SQL Server - How to Shrink Big Log File Size of a Database

I had previously written an article on how to shrink the logfile size of a database. But, while working intensely on databases that are pretty huge with respect to transactions, I felt it really challenging to shrink the log file. As I had previously mentioned, the log file size increases with an increase in transactions such as bulk data insertions, Index ReOrg, Update Statistics, or Full backups and Log backups running for quite a long time. There are two ways through which a big log file size can be shrunk. 

Method 1:

Say for example, if the log file size is utilized to 90 GB of 100 GB. Then, for sure there will be high difficulty in shrinking the log file. Here we can shrink the log file by giving the user databases to 10GB and shrink the log file.

Before to shrink the log file, check the log file size and log percentage with the below DBCC command: 

DBCC SQLPERF(logspace)



Now, let’s take a database for example – SQLArena_TEST
First, take the log backup of the respective database

BACKUP LOG <Database_Name> TO  DISK = N'<Drive_Path>.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Example:
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Shrink the file by giving the user database to 10 GB, as said the example here was taken to be as 90 GB utilized log. The user database needs to be reduced in 10 GB recursively to completely shink the log file. 

USE SQLArena_TEST
GO
DBCC SHRINKFILE ('SQLArena_TEST_Log', 80328) 




Note: Take the log backup twice or thrice with a different filename for the path consecutively to shink the database to 10 GB reduction successfully.

Method 2:

As per method 1, where the shrinking happens at 10 GB reduction for the user database, where we will be shrinking the log file size at a single shot. This method as far I have seen seems to be something pretty faster than any other shrinking method used in SQL Server. 

When the log file sizes are too high, we tend to make a mistake of giving just one path while taking the log backup and shrinking. Instead, let’s try to increase the drive path in the log backup script to finish the log backup and shrinking the data file of the user by giving zero and truncate. Take a look at the command mentioned:

– Given just one path for log backup and this fails in shrinking
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP 

– Give 7-8 drive paths to shrink at a single shot, Take this log backup twice
BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path1.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path2.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path3.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path4.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path5.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path6.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path7.trn',
DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path8.trn'
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once the log backup with multiple path files are created, shrink the user data by zero with TRUNCATEONLY command to completely shrink the big log file size. 

USE SQLArena_TEST
GO
DBCC SHRINKFILE (N'SQLArena_TEST_Log' , 0, TRUNCATEONLY)
GO

0 comments: