Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Problem Backingh Up Transaction Log on Old SQL000 Database

Avatar of raymurphy
raymurphy asked on
DatabasesMicrosoft SQL Server
3 Comments1 Solution113 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
Avatar of Vitor Montalvão
Vitor MontalvãoFlag of Switzerland imageIT Engineer
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers