connect from ASP app: indexes

Showing posts with label indexes. Show all posts

Sunday, March 11, 2012

Can't delete filegroup, even though it is empty

Hi All,
I have a SQL 2000 database that I had created a seperate file group for to
hold indexes. I have since deleted the indexes and want to delete the file
groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
<filegroupname> I receive an error indicating that 'The filegroup <filegroup>
cannot be removed because it is not empty'
If have verified that there are no tables or indexes in this filegroup and
have also run the following below queries to verify that the given filegroups
are empty:
select name,groupid
from sysindexes
where groupid=filegroup_id(<groupname>)
and indid > 0
Although there are no objects residing in the actual devices, they are still
listed in sysfiles. As such, even if I try to detach and reattach the DB,
the files are still linked and loaded with the database.
The tables to which the indexes existed are very large, so I also
stopped/restarted the sql services (thinking that possibly something may have
been stuck in memory) however that did not resolve the problem.
Has aonyone encountered this before? Is anyone aware of a fix for this?
Thanks,You can try using DBCC SHRINKFILE with the EmptyFile
option first. Then use ALTER DATABASE to remove the filegroup.
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> Hi All,
> I have a SQL 2000 database that I had created a seperate file group for to
> hold indexes. I have since deleted the indexes and want to delete the
file
> groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> <filegroupname> I receive an error indicating that 'The filegroup
<filegroup>
> cannot be removed because it is not empty'
> If have verified that there are no tables or indexes in this filegroup and
> have also run the following below queries to verify that the given
filegroups
> are empty:
> select name,groupid
> from sysindexes
> where groupid=filegroup_id(<groupname>)
> and indid > 0
> Although there are no objects residing in the actual devices, they are
still
> listed in sysfiles. As such, even if I try to detach and reattach the DB,
> the files are still linked and loaded with the database.
> The tables to which the indexes existed are very large, so I also
> stopped/restarted the sql services (thinking that possibly something may
have
> been stuck in memory) however that did not resolve the problem.
> Has aonyone encountered this before? Is anyone aware of a fix for this?
> Thanks,|||I have tried this. The DBCC shows no output other then the standard DBCC
execution statement. When I attempt to issue an ALTER DATABASE to drop the
filegroups I get the error indicating that the filesgroups are not empty.
There are no objects in these filegroups, sysfilesgroups also does not show
the filegroups although sysfiles and sysfiles1 still show the filegroups that
I am trying to remove.
Any help would be greatly appreciated.
Thanks,
"Armando Prato" wrote:
> You can try using DBCC SHRINKFILE with the EmptyFile
> option first. Then use ALTER DATABASE to remove the filegroup.
> "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> > Hi All,
> >
> > I have a SQL 2000 database that I had created a seperate file group for to
> > hold indexes. I have since deleted the indexes and want to delete the
> file
> > groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> > <filegroupname> I receive an error indicating that 'The filegroup
> <filegroup>
> > cannot be removed because it is not empty'
> >
> > If have verified that there are no tables or indexes in this filegroup and
> > have also run the following below queries to verify that the given
> filegroups
> > are empty:
> >
> > select name,groupid
> > from sysindexes
> > where groupid=filegroup_id(<groupname>)
> > and indid > 0
> >
> > Although there are no objects residing in the actual devices, they are
> still
> > listed in sysfiles. As such, even if I try to detach and reattach the DB,
> > the files are still linked and loaded with the database.
> >
> > The tables to which the indexes existed are very large, so I also
> > stopped/restarted the sql services (thinking that possibly something may
> have
> > been stuck in memory) however that did not resolve the problem.
> >
> > Has aonyone encountered this before? Is anyone aware of a fix for this?
> >
> > Thanks,
>
>|||This is kind of weird, but if i manually add the filegroup records back to
sysfilesgroups I can then see that there is an empty file in each (also
verified in EM by then looking at the filegroups properties to see the number
of files within). I was then able to ALTER DATABASE with a REMOVE FILE
refernecing what the original file name was, then issue an ALTER DATABASE
with a REMOVE FILEGROUP.
I am all set now.
Thanks All,
Dave
"DBADave" wrote:
> I have tried this. The DBCC shows no output other then the standard DBCC
> execution statement. When I attempt to issue an ALTER DATABASE to drop the
> filegroups I get the error indicating that the filesgroups are not empty.
> There are no objects in these filegroups, sysfilesgroups also does not show
> the filegroups although sysfiles and sysfiles1 still show the filegroups that
> I am trying to remove.
> Any help would be greatly appreciated.
> Thanks,
>
> "Armando Prato" wrote:
> > You can try using DBCC SHRINKFILE with the EmptyFile
> > option first. Then use ALTER DATABASE to remove the filegroup.
> >
> > "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> > news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> > > Hi All,
> > >
> > > I have a SQL 2000 database that I had created a seperate file group for to
> > > hold indexes. I have since deleted the indexes and want to delete the
> > file
> > > groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> > > <filegroupname> I receive an error indicating that 'The filegroup
> > <filegroup>
> > > cannot be removed because it is not empty'
> > >
> > > If have verified that there are no tables or indexes in this filegroup and
> > > have also run the following below queries to verify that the given
> > filegroups
> > > are empty:
> > >
> > > select name,groupid
> > > from sysindexes
> > > where groupid=filegroup_id(<groupname>)
> > > and indid > 0
> > >
> > > Although there are no objects residing in the actual devices, they are
> > still
> > > listed in sysfiles. As such, even if I try to detach and reattach the DB,
> > > the files are still linked and loaded with the database.
> > >
> > > The tables to which the indexes existed are very large, so I also
> > > stopped/restarted the sql services (thinking that possibly something may
> > have
> > > been stuck in memory) however that did not resolve the problem.
> > >
> > > Has aonyone encountered this before? Is anyone aware of a fix for this?
> > >
> > > Thanks,
> >
> >
> >|||Have you tried a DBCC SHRINKFILE with EMPTYFILE on the member files and then
dropping the files first?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:95372080-4621-4B1B-9F3E-907D7503D8B9@.microsoft.com...
I have tried this. The DBCC shows no output other then the standard DBCC
execution statement. When I attempt to issue an ALTER DATABASE to drop the
filegroups I get the error indicating that the filesgroups are not empty.
There are no objects in these filegroups, sysfilesgroups also does not show
the filegroups although sysfiles and sysfiles1 still show the filegroups
that
I am trying to remove.
Any help would be greatly appreciated.
Thanks,
"Armando Prato" wrote:
> You can try using DBCC SHRINKFILE with the EmptyFile
> option first. Then use ALTER DATABASE to remove the filegroup.
> "DBADave" <DBADave@.discussions.microsoft.com> wrote in message
> news:703563A7-BF30-445B-B563-2E0C323A9957@.microsoft.com...
> > Hi All,
> >
> > I have a SQL 2000 database that I had created a seperate file group for
> > to
> > hold indexes. I have since deleted the indexes and want to delete the
> file
> > groups. When I execute the ALTER DATABASE <dbname> REMOVE FILEGROUP
> > <filegroupname> I receive an error indicating that 'The filegroup
> <filegroup>
> > cannot be removed because it is not empty'
> >
> > If have verified that there are no tables or indexes in this filegroup
> > and
> > have also run the following below queries to verify that the given
> filegroups
> > are empty:
> >
> > select name,groupid
> > from sysindexes
> > where groupid=filegroup_id(<groupname>)
> > and indid > 0
> >
> > Although there are no objects residing in the actual devices, they are
> still
> > listed in sysfiles. As such, even if I try to detach and reattach the
> > DB,
> > the files are still linked and loaded with the database.
> >
> > The tables to which the indexes existed are very large, so I also
> > stopped/restarted the sql services (thinking that possibly something may
> have
> > been stuck in memory) however that did not resolve the problem.
> >
> > Has aonyone encountered this before? Is anyone aware of a fix for this?
> >
> > Thanks,
>
>

Thursday, March 8, 2012

Can't delete filegroup, even though it is empty

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
Ragnar
One method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>
|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I have a table in an SQL Server 2000 table that has no primary key, in
>> fact no indexes at all. (I didn't design this (:-)) )
>> I found that there is a duplicate row in the table but when I try to
>> delete it I get an error message:
>> "Key column information insufficient or incomplete. Too many rows were
>> affected by this update."
>> I had tried adding a primary key index but of course I couldn't since
>> there is a duplicate row.
>> I also tried just opening the table in EM and editing the data in the
>> column I want as primary key for one of the duplicate rows so I could set
>> the primary key, but I got an error message that the transaction can't
>> start in firehose mode.
>> Is there a simple way I can get out of this catch22 box? The DB supports
>> a busy Web site, so I was hoping to avoid shutting down the site.
>> Any help would be appreciated.
>> Ragnar
>

can't delete a full text catalog

Hello
I've defined a full text catalog that indexes several columns from two tables.
Now I want to erase it and it simply don't let me... using the console it
reports that my catalog has been lost and to use sp_fulltext_catalog to
repopulate or rebuild it...
I tryied so and the error was the same...
i need to erase that useless catalog because it don't work anymore and i
need to do another one with the same name...
how can i get it done?!?!
best regards
Jorge Ribeiro
Can you try to right click on your table in EM, and select Full Text Index
Table, and then select edit. Click through the dialogs until you get the new
catalog creation dialog. Create a new catalog.
This will clear some of this class of errors. If this won't work you will
probably have to use some of the full text procedures and possibly manually
remove some rows from the system tables. Post back here and I'll try to
help you further with this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:F60177EB-6333-4755-A035-202EC62FB27F@.microsoft.com...
> Hello
> I've defined a full text catalog that indexes several columns from two
tables.
> Now I want to erase it and it simply don't let me... using the console it
> reports that my catalog has been lost and to use sp_fulltext_catalog to
> repopulate or rebuild it...
> I tryied so and the error was the same...
> i need to erase that useless catalog because it don't work anymore and i
> need to do another one with the same name...
> how can i get it done?!?!
> best regards
> Jorge Ribeiro
|||Hi
I've done what you sujested (create a new catalog) and the result was the
same... the catalog has been lost and so on...
it didn't create a new catalog and the old one still persists
what can I do now?!?!
thanx
Jorge Ribeiro
"Hilary Cotter" wrote:

> Can you try to right click on your table in EM, and select Full Text Index
> Table, and then select edit. Click through the dialogs until you get the new
> catalog creation dialog. Create a new catalog.
> This will clear some of this class of errors. If this won't work you will
> probably have to use some of the full text procedures and possibly manually
> remove some rows from the system tables. Post back here and I'll try to
> help you further with this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
> news:F60177EB-6333-4755-A035-202EC62FB27F@.microsoft.com...
> tables.
>
>
|||drop the catalog using sp_fulltext_catalog 'catalogname', 'drop'
After you have completed doing this, delete the rows from
sysfulltextcatalogs
Then try to build your catalogs again.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:C159A27C-6C4F-4743-B532-1FF11E754DA4@.microsoft.com...[vbcol=seagreen]
> Hi
> I've done what you sujested (create a new catalog) and the result was the
> same... the catalog has been lost and so on...
> it didn't create a new catalog and the old one still persists
> what can I do now?!?!
> thanx
> Jorge Ribeiro
>
> "Hilary Cotter" wrote:
Index[vbcol=seagreen]
new[vbcol=seagreen]
will[vbcol=seagreen]
manually[vbcol=seagreen]
message[vbcol=seagreen]
it[vbcol=seagreen]
to[vbcol=seagreen]
i[vbcol=seagreen]
|||i've already done that
i've got an error message
Cannot drop full-text catalog 'cat_intranet_teste_paginas' because it
contains a full-text index.
can i still delete the catalog row from sysfulltextcatalogs?!
will it work?!?!
"Hilary Cotter" wrote:

> drop the catalog using sp_fulltext_catalog 'catalogname', 'drop'
> After you have completed doing this, delete the rows from
> sysfulltextcatalogs
> Then try to build your catalogs again.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
> news:C159A27C-6C4F-4743-B532-1FF11E754DA4@.microsoft.com...
> Index
> new
> will
> manually
> message
> it
> to
> i
>
>
|||back up your database. Then delete the rows.
Then try to create another catalog, and go back and edit the tables to move
them to the new catalog.
"Jorge Ribeiro" <JorgeRibeiro@.discussions.microsoft.com> wrote in message
news:096B13D9-C590-4B33-A2BF-C076FED64EDC@.microsoft.com...[vbcol=seagreen]
> i've already done that
> i've got an error message
> Cannot drop full-text catalog 'cat_intranet_teste_paginas' because it
> contains a full-text index.
> can i still delete the catalog row from sysfulltextcatalogs?!
> will it work?!?!
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
try to[vbcol=seagreen]
two[vbcol=seagreen]
console[vbcol=seagreen]
sp_fulltext_catalog[vbcol=seagreen]
and[vbcol=seagreen]

Sunday, March 11, 2012

Can't delete filegroup, even though it is empty

Thursday, March 8, 2012

Can't delete filegroup, even though it is empty

Can't delete filegroup, even though it is empty

Can't delete duplicate row

Can't delete duplicate row

Can't delete duplicate row

can't delete a full text catalog

connect from ASP app

Blog Archive

About Me