SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Seven Monitoring Scripts


Seven Monitoring Scripts

Author
Message
IsaacGoGo
IsaacGoGo
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 51
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/sevenmonitoringscripts.asp



pgw34
pgw34
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 163
Joseph's tips are always excellent but practicle as well (I would also recommend his book SQL 2000 Fast Answers)

paul warwick


paul warwick
IsaacGoGo
IsaacGoGo
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 51
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
Steve Jones
SSC Guru
SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)SSC Guru (282K reputation)

Group: Administrators
Points: 282118 Visits: 19906
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
www.dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
tulcanla
tulcanla
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 1
Thank you for all
but the coolest one is the SQL log script
Smile



icata
icata
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 295
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
byauchler
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
This is a great article. My only suggestion would be to use master..xp_readerrorlog in the script in tip # 7.

Thanks,
Biva



philcart
philcart
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18567 Visits: 1441
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

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Mike Good
Mike Good
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3268 Visits: 1078

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
Scott Arendt
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5269 Visits: 1733

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search