Forum Discussion
Solved
Why is my database set to read only and how can I fix it?
I have migrated my SQL Server database to a Docker container on WSL2 using 2019-latest and now I find my database file is read only.
If I change the state property "Database Read-Only" value to "True" then SQL Server says:
To change the database properties, SQL Server must close all other connections to the database. Are you sure you want to change the properties and close all other connections?
I hit yes and then get this error:
Alter failed for Database 'Pitcher8'. (Microsoft.SqlServer.Smo) For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&Evtsrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Unable to open the physical file "/var/opt/mssql/data/Pitcher8.mdf". Operating system error 5: "5(Access is denied.)". Unable to open the physical file "/var/opt/mssql/data/Pitcher8_log.ldf". Operating system error 5: "5(Access is denied.)". Could not restart database "Pitcher8". Reverting to the previous status. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5120) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5120-database-engine-error
I tried the Bing chat bot and it advised me to try the SQL Server Configuration manager which does not come with the docker image.
I tried this query for my database.
USE Pitcher8; GO ALTER DATABASE Pitcher8 SET READ_WRITE; GO
No joy. That query took a loooong time to execute so I stopped it.
I ran the stored system procedure sp_who2 and couldn't find any user causing issues and neither could the chat bot. I also ran the stored procedure xp_readerrorlog to view the SQL Server error log and got this output.
2023-08-16 11:45:37.770 spid52 [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2023-08-16 11:45:37.770 spid52 Starting up database 'Pitcher8'.
2023-08-16 11:45:37.790 spid52 Parallel redo is started for database 'Pitcher8' with worker pool size [8].
2023-08-16 11:45:37.800 spid52 Parallel redo is shutdown for database 'Pitcher8' with worker pool size [8].
2023-08-16 11:45:37.810 spid52 Synchronize Database 'Pitcher8' (5) with Resource Database.
2023-08-16 11:45:37.810 spid52 System objects could not be updated in database 'Pitcher8' because it is read-only.
2023-08-16 11:52:11.150 spid52 Setting database option READ_WRITE to ON for database 'Pitcher8'.
The log itself isn't helpful so the chatbot advised me to detach the ‘Pitcher8’ database from the container to a new location outside the container on the Windows files system, and then reattach the database using the recent file locations. So I detached the database, copied the database and log file from /var/opt/mssql/data to the Windows file system and back to /var/opt/mssql/, and still have the same problem as before.
Javier_Villegas Finally found out it was likely because I was running SQL Server in a Linux container. It said in this blog that the user accounts and groups on the base OS likely don’t sync up with the user accounts and groups inside the container.
Anyway all I did is use this command to list the log and mdf file inside the container:
docker exec MyContainer bash -c 'ls -lan /var/opt/mssql/data/MyDatabase*'
This was the output. The permissions show it was read-only in the container.
-rw-r----- 1 501 20 21474836480 Sep 24 14:23 MyDatabase.mdf
-rw-r----- 1 501 20 1073741824 Sep 24 14:23 MyDatabase_log.ldfI fixed that by running these 2 commands which allowed me to finally write to the database:
docker exec -u 0 MyContainer bash -c 'chown 10001:0 /var/opt/mssql/data/MyDatabase*'
docker exec -u 0 MyContainer bash -c 'chmod 660 /var/opt/mssql/data/MyDatabase*'
2 Replies
Seems like the database files are in read-only mode so sql get the DB as read-only
I don't know the specifics for Linux but in the Windows Server world this could be that the SQL Service account does not have the proper rights on the disk and/or folder where the data or logs files are located.
Regards
Javier
Javier_Villegas Finally found out it was likely because I was running SQL Server in a Linux container. It said in this blog that the user accounts and groups on the base OS likely don’t sync up with the user accounts and groups inside the container.
Anyway all I did is use this command to list the log and mdf file inside the container:
docker exec MyContainer bash -c 'ls -lan /var/opt/mssql/data/MyDatabase*'
This was the output. The permissions show it was read-only in the container.
-rw-r----- 1 501 20 21474836480 Sep 24 14:23 MyDatabase.mdf
-rw-r----- 1 501 20 1073741824 Sep 24 14:23 MyDatabase_log.ldfI fixed that by running these 2 commands which allowed me to finally write to the database:
docker exec -u 0 MyContainer bash -c 'chown 10001:0 /var/opt/mssql/data/MyDatabase*'
docker exec -u 0 MyContainer bash -c 'chmod 660 /var/opt/mssql/data/MyDatabase*'
