SQL Server database administration can be a complex and stressful job. Database administrators' responsibilities...
cover the performance, integrity and security of business data and SQL Server databases. To fulfill their duties and to make business data available to its users, database administrators have to perform routine DBA checks on their SQL Servers to monitor their status.
Verify all SQL Server instances are up
Check the connectivity of each SQL Server on the network, and make sure all SQL Server instances are up. Ensure that all databases hosted on SQL Server instances are online. In addition to this, validate the storage of database data on the hard disk, which can be done by running the Database Consistency Checker (DBCC) CHECKDB command against each database on every SQL Server instance. Using DBCC CHECKDB with PHYSICAL_ONLY runs faster, which is useful when it's running against very large databases (VLDBs) and when the server is not powerful.
Inspect SQL Server error logs for unusual events
No matter how well you have designed and tested a database, errors will occur. Because SQL Server stores all information, warnings and error messages in the operating system and application log files, reviewing error logs daily helps quickly and easily identify unexpected errors and security problems that have occurred in the SQL Server environment. By default, SQL Server keeps one current log and six archive logs. You can use either SQL Server Management Studio Log Viewer or the sp_readerrorlog undocumented stored procedure to view SQL Server error logs.
Verify that all scheduled jobs have run successfully
Managing SQL Server Agent jobs is a key DBA responsibility, as they are crucial to any SQL Server environment. They are created and scheduled to perform critical business and operational tasks. Therefore, it is important to keep track of all scheduled jobs that have not run successfully.
SQL Server Agent stores history information for jobs, alerts and operators. History information is stored in the sysjobhistory table of the msdb database. You view the history of jobs and job steps using the Job Activity Monitor. Or, alternatively, you can query sysjobhistory for failed jobs. For example, see the following query that keeps track of failed jobs over the previous 24 hours:
Verify success of database backups
One of a DBA's key management tasks is backing up databases on a regular basis. This is because reliable backups are the most important tool in ensuring data recovery. Therefore, it is important for the DBA to check for database backups and validate that they have been successfully created and saved in a secure location.
SQL Server keeps information about each successful backup in the msdb database. Information about each successful backup operation is stored in the backupset table, and information about each backup physical file in the backupmediafamily table. For example, I wrote the following query, which can be used to check all databases' backup status for any given SQL Server instance:
Monitor disk space
Check the amount of free space available on each SQL Server and make sure you are not low on disk space. For best performance, it is recommended that all disks should have at least 15% or more free space available. You should also investigate the cause of disk free space fluctuation, if there is significant variation in the disk space from the previous day. Often the abnormal growth of the transaction log can cause of significant variation in the disk space.
To monitor disk space, you can use the undocumented stored procedure xp_fixeddrives. However, the limitation of xp_fixeddrives is that it can only be used to return information about fixed drives, not mount points. To monitor free space on operating system volumes, including the mount points, you can use the sys.dm_os_volume_stats dynamic management function. This dynamic management function returns information about the operation system volume on which databases files are located. For example, you can query this dynamic management function as follows, to get drive free-space information on any given SQL Server instance:
Alternatively, you can write a PowerShell script or common language runtime, or CLR, procedure to monitor disk and mount-point free space.
Review database sizes and growth settings
The size of the databases on an SQL Server instance is a measurable quantity that should be tracked on each individual SQL Server instance. This should be done because if the database and transaction file runs out of space, then all transactions running against it will fail. Therefore, it is critical to monitor and manage the growth of the database.
You can use sys.master_files system view to monitor database growth settings and file sizes. This system view returns the status of all files for all databases, including those that are offline. For example, the following query returns the filename, size, location and growth settings of each database file for each database.
About the author
Basit Farooq is a lead database administrator, trainer and technical author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms.