Automating SQL Server Health Check (SQL Server 2005)

Question

Post reply

Automating SQL Server Health Check (SQL Server 2005)

Viewing 6 posts - 136 through 140 (of 140 total)

You must be logged in to reply to this topic. Login to reply

forsqlserver SSCoach Points: 18902 More actions · Answer 1

For Me its working fine 2012 but on 2008 its running and running only.

Script is:

USE [msdb]

GO

/****** Object: StoredProcedure [dbo].[uspEmailSQLServerHealth] Script Date: 01/28/2010 15:15:27 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspEmailSQLServerHealth]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[uspEmailSQLServerHealth]

GO

USE [msdb]

GO

/****** Object: StoredProcedure [dbo].[uspEmailSQLServerHealth] Script Date: 01/28/2010 15:15:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--drop table #jobs_status

CREATE PROCEDURE [dbo].[uspEmailSQLServerHealth]

(

@ServerIP VARCHAR(100), -- SQL Server 2005 Database Server IP Address

@Project VARCHAR(100), -- Name of project or cleint

@Recepients VARCHAR(2000), -- Recepient(s) of this email (; separated in case of multiple recepients).

@MailProfile VARCHAR(100), -- Mail profile name which exists on the target database server

@Owner VARCHAR(200) -- Owner, basically name/email of the DBA responsible for the server

)

/*

exec EmailSQLServerHealth '10.10.10.10', 'MYProject', 'myself@mycompany.com', 'TestMailProfile', 'My Self'

*/

AS

BEGIN

SET NOCOUNT ON

/* Drop all the temp tables(not necessary at all as local temp tables get dropped as soon as session is released,

however, good to follow this practice). */

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#jobs_status')

BEGIN

DROP TABLE #jobs_status

END

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#diskspace')

BEGIN

DROP TABLE #diskspace

END

IF EXISTS (SELECT NAME FROM sysobjects WHERE name = '#url')

BEGIN

DROP TABLE #url

END

IF EXISTS (SELECT NAME FROM sysobjects WHERE name = '#dirpaths')

BEGIN

DROP TABLE #dirpaths

END

-- Create the temp tables which will be used to hold the data.

CREATE TABLE #url

(

idd INT IDENTITY (1,1),

url VARCHAR(1000)

)

CREATE TABLE #dirpaths

(

files VARCHAR(2000)

)

CREATE TABLE #diskspace

(

drive VARCHAR(200),

diskspace INT

)

-- This table will hold data from sp_help_job (System sp in MSDB database)

/*CREATE TABLE #jobs_status

(

job_id UNIQUEIDENTIFIER,

originating_server NVARCHAR(30),

name SYSNAME,

enabled TINYINT,

description NVARCHAR(512),

start_step_id INT,

category SYSNAME,

owner SYSNAME,

notify_level_eventlog INT,

notify_level_email INT,

notify_level_netsend INT,

notify_level_page INT,

notify_email_operator SYSNAME,

notify_netsend_operator SYSNAME,

notify_page_operator SYSNAME,

delete_level INT,

date_created DATETIME,

date_modified DATETIME,

version_number INT,

last_run_date INT,

last_run_time INT,

last_run_outcome INT,

next_run_date INT,

next_run_time INT,

next_run_schedule_id INT,

current_execution_status INT,

current_execution_step SYSNAME,

current_retry_attempt INT,

has_step INT,

has_schedule INT,

has_target INT,

type INT

)

*/

-- To insert data in couple of temp tables created above.

INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives

--INSERT #jobs_status EXEC msdb.dbo.sp_help_job

select sj.job_id, sj.name, sc.name as Category, sj.Enabled, sjs.last_run_outcome,

(select max(run_date) from sysjobhistory sjh where sjh.job_id = sj.job_id) as last_run_date

into #jobs_status

from sysjobs sj

join syscategories sc

on sj.category_id = sc.category_id

join msdb.dbo.sysjobservers sjs

-- Variable declaration

DECLARE @TableHTML VARCHAR(MAX),

@StrSubject VARCHAR(100),

@Oriserver VARCHAR(100),

@Version VARCHAR(250),

@Edition VARCHAR(100),

@ISClustered VARCHAR(100),

@sp-2 VARCHAR(100),

@ServerCollation VARCHAR(100),

@SingleUser VARCHAR(5),

@LicenseType VARCHAR(100),

@StartDate DATETIME,

@EndDate DATETIME,

@Cnt int,

@URL varchar(1000),

@STR varchar(1000)

-- Variable Assignment

SELECT @Version = @@version

SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))

SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)

SELECT @StartDate = @StartDate - 1

SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd,

GETDATE() + 1)) AS DATETIME)

SET @Cnt = 0

IF serverproperty('IsClustered') = 0

BEGIN

SELECT @ISClustered = 'No'

END

ELSE

BEGIN

SELECT @ISClustered = 'YES'

END

SELECT @sp-2 = CONVERT(VARCHAR(100), SERVERPROPERTY ('productlevel'))

SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('Collation'))

SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType'))

