locked
truncate table RRS feed

  • Question

  • Hi:

    I have truncated my table that was about 150GB, but the space is not yet released.

    The database still shows the original size.

    I had done the rebuild/reorganize index updated statistics. Some one told me that it would automatically get released during Ghost ceanup, but it has been more than 3 days now I have truncated the table and still the space is not released, the DB still shows the same size.

    Can some one suggest me on this. also can this cause a performance hicup.

    • Moved by Kalman Toth Tuesday, June 17, 2014 11:32 AM Not database design
    Thursday, June 12, 2014 9:56 AM

Answers

  • hi,

    >>Some one told me that it would automatically get released during Ghost ceanup

    It automatically wont get released you need to shrink the data file. Use DBCC SHRINKFILE command or use GUI see below link

    http://technet.microsoft.com/en-us/library/ms190757(v=sql.105).aspx

    select the Release unused space check box. when shrinking using GUI.

    If above does not work Shrink in small chunks

    read below article specially remark section and troubleshooting

    http://msdn.microsoft.com/en-gb/library/ms189493.aspx

    PS: Keep in mind shrinking causes fragmentation.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Olaf HelperMVP Thursday, June 12, 2014 10:09 AM
    • Marked as answer by Sofiya Li Tuesday, July 15, 2014 2:44 AM
    Thursday, June 12, 2014 10:05 AM
  • I had done the shrinking as well with Release unused space check box. when shrinking using GUI, checked

    Did you read  'Troubleshooting' section in link http://msdn.microsoft.com/en-gb/library/ms189493.aspx or you are just blindly posting there is subsection file does not shrink refer to that. Are yu shrinking in chunks whats ouptut of below

    SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Sofiya Li Friday, June 13, 2014 6:57 AM
    • Marked as answer by Sofiya Li Tuesday, July 15, 2014 2:44 AM
    Thursday, June 12, 2014 10:54 AM

All replies

  • hi,

    >>Some one told me that it would automatically get released during Ghost ceanup

    It automatically wont get released you need to shrink the data file. Use DBCC SHRINKFILE command or use GUI see below link

    http://technet.microsoft.com/en-us/library/ms190757(v=sql.105).aspx

    select the Release unused space check box. when shrinking using GUI.

    If above does not work Shrink in small chunks

    read below article specially remark section and troubleshooting

    http://msdn.microsoft.com/en-gb/library/ms189493.aspx

    PS: Keep in mind shrinking causes fragmentation.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Olaf HelperMVP Thursday, June 12, 2014 10:09 AM
    • Marked as answer by Sofiya Li Tuesday, July 15, 2014 2:44 AM
    Thursday, June 12, 2014 10:05 AM
  • I had done the shrinking as well with Release unused space check box. when shrinking using GUI, checked
    Thursday, June 12, 2014 10:21 AM
  • I had done the shrinking as well with Release unused space check box. when shrinking using GUI, checked

    Did you read  'Troubleshooting' section in link http://msdn.microsoft.com/en-gb/library/ms189493.aspx or you are just blindly posting there is subsection file does not shrink refer to that. Are yu shrinking in chunks whats ouptut of below

    SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Proposed as answer by Sofiya Li Friday, June 13, 2014 6:57 AM
    • Marked as answer by Sofiya Li Tuesday, July 15, 2014 2:44 AM
    Thursday, June 12, 2014 10:54 AM