Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

When was my SQL Server Databases last accessed/used?

Status
Not open for further replies.
tnguyen315

tnguyen315

MIS
Feb 4, 2009
137
US
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
 
Sort by date Sort by votes
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).
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
SQL Server in Windows
Replies
1
Views
759
pjw001
pjw001
CodeWell
  • Locked
  • Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
642
CodeWell
CodeWell
bethabernathy
  • Locked
  • Question
Front End Access 365 Database dsn Connection Fails when attempting to link to 2016 SQL server
Replies
1
Views
534
dhookom
dhookom
JoPaBC
  • Locked
  • Question
Schedule and automate backups of SQL Express databases in SQL Server Express 1
Replies
5
Views
766
JoPaBC
JoPaBC
Ikatiemarie
  • Locked
  • Question
SQL Server 2008 SP_Send_dbmail
Replies
0
Views
1K
Ikatiemarie
Ikatiemarie

Part and Inventory Search

Sponsor

Back
Top