The DBA tools DB … your valuable repository

During the normal day-to-day work, we DBAs deal with a huge amount of information for the many purposes like troubleshooting problems, collecting data, analyzing figures …etc.

In fact it is very useful to store this information “or part of it by default” in a single repository, this simply give the DBAs the opportunity to analyze the collected data and the can generate some kind of figures, trends and also tracking.

This is the main idea behind creating the “dbatoolsdb”, a database that holds this kind of information and also divided into multiple schemas based on the section related to the collected information, for example a schema with name “sec” is used to hold all tables related to security information collected, “lck” schema can be sued then to hold locking and blocking information …etc.

My first use for the dbatoolsdb was a request for generating capacity reports for SQL server memory and storage on specific group of servers for a specific period of time, simply this can be handled by collecting this information on the target SQL servers and automating this process by using SQL Agent jobs then storing it in database tables that could be queried later and generating the required reports based on this stored information.

In this post I will provide the code for creating the dbatoolsdb database and its major used schemas, this database will be used many times in the upcoming posts as a repository for all information we will deal with.

SQL Code:
Create Database

USE [master]

GO

CREATE DATABASE dbatoolsdb ON  PRIMARY

( NAME = N'dbatoolsdb', FILENAME = N'D:\SQL_DBs\dbatoolsdb.mdf' , SIZE = 40960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

 LOG ON

( NAME = N'dbatoolsdb_log', FILENAME = N'D:\SQL_DBs\dbatoolsdb_log.LDF' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )

GO

Create Schemas

USE [DBAToolsDB]

GO

CREATE SCHEMA [sec]

GO

CREATE SCHEMA [trc]

GO

CREATE SCHEMA [lck]

GO