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
go
Create table Last_DB_Track 
( last_checked datetime
, new_db_name varchar(128)
)
go

Here is the  procedure code:

Create Procedure sp_track_new_databases 
as
begin
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 
 begin 
   select @newDbCnt = count(name) 
     from sysdatabases
     where crdate > @last_run
 end 
-- empty tracking table from prev run contents
truncate table Last_DB_Track
-- fill new results 
if (@newDbCnt = 0) 
 Begin 
  insert Last_DB_Track 
   values (getDate(),
           'New Database were not found since last check')
 end 
Else
 Begin 
  insert Last_DB_Track
    Select getDate(), name 
     from sysdatabases 
     where crdate > @last_run
 End       
set nocount off
-- return the count as a result
Return @newDbCnt 
End 
go

Example of  Usage:

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

Conclusion

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)