Compacting SQL DB
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=14356
Printed Date: 30 March 2026 at 3:26am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Compacting SQL DB
Posted By: rob_andy
Subject: Compacting SQL DB
Date Posted: 21 March 2005 at 4:50pm
| I'm new to MS SQL, I've had to upgrade from Acess to SQL because of the rate of expansion of my car club. I've had a really steep learning curve and now know a fair bit about importing and copying etc... After a lot of trouble shooting I have managed to move my access across into an SQL environment. The SQL package I'm using is only web based and does not use enterprise manager. Does anybody out there know how to stream line my db, at the moment in access it shows as 15mb, when I import and do all the moving about it shows as 35+ MB with a log file of over 50 MB. My limit for both is 200 so I want to get this down as much as possible. Any ideas? Thanks for help in advance. |
Replies:
Posted By: michael
Date Posted: 21 March 2005 at 5:40pm
|
If you don't need anything in your log you can run
backup log yourdbname with truncate_only
dbcc shrinkdatabase (yourdbname) which should compact your db a bit and "empty" the log file. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: rob_andy
Date Posted: 21 March 2005 at 6:30pm
| Wow, thanks so much for that. Tried it and worked a treat. Going to move across tomorrow onto the SQL. Owe you one. R |
Posted By: sifra
Date Posted: 25 March 2005 at 12:25pm
|
DBCC SHRINKFILE works a treat for a few months. If autogrow is on then high frequency shrinking and expanding of the database files will cause extensive OS fragmentation, so your DBCC DBREINDEX will be of little value trying to eliminate extent level fragmentation.
It is better to set your filegroups to a size that allows 6-12 months of growth, and set autogrow off. Fixed size filegroups never fragment at the OS level. SQL server will perform block reads of data pages only if they are logically and physically contiguous.
The risk is that fixed size filegroups will fill up, so set an alert that emails you when your filegroup is > 90% full.
 |