SQL Full Backup & Transaction Log backup

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


To better understand Transaction Log Truncation:


Troubleshot a Full Transaction Log:

SELECT name ,
       recovery_model_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:

SELECT st.session_id ,
         st.is_user_transaction ,
         dt.database_transaction_begin_time ,
         dt.database_transaction_log_record_count ,
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:



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