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