August 1, 2005 at 9:11 am
Our database has about 50% of it's space UNUSED. We've tried shrinking the database and DBCC CLEANTABLE on individual tables but nothing has worked to remove the unused space.
How can we reclaim this space?
August 4, 2005 at 8:00 am
This was removed by the editor as SPAM
August 4, 2005 at 1:42 pm
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
file_name
Is the logical name of the file shrunk. File names must conform to the rules for identifiers. For more information, see Using Identifiers.
file_id
Is the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sysfiles in the current database.
target_size
Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.
You'll want to specify a size. without the optional { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } portion.
If you are in Enterprise manager, right click on database -> all tasks -> Shrink database, then click the file button, select the file you wanna shrink and then select the shrink file to option and set it to what U want.
October 27, 2010 at 1:30 am
I know this topic is long-dead, but in case someone finds it while searching for LOB unused space issues, the following topics deal with (what looks like) the same issue and offer a work-around for freeing the unused space:
http://www.sqlservercentral.com/Forums/FindPost740006.aspx
http://www.sqlservercentral.com/Forums/FindPost1011086.aspx
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
October 27, 2010 at 2:02 am
ecarmon-249589 (8/1/2005)
How can we reclaim this space?
You can also reclaim space from LOG perspective . First backup the log and then Do the DBCC shrinkfile
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply