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]
Go
SET NOCOUNT ON
DECLARE @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 @LSN
SET @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_HEX
SELECT [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