SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Back end querying
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

curiousbynature2111
Starting Member

4 Posts

Posted - 04/24/2013 :  18:23:06  Show Profile  Reply with Quote
Hi, If i execute a query using the query analyser in SQL management studio (SQL 2008 R2) is the query and or results recorded anywhere on the server?
If so how can I see that?
Many thanks for any responses.

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/24/2013 :  18:31:03  Show Profile  Visit chadmat's Homepage  Reply with Quote
The query will be stored in the procedure cache, but not the results. You can get the query text via sys.dm_exec_sql_text and passing in the sql_handle. You can get the SQL_HANDLE from sys.dm_exec_query_stats, or sys.dm_exec_requests (maybe other places)

-Chad
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 04/24/2013 :  18:34:40  Show Profile  Reply with Quote
Something like this will give you some details, but these are really management views; not stored into a table. So if you restart the server for example, you won't be able to get anything that happened before the restart.
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
YOu can filter and sort the results to fit your needs.
Go to Top of Page

curiousbynature2111
Starting Member

4 Posts

Posted - 04/24/2013 :  18:35:15  Show Profile  Reply with Quote
Many thanks Chad. Is the query stored permanently in the procedure cache?
The reason I'm asking is I found some very suspicious behavior and would like to bring it to light but because of the nature of my management I wouldn't like it known that I ran a query to confirm my suspicions. I'd end up the bad guy if you know what I mean.
Go to Top of Page

curiousbynature2111
Starting Member

4 Posts

Posted - 04/24/2013 :  18:37:34  Show Profile  Reply with Quote
Many thanks for that reply too James K
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/24/2013 :  18:45:22  Show Profile  Visit chadmat's Homepage  Reply with Quote
No, it will either be aged out of the procedure cache, or get flushed when SQL gets restarted (or the cache is flushed manually)

-Chad
Go to Top of Page

curiousbynature2111
Starting Member

4 Posts

Posted - 04/24/2013 :  18:52:55  Show Profile  Reply with Quote
Many thanks Chad. Thanks for taking the time to reply.
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/24/2013 :  18:54:06  Show Profile  Visit chadmat's Homepage  Reply with Quote
You are welcome!

-Chad
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000