Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2013-11-06 : 08:26:13
|
| I have some databases on my server that I suspect have not been used for a long time and I would like to confirm this.Is there any way to find out when a database was last used/written to?i.e. check the last transaction. |
|
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
Posted - 2013-11-13 : 02:47:00
|
| Hi This is undocumented query I am sharing with you may be this will help you. Your database should be in full recovery mode and at-least one full backup has be taken.Note:- This query won't find any select statement executed against database. This will give only information about last insert,delete or update.Use [Your_database_name]GoSET NOCOUNT ONDECLARE @LSN NVARCHAR(46)DECLARE @LSN_HEX NVARCHAR(25)DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))DECLARE @stmt VARCHAR(256)SET @LSN = (SELECT TOP 1 [Current LSN] FROM fn_dblog(NULL, NULL))PRINT @LSNSET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'INSERT @tbl EXEC(@stmt)SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'INSERT @tbl EXEC(@stmt)SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'INSERT @tbl EXEC(@stmt)SET @LSN_HEX =(SELECT i FROM @tbl WHERE id = 1) + ':' + (SELECT i FROM @tbl WHERE id = 2) + ':' + (SELECT i FROM @tbl WHERE id = 3)PRINT @LSN_HEXSELECT [begin time],[Transaction Name],[End Time]FROM ::fn_dblog(@LSN_HEX, NULL) --where [Database Name]='offline'Where [transaction name] not in ('Backup:InvalidateDiffMaps','Backup:InvalidateDiffMaps','Backup:CommitDifferentialBase') and [begin time]<>'null' or [end time]<> 'null' In Love... With Me! |
 |
|
|
|
|
|
|
|
Check when database last used