Can''t drop trigger assembly

Created an assembly based on this article...

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

Now I want to make changes and redeploy the assembly but when I try to drop I get this.

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'AuditCommon')

DROP ASSEMBLY [AuditCommon]

Msg 6590, Level 16, State 1, Line 3

DROP ASSEMBLY failed because 'AuditCommon' is referenced by object 'clr_trigger'.

Any ideas?

Figured it out. clr_trigger was a trigger that I created somewhere along the way which was dependent upon the assembly. It didn't appear anywhere in SQL Manager Studio so I thought that it was some sort of system object.|||A late reply,

using show dependencies, list out those need to removed. Remove the listed first and then drop the assembly.

Can't drop trigger assembly

Created an assembly based on this article...

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

Now I want to make changes and redeploy the assembly but when I try to drop I get this.

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'AuditCommon')

DROP ASSEMBLY [AuditCommon]

Msg 6590, Level 16, State 1, Line 3

DROP ASSEMBLY failed because 'AuditCommon' is referenced by object 'clr_trigger'.

Any ideas?

Figured it out. clr_trigger was a trigger that I created somewhere along the way which was dependent upon the assembly. It didn't appear anywhere in SQL Manager Studio so I thought that it was some sort of system object.|||A late reply,

using show dependencies, list out those need to removed. Remove the listed first and then drop the assembly.

Can't drop subscription

Either through EM or using sp_dropsubscription. Whole system locks up. I've
even tried to drop it on a per article basis. First few table/articles went
fine, but when went to drop an article for a very large table, whole system
locked up again.
--Thanks,
Kristy
try sp_dropsubscription from QA. You might want to stop your distribution,
merge, log reader agents to be able to do this. Then start them up one by
one.
How many subscribers do you have? are they push or pull? Are you running
them continuously or staggering them. If you have a large number >50 (or
perhaps even >20) use pull, and stagger your schedules, perhaps every 7 or
11 minutes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:uAVjjbIMFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Either through EM or using sp_dropsubscription. Whole system locks up.
I've
> even tried to drop it on a per article basis. First few table/articles
went
> fine, but when went to drop an article for a very large table, whole
system
> locked up again.
> --Thanks,
> Kristy
>
|||I have run the sp_dropsubscriptions from QA and it locks up. I have also run
it from QA and have been trying to select on a pure article basis. The first
15 articles went fine, but the 16th locked everything up again. This
particular table is massive, so I didn't know if this had anything to do
with it.
We have 1 publisher and 1 subscriber. distributor is currently on publisher.
THe subscriber is really just a hot backup for the publisher since we don't
have clustering. If something were to happen to pub and we couldn't bring
back up then we would switch to sub.
It is currently a push subscription, but we will be using the pull with a
new subscription. the immediate_sync is 1 (which I think means immediate)
and sync_method = 3 (which I have no idea what that means.) The agents have
not been running for 2 days as we are dropping this subscription and
starting over with a remote distributor and pull subscription. I have even
backed up the distribution database, truncated the ms_repltransactions and
ms_replcommands tables and then shrink the distribution database files.
Previously the dist db had grown to about 60 GB with the combined file size.
Now its about 4 MBs.
I have searched all over online and through the repl book and can't find
anything. I don't know what else to do!!!!
Many thanks for your help,
Kristy
we are using tran repl and I have stopped the agents 2 days ago.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%232YjarIMFHA.3080@.TK2MSFTNGP10.phx.gbl...
> try sp_dropsubscription from QA. You might want to stop your distribution,
> merge, log reader agents to be able to do this. Then start them up one by
> one.
> How many subscribers do you have? are they push or pull? Are you running
> them continuously or staggering them. If you have a large number >50 (or
> perhaps even >20) use pull, and stagger your schedules, perhaps every 7 or
> 11 minutes.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:uAVjjbIMFHA.3832@.TK2MSFTNGP12.phx.gbl...
> I've
> went
> system
>
|||I figured it out. The previous DBA had a job that put an UPDLOCK, HOLDLOCK
on the table I was trying to drop from replication. I can probably do a drop
all now instead of a per article basis.
--K
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%23a98n9IMFHA.3988@.tk2msftngp13.phx.gbl...
> I have run the sp_dropsubscriptions from QA and it locks up. I have also
run
> it from QA and have been trying to select on a pure article basis. The
first
> 15 articles went fine, but the 16th locked everything up again. This
> particular table is massive, so I didn't know if this had anything to do
> with it.
> We have 1 publisher and 1 subscriber. distributor is currently on
publisher.
> THe subscriber is really just a hot backup for the publisher since we
don't
> have clustering. If something were to happen to pub and we couldn't bring
> back up then we would switch to sub.
> It is currently a push subscription, but we will be using the pull with a
> new subscription. the immediate_sync is 1 (which I think means immediate)
> and sync_method = 3 (which I have no idea what that means.) The agents
have
> not been running for 2 days as we are dropping this subscription and
> starting over with a remote distributor and pull subscription. I have even
> backed up the distribution database, truncated the ms_repltransactions and
> ms_replcommands tables and then shrink the distribution database files.
> Previously the dist db had grown to about 60 GB with the combined file
size.[vbcol=seagreen]
> Now its about 4 MBs.
> I have searched all over online and through the repl book and can't find
> anything. I don't know what else to do!!!!
> Many thanks for your help,
> Kristy
> we are using tran repl and I have stopped the agents 2 days ago.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%232YjarIMFHA.3080@.TK2MSFTNGP10.phx.gbl...
distribution,[vbcol=seagreen]
by[vbcol=seagreen]
or
>

Cant create new database -> no disk space (os error 112)

Hello everybody

Had some problems yesterday with a full transaction log. Was able to solve it by following microsofts knowledge base article n 272318.

However, today I'm trying to create a new DB using the enterprise manager and get this error:

d:\databases\HERCULES_1_Data.MDF: Operating system error 112(Es steht nicht genug Speicherplatz auf dem Datentrger zur Verfgung.) encountered.

The free disk space is > 60GB. That's by far enough... I only need 1-2 GB.

Any ideas what's wrong here?

Thanks anyone in advance for some guideance.

RenaudAre you sure both the log and the data files were being created on D? How much space is left on your C drive?|||Yes, both files on D:\ but not created at all of course, because of the insufficient disk space error message.

However, on C:\ there's 1.6 GB left. But IMO this shouldn't matter and not be related to the error message, right?|||How are you trying to create this database? By a script, or by Enterprise Manager?|||I'm using the enterprise manager|||How big is your model database? It should be about 1 or 2 MB in size.|||According to the database properties, general tab, the model database is 1.25MB in size with 0.27MB of free space.|||huh... exec xp_fixeddrives tells me:

On C, 1395MB free space
On D, 1MB free space

How can I fix it?|||... and EXEC xp_cmdshell 'dir d:\' gives me back:

1.298.432 Bytes free

Opening a command line (DOS box) and entering "dir d:\" gives me back:

64.280.199.168 Bytes free

?!?!?|||FOUND IT!! There were disk quotas activated on our SBS 2003 Server. Didn't realize that before. Seems to be a default setting when creating new user accounts on Windows. Our SQL Server runs under its own windows account. Wasn't aware of this, now I know better!

Thanks a lot for your help.

Renaud|||Thank you for reporting back the fix to a problem the rest of us may very well encounter.