troubleshooting Question

Problem Backingh Up Transaction Log on Old SQL000 Database

Avatar of raymurphy
raymurphy asked on
DatabasesMicrosoft SQL Server
3 Comments2 Solutions113 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros