Every
SQL Server database has at least two files; a data file and a transaction log
file. The data file stores user and system data while the transaction log file
stores all transactions and database modifications made by those transactions.
As time passes, more and more database transactions occur and the transaction
log needs to be maintained. If your database is in the Simple recovery mode,
then the transaction log is truncated of inactive transaction after the
Checkpoint process occurs. The Checkpoint process writes all modified data
pages from memory to disk. When the Checkpoint is performed, the inactive
portion of the transaction log is marked as reusable.
Transaction
Log Backups
If
your database recovery model is set to Full or Bulk-Logged, then it is
absolutely VITAL that you make transaction log backups to go along with your
full backups. SQL Server 2005 databases are set to the Full recovery model by
default, so you may need to start creating log backups even if you haven't ran
into problems yet. The following query can be used to determine the recovery
model of the databases on your SQL Server instance.
SELECT
name, recovery_model_desc
FROM
sys.databases
Be
aware:
Full backups are the starting point for any type of recovery process, and are
critical to have in case you run into trouble. In fact, you cannot create
transactional log backups without first having created a full backup at some
point.
The
Full or Bulk-logged Recovery Mode
With
the Full or Bulk-Logged recovery mode, inactive transactions remain in the
transaction log file until after a Checkpoint is processed and a transaction log
backup is made. Note that a full backup does not remove inactive transactions
from the transaction log. The transaction log backup performs a truncation of
the inactive portion of the transaction log, allowing it to be reused for future
transactions. This truncation does not shrink the file, it only allows the
space in the file to be reused It is these transaction log backups that keep
your transaction log file from growing too large.
If
your database recovery model is set to FULL, and you're not creating transaction
log backups and never have, you may want to consider switching your recovery
mode to Simple. The Simple recovery mode should take care of most of your
transaction log growth problems because the log truncation occurs after the
Checkpoint process. You'll not be able to recover your database to a point in
time using Simple. To switch your recovery model to Simple mode, issue the
following statement in your database:
ALTER DATABASE
YourDatabaseName
SET RECOVERY
SIMPLE
How
to prevent / fix the problem:
1)
Convert the Recovery Model to Simple Recovery
If
you are truncating the transaction logs, this means you are breaking the T-Log
LSN (Log Sequence Numbers). This follows that if disaster comes, you would not
be able to restore your T-Logs and there would be no option for you to do point
in time recovery. If you are fine with this situation and there is nothing to
worry, I suggest that you change your recovery model to Simple Recovery Model.
This way, you will not have extra ordinary growth of your log file.
OR
2)
Start Taking Transaction Log Backup
If
your business does not support loss of data or requires having point in time
recovery, you cannot afford anything less than Full Recovery Model. In Full
Recovery Model, your transaction log will grow until you take a backup of it.
You need to take the T-Log Backup at a regular interval. This way, your log
would not grow beyond some limits. If you are taking an hourly T-Log backup,
your T-Log would grow until one hour but after this the T-Log backup would
truncate all the ‘committed’ transactions once you take it. Doing this would
lead the size of the T-Log not to go down much, but it would rather be marked as
empty for the next hour’s T-Log to populate.
With
this method, you can restore your database at Point of Time if a disaster ever
happens at your server.
Not
performing transaction log backups is probably the main cause for your
transaction log growing too large. However, there are other situations that
prevent inactive transactions from being removed even if you're creating regular
log backups. The following query can be used to get an idea of what might be
preventing your transaction log from being truncated:
SELECT
name, log_reuse_wait_desc
FROM
sys.databases
Possible
reasons can be:
Long-Running Active Transactions
A
long running transaction can prevent transaction log truncation. These types of
transactions can range from transactions being blocked from completing to open
transactions waiting for user input. In any case, the transaction ensures that
the log remain active from the start of the transaction. The longer the
transaction remains open, the larger the transaction log can grow. To see the
longest running transaction on your SQL Server instance, run the following
statement:
DBCC
OPENTRAN
If
there are open transactions, DBCC
OPENTRAN
will provide a session_id (SPID) of the connection that has the transaction
open. You can pass this session_id to sp_who2 to determine which user has the
connection open:
EXECUTE
sp_who2 spid
Alternatively,
you can run the following query to determine the user:
SELECT
* FROM sys.dm_exec_sessions
WHERE
session_id = spid --from DBCC OPENTRAN
You
can determine the SQL statement being executed inside the transactions a couple
of different ways. First, you can use the DBCC INPUTBUFFER() statement to return the
first part of the SQL statement
DBCC
INPUTBUFFER(spid) --from DBCC OPENTRAN
SELECT
r.session_id, r.blocking_session_id, s.program_name,
s.host_name, t.text FROM sys.dm_exec_requests r INNER JOIN
sys.dm_exec_sessions s ON r.session_id = s.session_id CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t WHERE s.is_user_process = 1 AND
r.session_id = SPID --FROM DBCC OPENTRAN
Backups
Log
truncation cannot occur during a backup or restore operation. If a database
backup is keeping your log from being truncated you might consider cancelling
the backup to relieve the immediate problem.
Transactional Replication
With
transactional replication, the inactive portion of the transaction log is not
truncated until transactions have been replicated to the distributor. This may
be due to the fact that the distributor is overloaded and having problems
accepting these transactions or maybe because the Log Reader agent should be ran
more often. IF DBCC OPENTRAN indicates that your oldest active transaction is a
replicated one and it has been open for a significant amount of time, this may
be your problem.
Database Mirroring
Database
mirroring is somewhat similar to transactional replication in that it requires
that the transactions remain in the log until the record has been written to
disk on the mirror server. If the mirror server instance falls behind the
principal server instance, the amount of active log space will grow. In this
case, you may need to stop database mirroring, take a log backup that truncates
the log, apply that log backup to the mirror database and restart
mirroring.
Disk Space
It
is possible that you're just running out of disk space and it is causing your
transaction log to error. If you cannot free enough disk space on the drive that
currently contains the log file then you may need to move the file to a drive
with enough space to handle the log. If your log file is not set to grow
automatically, you'll want to consider changing that or adding additional space
to the file. Another option is to create a new log file for the database on a
different disk that has enough space by using the ALTER DATABASE
YourDatabaseName ADD LOG FILE syntax.
Shrinking the File
Once
you have identified your problem and have been able to truncate your log file,
you may need to shrink the file back to a manageable size. You should avoid
shrinking your files on a consistent basis as it can lead to fragmentation
issues. However, if you've performed a log truncation and need your log file to
be smaller, you're going to need to shrink your log file. You can do it through
management studio by right clicking the database, selecting All Tasks, Shrink,
then choose Database or Files. If I am using the Management Studio interface, I
generally select Files and shrink only the log file.
This
can also be done using TSQL. The following query will find the name of my log
file. I'll need this to pass to the DBCC:
SHRINKFILE
command.
SELECT
name
FROM
sys.database_files
WHERE
type_desc = 'LOG'
Once
I have my log file name, I can use the DBCC command to shrink the file. In the
following command I try to shrink my log file down to 1GB.
DBCC
SHRINKFILE ('SalesHistory_Log', 1000)
Also,
make sure that your databases are NOT set to auto-shrink. Databases that are
shrank at continuous intervals can encounter real performance
problems.
TRUNCATE_ONLY and NOLOG
If
you're a DBA and have ran into one of the problems listed in this article
before, you might be asking yourself why I haven't mentioned just using
TRUNCATE_ONLY to truncate the log directly without creating the log backup. The
reason is that in almost all circumstances you should avoid doing it. Doing so
breaks the transaction log chain, which makes recovering to a point in time
impossible.
Moving
forward
You
should consider adding notifications to your system to let you know when your
database files are reaching a certain threshold. The more proactive you are in
terms of alerts for these types of events, the better chance you'll have to
correct the issue before it turns into a real problem…