Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007

    Unanswered: Query to find SQL activity at specific time?

    Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server.

    Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails.

    It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on.

    Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working.

    Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost.

    Thanks in advanced!

  2. #2
    Join Date
    Jan 2004
    In a large office with bad lighting
    Without specifics, all you can get is general answers. Go Google SQL Server Black Box and see if that will help you.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Mar 2004
    You can use SQL Profiler to run a SQL trace to a system file on the server or a network drive, or to a table on a different SQL instance. That way, you can choose what you want to look at, including users and spid's along with a lot of other data. It's completely customizable. You can also set the size so that it doesn't grow too large and then you can look at the data after the other server has crashed.

    It's also important to be sure that you have enough processor, memory and disk space as the first and foremost problem. I've come into a lot of systems that were having "hanging" issues and the servers were bare minimum or below what they needed to be running.

    I hope this helps.
    "An adventure is only an inconvenience rightly understood; an inconvenience is only an adventure wrongly considered." ~G.K. Chesterton

  4. #4
    Join Date
    Mar 2007
    Thank you! I think that will do it. Now I have something to go on.

Posting Permissions

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