February 2, 2004 at 12:33 pm
Hi everyone,
I have a server with a 250Gb database on it. Every time I reboot
this server, the database rolls back/forward transactions and it
takes approximately 2 hours for this process to finish. How can I
make the roll back/forward time shorter? My other question of course
is why does it take so long? Thanks in advance for your help.
February 2, 2004 at 12:48 pm
Depending on the nature of your database (not just its size), there may not be anything you can do. You might want to check the nature of the transactions being performed and if possible, break them up into smaller (quicker) transactions. Or you could possibly optimize them to that they just complete faster. Faster hardware might help too if you are CPU or IO bound etc...
If you can't do either of these things then you may just have to live with the current situation i.e. a long running transaction will take a long time to rollback.
Having said that, two hours seems pretty excessive, unless you are attempting to stop in the middle of a big ETL process. I would seriously look at your activity and see if you can't either break it up a bit or optimize it. Basically, the problem isn't the time it takes to shut it down, that's just a symptom.
P.S. resist the temptation to just "hit the switch" because that will just cause SQL Server to take a long time in recovery on start up.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 2, 2004 at 1:00 pm
Is the delay upon stopping or starting the SQL Server service? What is the recovery interval option set to on this server?
--Jonathan
February 2, 2004 at 1:39 pm
That's a good point, I assumed that the delay was on shutdown, now that I re-read the post it's not clear...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 2, 2004 at 3:02 pm
Hi all, sorry for the delay in my response as I am having trouble getting to this site for some reason.
The delay occurs when I reboot the server. I have not rebooted the service and so I don't know if the same delay will occur.
The recovery interval has been set to 0.
The type of processing that occurs on this server is actuall long updates and inserts. But I thought that all transactions will commit before shutdown (all users stop all their processes before reboot) and so upon restarting the server there would not be any rollbacks.
Thanks for your help again.
February 2, 2004 at 3:30 pm
Do not just shut down Windows to "reboot." That is forcing a SHUTDOWN WITH NOWAIT after a short time (20 seconds?), which means there will be no recent checkpoint.
Instead use one of the methods that guarantees an orderly shutdown of SQL Server:
http://msdn.microsoft.com/library/en-us/adminsql/ad_1_start_6628.asp
Your shutdown will take longer, but your startup will be much faster.
--Jonathan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply