October 1, 2014 at 1:02 pm
Please provide details of your testing.
October 1, 2014 at 4:49 pm
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.
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
October 2, 2014 at 12:48 pm
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
March 7, 2016 at 9:56 am
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 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply