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 123»»»

SQL Overview IV - DBA's Morning Review Expand / Collapse
Author
Message
Posted Saturday, May 31, 2008 5:05 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:24 AM
Points: 182, Visits: 996
Comments posted to this topic are about the item SQL Overview IV - DBA's Morning Review

David Bird

My PC Quick Reference Guide
Post #509530
Posted Monday, June 02, 2008 4:31 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711
Pretty good article and code... Well done!
Post #509734
Posted Monday, June 02, 2008 6:15 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,191, Visits: 1,368
Excellent article. Thanks for the resources provided in the end. :)


Post #509771
Posted Monday, June 02, 2008 7:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 1:34 PM
Points: 17, Visits: 178
The zip files seems to be unavailable.
Post #509849
Posted Monday, June 02, 2008 8:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
Very nice article

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #509888
Posted Monday, June 02, 2008 8:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:24 AM
Points: 182, Visits: 996
Download the zip package under the Resources heading.

The one under Additional Resources does not work. Sorry


David Bird

My PC Quick Reference Guide
Post #509897
Posted Monday, June 02, 2008 2:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 3:37 PM
Points: 5, Visits: 123
I was wondering what do you suggest for monitoring SQL job that are hanging or running non stop?
Post #510286
Posted Monday, June 02, 2008 2:55 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:24 AM
Points: 182, Visits: 996
That problem has bitten me more a than a few times. I have yet to develop an approach to handle all jobs. For database backups, missing a backup could be caused by the job hanging. You would be notified of missing backups using the reports in this article.

For a specific job, I have used the following SQL in a job on the job's instance to check if the job has finished within the last day.

IF NOT EXISTS
(select 1

from msdb.dbo.sysjobhistory jh inner join msdb.dbo.sysjobs j on j.job_id = jh.job_id
where DATEADD (ss, (jh.run_duration/10000 * 3600) +
(jh.run_duration/100 %100 * 60) +
jh.run_duration %100, +
CAST(CAST(jh.run_date AS char(8)) + ' ' + -- Convert run_date to DateTime data type
STUFF(STUFF( -- Insert : into Time
RIGHT('000000' + -- Add leading Zeros
CAST(jh.run_time AS varchar(6)) ,6) , 3, 0, ':'), 6, 0, ':') AS datetime) )
> dateadd (dd,-1,getdate())

and j.name = 'DBA-Job that sometimes Hangs'
and jh.step_id = 0
)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'me@company.com',
@subject = '*** ALERT - Archive SPT Logs Not Finished ****',
@body = 'Check SERVER and see why the job DBA-Job has not finished' ;

END

To monitor all jobs for this type of problem is a goal of mine that I have yet to tackle.




David Bird

My PC Quick Reference Guide
Post #510301
Posted Tuesday, June 03, 2008 4:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 8:56 PM
Points: 7, Visits: 102
great article!
But, I think some other points might need to be monitor,
for example, cpu usage, disk io, etc..; system event log, cluster log, etc..;
DB file growth, table space growth, etc..;
duration growthof jobs, index usage, health of data buffer, etc..

In addition, we can cycle errlog when finding errlog size is too big,
we can use logparser to filter the errorlog that is old history date before two days, to increase the ERRORLOG reading performance.
Post #510527
Posted Tuesday, June 03, 2008 10:18 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:24 AM
Points: 182, Visits: 996
Wyfccc thanks for some great ideas.

I will looking into the Log Parser tool but if its executable needs to be on each server. It may not be worth using on all servers.


David Bird

My PC Quick Reference Guide
Post #510821
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse