Releasing free space allocated to a table.
-
Using Spotlight for SQL server, we've noticed that there is a lot of free space on various tables within the database (2K, sp3), some as high as 46% free. How do I reclaim that space? I've tried simply shrinking the data file of the database. It actually shrunk the file but the free spac remains. Any comments would be greatly appreciated.
Terry -
First I don't know the answer to the question you are asking.
It seems to me that there are limitations to the shrinking process, possibly because of how the page/extent process is designed.
If it is really important to you to get your db down to minimum size, maybe you could do the following (not sure about the exact steps). Maybe the following process could be tried on a test Db first.
1. via single user mode for db
2. backup DB
3. run sp_spaceused to determine the size of the data within the database.
4. define a new database with the size determined (or slightly more space).
5. copy old db to new db.
6. drop old db.
7. rename new db to old db name.
Other ideas?
GaryA
-
Check out DBCC SHRINKDATABASE in the Books OnLine.
-SQLBill
BOL=Books OnLine = Microsoft's SQL Server Help. Installed as part of the Client Tools. Found at Start>Programs>Microsoft SQL Server>Books OnLine or in Query Analyzer select Help.
-
SQLBill,
Are you suggesting that DBCC SHRINKDATABASE could be given a targetpercent of zero, if someone wants to squeeze out as much space as possible?
GaryA
-
Yes, with 0 or no number (DBCC SHRINKDATABASE mydatabase) will shrink the database to as small as it can get, but no smaller than what the mydatabase was set to as it's base size.
DBCC SHRINKFILE will let you shrink the database even smaller. It will let the database shrink down to the size of the MODEL database.
Remember, no matter what method you use it will NOT shrink the database down to a size smaller than the amount of data. Shrinking only deals with the free space.
-SQLBill
-
Won't this potentially lead to physical framentation with shrinks and then auto grows? Will the backup, restore and rename option be the best approach? I realize it would probably be a little more time consuming but I'm trying to find the best option with the best results each time.
Terry -
tosscrosby
Some scattered thoughts.
There are maybe two types of fragmentation that affect DBs.
One type.
When SQL Server asks for additional disk space, to increase a DB file size, from the operating system, it seems to me that there is a good possibility that the chunk acquired is not contiguous with the existing file containing the DB. Meaning, likely, that the more offen a file grows, the more likely this type of fragmentation will occur.
Second type.
As data and indexes are maintained, the DB suffers page splits and index splits within the allocated space area.
There are ways of dealing with each type.
However I would not say that just because there is fragmentation, of either type, means you will suffer poor performance.
Of course, we all should strive towards good design and maintenance practices.
It might be possible, I don't know, to add a new file to the DB with the size needed and then use DBCC SHRINKFILE with emptyfile option (hoping for a space allocation from the OS which is contiguous) and then deleting the emptied file.
It seems to me that sticking with DBCC SHRINKDATABASE and DBCC SHRINKFILE should be effective and preferred.
GaryA
-
The big problem is the shrink will get rid of free space in all tables. So then when it does regrow you will have some slowness (as it grows) and potential for fragging. But its probably there anyway from the last grow, so whats the big deal? Buy a defrag program and move on.
-
put a clustering index on it and use rebuild index or indexdefrag
also take a look at pad_index and fillfactor
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
use "DBCC UPDATEUSAGE" on those database / tables / indexes.
also
if any of those tables has undergone a ALTER TABLE and dropped varchar, text columns then use "DBCC CLEANTABLE"
And
If there are clustered indexes on those tables, rebuild them.
-
Note
you must make a Full backup daily (at least) and a transaction Log Backup at 1 hour (at maximum) so the data and the Log file will not increase that much.
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply