Extra logging in SQL error log

Hi All,
Hoping someone can assist with this. Is there an SQL trace flag, or
other method, where I can get the SQL server error logs (2000 and
2005) to log database detachements, and changes in DB status (eg to
read only from online etc).
I thought about a trigger on sysdatabases (sys.databases) but am
highly skeptical of triggers on system tables.
Why am I doing this? I need to monitor the up time of individual
databases on an instance, and third party tools are not an option.
thanks in advance.Hi
If you are on SQL Server 2005 you can create TRIGGER on database level (see
BOL for details)
To see error log
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6908c21a-65e3-458f-a272-fee256d86448.htm
<snafu66@.hotmail.co.uk> wrote in message
news:1188442379.790535.109850@.m37g2000prh.googlegroups.com...
> Hi All,
> Hoping someone can assist with this. Is there an SQL trace flag, or
> other method, where I can get the SQL server error logs (2000 and
> 2005) to log database detachements, and changes in DB status (eg to
> read only from online etc).
> I thought about a trigger on sysdatabases (sys.databases) but am
> highly skeptical of triggers on system tables.
> Why am I doing this? I need to monitor the up time of individual
> databases on an instance, and third party tools are not an option.
> thanks in advance.
>

External logs collection and monitoring

Essentially I am looking for a way to externally store db
audit logs and to be able to parse the data or filter for specific events an
d
ids for review by a security team. Something less manual than copying trace
files from the server to another server and going over each using profiler
(we're talking about 30 servers here!)... but not necessarily as hands-off a
s
flagging and email alerting only.
In going through support docs and threads here a couple questions have also
arisen...
Does the table the trace dumps to have to be part of the local db? Is it
possible to have the trace dump to an external db server?
In order to have trace dump the output to a table, does this require setting
up a job using SQL Trace stored procedures or can it be done just by changin
g
the server's auditing configuration?Server side traces (i.e. without using the Profiler GUI) can only write to
trace files. You can load trace files into a sql table (for example on a
central "audit" server) using the sytem function fn_trace_gettable. See BOL
for details
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JMBickham" <JMBickham@.discussions.microsoft.com> wrote in message
news:AF4060D6-6986-4999-85E3-5B090EC39A53@.microsoft.com...
> Essentially I am looking for a way to externally store db
> audit logs and to be able to parse the data or filter for specific events
> and
> ids for review by a security team. Something less manual than copying
> trace
> files from the server to another server and going over each using profiler
> (we're talking about 30 servers here!)... but not necessarily as hands-off
> as
> flagging and email alerting only.
> In going through support docs and threads here a couple questions have
> also
> arisen...
> Does the table the trace dumps to have to be part of the local db? Is it
> possible to have the trace dump to an external db server?
> In order to have trace dump the output to a table, does this require
> setting
> up a job using SQL Trace stored procedures or can it be done just by
> changing
> the server's auditing configuration?sql