Seven Monitoring Scripts

Question

Post reply

Seven Monitoring Scripts

IsaacGoGo

SSC Eights!

Points: 944
More actions
June 11, 2003 at 12:00 am

#160376

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/sevenmonitoringscripts.asp

Viewing 15 posts - 1 through 15 (of 15 total)

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

pgw34 SSC Veteran Points: 228 More actions · Answer 1

Joseph's tips are always excellent but practicle as well (I would also recommend his book SQL 2000 Fast Answers)

paul warwick

IsaacGoGo SSC Eights! Points: 944 More actions · Answer 2

Just an addendum to my article - for the

"INSERT #Errors EXEC xp_readerrorlog", this extended proc is in the master database... So change it to:

INSERT #Errors EXEC master..xp_readerrorlog

Also - remember to drop the temp table.

DROP TABLE #Errors

Thanks!

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Very nice scripts. Need some modifications for me, but these are handy to have around.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

tulcanla SSCommitted Points: 1541 More actions · Answer 4

Thank you for all

but the coolest one is the SQL log script

🙂

icata Hall of Fame Points: 3176 More actions · Answer 5

For the script that is looking for jobs still running I think an easyer script will be

msdb.dbo.sp_get_composite_job_info @execution_status = 1

since the rest of parameters has a default NULL.

Ionel

byauchler Valued Member Points: 54 More actions · Answer 6

This is a great article. My only suggestion would be to use master..xp_readerrorlog in the script in tip # 7.

Thanks,

Biva

philcart SSC-Forever Points: 47794 More actions · Answer 7

Just what I was looking for to drive my ASP status report.

One other tip for #7, I modified the SELECT statement to be like this,



SELECT TOP 50

CONVERT(datetime, LEFT(vchMsg, 23), 121) as MsgDate

, SUBSTRING(vchMsg, 24, 10) as MsgSource

, SUBSTRING(vchMsg, 33, 800) as MsgText

FROM #Tmp 

WHERE vchMsg NOT LIKE '%Log backed up%'

AND vchMsg NOT LIKE '%.TRN%'

AND vchMsg NOT LIKE '%Database backed up%'

AND vchMsg NOT LIKE '%.BAK%'

AND vchMsg NOT LIKE '%Run the RECONFIGURE%'

AND vchMsg NOT LIKE '%Copyright (c)%' 

AND LTRIM(RTRIM(LEFT(vchMsg, 1))) <> CHAR(9)

AND ID = 0

ORDER BY 

CONVERT(datetime, LEFT(vchMsg, 23), 121) DESC

Now it shows the date, source and message text in seperate fields. The TOP 50 and ORDER BY I put in so that only the last 50 messages are displayed.

I'd like to improve it further by using the either the OPENROWSET or OPENQUERY statements to execute the xp_readerrorlog procedure. This would get rid of the temp table and I could put this in a stored procedure on one server and dynamically retrieve the error logs from servers that aren't linked.

The alternative to the stored procedure approach would be to open a database connection on the ASP page and dynamically build and execute the SQL statement there. Not my preferred option.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

Edited by - phillcart on 09/14/2003 8:02:15 PM

Edited by - phillcart on 09/14/2003 8:03:56 PM

--------------------
Colt 45 - the original point and click interface

Mike Good SSCertifiable Points: 7415 More actions · Answer 8

Good stuff. To para #3 I would add "and unscheduled jobs."

I've been burned by someone else either removing or disabling the schedule of a regularly-scheduled job. At first glance you say "it's still enabled, why didn't it run?" There's no easy way to check if the schedule was deleted, but it is easy to see if the schedule is disabled.

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 9

I am going to add some of these into my daily reports. And yes, I do have co-workers that slip in new sysadmin users! At least this way I'll know it right away.

Any good scripts for monitoring replication status?

Regards,

Scott