Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » Space for Database SQL Server 2005
Add to briefcase

Space for Database SQL Server 2005 Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mianjum
Posted Wednesday, September 17, 2008 11:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 30, 2009 7:06 AM
Points: 8, Visits: 19
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.
Post #571204
Ninja's_RGR'us
Posted Wednesday, September 17, 2008 11:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 21,357, Visits: 9,538
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.
Post #571209
Rob Symonds
Posted Wednesday, September 17, 2008 12:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 9:58 AM
Points: 381, Visits: 495
Did you shrink the database/data file after you truncated the table?
Post #571228
Robert klimes
Posted Wednesday, September 17, 2008 12:11 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:50 PM
Points: 890, Visits: 1,859
DBCC SHRINKDATABASE will release unused space

Bob
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #571229
Jeffrey Williams 3188
Posted Wednesday, September 17, 2008 12:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317, Visits: 9,216
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #571234
mianjum
Posted Thursday, September 25, 2008 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 30, 2009 7:06 AM
Points: 8, Visits: 19
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.
Post #576421
sgmunson
Posted Friday, September 26, 2008 7:43 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:20 AM
Points: 1,446, Visits: 1,883
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)

Weight Loss Tips
Post #576842
CrazyMan
Posted Friday, September 26, 2008 8:08 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 3:00 PM
Points: 770, Visits: 1,593
check if the database is in Simple recovery mode, then try shrink the files,

:)
Post #576879
Wilfred van Dijk
Posted Friday, September 26, 2008 8:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:56 AM
Points: 898, Visits: 1,045
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
Post #576905
Wilfred van Dijk
Posted Friday, September 26, 2008 8:27 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:56 AM
Points: 898, Visits: 1,045

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
Post #576909
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.