SQL Server Performance

SQL 2000 Takes 5+ Minutes to Startup

Discussion in 'General DBA Questions' started by jharrison624, Oct 11, 2009.

  1. jharrison624 New Member

    I'm working with a client that is experiencing a strange problem. It takes SQL over 5 minutes to fully startup. This is a SQL 2000 with SP4 running on Windows 2003 with SP2. Everything appears to be working fine after the startup, but the delay is strange. The server has about 8 databases on it and all are noted as "Startign up database 'XXXX'. in the log. Then after about 5 minutes, "Recovery complete." is added to the log.
    This also seems to be a new problem that has developed in the last couple of months, but nothing major has changed on the server.
    I've started SQL using the -T3608 trace flag so that only Master is started, and then walked through each database to see how long each would take. All were quick to start, except for one. It took exactly 5 minutes to start. So, this database seems to be the problem. I can CheckDB on it and found no problems. I've stopped and restarted SQL several times to see if it would vary, but always 5 minutes.
    Does anyone have any ideas of what to look at next? I'm assuming that something is going on with the recovery process in this particular database, but nothing is showing up in the log. Is there a way to see what is going on during the recovery process?
    The database is fairly large, 70 GB, but I've worked with much larger without this issue. All the settings for the server and database appear to be the defaults.
    Any help would be greatly appreciated!!
  2. moh_hassan20 New Member

    [quote user="jharrison624"]I can CheckDB on it and found no problems[/quote]
    it may be due to abrupt shudown, to resolve have a look:
    http://support.microsoft.com/kb/820835
    review the disk performance where database are located
    check history of previous restore of the database, may be problems in restored db

  3. jharrison624 New Member

    I don't believe it has to do with the shutdown process. I've shutdown the SQL service several times during a period of low activity. The following startup is always 5+ minutes for the recovery to complete.
    The disk performance could contribute to the issue. All the system, data, index and temp files are on separate partitions, but on the same raid 5 disk array. We are moving to another server with dedicated drives for each on a SAN in the near future. I'm predicting that the problem goes away when we move.
    The strange thing about this issue is that it is always 5 minutes. Seems like a timeout of some sort. But I can't find any errors that should be the result of a timeout.
    Another piece of the puzzle that may or may not help. I was in the process of spliting the tempdb into multiple data files because of heavy use of temp tables. When allocating each data file, I set the file size to 2 GB with no growth. There are 4 CPU cores, so I created 4 files (or actually 3 files and resize the primary file). When I ran the ALTER DATABASE MODIFY FILE or ADD FILE, the command took about 2-3 minutes to run. I assumed that SQL was doing some sort of formatting of the disk space, but I don't ever remember it taking this long on other servers I've work with. But this was the first SQL 2000 server that I had implemented the multiple tempdb data files. I thought this may have been the problem, since after setting up all the files, I did a restart of SQL to ensure all was good. That was when I noticed the long startup. This is a new client and I haven't ever restarted this SQL server before, so I assumed that it was a problem with the tempdb setup I had done. But after several restarts and testing with trace files, did I find out it was an existing database that was (or seems to be) the issue.
    I did create a VM with Windows 2003 Server w/ SP2 then installed SQL 2000 w/ SP4. On this system, I went through the process of spliting the tempdb data files into 4. All the MODIFY FILE / ADD FILE changes ran in a few seconds as compared with the several minutes. So, I walked through all the settings again looking for anything different, but didn't find anything. While this isn't a true apples-to-apples comparision, it is in the same ballpark. No idea why as why the adding data files is taking so long.
    As far as I know there have not been any restores of this database, but will check with the client on it.

Share This Page