Recently I get into a situation where one of Database file freeze and stopped responding to any of the queries executed on the objects using the file
On killing the select statement it goes into rollback state with WAIT TYPE DISKIO_SUSPEND.
DISKIO_SUSPEND: Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.
Resolution: Forcefully take database offline.
ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK IMMEDIATE |
Bring the database Online
ALTER DATABASE database-name SET ONLINE |