[스크랩] Can DBCC SHRINKFILE Cause Blocking in SQL Server?

https://www.brentozar.com/archive/2015/04/dbcc-shrinkfile-blocking-locks-in-sql-server/

Can DBCC SHRINKFILE Cause Blocking in SQL Server? - Brent Ozar Unlimited®

Last Updated January 11, 2018

Kendra Little

Locking, Blocking, and Isolation Levels, SQL Server

36Comments

 

 

It sure can.

The lock risks of shrinking data files in SQL Server aren’t very well documented. Many people have written about shrinking files being a bad regular practice— and that’s totally true. But sometimes you may need to run a one-time operation if you’ve been able to clear out or archive a lot of data. And you might wonder what kind of pains shrinking could cause you.

One pain it could cause you is blocking and lock waits. It’s easy to reproduce, here’s how.

A Recipe for LCK_M_X Lock Waits and Blocking

I was able to easily reproduce blocking in my restored copy of the StackOverflow database on SQL Server 2014 by doing a few things.

I rebuilt a non-clustered index on the Posts table with this command:

 

1 2	ALTER INDEX [kl_Posts_OwnerUserId_CreationDate] on Posts REBUILD WITH (ONLINE=ON); GO

This ate up some space, and gave shrink something to do!

I then started a data modification and left an open transaction running:

 

1 2	BEGIN TRAN DELETE FROM dbo.Posts where Id=16;

And then, in another session, I started to shrink the data file:

 

1 2	DBCC SHRINKFILE ('StackOverflow'); GO

Behold, Blocking!

Shrink starts up and slogs its way through things, and soon enough, lock waits appear. This view is from Adam Machanic‘s sp_WhoIsActive:

I’ll take LCK_M_X Waits for 15,000, Alex!

If I run that with @get_locks=1, I can see details on exactly what lock SHRINKFILE is after:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

<Database name="StackOverflow">   <Locks>     <Lock request_mode="S" request_status="GRANT" request_count="1" />   </Locks>   <Objects>     <Object name="(null)">       <Locks>         <Lock resource_type="FILE.FILE_SHRINK" request_mode="X" request_status="GRANT" request_count="1" />       </Locks>     </Object>     <Object name="Posts" schema_name="dbo">       <Locks>         <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />         <Lock resource_type="PAGE" page_type="*" index_name="kl_Posts_OwnerUserId_CreationDate" request_mode="X" request_status="GRANT" request_count="3" />         <Lock resource_type="PAGE" page_type="*" index_name="kl_Posts_OwnerUserId_CreationDate" request_mode="X" request_status="WAIT" request_count="1" />       </Locks>     </Object>   </Objects> </Database>

Yep, it’s waiting on a page lock in kl_Posts_OwnerUserId_CreationDate.

But How Many Locks did SHRINKFILE Really Need?

Maybe it needed just a few locks… right?

Well, I ran a trace against my session that did that shrinkfile command, and here’s the number of locks by database and type that it caught…

Lock mode: XTreme

And this was a FAST run, except for that blocking!

Shrinking files is painful

It can actually be worse than this — on a few runs, I was able to reproduce blocking with SCH_M locks that could cause even nastier blocking chains.

Shrinking is slow, tedious work. It eats CPU, steals your IO, and always takes longer than you want. And if it has work to do and you’ve got users or processors modifying that data, lock contention ensues.

Worst of all, at the end of shrinking a database, nobody will ever thank you. Be careful out there, and if you must run it make sure you don’t leave it unattended.

Brent says: whoa, I never even thought about that. Maybe we should design indexes to improve DBCC SHRINKDB speed.

Erik says: if you do this more than once, I will come find you.