In this post let’s discuss how to Set-up internal data warehouse to analyse trends of databases Growths in SQL Server and DBA alerts for Maintenance and diagnosing SQL Servers.
The important information which are important for analysing the Database growth and Disk sizes growth etc. Can be captured to a central location for analysing the trends. A DBA is responsible for analysing the trends in SQL Server and providing management the relevant information on like expected growth of the Server/Database, Resources needed going further. If necessary a DBA will be involved in Archiving the older data into another server for various reasons like database growth, database table sizes, query performance etc.
DBA should set up various Data collection to ensure he analyses the trends and monitors Server Resources. Some of the important Data Collection needs to be setup are:
1.Collect Disk sizes on all SQL Servers.
2.Collect Database Data File and Log File sizes for calculating Database sizes and pre-sizing the Log and Data Files to appropriate sizes.
3.Collect Error Log history for any unwanted errors taking place on server.
4.Collect Job History from all SQL Servers.
DBA is also responsible in setting up alerting for Daily Tasks to check whether they successful or not. A Database Mail Profile needs to be defined on SQL Server to able to send emails which is the first Step of Alerting. The Various types of alerting which can be setup on Database are:
1. DBA alerts need to setup for basic monitoring and diagnosing SQL Server like a Emai with Daily Disk sizes on all SQL Servers compared to Older Disk Sizes with difference in them, percentage change.
2.DBA can setup a 3 hourly Email job to email the error log from SQL Servers to analyse any unknown errors.
3.DBA can setup a Database Files sizes changes based on a threshold to compare like if Data File or Log File grows by 10% in a day. A Database which is frequently growing needs to be analysed and pre-Sized for optimum performance.
4.DBA can setup alert Emails for Summarized Daily Maintenance Jobs history with information like whether they are successful or Failed.
5.DBA can setup alert Emails for Summarized Backup jobs to see if all backups have succeeded in all SQL Servers.
6.DBA can also setup Database Mail Alert if individual job fails by setting up a Operator in SQL Server.
7.DBA can setup a Login Audit by using SQL Server Audit functionality(From SQL 2008) or by setting up a job to run 3 hourly check to see if there is any new logins created and if any new SysAdmins added on SQL Server.
8.DBA can setup hourly/half-hourly check on SQL Servers to check if change in Disk sizes is more than a threshold percentage like 10%.
9.Setting up in-Built alerts for Database Buffer Cache hit ratio< 95%.
10.Setting up in-Built alerts for Page life expectancy <300.
11.Setting up in-Built alerts for Batch Requests per second based a threshold like sometimes 1000/1500/2000/5000 per second.
As I Post individual articles on how to setup above, I will update the above post with relevant hyperlinks to the posts.