SELECT @SingleUser = CASE SERVERPROPERTY ('IsSingleUser')

WHEN 1 THEN 'Yes'

WHEN 0 THEN 'No'

ELSE

'null' END

SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername'))

SELECT @strSubject = 'DB Server Daily Health Checks ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'

/*

Along with refrences to SQL Server System objects, You will also see lots of HTML code however do not worry,

Even though I am a primarily a SQL Server DBA, I am little fond of HTML,

so thought to show some of my HTML skills here :), trust me you would love to see the end product....

*/

SET @TableHTML =

'Server Info

<tr>

Server IP</td>

Server Name</td>

Project/Client</td>

</tr>

<tr>

<td width="27%" height="27">'+@ServerIP+'</td>

<td width="39%" height="27">' + @OriServer +'</td>

<td width="90%" height="27">'+@Project+'</td>

</tr>

</table>

<tr>

Version</td>

Edition</td>

Service Pack</td>

Collation</td>

LicenseType</td>

SingleUser</td>

Clustered</td>

</tr>

<tr>

<td width="50%" height="27">'+@version +'</td>

<td width="17%" height="27">'+@edition+'</td>

<td width="17%" height="27">'+@ServerCollation+'</td>

<td width="25%" height="27">'+@LicenseType+'</td>

<td width="25%" height="27">'+@SingleUser+'</td>

<td width="93%" height="27">'+@isclustered+'</td>

</tr>

</table>

Job Status

<tr>

Job Name</th>

Enabled</th>

Last Run</th>

Category</th>

Last Run Date</th>

Execution Time (Mi)</th>

</tr>'

SELECT

@TableHTML = @TableHTML + '<tr><td>' +

ISNULL(CONVERT(VARCHAR(100), A.name), '') +'</td>' +

CASE enabled

WHEN 0 THEN '<td bgcolor="#FFCC99">False</td>'

WHEN 1 THEN '<td>True</td>'

ELSE '<td>Unknown</td>'

END +

CASE last_run_outcome

WHEN 0 THEN '<td bgColor="#ff0000"><blink>

<a href="mailto:servicedesk@mycompany.com?subject=Job failure - ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101)

