SQLServerWiki

“The Only Thing That Is Constant Is Change”

Transaction Log backup fails intermittently.

Posted by database-wiki on July 10, 2012

The below query gives the backup history of all the databases.

SELECT database_name, user_name as ExecutedBy, physical_device_name,
backup_finish_date, backup_finish_date
FROM msdb..backupset bckset
INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id order by database_name, backup_finish_date

Query to find if full backup was taken.

SELECT * FROM msdb..backupset WHERE database_name = ‘LOG’ AND [type] = ‘D’

My testing:

Create a database truncatedlog

CREATE TABLE t1 (c1 INT, c2 VARCHAR (8000))

GO

SET NOCOUNT ON

GO

DECLARE @a INT;

SELECT @a = 1;

WHILE (@a < 10000)

BEGIN

INSERT INTO t1 VALUES (@a, REPLICATE (‘a’, 8000));

SELECT @a = @a + 1;

END;

GO

BACKUP DATABASE truncatedlog TO DISK = ‘c:\ truncatedlog.bck’ WITH INIT;

GO

DECLARE @DBName SYSNAME

SET @DBName = ‘truncatedlog’

DECLARE @LatestBackupDate DATETIME, @BackedUpDBCreationDate DATETIME

DECLARE @DBCreationDateNoMilliseconds DATETIME

— In msdb..backupset table, database_creation_date column is in datatime format

— but without milliseconds, so we remove milliseconds from database creation date.

SELECT @DBCreationDateNoMilliseconds =

DATEADD(millisecond, -DATEPART(millisecond, create_date), create_date)

FROM sys.databases

WHERE [name] = @DBName

IF @DBCreationDateNoMilliseconds IS NULL

BEGIN

PRINT ‘Database ‘ + @DBName + ‘ doesn’ + CHAR(39) + ‘t exist on the server’

RETURN

END

SELECT

@LatestBackupDate = backup_finish_date,

@BackedUpDBCreationDate = database_creation_date

FROM msdb..backupset

WHERE

backup_set_id = (

SELECT MAX(backup_set_id)

FROM msdb..backupset

WHERE database_name = @DBName AND

[type] = ‘D’ )

IF @LatestBackupDate IS NULL

BEGIN

PRINT ‘Full backup had never been taken for database ‘ + @DBName

RETURN

END

IF @BackedUpDBCreationDate = @DBCreationDateNoMilliseconds

PRINT ‘Full backup exists for database ‘ + @DBName + ‘; Transaction log backups can be taken’

ELSE

PRINT ‘Full backup exists for the current version of ‘ + @DBName + ‘ database  had never been taken. Existing backup refers to non-existing database with the same name’

Now

BACKUP LOG truncatedlog WITH TRUNCATE_ONLY

Run the above script once again.

Still it says fullbackup is available.

But LSN chain has broken.

Now how to check if tlog has been truncated for a database?

The answer lies in the dbcc dbtable output. If the value of m_backupLSN is 0:0:0 0x(0:0:0) then the transaction log has been truncated using backup log with tuncate_only or no_log.

Repro:

CREATE DATABASE test

GO

ALTER DATABASE test SET RECOVERY FULL

GO

CREATE TABLE testtab( numbers int )

GO

DECLARE @i int

SET @i=1

WHILE @i<=100

BEGIN

INSERT INTO testtab VALUES(@i)

SET @i=@i+1

END

GO

BACKUP DATABASE test TO DISK = ‘c:\Testdb.bak’

–m_backupLSN = 30:168:158 0x(1e:a8:9e)  FULLBK

–m_backupLSN = 30:252:1 0x(1e:fc:1)       TLOGBK

–m_backupLSN = 0:0:0 0x(0:0:0) NO_LOG TRUNCATE_ONLY

GO

DELETE FROM testtab WHERE numbers<=10

GO

BACKUP LOG test TO DISK = ‘c:\Testlog.trn’

GO

DELETE FROM testtab WHERE numbers<=20

BACKUP LOG test WITH NO_LOG

GO

BACKUP LOG test TO DISK = ‘c:\Testlog.trn’

GO

Since the Tlog has been truncated we will get the below output.

Error:

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

DBCC TRACEON(3604,-1)

DBCC DBTABLE(9)

Now how to find who has truncated the transaction log?

Can be found out by creating a trigger for backup log command.

Other Queries:

Databases with no backups:

SELECT DB_NAME(rs.database_id)

FROM sys.database_recovery_status rs

WHERE rs.last_log_backup_lsn IS NULL;

Query to find which Tlog backup need to be restored next.

select database_name, type, first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn

from msdb..backupset where database_name = ‘log’

database_name                                                                                                                    type first_lsn                               last_lsn                                checkpoint_lsn                          database_backup_lsn

——————————————————————————————————————————– —- ————————————— ————————————— ————————————— —————————————

log                                                                                                                              D    26000000006900037                       26000000008700001                       26000000006900037                       0

log                                                                                                                              L    26000000006900037                       26000000021300001                       26000000006900037                       26000000006900037

log                                                                                                                              L    26000000021300001                       26000000021600001                       26000000006900037                       26000000006900037

(3 row(s) affected)

TITLE: Microsoft SQL Server Management Studio

——————————

Restore failed for Server ‘BALAJI2-3136\robert’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

——————————

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 26000000021300001, which is too recent to apply to the database. An earlier log backup that includes LSN 26000000008700001 can be restored. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476

——————————

BUTTONS:

OK

——————————

To know the LSN of the backups we can use

RESTORE HEADERONLY from disk=’c:\DB_TestLSN.bak’

RESTORE HEADERONLY from disk=’c:\DB_TestLSN_LOG1′

RESTORE HEADERONLY from disk=’c:\DB_TestLSN_LOG2′

Query to find reuse of transaction log space is currently waiting on one of the following:

SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = ‘log’;

GO

Values:

NOTHING

CHECKPOINT

LOG_BACKUP

ACTIVE_BACKUP_OR_RESTORE

ACTIVE_TRANSACTION

DATABASE_MIRRORING

REPLICATION

DATABASE_SNAPSHOT_CREATION

LOG_SCAN

OTHER_TRANSIENT

On more similar scenario.

1. Create database MyDB.

2. Create full backup of ‘MyDB’.

3. Drop database ‘MyDB’ (without deleting backup history).

4. Create new database with the name ‘MyDB’ or restore from backup.

What do we have now? Query will return true while actually backup we have belongs to another non-existing database. Try to backup transaction log of the new DB – you’ll receive error.

http://sqlblog.com/blogs/michael_zilberstein/archive/2008/03/01/Have-your-database-ever-been-backed-up_3F00_.aspx

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

 
%d bloggers like this: