May 23, 2016 at 11:59 am
Hello SQL Experts,
Recently, I am facing weird situation.
We have a SQL job. In that job we are running a statement
alter database dbname set SET RESTRICTED_USER.
After some data import from csv files, we are bringing the database to multi user mode.
I have sys admin permission. So even though database in restricted_user, I should have to able to use the database but some how I can't able use the database. Getting the below error
Database is in transition. Try the statement later
I remember Previously I was able to connect, even though db is in restricted state. There are no change in permission.
Any idea the reason behind this?
May 23, 2016 at 12:16 pm
I thought that error message was related to OFFLINE database state.
Check sysdatabases and sys.databases to see various state flags for the database of concern.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 23, 2016 at 12:57 pm
I've found that most commonly people hit that error when there's an ALTER DATABASE command to change the state of the DB that has not yet completed, usually because the ALTER DATABASE command is blocked.
There are some other more unusual cases I've run into, but that's the most common.
When you encountered the error, did you confirm that the ALTER DATABASE ... SET RESTRICTED_USER had completed?
Cheers!
P.S. Here are easy repro steps as an example.
In a query window, run this:
CREATE DATABASE in_transition;
GO
USE in_transition;
GO
In a second query window, run this:
ALTER DATABASE in_transition SET RESTRICTED_USER;
In a third query window, now run this:
SELECT * FROM in_transition.sys.tables;
In this scenario, the ALTER DATABASE in query window 2 is blocked by the session from query window 1. As long as that is the case, other sessions that attempt to USE or query the database will throw error 952 as you are seeing.
May 23, 2016 at 1:21 pm
Now I can able to connect to the database.
Here is the my main issue.
We have a sql job which has several steps.
In 1 step, we are killing the existing connections and in the same steps we are keeping the database in multi user mode.
In the 2nd step, we are using different database and importing data from csv files (This db is used as staging) to the database2.
In the 3rd step, we are trying to import data from database2 (Stage db) to database1.
The step is failing with the error Unable to connect to SQL Server. The step failed.
The agent is running under service account. The below errors are logged under the SQL Agent logs
[298] SQLServer Error: 18456, Login failed for user 'ServiceAccount'. [SQLSTATE 28000]
[298] SQLServer Error: 4060, Cannot open database "database1" requested by the login. The login failed. [SQLSTATE 42000]
so in that case, do you think when running step3. The command in step1, which is altering the db to restricted mode is not completed yet? Because the service account has also sysadmin permissions. Also this job is running fine for almost 1 yr. Why suddenly happening this?
May 23, 2016 at 2:14 pm
If 1) this really is all in a single job, 2) the ALTER DATABASE ... SET RESTRICTED_USER is in the first job step, and 3) the first job step is succeeding, then it would be a different scenario than the one I described above.
With the limited information we have, it's difficult for me to guess much further. You'll just have to tackle this systematically.
1) You say that you got the 952 error, and then were able to connect later. What steps, if any, had you taken between getting the 952 error and the successful login?
2) It might end up not being all that helpful, but when the job runs and fails, what is the exact message in the error log for the 18456 (the login failure)?
3) Try running the job in pieces.
a) Manually run just step 1. Check to see that it succeeds, and check the state and accessibility of the database at that point.
b) Repeat this for each of the following steps, carefully noting the state and accessibility of the database at each step.
With the right information collected, it should be pretty straightforward to determine the problem; right now we just don't have enough data to say for sure.
Cheers!
May 24, 2016 at 6:18 am
Does more than one account in your system have restricted user access? That would likely be what's occurring. Something else is there, preventing you from changing the database setting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2016 at 1:31 pm
Do you mean
The alter database to restricted mode is failing because some other sysadmin connection is being used that time.
This is exactly running in step1
BEGIN
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('TestDB')
and SPID >50
and SPID <> @@Spid
--select @kill
EXEC(@kill);
ALTER DATABASE TestDB SET RESTRICTED_USER
ALTER DATABASE TestDB SET RESTRICTED_USER
It looks Alter database is running 2 times in sequnece
Is this causes any issues? But still not understand how it was successful previously
May 24, 2016 at 1:31 pm
Do you mean
The alter database to restricted mode is failing because some other sysadmin connection is being used that time.
This is exactly running in step1
BEGIN
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('TestDB')
and SPID >50
and SPID <> @@Spid
--select @kill
EXEC(@kill);
ALTER DATABASE TestDB SET RESTRICTED_USER
ALTER DATABASE TestDB SET RESTRICTED_USER
It looks Alter database is running 2 times in sequenece
Is this causes any issues? But still not understand how it was successful previously
May 24, 2016 at 1:35 pm
ramana3327 (5/24/2016)
Do you meanThe alter database to restricted mode is failing because some other sysadmin connection is being used that time.
This is exactly running in step1
BEGIN
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('TestDB')
and SPID >50
and SPID <> @@Spid
--select @kill
EXEC(@kill);
ALTER DATABASE TestDB SET RESTRICTED_USER
ALTER DATABASE TestDB SET RESTRICTED_USER
It looks Alter database is running 2 times in sequenece
Is this causes any issues? But still not understand how it was successful previously
We still don't have enough information to really explain why you're seeing what you're seeing.
However, I can definitely suggest that instead of trying to manually kill all the processes in the DB and hope nothing jumps in before you issue the ALTER DATABASE command, just use ALTER DATABASE...WITH ROLLBACK IMMEDIATE instead.
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply