Automating SQL Server Health Check (SQL Server 2005)
-
nplace6530 (2/2/2010)
Hi AllI was also a little concerned about turning on cmdshell so I have made the following changes to the original script which shows backup file data but does not use cmdshell.
Replace:
CREATE TABLE #dirpaths(files VARCHAR(2000))With:
CREATE TABLE #dirpaths(files nvarchar(2000),Depth int,IsFile bit)And replace:
SELECT @STR = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D'''INSERT #dirpaths SELECT 'PATH: ' + @URLWith:
select @STR = 'EXEC Master.dbo.xp_DirTree "' + @URL + '",1,1'INSERT #dirpaths SELECT 'PATH: ' + @URL, 1, 1This replaces the "EXEC master.dbo.xp_cmdshell" command with the undocumented "EXEC Master.dbo.xp_DirTree" command.
No need to switch on the potentially dangerous xp_cmdshell option.
First off, thanks for the code. It is amazing and it will help me greatly if i resolve this issue.
The code was working fine but for security reasons I needed to turnoff xp_cmdshell on production environment. Then I tried the work around above and I replaced both segments of the code but i couldnt get it working to return the Physical Backup Files . However when I only commented out SELECT @STR = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D''' and it just work fine. Was it supposed to work eventhough i commented out this part of the code? I checked again and xp_cmdshell is still turned off. Can I then assume it is safe and continue using it on production environment safely?
Please help.
Thanks,
-
@arr.nagaraj i will work on CPU thing
CPU used by SQL Server can be found from sys.dm_os_ring_buffers.
Refer http://strictlysql.blogspot.com/2010/06/finding-cpu-utilization-in-sql-server.html%5B/url%5D
Regards,
http://Strictlysql.blogspot.com
Raj -
@raj thats excellent and thank you so much for posting on this article
-
-
Hi all,
just a quick hint concerning the change of the proc using xp_dirtree instead of xp_cmdshell.
You have to modify the string
INSERT #dirpaths values('')to
INSERT #dirpaths values('','','')Otherwise you get an error when inserting the blank row into #dirpaths (got it by myself on a SQL 2008)
Regards
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter -
Is there a SQL2000 version of this SP?
...thanks
-
Hello,
I'm not a expert but there something tricky because I didn't get the right execution time for jobs.
First I replaced "INSERT #jobs_status EXEC msdb.dbo.sp_help_job" by
"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 "
It works but I thing there are a problem with this part of script :
"Datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes"
Why did you use "last_executed_step_date" and not "start_execution_date" to calculate the execution time of job.
I executed this script to check I get a different result but when I compared with the history of jobs I got the right result :
select
A.job_id,
A.start_execution_date,
A.stop_execution_date,
datediff(mi, A.start_execution_date, A.stop_execution_date) execution_time_minutes
from msdb..sysjobactivity A
Could you help me ?
-
Find it useful. Many Thanks!
Roy
-
Has there been a fix for the error below?
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.
-
I have to take a look on monday when I´m back in office.
Also had the error in the beginning. Just changed a variable or column of a temp table since it was too small.
That fixed it for me.
Cannot remember right now where the problem exactly was
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter -
How can i declared a variable at the beginning of the stored procedure, @TableHTML ?
-
Sorry Christopher,
I completely forgot to look after the modification to get rid of the overflow error.
The only difference I have found is in line 330 (depending on your editor of choice).
It's the
select ...from sys.databases MSTinner join (select b.name [LOG_DBNAME],CONVERT(DECIMAL(10,2), sum....Here I have a DECIMAL(20,2).
Afterwards the script worked fine for me.
christopher.jones 39322 (11/18/2011)
Has there been a fix for the error below?Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter -
What exactly do you need or in which part of the sproc do you want to declare an additional variable?
alnawrass2002 (4/8/2012)
How can i declared a variable at the beginning of the stored procedure, @TableHTML ?--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter -
Dirk.Hondong (4/10/2012)
What exactly do you need or in which part of the sproc do you want to declare an additional variable?alnawrass2002 (4/8/2012)
How can i declared a variable at the beginning of the stored procedure, @TableHTML ?Its mentioned in the article: " I have declared a variable at the beginning of the stored procedure, @TableHTML, which gets built and then executed at the end before it sends an e-mail".
What the code the declare the same?
Regards
-
Where is the .sql File. Also, i do not see SP code for TableHTML
Viewing 15 posts - 106 through 120 (of 140 total)
You must be logged in to reply to this topic. Login to reply