object in the tempdb database needs to be drop

Dear all,

in SQL server 2005 with SP2, if the application said "there is already an object named 't1_pur_key' in the database and we know it is an object in the tempdb and this object never disappear from tempdb, anyway we can drop it ourselves and let the application keep going ?

we may need to restart the SQL server 2005 with SP2 if we can't drop it manually.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You can only drop it, when you application logic allows it to be dropped..

The interesting questions are

Who created that object? Is it different from the object, that your application want's to create?
marrowyungSenior Technical architecture (Data)Author Commented:
"You can only drop it, when you application logic allows it to be dropped.."

ok, I can simply just try to drop it and see if it success !

"Who created that object?"
the application logic ! I remember last time once the SQL server restarted, then it is fixed.

"Is it different from the object, that your application want's to create? "

from this sense by the message, the object  already exist and the application try to create one more and it found the same object there, there error comes out !

I copy the message from the application error message prompt!
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try to drop the table or not?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

marrowyungSenior Technical architecture (Data)Author Commented:
that object. I can't see any way to find it ! I just got a name.

anyway to find the object in tempdb by name ? then it could be easlier !
marrowyungSenior Technical architecture (Data)Author Commented:
or I will need to restart the SQL server tomorrow.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just run a simple SELECT:
SELECT * FROM t1_pur_key

Open in new window


If no error then run the DROP command:
DROP TABLE t1_pur_key

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
Msg 208, Level 16, State 1, Line 1
Invalid object name 't1_pur_key'.
marrowyungSenior Technical architecture (Data)Author Commented:
how about find any object with that name? what is the command ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think you have that object created. If it was temporary then it might been deleted already.
Anyway you can run this:
SELECT *
FROM sys.sysobjects
WHERE name LIKE '%t1_pur_key%'

Open in new window

ste5anSenior DeveloperCommented:
What does this return?

USE tempdb;
GO

SELECT  T.name ,
        T.object_id ,
        T.type_desc ,
        T.create_date ,
        T.modify_date 
FROM    sys.tables T;
GO

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
victor,

"If it was temporary then it might been deleted already."

 I am guessing the same thing.

your query returns empty rows and it means this object exists!

ste5an,

I modify your query using:

USE tempdb;
GO

SELECT  T.name ,
        T.object_id ,
        T.type_desc ,
        T.create_date ,
        T.modify_date 
FROM    sys.tables T
where name LIKE '%t1_pur_key%';
GO

Open in new window

and it returns nothing.
Vitor MontalvãoMSSQL Senior EngineerCommented:
your query returns empty rows and it means this object exists!
Which query? If the 2nd one then means the object doesn't exist.
ste5anSenior DeveloperCommented:
hmm, are you sure that it is an object in TEMPDB??

EXECUTE sys.sp_MSforeachdb 'SELECT ''?'' AS db_name, T.name , T.object_id , T.type_desc , T.create_date , T.modify_date FROM ?.sys.tables T WHERE T.name LIKE ''%t1_pur_key%''';

Open in new window


or when it's not a table:

EXECUTE sys.sp_MSforeachdb 'SELECT ''?'' AS db_name, O.name , O.object_id , O.type_desc , O.create_date , O.modify_date FROM ?.sys.objects O WHERE O.name LIKE ''%t1_pur_key%''';

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
victor,

when I do it again, it output one row!

this one:
SELECT *
FROM sys.sysobjects
WHERE name LIKE '%t1_pur_key%'

Open in new window


oh, is that mean it can just drop itself and then later on create by other process?

if tomorrow the error still there how can I drop it?

ste5an,

"hmm, are you sure that it is an object in TEMPDB??"
I guess

the first query return no row for me for each DB.

the second query:

EXECUTE sys.sp_MSforeachdb 'SELECT ''?'' AS db_name, O.name , O.object_id , O.type_desc , O.create_date , O.modify_date FROM ?.sys.objects O WHERE O.name LIKE ''%t1_pur_key%''';

Open in new window


return one line from second line, so it is not a talbe but an object?

how can I drop it ? this is not MS SLQ build in object and I can safely drop it, right?
marrowyungSenior Technical architecture (Data)Author Commented:
I get back to you all 12 hours later.
ste5anSenior DeveloperCommented:
Please post the output..
marrowyungSenior Technical architecture (Data)Author Commented:
hi here:

output of this:

SELECT *
FROM sys.sysobjects
WHERE name LIKE '%t1_pur_key%'

Open in new window


is :

output 1
and output of this:

EXECUTE sys.sp_MSforeachdb 'SELECT ''?'' AS db_name, O.name , O.object_id , O.type_desc , O.create_date , O.modify_date FROM ?.sys.objects O WHERE O.name LIKE ''%t1_pur_key%''';

is

output  2
marrowyungSenior Technical architecture (Data)Author Commented:
this one:

EXECUTE sys.sp_MSforeachdb 'SELECT ''?'' AS db_name, T.name , T.object_id , T.type_desc , T.create_date , T.modify_date FROM ?.sys.tables T WHERE T.name LIKE ''%t1_pur_key%''';

return no rows
marrowyungSenior Technical architecture (Data)Author Commented:
so you can see that this object stay in the tempdb, so how to drop it ?

before dropping how can we make sure that it is not MS SQL server related internal object so that after drop we don't kill the MS SQL.
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's a constraint, not a table.
What the following query returns?
SELECT *
FROM sys.sysobjects
WHERE name LIKE '%t1%'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
E.g.

SELECT  SCHEMA_NAME(P.schema_id) AS parent_schema_name ,
        P.name AS parent_name ,
        O.name ,
        O.type_desc ,
        O.create_date ,
        O.modify_date
FROM    sys.objects O
        INNER JOIN sys.objects P ON P.object_id = O.parent_object_id
WHERE   O.name LIKE '%t1_pur_key%';

Open in new window


Then it's an
 ALTER TABLE tableName DROP CONSTRAINT yourConstraintName;

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

once restart the MS SQL service , that object gone !
Vitor MontalvãoMSSQL Senior EngineerCommented:
once restart the MS SQL service , that object gone !
Because was in tempdb. Tempdb is recreated every time SQL Server starts. This issue can happen again if you don't find the root cause of it.
marrowyungSenior Technical architecture (Data)Author Commented:
Victor yes I knew, but dropping constraint is bad to application, agree? restarting seems the best and safest way to do it. just one minutes to restart ! if dropping it and let the application malfunction, then it is bad !

it happen around 2 times per year and we just restart it.

don't you think this one : ALTER TABLE tableName DROP CONSTRAINT yourConstraintName;
can cause other issue?
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to see that's a temporary table. If you restart the SQL Service will drop the table and the constraint as well apart from disconnect all users.
You need to find the part of code that is creating the temporary table. For sure there's something missing somewhere in the code as it is trying to create the constraint again.
marrowyungSenior Technical architecture (Data)Author Commented:
"You need to see that's a temporary table. If you restart the SQL Service will drop the table and the constraint as well apart from disconnect all users."

we announce that windows.

"You need to find the part of code that is creating the temporary table. For sure there's something missing somewhere in the code as it is trying to create the constraint again."

that one is an third party application called Epicor, we can't fix any code there.
ste5anSenior DeveloperCommented:
hmm, an interesting question: what's wrong with the vendors/manufacturers support?
marrowyungSenior Technical architecture (Data)Author Commented:
"hmm, an interesting question: what's wrong with the vendors/manufacturers support?"

you mean ask them ? someone follow that up usually and he is on leave..

he will do it and I think I can suggest drop the constraint later on as originally it is not here so it should be very safe, agree  ?
ste5anSenior DeveloperCommented:
The error message indicates that there is problem in the software you're using.

While you can drop the constraint, I'm not sure whether this will not introduce new problems.

Will the new constraint be the same? When not, you're losing the uniqueness on some columns. Which may or may not be a problem.

So, I would not drop that constraint.
marrowyungSenior Technical architecture (Data)Author Commented:
"The error message indicates that there is problem in the software you're using. "

that one is Epicor ! the finance software.

"While you can drop the constraint, I'm not sure whether this will not introduce new problems. "

yeah I understand , what I mean is if this object is not in DB for a day after reboot, then I think it will be ok ?

"Will the new constraint be the same? When not, you're losing the uniqueness on some columns. Which may or may not be a problem. "

yes, will be the same as the problem is, when the application try to add this contraint, it find a same one !
ste5anSenior DeveloperCommented:
yeah I understand , what I mean is if this object is not in DB for a day after reboot, then I think it will be ok ?

No. During this timespan it is possible that duplicate values could be inserted. This can have any effect from none to all calculations are going berserk.
marrowyungSenior Technical architecture (Data)Author Commented:
no this only mean I have to restart it any time I see this !
ste5anSenior DeveloperCommented:
Do you have access to the source code? Can you prove that this the case?

Restarting the server is okay, as this is a quite normal operation. Dropping the constraint is not okay. Cause in the first case, also the table is dropped. Which is not the case when you drop the constraint manually.

Thus the CAVEAT: I would not drop the constraint. I would bother the vendor/manufacturer instead.
marrowyungSenior Technical architecture (Data)Author Commented:
"Do you have access to the source code? Can you prove that this the case?"

that DB only used by Epicor, I have no source code.

"Dropping the constraint is not okay. Cause in the first case, also the table is dropped"

dropping the contraint dropping the table too? not as easy as droping index and trigger?
ste5anSenior DeveloperCommented:
It's that plain simple: Without access to the source code of the application, you cannot decide whether dropping the constraint is okay or not.
marrowyungSenior Technical architecture (Data)Author Commented:
yes, then just restart it, new epicor will arrive at the end of this year!
marrowyungSenior Technical architecture (Data)Author Commented:
can't see why drop a constaint will drop that table too !
ste5anSenior DeveloperCommented:
Dropping the constraint, means that you now allow that duplicate values could be inserted in your table. This changes the logical model of the database.

Thus it may have an effect. Thus it's not safe.

While there is a great chance that nothing happens, there is still the possibility for failure.
marrowyungSenior Technical architecture (Data)Author Commented:
"Dropping the constraint, means that you now allow that duplicate values could be inserted in your table. This changes the logical model of the database."

but this seems controlled by primary key, right?

"While there is a great chance that nothing happens, there is still the possibility for failure."

I agree !
ste5anSenior DeveloperCommented:
No. A unique constraint adds an additional layer of uniqueness on the values. What could happen:

CREATE TABLE #Test
    (
      ID INT PRIMARY KEY ,
      Val1 INT ,
      Val2 INT ,
      CONSTRAINT UQ_Test UNIQUE ( Val1, Val2 )
    );
GO

INSERT  INTO #Test
VALUES  ( 1, 1, 1 ),
        ( 2, 2, 2 );
GO

DECLARE @ID INT;
SET @ID = (SELECT ID FROM #Test WHERE Val1=1 AND Val2 = 1);
SELECT @ID;
GO

ALTER TABLE #Test DROP CONSTRAINT UQ_Test;
GO

INSERT  INTO #Test
VALUES  ( 3, 1, 1 );
GO

DECLARE @ID INT;
SET @ID = (SELECT ID FROM #Test WHERE Val1=1 AND Val2 = 1);
SELECT @ID;
GO

DROP TABLE #Test;
GO

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
I am sorry, what are you going to show me is ?
ste5anSenior DeveloperCommented:
You are dropping the constraint, but you cannot control the timeframe when it is reestablished. In the meanwhile it is possible, that data could be entered which would violate the constraint. But there may be logic which relies on that constraint.
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, that's what I am worry about the logic behind and that's why I prefer to just restart it.

when I do your query I got :

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)


when I run the second :

DECLARE @ID INT;
SET @ID = (SELECT ID FROM #Test WHERE Val1=1 AND Val2 = 1);
SELECT @ID;
GO

it is normal ,right?
marrowyungSenior Technical architecture (Data)Author Commented:
but the point is we have already drop the constraint and this means the constraint can't control the  Val1=1 AND Val2 = 1 anymore.

then why it complain with that error ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why not run only:
SELECT ID FROM #Test WHERE Val1=1 AND Val2 = 1
ste5anSenior DeveloperCommented:
@Vitor, it's just an example, why dropping the constraint is a bad idea.
ste5anSenior DeveloperCommented:
@Marrowyung, cause the constraint ensured that this kind of scalar select works.
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, but it don't drop the table, this is also what I want to know.

yeah, this break the logic, right?
marrowyungSenior Technical architecture (Data)Author Commented:
from my point of view, dropping that means we don't want the logic anymore and I am not sure why that logic still there.
ste5anSenior DeveloperCommented:
I guess you're missing the point.

A constraint is a declaration, it is part of the logical model. Removing it, changes the entire logical model behind the database model (well, under most circumstances).

Without changing the basic table structure.

Thus T-SQL code (data retrieval and DML) in your application is only affected, when it relies on the logic and uses constructs or assumptions implied by that former model. Thus it may result in errors. The actual T-SQL code may fail or not, but this is not the point.

Another non-failing sample:

CREATE TABLE #Test
    (
      ID INT PRIMARY KEY ,
      Val1 INT ,
      Val2 INT ,
      CONSTRAINT UQ_Test UNIQUE ( Val1 )
    );
GO

INSERT  INTO #Test
VALUES  ( 1, 1, 1 ),
        ( 2, 2, 2 );
GO

SELECT  SUM(Val2) AS AverageVal
FROM    #Test
GROUP BY Val1;
GO

ALTER TABLE #Test DROP CONSTRAINT UQ_Test;
GO

INSERT  INTO #Test
VALUES  ( 3, 1, 1 );
GO

SELECT  SUM(Val2) AS AverageVal
FROM    #Test
GROUP BY Val1;
GO

DROP TABLE #Test;
GO

Open in new window


The code calculates the average of Val2 per Val1. But it relies on the assumption that Val1 is unique. Thus SUM() = AVG().

After removing the constraint, we can insert another row. But now the result is wrong.
Not a perfect sample, cause a good developer should have used AVG() instead of SUM() int the first place. But no ones perfect.

So it may introduce serious problems.. Which are later hard to track down. And may be even irreversible.
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, this time no error !

from my point of view, this is good as removing constraint and do the query again should not gives errors as the constraint gone.

I just don't understand that.

yeah value is wrong as it is a group by statement on VAl1, and val1 is not unique any more.

so you means here is that if unique constraint is not here any more business result/application result can be wrong, right? so don't drop it if it happen again ?
ste5anSenior DeveloperCommented:
so you means here is that if unique constraint is not here any more business result/application result can be wrong, right? so don't drop it if it happen again ?

Yes, that's it.

Rebooting the server is a "normal" operation. Thus the application should handle this. When not, then it is a software problem. Hopefully covered by SLA or warranty.

Dropping the constraint is not a normal operation. This may even result in a loss of warranty in the case of an error.
marrowyungSenior Technical architecture (Data)Author Commented:
:"Dropping the constraint is not a normal operation. This may even result in a loss of warranty in the case of an error. "

what I consider and said before is, I double check the constraint when the application has no problem, the constraint is not here! so this can mean we can drop the constraint when there are problem about that as it should not be here at all and by some reason it just didn't dropped by the application logic.

therefore I just drop it for the application, so this should make sense?
ste5anSenior DeveloperCommented:
I double check the constraint

This means that you have either

a) access to the entire source of the application and verified this

or

b) you have setup a test system and fuzzed the application without producing an error.

My point is: It may look simple, easy and secure to drop a constraint. But it is not. There may lurk some glitches based on assumptions or bad code in your application, which will only show up afterwards. And not even necessarily right after dropping the constraint.
marrowyungSenior Technical architecture (Data)Author Commented:
Yeah, so that only thing I can do is to restart the SQL server service and let the application continue operating !

no I don't have the code but think about that, why that constraint is not come up any more after I restart and even now !
marrowyungSenior Technical architecture (Data)Author Commented:
tks all, it seems the close button is gone ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.