SQL Server log file can’t be shrunk?

A lot of time I heard from someone that the storage space of their SQL Server log files can’t be release completely. Even you can view 99% of the log file is free. And no matter how many times you issued the command DBCC SHRINKFILE. The size is just leave there and never returns to the OS.

So what happened to your log file? Does this mean shrink file was failed due to some reason?

No. Actually there is nothing to do with the shrink file command. The key SQL Server doesn’t release the space is the internal mechanism of SQL Server log file. SQL Server log file was organized into chunks which called as virtual log files (VLF). The log file can only be shrunk to a VLF boundary. And the VLF size is determined when the log file was created or increased. The example screen capture was taken from a test database, which the log file was initialized to be 1GB. It seems SQL Server initialized the log file with 4 VLFs, which is slightly different with the declaration in SQL Server Internal (8). So when we try to shrink the file, it is always be 256MB and will never be less than this size.

Alright. Then what we can do for an existing database if we really really like to shrink the log file, although it is meaningless in most cases. The simplest method is close the database cleanly, then detach it and re-attach it back with out the log file. To close the database cleanly, you need disconnect all applications and user sessions, and run the backup database command. When you attach the data file back, you can implicitly delete the log file entry in the file list.

Be caution, please avoid to do this in production environment. Or you have already planned the time and process for potentially recovery the database.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: