How to check if a database is being used?????

At the Windows level, rather than the SQL level, it is possible to do auditing by file. You would be able to track Open/Close, and view the results in your Windows Security Event Log. Could expand on the subject if you need additional detail

Angelindiego SSCertifiable Points: 6048 More actions · Answer 4

Angelindiego

SSCertifiable

Points: 6048

May 10, 2010 at 8:37 am

#1165029

expand away....grasshopper!!

Thank you!!,

Angelindiego

dean_vr SSC Rookie Points: 27 More actions · Answer 5

dean_vr

SSC Rookie

Points: 27

July 15, 2010 at 5:52 am

#1193393

list of dbs in use since last reboot

select a.name, a.dbid, max(last_user_seek), max(last_user_scan)

from sys.sysdatabases a

left outer join sys.dm_db_index_usage_stats b on a.dbid = b.database_id

group by a.name, a.dbid

Angelindiego SSCertifiable Points: 6048 More actions · Answer 6

Angelindiego

SSCertifiable

Points: 6048

July 15, 2010 at 8:25 am

#1193515

SWEET!! Thank you!!

Thank you!!,

Angelindiego

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

dean_vr (7/15/2010)

list of dbs in use since last reboot

select a.name, a.dbid, max(last_user_seek), max(last_user_scan)

from sys.sysdatabases a

left outer join sys.dm_db_index_usage_stats b on a.dbid = b.database_id

group by a.name, a.dbid

That may be of limited value if you have regular maintenance jobs for a database, like DBCC checks or reindexing. However, you can see if there has been any access since the last job ran.

Angelindiego SSCertifiable Points: 6048 More actions · Answer 8

Angelindiego

SSCertifiable

Points: 6048

July 15, 2010 at 9:08 am

#1193564

this works for me and thank you again!!

Thank you!!,

Angelindiego

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 9

SQLBuddy123

One Orange Chip

Points: 28294

July 15, 2010 at 10:39 am

#1193627

Hi

You can also use Performance Dashboards reports and check I/O statistics of the databases to check the number of reads and writes for the databases.

Thank You,

Best Regards,

SQLBuddy

Angelindiego SSCertifiable Points: 6048 More actions · Answer 10

Angelindiego

SSCertifiable

Points: 6048

July 15, 2010 at 10:59 am

#1193638

can you give me more details on your suggestion please???? thank you!!

Thank you!!,

Angelindiego

JC-3113 SSCrazy Eights Points: 8366 More actions · Answer 11

JC-3113

SSCrazy Eights

Points: 8366

July 15, 2010 at 11:37 am

#1193664

when i run the script i get:

Warning: Null value is eliminated by an aggregate or other SET operation.

what does that mean ?

thanks

Jim

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 12

SQLBuddy123

One Orange Chip

Points: 28294

July 15, 2010 at 12:14 pm

#1193695

Hi

Check these for installing and using SQL Server Performance Dashboard reports

http://www.mssqltips.com/tip.asp?tip=1553

http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx

Thank You,

Best Regards,

SQLBuddy

Angelindiego SSCertifiable Points: 6048 More actions · Answer 13

Angelindiego

SSCertifiable

Points: 6048

July 15, 2010 at 12:23 pm

#1193699

Yeah! Again, Thank you!!!

Thank you!!,

Angelindiego

kamran85 Newbie Points: 8 More actions · Answer 14

kamran85

Newbie

Points: 8

July 17, 2010 at 1:53 am

#1194479

Activity Monitor Is Anotherone

Paul White SSC Guru Points: 150457 More actions · Answer 15

It's not exactly what you need, but interesting and handy anyway:

My favourite way to see which databases are in use by any user sessions (even if they just have it as the context database, without running anything):



SELECT  DISTINCT

        database_name = DB_NAME(resource_database_id)

FROM    sys.dm_tran_locks TL

JOIN    sys.dm_exec_sessions S

        ON  S.session_id = TL.request_session_id

WHERE   TL.resource_type = N'DATABASE'

AND     TL.request_mode = N'S'

AND     TL.request_owner_type = N'SHARED_TRANSACTION_WORKSPACE'

AND     S.is_user_process = 1;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi