Geek Answers Handbook

Recover unused space in SQL Server 2008

I have a table with over 300,000 records about 1.5 GB in size

In this table, I have three varchar(5000) fields varchar(5000) , the rest are small fields.

When update , set these three fields to '' .

After shrinking (databases and files) the database uses almost the same space as before ...

 DBCC SHRINKDATABASE(N'DataBase' ) DBCC SHRINKFILE (N'DataBase' , 1757) DBCC SHRINKFILE (N'DataBase_log' , 344)

Any ideas on how to recover this disk space?

+6
sql-server-2008 administration
source share
3 answers

Essentially, you need to “move” the contents of the table from one place on your hard drive to another. With such a move, SQL will effectively “repack” the contents of the pages. Just replacing 5,000 bytes of data with 3 (or 0 and an upside down empty bit string) will not force SQL to revise or rewrite the contents of the table pages.

If the table has a clustered index, simply re-indexing it (ALTER INDEX ... REBUILD ...) will do the trick.

If the table does not have a clustered index, you can either create it, or delete it, or SELECT ... INTO ... a new table, delete the old table and rename the new one to the original name.

+3
source share

Just because you set the column to nil does not mean that the database will reload the table. The updated record will still be placed on the same page on which it will correspond earlier (the amount of free space on the page will increase).

Also, you know, doesn’t that varchar (5000) mean that it takes 5000 octets? This variable length is a two-octet prefix containing the length of the field data, followed by the data. Setting the varchar (5000) column in the foobar row will require 8 octets of space (2 + 6).

Recover your indexes, including the clustering index.

If you do not have a clustering index, add it. This will force a table reorganization. Now drop the clustering index.

Now that you are shrinking the data file, you need to recover some disk space.

+2
source share

I just needed to set these fields to null, issue compression, and then set them to '

and db - from 1.5 GB to 115 MB

quite strange ...

-

in fact, setting these fields to NULL means that the entire table is recreated - the trick

0
source share

More articles:


All Articles