Tuesday, March 20, 2012

alert for database drop

Hello All,
SQL2K SP3a
I would like to create an alert when a database is dropped from my sql
servers. is it possible?
Thanks,
sqlgirl
sqlgirl (nice nickname!)
If you can create a trigger on sysobjects - then yes. Any object drop would
result in deleting corresponding entry from sysobjects table. Please let me
know if it helps.
Leon Shargorodsky
"Biva" wrote:

> Hello All,
> SQL2K SP3a
> I would like to create an alert when a database is dropped from my sql
> servers. is it possible?
> Thanks,
> sqlgirl
>
>
|||I don't think you can create a trigger on system tables. However, you could
have a job scheduled that runs every minute or every five minutes that
checks count(*) from sysdatabases and logs it. If this count is ever
*lower* than the last time the job ran, a database has been removed... send
an alert. If you shadow copy the database names from sysdatabases on each
run (maybe in two separate tables), you could even determine which
database(s) got dropped.
http://www.aspfaq.com/
(Reverse address to reply.)
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> sqlgirl (nice nickname!)
> If you can create a trigger on sysobjects - then yes. Any object drop
would
> result in deleting corresponding entry from sysobjects table. Please let
me[vbcol=seagreen]
> know if it helps.
> Leon Shargorodsky
> "Biva" wrote:
|||Hi
Biva, wait for SQL Server 2005 and you will be able to define a trigger on
drop database.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uSQpFPdGFHA.3092@.tk2msftngp13.phx.gbl...
> I don't think you can create a trigger on system tables. However, you
could
> have a job scheduled that runs every minute or every five minutes that
> checks count(*) from sysdatabases and logs it. If this count is ever
> *lower* than the last time the job ran, a database has been removed...
send
> an alert. If you shadow copy the database names from sysdatabases on each
> run (maybe in two separate tables), you could even determine which
> database(s) got dropped.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:664C0636-67CC-4551-B5B0-8BBCD51F5686@.microsoft.com...
> would
> me
>
|||> Biva, wait for SQL Server 2005 and you will be able to define a trigger on
> drop database.
Actually, I think you will need to use event notifications, but yes you will
be able to get it done with less work than my proposal (assuming you are
willing to wait for SQL Server 2005 to go RTM :-)).
DDL triggers only cover events that can be in a transaction, and I don't
think CREATE/DROP DATABASE fall under that category.

No comments:

Post a Comment