Today I am going to post why you should NOT shrink log files periodically.Let us create a Test database.Let us name it as "Test"

Create Database Test
Go


Now get to get the logspace used by our newly created database you can use the following DBCC command.

DBCC SQLPERF(logspace)

In our case the logspace size for the newly created database is 0.484375MB.




Now let us create a single table say T with a single column id and insert about a million records in it and then run the DBCC SQLPERF(logspace) to get the log size.

As you can see the log size has now increased by 300 percent.



The following query will give the logical name of the logfile for the database we just created.

Use Test
Go

select name from sys.sysfiles

In our case it is Test_log.

Now let us shrink the log file.We will use the DBCC SHRINKFILE command.

DBCC SHRINKFILE(Test_log)

The o/p of the above command shrinks the log file to the approximately the original size as when the database was created.



Now let us try to delete the records from the the only table in the Database.This will cause lot of log records written to the log file.

Delete from T where id>0

again let us check the log file size.

Wow log file has now bloated to almost 460 percent of the original size.


So the conclusion from all the above would be that log file will increase no matter how many times you shrink it.

So always have a good backup strategy to keep the log file size in check.Log shrinking should only be done in very very emergency situations only.