Volume Shadow Copy Services Start and so do the Insertion Timeouts

Question

Volume Shadow Copy Services Start and so do the Insertion Timeouts

Buzz-523717

SSC-Addicted

Points: 494
More actions
November 19, 2010 at 7:49 am

#231786

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.

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

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

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

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 2

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.

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 3

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

Adiga One Orange Chip Points: 27284 More actions · Answer 4

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

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 5

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....

Adiga One Orange Chip Points: 27284 More actions · Answer 6

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

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" 😉