SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Tracking Down Newly Created Databases

By Eli Leiba,

There are cases, especially in large organizations, where the DBA team is too small to handle all the database management tasks on all the SQL Servers across the organization. In these cases, the DBA can grant programmers the db_owner role.

Sometimes programmers take advantage of this situation and create databases with size and growth definitions that are not confirmed with the DBA before creation. In order to prevent such situations from occurring, I designed a procedure to run from the master database on a recurring (weekly) basis using a job.

The procedure returns the count of the newly created databases on that server. If the count is positive then DBA should examine the database whose names are listed in the Last_DB_Track table. Here is the definition of this Table:

Use master
Create table Last_DB_Track 
( last_checked datetime
	, new_db_name varchar(128)

Here is the  procedure code:

Create Procedure sp_track_new_databases 


declare @last_run datetime 
declare @newDbCnt int

set nocount on 
set @newDbCnt = 0

-- get the last time the check was done
select @last_run = max(last_checked) from Last_DB_Track

-- if there was a last check then check if new databases exist
if @last_run is not null 
   select @newDbCnt = count(name) 
     from sysdatabases
     where crdate > @last_run

-- empty tracking table from prev run contents
truncate table Last_DB_Track

-- fill new results 
if (@newDbCnt = 0) 
  insert Last_DB_Track 
   values (getDate(),
           'New Database were not found since last check')
  insert Last_DB_Track
    Select getDate(), name 
     from sysdatabases 
     where crdate > @last_run

set nocount off
-- return the count as a result
Return @newDbCnt 


Example of  Usage:

Use master 
DECLARE @retval int
Exec @retval = sp_track_new_databases
If (@retval > 0)  
 Select * 
  from Last_DB_Track


The procedure can be used as a tool for monitoring newly created databases to keep the DBA informed with programmers activity that should be reported to the DBA.

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: iecdba@hotmail.com)

Total article views: 4864 | Views in the last 30 days: 0
Related Articles

Tracking and Reporting Database Growth

An article from new author Lokesh Gunjugnur that shows how you can set up tracking for database grow...


Change Tracking and Database Refactoring

Using change tracking in SQL Server 2008 to aid in database refactoring within an OLTP system.


Database Tracking

Tracking the Structural Changes, Store procedure changes in SQL for a period


Track database growth 2012/2014

Enhancement to Track database growth By Irwan Tjanterik, 2014/09/23


track the growth of SQL Database..

I was asked in my last company to track the growth of our Database