Automating SQL Server Health Check (SQL Server 2005)

Question

Automating SQL Server Health Check (SQL Server 2005)

Viewing 15 posts - 91 through 105 (of 140 total)

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

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 1

Hey kinzent, I agree with you, its really dangerous allowing exection of any command line from SQL and thats the reason why SS2005 have disabled cmdshell by default. However its totally DBAs call if you dont want the last section then you can comment the code which runs cmdshell.... for me its was really imp to have physical file information

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 2

Vote Of Thanks!!!!!!!

Thanks for all the posts. I really appretiate and I hope that this article will be helpful for DBAs and make their life little easy.

Special thanks (For making my solution perfect and fixing the bugs :))

@Mark-1022992:

For pointing out

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

exec uspEmailSQLServerHealth '10.10.10.10', 'MYProject', 'myself@mycompany.com', 'TestMailProfile'

@SRosewarne-795472: For recommending table vairable.

@nplace6530: For fixing

An INSERT EXEC statement cannot be nested

@david.beechum: reducing the backup stats range from 1 month to one day this has certainly improved the performance.

@timothy.shawley: For fixing the HTML tags issue.

Thanks a lot guys.......

I have also added this post at the begining as you guys deserve the credit.....

nplace6530 Ten Centuries Points: 1050 More actions · Answer 3

Hi All

I 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: ' + @URL

With:

select @STR = 'EXEC Master.dbo.xp_DirTree "' + @URL + '",1,1'

INSERT #dirpaths SELECT 'PATH: ' + @URL, 1, 1

This 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.

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 4

Ritesh Medhe

SSCommitted

Points: 1941

February 2, 2010 at 10:28 pm

#1112641

nplace, thx this is awesome...

philcart SSC-Forever Points: 47794 More actions · Answer 5

nplace6530 (2/2/2010)
Hi All
...
With:
select @STR = 'EXEC Master.dbo.xp_DirTree "' + @URL + '",1,1'
INSERT #dirpaths SELECT 'PATH: ' + @URL, 1, 1
This 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.

Not really a good solution to replace one potentially dangerous option with another.

If the filename from msdb.dbo.backupmediafamily isn't sufficient and you need to retrieve the file names directly from the disk, how about running a short batch file using Windows Task Scheduler.

Something like,

DIR G:\MyBackups /B/O:D > C:\backup_file_list.txt

OSQL -E -S. -Q"delete from MyDB.dbo.backup_file_list"

BCP MyDB.dbo.backup_file_list in "C:\backup_file_list" -c -T

Then you have the list of physical files there ready for your "health check" procedure.

--------------------
Colt 45 - the original point and click interface

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

@phil,

OSQL/SQLCMD is n't a gr8 option either. OSQL marked depreciation and replaced by SQLCMD.

I would like to know the reason why u say its dangerous to use xp_dirtree (

other than it being undocumented )

IMO, I prefer dirtree to xp_cmdshell as enabling it allows someone to execute any DOS cmd. As Health check scripts are used only by DBAs undocumented stuffare not a huge pblm.

Regards,
Raj

http://Strictlysql.blogspot.com

philcart SSC-Forever Points: 47794 More actions · Answer 7

As Health check scripts are used only by DBAs undocumented stuffare not a huge pblm.

Unfortunately, as evidenced by a number of replies to this article, its not only executed by DBAs. In this day and age, it unfirtunate that more and more non-DBAs are being asked to look after databases with no real training at all.

Yes, you can replace OSQL in my example with SQLCMD. It will basically be the same thing. For an even more up-to-date approach, you could use a Powershell script in a SQL Agent job.

--------------------
Colt 45 - the original point and click interface

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

philcart,

The idea is if a health check script fails(becos of undocumented function), applications dont crash and users dont complain. But if a server crashes or data loss occurs because of hack attack using xp_cmdshell then people seriously complain and the DBA is in a soup.

So I prefer xp_dirtree to xp_cmdshell. If you have any specific reason to differ please explain.

Regards,

Raj

Regards,
Raj

http://Strictlysql.blogspot.com

nplace6530 Ten Centuries Points: 1050 More actions · Answer 9

The xp_dirtree function was only added in SQL2005 so it’s not likely to disappear and will probably be around for quite a while.

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

@nplace6530,

Yes true. Along with their longevity, the scary part about undocumented stuff

is their behavior after a patch installation/hot fix update which alter system tables and hence can change the behavior of undocumented sp. MS needn't announce it as its Undocumented.To me Undocumented is acceptable on a health check script but not on application code

Regards,
Raj

http://Strictlysql.blogspot.com

philcart SSC-Forever Points: 47794 More actions · Answer 11

nplace6530 (2/3/2010)
The xp_dirtree function was only added in SQL2005 so it’s not likely to disappear and will probably be around for quite a while.

Actually xp_dirtree has been around for a while, probably as long as xp_cmdshell.

With the push towards Powershell and CLR, expect a lot, read all, of these extended stored procedures to be phased out.

My preference is to never use undocumented procedures when a documented alternative is available.

--------------------
Colt 45 - the original point and click interface

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

Hi Phil,

/* With the push towards Powershell and CLR, expect a lot, read all, of these extended stored procedures to be phased out. */

Powershell/CLR can be a good alternative for a undocumented option. Would be gr8 if you could

elaborate how CLR/Powershell would solve the current issue. As already a few folks have started using this solution, they would be benifit by your response 🙂

/*

My preference is to never use undocumented procedures when a documented alternative is available.

*/

Agreed again. But under the current circumstances, we wanted to compare xp_cmdshell and xp_dirtree

and clearly realise the issues attached with xp_cmdshell.

Regards,

Raj

Regards,
Raj

http://Strictlysql.blogspot.com

philcart SSC-Forever Points: 47794 More actions · Answer 13

A selection of examples

http://www.sqlservercentral.com/articles/powershell/65324/[/url]

http://www.simple-talk.com/sql/database-administration/why-this-sql-server-dba-is-learning-powershell/[/url]

http://itknowledgeexchange.techtarget.com/dba/powershell-sql-server-health-check-script/[/url]

--------------------
Colt 45 - the original point and click interface

suyog007_extc Grasshopper Points: 14 More actions · Answer 14

hi Ritesh,

Thanks for the query... its very useful for DBA..

i have one doubt..

how to send the output of query as an attachment.

i use

exec msdb.dbo.sp_send_dbmail

@profile_name = 'test',

@recipients = 'suyog.pagare@gmail.com',

@query = 'exec uspEmailSQLServerHealth10 '192.168.1.120','abc','suyog.pagare@gmail.com;;','test','MYSELF'',

',

@subject = 'Work Order Count',

@attach_query_result_as_file = 1 ;

but i got one error saying

incorrect syntax near '192.168'

i have put server name instead of IP but its not working.

please help me out with some resolutions...

my basic requirement is to send the output to outlook mail.

philcart SSC-Forever Points: 47794 More actions · Answer 15

You need to use two single quotes

eg:

@query = 'exec uspEmailSQLServerHealth10 ''192.168.1.120'', ''abc'', ''suyog.pagare@gmail.com;;'', ''test'', ''MYSELF''',

If you're editing the SQL statement in Management Studio the entire value for the @query parameter should be read to indicate a string (the default coloring).

--------------------
Colt 45 - the original point and click interface