Sql-server – Cannot drop distribution database in SQL Server 2012

replicationsql server

I am trying to drop distribution database but cannot as I am getting this error:

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000056000 in file 'E:\XXX_Replication\Data\distribution.MDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The underlying drive on which the .mdf file existed was replaced, thus deleting the .mdf files. Replication is not needed on this server. But this is production server and cannot be restarted.. Please help me to delete this database.

Best Answer

first of all, thanks for the help and suggestions. I have now managed to delete the distribution database without rebooting the server. this is how i did it.

  1. Created a distribution database with similar file locations on a different server.
  2. Took the backup of this new distribution database.
  3. Restored this backup on the existing distribution database with REPLACE, MOVE option.
  4. Finally disabled distribution on the server.

Thanks again for the help.

Related Question