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
quote:
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
quote:
>that fails because there are temporary worktables in
there that are still
quote:
>being used, though QA would report as if the file had
been shrunk.
quote:|||Hi,
>--
>Regards
>Ray Mond
>
>.
>
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/defaul...kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
quote:|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
> 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
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...[QUOTE]
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> file? Sometimes,
> there that are still
> been shrunk.|||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...
quote:|||Thanks Guys,
> 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
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
quote:
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
quote:
>of the data from the end of the file to the front of the
file, it only
quote:
>returns any unused end portion of the file... instead
try something like
quote:
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
quote:
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
quote:
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
quote:
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||> Question though, is there an automated way without using
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
the[QUOTE]
>
>.
>
quote:
> 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...[QUOTE]
> 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
>
>
> does NOT move any
> file, it only
> try something like
> then truncate all of
> Charlotte, NC
> (PASS) and it's
> message
> the|||Thanks Paul
quote:
>--Original Message--
of[QUOTE]
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
quote:
>on a bunch of things, including how much you ask the file
to shrink, the
quote:
>distribution of free space at the start of the file, the
speed of your IO
quote:
>system, and most importantly, the amount of concurrent
activity on the
quote:
>system that may block shrink. Remember that shrink is an
online operation
quote:
>and so does not block concurrent activity - this means
you can let it run
quote:
>longer than your maintenance window as long as you can
cope with the small
quote:
>drop in workload throughput from having shrink working in
the database.
quote:
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
quote:
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
option[QUOTE]
over[QUOTE]
it[QUOTE]
of[QUOTE]
parameter[QUOTE]
the[QUOTE]
Enterprise[QUOTE]
>
>.
>
No comments:
Post a Comment