DBCC Shrinkfile

Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
--
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
>that fails because there are temporary worktables in
there that are still
>being used, though QA would report as if the file had
been shrunk.
>--
>Regards
>Ray Mond
>
>.
>|||Hi,
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
be residing on the end of the file, which prevents any significant
truncation.
--
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> >--Original Message--
> >By any chance, are you trying to shrink the tempdb data
> file? Sometimes,
> >that fails because there are temporary worktables in
> there that are still
> >being used, though QA would report as if the file had
> been shrunk.
> >
> >--
> >Regards
> >Ray Mond
> >
> >
> >.
> >|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Thanks Guys,
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
>of the data from the end of the file to the front of the
file, it only
>returns any unused end portion of the file... instead
try something like
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> Dear All,
>> I have just tried to shrink a file, both in Enterprise
>> Manager and in Query Analyser.
>> Within EM it hung.
>> Within QA it said it shrinked it but when I looked at
the
>> size of the file it was still the same size.
>> The code I used in QA was
>> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> Can anyone gice me pointers ?
>> Thanks
>> J
>
>.
>|||> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> >--Original Message--
> >THe command you are using with the Truncateonly parameter
> does NOT move any
> >of the data from the end of the file to the front of the
> file, it only
> >returns any unused end portion of the file... instead
> try something like
> >
> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
> >
> >this command will move data from the end to the front ,
> then truncate all of
> >the unused portion ...
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> >> Dear All,
> >>
> >> I have just tried to shrink a file, both in Enterprise
> >> Manager and in Query Analyser.
> >>
> >> Within EM it hung.
> >>
> >> Within QA it said it shrinked it but when I looked at
> the
> >> size of the file it was still the same size.
> >>
> >> The code I used in QA was
> >>
> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> >>
> >> Can anyone gice me pointers ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||Thanks Paul
>--Original Message--
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
>on a bunch of things, including how much you ask the file
to shrink, the
>distribution of free space at the start of the file, the
speed of your IO
>system, and most importantly, the amount of concurrent
activity on the
>system that may block shrink. Remember that shrink is an
online operation
>and so does not block concurrent activity - this means
you can let it run
>longer than your maintenance window as long as you can
cope with the small
>drop in workload throughput from having shrink working in
the database.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
>> Thanks Guys,
>> It worked using the dbcc shrinkfile(FileName, 4670)
option
>> a couple of you guys sugested.
>> The main problem here was time. In this case it took
over
>> 20 minutes to shrink, and I was about to cancel it when
it
>> worked ;)
>> Anyway thanks for that.
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>> Again thanks for your help
>> J
>>
>>
>> >--Original Message--
>> >THe command you are using with the Truncateonly
parameter
>> does NOT move any
>> >of the data from the end of the file to the front of
the
>> file, it only
>> >returns any unused end portion of the file... instead
>> try something like
>> >
>> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>> >
>> >this command will move data from the end to the front ,
>> then truncate all of
>> >the unused portion ...
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> >> Dear All,
>> >>
>> >> I have just tried to shrink a file, both in
Enterprise
>> >> Manager and in Query Analyser.
>> >>
>> >> Within EM it hung.
>> >>
>> >> Within QA it said it shrinked it but when I looked at
>> the
>> >> size of the file it was still the same size.
>> >>
>> >> The code I used in QA was
>> >>
>> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> >>
>> >> Can anyone gice me pointers ?
>> >>
>> >> Thanks
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>