Transaction Log

Question

Post reply

Transaction Log

TST1

SSCommitted

Points: 1798
More actions
July 1, 2011 at 4:58 am

#240217

Hi,

One of my developers has run a select script but he didn't save the codes. Is there a way I can pull this out from proc cache considering we use Simple mode?

Thanks

Viewing 15 posts - 1 through 15 (of 44 total)

You must be logged in to reply to this topic. Login to reply

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 1

If Code is executed then execute this script

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS ='SLEEPING'

ORDER BY CPU DESC

if code is executing then execute this script

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS !='SLEEPING'

ORDER BY CPU DESC

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Maybe, depends whether the query has flushed out of cache.

SELECT text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)

You'll have to do some filtering.

Btw, this has nothing to do with the transaction log, selects aren't logged in any way (they aren't database modifications). The caching of query plans is independent of recovery model, implemented in a different portion of the engine for completely different reasons

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Syed Jahanzaib Bin hassan (7/1/2011)

If Code is executed then execute this script

SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE STATUS ='SLEEPING'

ORDER BY CPU DESC

That requires that the connection is still open. If the developer closed the query window, then his connection is closed and won't appear in the list of connected sessions.

btw, why do you persist in writing queries against a compatibility view that is deprecated and scheduled for removal. You're teaching people bad practices.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 4

btw, why do you persist in writing queries against a compatibility view that is deprecated and scheduled for removal. You're teaching people bad practices.

is this bad practice ? what is deprecated and scheduled for removal ?

kindly send any best practices regarding this from microsoft ? I will appreciate ?

prove it this is bad one ?

My Blog
www.aureus-salah.com

TST1 SSCommitted Points: 1798 More actions · Answer 5

TST1

SSCommitted

Points: 1798

July 1, 2011 at 7:09 am

#1347004

Thanks Gail, but the guy closed his session so I guess he'll have to re-write!

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

TST1 (7/1/2011)

Thanks Gail, but the guy closed his session so I guess he'll have to re-write!

The query I gave you will work even if he's closed the session, providing the plan is still in cache. The query Syed wrote won't work if the session is closed.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Syed Jahanzaib Bin hassan (7/1/2011)

btw, why do you persist in writing queries against a compatibility view that is deprecated and scheduled for removal. You're teaching people bad practices.

is this bad practice ? what is deprecated and scheduled for removal ?

Yes, it's bad practice. Sysprocesses is deprecated, it's a compatibility view solely for old code written for SQL 2000

prove it this is bad one ?

Certainly

http://msdn.microsoft.com/en-us/library/ms179881.aspx

sys.sysprocesses (Transact-SQL)

SQL Server 2008 R2

Contains information about processes that are running on an instance of SQL Server. These processes can be client processes or system processes. To access sysprocesses, you must be in the master database context, or you must use the master.dbo.sysprocesses three-part name.

Important noteImportant

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 8

Yes, it's bad practice. Sysprocesses is deprecated, it's a compatibility view solely for old code written for SQL 2000

Practice and Recommendation are 2 different things,Here we are providing solution,that is not war for deprecated and removal ,your point is valid for the environment of development of an application not to find the solution of an issue or problem

What microsoft mentioned in your link, I think you didnt read the full para

Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Microsoft didnt say anything regarding Administration,Why DBA cant use this feature ?

@Gila you can debate better than me when we in the class of SQL Server deprecated objects but I emphasis on the solution and practical talking

My Blog
www.aureus-salah.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Oh for crying out loud....

Yes I absolutely did read the entire paragraph. Are you telling me that when DBAs write scripts they are not developing those scripts? What are they doing then? Administering the scripts? Conjuring them from thin air?

That view WILL go away in a future version, maybe even the next one. There are replacements for it that view that are fully documented, much better laid out, much clearer column names and those have been around since SQL 2005. There is no reason to be using views that are only included so that code written for SQL 2000 won't break.

Admins need to be familiar with the new DMV and need to note the deprecation so that when MS does remove those old views (and they will) the admins won't be left with all of their admin script breaking.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 10

Syed Jahanzaib Bin hassan (7/1/2011)

Yes, it's bad practice. Sysprocesses is deprecated, it's a compatibility view solely for old code written for SQL 2000

Practice and Recommendation are 2 different things,Here we are providing solution,that is not war for deprecated and removal ,your point is valid for the environment of development of an application not to find the solution of an issue or problem ....

I must be misunderstanding you. I've never before seen someone recommend hypocrisy before without it being essentially sarcastic.

Are you really saying "recommend things that you don't do" or "practice things that you recommend against"? That's what that looks like.

So, am I misunderstanding you, or is that really what you mean?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SQLRNNR SSC Guru Points: 281324 More actions · Answer 11

My 2 bits

Admins most certainly need to be familiar with the catalog views and the DMVs. The new DMVs that were designed to replace sysprocesses are many times better. The only reason sysprocesses has yet to be removed is that it provides one piece of functionality that does not yet exist in the replacement DMVs. MS is working on that one feature (http://connect.microsoft.com/SQLServer/feedback/details/257502/deprecation-of-sysprocesses-dmvs-doesnt-fully-replace-all-columns ) in theory. IMHO, that does not prevent the recoding of admin scripts to use the DMVs. Nor should that pre-empt somebody from learning the new structures.

Personally, I would replace sysprocesses where possible. Backwards compat to 2000 is not enough of a reason for me.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 12

Orlando Colamatteo

SSC Guru

Points: 182276

July 1, 2011 at 11:14 am

#1347215

Syed,

Here is an article for your review. It will give you the information you need to determine which of your stored queries should be updated:

Mapping System Tables to System Views (Transact-SQL)

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 13

TST1 (7/1/2011)

Thanks Gail, but the guy closed his session so I guess he'll have to re-write!

I did the same thing myself a couple of weeks back. This little query[/url] saved my bacon. Try it out.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 14

Grant Fritchey (7/1/2011)

TST1 (7/1/2011)

Thanks Gail, but the guy closed his session so I guess he'll have to re-write!

I did the same thing myself a couple of weeks back. This little query[/url] saved my bacon. Try it out.

yep grant i have read yours last week only.i thought to post it yours. You did it.:-)

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/