November 13, 2015 at 4:03 am
Hello,
I am trying to drop distribution database but cannot as I am getting the below error
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000056000 in file 'E:\BM_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.
I have tried everything like
removing publication
removing subscription etc.
I cannot even run CHECKDB on it as I keep on getting the above error.
Also to keep in mind that this is happening on my Production server.
And now because of this error I cannot even add this server to Redgate ..
Please help.
Kind regards
T
I am exhausted now. Tried everything. but this freaking distribution database is not getting removed from management studio.
This is also blocking created further publications and subscriptions. I CANNOT / not allowed to restrart SQL server as this is PRODUCTION. but i need to get rid of this database. again below is the error that |I get..
" Msg 823, Level 24, State 2, Procedure sp_MSdrop_qreader_agent, Line 20
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000002f0000 in file 'E:\BM_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."
PLEASE HELP Guys....
November 13, 2015 at 4:05 am
The operating system returned error 21(The device is not ready.) to SQL Server
The E drive, which is where the data file is, is not present or is not online. Speak with your sysadmin/storage admin about that drive, what's wrong with it and why it's not accessible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2015 at 4:23 am
The E:\ is present. and I can access the mentioned folder.
November 13, 2015 at 4:30 am
have you disabled replication on the server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 13, 2015 at 4:38 am
While trying to disable replication I still get the same error.
I cannot issue any command against the distribution database as I get the same error again and again.
can I try deleting the distribution.mdf file ??
I am not using replication at all.
November 13, 2015 at 4:41 am
Try taking the DB offline and bringing it back online, see if that fixes things. When SQL tried to open that DB, the E drive was not accessible to it, hence the errors.
Don't delete the files unless you want some messy cleanup afterwards
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2015 at 4:52 am
Nope cannot take it OFFLINE.
cannot do anything with distibution database.
All the ALTER commands against it are failing with the same error.
cannot even open the properties dialog box.
November 13, 2015 at 5:00 am
Can you remote onto the server and double-check that this path exists and is accessible, and that the account that SQL runs under has full control of that folder?
E:\BM_Replication\Data\
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2015 at 5:09 am
I think I know what has happened.
Around 2 weeks back, we had an outage on this server. and it was decided that the storage on the E:\ drive needs to be replaced.
earlier it was ISCSi storage and it was swapped with Netapp.
distribution data and log files were on this drive.
someone copied these files from the old E:\ drive to the new one.
now today I was able to delete both those file .mdf and .ldf.
but i can still see tables under distribution database, which is confusing me as I thought that by deleting the .mdf file the tables etc. will also get deleted. I cannot find any other file on the server related to disrtibution database.
I have no clue what is going on.
November 13, 2015 at 5:30 am
Did you take a copy of the files before you deleted?
Like Gail asked, did you check the permission before you deleted those files?
-Roy
November 13, 2015 at 5:37 am
I have the permissions.
but I did not take the copy of the files before deletion
November 13, 2015 at 6:21 am
I didn't ask if you had permissions. Your permissions are completely irrelevant.
I asked whether the account that SQL runs under has permission to that folder
When can you restart the SQL instance on this server (not the server, just SQL)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2015 at 6:39 am
tauseef.jan (11/13/2015)
I think I know what has happened.Around 2 weeks back, we had an outage on this server. and it was decided that the storage on the E:\ drive needs to be replaced.
earlier it was ISCSi storage and it was swapped with Netapp.
distribution data and log files were on this drive.
someone copied these files from the old E:\ drive to the new one.
now today I was able to delete both those file .mdf and .ldf.
but i can still see tables under distribution database, which is confusing me as I thought that by deleting the .mdf file the tables etc. will also get deleted. I cannot find any other file on the server related to disrtibution database.
I have no clue what is going on.
Have you run the drop database command against the distribution database? Maybe the database is still there, because the meta data about the database has not been cleaned up properly.
November 13, 2015 at 7:00 am
I have got full permissions on the folder.
But i did not take the backup of the files before deleting them
November 13, 2015 at 7:08 am
Under what user is the service running? Under your name or a service account. If the service is not running under your account, it does not matter whether you have all permissions or not. Also, was the directory path exactly the same as before? No spelling mistake or spaces in between?
But since you deleted those files this is of no use anymore.
You might have to restart the SQL Server service account.
-Roy
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply