Monitoring Longest Running Transaction using SQL Server Agent Alerts

  • krystian

    Old Hand

    Points: 351

    Please provide details of your testing.

  • louislamour2673

    Mr or Mrs. 500

    Points: 584

    Hello Krystian,

    With the exception of changing @recipients and @profile_name, I ran your script just as you posted it. I ran the first part creating sp_DetectLongRunningTransactions. I didn't run the part creating the job I just tried running the script as such EXEC sp_DetectLongRunningTransactions @OpenTransactionTreshold = 30

    I'm using a test server so there is no activity on it at all. It is 2008 R2 Datacenter OS and SQL 2012 SP1 Enterprise. When I run the script with nothing running in the instance, it comes back with successful completion. I then created a test table and kicked off a query against it that will run awhile, that way I could test different thresholds etc. Anytime I executed the SP, whether before the threshold or after, it didn't matter, I would get that same conversion error. I don't have a lot of experience with coding at this point but this link seems to be related to what I'm seeing here.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f8f37d92-bb22-4c7d-a262-255c1d6682f8/msg-241-level-16-state-1-line-48-conversion-failed-when-converting-date-andor-time-from?forum=transactsql

    That's it. Here is the code I used to create a test table and query:

    --create table

    /*CREATE TABLE dbo.TestTableSize

    (

    MyKeyField VARCHAR(10) NOT NULL,

    MyDate1 DATETIME NOT NULL,

    MyDate2 DATETIME NOT NULL,

    MyDate3 DATETIME NOT NULL,

    MyDate4 DATETIME NOT NULL,

    MyDate5 DATETIME NOT NULL

    )*/

    --execute long running query

    DECLARE @RowCount INT

    DECLARE @RowString VARCHAR(10)

    DECLARE @Random INT

    DECLARE @Upper INT

    DECLARE @Lower INT

    DECLARE @InsertDate DATETIME

    SET @Lower = -730

    SET @Upper = -1

    SET @RowCount = 0

    WHILE @RowCount < 3000000

    BEGIN

    SET @RowString = CAST(@RowCount AS VARCHAR(10))

    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

    SET @InsertDate = DATEADD(dd, @Random, GETDATE())

    INSERT INTO TestTableSize

    (MyKeyField

    ,MyDate1

    ,MyDate2

    ,MyDate3

    ,MyDate4

    ,MyDate5)

    VALUES

    (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString

    , @InsertDate

    ,DATEADD(dd, 1, @InsertDate)

    ,DATEADD(dd, 2, @InsertDate)

    ,DATEADD(dd, 3, @InsertDate)

    ,DATEADD(dd, 4, @InsertDate))

    SET @RowCount = @RowCount + 1

    END

  • krystian

    Old Hand

    Points: 351

    I just modify a little your testing scenario by starting explicit transaction after creating table.

    So please execute the following

    CREATE TABLE dbo.TestTableSize

    (

    MyKeyField VARCHAR(10) NOT NULL,

    MyDate1 DATETIME NOT NULL,

    MyDate2 DATETIME NOT NULL,

    MyDate3 DATETIME NOT NULL,

    MyDate4 DATETIME NOT NULL,

    MyDate5 DATETIME NOT NULL)

    go

    --execute long running query and start explicit transaction

    BEGIN TRAN

    DECLARE @RowCount INT

    DECLARE @RowString VARCHAR(10)

    DECLARE @Random INT

    DECLARE @Upper INT

    DECLARE @Lower INT

    DECLARE @InsertDate DATETIME

    SET @Lower = -730

    SET @Upper = -1

    SET @RowCount = 0

    WHILE @RowCount < 3000000

    BEGIN

    SET @RowString = CAST(@RowCount AS VARCHAR(10))

    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

    SET @InsertDate = DATEADD(dd, @Random, GETDATE())

    INSERT INTO TestTableSize

    (MyKeyField

    ,MyDate1

    ,MyDate2

    ,MyDate3

    ,MyDate4

    ,MyDate5)

    VALUES

    (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString

    , @InsertDate

    ,DATEADD(dd, 1, @InsertDate)

    ,DATEADD(dd, 2, @InsertDate)

    ,DATEADD(dd, 3, @InsertDate)

    ,DATEADD(dd, 4, @InsertDate))

    SET @RowCount = @RowCount + 1

    END

    rollback tran

    go

    drop table TestTableSize

    go

    Then immediatelly open second SQL session to the same database where you executing your test and execute several times 'exec sp_DetectLongRunningTransactions 5' and let me know the results

  • SQL_Hunt

    SSC-Dedicated

    Points: 33267

    Instead of running it via a SQL job, it makes more sense to run it using SQL Alert as using SQL job you can only schdule to run it every 10 secs. What happens within the 10 secs there are long running tran???

    Thanks.

Viewing 4 posts - 16 through 19 (of 19 total)

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