Print Page | Close Window

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. Wink
 
 



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net