Need to schedule a job that runs on all databases in an instance

Question

Post reply

Need to schedule a job that runs on all databases in an instance

sreeya

SSC Eights!

Points: 979
More actions
March 5, 2009 at 2:14 pm

#155376

Hi All,
I need to schedule a job which captures some database size related info and inserts into a table.I modified sp_spaceused and executed on all databases .This works fine when I am running the below sql but the job fails whenever new database is added to the instance because I haven't created an sp on that database. Is there a way to notify our dba team when ever a new database is created so that I can create an sp on that database?
Here is the sql:
DECLARE @sql nVARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] NOT IN ('model', 'tempdb','master','msdb')
ORDER BY [name]
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'USE [' + @DB +']' + CHAR(13) + 'exec dbo.databasesize' + CHAR(13)
INSERT INTO performance_unit.dbo.Database_Size
exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
deallocate curDB
Thanks,
Swetha

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

subhashpunia SSC-Addicted Points: 406 More actions · Answer 1

Use the DDL Trigger functionality:

CREATE TRIGGER ddl_trig_database

ON ALL SERVER

FOR CREATE_DATABASE

AS

--Add here code to notify dba team.

GO

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

That trigger is def a good idea :). Another option you have is since the stored procedure is required in each database; why not add it to the MODEL database?

Since each database is a template of model; you get your work completed for you without any headach later :).

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Mohit (3/5/2009)
That trigger is def a good idea :). Another option you have is since the stored procedure is required in each database; why not add it to the MODEL database?
Since each database is a template of model; you get your work completed for you without any headach later :).

Heh... yeah... except when you're trying to create a database without all the stuff. I'll be back...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Glenn Dorling SSCertifiable Points: 7878 More actions · Answer 4

Obviously I don't know what's in your databasesize proc, but it should be possible to havce a single copy of that in the performance_unit (where you're writing the stats). With a bit of minor re-coding it should be able to include the logic from both the existing command and the code you've posted, then the job becomes a simple call to that one procedure.

That's the way we've done it for our systems anyway, and you'd have to be doing something very different for it not to work on your system.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 5

Jeff Moden (3/5/2009)
Mohit (3/5/2009)
That trigger is def a good idea :). Another option you have is since the stored procedure is required in each database; why not add it to the MODEL database?
Since each database is a template of model; you get your work completed for you without any headach later :).
Heh... yeah... except when you're trying to create a database without all the stuff. I'll be back...

Heh :p I wouldn't put the entire world in Model; well I might LOL. I get lazy fast heh, like this article I am trying to finish lol. What did I get myself into; writing article is alot more work then I though originally ^^.

To be serious for a sec, in Model databases; only thing i needed to create in my model database was a custom type. Because this application which is split across 4 databases use temp db with that type. So I kept running into issues without that type. In your opinion Jeff when will you consider making changes to MODEL so it can be in each database? I guess another change I have made is with mode on my DEV/TEST servers is made sure the Recovery Model is SIMPLE because point-in-time recovery is not needed there.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

sreeya SSC Eights! Points: 979 More actions · Answer 6

Thanks all . I created a DDL trigger and my problem is resolved