Post reply

Reboot takes a very long time

  • February 10, 2004 at 4:20 pm

    #65931

    Hi everyone, I had posted a message on this board a week or two ago about the same problem.  I have a SQL 2000 SP3 running on Win 2000 (with latest SP).  Recently, everytime I reboot the computer, one of the databases on this server takes a long time to rollback/forward transactions.  It takes more than 2 hours for the rollback to finish and in the meantime, no one can access the databases to do any kind of work.  Previously I used to just restart the server and someone on this board suggested that I should shut down the sql service before I reboot the server.  Another person on this board suggested that I need to have my applications commit more frequently.  Right now that database is in the development stage, there is a lot of programming going on.  So I asked the developer to make sure that his scripts commit frequently, which he did.  Today I rebooted the server by first shutting down the sql service manually and then rebooting the server.  And again it took more than 2 hours for the database to rollback.  Please help, what is causing the long rollbacks?  Thanks in advance!

  • February 10, 2004 at 4:29 pm

    #493518

    Hi,

    If you have Implicit Transactions turned on for your server, or if your application is turning this option on, try turning this off.  This will return you to 'autocommit' mode, which will cause a commit each time there is a successful sql statement.

    -Dan B

  • February 10, 2004 at 5:05 pm

    #493524

    Thanks Dan, I have made the change so that all databases will run in that mode...for now.  Are there any performance issues or any other issues that might come up once Implicit Transactions has been set to OFF? 

  • February 10, 2004 at 5:22 pm

    #493532

    I think your performance should improve actually.  Just think about all those open transactions being held by sql server waiting for a commit that never comes.  Turning this off should also help to keep your log file size down. 

    Of course the only real way to know is to test in your environment.

    -Dan B

Viewing 4 posts - 1 through 3 (of 3 total)

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