July 23, 2013 at 7:13 pm
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
July 24, 2013 at 4:11 am
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...
July 24, 2013 at 4:12 am
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...
July 24, 2013 at 5:11 am
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.
July 24, 2013 at 6:07 am
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
July 24, 2013 at 6:09 am
@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
July 24, 2013 at 1:25 pm
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
July 24, 2013 at 1:36 pm
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
July 24, 2013 at 1:55 pm
Thanks Kim!
July 24, 2013 at 2:27 pm
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.
July 25, 2013 at 10:29 am
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
July 25, 2013 at 10:33 am
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..
July 25, 2013 at 11:00 am
Thank you for the reply.
yes, I understand that now and am now getting:
Date7/25/2013 1:42:50 PM
LogJob History (DBA-SQL Monitor - Report Whats Running Now)
Step ID1
ServerAGV-SQLLAB
Job NameDBA-SQL Monitor - Report Whats Running Now
Step NameRun the Report
Duration00:00:00
Sql Severity16
Sql Message ID4701
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
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! 🙂
July 25, 2013 at 11:00 am
Thank you for the reply.
yes, I understand that now and am now getting:
Date7/25/2013 1:42:50 PM
LogJob History (DBA-SQL Monitor - Report Whats Running Now)
Step ID1
ServerAGV-SQLLAB
Job NameDBA-SQL Monitor - Report Whats Running Now
Step NameRun the Report
Duration00:00:00
Sql Severity16
Sql Message ID4701
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
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! 🙂
July 25, 2013 at 11:10 am
--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 33 total)
You must be logged in to reply to this topic. Login to reply