+'&cc=db.support@mycompany.com&body = SD please log this call to DB support,' + '%0A %0A' + '<<' + ISNULL(CONVERT(VARCHAR(100), name),'''') + '>> Job Failed on ' + @OriServer

+ '(' + @ServerIP + ')'+ '.' +'%0A%0A Regards,'+'">Failed</a></blink></td>'

WHEN 1 THEN '<td>Success</td>'

WHEN 3 THEN '<td>Cancelled</td>'

WHEN 5 THEN '<td>Unknown</td>'

ELSE '<td>Other</td>'

END +

'<td>' + ISNULL(CONVERT(VARCHAR(100), A.category),'') + '</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),'') + '</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),'') +'</td> </tr>'

FROM

#jobs_status A

inner join (

select

A.job_id,

datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes

from

msdb..sysjobactivity A

inner join (

select

max(session_id) sessionid,

job_id

from

msdb..sysjobactivity

group by

job_id

) B on a.job_id = B.job_id and a.session_id = b.sessionid

inner join (

select

distinct name,

job_id

from

msdb..sysjobs

) C on A.job_id = c.job_id

) X on A.job_id = X.job_id

ORDER BY

last_run_date DESC

SET @TableHTML = @TableHTML +

'</table>'

SELECT

@TableHTML = @TableHTML +

'

Databases

<tr>

Name</td>

CreatedDate</td>

DB Size(GB)</td>

State</td>

RecoveryModel</td>

</tr>'

select

@TableHTML = @TableHTML +

'<tr><td>' + ISNULL(name, '') +'</td>' +

'<td>' + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + '-' + CONVERT(VARCHAR

(4),DATEPART(yy,create_date)) +'</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), '') +'</td>' +

'<td>' + ISNULL(state_desc, '') +'</td>' +

'<td>' + ISNULL(recovery_model_desc, '') +'</td></tr>'

from

sys.databases MST

inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(10,2),(a.size * 8)) /1024)/1024) [Total Size GB]

from sys.sysaltfiles A

inner join sys.databases B on A.dbid = B.database_id

group by b.name)AA on AA.[LOG_DBNAME] = MST.name

order by

MST.name

SET @TableHTML = @TableHTML +

'</table>'

SELECT

@TableHTML = @TableHTML +

'

Disk Stats

<tr>

Disk</td>

Free Space (GB)</td>

</tr>'

SELECT

@TableHTML = @TableHTML +

'<tr><td>' + ISNULL(CONVERT(VARCHAR(100), drive), '') +'</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(100), ISNULL(CAST(CAST(diskspace AS DECIMAL(10,2))/1024 AS DECIMAL(10,2)), 0)),'') +'</td></tr>'

FROM

#diskspace

SELECT @TableHTML = @TableHTML + '</table>'

-- Code for SQL Server Database Backup Stats

SELECT

@TableHTML = @TableHTML +

'

SQL SERVER Database Backup Stats

<tr>

Date</th>

Database</th>

File Name</th>

Type</th>

Start Time</th>

End Time</th>

Size(GB)</th>

</tr>'

SELECT

@TableHTML = @TableHTML +

'<tr>

<td>' + ISNULL(CONVERT(VARCHAR(2), DATEPART(dd,MST.backup_start_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm, MST.backup_start_date)) +

'-' + CONVERT(VARCHAR(4), DATEPART(yyyy, MST.backup_start_date)),'') +'</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(100), MST.database_name), '') +'</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(100), MST.name), '') +'</td>' +

CASE Type

WHEN 'D' THEN '<td>' + 'Full' +'</td>'

WHEN 'I' THEN '<td>' + 'Differential' +'</td>'

WHEN 'L' THEN '<td>' + 'Log' +'</td>'

WHEN 'F' THEN '<td>' + 'File or Filegroup' +'</td>'

WHEN 'G' THEN '<td>' + 'File Differential' +'</td>'

WHEN 'P' THEN '<td>' + 'Partial' +'</td>'

WHEN 'Q' THEN '<td>' + 'Partial Differential' +'</td>'

ELSE '<td>' + 'Unknown' +'</td>'

END +

'<td>' + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), '') +'</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), '') +'</td>' +

'<td>' + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), '') +'</td>' +

'</tr>'

FROM

backupset MST

WHERE

MST.backup_start_date BETWEEN @StartDate AND @EndDate

ORDER BY

MST.backup_start_date DESC

SELECT @TableHTML = @TableHTML + '</table>'

-- Code for physical database backup file present on disk

INSERT #url

SELECT DISTINCT

SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) - CHARINDEX('\', REVERSE(BMF.physical_device_name), 0))

from

backupset MST

inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id

where

MST.backup_start_date BETWEEN @startdate AND @enddate

select @Cnt = COUNT(*) FROM #url

/*

WHILE @Cnt >0

BEGIN

SELECT @URL = url FROM #url WHERE idd = @Cnt

SELECT @STR = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D'''

INSERT #dirpaths SELECT 'PATH: ' + @URL

INSERT #dirpaths

EXEC (@Str)

INSERT #dirpaths values('')

SET @Cnt = @Cnt - 1

end

DELETE FROM #dirpaths WHERE files IS NULL

select

@TableHTML = @TableHTML +

'

Physical Backup Files

<tr>

Physical Files</th>

</tr>'

SELECT

@TableHTML = @TableHTML + '<tr>' +

CASE SUBSTRING(files, 1, 5)

WHEN 'PATH:' THEN '<td bgcolor = "#D7D7D7">' + files + '</td>'

ELSE

'<td>' + files + '</td>'

END +

'</tr>'

FROM

#dirpaths

SELECT

@TableHTML = @TableHTML + '</table>' +

'

Server Owner: '+@owner+'

Thanks

and Regards,

DB

Support Team

'

*/

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @MailProfile,

@recipients=@Recepients,

@subject = @strSubject,

@body = @TableHTML,

@body_format = 'HTML' ;

SET NOCOUNT OFF

END

GO

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 2

Hi Ritesh,

Could u pls help in running this script on sql server 2005 9.00.4060.00.

Its giving many errors.

your script is great.

Thanks

ravi.chinta Newbie Points: 7 More actions · Answer 3

Hi Ritesh,

Thanks a lot for the scripts. Worked perfectly.

Can i ask for a small help.

Is there a same way we can do for SQL 2000 servers?

Regards,

RK

cloepker SSC Rookie Points: 25 More actions · Answer 4

Dale Turley - Thursday, January 28, 2010 2:42 AM
First off - great article and a really handy stored procedure..When i executed the sp i got this error messageMsg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72An INSERT EXEC statement cannot be nested.Msg 242, Level 16, State 3, Procedure uspEmailSQLServerHealth, Line 126The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.It did send me an email though so all's good.

I also got the error telling me that I am not able to execute nested sprocs. Any help on this would be greatly appreciated!

Sue_H SSC Guru Points: 90860 More actions · Answer 5

cloepker - Friday, May 11, 2018 7:35 AM
Dale Turley - Thursday, January 28, 2010 2:42 AM
First off - great article and a really handy stored procedure..When i executed the sp i got this error messageMsg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72An INSERT EXEC statement cannot be nested.Msg 242, Level 16, State 3, Procedure uspEmailSQLServerHealth, Line 126The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.It did send me an email though so all's good.
I also got the error telling me that I am not able to execute nested sprocs. Any help on this would be greatly appreciated!

That poster DID post what the issue was. They said:

It is the language i was using - it was set to Brittish English
When i SET LANGUAGE 'English'
I didn't get the datetime conversion error and recived backup details in the email.

Sue

Abhishek SSCommitted Points: 1715 More actions · Answer 6

Hi Ritesh Medhe\ All

Thanks for the amazing script but i am unable to find the updated script you have mentioned in line #219986

Can you please help share ?

Automating SQL Server Health Check (SQL Server 2005)

Cookies on SQLServerCentral