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
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: firstname.lastname@example.org)