Any one help me out the scripts for daily health checkup?
I am daily take do the health checkup for all servers by manually by using the following sp's and commands now I need to do automation for all my manual checkp... Daily doing: - sp_spaceused, - dbcc sqlperf(logspace), - jobs view history , - job activity monitor, - checking error logs, - xp_fixeddrives, - checking backups are deleted (cleanups)or not from drives as per the retention period, - sp_helpdb 'dbname', - event viewer checking, The above all I am doing daly for all servers So now I need to do automation for atleast some of them please share the scripts to me?
The trick is to run these through the SQL Agent. That's your automation engine. You should be able to output most of what you've been looking at to tables and then you can monitor the data that way. I'd also suggest setting up a server-side trace using TSQL Scripts (that you can generate out of Profiler) and using Dynamic Management Views instead of of some of the system tables & such. You can also set up Alerts through SQL Agent to have it email you when there are errors, so you shouldn't have to peruse the error logs. Look into setting up a [Central Management Server] too. Honestly though, you're better off picking up a 3rd party tool that does all this stuff for you. :
If you have no budget, then you could do worse than look at rolling your own with the help of a series of articles on SSIS by David Bird, available at SSC's main site: I've used this in the past to monitor 100+ instances successfully.
here is a custom option step 1. create a .sql file with all you queries in it step 2. create a batch file with the following command sqlcmd -E -S domainname\instancename -i test.sql -o Resulttest.csv step 3. schedule the this batch file windows schedule task, all the results of the script will be saved in Resulttest.csv file.
What @ramesh is suggesting is that you put all of your queries into a single .sql file; for example, we'll call it "C:\CheckSQLServer.sql" You also need a .cmd file that contains something like: sqlcmd -S Servername\Instancename -E -i C:\CheckSQLServer.sql -o C:\CheckSQLServerResults.txt The parameters used are: * -S - this is the server name & instance name, so could be something as simple as "." (indicating default instance on current host), or `Servername.domain\instancename` to go to a remote server. * -E - this tells `sqlcmd` to use "integrated security", ie to log into the SQL Server using the windows credentials of the process that calls `sqlcmd`. If it's you running a script, then it'll use your credentials; if it's running as a scheduled task, then it'll run under the scheduler's account (or you can tell it to use your details, or something else's info). * -i - this gives the name of a file containing SQL script to run * -o - the name of the file to contain the results. If you don't want to use Windows authentication, then you can use SQL authentication with the following parameters: * -U - SQL user ID * -P - password For more information on the SQLCMD parameters, go to a DOS command window and run sqlcmd -? Once you are satisfied that this is working, then you can schedule the task to run every day using the Windows Scheduler. Quick and dirty way is to use the DOS `AT` command, or you can use the "Task Scheduler" control panel applet (probably hiding in "administrative tools").