Sometimes SQL Server slow in the morning - w2003 + sql 2000

Posted on 2004-11-10
Last Modified: 2008-03-06
I recently upgrade a server to run w2003. It was a clean install that had been happily running w2000 with the same database application backend on for years.

Since the upgrade, on two occassions I've come in, in the morning, to find the database application is running like a pig  ... it still works as it should but results are taking 10 times longer than normal to return.

A 'restart' of the MSSQLSERVER service sorts it instantly, although obviously that is less than idea as it kills the application which people are using.

Does anyone have any ideas why this might be happening ... it never happened until w2000.

Using SQL 2000 + SP3. I am pretty useless as using any tracing/debigging tools in SQL, so if you are going to suggest anything like that, go easy on me ....

Many thanks

Question by:SpencerSteel
    LVL 13

    Accepted Solution

    If this is happening on an occasional basis, it is likely that there is a user/app running a process that is taking a lot of resources on the system.

    Next time before you restart the service and stop everything that is running, have a look within Enterprise manager, Expand the Mmgt tab,  Current Activity, and have a look to see if anybody is blocking anybody else or what is actually running on the server.  You can check the three wait type fields to determine who/what is part of the cause of the bottleneck.

    You can also run: sp_who -- to find out what is running on the server in QA.
    If all this fails, setup a sql-server profiler trace to capture the user commands that have been run onto your server so you can then examine them later. (If you don't know how to use this have a look on MSDN -- Microsoft, lots of instructions there).

    Another way is to buy a product such as lumigent log explorer which will let you see what was running in the transaction logs, so when you restart the machine you can examine the transaction log to determine what was making the whole thing go wrong.  Okay you have to buy this product but it is very easy to use and useful.

    I doubt that it has anything to do with running on w2003, that the server is running slowly -- it is recommended not to upgrade a w2000 machine to w2003 and do a clean install.
    LVL 18

    Expert Comment

    "Sometimes SQL Server slow in the morning "   Kinda like me :)

    It wouldnt be a scheduled full db backup or something would it.

    Author Comment

    Thanks guys- I'll let you know what I find ...

    (Oh, I did do a clean install - sorry for any confusion)


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now