Sunday, November 27, 2011

Shrink a Transaction Log File of SQL server 2005 using Shrink File Command

Database Administrator can shrink transaction log file of SQL server database to remove the unused pages. SQL server 2005 database engine reuses unused space effectively & efficiently. You can shrink transaction log file with the help of two methods. One is command line method and another is manual method. When transaction log file grows unexpectedly then manual method is best effective.  

In this article, we will discuss about how to shrink the transaction log file of SQL server 2005 database manually under the full recovery model.

To Do this, you have to follow the below steps:

Backup the transaction log file of SQL server 2005.
Use DBCC SHRINKFILE command to shrink the transaction log file.

Syntax for DBCC SHRINKFILE
DBCC SHRINKFILE
(
    { file_name | file_id }
    { [ , EMPTYFILE ]
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

Note: The above described method to shrink transaction log file of SQL server 2005 may differ for the SQL server 2000

Question: Why shrinking a transaction log file in SQL server 2005 database is faster than shrinking the transaction log file in SQL server 2000?

Answer: Shrinking a transaction log file in SQL server 2005 is faster than SQL server 2000 because of log manager algorithm. To select next virtual log file in sql server 2005 is differ from SQL server server 2000. In SQL server 2000, log manager algorithm takes more time to pick next virtual log file in comparison with SQL server 2005.

Example: Suppose a transaction log file and 50 virtual files, only 2 virtual files are in used and rest 48 virtual files are unused. SQL server 2000 may store first virtual file at the start of the transaction log file and another virtual file in the middle of transaction log file. Log manager takes more time to find second virtual file but in SQL server 2005, a database administrator can perform shrink statement to transaction log file immediately two virtual files.

Summary: At last, we can say that:

If a log file has lots of free space then shrinking a transaction log file in SQL Server 2005 database is faster than shrinking a transaction log file in SQL Server 2000 database.

If a log file has no any free space then shrinking a transaction log file in SQL Server 2005 is the same as shrinking a transaction log file in SQL Server 2000.

No comments:

Post a Comment