Re: Shrinking the MDG while Maintaining Indexes
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/28/04
- Next message: Jéjé: "memory usage and configuration..."
- Previous message: Aaron: "Re: Query execution plan different between production/test - same"
- In reply to: Karl Pierburg: "Re: Shrinking the MDG while Maintaining Indexes"
- Next in thread: Karl Pierburg: "Re: Shrinking the MDG while Maintaining Indexes"
- Reply: Karl Pierburg: "Re: Shrinking the MDG while Maintaining Indexes"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 28 Jul 2004 09:01:22 -0400
A Reindex or IndexDefrag will essentially rebuild the indexes (or the table
in the case of a clustered index) and will attempt to adhere to the fill
factor (see the link below for more details). This process usually results
in less pages overall for that index and less fragmentation. But it has no
bearing what so ever on the .mdf or .ldf's overall size. That will only be
accomplished with DBCC SHRINKDATABSE or SHRINKFILE. I suspect that 20GB is
where you DB is happy for the moment and until you add a significant amount
of data it will probably not grow again unless you shrink it. Will you be
at PASS this year? If so then you should attend my session on Index
Fragmentation and I will explain all this and more<g>.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag
-- Andrew J. Kelly SQL MVP "Karl Pierburg" <KarlPierburg@discussions.microsoft.com> wrote in message news:237BD0F0-A026-4E39-A46B-E35348D8FF62@microsoft.com... > Andrew! > > No - we did go through all of this (on another other post), my concern was that it seemed like the database grew each night. When I used to shrink it every night, it stayed around 12 GB. My first night after not shrinking it went to 15 and then 20 the next night. I shrunk the MDF yesterday and reindexed it, this morning it's back to 20 so maybe it's reached its "happy place" at 20 gb. I have the space, I just didn't want it to keep growing by leaps and bounds every night. > > Let me ask you this question then. Say I insert a bunch of records into a table (causing the table / page count / extrent count to grow) and then delete them. Will a INDEXDEFRAG or REINDEX reclaim and resize the table (and MDF), or is it like a log file, where even after you truncate the log, the LDF is still large until you shrink it? > > "Andrew J. Kelly" wrote: > > > Karl, > > > > I thought we went through all of this<g>. Your database grew because you > > reindexed it and it needs a lot of space in the database to do this. If you > > shrink the file you will fragment your indexes again and your back to where > > you started. Then when you reindex the database will grow again. Starting > > to get the picture<g>. It's a vicious cycle that will keep happening over > > and over and it will only have a negative effect. The bottom line is you > > need plenty of free space in the database to operate properly. So just > > leave it as is and this won't keep happening. There is no penalty for too > > much free space in the database but there is for too little. Hope that > > clears it up. > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Karl Pierburg" <KarlPierburg@discussions.microsoft.com> wrote in message > > news:800E038E-5754-45FD-8C5D-24D003433F09@microsoft.com... > > > I've recently learned (via this Newsgroup), of the perils of using DBCC > > ShrinkDatabase and DBXCC ShrinkFile in regards to indexe fragementation > > (i.e. that these operations basically heavily fragment your indexes) > > > > > > Here's my situation: During nightly batch my MDF grows by about 2 GB a > > night. In the past, I would execute a DBCC ShrinkDatabase and all would be > > well, except for my indexes would be all fragmented. > > > > > > I ran sp_spaceused against my database, and found the database size to be > > about 20 GB, with 8 GB of unallocated space, which sounds right to me, my > > database is about 8 GB larger than normal. > > > > > > I can I reclaim this space on the OS? Can I run DBCC ShrinkFile(DataFile, > > 12000), or will this mess up my indexes? > > > > > >
- Next message: Jéjé: "memory usage and configuration..."
- Previous message: Aaron: "Re: Query execution plan different between production/test - same"
- In reply to: Karl Pierburg: "Re: Shrinking the MDG while Maintaining Indexes"
- Next in thread: Karl Pierburg: "Re: Shrinking the MDG while Maintaining Indexes"
- Reply: Karl Pierburg: "Re: Shrinking the MDG while Maintaining Indexes"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
