Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
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)

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

Tracking and Reporting Database Growth

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

ARTICLE

Change Tracking and Database Refactoring

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

FORUM

Database Tracking

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

SCRIPT

Track database growth 2012/2014

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

FORUM

track the growth of SQL Database..

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

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones