What’s going on, on my server, right now?

  • TheSQLGuru

    SSC Guru

    Points: 134017

    1) sysprocesses is deprecated and will be removed from SQL server (in the next build if my memory serves). Like Grant said, you really should switch to various system views/DMVs

    2) I agree with others that sp_whoisactive is an awesome improvement to sp_whox

    3) storing stuff for trending and post-event analytics is a great idea!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • sqlpanther

    Ten Centuries

    Points: 1204

    I figured out the issue

    ALTER DATABASE [SQL_Monitoring] SET HONOR_BROKER_PRIORITY OFF-- It is not supported in SS 2k5 I commented it out.

    and I removed ALTER DATABASE [SQL_Monitoring] SET COMPATIBILITY_LEVEL = 100

    Other question what i have is from step 3 are they all in one step of a job we need to include or different steps , can anyone guide me through this please...

  • sqlpanther

    Ten Centuries

    Points: 1204

    I figured out the issue

    ALTER DATABASE [SQL_Monitoring] SET HONOR_BROKER_PRIORITY OFF-- It is not supported in SS 2k5 I commented it out.

    and I removed ALTER DATABASE [SQL_Monitoring] SET COMPATIBILITY_LEVEL = 100

    Other question what i have is from step 3 are they all in one step of a job we need to include or different steps , can anyone guide me through this please...

  • terry.home

    SSC Eights!

    Points: 864

    Thanks for this. Looks like something that I could use. A few minor errors, I think many of them are due to website converting from " (double quotes) to '' (two single quotes)

    I managed to correct most of these, but got a little stuck at the last script just before step4

    the first problem I found was

    EXEC @ReturnCode = msdb.dbo.sp_add_job

    @job_name=”Whats Running Now',

    (Note the quote mark before Whats)

    After changing that I had several more coding issues and had to put it on hold as I had to get back to work but I will be keen to get this working and continue with step 4 and beyond.

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1299

    If you are copying/pasting out of the article, yes, I do believe formatting did get messed up by the publisher. There is a .sql file at the end of the article that you can download. Just complete the areas I have marked with <add your info here> and you should be all set to run without issue.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1299

    @Terry! If you are copying/pasting out of the article, yes, I do believe formatting did get messed up by the publisher. There is a .sql file at the end of the article that you can download. Just complete the areas I have marked with <add your info here> and you should be all set to run without issue.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • M Garcia

    SSC Enthusiast

    Points: 192

    Great article! I am kinda new to SQL and was wondering if the code can be used to monitor several servers on the network from that one database?

    Regards

  • Kim Killian-SiteDataView

    Ten Centuries

    Points: 1299

    Yes parts of it. You can setup the SPROC, alert and job on each server you want to monitor. Then if you want to capture the data just write the results to a single database then change the report to reflect the new db.tablename. You can also forward the agent results from the alert by doing a right click on sql server agent and go to properties->advanced.

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • M Garcia

    SSC Enthusiast

    Points: 192

    Thanks Kim!

  • DavidZahner

    Ten Centuries

    Points: 1029

    This is good stuff, thanks for posting it Kim. It always amazes me how many SQL Servers are not monitored for even simple things even though it is extremely simple (especially using your system) to set up and configure. I created a monitoring utility that I initially used for client systems (but now just distribute as freeware) that incorporates some of these same features for anyone that is interested, you can check it out at 365DBA.com/MonitorInfo.aspx.

  • jtodd 60645

    SSC Enthusiast

    Points: 180

    Hello Kim,

    Appreciate your script. I was hoping i could just copy and paste as I'm not familar with SQL as I'm a newbie. I received the follwoing error:

    Msg 14261, Level 16, State 1, Procedure sp_verify_job, Line 56

    The specified @name ('DBA-SQL Monitor - Report Whats Running Now') already exists.

    How do I get around this? Would appreciate your guidance.

    Thanks

  • sqlpanther

    Ten Centuries

    Points: 1204

    This means the job wit that name is already been created...

    u need to go under the jobs and run it to verify the reports if u get in ur email..

  • jtodd 60645

    SSC Enthusiast

    Points: 180

    Thank you for the reply.

    yes, I understand that now and am now getting:

    Date 7/25/2013 1:42:50 PM

    Log Job History (DBA-SQL Monitor - Report Whats Running Now)

    Step ID 1

    Server AGV-SQLLAB

    Job Name DBA-SQL Monitor - Report Whats Running Now

    Step Name Run the Report

    Duration 00:00:00

    Sql Severity 16

    Sql Message ID 4701

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted 0

    Message

    Executed as user: NT AUTHORITY\LOCAL SERVICE. Cannot find the object "SP_Who_Results" because it does not exist or you do not have permissions. [SQLSTATE 42S02] (Error 4701)Unable to open Step output file. The step failed.

    Can you shed light on this for this newbie! 🙂

  • jtodd 60645

    SSC Enthusiast

    Points: 180

    Thank you for the reply.

    yes, I understand that now and am now getting:

    Date 7/25/2013 1:42:50 PM

    Log Job History (DBA-SQL Monitor - Report Whats Running Now)

    Step ID 1

    Server AGV-SQLLAB

    Job Name DBA-SQL Monitor - Report Whats Running Now

    Step Name Run the Report

    Duration 00:00:00

    Sql Severity 16

    Sql Message ID 4701

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted 0

    Message

    Executed as user: NT AUTHORITY\LOCAL SERVICE. Cannot find the object "SP_Who_Results" because it does not exist or you do not have permissions. [SQLSTATE 42S02] (Error 4701)Unable to open Step output file. The step failed.

    Can you shed light on this for this newbie! 🙂

  • sqlpanther

    Ten Centuries

    Points: 1204

    --clean up sp_who

    --here uneed to add USE [SQL_Monitoring] else it will search under master DB by default

    use [SQL_Monitoring]

    Truncate table [SP_Who_Results]

    --run SP_Who

    --same for insert

    use [SQL_Monitoring]

    Insert into [SP_Who_Results]

Viewing 15 posts - 16 through 30 (of 34 total)

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