Options

transactional backup failing

lchalpanlchalpan Posts: 2
edited August 21, 2008 10:17AM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • Options
    Eddie DEddie D Posts: 1,780 Rose Gold 5
    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • Options
    Randy DoubRandy Doub Posts: 26
    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?
    · Share on Twitter
  • Options
    Randy DoubRandy Doub Posts: 26
    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))
    · Share on Twitter
  • Options
    perrywhittleperrywhittle Posts: 5
    rather than backup across the network, backup to local drive and use the "copyto" parameter to copy the file to the network
    · Share on Twitter
  • Options
    Randy DoubRandy Doub Posts: 26
    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.
    · Share on Twitter
  • Options
    Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    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.
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
Sign In or Register to comment.