Automating SQL Server Health Check (SQL Server 2005)

Question

Automating SQL Server Health Check (SQL Server 2005)

Viewing 15 posts - 61 through 75 (of 140 total)

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

Lindsay Stuckey SSC Enthusiast Points: 114 More actions · Answer 1

For those that are interested in the length of time it takes to run. I shortened the length of time by changing the line that calculates the start and end dates. Also I modified the section to exclude logfile details and finally only display the individual lines for the backup locations not the individual files.

Cheers!

nplace6530 Ten Centuries Points: 1050 More actions · Answer 2

Hi Mike D

run drop table #jobs_status

The re run the procedure. That should sort out your issue.

david.beechum SSC Enthusiast Points: 169 More actions · Answer 3

If this procedure seems to run forever for you then I have a solution. The below section of the sp uses a date range that when left as the default date range gave me a one month period that returned just over 30,000 rows. I changed line 138 to the following: SELECT @StartDate = DATEADD("d",-1,GETDATE()) and the sp finished in about 15 seconds.

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

steeled SSC Enthusiast Points: 178 More actions · Answer 4

Has anyone had problems with the email format in Outlook 2007? Mine si all jacked up, but looks OK when sent to my gmail account.

david.beechum SSC Enthusiast Points: 169 More actions · Answer 5

Yes the format is messed up for me too on Outlook 2007, I think there is a closing tag missing somewhere, maybe a </td>. I haven't had time to look through the html tags.

TaGen Valued Member Points: 57 More actions · Answer 6

[font="Times New Roman"]Hi Ritesh,

I implemented these automation on our servers and very happy with it.

I got this message:

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

An INSERT EXEC statement cannot be nested.

Mail queued.

Getting e-mail with no problem, even the job status failed.[/font]

timothy.shawley SSC Veteran Points: 228 More actions · Answer 7

Linda,

Set the on fail to either proceed to next statement or to exit reporting success in the Job and it will work even with the annoying notice that is popping up.

timothy.shawley SSC Veteran Points: 228 More actions · Answer 8

I found that the initial tags for end table </table> after the first couple sections (Job Status, Databases, Disk Stats) was missing. Also, the header's for most of the sections where showing up in the same sell as the last column headings. I moved the headers to above the table definintions and that solved those issues for me.

john.shurey SSC Rookie Points: 33 More actions · Answer 9

Do you have the updated script to correct the headers within Outlook? Thanks

timothy.shawley SSC Veteran Points: 228 More actions · Answer 10

Here is the copy of the original script that I edited to fix the Section headers. (This does work in SS 2008)

Due to forum restrictions I had to rename the SQL file as a .TXT file....

SQLRNNR SSC Guru Points: 281324 More actions · Answer 11

This is a nice concept. It could be useful to help save money or help a DBA get an idea to create a health check of his/her own.

Thanks for the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Bryan Conlon SSC Journeyman Points: 88 More actions · Answer 12

Getting this when i run this code:

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

An INSERT EXEC statement cannot be nested.

Mail queued.

Any thoughts?

phreeze Valued Member Points: 63 More actions · Answer 13

HI Ritesh,

very nice article....got over the couple of minor issues(email format issue and that nested thingy..)..

well done and thank you.

arr.nagaraj SSCertifiable Points: 6518 More actions · Answer 14

@rithesh,

Thanks. If i mananger to find smthng on CPU, i will post it. 🙂

@Omprakash K Deshpande,

SQL Alert helps monitoring fatal errors and SQL counters. CPU % falls under

windows counter and hence cant be monitored using SQL Alert.

Regards,
Raj

http://Strictlysql.blogspot.com

adjas SSC Enthusiast Points: 136 More actions · Answer 15

Hello,

I customised the script for our company usage fiddling a bit with HTML.

Also updated the script to exclude the nesting error.

Seems to be working fine, thank you guys.

It can definitely by extended and I will try.

So far I managed to created two jobs on two different SQL instances and it works.

You simply create the SP and the job on each instance and run the SP from the job with your SQL box IP address. It shows databases and the rest from the correct instance.

There are third-party apps doing similar think but it's nice to be able to receive an email just to remind you about your tasks when busy with something else

Cheers