October 30, 2015 at 12:32 pm
Hi All,
We are unable to restore the log files and it is giving the below error message and the database status is in Standby/SingleUSer/Read-only
Please suggest how to fix this issue
Message
Executed as user: Username. Changes to the state or options of database 'DatabaseNAme' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. [SQLSTATE 42000] (Error 5064) ALTER DATABASE statement failed. [SQLSTATE 42000] (Error 5069). The step failed.
November 1, 2015 at 5:15 am
Seems like you made the DB to Single USer but some other connection is holding the DB. SQL Agent might be holding check the processes access the DB. Kill all connection and then try.
April 5, 2016 at 1:05 am
This was removed by the editor as SPAM
April 5, 2016 at 1:24 am
Did you think it would still need fixing :-D?
April 5, 2016 at 1:34 am
This was removed by the editor as SPAM
April 5, 2016 at 2:47 am
jacksonandrew321 (4/5/2016)
Try to run this:ALTER DATABASE Databasename
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
It won't work.
ALTER DATABASE requires that the session running it can connect to the DB. If the DB is in single user mode and there's a user connected (as the OP's error message states), then the session running the ALTER DATABASE can't connect to the DB and hence can't run.
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
April 14, 2016 at 11:37 am
alter the log shipping configuration to disconnect users during the database restores. Its not an error you're seeing, you've told LS you don't want to disconnect users. When the connections have closed and the restore runs it'll be able to gain exclusive access to the secondary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply