I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.
You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:
> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
Showing posts with label emptyfile. Show all posts
Showing posts with label emptyfile. Show all posts
Thursday, February 16, 2012
Am I missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)
I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:
> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:
> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
Am I missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)
I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:
> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:
> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
Subscribe to:
Posts (Atom)