I've got a database that no one is supposed to be using. I would like to
take it offline but I'd like to verify first that noone is actually using
it. Is there any way, other than SQL Profiler, to see who is/has used the
database recently?

TIA - Jeff.

Mufasa

Using Query analyser/Management Studion

Sp_who2 -- check the dbname column
or
select * from master..sysprocess where dbid=xx -- replace xx with dbid
of your database
or

Enterprise manager/management studio->Management-> Current
Activity(2000)/Activity Monitor(2005)

vinu


"Mufasa" <jb@nowhere.com> wrote in message
news:eMwHaQV0IHA.548@TK2MSFTNGP06.phx.gbl...
> I've got a database that no one is supposed to be using. I would like to
> take it offline but I'd like to verify first that noone is actually using
> it. Is there any way, other than SQL Profiler, to see who is/has used the
> database recently?
>
> TIA - Jeff.
>
>

above will tell you who is using it.
not sure how you get details on who has used it...


"vinu" <vinu.t.1976@gmail.com> wrote in message
news:OIz5NYV0IHA.4500@TK2MSFTNGP03.phx.gbl...
> Mufasa
>
> Using Query analyser/Management Studion
>
> Sp_who2 -- check the dbname column
> or
> select * from master..sysprocess where dbid=xx -- replace xx with dbid
> of your database
> or
>
> Enterprise manager/management studio->Management-> Current
> Activity(2000)/Activity Monitor(2005)
>
> vinu
>
>
> "Mufasa" <jb@nowhere.com> wrote in message
> news:eMwHaQV0IHA.548@TK2MSFTNGP06.phx.gbl...
>> I've got a database that no one is supposed to be using. I would like to
>> take it offline but I'd like to verify first that noone is actually using
>> it. Is there any way, other than SQL Profiler, to see who is/has used the
>> database recently?
>>
>> TIA - Jeff.
>>
>>
>
>

If it's SQL2005, there is a default trace that should have been running
(unless it's disabled). You look up its generated trace files. But I'm not
sure if you can rely on it. For decommissioned servers, we typically run SQL
trace for a period of time to capture all the SQL statements. Since the
server is not supposed to be used, there shouldn't be a lot of trace data.

Linchi

"Mufasa" wrote:

> I've got a database that no one is supposed to be using. I would like to
> take it offline but I'd like to verify first that noone is actually using
> it. Is there any way, other than SQL Profiler, to see who is/has used the
> database recently?
>
> TIA - Jeff.
>
>
>

Try this:
I have been asked to document who is using which Database, as we are
moving Datacenters, so i have to build a inventory for databases and
figure out who is and has used particular Dbs. thats 350 of them.
I capture the sp_who2 info into a Table and do that every 2 hours for
the past couple of weeks and i am Ready to Query to find out whos been
using the Database(s).
I also used server side Traces running on those DB's. And at the end
of the day Dump them into a SQl table to analyze.

There are many ways to list what connections (if not specific users) are
active on a server. One way is to use sp_who2.
However, if there are hundreds of potential users, many of them in remote
locations, then just knowing who is connected will not be very useful,
especially if it's late on a Friday afternoon, and you need to perform a
maintenace operations that was clearly scheduled and announced ahead of time,
but some late working people with no life obviously didn't get the memo. ;-)
Well, there is an option, but don't share this little secret with too many
people...
It is possible to set a database to RESTRICTED_USER mode while
simultaneously disconnecting all connections and aborting any active
transactions. The following will disconnect all connections without active
transactions and allow 2 minutes for any currently active transactions to
complete. Once done, the database will be in RESTRICTED_USER mode, meaning
that only the sysadmin or members of the DBO group can access it. Once the
maintenace operation has completed, restore the database to MULTI_USER mode.

ALTER DATABASE database-name SET RESTRICTED_USER WITH ROLLBACK AFTER 120
SECONDS

http://www.blackwasp.co.uk/SQLRestrictedUser.aspx


"Mufasa" wrote:

> I've got a database that no one is supposed to be using. I would like to
> take it offline but I'd like to verify first that noone is actually using
> it. Is there any way, other than SQL Profiler, to see who is/has used the
> database recently?
>
> TIA - Jeff.
>
>
>