Transaction Log

  • 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

  • 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

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • 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
  • 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
  • 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 ?

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

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

  • 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
  • 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
  • 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

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • 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
  • 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

  • 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

  • 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

  • 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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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/

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

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