Determining LAST DB usage

2 Jun 2005 3:35 PM

mdgraves

Trying to build routine to determine when LAST a database was used - accessed
by a login. This would help determine AGE of databases, and build archival /
removal plans.

Anyone know how to get LAST ACCESSES info per database, per server instance?

2 Jun 2005 5:48 PM

Armando Prato

This comes up from time to time with various opinions.

One person suggested setting all db's to autoclose and
then checking the sql server log.... I don't like that approach
because there's a lag time every time the db is accessed.

The method I employ uses a sql server agent job that polls
the sysprocesses table to check for db connections. I then
write the information to a table I've created. I check this
table periodically for db's that haven't been touched in a
while and I then contact the owner of that db.

I'm sure other people here can offer other options they employ.

Show quoteHide quote

"mdgraves" <mdgra***@discussions.microsoft.com> wrote in message
news:5B1A0F63-E21F-415E-B259-15C79DF52D04@microsoft.com...
> Trying to build routine to determine when LAST a database was used -
accessed
> by a login. This would help determine AGE of databases, and build
archival /
> removal plans.
>
> Anyone know how to get LAST ACCESSES info per database, per server
instance?

2 Jun 2005 7:37 PM

mdgraves

Thanks.

Maybe another way?
Does the TRANS LOG record events when a connection happens?
Does the DateTime stamp on physical file LDF's update when LOG and DATA are
input via SQL?
I can them look at NT DateTime stamps.

Show quoteHide quote

"Armando Prato" wrote:

>
> This comes up from time to time with various opinions.
>
> One person suggested setting all db's to autoclose and
> then checking the sql server log.... I don't like that approach
> because there's a lag time every time the db is accessed.
>
> The method I employ uses a sql server agent job that polls
> the sysprocesses table to check for db connections. I then
> write the information to a table I've created. I check this
> table periodically for db's that haven't been touched in a
> while and I then contact the owner of that db.
>
> I'm sure other people here can offer other options they employ.
>
> "mdgraves" <mdgra***@discussions.microsoft.com> wrote in message
> news:5B1A0F63-E21F-415E-B259-15C79DF52D04@microsoft.com...
> > Trying to build routine to determine when LAST a database was used -
> accessed
> > by a login. This would help determine AGE of databases, and build
> archival /
> > removal plans.
> >
> > Anyone know how to get LAST ACCESSES info per database, per server
> instance?
>
>
>

2 Jun 2005 8:58 PM

Armando Prato

I'm not sure if you can necessarily rely on the datestamps. I also haven't
investigated it so
I can't say for sure.

Show quoteHide quote

"mdgraves" <mdgra***@discussions.microsoft.com> wrote in message
news:00A4726E-C18B-4522-A330-DEBCD78A9F48@microsoft.com...
> Thanks.
>
> Maybe another way?
> Does the TRANS LOG record events when a connection happens?
> Does the DateTime stamp on physical file LDF's update when LOG and DATA
are
> input via SQL?
> I can them look at NT DateTime stamps.
>
> "Armando Prato" wrote:
>
> >
> > This comes up from time to time with various opinions.
> >
> > One person suggested setting all db's to autoclose and
> > then checking the sql server log.... I don't like that approach
> > because there's a lag time every time the db is accessed.
> >
> > The method I employ uses a sql server agent job that polls
> > the sysprocesses table to check for db connections. I then
> > write the information to a table I've created. I check this
> > table periodically for db's that haven't been touched in a
> > while and I then contact the owner of that db.
> >
> > I'm sure other people here can offer other options they employ.
> >
> > "mdgraves" <mdgra***@discussions.microsoft.com> wrote in message
> > news:5B1A0F63-E21F-415E-B259-15C79DF52D04@microsoft.com...
> > > Trying to build routine to determine when LAST a database was used -
> > accessed
> > > by a login. This would help determine AGE of databases, and build
> > archival /
> > > removal plans.
> > >
> > > Anyone know how to get LAST ACCESSES info per database, per server
> > instance?
> >
> >
> >