Unable to Drop Data-File – Error: Time-out occurred while waiting for buffer latch type 3

  • Article

Hello All,

This month have been filled with interesting and thought provoking issues. Here's a new one

In a recent scenario, I tried dropping a “secondary” data-file in SQL 2005. Got below error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for DataFile 'test1dat4'. (Microsoft.SqlServer.Smo) ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Time-out occurred while waiting for buffer latch type 3 for page (1:70873021), database ID 5. (Microsoft SQL Server, Error: 845)

Please Note, I have already did, Empty file by migrating the data to other files in the same file-group.

As Per KB # 898709 latch type 3 indicates that the SQL Server process is trying to acquire an update (UP) latch.

This is what I did to resolve the issue:

1. Set the database to SINGLE_USER
USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

2. Remove the Secondary data-file
USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

3. Set the database back to MULTI_USER
USE master;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

It worked !

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.