Friday, March 30, 2012

Mapping pages in database file?

Hi there,
Recently for some reason, after I was trying adjusting the indexes om my
test database, the size of the database, balloned from ~4GB to 13GB+. This
is after I rolledback all the changes I did. The database runs in full
recovery mode.
Now I got the transaction log figured out, and with the help of dbcc
loginfo, I can see what's being used in the log. But what I'd like to find
out is how large the parts of the database is; ie. a complete run down, on
what SQL Server 2000 uses storage space on.
For reference, a dbcc shrinkdatabase gives the following -
11 2 16120 128 16120 128
According to BOL, the last number (Estimated Pages) is what it guess on the
database can be shrunked to, however, that's extremely unlikely, as I know,
the DB should be around 4GB just after being created and filled with data.
How do I go about this little project?
--
Necessity is the plea for every infringement of human freedom. It is the
argument of tyrants; it is the creed of slaves.
-- William Pitt, 1783Kim Noer wrote:
> How do I go about this little project?
By using DBCC SHOWCONTIG(table), investigating the Avg. Page Density (full)
and remembering that fillfactor works reverse of what previously thought. To
boil it down, I applied a fillfactor of 15%, when I should had applied 85%.
Clever.
--
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kim Noer" <kn@.nospam.dk> wrote in message
news:esALBYs8FHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Recently for some reason, after I was trying adjusting the indexes om my
> test database, the size of the database, balloned from ~4GB to 13GB+. This
> is after I rolledback all the changes I did. The database runs in full
> recovery mode.
> Now I got the transaction log figured out, and with the help of dbcc
> loginfo, I can see what's being used in the log. But what I'd like to find
> out is how large the parts of the database is; ie. a complete run down, on
> what SQL Server 2000 uses storage space on.
> For reference, a dbcc shrinkdatabase gives the following -
> 11 2 16120 128 16120 128
> According to BOL, the last number (Estimated Pages) is what it guess on
> the database can be shrunked to, however, that's extremely unlikely, as I
> know, the DB should be around 4GB just after being created and filled with
> data.
> How do I go about this little project?
> --
> Necessity is the plea for every infringement of human freedom. It is the
> argument of tyrants; it is the creed of slaves.
> -- William Pitt, 1783
>

No comments:

Post a Comment