March 15, 2016 at 10:19 am
I am not going to agree with Brandie right off the top. What are you using for your backups? Is this system part of a high availability solution and if so what?
March 15, 2016 at 10:54 am
We use Renovo for backups.
March 15, 2016 at 10:55 am
Oh, and it is not part of a high availability solution.
March 15, 2016 at 11:32 am
Not familiar with the product/service or how it works. I am going to have to start leaning to a rebuild of the server that Brandie recommended. That could change if you can provide additional information, such as any other errors that may be listed in the SQL Server or SQL Agent error logs or even in the Windows logs.
When it comes to backing up my SQL Server databases, I prefer using the native tools or 3rd party tools like Redgates, Idearas, or Lightspeed.
March 15, 2016 at 12:24 pm
Renovo is the SAN backup solution that also does SQL Server.
The only error I get in SQL Server is:
SQLServerAgent could not be started (reason: SQLServerAgent cannot start because the msdb database is read-only).
I see no errors in Windows logs
March 15, 2016 at 12:24 pm
I did some playing around on my local instance. First I set msdb to read-only in SQL Server and SQL Agent wouldn't start. Then I set it back to read-write, stopped all services, & marked the data and log files as read-only in Windows. The msdb database is marked as Recovery Pending, but the SQL Agent service will start, it just won't do anything. The same thing happens if just the data file is read-only in Windows. I never actually got an error stating that msdb was read-only.
You don't need to rebuild the SQL Server as you can just rebuild msdb. You can see how about 2/3 of the way down this BOL Article. YOu will lose jobs, operators, alerts, and anything else that you currently have setup in msdb.
I also have never heard of Renovo so I can't give any thoughts on it, other than I tend to be skeptical of any snapshot type backups of SQL Server, especially if they aren't using VSS and freezing the databases as they copy them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 15, 2016 at 2:00 pm
Did you check to see that MSSQLServer itself has permissions to the file?
It might be possible that the file was copied but permissions were not.
Check permissions on another instance to compare.
March 16, 2016 at 4:05 am
I am going to take a wild guess that there is some unwanted interaction between the backup system and SQL Server. It could be that the backup system has a NTFS write lock on the file which would result in SQL Server reporting that it is read-only.
My first step would be to say 'I have currently got a readable and apparently complete msdb, I need to make sure I do not loose this'. This means taking a SQL Server backup to a .bak file. If the presumed write lock prevents the backup from working, then I would take a 'copy only' SQL Server backup to a .bak file. If this fails I would shut down SQL Server and do a file copy of the msdb data and log files to another location. (I actually would aim to shut down SQL and take a file copy regardless of if the backup worked - always keep two routes to recovery.)
Then comes the troubleshooting. This may need help from the people managing the backup system and from your Windows support people. If having SQL Agent down is a problem then you have to timebox the troubleshooting.
If you get to the end of the timebox before the problem is found, then you need to do something that could be destructive.
I would first try to restore msdb, but use different file locations for the data and log files, so that the original files are left intact.
If I could only get a file copy of msdb, then I would take another copy of these files, start SQL Server in single-user mode, then detach and re-attach msdb to my copy of msdb.
After starting SQL Server in normal mode I would try to start SQL Agent. If all worked OK then I would first take a SQL Server backup of msdb then look at https://msdn.microsoft.com/en-us/library/ms190749(v=sql.105).aspx to see what other post-restore work was needed.
As the last part of the fix process I would take yet another SQL Server backup of msdb.
This would allow further troubleshooting to take place on the original msdb files that SQL Server found were read-only. You can attach these files using a different db name and try creating a new table to see if the read-only condition has gone. Eventually you will either find the cause of the read-only condition or give up trying. At this stage I would delete the old msdb files or they are bound to confuse me or my successor at a later date.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply