SQL 7 DB Size question

SQL 7 DB Size question

Post by robert » Tue, 26 Jun 2001 21:26:44



Have a SQL7 DB that uses 8GB...This number is based on tables and indexes
tab in enterprise manager...Ran a DTS to copy the database...The copy is
4GB...Based on an anlysis of record count in tables the 4GB number makes
sense...Three questions:  Why is the original bigger than it needs to be?
How can I get rif of the wasted space?  Can someone point me to a discussion
of the differences in storage requirements and performance between vchar and
char data types.

Thanx

Bob

 
 
 

SQL 7 DB Size question

Post by Tibor Karasz » Tue, 26 Jun 2001 21:49:08


You can have empty space on the pages, needing more pages. "Reclaim" by rebuilding
indexes. (DBCC DBREINDEX)

You can have non-used pages in extents. Reclaim as above.

You can have non-used extents in the database. Reclaim by shrinking database file size
(DBCC SHRINKDB or DBCC SHRINKFILE).

Note that first two does not shrink file size. Read in books online about physical
database architecture to understand the page/extent concept.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com


Quote:> Have a SQL7 DB that uses 8GB...This number is based on tables and indexes
> tab in enterprise manager...Ran a DTS to copy the database...The copy is
> 4GB...Based on an anlysis of record count in tables the 4GB number makes
> sense...Three questions:  Why is the original bigger than it needs to be?
> How can I get rif of the wasted space?  Can someone point me to a discussion
> of the differences in storage requirements and performance between vchar and
> char data types.

> Thanx

> Bob


 
 
 

1. DB Sizes: SQL Server 6.5 Admin Question

Because v6.5 will not auto grow my databases, I have been monitoring the db
sizes then manually growing them as needed.  Fortunately I only need to grow
a few db's once per month or so.

I am investigating methods of automating the process of determining space
left in the databases.  By viewing the db properties in the Enterprise
Manager, a db might show 50 meg free.  When I use a sp_spaceused command in
Query Analyzer, I see the unallocated space and unused columns.  Both these
columns values are usually quite different than the amount reported in the
db properties in EM.

Which number should I trust?  Is there any other method, including writing a
new stored procedure, that I can use to report the correct number?

John Mull
Programmer/Analyst
Catawba County Government
Newton, North Carolina USA

2. US - SYSTEMS ARCHITECTS NEEDED

3. Question about the file allocated size, used size in SQL Server 7.0

4. Rebuildm taking too long!

5. Questions about file allocated size, used size in SQL Server 7.0

6. NAIUA domain name restored

7. Newbie Questions..

8. DB size question

9. "Extra" DB Size / Performance Question

10. RMAN: question on DB sizing

11. DB size increased after reducing fields size

12. Dump size doesn't accord with the Data size of DB