DBCC SHRINKDATABASE

My old thread seems to evoke no response anymore, so I will just post a new
one.
database_name=AP_ODS
database_size=76024.00 MB
unallocated space=49730.95 MB
Above is the output when I ran the statements below:
sp_spaceused @.updateusage=TRUE
go
DBCC SHRINKFILE ('WAREHOUSE_DATA', TRUNCATEONLY)
go
Can anyone please help me out here? How the hell can I get the 49GB free spa
ce released to the OS?
Thank you.Alex,
TruncaleOnly may not do anything at all. From BOL:
Causes any unused space in the files to be released to the operating system
and shrinks the file to the last allocated extent, reducing the file size
without moving any data. No attempt is made to relocate rows to unallocated
pages. target_size is ignored when TRUNCATEONLY is used.
You see all shrink operations happen from the end of the file. If there is
data near the end and free space near the beginning (and you don't specify
TruncateOnly) sql server has to physically move that data to the beginning
to free up the space at the end. So if you omit the TruncateOnly it should
shrink but it may take a while. It will also more than likely heavily
fragment your tables. To unfragment them you need to reindex them and that
will cause the database to grow again and your back to where you started.
DB's need plenty of free space in them to operate properly. If you don't
care about frgamentation then go ahead and shrink it. If you do, and by the
name I would assume you would, leave the free space.
Andrew J. Kelly
SQL Server MVP
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:04DC64C2-6683-425E-BB9C-7F664457F5A1@.microsoft.com...
> My old thread seems to evoke no response anymore, so I will just post a
new one.
> database_name=AP_ODS
> database_size=76024.00 MB
> unallocated space=49730.95 MB
>
> Above is the output when I ran the statements below:
> sp_spaceused @.updateusage=TRUE
> go
> DBCC SHRINKFILE ('WAREHOUSE_DATA', TRUNCATEONLY)
> go
> Can anyone please help me out here? How the hell can I get the 49GB free
space released to the OS?
> Thank you.
>|||Andrew,
I appreciate your response. Let us for a moment forget that there is space o
n the server. Technically, what is the best way to get the free space back?
"So if you omit the TruncateOnly it should shrink but it may take a while."
- No sir, ommiting the TRUNCATEONLY option does nothing as well.
Worst case, what's the explanation for sql server hogging 70% of table space
for nothing?!!
Thanks.
-- Andrew J. Kelly wrote: --
Alex,
TruncaleOnly may not do anything at all. From BOL:
Causes any unused space in the files to be released to the operating system
and shrinks the file to the last allocated extent, reducing the file size
without moving any data. No attempt is made to relocate rows to unallocated
pages. target_size is ignored when TRUNCATEONLY is used.
You see all shrink operations happen from the end of the file. If there is
data near the end and free space near the beginning (and you don't specify
TruncateOnly) sql server has to physically move that data to the beginning
to free up the space at the end. So if you omit the TruncateOnly it should
shrink but it may take a while. It will also more than likely heavily
fragment your tables. To unfragment them you need to reindex them and that
will cause the database to grow again and your back to where you started.
DB's need plenty of free space in them to operate properly. If you don't
care about frgamentation then go ahead and shrink it. If you do, and by the
name I would assume you would, leave the free space.
Andrew J. Kelly
SQL Server MVP
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:04DC64C2-6683-425E-BB9C-7F664457F5A1@.microsoft.com...
> My old thread seems to evoke no response anymore, so I will just post a
new one.
> database_size=76024.00 MB
> unallocated space=49730.95 MB
> go
> DBCC SHRINKFILE ('WAREHOUSE_DATA', TRUNCATEONLY)
> go
space released to the OS?
>|||Alex,
What version are you running? In 7.0 sometimes it was required to backup
the log one or more times to get this to happen. It may not be immediate.
If you have open trans it can also effect it. The other thing that is most
likely the problem is that if the file was originally created at that size
it can not be shrunk below that. Can you post the results of the Shrinkfile
command?
Andrew J. Kelly
SQL Server MVP
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:6654D78D-C1BA-4F9A-8823-5AE362722D3D@.microsoft.com...
> Andrew,
> I appreciate your response. Let us for a moment forget that there is space
on the server. Technically, what is the best way to get the free space back?
> "So if you omit the TruncateOnly it should shrink but it may take a
while." - No sir, ommiting the TRUNCATEONLY option does nothing as well.
> Worst case, what's the explanation for sql server hogging 70% of table
space for nothing?!!
> Thanks.
> -- Andrew J. Kelly wrote: --
> Alex,
> TruncaleOnly may not do anything at all. From BOL:
> Causes any unused space in the files to be released to the operating
system
> and shrinks the file to the last allocated extent, reducing the file
size
> without moving any data. No attempt is made to relocate rows to
unallocated
> pages. target_size is ignored when TRUNCATEONLY is used.
> You see all shrink operations happen from the end of the file. If
there is
> data near the end and free space near the beginning (and you don't
specify
> TruncateOnly) sql server has to physically move that data to the
beginning
> to free up the space at the end. So if you omit the TruncateOnly it
should
> shrink but it may take a while. It will also more than likely
heavily
> fragment your tables. To unfragment them you need to reindex them
and that
> will cause the database to grow again and your back to where you
started.
> DB's need plenty of free space in them to operate properly. If you
don't
> care about frgamentation then go ahead and shrink it. If you do, and
by the
> name I would assume you would, leave the free space.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Alex G" <anonymous@.discussions.microsoft.com> wrote in message
> news:04DC64C2-6683-425E-BB9C-7F664457F5A1@.microsoft.com...
post a
> new one.
49GB free
> space released to the OS?|||Try this
backup log DBName with truncate_only
dbcc shrinkfile ('DBname',0,truncateonly)
dbcc shrinkfile ('DBName',0,truncateonly)

>--Original Message--
>Alex,
>What version are you running? In 7.0 sometimes it was
required to backup
>the log one or more times to get this to happen. It may
not be immediate.
>If you have open trans it can also effect it. The
other thing that is most
>likely the problem is that if the file was originally
created at that size
>it can not be shrunk below that. Can you post the
results of the Shrinkfile
>command?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Alex G" <anonymous@.discussions.microsoft.com> wrote in
message
>news:6654D78D-C1BA-4F9A-8823-
5AE362722D3D@.microsoft.com...
that there is space
>on the server. Technically, what is the best way to get
the free space back?
it may take a
>while." - No sir, ommiting the TRUNCATEONLY option does
nothing as well.
hogging 70% of table
>space for nothing?!!
BOL:
released to the operating
>system
extent, reducing the file
>size
relocate rows to
>unallocated
is used.
of the file. If
>there is
beginning (and you don't
>specify
that data to the
>beginning
the TruncateOnly it
>should
more than likely
>heavily
need to reindex them
>and that
back to where you
>started.
properly. If you
>don't
it. If you do, and
>by the
space.
wrote in message
7F664457F5A1@.microsoft.com...
anymore, so I will just
>post a
below:
hell can I get the
>49GB free
>
>.
>