I have a transaction log file of 306 MB for an old SQL Server 2000 database (unfortunately still needed to support a package from external supplier). Over the last couple of days the transaction log backup hast started failing with the following error
Msg 3241, Level 16, State 40, Line 29
The media family on
device '\\ourbackupservername\servers\SQL_Servers\oursqlservername\TranLog_Backups\ourdbname_20170410_HHMMSS.TRN' is
incorrectly formed.
SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 29
BACKUP LOG is terminating abnormally.
The SQL Server error log at the time of this failure shows about a dozen entries of :
spid53 BlkHeader from strip 0 at...........
The transaction log backup job has been running fine for months, always backing up the transaction log to the same server location on an hourly schedule. We are also backing up transaction logs from around another 45 databases from the same SQL Server 2000 instance (to the same \\ourbackupservername\servers\SQL_Servers\oursqlservername\TranLog_Backups\ location) without
any problems.
The full database backups work ok (although they are backed up to a different server to the transaction log backups).
Following the failure, if the transaction log has the following manually applied :
backup log ourdatabase with truncate_only
DBCC SHRINKFILE ('ourdatabase', 306)
Then the transaction log backup job will run OK for several more hours on its hourly schedule (between 00:30 and 23:30)
The Job runs every hour and runs OK for next 3 or 4 hours after the manual fix, but will then fail again at the same specific time (usually around 01:30 am).
Running DBCC CHECKDB ('ourdatabase') does not report any problems with the database - output shows CHECKDB found 0 allocation errors and 0 consistency errors in database 'ourdatabase'.
Code used to backup transaction log is :
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = '\\ourbackupservername\servers\SQL_Servers\oursqlservername\TranLog_Backups\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
+ '_'
+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.TRN'
BACKUP LOG @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Any ideas what could be causing the transaction log backup to fail - particularly frustrating because it will run OK for a while after applying the backup log/DBCC SHRINKFILE manual statement, and also because we don't get this problem on any of the other 45 databases from the same SQL Server 2000 instance ...... Also, have never came across this 'BlkHeader from strip 0' message on the error log before ...
Thanks
Ray