| Author |
Shrink 450 GB Database Quickly
|
|
| Joe K. 2005-09-10, 11:23 am |
|
I have a SQL Server 2000 database that is 450 GB size (Newyork.mdf) and 170
GB is unused space.
What is the quickest and safe way to shrink this large database to get back
the unused disk space?
Thank You,
| |
| Hari Prasad 2005-09-10, 11:23 am |
| Hi,
Use the DBCC SHRINKFILE .
If the LDF file size is huge then; take a transacion log backup before
running DBCC SHRINKFILE. Since the size you are planning to shrink is very
high
you could shrink the file in pieces while database activity is is less.
DBCCSHRINKFILE command is an online operation and is Safe.
See the below article from Tibor:-
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@discussions.microsoft.com> wrote in message
news:A73579CE-7F9D-4C79-89AA- 1CF2097FC0D4@microso
ft.com...
>
> I have a SQL Server 2000 database that is 450 GB size (Newyork.mdf) and
> 170
> GB is unused space.
>
> What is the quickest and safe way to shrink this large database to get
> back
> the unused disk space?
>
> Thank You,
| |
|
| Hari,
Is that true that shrinking only pushes the pages back to the beginning of
the file to fill the empty spaces? Therefore there's not reorganization of
pages?
"Hari Prasad" < hari_prasad_k@hotmai
l.com> wrote in message
news:#om4MJitFHA.1284@tk2msftngp13.phx.gbl...
> Hi,
>
> Use the DBCC SHRINKFILE .
>
> If the LDF file size is huge then; take a transacion log backup before
> running DBCC SHRINKFILE. Since the size you are planning to shrink is very
> high
> you could shrink the file in pieces while database activity is is less.
> DBCCSHRINKFILE command is an online operation and is Safe.
>
> See the below article from Tibor:-
>
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "Joe K." <Joe K.@discussions.microsoft.com> wrote in message
> news:A73579CE-7F9D-4C79-89AA- 1CF2097FC0D4@microso
ft.com...
>
>
| |
| Andrew J. Kelly 2005-09-11, 1:23 pm |
| A shrink only moves page and extents, it does not move rows. What you
generally end up with is a disorganization as compared to what they were
before with regards to physical and logical order of the pages. The rows are
not touched. The shrinking process tends to cause a lot of fragmentation in
logical to physical page order.
--
Andrew J. Kelly SQL MVP
"Leila" <Leilas@hotpop.com> wrote in message
news:eZKUPLvtFHA.3628@TK2MSFTNGP14.phx.gbl...
> Hari,
> Is that true that shrinking only pushes the pages back to the beginning of
> the file to fill the empty spaces? Therefore there's not reorganization of
> pages?
>
>
> "Hari Prasad" < hari_prasad_k@hotmai
l.com> wrote in message
> news:#om4MJitFHA.1284@tk2msftngp13.phx.gbl...
>
>
| |
|
| Thanks Andrew,
So I must necessarily recreate or defrag my indexes?
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:Og$kAUvtFHA.1596@TK2MSFTNGP10.phx.gbl...
> A shrink only moves page and extents, it does not move rows. What you
> generally end up with is a disorganization as compared to what they were
> before with regards to physical and logical order of the pages. The rows
are
> not touched. The shrinking process tends to cause a lot of fragmentation
in
> logical to physical page order.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Leila" <Leilas@hotpop.com> wrote in message
> news:eZKUPLvtFHA.3628@TK2MSFTNGP14.phx.gbl...
of[color=darkred]
of[color=darkred]
and[color=darkred]
get[color=darkred]
>
>
| |
| Andrew J. Kelly 2005-09-11, 8:23 pm |
| Correct and to do that properly you need lots of free space in the
datafiles. So don't shrink the db too much to begin with or it will just
grow again.
--
Andrew J. Kelly SQL MVP
"Leila" <Leilas@hotpop.com> wrote in message
news:OghZBvvtFHA.3500@TK2MSFTNGP09.phx.gbl...
> Thanks Andrew,
> So I must necessarily recreate or defrag my indexes?
>
>
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:Og$kAUvtFHA.1596@TK2MSFTNGP10.phx.gbl...
> are
> in
> of
> of
> and
> get
>
>
|
|
|
|