July 8, 2009 at 5:34 pm
I have a stored proc on a DB that runs once an hour that analyses recent web traffic. At the beginning of the proc I create a local temporary working table to use within the proc. This has been running fine for a long time however I have just noticed in the MS Agent job history that the job has been failing for the last week with the following error:
Msg 2714, Level 16, State 4, Procedure usp_sql_analyse_traffic, Line 97
There is already an object named 'PK_BATCHDATA' in the database.
Msg 1750, Level 16, State 0, Procedure usp_sql_analyse_traffic, Line 97
Could not create constraint. See previous errors.
It seems that the temp table and its related constraints have not been properly destroyed some time back and now everytime the job runs its failing as it thinks these objects are already in use. However I have tried deleting them but keep getting errors saying the objects don't exist.
A breakdown of the code, selects and errors are:
The SQL at the top of my proc which creates the temp table and PK constraint is:
CREATE TABLE #BATCHDATA(
RecordNo uniqueidentifier,
VisitorID uniqueidentifier,
Stamp datetime,
SitePk int,
SessionID int,
URL nvarchar(100),
Querystring nvarchar(max),
UserAgent nvarchar(300),
Browser varchar(50),
ClientIP varchar(50),
IsHackAttempt bit NOT NULL CONSTRAINT [BATCHDATA_IsHackAttempt] DEFAULT (0),
VisitType int
CONSTRAINT [PK_BATCHDATA] PRIMARY KEY CLUSTERED
(
[RecordNo] ASC,
[VisitorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Running the following
SELECT object_id('tempdb..#BATCHDATA')
returns NULL but this
SELECT object_id('tempdb..PK_BATCHDATA')
returns 1879717097
Also this
SELECT * FROM tempdb.sys.objects WHERE name like '%PK_BATCHDATA%'
returns a row detailing that the PK constraint does exist with a parentID = 1863717040. Looking up the object with this parentID
SELECT * FROM tempdb.sys.objects WHERE object_id=1863717040
I get details of the temp table. However trying to drop it with
DROP TABLE #BATCHDATA
OR
DROP TABLE tempdb..#BATCHDATA
errors with
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#BATCHDATA', because it does not exist or you do not have permission.
However running
SELECT * FROM tempdb.sys.objects WHERE NAME LIKE '#BATCHDATA_%'
OR
SELECT * FROM tempdb.sys.tables WHERE NAME LIKE '#BATCHDATA_%'
returns a row with name #BATCHDATA__________________________________________________________________________________________________________000000007A01
created on the 2nd of July
I cannot drop the constraint with this
ALTER TABLE tempdb..#BATCHDATA DROP CONSTRAINT PK_BATCHDATA
OR
ALTER TABLE #BATCHDATA DROP CONSTRAINT PK_BATCHDATA
as I get this error
Msg 4902, Level 16, State 1, Line 1
Cannot find the object "#BATCHDATA" because it does not exist or you do not have permissions.
Therefore it seems that the PK constraint and temporary table are still in the tempDB but I cannot delete them and its preventing the proc from running each hour.
What is the best way to remove these sticky records and why weren't they destroyed when the stored proc completed on the 2nd July. Even if the proc bombed out with an error I would have thought the local temp table and related constraints would have been destroyed.
Any help would be much appreciated.
July 9, 2009 at 7:27 am
I'd be interested to know what DBCC OPENTRAN returns when run in the database the stored procedure is in returns. It sounds like you might have a transaction that never committed or rolled back. You could also use sys.dm_tran_active_transactions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 10, 2009 at 8:25 am
Sorry about the duplicate post but when I first submitted the question I was met with a 500 error and lots of useful info about IPs,Paths,table names etc that I shouldn't really have seen.
Also I haven't been receiving email notifications about replies for some reason which I normally do so I didn't know you had replied to my post.
regarding the problem at hand:
DBCC OPENTRAN returns 0 active transactions
running
select * from
sys.dm_tran_active_transactions
returns 20 rows
19 have the name "worktable" and a transaction begin_time of 2009-06-21 08:46:27.827
They also have a transaction_type = 2 (read only) and transaction_state = 2 (active)
everything else is set to 0. T
the other row has a name of "SELECT" with todays date and if I refresh the stamp keeps changing so its something being used currently by the looks of things.
I don't know if this info is helpful or not as it and I don't know if these open/active transactions are related to the problem or not. The locked item in tempDB reports a stamp of 2nd July so the dates don't seem to match those of these open transactions.
The stored proc that created the table doesn't use any transactions itself but the proc is called by MS Agent every hour so maybe that wraps the proc in its own transaction I'm not too sure.
I am thinking of just restarting the service or rebooting the server in the dead of night to clear tempDB and then renaming the temp table and constraint in the code to get round this problem. However I would like to know how to clear this locked tempDB item without resorting to a restart if possible.
July 10, 2009 at 11:13 am
We had the same problem on your production server. Unfortunately I cannot explain the reason but you can use a workaround by creating a not named PK and DF constraint:
CREATE TABLE #BATCHDATA
(
RecordNo uniqueidentifier,
VisitorID uniqueidentifier,
Stamp datetime,
SitePk int,
SessionID int,
URL nvarchar(100),
Querystring nvarchar(max),
UserAgent nvarchar(300),
Browser varchar(50),
ClientIP varchar(50),
IsHackAttempt bit NOT NULL DEFAULT (0),
VisitType int
PRIMARY KEY CLUSTERED
(
[RecordNo] ASC,
[VisitorID] ASC
)
)
Flo
July 11, 2009 at 5:53 pm
Cheers for that Florian, I think that is the route to go down to prevent this from happening in future and I have modified my code to use non-named constraints however I still have a couple of non-clustered indexes that I add to the temp table which will have to stay.
I sure would like to know how this got in such a muddle and also how to resolve it without resorting to rewriting the code (renaming or unnaming the items) or restarting the service or server.
I can access the items in sys.objects but I cannot drop them. There must be some way of either getting permission to this object to drop it or maybe dropping objects using the object_id?? I have had a look on the web but found nothing helpful. Maybe some undocumented system function that I don't know about to kill/clear tempDB objects.
If anybody knows of such functions or methods please let me know.
July 11, 2009 at 7:08 pm
why weren't they destroyed when the stored proc completed on the 2nd July. Even if the proc bombed out with an error I would have thought the local temp table and related constraints would have been destroyed.
It appears that something went wrong in your job on July 2, such that the session is still active. As soon as that session is ended, then the temp table and it's objects will go away.
I think that there is a way that you can convert the hex code at the end of the temp table name into a session name. I haven't looked into it (might have been a nice addendum to the article I wrote on temp tables...).
Look for any sessions that are open that were opened on 7/2. Then kill the session. Your temp table (and the associated default, PK and indexes) should then go away.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 11, 2009 at 10:14 pm
I ran across this.. maybe it applies?
http://support.microsoft.com/kb/933097
See bug# 50000874
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 12, 2009 at 7:04 am
Thanks for that. The KB article does explicitly mention the exact problem I was having
50000874 When the stored procedure runs outside an explicit transaction, a temporary table in a stored procedure is not cleaned up correctly.
When I first noticed the problem I had a look at the current processes for any orphaned sessions but nothing was listed with any dates that match.
I will send the KB to the server boys and hopefully it may get installed before Christmas.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply