Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have several tables with amount of rows between 5M and 1.5G

Each table has its BLOB field, which size varies from 100 bytes to 30 MBytes and which is stored as 'large value types out of row' = ON

Tables are stored in different filegroups with 3-4 files each on different disk @ different LUNs @ very fast SAN

Every day these tables grow for 5-100 Gb in size and with 600k - 1.5M rows

After certain amount of time, which varies from 2 weeks to 6 months some of the rows are deleted or moved to archive DB, so - there is no any rows in worktables that older than 6 months.

Current configuration of server:

  • SQL server engine is 2008 R2 SP1 Enterprise @ 24 cores, @ 64Gb RAM
  • SQL Server runs with extra startup flags:

-T 3640; (Eliminates sending DONE_IN_PROC messages to client for each statement in stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way)

-T 1118;(Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent.)

-T 2301;(Enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of large data sets)

-T 1117;(Grows all data files at once, else it goes in turns.)

-E; (Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse applications that have a limited number of users running index or data scans)

-T 834; (Causes SQL Server to use Windows large-page allocations for the memory that is allocated for the buffer pool, http://msdn2.microsoft.com/en-us/library/aa366720.aspx, http://support.microsoft.com/kb/920093)

  • SQL Server uses Large Page Extensions
  • SQL Server utilizes fast file initialization option
  • AUTOSHRINK is OFF for all the databases

The problem is - that starting from some point of server's uptime (from couple of days to months) GHOST CLEANUP process refuses to work out forced cleanups and simply do its usual job - cleans up several pages in several seconds (which is seen thru Extended Events), which is not suitable, because it is not able to clean up all the deleted rows

The problem persists from the times of SQL Server 2005 RTM Enterprise

How I was tried to solve the issue:

  • Tried to force SCAN operations on clustered indexes of the tables
  • Tried to force SCAN operations, which involving all the contents of BLOB column on clustered indexes of the tables
  • system sp_clean_db_free_space & sp_clean_db_file_free_space
  • manually dbcc cleanpage(@dbid , @fileid, @page) for all the files and pages in DB
  • clustered index rebuilds and reorganizing
  • recreating database
  • DBCC FORCEGHOSTCLEANUP

  • When I run the query:

    select * 
    from sys.dm_db_index_physical_stats(db_id(), object_id('ProblemTable'), 1, 0, 'detailed')
    

    I see millions and tens of millions ghost records, but only for allocation unit type of LOB_DATA

The only things, that help:

  • stopping the server with SHUTDOWN command or restarting the whole host - it helps, after restart GHOST CLEANUP process runs some hours and actually cleans all the ghosted records
  • DBCC SHRINKFILE with EMPTYFILE option - moving all the data from one file to other or newly created files cleans up ghost records in this file only - the problem is that I really hate shrink operations. And this takes 3-4 days for ONE file

the question - is there exists any programmatic (preferable) or maintenance way to force GHOST CLEANUP without server downtime at all, because server downtime costs too much, even unacceptable - its from thousands to tens of thousands $ per hour

Problems were noticed alike mine are here:

And just the same is here:

share|improve this question

migrated from stackoverflow.com Jan 5 '12 at 14:33

This question came from our site for professional and enthusiast programmers.

5  
Sounds like something you should raise with Microsoft CSS/PSS –  Martin Smith Jan 5 '12 at 14:43
3  
Have you tried running DBCC FORCEGHOSTCLEANUP('DBName') on your database? –  MartinC Jan 5 '12 at 15:35
    
Yep, forgot to notice that, i'll update the question –  Oleg Dok Jan 5 '12 at 15:52
    
Did you get this resolved with CSS? –  Mark Storey-Smith Jul 7 '13 at 20:13
1  
@MarkStorey-Smith Oh, yes - the MS is recognized it as a bug. It is exists up to sql 2008 R2. support.microsoft.com/kb/2622823 –  Oleg Dok Jul 10 '13 at 8:25

2 Answers 2

up vote 6 down vote accepted

Finally, MS has recognized the issue as a bug: http://support.microsoft.com/kb/2622823

Briefly: It is fixed in

  • Sql Server 2008 SP3 CU4
  • Sql Server 2008 R2 CU10
  • Sql Server 2008 R2 SP1 CU4

In Sql Server 2012 SP1 I'm not experiencing the issue for more than year of runtime.

share|improve this answer

This is the kind of question that should go to CSS so that they can work the issue with you. You probably have software assurance and a support contract. If you don't a few hundred bucks shouldn't be that big a deal if restarting the instance costs you thousands of dollars per hour.

Have you tried allowing the database to close and then be brought online? This will cause crash recovery to run and may kick the ghost cleanup.

Are you writing to the table frequently? By frequently I mean all the time?

As to MSKB 932115 are you seeing the ghost records being left only in all files, or is it cleaning up the first file in the file group?

Why the use of -T1117 and instant file init?

share|improve this answer
    
1. I definitely will go to MS support. 2. If I close the DB it raises up about 10-30 min rolling trans back and forward, which is unacceptable. 3. The GC IS running, but it isn't processing deleted off-row LOB entries. 4. Writing to tables performing constantly depending on time of day from 20 to 600 writes per second and all the time. 5. The first file of DB isn't in use - it has not big tables and used only as system storage, so - there are simply no any ghost records. –  Oleg Dok Jan 6 '12 at 8:20
    
with -T1117 I just want to spread all the load between several files, instead when there is only one file left from filegroup, where still exists free space - it begins to slow down on PFS's LATCHes, instant file init minimizes the time of filegrowth, because an increment is set to 10-50 Gb per turn. I cannot simply set the files as large as I can, because it is completely unpredictable - which files will get their data today and in which volume. It is simplier to ask SAN admins to add more space, than predict WHom I should add the space to. –  Oleg Dok Jan 6 '12 at 8:25
    
What files in the file group which contains the full text data is the GC successfully doing work against? (if any) –  mrdenny Jan 6 '12 at 9:42
    
all - before some point of time and none - after it. It not connected to database context, it happens to all the DBs @ server –  Oleg Dok Jan 6 '12 at 9:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.