DBA Toolbox – Creating Your Own DBA Utilities Database

By | In Database Administration, SQL Server | June 23rd, 2016

When I was young, I had dreamed of becoming an aircraft mechanic. Every shop class that I had attended had teachers with exceptional skills in addition to many years of experience. The most common trait of each of these successful teachers is their unique toolboxes with collections of extensive tools that they had acquired over the years. In addition, they are true masters of their own crafts and extremely well-versed in using their toolsets in their toolboxes. As a DBA, you too can develop your own toolbox and be a master in the craft of administering and managing databases. SQL Server has already made available to you the built-in system tables, functions, and views. You just need to meet it half-way to create your own DBA toolbox by creating your own DBA utilities database.

The benefits of creating your own DBA utilities can include and not limited to the following:

  • Monitor the database growth
  • Report on disk drive spaces
  • Store the backup history
  • Capture the error logs
  • Keep track of the index usage
  • Log critical security events

The list can go on and on. Please reply to this blog with your ideas (tools) you wish to see.

In the following sections, we will explore:

  1. Creating the DBA utilities database
  2. Monitoring the database growth
  3. Reporting the disk drive spaces

To create the DBA utilities database, we can simply run the following TSQL script:

Script: Create_dbaUtilities_db.sql

Note: Make sure you change the data and log file paths.

To monitor the database growth, we will need to:

  1. Create the table to store the data and log file.
  2. Develop the stored procedure to query the sys.database_files and sys.filegroups system tables to report on the data and log file stats.
  3. Schedule a job to run the stored procedure on a daily basis.

Script: DBFileGrowth.sql

Script: Get_DBFileGrowth.sql

If you had queried the dbo.DBFileGrowth table, you will get the result output similar to the following output:

DBFileGrowth Table

Screen-shot: DBFileGrowth Table

The remaining step is for you to create a job to execute the stored procedure. Run it daily.

Reporting the disk drive spaces usage requires the following steps:

  1. Create the table to store the disk drive spaces.
  2. Develop the stored procedure to query the sys.master_files system table and the sys.dm_os_volume_stats dynamic management function to report on the disk drive space utilization.
  3. Schedule a job to run on a daily basis to run the stored procedure.

Script: DriveSpace.sql

Script: Get_DriveSpaceUtilization.sql

You can query the dbo.DriveSpace table and you will see the output similar to the following:

DriveSpace Table

Screen-shot: DriveSpace Table

The remaining step is for you to create a job to execute the stored procedure. Again, the same as capturing the database data and log file growth, run it daily.

By using this SQL Server script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

  1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.
  2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.
  3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

  1. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.
Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).