dropping datafile from primary filegroup

Is there a way to drop a datafile from the primary
filegroup after running the dbcc shrinkfile
(file_name,emptyfile).
There is a lot of fragmentation in the datafile and the
file is not shrinking. The size of the datafile right now
is 15 gig and no more than 5gig of data is present in the
datafile . After running the above dbcc command the
contents of the file will be moved onto the remaining data
files in the filegroup and allows the file to be dropped .
But this is not he case with the primary
filegroup/datafile and it errors out. Is there any other
way to drop the datafile or shrink the file to reduce the
fragmentationYou can't drop the Primary file or filegroup. But you can run DBCC
DBREINDEX or DBCC INDEXDEFRAG to reduce the fragmentation.
Andrew J. Kelly
SQL Server MVP
"rajeev" <potinenir@.yahoo.com> wrote in message
news:058201c37d6d$22e20190$a401280a@.phx.gbl...
> Is there a way to drop a datafile from the primary
> filegroup after running the dbcc shrinkfile
> (file_name,emptyfile).
> There is a lot of fragmentation in the datafile and the
> file is not shrinking. The size of the datafile right now
> is 15 gig and no more than 5gig of data is present in the
> datafile . After running the above dbcc command the
> contents of the file will be moved onto the remaining data
> files in the filegroup and allows the file to be dropped .
> But this is not he case with the primary
> filegroup/datafile and it errors out. Is there any other
> way to drop the datafile or shrink the file to reduce the
> fragmentation
>sql

dropping corrupt tables?

I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?
Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?
|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?

dropping corrupt tables?

I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=101248
8
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even mor
e
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?sql

dropping corrupt tables?

I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480 extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?Been a while since I last used 6.5, but if you are trying to get rid of the
table, could run DROP TABLE command and see what you get?
Eg:
DROP TABLE TableNameHere
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
I'm hoping that someone can help me with a sql 6.5 issue.
I have a couple of tables which would appear to be corrupt. dbcc checkdb
throws up the following:
attempt to fetch logical page 1012488 in database 'xxxxx' belongs to object
'0', not to object 'tableyyyyy'.
and a checkalloc gives the following error:
Table Corrupt: object id does not match between extent in allocation page
and Sysindexes; check the following extent: alloc pg#=1012480
extent#=1012488
object id on extent=0 (object name = 0) object id in Sysindexes=291232538
(object name = tableyyyy)
i have managed to bcp all the data into a new table so i thought that all
was looking good. now when i try to delete the original table through
enterprise manager it disapperas, but after a refresh its there again!
i thought the problem with the table not dropping might have been due to a
foreign key so i ran a truncate table command, and this produced an even
more
bizarre result!:
Could not truncate table 'tableyyyy' because there is not enough room in the
log to record the deallocation of all of the index and data pages.
can anyone suggest how i can drop these problem tables?|||Hi,
Before dropping try doing:-
1. Create a new database
2. Create the table and try to BCP IN the data which you BCP OUT
Once you find you can load the data then you could drop the table from ISQLW
using
Drop table <TABLE_NAME>
After that create the table with dependancies and BCP IN the data.
Thanks
Hari
SQL Server MVP
"rarara" <rarara@.discussions.microsoft.com> wrote in message
news:FB74A6B2-D5D4-47D2-BCE1-C408F8FBC203@.microsoft.com...
> I'm hoping that someone can help me with a sql 6.5 issue.
> I have a couple of tables which would appear to be corrupt. dbcc checkdb
> throws up the following:
> attempt to fetch logical page 1012488 in database 'xxxxx' belongs to
> object
> '0', not to object 'tableyyyyy'.
> and a checkalloc gives the following error:
> Table Corrupt: object id does not match between extent in allocation page
> and Sysindexes; check the following extent: alloc pg#=1012480
> extent#=1012488
> object id on extent=0 (object name = 0) object id in Sysindexes=291232538
> (object name = tableyyyy)
> i have managed to bcp all the data into a new table so i thought that all
> was looking good. now when i try to delete the original table through
> enterprise manager it disapperas, but after a refresh its there again!
> i thought the problem with the table not dropping might have been due to a
> foreign key so i ran a truncate table command, and this produced an even
> more
> bizarre result!:
> Could not truncate table 'tableyyyy' because there is not enough room in
> the
> log to record the deallocation of all of the index and data pages.
> can anyone suggest how i can drop these problem tables?

DROP and CREATE INDEXES

I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
SHOWCONTIG on the table both befor and after dropping and recreating the
indexes, including PK constraints, the results are the same? Any insight? I
thought this would reorganize the data to be more efficient? What am I doing
wrong?
DROP INDEX [PD7333].[F98741].[F98741_1]
ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
[ESEVSEQ])
ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
NONCLUSTERED ([ESEVSK], [ESEVSEQ])
DBCC SHOWCONTIG scanning 'F98741' table...
Table: 'F98741' (277576027); index ID: 0, database ID: 14
TABLE level scan performed.
- Pages Scanned........................: 30049
- Extents Scanned.......................: 3786
- Extent Switches.......................: 3785
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
- Extent Scan Fragmentation ...............: 97.94%
- Avg. Bytes Free per Page................: 221.6
- Avg. Page Density (full)................: 97.26%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.That is because your table is a HEAP which means there is no clustered
index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
proper column and create a clustered index and your problem will go away.
By the way you should use DBCC DBREINDEX instead of dropping and creating.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> SHOWCONTIG on the table both befor and after dropping and recreating the
> indexes, including PK constraints, the results are the same? Any insight?
> I
> thought this would reorganize the data to be more efficient? What am I
> doing
> wrong?
> DROP INDEX [PD7333].[F98741].[F98741_1]
> ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
> [ESEVSEQ])
> ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
> NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>
> DBCC SHOWCONTIG scanning 'F98741' table...
> Table: 'F98741' (277576027); index ID: 0, database ID: 14
> TABLE level scan performed.
> - Pages Scanned........................: 30049
> - Extents Scanned.......................: 3786
> - Extent Switches.......................: 3785
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
> - Extent Scan Fragmentation ...............: 97.94%
> - Avg. Bytes Free per Page................: 221.6
> - Avg. Page Density (full)................: 97.26%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.|||Thanks. I had started with DBREINDEX but when I saw it made no change, I
resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and I
don't know if I can add and/or change these indexes to clustered...I'll have
to find out.
"Andrew J. Kelly" wrote:
> That is because your table is a HEAP which means there is no clustered
> index. You can not reduce fragmentation on a HEAP by reindexing. Choose a
> proper column and create a clustered index and your problem will go away.
> By the way you should use DBCC DBREINDEX instead of dropping and creating.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
> > I'm trying to reduce fragmentation of indexes on tables. When I run DBCC
> > SHOWCONTIG on the table both befor and after dropping and recreating the
> > indexes, including PK constraints, the results are the same? Any insight?
> > I
> > thought this would reorganize the data to be more efficient? What am I
> > doing
> > wrong?
> >
> > DROP INDEX [PD7333].[F98741].[F98741_1]
> >
> > ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
> >
> > CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
> > [ESEVSEQ])
> >
> > ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
> > NONCLUSTERED ([ESEVSK], [ESEVSEQ])
> >
> >
> >
> > DBCC SHOWCONTIG scanning 'F98741' table...
> > Table: 'F98741' (277576027); index ID: 0, database ID: 14
> > TABLE level scan performed.
> > - Pages Scanned........................: 30049
> > - Extents Scanned.......................: 3786
> > - Extent Switches.......................: 3785
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
> > - Extent Scan Fragmentation ...............: 97.94%
> > - Avg. Bytes Free per Page................: 221.6
> > - Avg. Page Density (full)................: 97.26%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
>
>|||Well you need to tell them to get their act together<g>. You can create a
clustered index and then drop it to defrag the table but each table really
should have a clustered index on it.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:E8335A7A-73EA-427B-9DB3-1BBE473488D0@.microsoft.com...
> Thanks. I had started with DBREINDEX but when I saw it made no change, I
> resorted to the DROP/CREATE. Unforuntately this is a 3rd party system and
> I
> don't know if I can add and/or change these indexes to clustered...I'll
> have
> to find out.
> "Andrew J. Kelly" wrote:
>> That is because your table is a HEAP which means there is no clustered
>> index. You can not reduce fragmentation on a HEAP by reindexing. Choose
>> a
>> proper column and create a clustered index and your problem will go away.
>> By the way you should use DBCC DBREINDEX instead of dropping and
>> creating.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:9E90DF53-9D44-40CF-A611-BEF8C29BE3DD@.microsoft.com...
>> > I'm trying to reduce fragmentation of indexes on tables. When I run
>> > DBCC
>> > SHOWCONTIG on the table both befor and after dropping and recreating
>> > the
>> > indexes, including PK constraints, the results are the same? Any
>> > insight?
>> > I
>> > thought this would reorganize the data to be more efficient? What am I
>> > doing
>> > wrong?
>> >
>> > DROP INDEX [PD7333].[F98741].[F98741_1]
>> >
>> > ALTER TABLE [PD7333].[F98741] DROP CONSTRAINT [F98741_PK]
>> >
>> > CREATE NONCLUSTERED INDEX [F98741_1] ON [PD7333].[F98741] ([ESEVSPEC],
>> > [ESEVSEQ])
>> >
>> > ALTER TABLE [PD7333].[F98741] ADD CONSTRAINT [F98741_PK] PRIMARY KEY
>> > NONCLUSTERED ([ESEVSK], [ESEVSEQ])
>> >
>> >
>> >
>> > DBCC SHOWCONTIG scanning 'F98741' table...
>> > Table: 'F98741' (277576027); index ID: 0, database ID: 14
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 30049
>> > - Extents Scanned.......................: 3786
>> > - Extent Switches.......................: 3785
>> > - Avg. Pages per Extent..................: 7.9
>> > - Scan Density [Best Count:Actual Count]......: 99.23% [3757:3786]
>> > - Extent Scan Fragmentation ...............: 97.94%
>> > - Avg. Bytes Free per Page................: 221.6
>> > - Avg. Page Density (full)................: 97.26%
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>>