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, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
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 2, 2008 4:31 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,308, Visits: 1,378
Excellent article. Thanks for the resources provided in the end. :)


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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 12:21 PM
Points: 17, Visits: 192
The zip files seems to be unavailable.
Post #509849
Posted Monday, June 2, 2008 8:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:25 AM
Points: 1,262, Visits: 3,419
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 2, 2008 8:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
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 2, 2008 2:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

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

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

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
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 3, 2008 4:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 7, Visits: 120
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 3, 2008 10:18 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
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