September 17, 2008 at 11:26 am
I have a database table having 48GB data. Database size is 75GB and after truncate the table database not releasing the space. How I release the 48GB space from database.
September 17, 2008 at 11:39 am
I remember that in 2000, when there were text or image columns, we had to do a DBCC updateusage IIRC to have that table size updated.
If I'm wrong, please correct me, I never had to do this myself.
Hope this helps you in the right direction.
September 17, 2008 at 12:11 pm
Did you shrink the database/data file after you truncated the table?
September 17, 2008 at 12:11 pm
September 17, 2008 at 12:21 pm
The database file will not become smaller just because you truncated a table within the data file. If you do not have any issues with disk space - I wouldn't worry about the extra space available in the file.
If you really need to shrink the physical file - you can use DBCC SHRINKFILE to make the file smaller. Note: this is not something that should be done on a regular basis. If you decide to shrink the file, do not shrink the file as small as possible - rather, you want to leave at least 20% available after shrinking. Also, once the shrink has been done you will need to rebuild your indexes since shrinking will cause fragmentation in the file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 25, 2008 at 2:38 pm
Yes i shrink the database mdf file and after shrinking the file the database mdf file size reduced to 65GB, and after taking the complete backup, the backup file size is 18GB. When I restore the backup to another system it takes 65GB space.
September 26, 2008 at 7:43 am
Take a look at the size of all the indexes in that database. It's quite easy for indexes to occupy far more space than the data itself. You may also want to consider dropping the indexes, re-shrinking, then recreating the indexes and see where you end up. Again, be mindful of potential performance hits when the file is shrunk to a point where there's no empty space within the file.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 26, 2008 at 8:08 am
check if the database is in Simple recovery mode, then try shrink the files,
🙂
September 26, 2008 at 8:25 am
If you shrink a database, you'll truncate the free space at the end of a file.
Consider this datafile( X=data, .=free)
XXXXXXX.XX.....XX.......XX.XXXXXXX.XXXX............X..............
If you truncate this file, it will be something like this:
XXXXXXX.XX.....XX.......XX.XXXXXXX.XXXX............X
As you can see, a lot of free space is still in the database (this is also for your logfile where X represents active transactions).
In order to reclaim that space, you'll have to reorganize your datafile.
Wilfred
The best things in life are the simple things
September 26, 2008 at 8:27 am
check if the database is in Simple recovery mode, then try shrink the files,
This works only for logfiles
Wilfred
The best things in life are the simple things
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply