DBA Toolbox – Creating Your Own DBA Utilities Database

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:

-- Create the dbaUtilities database

CREATE DATABASE dbaUtilities

ON

(

NAME = dbaUtilities_data,

FILENAME = 'E:\SQLData\dbaUtilities_data.mdf',

SIZE = 100,

MAXSIZE = 500,

FILEGROWTH = 25

)

LOG ON

(

NAME = dbaUtilities_log,

FILENAME = 'L:\SQLLog\dbaUtilities_log.ldf',

SIZE = 50,

MAXSIZE = 300,

FILEGROWTH = 10

)

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.

-- Create the table to keep track of the database data and log file trending

-- Table name: DBFileGrowth

USE [dbaUtilities]

GO

CREATE TABLE [dbo].[DBFileGrowth](

[DBFileGrowthLoggingID] [int] IDENTITY (1,1) NOT NULL,

[DateTimeLogged] [datetime] DEFAULT GETDATE(),

[DatabaseName] [nvarchar](128) NOT NULL,

[Logical File Name] [nvarchar](225) NOT NULL,

[File Type] [nvarchar](10) NULL,

[Filegroup] [nvarchar](128) NULL,

[Autogrow / Max Size] [varchar](125) NULL,

[Reserved_MB] [float] NULL,

[Used_MB] [float] NULL,

[Free_MB] [float] NULL,

[Physical File Name] [nvarchar](260) NOT NULL,

CONSTRAINT [PK_DBFileGrowthLoggingID] PRIMARY KEY CLUSTERED ([DBFileGrowthLoggingID])

)

Script: DBFileGrowth.sql

-- Create the stored procedure to log the data and log file growth

-- Stored Proc Name: dbo.Get_DBFileGrowth

USE [dbaUtilities]

GO

CREATE PROC [dbo].[Get_DBFileGrowth]

AS

INSERT INTO [dbo].[DBFileGrowth](

[DatabaseName],

[Logical File Name],

[File Type],

[Filegroup],

[Autogrow / Max Size],

[Reserved_MB],

[Used_MB],

[Free_MB],

[Physical File Name]

)

EXEC sp_MSForEachDB'USE [?];

SELECT DB_NAME() AS DatabaseName,

df.name AS [Logical File Name],

df.type_desc AS [File Type],

CASE

WHEN fg.name IS NULL THEN ''LOG''

ELSE fg.name

END AS Filegroup,

CASE

WHEN is_percent_growth = 0

THEN LTRIM(STR(df.growth/128,12,1)) + '' MB, ''

ELSE

''By '' + CAST(df.growth AS VARCHAR) + '' percent, ''

END +

CASE

WHEN df.max_size = -1 THEN ''Unlimited''

ELSE ''Limited to '' + LTRIM(STR(df.max_size/128, 10, 1)) + '' MB''

END AS [Autogrow / Max Size],

ROUND(CAST((df.size) AS FLOAT)/128,2) AS Reserved_MB,

ROUND(CAST((FILEPROPERTY(df.name,''SpaceUsed'')) AS FLOAT)/128,2) AS Used_MB,

ROUND((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128),2) AS Free_MB,

df.physical_name AS [Physical File Name]

FROM sys.database_files df LEFT JOIN sys.filegroups fg

ON df.data_space_id = fg.data_space_id'

GO

Script: Get_DBFileGrowth.sql

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

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.

-- Create the table to keep track of the drive space usage

-- Table Name: DriveSpace

USE [dbaUtilities]

GO

CREATE TABLE [dbo].[DriveSpace](

[DriveSpaceLoggingID] [int] IDENTITY (1,1) NOT NULL,

[DateTimeLogged] [datetime] DEFAULT GETDATE(),

[Drive] [nvarchar](256) NOT NULL,

[DriveName] [nvarchar](256) NULL,

[TotalMBs] [decimal](12, 2) NOT NULL,

[FreeSpaceMBs] [decimal](12, 2) NOT NULL,

[% Free] [decimal](12, 2) NOT NULL,

CONSTRAINT [PK_DriveSpaceLoggingID] PRIMARY KEY CLUSTERED ([DriveSpaceLoggingID])

)

GO

Script: DriveSpace.sql

-- Create the stored procedure to log the disk drive space utilization

-- Stored Proc Name: dbo.Get_DriveSpaceUtilization

CREATE PROC [dbo].[Get_DriveSpaceUtilization]

AS

INSERT INTO [dbo].[DriveSpace]

([Drive]

,[DriveName]

,[TotalMBs]

,[FreeSpaceMBs]

,[% Free])

SELECT DISTINCT

vs.volume_mount_point [Drive],

vs.logical_volume_name AS [DriveName],

CAST(vs.total_bytes / 1048576 as decimal(12,2)) [TotalMBs],

CAST(vs.available_bytes / 1048576 as decimal(12,2)) [FreeSpaceMBs],

CAST((CAST(vs.available_bytes / 1048576 as decimal(12,2)) * 100) /(CAST(vs.total_bytes / 1048576 as decimal(12,2))) as decimal(12,2)) [% Free]

FROM

sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) vs

GO

Script: Get_DriveSpaceUtilization.sql

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

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.
4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

   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.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.