I had a similar thing happen to me. After upgrading from SQL Server 7.0 to SQL Server 2000, my database lost nearly 700MB. So, without panic, I said “Good, now I have more space for my needs!” and forgot about it.
Now, my database is 9GB in size and I think there is a lot of free space in it, but I haven’t defragmented it because it is running 24×7 and there are no performance problems.
Serge Shakhov
Database Administrator
Magnitogorsk Iron & Steel Works
*****
I enjoyed reading “How 11GB of SQL Server Data Mysteriously Disappeared”. I believe I have the answer. This behavior is a side affect of using a heap (a table without a clustered index). It is not a bug, and the behavior is not different in SQL Server 2000. The 11 MB of space savings was due to what happened during the upgrade from SQL Server 7.0 to SQL Server 2000.
[Brad: All the tables in the database in question had clustered indexes, so this most likely was not the cause. But I have included this feedback because it is still very useful information. Thanks for taking the time to write and send it.]
A clustered index is an integral part of how SQL Server manages data internally. It is possible to create tables without a clustered index, such a table is called a “heap” and how SQL Server manages the data in heaps is different than how it handles it when there is a clustered index. Heaps are useful for some operations, like non-logged bulk inserts. But in most cases, the lack of a clustered index has some important downsides.
DBCC DBREINDEX, which among other things reclaims space freed up by deletions, does nothing to tables without a clustered index (heaps). It runs without error, but it takes no action. I noticed this in two ways. First, because it runs almost instantly on very large heaps without causing my hard disk to light up. Secondly, because the results returned from DBCC SHOWCONTIG are identical before and after issuing a DBCC DBREINDEX.
Chapter 6 of the book Inside SQL Server 7.0 contains an excellent description of how data is stored and organized inside of tables. It explains how data is shuffled to make space for new rows, or freed when rows are deleted. Basically, when rows are deleted, space is freed up inside of the table. This creates free space within the 8k pages that make up a database table, but it doesn’t result in fewer 8k pages. DBCC DBREINDEX reclaims the empty space within pages to free up pages. It makes a copy of the table packing all the data more efficiently (depending on FILLFACTOR) thus freeing up data pages and making the table smaller.
The size in MB of a table as reported by SQL Server is based on the number of pages used regardless of how much free space resides inside of those pages. The backup file contains a copy of each of the pages, and thus its size is dependent on the number of pages used.
DBCC DBREINDEX won’t reclaim the unused space in a heap, but you can do it yourself by doing what DBCC DBREINDEX does; making a copy of the table.
Below is a SQL Script to demonstrate some of these concepts. I ran it on SQL Server 2000 server which shows that the behavior of heaps hasn’t fundamentally changed between SQL Server 7.0 and SQL Server 2000.
/* create a test table
*/
create table heap_test (
i int identity(1,1) not null
,string varchar(200) not null default ”
)
/* create a proc to populate the table with dummy data 60 byte strings where chosen to fit about 100 rows on an 8k SQL Server data page.
*/
create proc p_populate_heap_test( @qty_rows int)
as
set nocount on
declare @n int
set @n=1
while( @n <= @qty_rows)
begin
insert into heap_test( string ) values ( replicate(‘.’, 60) )
set @n = @n + 1
end
return
/* add 1000 rows to our test table. This will result in 10 or 11 pages of data
*/
p_populate_heap_test 1000
/* get the object id of the table so we can pass it to showcontig
*/
select object_id( ‘heap_test’)
/* display the number of pages used and free space per page. Ignore scan density because of the small size of the table
*/
dbcc showcontig( 1232059475 (replace this number) )
/* delete half of the data from this table
*/
delete from heap_test where i %2 = 1
/* Notice that the number of pages used is the same as before. The number of free bytes per page has gone up
*/
dbcc showcontig( 1232059475 (replace this number) )
/* Try to reclaim the empty space with dbreindex NOTE if using SQL Server 7.0 pass in the object_id instead of the table name
*/
dbcc dbreindex( heap_test )
/* Notice that the number of pages used is unchanged
*/
dbcc showcontig( 1232059475 (replace this number) )
/* Example output from DBCC SHOWCONTIG
*/
DBCC SHOWCONTIG scanning ‘heap_test’ table…
Table: ‘heap_test’ (1232059475); index ID: 0, database ID: 9
TABLE level scan performed.
– Pages Scanned…………………………..: 10
– Extents Scanned…………………………: 9
– Extent Switches…………………………: 8
– Avg. Pages per Extent……………………: 1.1
– Scan Density [Best Count:Actual Count]…….: 22.22% [2:9]
– Extent Scan Fragmentation ……………….: 88.89%
– Avg. Bytes Free per Page…………………: 396.0
– Avg. Page Density (full)…………………: 95.11%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Thanks,
Amadeus
]]>