Hi ALL
There are no connections or processes running against one
database yet I can't restore over it, detach it or drop it
because it says that it is in use. How is this possible'
Thanks
JUser DB or system DB?
>--Original Message--
>Hi ALL
>There are no connections or processes running against one
>database yet I can't restore over it, detach it or drop
it
>because it says that it is in use. How is this
possible'
>Thanks
>J
>.
>|||Make sure that you don't use the db from the connection where you want to drop it.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:045f01c3aef9$12df4e20$a501280a@.phx.gbl...
> Hi ALL
> There are no connections or processes running against one
> database yet I can't restore over it, detach it or drop it
> because it says that it is in use. How is this possible'
> Thanks
> Jsql
Showing posts with label drop. Show all posts
Showing posts with label drop. Show all posts
Tuesday, March 27, 2012
Cannot drop user. Can anyone help?
When trying to drop a user I get ' The database principle owns a schema in the database and cannot be dropped'
How Can I get around this? How can I tell which schema the user owns. It is not listed in the properties of the user.
How can I view a list of schema's and who they are owned by and how can I change ownership of schema's?|||Hi,
Which Version of Sql you are using? I assume that you are using SQL Server 2005 only. First you need to drop the schema to which the user is binded and then drop the user.
1.Open SSMS, Select your data base.
2. Select "Security" folder and expand it.
3. Select "User" folder and expand it.
4. Select and right click the user to be dropped.
5. Select "Properties" command will open "Database User" dialog box.
6. You can view the schemas which are binded to that user in the "Owned Schemas" section.
7. After determining the schema name, just cancel that dialog window.
8. Expand "Schemas" folder and select the schema that you have determined in step-4.
9. Delete/Drop that schema and then drop the user.
Hope this will help you.
Thanks & Regards,
Kiran.Y
|||in Security->Users->Right click->Properties-> I have "Schema owned by this user" and I have a check box marked on appropriate schema.
you can run too:
use yourDB
go
select schema_name,schema_owner
from information_schema.schemata
|||For changing ownership of schema see ALTER AUTHORIZATION command in Books Online
|||i have blogged this with example. See this
http://madhuottapalam.blogspot.com/search?q=Drop+Database+User+who+Owns+Schema
Madhu
Cannot drop type
Cannot drop a type event though it is not in use.
The type was created via create type and is an non-nullable nvarchar. It should have been nullable.
I have removed all dependencies on the type, by dropping tabes, stored procs etc, but still cannot drop it. SSMSE says the type cannot be dropped because it's in use. SSMSE shows no dependencies on the type.
Any help?
Can you run these queries
select object_name(object_id), * from sys.columns where user_type_id=type_id('Your_type_name')
select object_name(object_id), * from sys.parameters where user_type_id=type_id('Your_type_name')
and see if they return any rows back. If they do then there are still some dependencies left in the database on the type. The object_id is the object which has those dependencies.
Thanks
Asvin
|||Many thanks Asvin, that got it.
Turned out an old view was still referencing the type. Strange that SSMSE did not see this dependency.
|||
Also, check out this very good blog by Umachandar titled "Direct Dependencies on column"
http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx
Cannot drop type
Cannot drop a type event though it is not in use.
The type was created via create type and is an non-nullable nvarchar. It should have been nullable.
I have removed all dependencies on the type, by dropping tabes, stored procs etc, but still cannot drop it. SSMSE says the type cannot be dropped because it's in use. SSMSE shows no dependencies on the type.
Any help?
Can you run these queries
select object_name(object_id), * from sys.columns where user_type_id=type_id('Your_type_name')
select object_name(object_id), * from sys.parameters where user_type_id=type_id('Your_type_name')
and see if they return any rows back. If they do then there are still some dependencies left in the database on the type. The object_id is the object which has those dependencies.
Thanks
Asvin
|||Many thanks Asvin, that got it.
Turned out an old view was still referencing the type. Strange that SSMSE did not see this dependency.
|||
Also, check out this very good blog by Umachandar titled "Direct Dependencies on column"
http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx
Cannot drop triggers using dynamic SQL
Hi,
I'm using:
SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
I've used this on a number of occaisions but it doesn't seem to be dropping
the triggers this time.
Can anybody think of what I may be doing wrong here?
Many thanks for any assistance in advance
AntHi Ant
This SELECT will generate DROP TRIGGER statements, but it will not execute
them to actually drop the triggers. You need to take the output and execute
it.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> Hi,
> I'm using:
> SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> I've used this on a number of occaisions but it doesn't seem to be
> dropping
> the triggers this time.
> Can anybody think of what I may be doing wrong here?
> Many thanks for any assistance in advance
> Ant|||Hi Kalen,
Apoligies but you might have to walk me through that.
I tried:
exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
)
but naturally I got an error.
How is this done?
Many thanks for your answer.
Ant
"Kalen Delaney" wrote:
> Hi Ant
> This SELECT will generate DROP TRIGGER statements, but it will not execute
> them to actually drop the triggers. You need to take the output and execute
> it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> > Hi,
> > I'm using:
> >
> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >
> > I've used this on a number of occaisions but it doesn't seem to be
> > dropping
> > the triggers this time.
> >
> > Can anybody think of what I may be doing wrong here?
> >
> > Many thanks for any assistance in advance
> >
> > Ant
>
>|||Ant
DECLARE @.DeleteTrigger nvarchar(4000)
DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
FOR
SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
OPEN DeleteTrigger
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
EXEC(@.DeleteTrigger)
END
CLOSE DeleteTrigger
DEALLOCATE DeleteTrigger
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> Hi Kalen,
> Apoligies but you might have to walk me through that.
> I tried:
> exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> )
> but naturally I got an error.
> How is this done?
> Many thanks for your answer.
> Ant
>
> "Kalen Delaney" wrote:
>> Hi Ant
>> This SELECT will generate DROP TRIGGER statements, but it will not
>> execute
>> them to actually drop the triggers. You need to take the output and
>> execute
>> it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> > Hi,
>> > I'm using:
>> >
>> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >
>> > I've used this on a number of occaisions but it doesn't seem to be
>> > dropping
>> > the triggers this time.
>> >
>> > Can anybody think of what I may be doing wrong here?
>> >
>> > Many thanks for any assistance in advance
>> >
>> > Ant
>>|||Thank you.
Can this be done with Dynamic SQL? I'm sure I've done this before without
having to resort to using a Cursor(?)
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>|||Uri's solution IS dynamic SQL. Dynamic SQL means that we are building the
SQL command and then using EXEC to execute it, and he has this:
EXEC(@.DeleteTrigger)
I am assuming you are looking for a way to do it all with one statement.
You have not said what version you are using.
SQL 2000 has a procedures sp_execresultset that lets you execute the output
of another statement. This was not included in SQL 2005; I don't know why.
However, you should be able to get the definition of sp_execresultset from a
SQL 2000 server and create it in the master database of a SQL 2005 server
and get the same behavior.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:E46E8721-4815-4025-8C0E-D2092AF975DF@.microsoft.com...
> Thank you.
> Can this be done with Dynamic SQL? I'm sure I've done this before without
> having to resort to using a Cursor(?)
>
> "Uri Dimant" wrote:
>> Ant
>> DECLARE @.DeleteTrigger nvarchar(4000)
>> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
>> FOR
>> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> OPEN DeleteTrigger
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
>> IF @.@.FETCH_STATUS <> 0 BREAK
>> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
>> EXEC(@.DeleteTrigger)
>> END
>> CLOSE DeleteTrigger
>> DEALLOCATE DeleteTrigger
>>
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
>> > Hi Kalen,
>> >
>> > Apoligies but you might have to walk me through that.
>> >
>> > I tried:
>> >
>> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> > )
>> >
>> > but naturally I got an error.
>> >
>> > How is this done?
>> >
>> > Many thanks for your answer.
>> > Ant
>> >
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi Ant
>> >>
>> >> This SELECT will generate DROP TRIGGER statements, but it will not
>> >> execute
>> >> them to actually drop the triggers. You need to take the output and
>> >> execute
>> >> it.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.InsideSQLServer.com
>> >> http://sqlblog.com
>> >>
>> >>
>> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> >> > Hi,
>> >> > I'm using:
>> >> >
>> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >> >
>> >> > I've used this on a number of occaisions but it doesn't seem to be
>> >> > dropping
>> >> > the triggers this time.
>> >> >
>> >> > Can anybody think of what I may be doing wrong here?
>> >> >
>> >> > Many thanks for any assistance in advance
>> >> >
>> >> > Ant
>> >>
>> >>
>> >>
>>|||Hello Uri,
Thank you for this solution. I thought I'd exhaust all possiblities before
commiting to this. It does seem to ber the best one.
Much appreciated & sorry for the late reply
Ant
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>
I'm using:
SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
I've used this on a number of occaisions but it doesn't seem to be dropping
the triggers this time.
Can anybody think of what I may be doing wrong here?
Many thanks for any assistance in advance
AntHi Ant
This SELECT will generate DROP TRIGGER statements, but it will not execute
them to actually drop the triggers. You need to take the output and execute
it.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> Hi,
> I'm using:
> SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> I've used this on a number of occaisions but it doesn't seem to be
> dropping
> the triggers this time.
> Can anybody think of what I may be doing wrong here?
> Many thanks for any assistance in advance
> Ant|||Hi Kalen,
Apoligies but you might have to walk me through that.
I tried:
exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
)
but naturally I got an error.
How is this done?
Many thanks for your answer.
Ant
"Kalen Delaney" wrote:
> Hi Ant
> This SELECT will generate DROP TRIGGER statements, but it will not execute
> them to actually drop the triggers. You need to take the output and execute
> it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> > Hi,
> > I'm using:
> >
> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >
> > I've used this on a number of occaisions but it doesn't seem to be
> > dropping
> > the triggers this time.
> >
> > Can anybody think of what I may be doing wrong here?
> >
> > Many thanks for any assistance in advance
> >
> > Ant
>
>|||Ant
DECLARE @.DeleteTrigger nvarchar(4000)
DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
FOR
SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
OPEN DeleteTrigger
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
EXEC(@.DeleteTrigger)
END
CLOSE DeleteTrigger
DEALLOCATE DeleteTrigger
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> Hi Kalen,
> Apoligies but you might have to walk me through that.
> I tried:
> exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> )
> but naturally I got an error.
> How is this done?
> Many thanks for your answer.
> Ant
>
> "Kalen Delaney" wrote:
>> Hi Ant
>> This SELECT will generate DROP TRIGGER statements, but it will not
>> execute
>> them to actually drop the triggers. You need to take the output and
>> execute
>> it.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> > Hi,
>> > I'm using:
>> >
>> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >
>> > I've used this on a number of occaisions but it doesn't seem to be
>> > dropping
>> > the triggers this time.
>> >
>> > Can anybody think of what I may be doing wrong here?
>> >
>> > Many thanks for any assistance in advance
>> >
>> > Ant
>>|||Thank you.
Can this be done with Dynamic SQL? I'm sure I've done this before without
having to resort to using a Cursor(?)
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>|||Uri's solution IS dynamic SQL. Dynamic SQL means that we are building the
SQL command and then using EXEC to execute it, and he has this:
EXEC(@.DeleteTrigger)
I am assuming you are looking for a way to do it all with one statement.
You have not said what version you are using.
SQL 2000 has a procedures sp_execresultset that lets you execute the output
of another statement. This was not included in SQL 2005; I don't know why.
However, you should be able to get the definition of sp_execresultset from a
SQL 2000 server and create it in the master database of a SQL 2005 server
and get the same behavior.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:E46E8721-4815-4025-8C0E-D2092AF975DF@.microsoft.com...
> Thank you.
> Can this be done with Dynamic SQL? I'm sure I've done this before without
> having to resort to using a Cursor(?)
>
> "Uri Dimant" wrote:
>> Ant
>> DECLARE @.DeleteTrigger nvarchar(4000)
>> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
>> FOR
>> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> OPEN DeleteTrigger
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
>> IF @.@.FETCH_STATUS <> 0 BREAK
>> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
>> EXEC(@.DeleteTrigger)
>> END
>> CLOSE DeleteTrigger
>> DEALLOCATE DeleteTrigger
>>
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
>> > Hi Kalen,
>> >
>> > Apoligies but you might have to walk me through that.
>> >
>> > I tried:
>> >
>> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> > )
>> >
>> > but naturally I got an error.
>> >
>> > How is this done?
>> >
>> > Many thanks for your answer.
>> > Ant
>> >
>> >
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi Ant
>> >>
>> >> This SELECT will generate DROP TRIGGER statements, but it will not
>> >> execute
>> >> them to actually drop the triggers. You need to take the output and
>> >> execute
>> >> it.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >> www.InsideSQLServer.com
>> >> http://sqlblog.com
>> >>
>> >>
>> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
>> >> > Hi,
>> >> > I'm using:
>> >> >
>> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
>> >> >
>> >> > I've used this on a number of occaisions but it doesn't seem to be
>> >> > dropping
>> >> > the triggers this time.
>> >> >
>> >> > Can anybody think of what I may be doing wrong here?
>> >> >
>> >> > Many thanks for any assistance in advance
>> >> >
>> >> > Ant
>> >>
>> >>
>> >>
>>|||Hello Uri,
Thank you for this solution. I thought I'd exhaust all possiblities before
commiting to this. It does seem to ber the best one.
Much appreciated & sorry for the late reply
Ant
"Uri Dimant" wrote:
> Ant
> DECLARE @.DeleteTrigger nvarchar(4000)
> DECLARE DeleteTrigger CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT N'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> OPEN DeleteTrigger
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DeleteTrigger INTO @.DeleteTrigger
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.DeleteTrigger , 0, 1) WITH NOWAIT
> EXEC(@.DeleteTrigger)
> END
> CLOSE DeleteTrigger
> DEALLOCATE DeleteTrigger
>
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:A03F503E-6EF6-47EA-BAEB-4E52857F6F98@.microsoft.com...
> > Hi Kalen,
> >
> > Apoligies but you might have to walk me through that.
> >
> > I tried:
> >
> > exec(SELECT DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> > )
> >
> > but naturally I got an error.
> >
> > How is this done?
> >
> > Many thanks for your answer.
> > Ant
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Ant
> >>
> >> This SELECT will generate DROP TRIGGER statements, but it will not
> >> execute
> >> them to actually drop the triggers. You need to take the output and
> >> execute
> >> it.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> >> news:16961357-9B6B-4878-9E5C-47C532BAF8EA@.microsoft.com...
> >> > Hi,
> >> > I'm using:
> >> >
> >> > SELECT 'DROP TRIGGER ' + name FROM SYSOBJECTS WHERE type = 'TR'
> >> >
> >> > I've used this on a number of occaisions but it doesn't seem to be
> >> > dropping
> >> > the triggers this time.
> >> >
> >> > Can anybody think of what I may be doing wrong here?
> >> >
> >> > Many thanks for any assistance in advance
> >> >
> >> > Ant
> >>
> >>
> >>
>
>
cannot drop transactional subscription
My publisher server crashed and will not be coming back. I have to turn one
of the subscribing servers into the new publisher, but it still thinks it's
subscriber to the publisher that crashed.
Documentation says you can only drop the subscriptions from the publisher?
Now what?
JD,
running sp_removedbreplication should remove any remaining references.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Why do you say the subscriber cum publisher still thinks it is the
subscriber? What is the error message you are getting?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JD" <JD@.discussions.microsoft.com> wrote in message
news:66F31D84-0CEA-49C0-8A5D-970821275954@.microsoft.com...
> My publisher server crashed and will not be coming back. I have to turn
one
> of the subscribing servers into the new publisher, but it still thinks
it's
> subscriber to the publisher that crashed.
> Documentation says you can only drop the subscriptions from the
publisher?
> Now what?
sql
of the subscribing servers into the new publisher, but it still thinks it's
subscriber to the publisher that crashed.
Documentation says you can only drop the subscriptions from the publisher?
Now what?
JD,
running sp_removedbreplication should remove any remaining references.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Why do you say the subscriber cum publisher still thinks it is the
subscriber? What is the error message you are getting?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JD" <JD@.discussions.microsoft.com> wrote in message
news:66F31D84-0CEA-49C0-8A5D-970821275954@.microsoft.com...
> My publisher server crashed and will not be coming back. I have to turn
one
> of the subscribing servers into the new publisher, but it still thinks
it's
> subscriber to the publisher that crashed.
> Documentation says you can only drop the subscriptions from the
publisher?
> Now what?
sql
Cannot drop the table transactional replication error 3724
This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?
--
I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.
In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:
Command attempted:
drop Table "dbo"."MyReplTable"
(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)
Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)
I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.
I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)
Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.
If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).
/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO
use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--
The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.
Maybe I should ask what your business needs are, and then we can discuss a solution.
|||This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.
The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.
I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.
|||Hi,
From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.
So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.
Peng
|||Peng,
For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.
This is the way we are actually doing things in production now, in SQL 2000, without error.
Paul
|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.Cannot drop the table transactional replication error 3724
This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?
--
I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.
In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:
Command attempted:
drop Table "dbo"."MyReplTable"
(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)
Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)
I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.
I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)
Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.
If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).
/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO
use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--
The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.
Maybe I should ask what your business needs are, and then we can discuss a solution.
|||This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.
The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.
I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.
|||Hi,
From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.
So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.
Peng
|||Peng,
For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.
This is the way we are actually doing things in production now, in SQL 2000, without error.
Paul
|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.Sunday, March 25, 2012
Cannot drop the table "xyz" as it is being used for replication
I have a replication setup running on 2 Win2K Server PCs with SQL
Server 2000.
Both the PCs have a common DB installed on them.
The setup takes care of starting one PC as primary and other as
backup.
It also starts the SQL replication (transactional), where both the PCs
are configured as publisher, distributor and subscriber.
Now the replication service creates a publication ONLY ON
PRIMARY and a subscription ONLY ON BACKUP. The replication starts.
When a switch-over of primary and backup is done, the existing
publication and subscription are deleted. A new publication is created
on new PRIMARY and a new subscription on the new BACKUP. Again,
replication works without a problem.
HOWEVER, sometimes, (I'm not able to reproduce & it is very
infrequent) on a switch-over I get thee error--"Cannot drop the table
"xyz" as it is being used for replication." The table is the one I
used for replication. Also, the primary keys exist on this table in
this case even though it's a part of subscription. The only solution
till now is to delete the entire replication on both machines and
re-create it.
I'm not using this DB anywhere else for any other purpose. Any
clues, why am I getting this problem occasionaly?
Thanks in advance.
Nelabh,
it's sometimes the case that some replication metadata is left hanging
around in system tables. sp_removedbreplication usually fixes these issues,
or the more granular sp_MSunmarkreplinfo may also be of interest.
HTH,
Paul Ibison
Server 2000.
Both the PCs have a common DB installed on them.
The setup takes care of starting one PC as primary and other as
backup.
It also starts the SQL replication (transactional), where both the PCs
are configured as publisher, distributor and subscriber.
Now the replication service creates a publication ONLY ON
PRIMARY and a subscription ONLY ON BACKUP. The replication starts.
When a switch-over of primary and backup is done, the existing
publication and subscription are deleted. A new publication is created
on new PRIMARY and a new subscription on the new BACKUP. Again,
replication works without a problem.
HOWEVER, sometimes, (I'm not able to reproduce & it is very
infrequent) on a switch-over I get thee error--"Cannot drop the table
"xyz" as it is being used for replication." The table is the one I
used for replication. Also, the primary keys exist on this table in
this case even though it's a part of subscription. The only solution
till now is to delete the entire replication on both machines and
re-create it.
I'm not using this DB anywhere else for any other purpose. Any
clues, why am I getting this problem occasionaly?
Thanks in advance.
Nelabh,
it's sometimes the case that some replication metadata is left hanging
around in system tables. sp_removedbreplication usually fixes these issues,
or the more granular sp_MSunmarkreplinfo may also be of interest.
HTH,
Paul Ibison
Cannot drop the distribution database 'distribution' because it is currently in use.
All,
I use stored procedures from C#.net to configure and remove
replication.
Whenever, i remove replication i try to first check if any process for
distribution DB (using sp_who2) is remaining in the SQL process space
and kill it.
The possible processes, i knew of using the distribution DB are:
1. SQL .NET data client provider
2. MSSQL EM
3. SQL Query Analyzer
I also checked that no cleanup processes are using it.
But still i get the error "Cannot drop the distribution database
'distribution' because it is currently in use."
Can anyone help in finding out who else is using the Distribution DB?
Please reply asap!
Thanks in advance,
Lavanya
It could be the distribution cleanup agent, the log reader, the distribition
agent or the merge agent. For a user database you would make it single-user
with yourself as the only user then drop it: Alter database distribution set
single_user with rollback immediate; use tempdb; drop database distribution.
For the distributor I'd run sp_dropdistributor followed by
sp_dropdistributiondb rather than the "drop database".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Try this select * From sys.sysprocesses where dbid=db_id('distribution')
in sql 2005 in sql 2000, try this
select * From sysprocesses where dbid=db_id('distribution')
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
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1168258285.713911.183170@.s80g2000cwa.googlegr oups.com...
> All,
> I use stored procedures from C#.net to configure and remove
> replication.
> Whenever, i remove replication i try to first check if any process for
> distribution DB (using sp_who2) is remaining in the SQL process space
> and kill it.
> The possible processes, i knew of using the distribution DB are:
> 1. SQL .NET data client provider
> 2. MSSQL EM
> 3. SQL Query Analyzer
> I also checked that no cleanup processes are using it.
> But still i get the error "Cannot drop the distribution database
> 'distribution' because it is currently in use."
> Can anyone help in finding out who else is using the Distribution DB?
> Please reply asap!
> Thanks in advance,
> Lavanya
>
I use stored procedures from C#.net to configure and remove
replication.
Whenever, i remove replication i try to first check if any process for
distribution DB (using sp_who2) is remaining in the SQL process space
and kill it.
The possible processes, i knew of using the distribution DB are:
1. SQL .NET data client provider
2. MSSQL EM
3. SQL Query Analyzer
I also checked that no cleanup processes are using it.
But still i get the error "Cannot drop the distribution database
'distribution' because it is currently in use."
Can anyone help in finding out who else is using the Distribution DB?
Please reply asap!
Thanks in advance,
Lavanya
It could be the distribution cleanup agent, the log reader, the distribition
agent or the merge agent. For a user database you would make it single-user
with yourself as the only user then drop it: Alter database distribution set
single_user with rollback immediate; use tempdb; drop database distribution.
For the distributor I'd run sp_dropdistributor followed by
sp_dropdistributiondb rather than the "drop database".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Try this select * From sys.sysprocesses where dbid=db_id('distribution')
in sql 2005 in sql 2000, try this
select * From sysprocesses where dbid=db_id('distribution')
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
"Lavanya" <lavanyah@.gmail.com> wrote in message
news:1168258285.713911.183170@.s80g2000cwa.googlegr oups.com...
> All,
> I use stored procedures from C#.net to configure and remove
> replication.
> Whenever, i remove replication i try to first check if any process for
> distribution DB (using sp_who2) is remaining in the SQL process space
> and kill it.
> The possible processes, i knew of using the distribution DB are:
> 1. SQL .NET data client provider
> 2. MSSQL EM
> 3. SQL Query Analyzer
> I also checked that no cleanup processes are using it.
> But still i get the error "Cannot drop the distribution database
> 'distribution' because it is currently in use."
> Can anyone help in finding out who else is using the Distribution DB?
> Please reply asap!
> Thanks in advance,
> Lavanya
>
Cannot drop the database 'RMAModule' because it is currently in use
How can I stop getting this error when I try to delete a database
dynamically?
I am using this code...
DROP DATABASE RMAModule
If I just wait a while and try it again it will work - not urgent, but a
hastle. Thanks!!Hi,
Execute the below code from Query Analyzer,
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
drop database dbname
Replace the dbname with the actual dbname which you have to drop.
Thanks
Hari
MCDBA
"michaaal" <res0gyio@.verizon.net> wrote in message
news:#EwrBBlDEHA.2424@.TK2MSFTNGP09.phx.gbl...
> How can I stop getting this error when I try to delete a database
> dynamically?
> I am using this code...
> DROP DATABASE RMAModule
> If I just wait a while and try it again it will work - not urgent, but a
> hastle. Thanks!!
>sql
dynamically?
I am using this code...
DROP DATABASE RMAModule
If I just wait a while and try it again it will work - not urgent, but a
hastle. Thanks!!Hi,
Execute the below code from Query Analyzer,
use master
go
alter database dbname set RESTRICTED_USER with rollback immediate
go
drop database dbname
Replace the dbname with the actual dbname which you have to drop.
Thanks
Hari
MCDBA
"michaaal" <res0gyio@.verizon.net> wrote in message
news:#EwrBBlDEHA.2424@.TK2MSFTNGP09.phx.gbl...
> How can I stop getting this error when I try to delete a database
> dynamically?
> I am using this code...
> DROP DATABASE RMAModule
> If I just wait a while and try it again it will work - not urgent, but a
> hastle. Thanks!!
>sql
Cannot drop Temporary Table linked to Connection
Hi there!
I have a Java-Servlet that opens a connection to a SQL-Server 2000
With this connection I create a temporary local table.
I can call the temporary table within this connection as often as I want ... it's there as long as the connection is open. It's not dropped automatically as long as the connection is open. But when want to drop that table MANUALLY, I get a
[Microsoft][SQLServer JDBC Driver][SQLServer]Cannot drop the table '#tablename', because it does not exist in the system catalog.
I don't use any stored procedures that could drop the table before I do.
The database is accessed only by "normal" sql-statements through the JDBC-Driver. I can read the content of the table 10 times but if I try to drop it just in the next line, I get the above error message.
Now I know that the table will be dropped as soon as I close the connection, but anyway it's safer to do it manually ... so has somebody an idea?
Thanks
LookDo you need repeated access to the temp table or is it used just once ? Also, can you send the code you are using ? Is it straight sql or are you using a stored procedure (it appears that you are doing straight sql)?|||Found this in MSDN. Look for the article "Coding T-SQL like an Expert"
IF (object_id('tempdb..#mytemp') IS not Null)
Drop Table #mytemp
Although `tempdb..#authors_temp' is a truncation of the actual physical name of the object as held in tempdb..sysobjects, somehow OBJECT_ID() recognizes that you mean "your" temporary table called #mytemp.
I have a Java-Servlet that opens a connection to a SQL-Server 2000
With this connection I create a temporary local table.
I can call the temporary table within this connection as often as I want ... it's there as long as the connection is open. It's not dropped automatically as long as the connection is open. But when want to drop that table MANUALLY, I get a
[Microsoft][SQLServer JDBC Driver][SQLServer]Cannot drop the table '#tablename', because it does not exist in the system catalog.
I don't use any stored procedures that could drop the table before I do.
The database is accessed only by "normal" sql-statements through the JDBC-Driver. I can read the content of the table 10 times but if I try to drop it just in the next line, I get the above error message.
Now I know that the table will be dropped as soon as I close the connection, but anyway it's safer to do it manually ... so has somebody an idea?
Thanks
LookDo you need repeated access to the temp table or is it used just once ? Also, can you send the code you are using ? Is it straight sql or are you using a stored procedure (it appears that you are doing straight sql)?|||Found this in MSDN. Look for the article "Coding T-SQL like an Expert"
IF (object_id('tempdb..#mytemp') IS not Null)
Drop Table #mytemp
Although `tempdb..#authors_temp' is a truncation of the actual physical name of the object as held in tempdb..sysobjects, somehow OBJECT_ID() recognizes that you mean "your" temporary table called #mytemp.
Cannot Drop Table?
I have a table that is not functioning properly, so I have recreated it under a different name. Now I want to delete the problem table but it wont let me. This table has no dependencies, but I still can't drop it.
Here is the error message:
Lock request Time out period exceeded, error 1222
Any ideas on how to drop this table?
Did you check for locks with sp_who2 or Activity Monitor/Process Info (2005) Current Activity/Process Info (2000)? If there is a lock KILL the SPID(connection).
See:
http://msdn2.microsoft.com/en-gb/library/aa213032(SQL.80).aspx
|||check whether this table is reference by any other table.... use sp_help/sp_fkeys etc....
Run DBCC CheckTable to check for any corruption.
Madhu
Cannot Drop Table
I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.
What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com
|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>
|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.
What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com
|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>
|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>
Cannot Drop Table
I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>> I have a table with no dependencies. I cannot drop the table. I
>> tried from EM to delete it. I tried from Query Analyzer. I cannot
>> even delete an index that it has. It's not a big table and it only
>> has about 6,000 rows. Any ideas where to look? Thanks.
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>> I have a table with no dependencies. I cannot drop the table. I
>> tried from EM to delete it. I tried from Query Analyzer. I cannot
>> even delete an index that it has. It's not a big table and it only
>> has about 6,000 rows. Any ideas where to look? Thanks.
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>
Cannot Drop Table
I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>sql
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>sql
Cannot drop stored proc
I use sp_rename to rename several stored procedures.
sp_rename 'MyDB.dbo.spRPTKCTicketCount', ''MyDB.dbo.spRPTKCTicketCount_obsol
ete'
The message was:The object was renamed to 'MyDB.dbo.spRPTKCTicketCount'_obso
lete'.
Caution: Changing any part of an object name could break scripts and stored
procedures.
But now, I cannot drop the stored procedure because it was stored in sysobje
cts as 'MyDB.dbo.spRPTKCTicketCount'_obsolete'
So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an erro
r saying that
"Server: Msg 166, Level 15, State 1, Line 1
'DROP PROCEDURE' does not allow specifying the database name as a prefix to
the object name"
and if I take the Database name out of it, it says that it cannot find the i
t in the system catalogs.
Please help...Hi Jorge
Can you try putting the name in square brackets?
DROP PROCEDURE [MyDB.dbo.spRPTKCTicketCount_obsolete]
If that doesn't work, see if you can change the name again, to something
simpler.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:6A32D11A-2EFE-48BA-B61F-F1004EB1F446@.microsoft.com...
> I use sp_rename to rename several stored procedures.
> sp_rename 'MyDB.dbo.spRPTKCTicketCount',
''MyDB.dbo. spRPTKCTicketCount_obsolete'ed">
> The message was:The object was renamed to
'MyDB.dbo.spRPTKCTicketCount'_obsolete'.
> Caution: Changing any part of an object name could break scripts and
stored procedures.
> But now, I cannot drop the stored procedure because it was stored in
sysobjects as 'MyDB.dbo. spRPTKCTicketCount'_obsolete'red">
> So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an
error saying that
> "Server: Msg 166, Level 15, State 1, Line 1
> 'DROP PROCEDURE' does not allow specifying the database name as a prefix
to the object name"
> and if I take the Database name out of it, it says that it cannot find the
it in the system catalogs.
> Please help...
>|||Thank you...the brackets seem to work.
Actually, I'm impressed and surprised. I never thought that the author of Sq
l Server book would respond to me. It's an honor.
Jorge Chirinos
jorge_chirinos@.hotmail.com
sp_rename 'MyDB.dbo.spRPTKCTicketCount', ''MyDB.dbo.spRPTKCTicketCount_obsol
ete'
The message was:The object was renamed to 'MyDB.dbo.spRPTKCTicketCount'_obso
lete'.
Caution: Changing any part of an object name could break scripts and stored
procedures.
But now, I cannot drop the stored procedure because it was stored in sysobje
cts as 'MyDB.dbo.spRPTKCTicketCount'_obsolete'
So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an erro
r saying that
"Server: Msg 166, Level 15, State 1, Line 1
'DROP PROCEDURE' does not allow specifying the database name as a prefix to
the object name"
and if I take the Database name out of it, it says that it cannot find the i
t in the system catalogs.
Please help...Hi Jorge
Can you try putting the name in square brackets?
DROP PROCEDURE [MyDB.dbo.spRPTKCTicketCount_obsolete]
If that doesn't work, see if you can change the name again, to something
simpler.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jorge" <anonymous@.discussions.microsoft.com> wrote in message
news:6A32D11A-2EFE-48BA-B61F-F1004EB1F446@.microsoft.com...
> I use sp_rename to rename several stored procedures.
> sp_rename 'MyDB.dbo.spRPTKCTicketCount',
''MyDB.dbo. spRPTKCTicketCount_obsolete'ed">
> The message was:The object was renamed to
'MyDB.dbo.spRPTKCTicketCount'_obsolete'.
> Caution: Changing any part of an object name could break scripts and
stored procedures.
> But now, I cannot drop the stored procedure because it was stored in
sysobjects as 'MyDB.dbo. spRPTKCTicketCount'_obsolete'red">
> So when I use drop 'MyDB.dbo.spRPTKCTicketCount'_obsolete' it return an
error saying that
> "Server: Msg 166, Level 15, State 1, Line 1
> 'DROP PROCEDURE' does not allow specifying the database name as a prefix
to the object name"
> and if I take the Database name out of it, it says that it cannot find the
it in the system catalogs.
> Please help...
>|||Thank you...the brackets seem to work.
Actually, I'm impressed and surprised. I never thought that the author of Sq
l Server book would respond to me. It's an honor.
Jorge Chirinos
jorge_chirinos@.hotmail.com
Cannot DROP SERVICE
I'm having trouble dropping a Service Broker Seriver using the DROP SERVICE command.
After about 15 minutes, my SQL Server 2005 Standard edition replies with the following message:
Msg 701, Level 17, State 171, Line 1
There is insufficient system memory to run this query.
Is there another way to remove a service? I've tried restarting SQL, but this does not work.
Open a second connection to the server and please run this command:
dbcc memorystatus
Run it one before the DROP statement is issued, one or twice while is running (e.g after 5 minutes and after 10 minutes) and then once when the error occurs. Save the output each time into a file and please send me this output to remus.rusanu@.microsoft.com
This way I hope to identify the problem.
Thanks,
~ Remus
<before>
Memory Manager KB
--
VM Reserved 1064060
VM Committed 693280
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
--
VM Reserved 1059964
VM Committed 689336
AWE Allocated 0
MultiPage Allocator 7704
SinglePage Allocator 159856
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1592
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 677824
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4088
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 984
MultiPage Allocator 5320
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 816
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 6120
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 864
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 39496
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 368
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 224
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 40
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 34984
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51608
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
--
Stolen 3016
Free 49283
Cached 16966
Database (clean) 3079
Database (dirty) 11117
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
--
Committed 83462
Target 89991
Hashed 14197
Stolen Potential 103236
External Reservation 0
Min Free 32
Visible 89991
Available Paging File 178838
(8 row(s) affected)
Procedure Cache Value
--
TotalProcs 84
TotalPages 980
InUsePages 11
(3 row(s) affected)
Global Memory Objects Buffers
--
Resource 145
Locks 6454
XDES 461
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 33
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 62344
Maximum (Buffers) 62344
Limit 62344
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 65625
(11 row(s) affected)
Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 3281
Maximum (Buffers) 3281
Limit 3281
(5 row(s) affected)
Optimization Queue Value
--
Overall Memory 575897600
Target Memory 470007808
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 16967
Rate 0
Target Allocations 70300
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3010
Rate 0
Target Allocations 57374
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate 0
Target Allocations 70014
Future Allocations 15650
Last Notification 1
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</before>
Here is the 7 minute report.
<7min>
Memory Manager KB
--
VM Reserved 1067708
VM Committed 1055632
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
--
VM Reserved 1063612
VM Committed 1051688
AWE Allocated 0
MultiPage Allocator 11320
SinglePage Allocator 974488
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 1592
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 1036560
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4096
MultiPage Allocator 3688
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 240
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 944
MultiPage Allocator 5320
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 10072
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 145136
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 64
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 160
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 742048
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51512
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
--
Stolen 3012
Free 274
Cached 118808
Database (clean) 1899
Database (dirty) 4308
I/O 1
Latched 2
(7 row(s) affected)
Buffer Counts Buffers
--
Committed 128304
Target 128304
Hashed 6212
Stolen Potential 1421
External Reservation 0
Min Free 32
Visible 128304
Available Paging File 133742
(8 row(s) affected)
Procedure Cache Value
--
TotalProcs 1
TotalPages 1281
InUsePages 0
(3 row(s) affected)
Global Memory Objects Buffers
--
Resource 146
Locks 6442
XDES 914
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 31
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 5285
Maximum (Buffers) 5285
Limit 5240
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 5880
(11 row(s) affected)
Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 640
Maximum (Buffers) 640
Limit 640
(5 row(s) affected)
Optimization Queue Value
--
Overall Memory 822476800
Target Memory 51404800
Last Notification 0
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 118885
Rate -706
Target Allocations 82639
Future Allocations 0
Last Notification 2
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3004
Rate -3
Target Allocations 6275
Future Allocations 0
Last Notification 0
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate -96
Target Allocations 6275
Future Allocations 22354
Last Notification 0
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</7min>
Here is the after report.
<after>
Memory Manager KB
--
VM Reserved 1064060
VM Committed 755880
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
--
VM Reserved 1059964
VM Committed 751936
AWE Allocated 0
MultiPage Allocator 7704
SinglePage Allocator 154824
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 872
MultiPage Allocator 1592
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
- --
VM Reserved 1047772
VM Committed 740424
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 256
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 144
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
- --
VM Reserved 480
VM Committed 480
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
- --
VM Reserved 1024
VM Committed 1024
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4088
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 224
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 17512
MultiPage Allocator 192
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 976
MultiPage Allocator 5320
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 152
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 56
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 50480
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 104
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 424
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 160
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 208
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
- --
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 27064
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
- --
VM Reserved 2048
VM Committed 2048
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 51560
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
--
Stolen 3012
Free 58046
Cached 16341
Database (clean) 1591
Database (dirty) 12296
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
--
Committed 91287
Target 97284
Hashed 13888
Stolen Potential 103865
External Reservation 0
Min Free 32
Visible 97284
Available Paging File 170966
(8 row(s) affected)
Procedure Cache Value
--
TotalProcs 2
TotalPages 29
InUsePages 0
(3 row(s) affected)
Global Memory Objects Buffers
--
Resource 146
Locks 6448
XDES 461
SETLS 1
SE Dataset Allocators 2
SubpDesc Allocators 1
SE SchemaManager 52
SQLCache 31
Replication 2
ServerGlobal 25
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 68529
Maximum (Buffers) 68529
Limit 67688
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 71250
(11 row(s) affected)
Small Query Memory Objects Value
--
Grants 0
Waiting 0
Available (Buffers) 3605
Maximum (Buffers) 3605
Limit 3605
(5 row(s) affected)
Optimization Queue Value
--
Overall Memory 622592000
Target Memory 524369920
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
--
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
--
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-- --
Allocations 16342
Rate 0
Target Allocations 76000
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-- --
Allocations 3006
Rate 0
Target Allocations 64010
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-- --
Allocations 0
Rate 0
Target Allocations 76000
Future Allocations 16922
Last Notification 1
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
</after>
|||
DROP SERVICE has to send an END CONVERSATION to each conversation owned by that service. Apparently, the system in question cannot handle this load in one single transaction.
To work around, end all the conversations first, using a cursor over sys.conversation_endpoints:
declare crsConversations cursor for
select conversation_handle
from sys.conversation_endpoints e
join sys.services s on s.service_id = e.service_id
where s.name = 'myservicename';
open crsConversations;
declare @.dh uniqueidentifier;
declare @.batch int;
select @.batch = 0;
begin transaction
fetch next from crsConversations into @.dh;
while @.@.fetch_status =0
begin
-- replace this with end conversation @.dh with error ... if
-- and error has to be sent to the peer
--
end conversation @.dh with cleanup;
-- commit every 1000 conversations ended
select @.batch = @.batch + 1;
if @.batch > 999
begin
commit transaction;
begin transaction;
end
fetch next from crsConversations into @.dh;
end
commit transaction;
close crsConversations;
deallocate crsConversations;
go
drop service 'myservicename'
go
Subscribe to:
Posts (Atom)