Automating SQL Server Health Check (SQL Server 2005)

Question

Post reply

Automating SQL Server Health Check (SQL Server 2005)

Viewing 15 posts - 76 through 90 (of 140 total)

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

kinzent SSChasing Mays Points: 621 More actions · Answer 1

How much time it will take?

when I run on my personal DB,it completed in one minutes

but when i run on own product db,it took a long time ?

Who can tell me the reason~:-)

---------------------------------------
Thorn Bird...

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 2

Ritesh Medhe

SSCommitted

Points: 1941

January 28, 2010 at 10:42 pm

#1110460

hey nplace6530 thx a lot for resolvinf this issue.

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 3

@timothy.shawley thx for this script many of them had formating issue with outlook 2007. all looks good now 🙂

ERIC CRUDELI SSCommitted Points: 1786 More actions · Answer 4

Hi,

I tried to populate a temp table via INSERT #jobs_status EXEC msdb.dbo.sp_help_job

but I have the following message :

"Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67

An INSERT EXEC statement cannot be nested. "

Any idea ???

Cheers,

Eric

PS : The temp Table was populated

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 5

eric, this error can be ignore as it will not affect the output.

one of the posts by nplace6530 has suggested replacing

INSERT #jobs_status EXEC msdb.dbo.sp_help_job

with

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

on sjs.job_id = sj.job_id

nplace6530 Ten Centuries Points: 1050 More actions · Answer 6

Hi Ritesh

I forgot to say in my pervious post, thanks for this script. It was exactly what I was looking for and saved me quite a bit of work.

Many thanks!!!!

Nick.

chava.sreekanth SSC Journeyman Points: 98 More actions · Answer 7

Hey guys,

Require your help, While I am executing on the local server the script is running fine.

But when I try to execue the script on client production systems it is executing for indefinite time and when I forcefully stop execution I get the below error.....How to fix it.

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

An INSERT EXEC statement cannot be nested.

Query was cancelled by user.

"

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 8

you are welcome nplace6530 and I am happy to hear that its helping people 🙂

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 9

@chava.sreekanth: many others also reported performance issue. I guess its taking long time in section 6.. I will send the script excluding section 6 in a while try it and let me know

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 10

@chava.sreekanth: try this script, should work.... i read post where the issues was fixed by reducing the daterange for section 5. Section 5 returns backup history for 1 month. this version will return only 1 day histry...

chava.sreekanth SSC Journeyman Points: 98 More actions · Answer 11

Thank you,

I have executed the script, It is in the process of executing.....I will let you know once it gets executed.

But what is the estimated execution period?

Does it depend on no. of databases and their sizes?

Sreekanth

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 12

its really difficult to estimate exact execution time. it depends on lots of factors cpu, memory, number of jobs, databases... however if it is taking more than 30 seconds then it needs tuning . let me know ur server specs and time it took for execution

chava.sreekanth SSC Journeyman Points: 98 More actions · Answer 13

Thanks,

The provided Script has been executed.... it has taken 10 minutes approximate time.

But with the following warning :-

"Null value is eliminated by an aggregate or other SET operation"

What does it mean??

Execution time depends on what ??

chubbsm SSCommitted Points: 1770 More actions · Answer 14

I'm having problem w/2005 procedure this is executing. Haven't tried your updated code yet!!

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

An INSERT EXEC statement cannot be nested.

kinzent SSChasing Mays Points: 621 More actions · Answer 15

I don't think it is a good idea to use the xp_cmdshell to get the result on production DB.

It is other ways to achive the same reuslt?

A kind job by Ritesh Medhe,~ thx 🙂

---------------------------------------
Thorn Bird...