Depending on how Microsoft SQL Server is configured (Recovery Model set to Full or Bulk logged), the transaction log files will grow in size as needed so as to keep the database running. Once SQL server allocates disk space on the hard disk drive to a database transaction log file, that space will not be "freed up" when a transaction log backup is run. What is truncated by SQL server in the course of a backup is the amount of space that is in use in the transaction log. While the size of the transaction log file on the hard disk is not changed, the amount of space in use in the transaction log has been reduced.
To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part of the log file.
To prevent the transaction log files from growing unexpectedly, consider using one of the
following methods:
- Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
- Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
To determine the amount of disk space in use by the log file, use the following query in the SQL
Query Analyzer:
use master
DBCC SQLPERF (LOGSPACE)
No comments:
Post a Comment