INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

When was my SQL Server Databases last accessed/used?

When was my SQL Server Databases last accessed/used?

(OP)
Please help. I have SQL server 2000 and trying to migrate to either 2008 R2 or 2012. However, I have tons of databases in SQL Server 2000.
Is there any sample script that i can use to find out the last accessed date of the SQL Server databases?
I need to know if the databases are in use or if they are old and can be removed.
What is the best way to find out if databases are still active on my SQL Server?
Thanks in advance.
TN

RE: When was my SQL Server Databases last accessed/used?

Nguyen,

SQL Server 2000...that was a long time ago. I did a little research and I believe the way to go is to enable user login auditing then looking into the logs...The latter part would still be a manual process. You could also try to set to database OFFLINE and see who complains, but how long do you wait before you are satisfied is the question. In any case, if you are not certain, DO NOT DELETE the databases: set them offline or detach them.

Should you find a script to do this, you can use the sp_msforeachdb and sp_msforeachtable stored procedures to execute a script on all databases on a server, or all tables in a database. See an example here.

Good luck to you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close