Forum Discussion
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 https://www.nocentino.com/posts/2021-09-25-container-file-permissions-and-sql/ 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 https://www.nocentino.com/posts/2021-09-25-container-file-permissions-and-sql/ 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*'
