DBCC SHRINKFILE EMPTYFILE problem

[Bill Darnold]

I am trying to remove a file from a filegroup. The shrinkfile runs for a
few hours, then gets a deadlock. Here is the error:

Msg 1205, Level 13, State 57, Line 1

Transaction (Process ID 55) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.



I have a few questions about this issue:



1. What is causing this deadlock and how can I avoid it?

2. Does shrinkfile roll back all the data moved out of the file during this
transaction? I get several million IO operations before the failure. Have
I made progress toward emptying the file?

3. Once the shrinkfile fails, does SQL Server still add data to this file,
or is it marked as unavailable for new data?

[Hilary Cotter]

1) its hard to say, run profiler and capture the deadlock chain to figure it
out.
2) No, my experience is that some of the data is migrated.
3) yes, but you can mitigate file growth into this data file by setting it
up not to autogrow.
--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Bill Darnold
I am trying to remove a file from a filegroup. The shrinkfile runs for a
Msg 1205, Level 13, State 57, Line 1
Transaction (Process ID 55) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
1. What is causing this deadlock and how can I avoid it?
2. Does shrinkfile roll back all the data moved out of the file during
this transaction? I get several million IO operations before the failure.
Have I made progress toward emptying the file?
3. Once the shrinkfile fails, does SQL Server still add data to this
file, or is it marked as unavailable for new data?

[Charles Wang[MSFT]]

Hi Billd101,
For your three questions, essentially I agree with Hilary.
I would like to provide some extra information here on your issue.

The deadlock errors were probably caused by the concurrent user activity in
the database. You can use SQL Profiler to monitor the database activities.
These errors did not occur any longer after you started to run DBCC
SHRINKFILE / DBCC SHRINKDATABASE in single user mode.

When you decided to shrink your database, I recommend that you configure
your server instance startup in single user mode.
You can refer to:
How to: Configure Server Startup Options (SQL Server Configuration Manager)
http://msdn2.microsoft.com/en-us/library/ms345416.aspx

If your SQL Server is 2000, you can configure it via Enterprise Manager:
Right click the server instance name, click Properties, in General tab,
click Startup Parameters..., add the parameter '-m' and restart the SQL
Server service.
You may refer to:
SQL Server 2000 Administrator's Pocket Consultant: Configuring and Tuning
Microsoft SQL Server
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c02ppcsq.mspx

Please feel free to let me know if you have any other questions or concerns.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Bill Darnold]

The file I am trying to remove is 100GB. The shrinkfile runs for up to 24
hours before failing. I can't take the database offline for that long.

I want to know if I am making progress each time I shrink or if I am
starting over every time I rerun.

Post by Charles Wang[MSFT]
Hi Billd101,
For your three questions, essentially I agree with Hilary.
I would like to provide some extra information here on your issue.
The deadlock errors were probably caused by the concurrent user activity in
the database. You can use SQL Profiler to monitor the database activities.
These errors did not occur any longer after you started to run DBCC
SHRINKFILE / DBCC SHRINKDATABASE in single user mode.
When you decided to shrink your database, I recommend that you configure
your server instance startup in single user mode.
How to: Configure Server Startup Options (SQL Server Configuration Manager)
http://msdn2.microsoft.com/en-us/library/ms345416.aspx
Right click the server instance name, click Properties, in General tab,
click Startup Parameters..., add the parameter '-m' and restart the SQL
Server service.
SQL Server 2000 Administrator's Pocket Consultant: Configuring and Tuning
Microsoft SQL Server
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c02ppcsq.mspx
Please feel free to let me know if you have any other questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
======================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

[Charles Wang[MSFT]]

Hi,
Please use DBCC SHOWFILESTATS to check if your database file still has some
used extents.
Generally there are two reasons if a file cannot be removed from a
filegroup after being used DBCC SHRINKFILE with EMPTYFILE:
1) The file that you want to remove and shrink with empty is the primary
file. It would fail as expected because primary file contains some
unmovable contents and could not be emptied.
2) Because the contents of the target file has to move to other files in
the same filegroup, if other files in the same filegroup does not have
enough space to take the contents of the target file, the emptyfile would
fail.

You may check the two conditions and if your situation did not fit any one
of them, I recommend that you try rerunning DBCC SHRINKFILE with emptyfile
operation and check if the column UsedPages in the result is 0.
Then try removing the file again.

If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.

Charles Wang
Microsoft Online Partner Support

[Charles Wang[MSFT]]

Hi Bild,

Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.

I also discussed this issue with other support professionals. To perform an
operation of DBCC SHRINKDATABASE with empty downright, you may need to drop
the indexes on main tables. After your removing the file, you can recreate
the indexes.

If you have any other questions or concerns, please feel free to let us
know.

Have a great day!


Charles Wang
Microsoft Online Community Support