Database Compact? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database Compact?

Author

Topic

enaran
Starting Member

8 Posts

Posted - 2007-02-11 : 16:38:21

Hi,
I am wondering why a database that was 2Gb in size would come down to just 300Mb when I copy all the data from the origional database into a blank new one.
I ran a database shrink on the origional database but that only reduced it from 2.1 Gb to 2Gb.
I was shocked to see the size difference when I copied the data.
As far as I can tell the copied database still contains all the records of the origional.

I remember that MSAccess had a Compact function that literally copied all the data from one databse into a new empty one - is there the equivalent for SQL?

Any thoughts?

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-11 : 16:57:27

There are a lot of possible reasons for this.

What are the results of this query when you run it in each database?


select
	groupid,
	AllocatedMB = size/128. ,
	UsedMB = fileproperty( name,'SpaceUsed')/128. ,
	name
from
	sysfiles a
order by
	groupid,
	fileid


exec sp_spaceused null,'true'

CODO ERGO SUM

enaran
Starting Member

8 Posts

Posted - 2007-02-11 : 17:13:39

Hi Michael,

Results from Origional Database (Large)

groupid|AllocatedMB|UsedMB|Name
0|0.4922|0.2188|tim_log
1|1754.4375|1754.4375|tim

New Database with copied records from origional

groupid|AllocatedMB|UsedMB|Name
0|1|0.3438|zzztim_log
1|409|409|zzztim

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-11 : 17:18:50

You didn't run the stored procedure?

CODO ERGO SUM

enaran
Starting Member

8 Posts

Posted - 2007-02-11 : 17:33:24

Sorry, I had run it all together

Results of stored procedure

Large Data

database_name|database_size|unallocated space
tim|1754.93 MB|0.10 MB

Small Database
zzztim|410.00 MB|0.10 MB

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-11 : 17:59:40

The stored procedure produces two result sets.

CODO ERGO SUM

enaran
Starting Member

8 Posts

Posted - 2007-02-11 : 18:20:09

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-11 : 19:11:52

More than likely, your original database was badly in need of reindexing.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2007-02-12 : 02:06:15

DBCC SHOWCONTIG (MyTable) WITH ALL_INDEXES

will show you the fragmentation on a table (and each of the table's indexes), and thus whether it needs reindexing.

Results:
Scan Density -- 100% is best, 0% worst
Logical Scan Fragmentation - Lower is better
Extent Scan Fragmentation - Lower is better
Avg. Bytes free per page - Lower is better
Avg. Page density (full) - Higher is better

Don't worry about any table with very few Pages Scanned

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources