Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of raymurphy
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.