SQL Full Backup & Transaction Log backup

REMEMBER: Transaction Log is not automatically truncated when a Full Backup is performed.

http://blogs.technet.com/b/beatrice/archive/2008/07/24/full-backups-transaction-logs-backup.aspx

To better understand Transaction Log Truncation:

http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx

Troubleshot a Full Transaction Log:

SELECT name ,
       recovery_model_desc ,
       log_reuse_wait_desc
FROM   sys.databases
WHERE  name = 'MyDatabase'

If the value returned for log_reuse_wait_desc, from the previous sys.databases query, is LOG_BACKUP, then you are operating a database in the FULL recovery model (or less common, the BULK_LOGGED recovery model), without taking transaction log backups.
If the value returned for log_reuse_wait_desc is ACTIVE_TRANSACTION, then you are suffering long-running or uncommitted transactions.
Another common cause of the Active Transaction value for log_reuse_wait_desc is the presence of “orphaned” explicit transactions that somehow never got committed.

To identifying the active transaction:

DBCC OPENTRAN (MyDatabase)
SELECT st.session_id ,
         st.is_user_transaction ,
         dt.database_transaction_begin_time ,
         dt.database_transaction_log_record_count ,
         dt.database_transaction_log_bytes_used 
FROM     sys.dm_tran_session_transactions st
         JOIN sys.dm_tran_database_transactions dt
                   ON st.transaction_id = dt.transaction_id
                      AND dt.database_id = DB_ID('MyDatabase')
WHERE st.session_id = 56

Change the st.session_id value with the SPID value of DBCC OPENTRAN.

Recovery Model Overview:

http://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms189275.aspx
http://technet.microsoft.com/en-us/library/aa196649(v=sql.80).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s