Volume Shadow Copy Services Start and so do the Insertion Timeouts

  • Hi,

    I have a single SQL 2005 Standard instance running on Clustered Windows Server 2003 x64.

    Transaction log backups are performed every 6 hours by executing master..sqlbackup in a script step of the scheduled job.

    Full database back-ups of the database are performed nightly as well using the same method.

    During the Transaction Log backups and Full database back-ups there are not issues of timeouts for insertions.

    For the last 4 incidents where timeouts have been logged, the application event log on the cluster reflects the Volume Shadow Copy services starting at the exact time the issues begin. The issues end with the service stopping.

    I'm not sure what use the Volume Shadow Copy service is providing if we already are backing up the transaction logs and full database.

    I'm also curious if anyone has had similar issues with contention with Volume Shado Copy Service and SQL 2005.

    Thank you for any help or insight you might be able to provide.

  • When the Volume Shadow Copy is running on the server, do you see any Error Messages in SQL Server Error Log/ Application Event Log?

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • I do not see errors in the database event logs during the period of time VSS is running.

    I see normal messages:

    1) The Volume Shadow Copy service was successfully sent a start control.

    2) The Volume Shadow Copy service entered the running state.

    3) The Microsoft Software Shadow Copy Provider service was successfully sent a start control.

    4) The Microsoft Software Shadow Copy Provider service entered the running state.

    5) The Volume Shadow Copy service entered the stopped state.

    6) The Microsoft Software Shadow Copy Provider service entered the stopped state.

  • I should indicate also that there are no messages logged to the SQL Server Logs during this time.

  • I have seen issues with VSS but it used to have "I/O is frozen..." messages in SQL Server Error Log. This is because VSS Writter issues BACKUP DATABASE ...with SNAPSHOT command. This article is a good read on this issue.

    Since there are no messages in Error Log in your case, I doubt this could have caused an issue.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Thank you for your response and for the link. It was a good read and it does help.

    The timeouts that are being logged are being logged by processes on a different server attempting to access the database and perform insertions.

    They respond back:

    Message: Exception Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. /// Stack Trace: at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

    at System.Data.SqlClient.SqlConnection.Open()

    The I/O freeze at the time the remote service is trying to access the db may trigger this timeout exception. I'll keep watching the processes.

    I'm wondering though if we need the VSS snapshot at all when we are doing 4 backups of the transactoin logs a day, full nightly db backup etc. It seems VSS runs many times throughout the day - though, it does not trigger the issue each time. Of course, there might not be insertion activity at the other times....

  • If VSS is causing issues, it can be disabled from running. Instead you may want to increase the number of transaction log backups that you take, to make up for disabling VSS backup.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Buzz-523717 (11/19/2010)


    executing master..sqlbackup in a script step of the scheduled job.

    Check the Redgate SQLBackup logs for details of any potential errors or warnings

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply