Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Angry Unanswered: SQL Server Maintenance

    I have been having an extremely annoying problem with SQL server. About 3 to 4 time a day, it starts running some job that takes 30+ minutes to finish. The problem is that it bogs the system down, and consumes so many resources, that it it is almost impossible to run anything while the job is running. Most of the time, this job runs when the server is idle. And, much of the time, it has been idle for at least 30 minutes, and often longer. Also, there is excessive hard drive activity while this task runs.

    I am unable to find out what is going on because Enterprise manager times out trying to connect to it, and other tasks remotely connecting either time out or get a network error trying to connect. I have task manager running all the time and it shows task 'sqlservr.exe' hogging the system when this is happening.

    Can anyone shed any light on what is happening, why, and how I can stop this?? If it is performing maintenance, is there a way to get it to schedule this for specific times rather than during normal idle system activity?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look for a job that fires every 6 to 8 hours. While the job is running, you can use OSQL to retrieve all runnable process SPIDs and dbcc inputbuffer(<spid>).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Posts
    24
    I never used OSQL before, so I assumed run it from command prompt. So, I opened a command prompt and ran it. It asked for a password and then froze. After 5 minutes it has still not shown anything (hard drive still pounding away). This is all that is showing in the command window...


    > osql
    Password:
    _

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    check to see if you have database maintainence plans created and check their running time
    it is common for people to initially install sql server
    then see the maintainance plan wizard and create a plan and then forget about it.

    then later on you regret it.

  5. #5
    Join Date
    Feb 2004
    Posts
    24
    I know for a fact that I have never played with, or even looked at, the maintenance plan. So, unless there is a default that is pre-defined by a new installation of it, then I have not defined one.... just checked... there was one defined for a database, containing some data extracted from a database someone gave me for testing. This database no longer exists on my system, so I don't see how this plan was doing anything. I looked at the settings and it was not set to perform maintenance on any of the existing databases. I did delete it anyway, in case it was doing something strange. I hope this is the culprit.

  6. #6
    Join Date
    Feb 2004
    Posts
    24

    Angry

    Well, deleting the maintenance job did nothing. It has already run whatever task it has been running twice since I deleted the maintenance job entry... once last night and once this morning. Thinking the cause might be external, I have done two things...

    1. put the server on a UPS (thinking there might be some power dips happening)
    2. I have tried to defragment the drive several times with only limited success. Some of the database files are so large that the there is insufficient free space remaining to defragment them. Working on the assumption that part of the "activity" might be to optimize the databases (and being unable to do so because of fragmentation), I have detached several of the larger databases and moved them (temporarily) to another drive to make enough room to properly defragment the drive (which I think was around 50% fragmented).

    I don't know if either will help, but they couldn't hurt.

  7. #7
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    Sounds like indexing to me as it uses "idle time"
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

  8. #8
    Join Date
    Feb 2004
    Posts
    24
    Then why would it be doing it 4 or 5 times a day, every day, even when I haven't added indices to any of the tables in any of the database files in over a week. Nor have I added/deleted/changed any of the records in the tables. I have only done queries against these databases. I only have one test database that I am changing and it has been detached for the last couple of weeks. Could it be re-analyzing column statistics instead (which I think only effects non-indexed columns)? And, I'm guessing, but queries against the databases might trigger this operation...

  9. #9
    Join Date
    Dec 2003
    Posts
    46

    Check for any scheduled Jobs

    Use Enterprise Manager to check for any scheduled Jobs. They are located in Management/SQL Server Agent/Jobs. Is there anything there?

    Alex

  10. #10
    Join Date
    Feb 2004
    Posts
    24
    Nope... nothing there at all. Empty slate.

  11. #11
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63

    trace activity

    Try using sql profiler to see what's going on.

  12. #12
    Join Date
    Feb 2004
    Posts
    24
    I think I have solved the problem. I spent much of the afternoon yesterday copying off some of the larger databases onto a separate hard drive in order to make sufficient room to defragment the drive. The fragmentation was actually somewhere around 75% - 80%. The problem does not seem to have happened since defragmenting. I am still in the process of copying the files back, but since mid afternoon yesterday, I have not heard the hard drive get hammered and the system performance has dramatically improved.

    I don't know what SQL Server could have needed to do for excessive fragmentation, but it does not appear to be happening now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •