transactional backup failing

lchalpan · August 30, 2007 2:26PM

Getting a failure 'occasionally' for a trans log backup. Always happens at the same time of day (3:00 am) Can't find anything in my search that might give me an idea of what is going on. Generates a backup file regardless. Suggestions?

Warning 175: Another process might be locking this file: g:\MSSQL\BACKUP\logship\active\DBName.db.Log070828030000.sqb (attempt 1) (Cannot open file "g:\MSSQL\BACKUP\logship\active\DBName.db.Log070828030000.sqb". The process cannot access the file because it is being used by another process.

Eddie D · September 3, 2007 2:45PM

Thank you for post into the forum.

Can you please check to see if you have any Anti-Virus software running, particularly if you an on-access AV Scanner?

I have seen this type of error before with users running on-access AV scanner or a scheduled AV scan.

Many Thanks
Eddie

Eddie Davis
Red Gate Software Ltd
Technical Support
E-Mail: support@red-gate.com

Randy Doub · August 13, 2008 11:49AM

I'm getting Warning 175 once or twice a week using 5.3.0.178. I'm sure its the DFS replication software running, copying my backup files to an offsite server. I did a restore VerifyOnly and I'm told the backup set is OK. My question is, what happens? Does SQL Backup just wait until the file is unlocked? Is there the potential for a timeout and error? How come it doesn't happen with the native tsql backup command?

Randy Doub · August 14, 2008 8:58AM

Looks like its three strikes and out. Correct? So is my backup file any good?

SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [tkcsdb] TO DISK = '\\filcnc1s08pp001\sqlback\Kronos_v5\<AUTO>.sqb' WITH COMPRESSION = 1, ERASEFILES = 30, MAILTO_ONERROR = 'r.doub@kennedyhealth.org' "

ERRORS AND WARNINGS

8/14/2008 12:10:00 AM: Backing up tkcsdb (full database) to:
8/14/2008 12:10:00 AM: \\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb

8/14/2008 12:10:00 AM: BACKUP DATABASE [tkcsdb] TO VIRTUAL_DEVICE = 'SQLBACKUP_830117AB-665B-4E75-9A11-784DE59030D9' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (tkcsdb), 8/14/2008 12:10:00 AM', DESCRIPTION = N'Backup on 8/14/2008 12:10:00 AM Server: SQLKNS01 Database: tkcsdb'

8/14/2008 12:19:13 AM: Database size : 55.974 GB
8/14/2008 12:19:13 AM: Compressed data size: 9.429 GB
8/14/2008 12:19:13 AM: Compression rate : 83.15%

Processed 16472 pages for database 'tkcsdb', file 'tkcsdb_Data' on file 1.
Processed 1285640 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs1' on file 1.
Processed 1317960 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs2' on file 1.
Processed 485048 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs3' on file 1.
Processed 346760 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs4' on file 1.
Processed 1208368 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs5' on file 1.
Processed 8760 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs6' on file 1.
Processed 1184 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs7' on file 1.
Processed 560 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs8' on file 1.
Processed 576 pages for database 'tkcsdb', file 'tkcsdb_data_tkcs9' on file 1.
Processed 65 pages for database 'tkcsdb', file 'tkcsdb_Log' on file 1.
BACKUP DATABASE successfully processed 4671393 pages in 550.799 seconds (69.477 MB/sec).
8/14/2008 12:19:14 AM: Warning 175: Another process might be locking this file: \\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb (attempt 1) (Cannot open file "\\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb". The process cannot access the file because it is being used by another process.
)
8/14/2008 12:19:20 AM: Warning 175: Another process might be locking this file: \\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb (attempt 2) (Cannot open file "\\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb". The process cannot access the file because it is being used by another process.
)
8/14/2008 12:19:26 AM: Warning 175: Another process might be locking this file: \\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb (attempt 3) (Cannot open file "\\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb". The process cannot access the file because it is being used by another process.
)
8/14/2008 12:19:32 AM: Error 775: Failed to open file to write LSN data. Another process might be locking this file: (\\filcnc1s08pp001\sqlback\Kronos_v5\Aug_14_2008_tkcsdb_FULL.sqb (The process cannot access the file because it is being used by another process))

perrywhittle · August 18, 2008 8:59AM

rather than backup across the network, backup to local drive and use the "copyto" parameter to copy the file to the network

Randy Doub · August 18, 2008 9:16AM

I understand the "copy to" functionality. I don't always have the luxury of available local disk to do that. I will address my issue by having the replication software run in a window when there aren't a lot of full backups going on, instead of the "immediate" option.
I still have the same questions... I wanted to know if there was the potential of failure due to timeout. Apparently there is. Is it always three attempts before failing or is it the duration of the attempt that causes the failure? Why does this happen with SQL Backup and not the TSQL backup command? Thanks.

Matthew Flatt · August 21, 2008 10:17AM

Hi Randy,

This error is encountered when SQL Backup attempts to write header information to the backup file and is unable too because another process has grabbed the file.

SQL Backup will attempt to write the data 4 times, if it fails the 4th time you are given the 775 error and the data is not written.

The backup is still valid but may cause problems if you try to restore multiple SQL Backup transaction log backups in the same job. This is because the LSN data is unavailable to put the backups in correct restoration order.

I hope this helps.

transactional backup failing

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk