Agent Jobs

Create Custom Tracking Database ZTRACK

The ZTRACK database is my simple little tracking solution without having to incorporate any other database services, or associated SQL Features. Basically this is a custom Database that’s managed with just a series of Agent Jobs, queries, triggers. If you’re the kind of DBA that likes to write up your own kind of solution cause it’s fast, reliable, and doesn’t require a lot of effort then this is for you. Everything is contained without the ZTRACK Database so any objects you create (aside from triggers which mainly reside on the system tables), should be kept in this database.

Lets begin. First; create a database called ZTRACK. Nothing fancy. Use all the defaults, then run the following logic to create the tables. The first table of the ZTRACK database is called ABOUT_ZTRACK. This gives a basic description for everything you create. I’m prefixing the table names with “zt” as to avoid any name collision with reserved keywords. This might make queries alittle easier.

use ZTRACK;
set nocount on

create table ABOUT_ZTRACK
(
zt_id int identity(1,1) primary key not null
,	zt_object_type varchar(255)
,	zt_object_name varchar(255)
,	zt_object_logic varchar(max) default 'None'
,	zt_description varchar(max)
)

insert into about_ztrack
(
zt_object_type
,	zt_object_name
--,	zt_object_logic
,	zt_description
)
values
(
'DATABASE'
,	'ZTRACK'
--,
,	'This Database is desiged to hold information about various system configurations, growth history, and performance history.' ) 

It’s up to you to commit to creating a an entry and give it a description… Or… create a triggers under the SYSOBJECTS table, and create an entry automatically; then you can simply update the zt_description column later on if necessary.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s