October 30, 2003 at 2:37 pm
I have a server with 4 user databases. I have created a different maintenance plan for each user database. All 4 databases are getting nightly backups and transaction logs every 3 hours. The backups on 3 of the databases run without errors. But on one database the transaction logs are periodically failing and the full backups consistently failing.
All 4 maintenance plans are backing up to the local disk (all in the same folder) which then get backed up nightly via Tivoli.
The error messages in the errorlog are:
BACKUP failed to complete the command BACKUP DATABASE [tempest_prod] TO DISK = N'E:\Microsoft SQL Server\MSSQL$XL\BACKUP\txl_prod\txl_prod_db_200310301606.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
BackupDiskFile::OpenMedia: Backup device 'E:\Microsoft SQL Server\MSSQL$XL\BACKUP\txl_prod\txl_prod_db_200310301606.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).
Here is the maintenance plan report from the full backup failing:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'Servername\XL' as 'Domain\SQLService' (trusted)
Starting maintenance plan 'XL' on 10/29/2003 9:00:03 PM
[1] Database tempest_prod: Database Backup...
Destination: [E:\Microsoft SQL Server\MSSQL$XL\BACKUP\txl_prod\txl_prod_db_200310292100.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[2] Database txl_prod: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'E:\Microsoft SQL Server\MSSQL$XL\BACKUP\txl_prod\txl_prod_db_200310292100.BAK' is incorrectly formed. SQL Server cannot process this media family.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'XL' on 10/29/2003 9:00:17 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
When the transaction logs fail, they give the same error messages. But they only fail about 25% of the time.
Is there any other information that would be useful? Any suggestions are appreciated.
October 31, 2003 at 2:09 pm
October 31, 2003 at 2:41 pm
Disk space?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 3, 2003 at 2:36 pm
Backing up to a local hard drive, no tape drive even installed and there is plenty of disk space (all 4 databases total less than a gig and there is 60 gigs free) .
Since my original post, I've noticed something. It seems that the stats option in the backup command (run from Query Analyser or scheduled in a job) seems to cause the full backup to fail. For instance, this command fails:
BACKUP DATABASE txl_prod TO DISK = 'E:\Microsoft SQL Server\MSSQL$XL\BACKUP\txl_prod\txl_prod_db.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
while, this one succeeds (stats = 10 removed):
BACKUP DATABASE txl_prod TO DISK = 'E:\Microsoft SQL Server\MSSQL$XL\BACKUP\txl_prod\txl_prod_db.BAK' WITH INIT , NOUNLOAD , NOSKIP , NOFORMAT
I don't notice the same behaviour with the transaction logs however, scheduling a backup log statement that includes the stats = 10 option is successful 100% of the time.
Seems very strange to me as I thought the stats option just displays the percent complete while the statement runs. Any reason the stats option should cause the backup statement to fail? Or any reason why the scripted transaction log backups run fine but the sqlmaint jobs fail periodically? Even though it's not very hard to script out my own jobs, I would really like to find the REASON the maintenance plan jobs are failing.
Any thoughts are appreciated.
November 5, 2003 at 8:18 am
Hi friends:
I have the same problem in one of my production server.
I don't know why the backup of one of six database fail with the following error:
BACKUP failed to complete the command BACKUP DATABASE [tempest_prod] TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\DM_Costos_db_200310301606.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
BackupDiskFile::OpenMedia: Backup device 'D:\Microsoft SQL Server\MSSQL\BACKUP\DM_Costos_db_200310301606.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).
In same cases the backup finish OK after restart the machine but not always restart fix the problem.
Please, any help will be appreciated
November 17, 2003 at 11:51 am
Are you doing nightly backups and have you configured files to be kept for a period of 24 hours?
If you have, is the available disk space able to succesfully hold backups for two nights without deletes?
Mike
November 28, 2003 at 2:00 pm
Just wanted to report back that I have actually solved this problem. In my case someone had added an invalid Named Pipes alias in the client network utility on the server. Removing this alias (as it wasn't needed) solved my problems.
Thanks to everyone who offered their suggestions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply