September 4, 2012 at 8:58 am
I'm a longtime developer who recently became a (junior) DBA due to attrition. I've very familiar with SQL and T-SQL but not familiar with the care and feeding of the database.
I have a production database, let's call it DB.mdf, and a test d/b: DBtest.mdf, in the same instance. This morning I was doing a refresh of my test data from a recent backup file of my production d/b, DB.bak. In both systems the database and log files have the same logical name since that's what the test version of the front end expects. I restore them to different physical names of DBtest.mdf and DBtest_log.ldf.
The problem is that this morning, I had about 10 users already hitting the production d/b, but it immediately brought them to a halt; the front end kept timing out for all of them. I had to stop the Restore process of my test data so that the production db would wake up and go back to work. Then I had to delete my test d/b because it was hung in the ...Restoring state and wouldn't answer any commands.
Does anyone know why this happened? Was it because I had users in the same instance, even though they were hitting production and not test? Is Restore so resource-intensive?
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
September 4, 2012 at 9:04 am
BACKUP and RESTORE operations don't block, but they are rather resource intensive, so it's quite possible that things might appear to be blocking to other users.
My first guess would be that it's an I/O issue, as backup and restores are very I/O intensive. Do you have additional drives to use - like can you try restoring the test database to different volumes from the live database?
September 4, 2012 at 12:43 pm
As it turns out, you were right, it was intensive I/O slowing it down, but it was from a tape backup job being run this morning b/c of the Labor Day weekend. When I tried it a couple of hours later, it ran fine at about 1Gb/minute.
Thanks for the help,
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
September 4, 2012 at 1:39 pm
In SQL2005 a restore of any database in an instance caused the proc cache to be flushed, this would lead to a peformance hit. You would see messages to that effect in the errorlog. This would be a reason to do the restotre at a quite time.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply