Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DMV for capturing all SQL Statements instead of just the last Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 10:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:53 AM
Points: 80, Visits: 199
Is there a way to get from a DMV not the last statement executed by a Session but all statements executed by that Session I am trying to capture what the Connection was running with blocking occurred earlier. It currently isn’t blocking and our MDW didn’t capture any transactions at that time.


Post #1404899
Posted Thursday, January 10, 2013 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 7,094, Visits: 12,582
What you see with the DMVs is the input buffer, i.e. the last batch. If you did not have a Trace or Extended Events session running then the information is no longer available.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1405362
Posted Thursday, January 10, 2013 9:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:53 AM
Points: 80, Visits: 199
Thanks. I actually found it in our MDW report when drilling down on Locks.


Post #1405522
Posted Thursday, January 10, 2013 9:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 7,094, Visits: 12,582
Nice. Thanks for posting back. Do you know if that was part of the default collection set, or a custom one you setup?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1405524
Posted Thursday, January 10, 2013 9:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:53 AM
Points: 80, Visits: 199
We had to set up Management Data Warehouse (MDW) to monitor. I was able to trace the SQL Code that was causing the blocking by drilling down the Server Acitviy Report for that time frame. There was a spike in 'locks', just had to drill down on it.


Post #1405527
Posted Thursday, January 10, 2013 10:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 7,094, Visits: 12,582
DanielP (1/10/2013)
We had to set up Management Data Warehouse (MDW) to monitor. I was able to trace the SQL Code that was causing the blocking by drilling down the Server Acitviy Report for that time frame. There was a spike in 'locks', just had to drill down on it.

Sorry if I was not clear. I meant within MDW, is this info available to you via the information collected by the Default Data Collector or do you have some Custom Data Collectors setup as well that makes this research possible?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1405539
Posted Thursday, January 10, 2013 10:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:53 AM
Points: 80, Visits: 199
Sorry, no just the defaults.


Post #1405541
Posted Thursday, January 10, 2013 10:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 7,094, Visits: 12,582
Good to know. Thanks for posting back.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1405549
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse