Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Someone was running a query on our SQL Server database remotely and their system crashed.

They have no backup of that query and want to see what was run on the server.

Is it possible to find this query in a log or in a history somewhere?

share|improve this question
    
Not in a log. Checked into a TFS or SourceSafe version control system? Result set off to a txt so it can be recreated? –  jl01 Jul 22 '11 at 20:49
2  
For future design you might want to consider adding triggers and audit/history tables. Then would would be able to utilize last updated time/user. –  Thomas Stringer Jul 23 '11 at 11:53

4 Answers 4

up vote 8 down vote accepted

Similar Grant Fritchey had the issue where he had closed SSMS and lost the query he had been working on...blogged about here: Oh ****!

share|improve this answer
1  
That's a nice article! Thanks! And following Grant's article, Recover Backed-up query files in SQL Server Management Studio might be helpful. –  Marian Jul 22 '11 at 21:59

You might be able to retrieve info from cached query plans, check BOL for info on sys.dm_exec_query_stats, or run this from management studio connected to the same database:

SELECT  d.plan_handle ,
        d.sql_handle ,
        e.text

FROM    sys.dm_exec_query_stats d
        CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e

Filter the output with

WHERE text like '%something%'

to narrow the results.

share|improve this answer

If database was in full recovery mode then there might be a chance to recover some data and get insights on what was done by reading transaction log.

Unfortunately this is not supported by default but there are ways to do this.

You can try using third party tools such as ApexSQL Log or SQL Log Rescue (free but SQL 2000 only).

Another option is to try using undocumented functions DBCC LOG or fn_dblog. This is more complex but its free.

share|improve this answer

2005+, default trace to the rescue.

The default trace rolls over at 20mb but SQL retains the history of 5 traces. With access to the server you could retrieve the *.trc files from the MSSQL\Log directory. If you can't access the server, the following will give you the name of the current default trace file:

SELECT * FROM ::fn_trace_getinfo(default) 

If the current file is for example E:\MSSQL.1\MSSQL\LOG\log_200.trc, the previous files should be log_199.trc, log_198.trc etc. Get the contents of the trace with:

SELECT * FROM fn_trace_gettable('E:\MSSQL.1\MSSQL\LOG\log_199.trc', default)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.