August 21, 2008 at 11:06 am
Could you please share your view on the events we need to capture related to regular monitoring of SQL Server databases?
We are actually working to build a baseline for Monitoring Standard and looking to consolidate experience from all. So I thoght it is the best place (Per my knowledge) where I can get some very experienced views 😛
Thanks for your time.
August 22, 2008 at 3:11 pm
Before answering that I need to know what you are trying to get out of monitoring. For example, are you trying to find slow running queries? Are you trying to find out who is accessing tables with sensitive data for PCI, SOX, etc.? Are you trying to find out if people are making changes to the database or server?
What's the goal?
David
August 22, 2008 at 10:16 pm
Utsab Chattopadhyay (8/22/2008)
We are trying to make a standard here which we may share among the organazation as a standard monitoring events. It should cover all the typical issues from all areas we generally monitorPlease let me know if you need more info
One way is to break them apart by goals and have them ready to run when you need them. For example:
1. performance monitoring
2. sensitive data like credit card numbers
3. look for database/server changes or events that require ddl_admin, db_owner, sysadmin rights etc. (pretty much all of the Security Audit section)
4. suddenly I'm drawing a blank other possibilities ... any thoughts?
If you have one that needs to run continuously (like #2 above would be) have the SQL Agent job stop and restart periodically in case a trace fails or is accidentally turned off. Keep only a few old trace files around when you restart so that the drives won't fill up and your trace processing software has a chance to grab all the files. If you have to run #2, #3 is a good one to run with it. The third one is also particularly handy to run when installing or upgrading vendor software.
David
August 23, 2008 at 9:32 am
Well, the base idea of monitoring is very good but has to be implemented effectively. To begin with identify the core areas that need to be monitored on a daily basis and try to automate the process with immediate notification in the event of an undesired outcome. I would suggest you to creat e a Baseline_DBA Routine DB and create a set of SP's in it.Incorporated these SP's in automated jobs and be at ease with the world. Some of the SP's I use are listed below
1.USP_AUDITDISKSPACEPROBLEMS
2.USP_DRIVESTATS
3.USP_AUDITUPDATEFILESIZES
4.US_AUDITRECOVERYMODELS
5.USP_AUDITDATABASEGROWTHPROBLEMS
6.USP_BACKUPDATABASES
7.USP_SENDEMAIL
8.USP_SENDNOTIFICATION
9.USP_GETOAPROPERTY
10.USP_REINDEXDB
And then if some of the one off processes fail you would be able pin point the exact cause of it. You can also schedule Profiler Traces every week to monitor for the Resources taken by SQL server and then act accordingly.
Thanks..!!! 🙂
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply