[windows2000] KB: How to stop the transaction log of a SQL Server database from growing unexpectedly
- From: "Jim Kenzig http://thin.net" <jimkenz@xxxxxxxxxxxxxx>
- To: windows2000@xxxxxxxxxxxxx
- Date: Wed, 29 Sep 2004 07:37:46 -0400
I just love when things I bring up on this list come out as KB articles days
aftwards....coincidence?
JK
http://support.microsoft.com/?kbid=873235
How to stop the transaction log of a SQL Server database from growing
unexpectedly
View products that this article applies to.
IN THIS TASK
INTRODUCTION
More Information
Reduce the size of the transaction logs
Truncate the inactive transactions in your transaction log
Shrink the transaction log file
Prevent the transaction log files from growing unexpectedly
REFERENCES
INTRODUCTION
This article describes the steps that you must follow when the transaction
logs expand to an unacceptable limit. The expansion of the transaction logs
can make your Microsoft SQL Server database unusable. This article also
provides the options that you can use to stop the transaction logs from
growing unexpectedly.
back to the top
MORE INFORMATION
In SQL Server 2000, each database contains at least one data file and one
transaction log file. SQL Server 2000 stores the data physically in the data
file. The transaction log file stores the details of all the modifications
that you perform on your SQL Server database and the details of the
transactions that performed each modification. Because the transactional
integrity is considered a fundamental and intrinsic characteristic of SQL
Server, logging the details of the transactions cannot be turned off in SQL
Server 2000.
The transaction log file is logically divided into smaller segments that are
referred to as virtual log files. In SQL Server 2000, you can configure the
transaction log file to expand as needed. The transaction log expansion can
be governed by the user or can be configured to use all the available disk
space. Any modifications that SQL Server makes to the size of the
transaction log file, such as truncating the transaction log files or
growing the transaction log files, are performed in units of virtual log
files.
If the transaction log file that corresponds to a SQL Server database is
filled and if you have set the option for the transaction log files to grow
automatically, the transaction log file grows in units of virtual log files.
Sometimes, the transaction log file may become very large and you may run
out of disk space. When a transaction log file grows until the log file uses
all the available disk space and cannot expand any more, you can no longer
perform any data modification operations on your database. Additionally, SQL
Server may mark your database as suspect because of the lack of space for
the transaction log expansion.
For additional information about the scenarios that may cause the
transaction log file to grow unexpectedly, click the following article
number to view the article in the Microsoft Knowledge Base:
317375 Transaction log grows unexpectedly or becomes full on SQL Server
http://support.microsoft.com/default.aspx?kbid=317375
back to the top
Reduce the size of the transaction logs
To recover from a situation where the transaction logs grow to an
unacceptable limit, you must reduce the size of the transaction logs. To do
this, you must truncate the inactive transactions in your transaction log
and shrink the transaction log file.
Note The transaction logs are very important to maintain the transactional
integrity of the database. Therefore, you must not delete the transaction
log files even after you make a backup of your database and the transaction
logs.
back to the top
Truncate the inactive transactions in your transaction log
When the transaction logs grow to an unacceptable limit, you must
immediately back up your transaction log file. While the backup of your
transaction log files is created, SQL Server automatically truncates the
inactive part of the transaction log. The inactive part of the transaction
log file contains the completed transactions, and therefore, the transaction
log file is no longer used by SQL Server during the recovery process. SQL
Server reuses this truncated, inactive space in the transaction log instead
of permitting the transaction log to continue to grow and to use more space.
For additional information about the issues that you must consider when you
make a backup of the transaction logs and the issues that you must consider
when you restore the transaction log backups, visit the following topics in
SQL Server Books Online:
Transaction log backups
Transaction log backup and restore
You can also delete the inactive transactions from a transaction log file by
using the Truncate method. For additional information about truncating the
transaction logs, see the "Truncating the transaction log" topic in SQL
Server Books Online.
Important After you manually truncate the transaction log files, you must
create a full database backup before you create a transaction log backup.
For additional information about the issues that may occur when you truncate
the transaction log files, click the following article number to view the
article in the Microsoft Knowledge Base:
62866 Reasons why SQL transaction log is not being truncated
http://support.microsoft.com/default.aspx?kbid=62866
Shrink the transaction log file
The backup operation or the Truncate method does not reduce the log file
size. 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
inside the log file.
Note The DBCC SHRINKFILE Transact-SQL statement cannot truncate the log and
shrink the used space inside the log file on its own.
For more information about shrinking the transaction log files, see the
following topics in SQL Server Books Online:
Shrinking the transaction log
DBCC SHRINKFILE
For additional information about how to shrink the transaction log files in
SQL Server 2000, click the following article number to view the article in
the Microsoft Knowledge Base:
272318 Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?kbid=272318
For additional information about the problems that may occur when you shrink
the transaction log files, click the following article numbers to view the
articles in the Microsoft Knowledge Base:
814574 PRB: Error message: "Cannot shrink log file ..." occurs when you
shrink the transaction log file
http://support.microsoft.com/default.aspx?kbid=814574
324432 PRB: DBCC SHRINKFILE and SHRINKDATABASE commands may not work because
of sparsely populated text, ntext, or image columns
http://support.microsoft.com/default.aspx?kbid=324432
back to the top
Prevent the transaction log files from growing unexpectedly
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.
For additional information about the issues to consider when you configure
the autogrow option, click the following article number to view the article
in the Microsoft Knowledge Base:
315512 Considerations for autogrow and autoshrink configuration
http://support.microsoft.com/default.aspx?kbid=315512
Change the recovery model. If a disaster or data corruption occurs, you must
recover your database so that the data consistency and the transactional
integrity of the database are maintained. Based on how critical the data in
your database is, you can use one of the following recovery models to
determine how your data is backed up and what your exposure to the data loss
is:
Simple recovery model
Full recovery model
Bulk-logged recovery model
By using the simple recovery model, you can recover your database to the
most recent backup of your database. By using the full recovery model or the
bulk-logged recovery model, you can recover your database to the point when
the failure occurred by restoring your database with the transaction log
file backups.
By default, in SQL Server 2000, the recovery model for a SQL Server database
is set to the Full recovery model. With the full recovery model, regular
backups of the transaction log are used to prevent the transaction log file
size from growing out of proportion to the database size. However, if the
regular backups of the transaction log are not performed, the transaction
log file grows to fill the disk, and you may not be able to perform any data
modification operations on the SQL Server database.
You can change the recovery model from full to simple if you do not want to
use the transaction log files during a disaster recovery operation.
Back up the transaction log files regularly to delete the inactive
transactions in your transaction log.
Design the transactions to be small.
Make sure that no uncommitted transactions continue to run for an indefinite
time.
Schedule the Update Statistics option to occur daily.
To defragment the indexes to benefit the workload performance in your
production environment, use the DBCC INDEXDEFRAG Transact-SQL statement
instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC
DBREINDEX statement, the transaction log may expand significantly when your
SQL Server database is in Full recovery mode. Additionally, the DBCC
INDEXDEGRAG statement does not hold the locks for a long time, unlike the
DBCC DBREINDEX statement.
For additional information about defragmenting the indexes in SQL Server
2000, see the following Microsoft Web site:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
If you must run the DBCC DBREINDEX statement as a job that is a part of the
database maintenance plan, you must break up the job into multiple jobs.
Additionally, you must take the frequent backups for the transaction logs
between the execution of the jobs.
back to the top
REFERENCES
For additional information about troubleshooting the additional disk space
requirement during the recovery process, see the "Insufficient disk space"
topic in SQL Server Books Online. For additional information about the
transaction log architecture, see the following topics in SQL Server Books
Online:
Transaction log architecture
Transaction log logical architecture
Transaction log physical architecture
For more information about the recovery models in SQL Server 2000, see the
following topics in SQL Server Books Online:
Selecting a recovery model
Simple recovery
Full recovery
Bulk-logged recovery
Switching recovery models
back to the top
The information in this article applies to:
Microsoft SQL Server 2000 (all editions)
Last Reviewed: 9/16/2004 (1.0)
Keywords: kbDiskMemory kbDisasterRec kbhowto kbConfig kbinfo KB873235
kbAudDeveloper
********************************************************
This Weeks Sponsor StressedPuppy.com Games
Feeling stressed out? Check out our games to
relieve your stress.
http://www.StressedPuppy.com
********************************************************
To Unsubscribe, set digest or vacation
mode or view archives use the below link.
http://thethin.net/win2000list.cfm
Other related posts:
- » [windows2000] KB: How to stop the transaction log of a SQL Server database from growing unexpectedly
