Tag Archives: backup

Stop database backup information from appearing in the Error Log

This post is in response to a question from one of my blog readers. Whenever a Database or Transaction Log is backed up, a corresponding entry is logged in the SQL Server Error Log regarding the backup. On an instance which has quite a lot of databases or if the transaction log backup is scheduled to taken very frequently, the entries regarding the backup operation will eventually bloat the Error Log like in the below screenshot.

Is there a way to stop these messages from appearing in the Error Log?” was the question. Yes, it is possible. The Trace Flag 3226 has to be enabled as a start-up parameter as seen in this screenshot.

Once the SQL Server Instance is restarted after enabling this trace flag, the information about the successful backup operations will not be logged in the Error Log. However, whenever any backup fails it will be logged in the Error Log even with T3226 on.

Was that backup file taken through Native or third party tool?

On Production Instances it is common to take the SQL Server Database backup through third party tools. Until SQL Server 2008, only the third party tools provided the most important feature, Backup compression. These tools also provide backup encryption along with compression.

At times, the DBA needs to take ad-hoc database backup manually in cases where a Change Control to be deployed. When the disaster strikes, it will be little tedious to find out whether a particular database backup was taken through the Native SQL tools or through the third party backup utility.

In such situations, I use the RESTORE HEADERONLY command to quickly check if the backup was taken through the third party tool or not. Here is a sample script.


If the file was taken through SQL Server Native tool, this command would return the header information correctly.

If the database backup was taken through a third party tool, this command usually fails with the below error.

Another approach is to query the backupset table in MSDB. The values in the description and name can also be used to distinguish between native and third party tool backups. While taking manual database backups, we usually don’t enter the description for the backup set. Hence usually this column will have a null value for database backups taken manually. Also some tools maintain the separate software_vendor_id for the database backups taken through them.

Please feel free to share if you are following a different approach to verify the tool through which the database backup was taken.

The backup data is incorrectly formatted

Few days back when I was checking the Backup Report, found that for one database backup did not happen for quite a lot of days. Asked my team member to check why it was failing. He found out that the Database Backup job was failing with the below error.

The backup data in '\NetworkSharebackupfilename.bak' is incorrectly formatted.
Backups cannot be appended, but existing backup sets may still be usable.
[SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

The backup script was supposed to overwrite the existing backup file since it was using with init. But it was not doing so. Within seconds the backup job would fail with the above error message.

What does this error message mean?

Error message 3266 is reported when SQL Server detects filemark error on the backup device. A filemark for a backup device holds all the details of a backup device like the size of the block, number of blocks in a device etc. If a filemark error is detected, SQL Server treats the entire backup media as corrupt and does not write any more data to the backup media.

How to resolve this issue?

Resolving this error message is quite easy. One option is to delete the existing backup file and the other one is to use the with format option in the backup script.

Since the first option was the easiest, we decided to delete the file and restart the backup job. But the DBA team did not have access to the network share where the backup file is located. But the SQL Server service account had FULL permissions on it. Hence the DBA executed the following query to delete the old backup file.

xp_cmdshell 'del \NetworkshareBackupFileName.bak'

Since the xp_cmdshell command executes under the credentials of the SQL Server service account, the backup file got deleted. When the job was executed the next time, the database was backed up to the network share successfully.

This is one of the reasons why I personally don’t recommend taking database backups directly on to a network share. I have seen the backup jobs reporting success, still the backup file was corrupt when it was needed most. The best approach is to take the database backup to local drive and then schedule a copy job using utilities like ROBOCOPY which provide far more flexibility while copying a file with options like auto retry on error.

Could not load file or assembly error while using TDP makes a come back

Couple of month’s back I had posted about an error while trying to backup the database using TDP. The same issue occurred again but this time on a different server. The error message was same as earlier.

Unlike last time I did not have do any guess work as to why this error started all of the sudden. Couple of days back the Application Support team had tried re-installing SQL Server client components and also applied some updates to Visual Studio components. From the Event Logs it looked like the installation did not go well and they “somehow” got their application working. The Event Log was filled with messages like this.

Event Source:	.NET Runtime Optimization Service
Event Category:	None
Event ID:	1101
Date:		12/16/2010
Time:		8:30:25 AM
User:		N/A
Computer:	ServerName
.NET Runtime Optimization Service (clr_optimization_v2.0.50727_64)
- Failed to compile: Microsoft.SqlServer.SQLTaskConnectionsWrap,
Version=, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002

But the changes made in the SMO components did not go well with the TDP application. To fix this issue I did not have to take the longer route like I did last time. Since the database backups had failed for two consecutive days, it was a High Severity Incident and I could do *anything* to fix it. The option was to either reinstall TDP for SQL Server or Install the SMO components which the TDP application was referring to. Since the former is out of scope for the DBA team, I chose the latter.

All I had to do was to download and install Microsoft SQL Server System CLR Types and Microsoft SQL Server 2008 Management Objects from here. As soon as these were installed Microsoft.SqlServer.Smo Version assembly was available in C:\WINDOWS\ASSEMBLY folder. As expected TDP started working fine and I got the license to spend my Saturday on something other than SQL Server related issues 😀

What’s in a name? Enough to make the backup jobs fail

Database backup failure is one of the common issues faced by the DBA. There are many reasons for the backup to fail like Permissions issue, lack of drive space and so on. Recently I came across a question where the backup was failing, but not for the obvious reasons.

What is the issue?

  • All the databases on an instance were configured to be backed up using a Maintenance Plan. One of the databases had some issue and it was detached/attached back to the instance.
  • The database backups for all the databases were successful except for the one which was attached recently.
  • The error message read something like this.
"Cannot open backup device 'D:\backup\mydatabase \mydatabase _backup_201012081958.bak'.

Operating system error 3(The system cannot find the path specified.).

What troubleshooting was done?

  • Checked if the folder D:\backup existed. It did.
  • Checked for the permissions of the service account on the backup path. It was not an issue since the other databases were being backed up to the same path.
  • Any error messages in the SQL Server Error Log during that time. No error messages reported except for the one mentioned above.
  • Checked the drive for any possible issues. No error messages were available in the Event Viewer.

How the issue got fixed?

A closer look at the error message reveals it all.

Yes, you guessed it right. The database name had trailing spaces in it! While attaching the database through SSMS one extra space got added at the end of the database name. Renaming the database using the below script fixed the issue.

ALTER DATABASE [mydatabase ] MODIFY NAME ='mydatabase'

I/O is frozen on database, No user action is required

I recently responded to one of the questions in which the user wanted to know what these messages in the SQL Server Error Log mean.

spid92,Unknown,I/O is frozen on database [databasename]. No user action is required.
spid92,Unknown,I/O was resumed on database [databasename]. No user action is required.

Even in my environment I have seen this message in some of the Data warehouse instances. This message is logged in the Error Log whenever any backup service making use of SQL Server Virtual Device Interface (VDI) tries to backup the database (with snapshot)/drive on which the database files reside. Microsoft Backup (ntbackup.exe), Volume Shadow Copy (VSS), Data Protection Manager (DPM) and third party tools like Symantec Business Continuance Volume (BCV) are some of the application which cause this message to logged in the SQL Server Error Log.

What does these messages mean? Let me explain this with an example. Suppose ntbackup.exe is configured to take the backup of D drive. This drive has some data files related to few databases on SQL Server. Since the data files are in use by SQL Server, if these files are copied as it is the files in the backup will be inconsistent. To ensure that the database files are consistent in the drive backup, this application internally issues a BACKUP DATABASE [databasename] WITH SNAPSHOT command against the database. When this command is issued, the I/O for that database is frozen and the backup application is informed to proceed with its operation. Until the BACKUP WITH SNAPSHOT command is complete, the I/O for the database is frozen and the I/O is resumed once it completes. The corresponding messages are logged in the SQL Server Error Log.

If the ntbackup is issued during business hours, the users accessing the database will surely have some connectivity issues. This is one of reasons why the backup on production systems should not be done during business hours. Like I mentioned earlier, in my environment I have seen these messages but during the regular downtime for backups.

Pruning backup history taking too long to complete? Read on

Just a while ago, one of the colleagues from my previous employer posted a question on #sqlhelp to know how backup related records in MSDB database get purged. I gave him this screenshot of SQL Server Agent properties which controls the Job History in MSDB.

I was about to tweet about sp_delete_backuphistory stored procedure which can be used to prune the backup/restore history in MSDB. But someone else did it before I could. This stored procedure reminded me of one of the questions I had come across in one of the forums, where the user trying to drop the database using SSMS and SSMS would just hang. After a few responses he informed that he had selected “Delete backup and restore history information….” option while dropping the database.

There were no processes which were blocking this still the database would never get dropped. He had several thousand records of backup history for this database. The drop database session was progressing but it was really slow because it was waiting for the “Delete backup and restore….” transaction to complete. You may be surprised to know the reason for this. No indexes/foreign keys are there on the tables related to Backup/Restore history tables! sp_delete_backuphistory stored procedure deletes data from the following tables in MSDB.

  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup
  • restorehistory

Run this query on MSDB and you would be surprised to know that there are only 4 indexes present for those 8 tables.

    t.name as [TableName],
    ind.name as [IndexName],
    col.name as [ColumnName],
    ind.type_desc [IndexType]
    sys.indexes ind
inner join
    sys.index_columns ic on
      ind.object_id = ic. object_id and ind.index_id = ic. index_id
inner join
    sys.columns col on
      ic.object_id = col.object_id and ic.column_id = col.column_id
inner join
    sys.tables t on
      ind.object_id = t. object_id
     ind.is_unique = 0
    and ind. is_unique_constraint = 0
    and t.name in
order by
    t .name, ind.name

Since there are no proper indexes on these tables, the stored procedure would obviously take a long time to complete. What is the fix? Create the indexes on the columns that are being referred by the sp_delete_backuphistory stored procedure. Geoff N. Hiten had written a good script which does this job. The script can be downloaded from here. The user created the indexes on MSDB using this script and he was in for a surprise. The backup job history pruning completed quickly and the database got dropped in a few seconds.

If you are facing similar issues, please go ahead and create those indexes on MSDB tables. Even if you are not affected by this yet, it doesn’t hurt to keep these indexes on. You never know when you need them 😉