DBfile usage: emptyfile

Showing posts with label emptyfile. Show all posts

Thursday, March 29, 2012

DBCC SHRINKFILE ('filename' , EMPTYFILE) not working

I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Robert
i should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100GB.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:

> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and with
> sp_who2 active it shows the command DbccFilesCompact, but it just sits there
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert
sql

DBCC SHRINKFILE ('filename' , EMPTYFILE) not working

I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to th
e
correct filegroup (it defaulted to the primary).
I am trying to use DBCC shrinkfile with the emptyfile option so that I can
then go and do a 'alter database remove file'. I start the process and with
sp_who2 active it shows the command DbccFilesCompact, but it just sits there
and spins forever.
I see cpu and diskio cycles escalating but nothing ever happens.
is there a step before this that i am forgetting?
setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
model.
gracias,
Roberti should state a few more facts.
It is a data file (not log), and the whole DB 1.8 TB. The file is only 100G
B.
I have tried to backup just that filegroup (only 1 file in it) and do a
emptyfile right afterwards but no luck...
"sql411@.nospam.com" wrote:

> I created a file (size = 1 GB, filegrowth=1 GB) but I forgot to add it to
the
> correct filegroup (it defaulted to the primary).
> I am trying to use DBCC shrinkfile with the emptyfile option so that I can
> then go and do a 'alter database remove file'. I start the process and wi
th
> sp_who2 active it shows the command DbccFilesCompact, but it just sits the
re
> and spins forever.
> I see cpu and diskio cycles escalating but nothing ever happens.
> is there a step before this that i am forgetting?
> setup is: x64 sql 2005, sp1 (windwos 2003 ee x64), db in simple recovery
> model.
> gracias,
> Robert

DBCC ShrinkFile

How long does it take to execute DBCC Shrinkfile(DB_FILE, emptyfile) on a 10GB datafiles? If you put your datafiles together with the tempdb datafiles on the same logical drive do we have a performance issue?

Thanks

The time taken for this activity is depends.

THe other part of ur question, Temp db should always be in seperate physical drive if possible for better performance. Datafile is Random read/write but temp db will have moreor less sequential read./write. So if u keep both in same drive it will have performance hit

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx

http://msdn2.microsoft.com/en-us/library/ms345368.aspx

Madhu

Thursday, March 8, 2012

dbcc emptyfile

Hi,
(SQL 2K)
We used to have a database with 1 100Gb data file
I've now created 5 25Gb files and ran the DBCC EmptyFile command to move all
the data out of the primary file to the new ones.
Enterprise Mgr reports the primary file is 99% empty - excellent.
Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
ShrinkFile, backing up logs, full backups etc will shrink it.
Can anyone suggest what I might be missing?
Thanks
NickHi
Can you post your DBCC SHRINKFILE command and the output from sp_spaceused,
sp_helpfile.
John
"Nick" wrote:
> Hi,
> (SQL 2K)
> We used to have a database with 1 100Gb data file
> I've now created 5 25Gb files and ran the DBCC EmptyFile command to move all
> the data out of the primary file to the new ones.
> Enterprise Mgr reports the primary file is 99% empty - excellent.
> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
> ShrinkFile, backing up logs, full backups etc will shrink it.
> Can anyone suggest what I might be missing?
> Thanks
> Nick
>
>|||John,
sp_spaceused:
MyDB 290343.88 MB 69625.83 MB
Reserved Data Index Unused
155007152 KB 92248808 KB 5123888 KB 57634456 KB
sp_helpfile:
name fileid filename filegroup size maxsize growth usage
Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
Unlimited 153600 KB data only
Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
Unlimited 10% log only
Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB Unlimited
10% log only
MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited 10%
data only
MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB Unlimited
10% data only
MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB Unlimited
10% data only
MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB Unlimited
10% data only
Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB Unlimited
10% log only
Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB Unlimited
10% log only
I'm trying to shrink Merlin_Data - the primary file - by running:
DBCC SHRINKFILE (N'Merlin_Data' , 200)
and I've also tried:
DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
Any pointers really appreciated!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> Hi
> Can you post your DBCC SHRINKFILE command and the output from
> sp_spaceused,
> sp_helpfile.
> John
> "Nick" wrote:
>> Hi,
>> (SQL 2K)
>> We used to have a database with 1 100Gb data file
>> I've now created 5 25Gb files and ran the DBCC EmptyFile command to move
>> all
>> the data out of the primary file to the new ones.
>> Enterprise Mgr reports the primary file is 99% empty - excellent.
>> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
>> ShrinkFile, backing up logs, full backups etc will shrink it.
>> Can anyone suggest what I might be missing?
>> Thanks
>> Nick
>>|||Hi
The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so your
shrinkfile should have worked!
Try
DBCC SHRINKFILE ( 1, 200 )
or
DBCC SHRINKFILE ( 1 )
John
"Nick" wrote:
> John,
> sp_spaceused:
> MyDB 290343.88 MB 69625.83 MB
> Reserved Data Index Unused
> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> sp_helpfile:
> name fileid filename filegroup size maxsize growth usage
> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> Unlimited 153600 KB data only
> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
> Unlimited 10% log only
> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB Unlimited
> 10% log only
> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited 10%
> data only
> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB Unlimited
> 10% data only
> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB Unlimited
> 10% data only
> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB Unlimited
> 10% data only
> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB Unlimited
> 10% log only
> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB Unlimited
> 10% log only
> I'm trying to shrink Merlin_Data - the primary file - by running:
> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> and I've also tried:
> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> Any pointers really appreciated!
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> > Hi
> >
> > Can you post your DBCC SHRINKFILE command and the output from
> > sp_spaceused,
> > sp_helpfile.
> >
> > John
> >
> > "Nick" wrote:
> >
> >> Hi,
> >>
> >> (SQL 2K)
> >>
> >> We used to have a database with 1 100Gb data file
> >>
> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to move
> >> all
> >> the data out of the primary file to the new ones.
> >>
> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
> >>
> >> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >>
> >> Can anyone suggest what I might be missing?
> >>
> >> Thanks
> >>
> >> Nick
> >>
> >>
> >>
>
>|||Thats what I thought.
Even in enterprise manager, it says "Min file size 150mb" so I try shrinking
it to 200Mb and it just doesn't budge. Not an inch.
Geeze, it's frustrating as I need this file as small as possible as I've
moved all the data out of it.
Is there any way of seeing if SQL is even attempting to shrink the file? The
DBCC command returns very quickly (< 1sec) so I guess it's an off-line
process.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> Hi
> The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
> your
> shrinkfile should have worked!
> Try
> DBCC SHRINKFILE ( 1, 200 )
> or
> DBCC SHRINKFILE ( 1 )
> John
> "Nick" wrote:
>> John,
>> sp_spaceused:
>> MyDB 290343.88 MB 69625.83 MB
>> Reserved Data Index Unused
>> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
>> sp_helpfile:
>> name fileid filename filegroup size maxsize growth usage
>> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
>> Unlimited 153600 KB data only
>> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
>> Unlimited 10% log only
>> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
>> Unlimited
>> 10% log only
>> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited
>> 10%
>> data only
>> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
>> Unlimited
>> 10% data only
>> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
>> Unlimited
>> 10% data only
>> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
>> Unlimited
>> 10% data only
>> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
>> Unlimited
>> 10% log only
>> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
>> Unlimited
>> 10% log only
>> I'm trying to shrink Merlin_Data - the primary file - by running:
>> DBCC SHRINKFILE (N'Merlin_Data' , 200)
>> and I've also tried:
>> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
>> Any pointers really appreciated!
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
>> > Hi
>> >
>> > Can you post your DBCC SHRINKFILE command and the output from
>> > sp_spaceused,
>> > sp_helpfile.
>> >
>> > John
>> >
>> > "Nick" wrote:
>> >
>> >> Hi,
>> >>
>> >> (SQL 2K)
>> >>
>> >> We used to have a database with 1 100Gb data file
>> >>
>> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
>> >> move
>> >> all
>> >> the data out of the primary file to the new ones.
>> >>
>> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
>> >>
>> >> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
>> >> ShrinkFile, backing up logs, full backups etc will shrink it.
>> >>
>> >> Can anyone suggest what I might be missing?
>> >>
>> >> Thanks
>> >>
>> >> Nick
>> >>
>> >>
>> >>
>>|||Hi Nick
If the command was working it should take some time, you may want to try a
larger size say 94000. What size is model?
John
"Nick" wrote:
> Thats what I thought.
> Even in enterprise manager, it says "Min file size 150mb" so I try shrinking
> it to 200Mb and it just doesn't budge. Not an inch.
> Geeze, it's frustrating as I need this file as small as possible as I've
> moved all the data out of it.
> Is there any way of seeing if SQL is even attempting to shrink the file? The
> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
> process.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> > Hi
> >
> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
> > your
> > shrinkfile should have worked!
> >
> > Try
> >
> > DBCC SHRINKFILE ( 1, 200 )
> > or
> > DBCC SHRINKFILE ( 1 )
> >
> > John
> >
> > "Nick" wrote:
> >
> >> John,
> >>
> >> sp_spaceused:
> >> MyDB 290343.88 MB 69625.83 MB
> >>
> >> Reserved Data Index Unused
> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> >>
> >> sp_helpfile:
> >>
> >> name fileid filename filegroup size maxsize growth usage
> >>
> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> >> Unlimited 153600 KB data only
> >>
> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448 KB
> >> Unlimited 10% log only
> >>
> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
> >> Unlimited
> >> 10% log only
> >>
> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB Unlimited
> >> 10%
> >> data only
> >>
> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
> >> Unlimited
> >> 10% data only
> >>
> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
> >> Unlimited
> >> 10% data only
> >>
> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
> >> Unlimited
> >> 10% data only
> >>
> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
> >> Unlimited
> >> 10% log only
> >>
> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
> >> Unlimited
> >> 10% log only
> >>
> >> I'm trying to shrink Merlin_Data - the primary file - by running:
> >>
> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> >> and I've also tried:
> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> >>
> >> Any pointers really appreciated!
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> >> > Hi
> >> >
> >> > Can you post your DBCC SHRINKFILE command and the output from
> >> > sp_spaceused,
> >> > sp_helpfile.
> >> >
> >> > John
> >> >
> >> > "Nick" wrote:
> >> >
> >> >> Hi,
> >> >>
> >> >> (SQL 2K)
> >> >>
> >> >> We used to have a database with 1 100Gb data file
> >> >>
> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
> >> >> move
> >> >> all
> >> >> the data out of the primary file to the new ones.
> >> >>
> >> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
> >> >>
> >> >> Problem is, it wont shrink from being 100Gb in size - no amout of DBCC
> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >> >>
> >> >> Can anyone suggest what I might be missing?
> >> >>
> >> >> Thanks
> >> >>
> >> >> Nick
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Model - what a good idea! SQL can't shrink a database smaller than that can
it?
With much excitement, I check model's size... 1.5Mb!
Damm!!!
DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
than 1 sec.
Logged in as sa.
File permissions maybe'
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
> Hi Nick
> If the command was working it should take some time, you may want to try a
> larger size say 94000. What size is model?
> John
> "Nick" wrote:
>> Thats what I thought.
>> Even in enterprise manager, it says "Min file size 150mb" so I try
>> shrinking
>> it to 200Mb and it just doesn't budge. Not an inch.
>> Geeze, it's frustrating as I need this file as small as possible as I've
>> moved all the data out of it.
>> Is there any way of seeing if SQL is even attempting to shrink the file?
>> The
>> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
>> process.
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
>> > Hi
>> >
>> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
>> > your
>> > shrinkfile should have worked!
>> >
>> > Try
>> >
>> > DBCC SHRINKFILE ( 1, 200 )
>> > or
>> > DBCC SHRINKFILE ( 1 )
>> >
>> > John
>> >
>> > "Nick" wrote:
>> >
>> >> John,
>> >>
>> >> sp_spaceused:
>> >> MyDB 290343.88 MB 69625.83 MB
>> >>
>> >> Reserved Data Index Unused
>> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
>> >>
>> >> sp_helpfile:
>> >>
>> >> name fileid filename filegroup size maxsize growth usage
>> >>
>> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
>> >> Unlimited 153600 KB data only
>> >>
>> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448
>> >> KB
>> >> Unlimited 10% log only
>> >>
>> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
>> >> Unlimited
>> >> 10% log only
>> >>
>> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
>> >> Unlimited
>> >> 10%
>> >> data only
>> >>
>> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
>> >> Unlimited
>> >> 10% data only
>> >>
>> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
>> >> Unlimited
>> >> 10% data only
>> >>
>> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
>> >> Unlimited
>> >> 10% data only
>> >>
>> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
>> >> Unlimited
>> >> 10% log only
>> >>
>> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
>> >> Unlimited
>> >> 10% log only
>> >>
>> >> I'm trying to shrink Merlin_Data - the primary file - by running:
>> >>
>> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
>> >> and I've also tried:
>> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
>> >>
>> >> Any pointers really appreciated!
>> >>
>> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > Can you post your DBCC SHRINKFILE command and the output from
>> >> > sp_spaceused,
>> >> > sp_helpfile.
>> >> >
>> >> > John
>> >> >
>> >> > "Nick" wrote:
>> >> >
>> >> >> Hi,
>> >> >>
>> >> >> (SQL 2K)
>> >> >>
>> >> >> We used to have a database with 1 100Gb data file
>> >> >>
>> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
>> >> >> move
>> >> >> all
>> >> >> the data out of the primary file to the new ones.
>> >> >>
>> >> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
>> >> >>
>> >> >> Problem is, it wont shrink from being 100Gb in size - no amout of
>> >> >> DBCC
>> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
>> >> >>
>> >> >> Can anyone suggest what I might be missing?
>> >> >>
>> >> >> Thanks
>> >> >>
>> >> >> Nick
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi Nick
I presume that you did a sp_helpfile again after the DBCC SHRINKFILE?
I doubt it is file permissions are a problem.
Have you tried DBCC SHRINKDATABASE?
John
"Nick" wrote:
> Model - what a good idea! SQL can't shrink a database smaller than that can
> it?
> With much excitement, I check model's size... 1.5Mb!
> Damm!!!
> DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
> than 1 sec.
> Logged in as sa.
> File permissions maybe'
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
> > Hi Nick
> >
> > If the command was working it should take some time, you may want to try a
> > larger size say 94000. What size is model?
> >
> > John
> >
> > "Nick" wrote:
> >
> >> Thats what I thought.
> >>
> >> Even in enterprise manager, it says "Min file size 150mb" so I try
> >> shrinking
> >> it to 200Mb and it just doesn't budge. Not an inch.
> >>
> >> Geeze, it's frustrating as I need this file as small as possible as I've
> >> moved all the data out of it.
> >>
> >> Is there any way of seeing if SQL is even attempting to shrink the file?
> >> The
> >> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
> >> process.
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> >> > Hi
> >> >
> >> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB) so
> >> > your
> >> > shrinkfile should have worked!
> >> >
> >> > Try
> >> >
> >> > DBCC SHRINKFILE ( 1, 200 )
> >> > or
> >> > DBCC SHRINKFILE ( 1 )
> >> >
> >> > John
> >> >
> >> > "Nick" wrote:
> >> >
> >> >> John,
> >> >>
> >> >> sp_spaceused:
> >> >> MyDB 290343.88 MB 69625.83 MB
> >> >>
> >> >> Reserved Data Index Unused
> >> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> >> >>
> >> >> sp_helpfile:
> >> >>
> >> >> name fileid filename filegroup size maxsize growth usage
> >> >>
> >> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> >> >> Unlimited 153600 KB data only
> >> >>
> >> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL 14592448
> >> >> KB
> >> >> Unlimited 10% log only
> >> >>
> >> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
> >> >> Unlimited
> >> >> 10% log only
> >> >>
> >> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
> >> >> Unlimited
> >> >> 10%
> >> >> data only
> >> >>
> >> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
> >> >> Unlimited
> >> >> 10% data only
> >> >>
> >> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
> >> >> Unlimited
> >> >> 10% data only
> >> >>
> >> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
> >> >> Unlimited
> >> >> 10% data only
> >> >>
> >> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
> >> >> Unlimited
> >> >> 10% log only
> >> >>
> >> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
> >> >> Unlimited
> >> >> 10% log only
> >> >>
> >> >> I'm trying to shrink Merlin_Data - the primary file - by running:
> >> >>
> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> >> >> and I've also tried:
> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> >> >>
> >> >> Any pointers really appreciated!
> >> >>
> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > Can you post your DBCC SHRINKFILE command and the output from
> >> >> > sp_spaceused,
> >> >> > sp_helpfile.
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Nick" wrote:
> >> >> >
> >> >> >> Hi,
> >> >> >>
> >> >> >> (SQL 2K)
> >> >> >>
> >> >> >> We used to have a database with 1 100Gb data file
> >> >> >>
> >> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command to
> >> >> >> move
> >> >> >> all
> >> >> >> the data out of the primary file to the new ones.
> >> >> >>
> >> >> >> Enterprise Mgr reports the primary file is 99% empty - excellent.
> >> >> >>
> >> >> >> Problem is, it wont shrink from being 100Gb in size - no amout of
> >> >> >> DBCC
> >> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >> >> >>
> >> >> >> Can anyone suggest what I might be missing?
> >> >> >>
> >> >> >> Thanks
> >> >> >>
> >> >> >> Nick
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Well, it took a while but I've sussed it.
It turns out, that my log files were on a Windows mount-point which should
be supported on SQL2K & Windows Server 2003 Enterprise non-clustered.
I thought maybe SQL Server needed some more log space for the data file
shrink, but, whenever I tried expanding the logs SQL told me "File not
found". Very weird. So, I detached the db then moved the logs to a normal
drive and re-attached it. I could then increase the log sizes and the data
file shrank straight away.
Maybe SQL Server was unable to claim log-space for the shrink but didn't
report the error correctly as it was getting confused with the mount-points?
I'm guessing, but it works at last and many thanks for your assistance.
Nick
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:285E26C2-FF94-4539-B890-088B681AC999@.microsoft.com...
> Hi Nick
> I presume that you did a sp_helpfile again after the DBCC SHRINKFILE?
> I doubt it is file permissions are a problem.
> Have you tried DBCC SHRINKDATABASE?
> John
> "Nick" wrote:
>> Model - what a good idea! SQL can't shrink a database smaller than that
>> can
>> it?
>> With much excitement, I check model's size... 1.5Mb!
>> Damm!!!
>> DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
>> than 1 sec.
>> Logged in as sa.
>> File permissions maybe'
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
>> > Hi Nick
>> >
>> > If the command was working it should take some time, you may want to
>> > try a
>> > larger size say 94000. What size is model?
>> >
>> > John
>> >
>> > "Nick" wrote:
>> >
>> >> Thats what I thought.
>> >>
>> >> Even in enterprise manager, it says "Min file size 150mb" so I try
>> >> shrinking
>> >> it to 200Mb and it just doesn't budge. Not an inch.
>> >>
>> >> Geeze, it's frustrating as I need this file as small as possible as
>> >> I've
>> >> moved all the data out of it.
>> >>
>> >> Is there any way of seeing if SQL is even attempting to shrink the
>> >> file?
>> >> The
>> >> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
>> >> process.
>> >>
>> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> >> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB)
>> >> > so
>> >> > your
>> >> > shrinkfile should have worked!
>> >> >
>> >> > Try
>> >> >
>> >> > DBCC SHRINKFILE ( 1, 200 )
>> >> > or
>> >> > DBCC SHRINKFILE ( 1 )
>> >> >
>> >> > John
>> >> >
>> >> > "Nick" wrote:
>> >> >
>> >> >> John,
>> >> >>
>> >> >> sp_spaceused:
>> >> >> MyDB 290343.88 MB 69625.83 MB
>> >> >>
>> >> >> Reserved Data Index Unused
>> >> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
>> >> >>
>> >> >> sp_helpfile:
>> >> >>
>> >> >> name fileid filename filegroup size maxsize growth usage
>> >> >>
>> >> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
>> >> >> Unlimited 153600 KB data only
>> >> >>
>> >> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL
>> >> >> 14592448
>> >> >> KB
>> >> >> Unlimited 10% log only
>> >> >>
>> >> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
>> >> >> Unlimited
>> >> >> 10% log only
>> >> >>
>> >> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
>> >> >> Unlimited
>> >> >> 10%
>> >> >> data only
>> >> >>
>> >> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
>> >> >> Unlimited
>> >> >> 10% data only
>> >> >>
>> >> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
>> >> >> Unlimited
>> >> >> 10% data only
>> >> >>
>> >> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
>> >> >> Unlimited
>> >> >> 10% data only
>> >> >>
>> >> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
>> >> >> Unlimited
>> >> >> 10% log only
>> >> >>
>> >> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
>> >> >> Unlimited
>> >> >> 10% log only
>> >> >>
>> >> >> I'm trying to shrink Merlin_Data - the primary file - by running:
>> >> >>
>> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
>> >> >> and I've also tried:
>> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
>> >> >>
>> >> >> Any pointers really appreciated!
>> >> >>
>> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> >> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
>> >> >> > Hi
>> >> >> >
>> >> >> > Can you post your DBCC SHRINKFILE command and the output from
>> >> >> > sp_spaceused,
>> >> >> > sp_helpfile.
>> >> >> >
>> >> >> > John
>> >> >> >
>> >> >> > "Nick" wrote:
>> >> >> >
>> >> >> >> Hi,
>> >> >> >>
>> >> >> >> (SQL 2K)
>> >> >> >>
>> >> >> >> We used to have a database with 1 100Gb data file
>> >> >> >>
>> >> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command
>> >> >> >> to
>> >> >> >> move
>> >> >> >> all
>> >> >> >> the data out of the primary file to the new ones.
>> >> >> >>
>> >> >> >> Enterprise Mgr reports the primary file is 99% empty -
>> >> >> >> excellent.
>> >> >> >>
>> >> >> >> Problem is, it wont shrink from being 100Gb in size - no amout
>> >> >> >> of
>> >> >> >> DBCC
>> >> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
>> >> >> >>
>> >> >> >> Can anyone suggest what I might be missing?
>> >> >> >>
>> >> >> >> Thanks
>> >> >> >>
>> >> >> >> Nick
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi Nick
I hae certainly had problems with mount points in the past so gave up using
them, they didn't allow database files larger than the host disc size to be
created. I don't know if that is still a problem though!!
John
"Nick" wrote:
> Well, it took a while but I've sussed it.
> It turns out, that my log files were on a Windows mount-point which should
> be supported on SQL2K & Windows Server 2003 Enterprise non-clustered.
> I thought maybe SQL Server needed some more log space for the data file
> shrink, but, whenever I tried expanding the logs SQL told me "File not
> found". Very weird. So, I detached the db then moved the logs to a normal
> drive and re-attached it. I could then increase the log sizes and the data
> file shrank straight away.
> Maybe SQL Server was unable to claim log-space for the shrink but didn't
> report the error correctly as it was getting confused with the mount-points?
> I'm guessing, but it works at last and many thanks for your assistance.
> Nick
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:285E26C2-FF94-4539-B890-088B681AC999@.microsoft.com...
> > Hi Nick
> >
> > I presume that you did a sp_helpfile again after the DBCC SHRINKFILE?
> >
> > I doubt it is file permissions are a problem.
> >
> > Have you tried DBCC SHRINKDATABASE?
> >
> > John
> >
> > "Nick" wrote:
> >
> >> Model - what a good idea! SQL can't shrink a database smaller than that
> >> can
> >> it?
> >> With much excitement, I check model's size... 1.5Mb!
> >> Damm!!!
> >> DBCC SHRINKFILE (N'Merlin_Data' , 94000) also comes back instantly, less
> >> than 1 sec.
> >>
> >> Logged in as sa.
> >>
> >> File permissions maybe'
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:FE88B764-03DC-4AD0-A6D7-5264410E1F91@.microsoft.com...
> >> > Hi Nick
> >> >
> >> > If the command was working it should take some time, you may want to
> >> > try a
> >> > larger size say 94000. What size is model?
> >> >
> >> > John
> >> >
> >> > "Nick" wrote:
> >> >
> >> >> Thats what I thought.
> >> >>
> >> >> Even in enterprise manager, it says "Min file size 150mb" so I try
> >> >> shrinking
> >> >> it to 200Mb and it just doesn't budge. Not an inch.
> >> >>
> >> >> Geeze, it's frustrating as I need this file as small as possible as
> >> >> I've
> >> >> moved all the data out of it.
> >> >>
> >> >> Is there any way of seeing if SQL is even attempting to shrink the
> >> >> file?
> >> >> The
> >> >> DBCC command returns very quickly (< 1sec) so I guess it's an off-line
> >> >> process.
> >> >>
> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> >> news:F2199DEB-6438-467B-B909-DBB935583740@.microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > The file Merlin_Data is 97280000 KB and only uses 153600 KB (150 MB)
> >> >> > so
> >> >> > your
> >> >> > shrinkfile should have worked!
> >> >> >
> >> >> > Try
> >> >> >
> >> >> > DBCC SHRINKFILE ( 1, 200 )
> >> >> > or
> >> >> > DBCC SHRINKFILE ( 1 )
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Nick" wrote:
> >> >> >
> >> >> >> John,
> >> >> >>
> >> >> >> sp_spaceused:
> >> >> >> MyDB 290343.88 MB 69625.83 MB
> >> >> >>
> >> >> >> Reserved Data Index Unused
> >> >> >> 155007152 KB 92248808 KB 5123888 KB 57634456 KB
> >> >> >>
> >> >> >> sp_helpfile:
> >> >> >>
> >> >> >> name fileid filename filegroup size maxsize growth usage
> >> >> >>
> >> >> >> Merlin_Data 1 E:\Data1\MerlinDB\Merlin_Data.MDF PRIMARY 97280000 KB
> >> >> >> Unlimited 153600 KB data only
> >> >> >>
> >> >> >> Merlin_Log 2 E:\Logs1\MerlinDB\Logs1\MerlinDB_Log1.LDF NULL
> >> >> >> 14592448
> >> >> >> KB
> >> >> >> Unlimited 10% log only
> >> >> >>
> >> >> >> Merlin_Log2 3 e:\logs1\merlindb\Merlin_Log2.ldf NULL 22561024 KB
> >> >> >> Unlimited
> >> >> >> 10% log only
> >> >> >>
> >> >> >> MerlinDB 5 e:\data1\MerlinDB\MerlinDB.ndf PRIMARY 30720000 KB
> >> >> >> Unlimited
> >> >> >> 10%
> >> >> >> data only
> >> >> >>
> >> >> >> MerlinDB_2 6 e:\data1\merlindb\MerlinDB_2.ndf PRIMARY 30720000 KB
> >> >> >> Unlimited
> >> >> >> 10% data only
> >> >> >>
> >> >> >> MerlinDB_3 7 e:\data1\merlindb\MerlinDB_3.ndf PRIMARY 33792000 KB
> >> >> >> Unlimited
> >> >> >> 10% data only
> >> >> >>
> >> >> >> MerlinDB_4 8 e:\data1\merlindb\MerlinDB_4.ndf PRIMARY 33792000 KB
> >> >> >> Unlimited
> >> >> >> 10% data only
> >> >> >>
> >> >> >> Merlin_Log3 9 e:\logs1\merlindb\Merlin_Log3.ldf NULL 22561024 KB
> >> >> >> Unlimited
> >> >> >> 10% log only
> >> >> >>
> >> >> >> Merlin_Log4 10 e:\logs1\merlindb\Merlin_Log4.ldf NULL 11293632 KB
> >> >> >> Unlimited
> >> >> >> 10% log only
> >> >> >>
> >> >> >> I'm trying to shrink Merlin_Data - the primary file - by running:
> >> >> >>
> >> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200)
> >> >> >> and I've also tried:
> >> >> >> DBCC SHRINKFILE (N'Merlin_Data' , 200, TRUNCATEONLY)
> >> >> >>
> >> >> >> Any pointers really appreciated!
> >> >> >>
> >> >> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> >> >> news:0790504B-90AF-4CC8-8E9B-0FB395B51927@.microsoft.com...
> >> >> >> > Hi
> >> >> >> >
> >> >> >> > Can you post your DBCC SHRINKFILE command and the output from
> >> >> >> > sp_spaceused,
> >> >> >> > sp_helpfile.
> >> >> >> >
> >> >> >> > John
> >> >> >> >
> >> >> >> > "Nick" wrote:
> >> >> >> >
> >> >> >> >> Hi,
> >> >> >> >>
> >> >> >> >> (SQL 2K)
> >> >> >> >>
> >> >> >> >> We used to have a database with 1 100Gb data file
> >> >> >> >>
> >> >> >> >> I've now created 5 25Gb files and ran the DBCC EmptyFile command
> >> >> >> >> to
> >> >> >> >> move
> >> >> >> >> all
> >> >> >> >> the data out of the primary file to the new ones.
> >> >> >> >>
> >> >> >> >> Enterprise Mgr reports the primary file is 99% empty -
> >> >> >> >> excellent.
> >> >> >> >>
> >> >> >> >> Problem is, it wont shrink from being 100Gb in size - no amout
> >> >> >> >> of
> >> >> >> >> DBCC
> >> >> >> >> ShrinkFile, backing up logs, full backups etc will shrink it.
> >> >> >> >>
> >> >> >> >> Can anyone suggest what I might be missing?
> >> >> >> >>
> >> >> >> >> Thanks
> >> >> >> >>
> >> >> >> >> Nick
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Thursday, March 29, 2012

DBCC SHRINKFILE ('filename' , EMPTYFILE) not working

DBCC SHRINKFILE ('filename' , EMPTYFILE) not working

DBCC ShrinkFile

Thursday, March 8, 2012

dbcc emptyfile

dbcc emptyfile

dbcc emptyfile

DBfile usage

Blog Archive

About Me