March 18, 2008 at 1:37 pm
I am mirroring a SQL 2005 database and have a snapshot scheduled on the mirror every hour. This normally takes 3 seconds but now seems to be experiencing problems with the snapshot creation. A 3-second snapshot will run, then it might take 5 minutes, then maybe 10 minutes, etc. and each time a new snapshot runs it takes longer. I've been turning off services and doing reboots to see if something is hanging or using up resources and can't determin what is going on. The server is a 64-bit Windows 2003 with sp2 and SQL 2005 is also sp2. There are no errors in the NT logs or SQL log except for login failures during the creation of the snapshot. The snapshot file is 395 GB.
Has anyone run across this before or have any ideas of where I might look?
March 18, 2008 at 2:47 pm
That's really strange as the snapshot creation should not be doing anything. Is this during periods of heavy change to the main database?
Only thing I can think is that there are a lot of pages being written at that time.
March 19, 2008 at 5:01 am
I was thinking that maybe the virus scans were scanning the data folder where the mirrored database and snapshot live. The scans were stopped yesterday so I'm waiting to see happens today. If that's not the issue then I'm going to start looking at the Veritas server backups. This is new to our server group and I don't know how they set it up. I understand that it can back up live files so if this is the case, maybe it is part of the problem. Will keep you posted. Thanks.
March 19, 2008 at 9:41 pm
If I understand your question right, you have a database mirroring set up. On the mirrored database, you have snapshot scheduled every hour, and the snapshot runs 3 seconds or 5/10 minutes. As I know the snapshot takes the changes of the (mirrored) database, if the changed volume is bigger, the time takes longer then. The snapshot file size is the same as the database size even no much data inside (sparse file).
Maybe need to take a look the transaction volume passed from principle to the mirrored database during that time. Or check the actual size of sparse file itself.
Check books online:
“Understanding Sparse File Sizes in Database Snapshots”
“How Database Snapshots Work”
While other process on this mirrored server could affect, but may something from database itself.
March 20, 2008 at 5:58 am
Actually, I probably need to explain the snapshots on the mirrored database. They are scheduled every hour and normally take 2-3 seconds to complete--even on a very busy database. I have the job set to re-try 30 times at 1 minute intervals, and sometimes the job will try for up to 30 minutes before it can create the snapshot. I had the virus scans stopped on the server the other day, and since then the snapshots have been normal.
Occasionally at night it will take awhile to create a snapshot (meaning with re-tries), but that's only because some of the tables on the Principal database were reindexed--which is normal. When the issue occurred during the day the Tlog backups were small and database activity was normal.
So far, I think my assumption was right about the virus scans. Thanks.
December 7, 2011 at 10:12 am
Moderator please take a look at the page ...
it is unavailable.
August 6, 2012 at 11:42 am
vijaygopal1216 (12/7/2011)
Moderator please take a look at the page ...it is unavailable.
It looks like the author moved the article one day in the future. the link below works.
he says that the snapshot will take a long time if there is any long-running transaction on the source database. too bad.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply