asked
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?
Hi everyone is there anybody have any chance please let me know where we will get the scripts for daily basis work... Kindly gothrough the question for the same... I got good answers thanks for all but need to send dbmail with outputs for atleast some of the dailybasis work by automation....
I would recommend you to buy a tool like Red Gate SQL Monitor
Not so automatic when you support multiple Clients and/or domains, and/or networks, and/or VPNs with a limited budget.
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][1] too. Honestly though, you're better off picking up a 3rd party tool that does all this stuff for you. [1]:
http://msdn.microsoft.com/en-us/library/bb895144.aspx
It's hard work to create an automatic monitoring tool by yourself, and there are some good 3rd party tool on the market. Like your SQL monitor from Red gate. :) BTW, last week I talked to one of your collegues about improvements to SourceControl.
Good to hear it. That tool could use some work. There are a few new things coming out soon. I've started testing them (and finding problems).
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.
Thanks a lot for everyone... But we are supporting to non-critical apps so not able to buy thrird pary tools... So need to create automation for atleast some of the scripts.... Do you have any type of scripts let me know with dbmail that is fine... thanks to everyone in advance...
The scripts above, if you follow them through, will set up alerts that are emailed to you. I took the idea and wrapped in a few extra features and monitoring odds and ends, and then put a set of reports into SSRS around the whole thing for non-critical information, and left the emails about failed jobs / long-running queries / overly long jobs etc in place.
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.
I tried putting this as a comment, but couldn't get the formatting right. And I'm being a bit long-winded...
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").
