1

I left my computer last night running a batch delete in the Query Editor of Sql Server Management Studio. I was running this to test the speed of the delete on a large database before we used the script on our live database. During the night my computer restarted for an update. I am wondering if there's any way to view a log of the previous statements executed, and see the time it took to execute. It was deleting from SQL Server 2000. Thanks!

1
  • Not really, no.
    – user3914
    Dec 10, 2009 at 13:31

3 Answers 3

2

The only way to do this is by having set up some form of monitoring process prior to (and during) the operation. E.g. the Profiler tool allows you to log the duration of (and any statements, but not results) of any batch, or you could issue a SELECT or PRINT showing the results of getdate() prior to, and after, the statement.

With modification operations like a DELETE, there will be a record in the transaction log (assuming it hasn't been truncated since, with a log backup or a checkpoint in simple recovery mode), but mere mortals can't inspect the transaction log with built-in tools. Third-party log inspectors are available though, but the transaction log shows the time an operation occurred, it doesn't contain information about its duration :)

I realise that this is shutting the gate after the horse is bolted, but there's no built-in record of things like this that will survive a reboot, sorry!

1
  • Thanks for the response. Looks like I'm restoring the table and trying again!
    – user27240
    Dec 10, 2009 at 14:25
2

Maybe mere mortals can't view the log files, but a DBA can glean all kinds of information. Here are a couple queries to get you started.

-- Run this query to get the transaction ID
USE YourDatabase
GO
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName    
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'
    and AllocUnitName like '%YourTableName%'
ORDER by [transaction ID] desc

-- Run this query to find the transaction SID
SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [End Time],
    [Transaction Name],
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = 'TransactionID from above'

-- or use below to search using time instead of transaction ID

    [Begin Time] between 'starttime' and 'endtime'
AND
    [Operation] = 'LOP_BEGIN_XACT'

-- Run this to determine who ran the delete
USE MASTER
GO   
SELECT SUSER_SNAME([Transaction SID from above])
0

I know this is old, but this query is helpful to recover scripts or other SQL your ran but lost.

SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql ORDER BY execquery.last_execution_time DESC

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .