Understand how shrinking a LOG file works :

Understand how shrinking a LOG file works :

Many of them faced while shrinking a log file in the database, after applying the DBCC
Shrinkfile option also the files size not reduced. What was the structure behind, how it works.

For an example you have a database consists of data and log files, which log file size is growing abnormally exceeding hard drive size. So you need to reduce the file to accommodate within the available space. So maximum people would perform following options to decrease the file size.

1) Either truncating log file directly then shrink
Backup log 'DATABASE' with truncate_only
DBCC shrinkdatabase ('database',10) (or)
DBCC shrinkfile ('logfile')

2) or Applying the shrinkdatabase option directly on the database.

3) or Keeping Full / Log backup jobs in the server with some time intervals and shrinking the database.

Whether these above options are sufficient to perform the maintenance of Log file in the server. No, these can be manageable upto some extent only, if you can follow the above activities with slight changes you can expect 100% results from it.


What are the drawbacks in above processes :

1) If you truncate directly your log file then shrink the database, you can have reduced log file, but if you need to restore your database using the log backup.......... no backup available

2) Applying shrink option directly doesn't give you much impact

3) Keeping Log backup and perform shrink option will give you the good result but it should happend sequentially.


The Process behind log backup and shrink :
1) I have database consists of log file size 100Mb and Used space in the log file is 94 MB.
This can be find out using DBCC sqlperf(logspace).
2) I have performed DBCC shrinkfile ('log file') for size decrease ; but it increased instead of decrease. Why bcos the log file having the logs and for shrinking it written on the top of it, it increased. Use DBCC sqlperf(logspace) to check the details.
3) Now I have performed backup log file then performed the shrink option. It reduced to the initial size and my drive is free now.


Additional options to findout :

A) Run following query
DBCC loginfo
If the status in the below table is 2 then it is waiting for the backup
else it is 0 then it can be shrinked

result :
Field FileSize Startoffset FseqNo STatus Parity CreateLSN
2 2555904 8192 98682 2 64 0
2 2555904 2564096 98683 2 128 0
2 2555904 5120000 98685 2 64 0
2 2809856 7675904 98684 2 128 0
2 253952 10485760 98686 2 64 98685000000407400016
2 253952 10739712 98687 2 64 98685000000407400016
2 253952 10993664 98688 2 64 98685000000407400016



b) Run following query whether the backup is pending or not
select log_reuse_wait_desc from sys.databases where name = 'database'

if the result is 'LOG_BACKUP' then waiting for backup
if the result if 'NOTHING' then it can be shrinked


Note : In Sql Server 2008 there is no truncating log backup option, instead you can alter the recovery mode into simple then shrink. Again change the mode into normal.