July 11, 2012 at 1:26 am
Hello,
I am having trouble setting a filegroup within a database to read only.
I have created a filegroup called "Archive" within a database named "SampleDB". It is not the default filegroup and when I try the following T-SQL statement:
USE TempDB
GO
ALTER DATABASE SampleDb
MODIFY FILEGROUP [Archive] READONLY
GO
The following error appears:
Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'SampleDB'
The PC that's running SQL Server with the database in question on has been freshly rebooted and upon starting SSMS I then open a new query window and execute the above T-SQL, so I do not understand why the system is telling me the database is in use.
Any help appreciated.
Regards
Steve
July 11, 2012 at 1:34 am
There's another connection, probably from SSMS (it often opens multiple connections).
Make sure object explorer is closed, that you are in the context of some other DB to run the alter (master's good) and that there are no other open connections (check sys.dm_exec_requests)
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
July 11, 2012 at 2:14 am
OK, I am not familiar with the table you suggested, but I'm guessing that it was the "open_transaction_count" column that should be of key interest? If so, then none of the counts were > 0.
In the meantime I've rebooted the system again and after doing/checking the above issued the T-SQL command as per my original post (this time using master rather than TempDB as the database context for what it's worth).
Interestingly, the result is the same (i.e it doesn't work) but the error message is now different. It now states:
Msg 3636, Level 16, State 1, Line 2
An error occurred while processing 'AltFile' metadata for database id 9 file id 6.
July 11, 2012 at 3:02 am
No, transaction count is of no interest here. All that's of interest is whether there are other connections (other rows) than yours.
That error sounds nasty.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
July 11, 2012 at 3:27 am
Ran the DBCC CHECKDB command you supplied and re-tried, but the result was the same.
With regards to querying sys.dm_exec_requests, I get 34 rows returned. However, I am not sure how I can tell what rows relate to me as it were.
July 11, 2012 at 3:31 am
And, the checkDB output was???
That command doesn't fix anything, it checks for errors in the database.
As for sys.dm_exec_requests, any sessions from your login name other than the one you are using to run the alter.
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
July 11, 2012 at 3:55 am
There were no message returned as a result of performing the DBCC CHECKDB command with the options you specified other than telling me that the command was successful.
If I understand you - and I'm not sure I do 🙂 All the rows contained in the query of sys.dm_exec_requests have the same user_id.
July 11, 2012 at 5:59 am
raotor (7/11/2012)
There were no message returned as a result of performing the DBCC CHECKDB command with the options you specified other than telling me that the command was successful.If I understand you - and I'm not sure I do 🙂 All the rows contained in the query of sys.dm_exec_requests have the same user_id.
Run below given query to check if any other connections are made to the same database that you are trying to modify. You should get just 1 row if only you have connected to the database.
SELECT * FROM sys.dm_exec_requests WHERE session_id > 50 AND database_id = DB_ID('<YourDatabaseNameHere>')
July 11, 2012 at 6:32 am
OK, I ran the query you kindly provided and it yielded no rows. This suggests that there are no other resources/connections using the database in question.
Interestingly, prior to receiving your last message I also tried removing the filegroup and starting over. I successfully removed the filegroup and decided to recreate it using the SSMS GUI via the "Properties" context menu option while right-clicking on the DB in question. This time I created the filegroup and ticked the "Read Only" check box hoping that would somehow make some difference further along the process.
Filegroup creation was successful and then I created the file within it without any problems. OK, the "Read Only" check box ticked earlier didn't achieve anything as the filegroup defaulted to "read write" once I'd actually created a file within it.
So, I was back where I started more or less. Next I tried to change the filegroup to READONLY status and the same error was produced.
I'm assuming that not actually having any tables or any data in them present within this filegroup should make any difference to the problem I have.
Quite frankly I am at a loss. I have had no problem creating a read only filegroup within another database on the same server, so I'm baffled.
Regards
Steve
July 11, 2012 at 7:16 am
raotor (7/11/2012)
OK, I ran the query you kindly provided and it yielded no rows. This suggests that there are no other resources/connections using the database in question.
Now there aren't. Back when you were getting errors about 'other users' there would have been.
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
July 11, 2012 at 7:34 am
GilaMonster (7/11/2012)
raotor (7/11/2012)
OK, I ran the query you kindly provided and it yielded no rows. This suggests that there are no other resources/connections using the database in question.Now there aren't. Back when you were getting errors about 'other users' there would have been.
Sure, I understand (or at least I think I do) that rows will appear during the execution of my ALTER command and will disappear once done. But I thought this query was going to help me determine whether there were any latent connections present?
July 11, 2012 at 8:06 am
Sorry, sys.dm_exec_sessions (not requests). Shouldn't answer questions before 2nd cup of coffee.
That will show you other connections present, filter for is_user_session = 1 (or something like that, can't recall column name offhand)
As for the other error, not sure, I've asked some other people if they know. Nothing yet.
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
July 11, 2012 at 8:22 am
GilaMonster (7/11/2012)
Sorry, sys.dm_exec_sessions (not requests). Shouldn't answer questions before 2nd cup of coffee.That will show you other connections present, filter for is_user_session = 1 (or something like that, can't recall column name offhand)
As for the other error, not sure, I've asked some other people if they know. Nothing yet.
Hey, no worries 🙂
Just glad of any help on offer.
THanks for all your efforts.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply