question

pharris99 avatar image
pharris99 asked

No Objects May Be Dropped from This Database

I have a 2TB inherited database with 1 filegroup. Recently I received new disk space and started creating multiple file groups and moving tables to them. Originally I was using the drop and create clustered index to move tables. I had a very large table that was taking over 8 hours to move so I stopped the process and switched to the BCP out, create cluster, drop original method and now when I try to drop an object I get Msg 50000, Level 16, State 1, Procedure TR_NO_TABLE_DROPS, Line 14 No Objects May Be Dropped from This Database Msg 3609, Level 16, State 2, Line 4 The transaction ended in the trigger. The batch has been aborted. I cannot drop any obects at this point. Any ideas?
sql-server-2008-r2
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pharris99 avatar image
pharris99 answered
My 2nd in charge answered this for me. he added a trigger last week block table drops.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

There you go. Go ahead and mark this as the answer.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
There could be some DDL triggers preventing you from dropping the objects, query sys.triggers to investigate.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
As indicated by m'learned colleagues, and as you have found yourself, this is down to a DDL trigger. There are a couple of clues that help to identify this: 1. the error number. You can tell it's not likely to be a system-generated thing by the error message number - 50000 is the default error number generated by the T-SQL command [`RAISERROR`][1]. 2. The procedure name `TR_NO_TABLE_DROPS` isn't one of the ones that Microsoft provides within SQL Server. [1]: http://msdn.microsoft.com/en-us/library/ms178592.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.