July 8, 2009 at 5:36 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:20 am
Duplicate post, please post answers here, http://www.sqlservercentral.com/Forums/Topic749767-338-1.aspx
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply