Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Seven Monitoring Scripts Expand / Collapse
Author
Message
Posted Wednesday, June 11, 2003 12:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, Visits: 51
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jsack/sevenmonitoringscripts.asp


Post #13119
Posted Friday, June 13, 2003 1:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:19 AM
Points: 160, Visits: 161
Joseph's tips are always excellent but practicle as well (I would also recommend his book SQL 2000 Fast Answers)

paul warwick



paul warwick
Post #66214
Posted Friday, June 13, 2003 5:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:16 AM
Points: 24, 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!







Post #66215
Posted Friday, June 13, 2003 9:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:13 PM
Points: 33,100, Visits: 15,210
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
Post #66216
Posted Friday, June 13, 2003 2:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 6, 2004 11:03 AM
Points: 109, Visits: 1

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







Post #66217
Posted Wednesday, June 18, 2003 1:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:30 PM
Points: 229, Visits: 275
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





Post #66218
Posted Friday, September 12, 2003 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 1, 2005 9:51 AM
Points: 2, 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




Post #66219
Posted Sunday, September 14, 2003 8:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:06 PM
Points: 2,693, Visits: 1,202
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
Post #66220
Posted Monday, June 14, 2004 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 301, Visits: 792

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.




Post #120895
Posted Friday, May 13, 2005 7:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 3,447, Visits: 1,475

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

Post #182218
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse