Please bear with me as I'm not a DBA nor am I a SQL server expert. We added an index to a table and noticed after about a day or so, the storage for the index grew to around 200 MB.
The reason for this, we surmised, is due to the high number of ghost records.
However when we do looked into it, we got the following results for the tables in the db:
| TableName | record_count | ghost_record_count | version_ghost_record_count | |:--------- |:------------:|:------------------:|:--------------------------:| | Table1 | 19,936,533,500 | 5000 | 473,444,900| | Table2 | 902,990,900 | 100 | 9,143,000| | Table3 | 5,502.563,178 | 800 | 120,117,300| | Table4 | 1,548,468,000 | 500 | 29,863,400| Seeing that the version ghost records is so big, we figured restarting the application (not the database) server might clear the records. This did not seem to be the case however. To be honest I am stumped as to what could be the reason for the persistence of the version ghost records. I'd appreciate any insight into how to troubleshoot/manage this. Or even some additional knowledge or past experience about this topic since I've found very little on the interwebs. Thank you.

I have been trying to troubleshoot an issue I have detected on a UAT SQL Server, there is some resource usage even if the server is not being used... On Activity monitor I have an average of 5% CPU usage (Real SQL Server Usage double checked with DMVs) and also a 20mb/s disk IO, this has been the same for 2 days now... I have found that the only process that is doing IO Operation is the ghost cleanup... After reviewing what data file is doing the IO Operation I found that I have 1500Reads/sec (NO WRITE OPERATIONS) on one Database where some users were doing load tests days ago... What can I do with this, will this process finish? Is there anything I can do?

Just started as a dba with a company running SQL Server 2012 High Availability and SharePoint. Short description: Database with no user activity still has growing log file. Long description: They have many, many SharePoint dbs where the logfile size dwarfs the datafile size. They've just been throwing extra disk space at the issue. They were in Full Recovery (mandatory for HA) but weren't taking Log backups. Simply by taking a log backup and a DBCC Shrinkfile to a manageable size I've started to bring things under control. However, on my trial database the logfile is growing rapidly again. As far as I can ascertain, no user activity in the database has taken place (insert/update/deletes) since I did the log backup. Running select [Current LSN], Operation, Context, [Transaction ID] , SPID, [Begin Time], [Transaction Name] from fn_dblog(null,null) shows hundreds of rows of GhostCleanup Task (looking for deletes) followed by a Begin Transaction and a Commit Transaction: Can anyone explain what's causing this? I can obviously set up Maintenance Plans to do Log backups much more frequently to keep the size in check, but I'd like to understand why there are all these transactions occurring in a 'static' database. I've read about setting trace flag 661 to disable ghost cleanup, but don't want to do a server-wide change at this proof of concept stage. I'm not sure if this is a HA issue, SharePoint issue, neither or both! I've read lots of posts about SharePoint causing runaway logs but no real explanation. It's the primary AOAG server. There are two synchronous secondaries and an asynchronous DR secondary. dbcc opentran(EXT_Metadata_DB) reports: >No active open transactions. Log file is still growing despite no user activity. There were 5 Autogrowth events overnight. No transactional replication on this db, though HA obviously needs to write the log to the secondary servers. No blocked processes visible. select d.name, d.log_reuse_wait_desc from sys.databases d where d.database_id = 15 gives:

EXT_Metadata_DB    LOG_BACKUP

This seems to imply that all transactions are in the current VLF which can't be cleared. Puzzling thing is, I haven't done any log backups since I did the Shrinkfile. I'm using a tiny database for this investigation. Data file is only 14MB and I shrank the log file down to 4MB (13% full). A day or so later, data file remains at 14MB, but log file has now grown to 69MB and is 92% full. The particular SQL Server instance I'm looking at has 675GB of log files. select name, size from sys.database_files where type = 1 gives:

EXT_Metadata_DB_log	8872

before a checkpoint. Ran checkpoint but size remains at 8872. Server recovery interval is the default 0. If I run: SELECT context AS Context, [Transaction Name] as Description, COUNT(context) AS Count from fn_dblog(null,null) GROUP BY context, [Transaction Name] order by count desc I get 19 rows:

LCX_NULL	        NULL	                    12681
LCX_NULL	        GhostCleanupTask	        12326
LCX_BOOT_PAGE	    NULL	                    12185
LCX_CLUSTERED	    NULL	                    451
LCX_NULL	        UpdateQPStats	            81
LCX_FILE_HEADER	    NULL	                    35
LCX_NULL	        SetFileSize	                33
LCX_PFS	NULL	                                12
LCX_TEXT_MIX	    NULL	                    10
LCX_BOOT_PAGE_CKPT	NULL	                    10
LCX_DIFF_MAP	    NULL	                    7
LCX_NULL	        DeallocUnlinkedReorgPage	6

No user activity, but 37847 transaction entries in 24 hours. Is this to be expected? I've not modified any data. It's a static database of metadata.

A database that is part of a SQL Server Always Availability Group with both *synchronous* and *asynchronous* readable secondaries that are being used to scale out read-only workloads is experiencing a build up of version ghost records of allocation type LOB_DATA. This occurs on a table that has a high level of INSERT operations applied to it. REBUILDing the clustered index of this table removes any version ghost records of allocation type IN_ROW_DATA but not of allocation type LOB_DATA. Performing a manual fail-over removes the version ghost rows, but this is undesirable. While I am investigating the root cause of the build up of version ghost rows, is there any other way of removing version ghost records of allocation type LOB_DATA?

Using SQL Server 2016, Always-On Running a simple SELECT on a table within a secondary Availability Group. select distinct some_column from some_table oo (nolock) inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column SSMS eventually sends back this error: > Msg 3948, Level 16, State 2, Line 14061 The transaction was terminated > because of the availability replica config/state change or because > ghost records are being deleted on the primary and the secondary > availability replica that might be needed by queries running under > snapshot isolation. Retry the transaction. The same SELECT with (NOLOCK) works fine on the primary AG. With or without the NOLOCK it fails most of the time 9/10 and sometimes executes correctly but that's rare. No changes were made to the AG at all. There are. no changes in the *AlwaysOn_Health* extended event session. The low_water_mark_for_ghosts in sys.dm_hadr_database_replica_states is NULL for some and for others there is a value. For those with a value, this is also the primary. Using the script provided by Nic:

;
WITH    PrimaryStats
          AS ( SELECT   DB_NAME(database_id) AS DatabaseName ,
                        low_water_mark_for_ghosts ,
                        ar.replica_server_name ,
                        ar.availability_mode_desc
               FROM     sys.dm_hadr_database_replica_states hdrs
                        JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
               WHERE    ar.replica_server_name = @@SERVERNAME
             )
    SELECT  DB_NAME(database_id) AS DatabaseName ,
            hdrs.low_water_mark_for_ghosts AS LowWaterMarkSecondaryReplica,
            ps.low_water_mark_for_ghosts AS LowWaterMarkLocalReplica,
			ps.low_water_mark_for_ghosts - hdrs.low_water_mark_for_ghosts AS GhostWatermarkDiff,
            ar.replica_server_name AS ReplicaNode,
			DATEDIFF(SECOND, last_redone_time, GETDATE()) AS RedoDiffSec,
			last_redone_time
    FROM    sys.dm_hadr_database_replica_states hdrs
            JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
            JOIN PrimaryStats ps ON ps.DatabaseName = DB_NAME(database_id)
    ORDER BY 
            DatabaseName ASC,'NODE ' + right(ar.replica_server_name ,1);

The output is: When the LowWaterMarkSecondaryReplica is NULL, its because that's the instance I'm currently executing this query from.

Has anyone encountered this? We are using SQL Server 2012 Enterprise Edition with SP1. During a time of low/no activity, I noticed the Ghost Cleanup process is utilizing CPU at a very high rate, from 75% to 99%, these aren't bursts but sustained usage. I don't see the ghost cleanup process blocking itself, it just seems to take a lot of CPU while it runs, and never seems to complete. How can I tell if it's actually doing something? Is this a bug with SQL Server 2012? I have seen this in SQL 2008 and there was a fix for it but I haven't seen anything for 2012.

We're running SQL Enterprise 2016 (13.0.4411). I have a database that was using service broker to handle some workflow, long story short, it was designed incorrectly and none of the conversations were closed correctly. The sys.sysdercv system table blew up to about 270,000,000 rows and 140GB in size. I created an agent job that grabbed the top 50,000 conversation handles from sys.conversation_endpoints in DI or DO state, and closed them with cleanup. This agent job ran for a couple of weeks, and finally got the majority of the table cleaned out. However, now it appears that there may be a corrupted page in sys.sysdercv. The ghost cleanup process (spid 596 in this case) has been chugging along for about 37 hours now. Checking it with sp_whoisactive shows that it's trying to get a lock on sys.sysdercv. I'm also seeing the following error in the SQL log: A time-out occurred while waiting for buffer latch -- type 3, bp 000000FA1CA98B80, page 1:21335899, stat 0x10b, database id: 13, allocation unit Id: 281474981494784/4784128, task 0x000000AF954AACA8 : 0, waittime 300 seconds, flags 0x100000001a, owning task 0x000001B98D8D04E8. Not continuing to wait. The allocation unit ID corresponds to the clustered index on sys.sysdercv. The owning task address corresponds to the ghost cleanup process. I tried to run the following: DBCC PAGE (13, 1, 21335899, 0) WITH TABLERESULTS; This just hangs and doesn't return anything (sp_whoisactive shows that it's blocked by the ghost cleanup process). I also tried to run this: DBCC CHECKTABLE ('sys.sysdercv') WITH TABLOCK, ALL_ERRORMSGS; Same deal, it hangs, then eventually times out with a latch error complaining about the same page. At this point, I'm unsure of what to do. There is no data I currently care about in sys.sysdercv or sys.sysdesend (no messages queued at the moment), so I was hoping I could just truncate these tables, which would (ideally) get rid of the offending page. I can't truncate system tables though. I can't kill the ghost process in order to do DBCC CHECKTABLE. I thought about stopping the SQL service, enabling trace flag 661 on startup to prevent the ghost cleanup process from running, and then doing the table check. However, I'm also seeing this error message: One or more recovery units belonging to database 'xxx' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure. So I don't know if the database will even come back online if I restart the SQL service. My differential backups have been failing due to this issue, and DBCC OPENTRAN shows that the ghost cleanup is preventing the log space from being reused. So...all that said, is there anything at all I can do to fix this problem? Is there some magical trace flag that would allow me to truncate system tables? Can I safely restart SQL without the ghost cleanup process in order to run DBCC CHECKTABLE (as in, will my database come back online since it hasn't been able to checkpoint in the past day and a half)? Any help would be greatly appreciated.

I'm running a high transactional database (~175k transactions / Minute on average, almost 9M records per hour added and removed) Up till recently this hasn't been too much of an issue as we've been at ~7.5M records added and removed but with the latest influxes of data the ghost cleanup doesn't appear to be able to keep up with cleaning up the unused space on tables / indexes. A few days ago we reached 53 GB of 'Unused Space' across 16 tables (mostly 2 of them) so as a result started looking into the ghost cleanup process to find it runs once every 5 seconds and runs over 10 pages. My current solution is that early morning I am running three threads of the following command: DECLARE @2hours datetime = dateadd(hour,2,getutcdate()) WHILE getutcdate() < @2hours BEGIN DBCC FORCEGHOSTCLEANUP ('DBNAME') WITH NO_INFOMSGS END to catch up with the backlog from the night before (when most of our deletes take place) I'm wondering if there's any way to change the default settings from 5 seconds and 10 pages to say every second or run over 20 pages, is there any way of doing that or should I continue just spinning up multiple cleanup procs to clear out the data, or if there's any other actions that can assist with this Re-indexing runs on most effected indexs atleast once a week (most are every other day) SQL Server 2012 Enterprise SP3_CU8 (upgrading to CU9 tomorrow) on AlwaysOn High Availabilty Cluster also with replication (distribution on a separate server)

I found lots of ShrinkD, ghostcleanuptask, and BTree Split/Shrink in my SQL Server logs. At the time of these logged events, the performance counters showed that lazy writes were very slow, the log flush rate was high, and write/sec was close to 30000. Disk IO was also very high. I'm sure that the server traffic was normal and there was no abnormal data file growth at the time, but the log file was twice as large as usual. I used the dump_log() function to get statistics on logs: My configuration: * Windows Server 2008 sp2 * SQL Server 2014 * Database target_recovery_time = 60 seconds * Database auto shrink and index auto shrink = false * Log backup hourly * Physical disk: pcie ssd 3.0T with 70% used I recently: * Cleaned up useless indexes * Reorganized fragmented indexes * Increased the automatic archiving data operation about 500w daily backup and delete Archiving was at 4:00-5:00 very day, I suspect that the ghost cleanup is related to my physical backup deletion and archiving. The logfile was double than the same time yesterday. **My Question:** * What is the "shrinkd" in SQL Server logs? * Is it shrink database? I can't find any information about it on the Internet.If the AUTO_SHRINK set off and there is not skrink job,how did it be triggered? Thanks.

Had a problem in my SQL Server Cluster for 2 weeks, I had the GHOST_CLEANUP process running 24/7 never stopping even if the users were doing nothing. I was checking the "Free Space" parameter in the Database and always was at the same value (Around 1GB). I did a backup of the database and restored in the same server with the same name to see if running sp_clean_db_free_space would free up the space that GHOST_CLEANUP was not able to. After 2 hours...TADA!...the space was cleaned and I had 80GB free from 100GB Database. So I decided to run sp_clean_db_free_space stored procedure on the original database to see what happens. After almost 30m it finished not freeing any space. The only different thing was that the original database was synched under an Availability Group. I disconnected the database from the Availability Group and now I can see that the GHOST_CLEANUP task is slowly freeing space. I will assume that the problem was the Availability Group, but does anyone know **WHY**!? I can confirm no blocking on primary by read operations on secondary because the secondary node is not even used for read only operations as the application doesn't allow to configure this. There are no users connected to the secondary node. Today, after checking I found that each 5 seconds SQL Server jumped from 0-1% CPU to 10-15% CPU then dropped again to 0-1% CPU after 1 second. No I/0 usage, only CPU. Decided to disconnect all my Availability Replicas, after 1 minute, SQL Server dropped to a stable 0-1% CPU, connected the replicas again and remains at 0-1%. I have a problem with this GHOST_CLEANUP and I don't know how to solve it. I am using Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64).

I am trying to replace data in table referenced by FKs in other tables on SQL Server. There are more than 1M rows in both referenced and referencing table and I want to make the replacement atomic. Obvious solution would be: BEGIN TRAN ALTER TABLE TabReferencing NOCHECK CONSTRAINT MyForeignKey DELETE FROM TabReferenced INSERT INTO TabReferenced (...) ALTER TABLE TabReferencing CHECK CONSTRAINT MyForeignKey COMMIT If those statements (executed one-by-one or in batch, over network on on local SQL Server) take longer than a few seconds to complete, things go nasty. GHOST CLEANUP background thread on server starts up few moments after DELETE and starts using 100% CPU and quite a lot of IO, degrading overall performance to unacceptable level (500 inserts/min compared to 50000 inserts/min without cleanup). So far I have tried following workarounds: - Disabling FK before beginning transaction and re-enabling it after COMMIT works fine (quick enough), but if process (or network) fails during inserts, I end up with table with no FKs. - Deleting data from both referencing and referenced table and NOT touching FK works fine, but I don't want to repopulate referencing table due to it's size. - Dropping and re-creating FK instead of dis/enabling in transaction makes no difference. - Dropping and re-creating FK and using TRUNCATE instead of DELETE works fine - at least until I need DELETE with a WHERE clause. - Disabling GHOST CLEANUP thread (DBCC TRACEOFF(661, -1)) makes original batch complete at full speed, but it's not the real solution for production environment. Why does disabling FK and deleting table's content trigger GHOST CLEANUP and just deleting table or truncating table does not? Why does cleanup with disabled/dropped FK takes way longer than same cleanup with FK enabled? There are no triggers in DB. All FKs have empty ON UPDATE/DELETE action.

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: * http://support.microsoft.com/kb/932115 * http://www.sqlservercentral.com/Forums/Topic496244-149-1.aspx And just the same is here: * http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c023e51c-92ad-471b-89a6-8c93732ee6cb/