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.
PingBack from http://microsoft-sharepoint.simplynetdev.com/unable-to-drop-data-file-%e2%80%93-error-time-out-occurred-while-waiting-for-buffer-latch-type-3/