Automating SQL Server Health Check (SQL Server 2005)

Question

Post reply

Automating SQL Server Health Check (SQL Server 2005)

Viewing 15 posts - 121 through 135 (of 140 total)

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

Dirk.Hondong Ten Centuries Points: 1055 More actions · Answer 1

Look at the very 1st post.

There is an attachment with the script.

It has an .txt ending.

Regarding the code:

Inside the whole SP, there´s one variable called @TableHTML which will be used to generate the HTML code for the Email Body.

-- 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)

Regards

Dirk

--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter

glamourth SSC Enthusiast Points: 164 More actions · Answer 2

Hi All,

Your the scripts is awesome..!!

Can you guide how this can be modified to get the same information for multiple servers i.e running this script in one server that will provide information for all server of the same domain...

I will be glad to get guidance on this...

Thanks.!

Dirk.Hondong Ten Centuries Points: 1055 More actions · Answer 3

I never tried it in this way...

What I did to run the solution:

Implemented the proc and a corresponding sql srv job on each server.

Now I get from every server a report in the morning. With the help of a filter rule I highlight those emails which contain a failed or false error message

If you need to gather the informations first, then I would recommend that you take a look at Rodney Landrum's Tacklebox. [/url]

There's an example how to build a repository, get information from multiple servers via a SSIS package and afterwards run reports against these information.

--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter

glamourth SSC Enthusiast Points: 164 More actions · Answer 4

Thanks a lot Dirk for your quick response..!!

Info provided is really good but am looking for a such a automating scripts that should be running on one server and retrieves the SQL service's status for all servers in domain.

Benefit of it would be that one server will act something like "Monitor server". Am a core SQL server DBA and do not have much knowledge on t-sql programming part...

Let me know if you get some info on the same.

Thanks,

Dirk.Hondong Ten Centuries Points: 1055 More actions · Answer 5

Hi there,

it´s the same on my side.

I look out for useful scripts, tools etc and adapt them.

The repository mentioned in the tacklebox is running on one server in the company I work for and gathers information from more than 200 productive instances.

So the only thing I did was to enter all servers in on of the tables of the repository. That´s the "source" for the ssis package which then connects to each server indiviudally and gathers the information.

Quite easy if you can setup up some kind of service account which has access to all your servers/instances.

This account is also the proxy account on the server which is running the ssis package.

To set it up properly and let it run in my environment it took me about 3-4 hours and now works like a charm.

Regards and have a great weekend

Dirk

--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter

alnawrass2002 SSC Veteran Points: 247 More actions · Answer 6

Dirk.Hondong (4/24/2012)
Look at the very 1st post.
There is an attachment with the script.
It has an .txt ending.
Regarding the code:
Inside the whole SP, there´s one variable called @TableHTML which will be used to generate the HTML code for the Email Body.
-- 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)
Regards
Dirk

Dear Dirk

I cant find any txt file attached in this post,

but i copy and paste your code there then i got the following error msg:

Must declare the scalar variable "@ServerIP".

Msg 105, Level 15, State 1, Line 16

Unclosed quotation mark after the character string '</td>

</tr>

</table&gt

alnawrass2002 SSC Veteran Points: 247 More actions · Answer 7

and this is the code which i use:

-- 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),

---------------------------- Section A ------------------------------

SET @TableHTML =

'Server Info

<tr>

Server IP</td>

Server Name</td>

Project/Client</td>

</tr>

---------------------------- Section B ------------------------------

<tr>

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

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

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

</tr>

</table>

Dirk.Hondong Ten Centuries Points: 1055 More actions · Answer 8

The very first post has a link in it´s footer:

http://www.sqlservercentral.com/Forums/Attachment5016.aspx

--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter

manus4u Ten Centuries Points: 1238 More actions · Answer 9

Thanks for the script...

I did some modifications as per my requirement and it works like a charm.

But, when i scheduled this as a job i am getting a blank report. but if i run the same code in the job in query window, i get the html output.

I tried running the job myself manually, still same result.

Any hints?

Thanks

MvM

nimmagaddakrupanandh SSC Journeyman Points: 77 More actions · Answer 10

Hi This is krupanandh.N working as a sql dataabase Administrator in spi mysore.

i have scheduled sp "dbo.uspEmailSQLServerHealth" daily triggered at @9 am .

But The job is failing but iam getting reports.

when iam executed this job iam geting the following error.

Message

Executed as user: domain\user An INSERT EXEC statement cannot be nested. [SQLSTATE 42000] (Error 8164). The step failed.

any one help me on this.

Thanks

krupanandh.N

cletocs Newbie Points: 5 More actions · Answer 11

Thanks for the great script, but I want to save the output to servername.html

yasirbam SSC Veteran Points: 208 More actions · Answer 12

yasirbam

SSC Veteran

Points: 208

November 5, 2013 at 7:39 am

#1664143

Great Article Ritesh thanks

Yasir 😉

rv-1116394 Newbie Points: 7 More actions · Answer 13

Hi there.

I have tried this script on both SQL Server 2008 and 2008 R2 and I get an error when executing the procedure

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72

An INSERT EXEC statement cannot be nested.

Any suggestions?

Regards Robert

ighiwiyisii Newbie Points: 7 More actions · Answer 14

Thanks for the post, for me it was perfect, I have taken what I need and added to it. Its helping us with our morning checks and is a good little email to look at when you're on-call to pre-empt any issues

forsqlserver SSCoach Points: 18902 More actions · Answer 15

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