am I using SHRINKFILE correctly?

I created some huge tables over the weekend for testing and I have since
dropped them since I don't need them however my database is still over 3
gig. (pre testing size was a few hundred meg)
I'm doing
DBCC SHRINKFILE(mydatabase_Data,TRUNCATEONLY )
DBCC SHRINKFILE(mydatabase_v1_Log,1)
and it seems to be working but the file size of the mdf file remains over 3
gig.
What am I missing here ?
Thanks alot.
Hi
It seems that there is still data in one of the later extents and
TRUNCATEONLY does not re-allocate this to an earlier free extent. Try
specifying a target size instead.
John
"D" wrote:

> I created some huge tables over the weekend for testing and I have since
> dropped them since I don't need them however my database is still over 3
> gig. (pre testing size was a few hundred meg)
> I'm doing
> DBCC SHRINKFILE(mydatabase_Data,TRUNCATEONLY )
> DBCC SHRINKFILE(mydatabase_v1_Log,1)
> and it seems to be working but the file size of the mdf file remains over 3
> gig.
> What am I missing here ?
> Thanks alot.
>
>
|||That did it. Cool ! Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:085AADB1-D52A-488D-BE04-E5976C34D4DA@.microsoft.com...[vbcol=seagreen]
> Hi
> It seems that there is still data in one of the later extents and
> TRUNCATEONLY does not re-allocate this to an earlier free extent. Try
> specifying a target size instead.
> John
> "D" wrote:

am I using SHRINKFILE correctly?

I created some huge tables over the weekend for testing and I have since
dropped them since I don't need them however my database is still over 3
gig. (pre testing size was a few hundred meg)
I'm doing
DBCC SHRINKFILE(mydatabase_Data,TRUNCATEONLY )
DBCC SHRINKFILE(mydatabase_v1_Log,1)
and it seems to be working but the file size of the mdf file remains over 3
gig.
What am I missing here ?
Thanks alot.Hi
It seems that there is still data in one of the later extents and
TRUNCATEONLY does not re-allocate this to an earlier free extent. Try
specifying a target size instead.
John
"D" wrote:

> I created some huge tables over the weekend for testing and I have since
> dropped them since I don't need them however my database is still over 3
> gig. (pre testing size was a few hundred meg)
> I'm doing
> DBCC SHRINKFILE(mydatabase_Data,TRUNCATEONLY )
> DBCC SHRINKFILE(mydatabase_v1_Log,1)
> and it seems to be working but the file size of the mdf file remains over
3
> gig.
> What am I missing here ?
> Thanks alot.
>
>|||That did it. Cool ! Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:085AADB1-D52A-488D-BE04-E5976C34D4DA@.microsoft.com...[vbcol=seagreen]
> Hi
> It seems that there is still data in one of the later extents and
> TRUNCATEONLY does not re-allocate this to an earlier free extent. Try
> specifying a target size instead.
> John
> "D" wrote:
>

am I using SHRINKFILE correctly?

I created some huge tables over the weekend for testing and I have since
dropped them since I don't need them however my database is still over 3
gig. (pre testing size was a few hundred meg)
I'm doing
DBCC SHRINKFILE(mydatabase_Data,TRUNCATEONLY )
DBCC SHRINKFILE(mydatabase_v1_Log,1)
and it seems to be working but the file size of the mdf file remains over 3
gig.
What am I missing here ?
Thanks alot.Hi
It seems that there is still data in one of the later extents and
TRUNCATEONLY does not re-allocate this to an earlier free extent. Try
specifying a target size instead.
John
"D" wrote:
> I created some huge tables over the weekend for testing and I have since
> dropped them since I don't need them however my database is still over 3
> gig. (pre testing size was a few hundred meg)
> I'm doing
> DBCC SHRINKFILE(mydatabase_Data,TRUNCATEONLY )
> DBCC SHRINKFILE(mydatabase_v1_Log,1)
> and it seems to be working but the file size of the mdf file remains over 3
> gig.
> What am I missing here ?
> Thanks alot.
>
>|||That did it. Cool ! Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:085AADB1-D52A-488D-BE04-E5976C34D4DA@.microsoft.com...
> Hi
> It seems that there is still data in one of the later extents and
> TRUNCATEONLY does not re-allocate this to an earlier free extent. Try
> specifying a target size instead.
> John
> "D" wrote:
>> I created some huge tables over the weekend for testing and I have since
>> dropped them since I don't need them however my database is still over 3
>> gig. (pre testing size was a few hundred meg)
>> I'm doing
>> DBCC SHRINKFILE(mydatabase_Data,TRUNCATEONLY )
>> DBCC SHRINKFILE(mydatabase_v1_Log,1)
>> and it seems to be working but the file size of the mdf file remains over
>> 3
>> gig.
>> What am I missing here ?
>> Thanks alot.
>>
>>

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.
>

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.
>

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.
>