Release unused spaced on tables in SQL2005

Release unused spaced on tables in SQL2005

• Rania_TOPIT

  Not really. It basically is a matter of time for the system to clean up after itself. There was not a magic keyword (other than using TRUNCATE, which is not typically available in an active OLTP system)

  
  
  

• NPrinsloo

  nothing
  

• cliffy_1.rm

  Hi

  I didn't found resolution for problems like this in my databse, too.
  But I have workaround.

  You must script yours table with all dependent objects (pk,fk,triggers, indexes, etc)

  Run script something like this:

  SELECT * INTO tabela_tmp FROM tabela

  delete old tabela
  sp_rename 'tabela_tmp', 'tabela'

  Run script to update dependet objects (pk,fk,triggers, indexes, etc)

  After this you may have clean table without unused space. :)

  PS
  I try with CleanTable or something like that but I'ts not working :/

  
  
  

• jkelly295

  The problem isn't that i can't shrink the logfile. I need to release empty space from individual tables that for some reason still hold at lot of space even though the tables has zero rows!!
  

• Haidar

  Just to let you know I am still trying to get an answer about this one for you. I have some more testing as well to see if I can get a good answer as to whether this really matters or not :)
  
  

• coderrich

  Not 100% sure if this is the exact cause of your values, but from the sp_spaceused entry in books online:

  "When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available. For more information about deferred allocations, see Dropping and Rebuilding Large Objects."

  Looking at the Dropping and Rebuilding Large Objects topic implies that the space will be released eventually.

  Are you having problems with this, or did you just check and it immediately didn't vanish I got much the same behavior when I did:

  create table testSizing
  (
  testSizingId int identity,
  spaceUser char(8000)
  )
  go
  set nocount on
  go
  insert into testSizing(spaceUser)
  select replicate('a',8000)
  go 10000
  exec sp_spaceused 'testSizing','true'
  go
  delete from testSizing
  go
  exec sp_spaceused 'testSizing','true'
  Go
  

  And the data stayed allocated even through a restart of SQL Server (on my laptop.) If you think this is a real problem (and no one else pipes up :) I will see what I can find out.

  
  
  

• John2006

  What happens when you run DBCC UPDATEUSAGE

  Denis the SQL Menace

  http://sqlservercode.blogspot.com/

  
  

• Tim862000

  Have you found a resolution to this problem
  

• NeonLight5325

  Maybe you should truncate the log file.

  You can run:

  backup log <database name> with truncate_only

  After you run this command you can try DBCC shrinkfile.

  
  
• Release unused spaced on tables in SQL2005