SQLServerWiki

“The Only Thing That Is Constant Is Change”

SQL 2005/Issue reclaiming LOB Pages after deletion.

Posted by database-wiki on October 23, 2011

Problem Description: =======================================================================

We noticed that the size of table doesn’t reduce after deleting BLOB records. This was a 6TB database and we were running on a 32 bit environment. So the LOB pages could not be reclaimed and DBCC CLEANTABLE failed with Error 1204.

DBCC CLEANTABLE (‘mxit_store’, ‘filedata’)

Msg 1204, Level 19, State 4, Line 1

The instance of the SQL Server Database Engine cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active users.
Ask the database administrator to check the lock and memory configuration for
this instance, or to check for long-running transactions.

Cause:
=======================================================================
For the first one, this behaviour is by design and there are three parts of our functionality that cause this behaviour.

. Upon rolling back an insertion of LOB data, we do not deallocate the LOB pages. Instead, the LOB pages remain allocated, and they are potentially used the next time LOB data is inserted.

. When inserting a LOB value, if the value is larger than one page, we break it up into page-sized fragments, with the last fragment being the remainder (i.e. possibly smaller than a page).

. When inserting a LOB value (or fragment), we typically search through the existing LOB pages to find free space for it. However, we have a performance optimization. If the value or fragment is exactly the size of one page, we do not search through the existing LOB pages. Instead, we simply allocate a new page for it. The reason for this optimization is that typically the free space on the existing LOB pages are all less than one page in size. This is because the most common way to get free space on a LOB page is when we insert a LOB value or fragment less than one page in size, leaving the remaining space on that page as free. Although we can get an entire free LOB page from rolling back a LOB insertion, this is more uncommon. Thus, rather than searching through the existing LOB pages and most likely not finding an entire free page, we just allocate a new page.

Please note that this hasn’t changed yet but the design is under review.

ANALYSIS
==================================================================
So, when a user rolls back a LOB insertion, we leave all the LOB pages allocated. The next time they try to insert LOB data, if the LOB data is greater than a page, we allocate new pages for it, only reusing the existing pages for the remainder. If this is also rolled back, we have a net increase in empty LOB pages.

The problem became worse here due to VAS limitation in a 32 bit environment and thus we noticed that DBCC CLEANTABLE failed with Error 1204.

The explanation for this error is due to the fact that SQL Server cannot obtain a lock resource. This can be caused by either of the following reasons:

. SQL Server cannot allocate more memory from the operating system, either because other processes are using it, or because the server is operating with the max server memory option configured.

. The lock manager will not use more than 60 % of the memory available to SQL Server.

To try and workaround with this issue we suggested adding a /3GB switch to boot.ini to increase the VAS by 1 gigs. Error 1204 is raised when 60% of BPOOL is used for locks. For default 2GB (1.6 visible, this will be 960MB so we are adding another 600 Mb with adding of /3Gb).

After enabling the /3GB switch we ran the following which helped in getting the space reclaimed.

USE MXIT_STORE
go
DBCC CLEANTABLE (‘mxit_store’, ‘filedata’,1000)
Go

ADDITIONAL SUGGESSTION

We also discussed that a 6TB database on an x86 platform is not a recommended configuration IMO, and for databases of this size, we’ll see scale problems. But you have plans to move to 64 bit environment where the 1204 would not come up when you try running DBCC CLEANTABLE as the VAS is almost unlimited there.

IMPORTANT Information about DBCC CLEANTABLE:-

DBCC CLEANTABLE reclaims space after a variable-length column is dropped. A variable-length column can be one of the following data types: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. The command does not reclaim space after a fixed-length column is dropped.

If the dropped columns were stored in-row, DBCC CLEANTABLE reclaims space from the IN_ROW_DATA allocation unit of the table. If the columns were stored off-row, space is reclaimed from either the ROW_OVERFLOW_DATA or the LOB_DATA allocation unit depending on the data type of the dropped column. If reclaiming space from a ROW_OVERFLOW_DATA or LOB_DATA page results in an empty page, DBCC CLEANTABLE removes the page.

DBCC CLEANTABLE runs as one or more transactions. If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows.

Reference:

=======

http://indiandotnet.wordpress.com/2011/04/27/space-issue-of-sql-server-database-how-to-claim-free-space-of-sql-server/

Required Queries:

============

1. Zip the SQL Server ERRORLOG folder and You can get the location by running the query select serverproperty

(‘ErrorLogFileName’)   2. Output for => select * from master.sys.databases

3. Run the below query to get the ghost record count. change the DATABASE_NAME in the below query.   SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N’DATABASE_NAME’), NULL , NULL, NULL , ‘DETAILED’)

4. Output for => DBCC SQLPERF(logspace)

5. Output for => DBCC LOGINFO

6. Output for => SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = ‘DATABASE_NAME’

7. Output for =>

DBCC TRACEON(2509, -1)   DBCC TRACESTATUS

dbcc checkdb (‘DATABASE_NAME’)

8. Output for => SELECT * FROM sys.dm_db_partition_stats

9. Output for => select * from sys.dm_db_file_space_usage

10. Output of    SELECT sum((total_pages)) pages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE type_desc=’ROW_OVERFLOW_DATA’ GO   11. Output of    SELECT sum((total_pages)) pages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE type_desc=’IN_ROW’ GO

12. Output of   SELECT object_name(object_id) AS name, partition_id,index_id,partition_number AS pnum, rows, allocation_unit_id AS au_id, type_desc as page_type_desc, total_pages AS pages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id order by name

15. Output for => sp_helpdb ‘DATABASE_NAME’

16. Output for =>

USE DATABASE_NAME

SELECT     a.FILEID,     CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,     CONVERT(decimal(12,2),ROUND(fileproperty(a.name,’SpaceUsed’)/128.000,2)) as [SPACEUSEDINMB],     CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) as [FREESPACEINMB],     a.name as [DATABASENAME],     a.FILENAME as [FILENAME] FROM     dbo.sysfiles a

17. This should be run on both the databases.

CREATE PROCEDURE getAllTablesSize

AS

BEGIN

DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;

CREATE TABLE

#temp (

[name] varchar(250),

[rows] varchar(50),

[reserved] varchar(50),

[data] varchar(50),

[index_size] varchar(50),

[unused] varchar(50)

);

INSERT #temp EXEC (‘sp_msforeachtable ”sp_spaceused ””?”””’);

UPDATE

#temp

SET

[rows] = LTRIM(RTRIM(REPLACE(t.rows,’KB’,”))),

[reserved] = LTRIM(RTRIM(REPLACE(t.reserved,’KB’,”))),

[data] = LTRIM(RTRIM(REPLACE(t.data,’KB’,”))),

[index_size] = LTRIM(RTRIM(REPLACE(t.index_size,’KB’,”))),

[unused] = LTRIM(RTRIM(REPLACE(t.unused,’KB’,”)))

FROM #temp AS t

SELECT

SUM(CAST([reserved] as decimal))/1024 AS ‘Total reserved MB’,

SUM(CAST([data] as decimal))/1024 AS ‘Total data MB’,

SUM(CAST([index_size] as decimal))/1024 AS ‘Total index_size MB’,

SUM(CAST([unused] as decimal))/1024 AS ‘Total unused MB’

FROM

#temp

SELECT

[name] ,

CAST([rows] as INT)’rows’ ,CAST([reserved] as INT)/1024 ‘reserved MB’,

CAST([data] as INT)/1024 ‘data MB’ ,

CAST([index_size]/1024 as INT)’index_size MB’,

CAST([unused] as INT)/1024 ‘unused MB’

FROM

#temp

ORDER BY

CAST(reserved as INT) DESC

DROP  TABLE #temp;

END

GO

EXECUTE getAllTablesSize

18. Largest table.

USE ‘DATABASE_NAME’ GO SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 ORDER BY st.row_count DESC GO

One Response to “SQL 2005/Issue reclaiming LOB Pages after deletion.”

  1. JJ Usher said

    Excellent post. I am facing many of these issues
    as well..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: