Orphaned Trigger...

17 Aug 2006 6:57 PM

dave.seng

I have a trigger that was copied over from sql2000 to a sql2005 box
that was on the sysusers table. There were apparently 'problems' with
the trigger, and not sure if it's even possible to actually create a
trigger on the sysusers table anymore. Anyway, I can query the
sysobjects and syscomments table and see this trigger but it does not
contain any dependencies in the sysdepends table.

When I drop to drop the trigger, I get the following error:

DROP TRIGGER tr_RecordUserChanges;
Msg 211, Level 23, State 215, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any,
should be discarded.

I run DBCC CHECKCATALOG as prescribed but I get no error messages, and
I still get the same message when attempting to execute the DROP
TRIGGER statement.

If anyone could be of assistance I would greatly appreciate it.

Thanks,
Dave

17 Aug 2006 7:26 PM

dave.seng

Adds 1000 lines of info to the server log. The following lines look to
be the juciest ones.

08/17/2006 14:16:22,spid179,Unknown,Possible schema corruption. Run
DBCC CHECKCATALOG.
08/17/2006 14:16:22,spid179,Unknown,* DROP TRIGGER
tr_RecordUserChanges;
08/17/2006 14:16:22,spid179,Unknown,* Input Buffer 90 bytes -
08/17/2006 14:16:22,spid179,Unknown,* ex_raise2: Exception raised<c/>
major=2<c/> minor=11<c/> state=215<c/> severity=23
08/17/2006 14:16:22,spid179,Unknown,* 08/17/06 14:16:22 spid 179
08/17/2006 14:16:22,spid179,Unknown,* BEGIN STACK DUMP:
08/17/2006 14:16:22,spid179,Unknown,* User initiated stack dump. This
is not a server exception dump.
08/17/2006 14:16:22,spid179,Unknown,**Dump thread - spid = 179<c/> PSS
= 0x4F0C15B0<c/> EC = 0x4F0C15B8
08/17/2006 14:16:22,spid179,Unknown,Using 'dbghelp.dll' version '4.0.5'
08/17/2006 14:16:22,spid179,Unknown,ex_raise2: Exception raised<c/>
major=2<c/> minor=11<c/> state=215<c/> severity=23<c/> attempting to
create symptom dump

17 Aug 2006 8:00 PM

Michael Epprecht [MSFT]

Hi

Triggers on SQL Server System Tables have never been recommended by, or
supported by Microsoft under an version of SQL Server.
SQL Server has a new feature called "DDL triggers" for this type of
activity.

Please contact Microsoft Product Support in your Country to help you resolve
your issue.
http://www.microsoft.com/services/microsoftservices/srv_support.mspx

--
Mike

This posting is provided "AS IS" with no warranties, and confers no rights.

<dave.s***@gmail.com> wrote in message

Show quote

news:1155842806.885951.300560@74g2000cwt.googlegroups.com...
> Adds 1000 lines of info to the server log. The following lines look to
> be the juciest ones.
>
> 08/17/2006 14:16:22,spid179,Unknown,Possible schema corruption. Run
> DBCC CHECKCATALOG.
> 08/17/2006 14:16:22,spid179,Unknown,* DROP TRIGGER
> tr_RecordUserChanges;
> 08/17/2006 14:16:22,spid179,Unknown,* Input Buffer 90 bytes -
> 08/17/2006 14:16:22,spid179,Unknown,* ex_raise2: Exception raised<c/>
> major=2<c/> minor=11<c/> state=215<c/> severity=23
> 08/17/2006 14:16:22,spid179,Unknown,* 08/17/06 14:16:22 spid 179
> 08/17/2006 14:16:22,spid179,Unknown,* BEGIN STACK DUMP:
> 08/17/2006 14:16:22,spid179,Unknown,* User initiated stack dump. This
> is not a server exception dump.
> 08/17/2006 14:16:22,spid179,Unknown,**Dump thread - spid = 179<c/> PSS
> = 0x4F0C15B0<c/> EC = 0x4F0C15B8
> 08/17/2006 14:16:22,spid179,Unknown,Using 'dbghelp.dll' version '4.0.5'
> 08/17/2006 14:16:22,spid179,Unknown,ex_raise2: Exception raised<c/>
> major=2<c/> minor=11<c/> state=215<c/> severity=23<c/> attempting to
> create symptom dump
>

18 Aug 2006 12:24 PM

dave.seng

Yep, I was afraid that that was what I was going to have to do. I fear
my venture into the land of tech support.

18 Aug 2006 12:44 PM

Dan Guzman

If you have scripts (or can generate scripts) for all of your objects, you
have the option of building a new database from scratch, copying data from
the existing one.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<dave.s***@gmail.com> wrote in message

Show quote

news:1155903869.885940.258140@b28g2000cwb.googlegroups.com...
> Yep, I was afraid that that was what I was going to have to do. I fear
> my venture into the land of tech support.
>

18 Aug 2006 1:27 PM

dave.seng

That's how I actually discovered the problem. I was using RedGate's
SQLCompare, and it didn't like the scary ghost trigger.

Not a bad idea though. Thanks for the advice.