Avatar of raymurphy
raymurphy
 asked on

Problem Backingh Up Transaction Log on Old SQL000 Database

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
DatabasesMicrosoft SQL Server

Avatar of undefined
Last Comment
raymurphy

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Alexander Ardatov

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
raymurphy

ASKER
Thanks for that, Vitor - shrinking down to 1MB size instead of 306MB seems to have fixed the issue, as there haven't been any subsequent transaction log backup failures since applying that manual shrink yesterday.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy