database access statistics?

Is there a simple way to see when the last time a
database was accessed?
Best,
Adria
a-carbo@.tamu.eduSQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
you can make use of profiler as well by running trace.
--
-Vishal|||go to the errorlog file which can be found at
$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
and open the errorlog file , the lastest errorlog file will not have any
extension. Every time SQL Server is started, the current error log is
renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes
errorlog.3 and so on you will have to scroll through these errorlog files to
see which user has logged in at what time. This method will be useful if you
want to track "database connection" made by selected users who are specific
to the particular database only. System administrators like 'sa' can logon
to any of the database. Hence you can not exactly track database access on
the basis of database connections but, on the basis of "user connections"
made.
If you purely want to check the database connections you will have to make
use of SQL Profiler. Create a new trace with the events under "Security
audit" >> "audit login" . Under data columns include "database name". and
run the trace.
--
-Vishal
"Adria" <a-carbo@.tamu.edu> wrote in message
news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> I just checked the Properties on the server and found
> that the Audit Level was already set to all/successes.
> How then do I view connections?
> FYI ... this is a database that I suspect has not been
> used for some time.
> Best,
> Adria
> >--Original Message--
> >SQL Server can log event information for logon attempts
> and you can view it
> >by reviewing the errorlog. By turning on the auditing
> level of SQL Server.
> >
> >follow these steps to enable auditing of all/successfull
> connections with
> >Enterprise Manager in SQL Server:
> >
> >Expand a server group.
> >Right-click a server, and then click Properties.
> >On the Security tab, under Audit Level, click
> all/success etc(required
> >option).
> >You must stop and restart the server for this setting to
> take effect.
> >
> >you can make use of profiler as well by running trace.
> >
> >--
> >-Vishal
> >
> >
> >.
> >|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You will need to monitor this yourself with profiler
going forward. I don't think there is any way to get
historical usage if you haven't been monitoring for such.
-Sue
On Thu, 4 Sep 2003 07:45:45 -0700, "Adria"
<a-carbo@.tamu.edu> wrote:
>I just checked the Properties on the server and found
>that the Audit Level was already set to all/successes.
>How then do I view connections?
>FYI ... this is a database that I suspect has not been
>used for some time.
>Best,
>Adria
>>--Original Message--
>>SQL Server can log event information for logon attempts
>and you can view it
>>by reviewing the errorlog. By turning on the auditing
>level of SQL Server.
>>follow these steps to enable auditing of all/successfull
>connections with
>>Enterprise Manager in SQL Server:
>>Expand a server group.
>>Right-click a server, and then click Properties.
>>On the Security tab, under Audit Level, click
>all/success etc(required
>>option).
>>You must stop and restart the server for this setting to
>take effect.
>>you can make use of profiler as well by running trace.
>>--
>>-Vishal
>>
>>.|||Vishal, Database Name is not one of the available columns
in the SQL Profiler, unfortunately. I'm trying to find
out the same thing on one of my servers and haven't found
a clean way to make the determination. (My latest RFI is
in subject "Determine database no longer in use - Redux"
If there was some time of condition that could be
monitored to determine if an application opens a
particular database it would help tremendously. Right now
I'm trying to capture "Active Transactions" because that
seems to be the closest I can find to 'real' usage. If
there's another one you can think of I'd be much obliged.
Allen
>--Original Message--
>go to the errorlog file which can be found at
>$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
>and open the errorlog file , the lastest errorlog file
will not have any
>extension. Every time SQL Server is started, the current
error log is
>renamed to errorlog.1; errorlog.1 becomes errorlog.2,
errorlog.2 becomes
>errorlog.3 and so on you will have to scroll through
these errorlog files to
>see which user has logged in at what time. This method
will be useful if you
>want to track "database connection" made by selected
users who are specific
>to the particular database only. System administrators
like 'sa' can logon
>to any of the database. Hence you can not exactly track
database access on
>the basis of database connections but, on the basis
of "user connections"
>made.
>If you purely want to check the database connections you
will have to make
>use of SQL Profiler. Create a new trace with the events
under "Security
>audit" >> "audit login" . Under data columns
include "database name". and
>run the trace.
>--
>-Vishal
>"Adria" <a-carbo@.tamu.edu> wrote in message
>news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
>> I just checked the Properties on the server and found
>> that the Audit Level was already set to all/successes.
>> How then do I view connections?
>> FYI ... this is a database that I suspect has not been
>> used for some time.
>> Best,
>> Adria
>> >--Original Message--
>> >SQL Server can log event information for logon attempts
>> and you can view it
>> >by reviewing the errorlog. By turning on the auditing
>> level of SQL Server.
>> >
>> >follow these steps to enable auditing of
all/successfull
>> connections with
>> >Enterprise Manager in SQL Server:
>> >
>> >Expand a server group.
>> >Right-click a server, and then click Properties.
>> >On the Security tab, under Audit Level, click
>> all/success etc(required
>> >option).
>> >You must stop and restart the server for this setting
to
>> take effect.
>> >
>> >you can make use of profiler as well by running trace.
>> >
>> >--
>> >-Vishal
>> >
>> >
>> >.
>> >
>
>.
>|||> Database Name is not one of the available columns
> in the SQL Profiler, unfortunately.
This column will not come by default.When you create a new trace go to the
3rd tab "data columns" and add the data column "database name" to the
selected data.
You can run the system stored procedure sp_who2 from query analyzer.
or query sysprocesses table to find users and processes
Ex:
select * from sysprocesses where db_name(dbid) = 'master' --specify
To get the active transaction on particular database you can run a dbcc
command.
dbcc opentran ('<database_name>')
--
-Vishal
"Allen White" <awhite@.advanstar.com> wrote in message
news:1e8101c372fa$8afa0f20$a601280a@.phx.gbl...
> Vishal, Database Name is not one of the available columns
> in the SQL Profiler, unfortunately. I'm trying to find
> out the same thing on one of my servers and haven't found
> a clean way to make the determination. (My latest RFI is
> in subject "Determine database no longer in use - Redux"
> If there was some time of condition that could be
> monitored to determine if an application opens a
> particular database it would help tremendously. Right now
> I'm trying to capture "Active Transactions" because that
> seems to be the closest I can find to 'real' usage. If
> there's another one you can think of I'd be much obliged.
> Allen
>
> >--Original Message--
> >go to the errorlog file which can be found at
> >
> >$\Program Files\Microsoft SQL Server\MSSQL$<instance>\LOG
> >
> >and open the errorlog file , the lastest errorlog file
> will not have any
> >extension. Every time SQL Server is started, the current
> error log is
> >renamed to errorlog.1; errorlog.1 becomes errorlog.2,
> errorlog.2 becomes
> >errorlog.3 and so on you will have to scroll through
> these errorlog files to
> >see which user has logged in at what time. This method
> will be useful if you
> >want to track "database connection" made by selected
> users who are specific
> >to the particular database only. System administrators
> like 'sa' can logon
> >to any of the database. Hence you can not exactly track
> database access on
> >the basis of database connections but, on the basis
> of "user connections"
> >made.
> >
> >If you purely want to check the database connections you
> will have to make
> >use of SQL Profiler. Create a new trace with the events
> under "Security
> >audit" >> "audit login" . Under data columns
> include "database name". and
> >run the trace.
> >
> >--
> >-Vishal
> >
> >"Adria" <a-carbo@.tamu.edu> wrote in message
> >news:1c6f01c372f3$390c6980$a501280a@.phx.gbl...
> >> I just checked the Properties on the server and found
> >> that the Audit Level was already set to all/successes.
> >>
> >> How then do I view connections?
> >>
> >> FYI ... this is a database that I suspect has not been
> >> used for some time.
> >>
> >> Best,
> >> Adria
> >>
> >> >--Original Message--
> >> >SQL Server can log event information for logon attempts
> >> and you can view it
> >> >by reviewing the errorlog. By turning on the auditing
> >> level of SQL Server.
> >> >
> >> >follow these steps to enable auditing of
> all/successfull
> >> connections with
> >> >Enterprise Manager in SQL Server:
> >> >
> >> >Expand a server group.
> >> >Right-click a server, and then click Properties.
> >> >On the Security tab, under Audit Level, click
> >> all/success etc(required
> >> >option).
> >> >You must stop and restart the server for this setting
> to
> >> take effect.
> >> >
> >> >you can make use of profiler as well by running trace.
> >> >
> >> >--
> >> >-Vishal
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >