2

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:

monitor

statistics from logs

shrinkd

BTree Split/Shrink

GhostCleanupTask

wirte on zabbix

begin

info

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.
7

This question was also asked in a comment on Paul Randal's blog Inside the Storage Engine: Ghost cleanup in depth

Paul replied:

Looks like

a) you’ve got auto-shrink enabled for the database, or there’s an Agent job doing shrink
b) you’ve had a lot of deletes, and the shrink ‘touched’ a lot of the pages with deleted records on so they got queued up for the ghost cleanup task to take care of.

You need to fix a).

Feedback from the question author:

When it happened again, I set dbcc traceon(661,-1) and the server resumed immediately. Why would there be so many ghostcleanup tasks suddenly without any shrink job? The answer: if the number of pages with ghosted rows is above the specified (hard-coded) limit, the task runs non-stop till the count comes down below the threshold value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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