Re: Shrinking Datafile
- From: "Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx>
- Date: Mon, 17 Aug 2009 15:52:36 -0400
Quite honestly if you are that tight on space this is only a very short term stop gap and you will have to find more disk space anyway. To answer the question about the space needed for a given index rebuild it is approx 1.2 times the size of the individual index. But if you don't have lots of free space the rebuilt index will almost never be rebuilt contiguously as the free space will invariably be small pockets here and there in the file. Yes you can defrag or reorg the indexes with less space but you don't always get the defragmentation percent you are looking for. This will vary from db to db though. What do you hope to accomplish by adding new filegroups? First off you will need space to add them so you will be in a catch 22 type scenario in which you need to move an index, shrink the file, grow the other file etc. This will end up in even worse fragmentation than what you have now. It will also almost assuredly mean you will have windows level file level fragmentation on top of the SQL Server logical fragmentation. If you don't have more disk space I would continue with the route you are going and just be aware you will have to add space at some point anyway and prepare for that as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@xxxxxxxxx> wrote in message news:3f58e00e-a099-4300-8575-6fbab280aabe@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Andrew,
Thanks for the reply. The issue is this DB is on my main OLTP server
and I can really use the breathing room on some other databases. I
understand what shrink will do to my indexes, and I am prepared to
take the hit in order for the shrink to occur. Once the shrink is
complete I was planning on running my weekly Maint job over the
database to defragment the indexes. I rarely completely rebuild
indexes unless they are on smaller tables and it can be done quickly.
I defrag them weekly as part of my maint strategy a variation on the
AIT scripts from MS.
My largest index on any given table in this DB is 2.5 gig, clustered.
I am under the assumption that that would be the max I would need if I
rebuilt the indexes. I really can use the extra 9 or 10 gig if I can
get the shrink to finish. I actually am thinking about creating
another filegroup and splitting it into two or three files, then
moving the tables to the new filegroup. What are your thoughts on
that?
Thanks again!
David Hay
On Aug 17, 2:28 pm, "Andrew J. Kelly" <sqlmvpnooos...@xxxxxxxxxxxx>
wrote:
The only way to speed up shrink file is to get better hardware and make sure
the log file is on a fast array by itself. But I think what you are
attempting to do is pointless. 33GB free in a 160GB db is the least amount
of free space I would want to have. You need free space for lots of
operations the least of which is index creation and rebuilds. Removing that
free space with shrink file will fragment your indexes horribly and you will
suffer performance issues as a result. Then when you attempt to rebuild the
indexes you will just grow again. I would leave it where it is at. If that
is causing you problems then please let us know what those are and maybe we
can suggest a better alternative.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
.
- References:
- Shrinking Datafile
- From: David Hay
- Re: Shrinking Datafile
- From: Andrew J. Kelly
- Re: Shrinking Datafile
- From: David Hay
- Shrinking Datafile
- Prev by Date: RE: Sql Server 2008 workgroup downgrade
- Next by Date: RE: Installing Adventure Works sample databases - need help
- Previous by thread: Re: Shrinking Datafile
- Next by thread: Re: Shrinking Datafile
- Index(es):
Relevant Pages
|
