April 4, 2012 at 4:57 pm
Hello All,
I'm using the following query
SELECT * FROM fn_dblog(null, null)
WHERE AllocUnitName LIKE '%UserUrls%';
UserUrls is a table. I just deleted some records from it, and I want to know the exact time when that happened (Begin and End time) and by whom.
The above select has null values for the columns Begin time and End time, but not null for some other types of transactions for some other tables. The number of records in UserUrls is about 100 records with 2 columns.
Can anyone help or suggest something about how to obtain the exact time of all deletions made on that table?
Thanks in advance
IgorMi
Igor Micev,My blog: www.igormicev.com
April 4, 2012 at 5:15 pm
The begin and end are tracked by the LOP_BEGIN_XACT and (I believe) by the matching commit or rollback. So look for the begin tran and commit log records that match the delete.
The tran log won't tell you who ran the delete. It does (iirc) log the database user id, but that only helps if it's a non-sysadmin user (sysadmins are dbo, so uid = 1) and all your users have different user IDs in the database (no app login, no common user, no sysadmin or DBA)
I suggest using SQLTrace or Extended Events instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply