Database access

Is there any way to query SQL Server to see the last time a given database was access?
thanks guys
Hi,
SQL Server will not store this information into any tables. Only way is to
run the profiler or write you own triger to monitor the master..sysprocesses
table
and log the audit information into a table. Later you could query the table
to get the last access date and time for the database.
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys
|||No.
What I do is run a stored proc executed via SQL Agent that polls the
sysprocesses table hourly for db connections and I write the info to a table
I've defined.
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys
|||Hari,
Thanks so much for the help. I think I like the trigger idea. In any case, it will sharpen my scripting skills, such as they are.
Sara Beth
"Hari Prasad" wrote:

> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the master..sysprocesses
> table
> and log the audit information into a table. Later you could query the table
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>
|||Simon,
Thank you for the suggestion. I see from your answer and Hari's that this will be a wee bit of work for me. Ah, but that's ok. It'll keep me busy.
Sara Beth
"Simon" wrote:

> No.
> What I do is run a stored proc executed via SQL Agent that polls the
> sysprocesses table hourly for db connections and I write the info to a table
> I've defined.
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>
|||I believe the sysprocesses table is a virtual table and triggers cannot be
defined on them.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the
master..sysprocesses
> table
> and log the audit information into a table. Later you could query the
table[vbcol=seagreen]
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
database
> was access?
>
|||Hi Simon
This is correct, but not the whole story.
Triggers cannot be created on ANY system table, not just the virtual tables.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Simon" <me@.me.me> wrote in message
news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I believe the sysprocesses table is a virtual table and triggers cannot be
> defined on them.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
to
> master..sysprocesses
> table
> database
>
|||Hi Kalen... "Simon" is my pen name... you may remember me
from our discussions via email a couple weeks back revolving
around the aging out of statistics that Microsoft changed on you
after your book went to press... You may remember me!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O$7w0rcbEHA.644@.tk2msftngp13.phx.gbl...
> Hi Simon
> This is correct, but not the whole story.
> Triggers cannot be created on ANY system table, not just the virtual
tables.[vbcol=seagreen]
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Simon" <me@.me.me> wrote in message
> news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
is
> to
>
|||Hi Simon / Kalen,
Thanks for pointing out my error.
Sara Beth,
Like Simon suggested, you could create a table with Login name ,
database_name, login_time ,last_batch_time in some database and populate the
table using sysprocesses. Schedule the acticvity using sql agent jobs
Create the below table in some database.
Create table audit_database(login varchar(256),
dbname varchar(128),
login_time datetime,
last_batch_time datetime)
Schedule the below script to execute using SQL Agent -- Jobs
insert into dbname..audit_database select loginame
,db_name(dbid),login_time,last_batch from master..sysprocesses
change the dbname.. withoriginal database name in which the audit_database
table resides.
Use the below query to get the last login name for database wise.
select dbname, max(login_time) as last_login_time from audit_database
group by dbname
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:0ACD84B6-3BAF-4C58-AC57-20D17606B0DF@.microsoft.com...
> Simon,
> Thank you for the suggestion. I see from your answer and Hari's that
this will be a wee bit of work for me. Ah, but that's ok. It'll keep me
busy.[vbcol=seagreen]
> Sara Beth
> "Simon" wrote:
table[vbcol=seagreen]
database[vbcol=seagreen]