Re: Database Growth & Space Recovery Problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 07:01:24 -0400

One thing is that you should be suspicious of the numbers , as you are..

SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..

For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true

The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results....If the numbers
still look funny, we'll try something else... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Harcharan Jassal" <hjjassal@yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
>
> SP_SPACEUSED 'BUILD' Results
> name   rows  reserved  data  index_size unused
>
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB  32 KB
>
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB  32 KB
>
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB  604744 KB 3456 KB 80 K
>
> 4. After Executing DBCC DBReindex (build,'',70)
>
> Build 663211 124096 KB 123392 KB 712 KB  -8 KB
>
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
>
> Regards,
> Harcharan
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Quantcast