SQLServerCentral / Discuss Content Posted by Joseph Sack / Article Discussions / Article Discussions by Author / Seven Monitoring Scripts / Latest Posts

RE: Seven Monitoring Scripts

Dave May — Mon, 15 May 2006 06:16:00 GMT

Another tip for xp_readerrorlog -- the first parameter is an integer that determines which error log to read.

xp_readerrorlog -- current errorlog
xp_readerrorlog 1 -- most recent archived errorlog
xp_readerrorlog 2 -- etc.

Dave.

RE: Seven Monitoring Scripts

chadra kiran — Wed, 20 Jul 2005 05:48:00 GMT

sql monitoring tips are excelent. i expect more tips like that.

thax and regards

kiran

sql dba

RE: Seven Monitoring Scripts

Zman57 — Mon, 16 May 2005 08:13:00 GMT

These scripts are very useful.

RE: Seven Monitoring Scripts

growl — Mon, 16 May 2005 01:16:00 GMT

Smart and simple.

I also find using sysjobs_view useful in place of sysjobs. Especially if you are managing more than one server.

The extra colum originating_Server can tell which server the job failed on.

RE: Seven Monitoring Scripts

Roshan Patel — Sun, 15 May 2005 23:20:00 GMT

Excellent.

RE: Seven Monitoring Scripts

Vipul Shah — Fri, 13 May 2005 07:42:00 GMT

Great scripts. Very useful.

Thanks

Vipul

RE: Seven Monitoring Scripts

Scott Arendt — Fri, 13 May 2005 07:08:00 GMT

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

RE: Seven Monitoring Scripts

Mike Good — Mon, 14 Jun 2004 08:13:00 GMT

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.

RE: Seven Monitoring Scripts

philcart — Sun, 14 Sep 2003 20:00:00 GMT

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 MsgTextFROM #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 = 0ORDER 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 helpsPhill Carter--------------------Colt 45 - the original point and click interface Edited by - phillcart on 09/14/2003 8:02:15 PMEdited by - phillcart on 09/14/2003 8:03:56 PM

RE: Seven Monitoring Scripts

byauchler — Fri, 12 Sep 2003 11:07:00 GMT

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

RE: Seven Monitoring Scripts

icata — Wed, 18 Jun 2003 13:41:00 GMT

For the script that is looking for jobs still running I think an easyer script will bemsdb.dbo.sp_get_composite_job_info @execution_status = 1since the rest of parameters has a default NULL.Ionel

RE: Seven Monitoring Scripts

tulcanla — Fri, 13 Jun 2003 14:21:00 GMT

Thank you for all but the coolest one is the SQL log script:)

RE: Seven Monitoring Scripts

Steve Jones - SSC Editor — Fri, 13 Jun 2003 09:27:00 GMT

Very nice scripts. Need some modifications for me, but these are handy to have around.Steve Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjoneswww.dkranch.net

RE: Seven Monitoring Scripts

IsaacGoGo — Fri, 13 Jun 2003 05:43:00 GMT

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_readerrorlogAlso - remember to drop the temp table.DROP TABLE #ErrorsThanks!

RE: Seven Monitoring Scripts

PAUL WARWICK — Fri, 13 Jun 2003 01:51:00 GMT

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

Seven Monitoring Scripts

IsaacGoGo — Wed, 11 Jun 2003 00:00:00 GMT

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