Unable to drop user tables in Master Database

Question

Post reply

Unable to drop user tables in Master Database

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
September 7, 2011 at 3:32 am

#247428

Hi All,
In one of my instances I am unable to drop the user tables in Master database.
When I am trying to drop the table receiving below error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Table 'dbo.fragmentation_info1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Table&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The transaction ended in the trigger. The batch has been aborted.
dropped (Microsoft SQL Server, Error: 3609)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3609&LinkId=20476
------------------------------
BUTTONS:
Can you please help me in this.
Thank You.
Regards,
Raghavender Chavva

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Raghavender (9/7/2011)
...The transaction ended in the trigger. The batch has been aborted...

A trigger is causing this. Check the trigger.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

You probably have a DDL trigger in place. Disable the trigger and you should be fine.

-- Gianluca Sartori

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 3

There is no triggers on that table.

Thank You.

Regards,
Raghavender Chavva

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Raghavender (9/7/2011)
There is no triggers on that table.

Check for Database Triggers. DDL triggers are on database, not on table.

Please run this query:

select * from sys.triggers

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 5

I have checked that but no triggers are there.

Thank You.

Regards,
Raghavender Chavva

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Try checking for server triggers.

USE MASTER;

Go

SELECT * FROM sys.server_triggers AS ST

SELECT * FROM sys.triggers AS T

The error clearly says that there is a trigger involved that is not allowing dropping of tables.

Try running the drop in T-SQL instead of the GUI and you might get better error reporting.

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

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 7

If I run above script its showing no triggers related to that table.

🙁

Thank You.

Regards,
Raghavender Chavva

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

It is returning 0 rows?

It is a database level trigger. It is common for all the tables. Not just related to that table.

You can check using SSMS UI also. Under <database>...Programmability...Database Triggers.

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 9

Suresh B. (9/8/2011)
It is returning 0 rows?
It is a database level trigger. It is common for all the tables. Not just related to that table.
You can check using SSMS UI also. Under <database>...Programmability...Database Triggers.

Using query also its not showing any triggers and by GUI also we are unable to find any triggers.

Thank You.

Regards,
Raghavender Chavva

Suresh B. SSC-Insane Points: 22986 More actions · Answer 10

I guess, you do not have enough permissions to view the database level triggers...

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 11

We are using 'sa' to login into the SQL Server.

Thank You.

Regards,
Raghavender Chavva

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

You could trace the process with profiler, using the "tuning" built-in template.

You should be able to capture alle the statements executed in the session.

-- Gianluca Sartori