Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Volume Shadow Copy Services Start and so do the Insertion Timeouts Expand / Collapse
Author
Message
Posted Friday, November 19, 2010 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:26 AM
Points: 144, Visits: 175
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.
Post #1023536
Posted Friday, November 19, 2010 9:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1023614
Posted Friday, November 19, 2010 1:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:26 AM
Points: 144, Visits: 175
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.

Post #1023791
Posted Friday, November 19, 2010 1:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:26 AM
Points: 144, Visits: 175
I should indicate also that there are no messages logged to the SQL Server Logs during this time.
Post #1023793
Posted Saturday, November 20, 2010 10:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1023938
Posted Saturday, November 20, 2010 10:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:26 AM
Points: 144, Visits: 175
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....
Post #1023946
Posted Saturday, November 20, 2010 11:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1023947
Posted Saturday, November 20, 2010 3:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 6,279, Visits: 13,514
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"
Post #1023972
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse