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
