Master in single user mode

On Thu, 18 Sep 2008 06:39:01 -0700, Sammy

Post by Sammy
I had to restore a master database on sql 2005. Now its continually in single
user mode and I can make it multiuser mode has anyone had this error

You had to start SQL Server in single user mode to make the change.
Stop SQL Server, and then restart it the normal way rather than with
the extra parameter you used to put it in single user mode.

So the question is, how did you start it in single user mode? Did you
make a change to the service? If so you need to undo that. I prefer
to start it from the command line for when using a special parameter
for just a few minutes, which saves messing with the service.

Roy Harvey
Beacon Falls, CT

Sammy

2008-09-18 14:52:03 UTC

Had a critical server failure. When sysadmin performing a repair it deleted
the system databases.
I restored the backed up master database on another server. Then detached
these master files and placed them in the sql server with a failure directory
where sql
server system databases were.
I could then start sql server but I can't get it out of single user mode.
Everything is working apart from sql server in single user mode and I can't
get this removed.

the configuration manager neither

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 06:39:01 -0700, Sammy

Post by Sammy
I had to restore a master database on sql 2005. Now its continually in single
user mode and I can make it multiuser mode has anyone had this error

You had to start SQL Server in single user mode to make the change.
Stop SQL Server, and then restart it the normal way rather than with
the extra parameter you used to put it in single user mode.
So the question is, how did you start it in single user mode? Did you
make a change to the service? If so you need to undo that. I prefer
to start it from the command line for when using a special parameter
for just a few minutes, which saves messing with the service.
Roy Harvey
Beacon Falls, CT

Roy Harvey (SQL Server MVP)

2008-09-18 15:46:42 UTC

On Thu, 18 Sep 2008 07:52:03 -0700, Sammy

Post by Sammy
Had a critical server failure. When sysadmin performing a repair it deleted
the system databases.
I restored the backed up master database on another server. Then detached
these master files and placed them in the sql server with a failure directory
where sql
server system databases were.
I could then start sql server but I can't get it out of single user mode.
Everything is working apart from sql server in single user mode and I can't
get this removed.

That is not sound like the usual approach to restoring the master
database. Do you have a backup of master from the database affected?
As long as you have it running in single user mode you can restore the
last backup of master, as described in Books Online. (I trust that
you started by reading the section titled "Restoring the master
Database" in Books Online.)

As for being stuck in single user mode, unless this is a side effect
of using a copy of master from another server I would double check the
startup parameters of the service. Also check the SQL Server log for
any interesting messages.

Roy Harvey
Beacon Falls, CT

Sammy

2008-09-18 16:41:01 UTC

Yes the problem was as described the master database did not exist on the
server at all. So it failed start up.

I did have a backup of this database but I can't get the server to start
without a mdf file.

Thats why I had to restore that database on to another data server just to
get the mdf file, ldf files.

Then I used these and placed them in the sql data directory. But now as I
said its always in single user mode

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 07:52:03 -0700, Sammy

That is not sound like the usual approach to restoring the master
database. Do you have a backup of master from the database affected?
As long as you have it running in single user mode you can restore the
last backup of master, as described in Books Online. (I trust that
you started by reading the section titled "Restoring the master
Database" in Books Online.)
As for being stuck in single user mode, unless this is a side effect
of using a copy of master from another server I would double check the
startup parameters of the service. Also check the SQL Server log for
any interesting messages.
Roy Harvey
Beacon Falls, CT

Roy Harvey (SQL Server MVP)

2008-09-18 17:18:40 UTC

On Thu, 18 Sep 2008 09:41:01 -0700, Sammy

Post by Sammy
Yes the problem was as described the master database did not exist on the
server at all. So it failed start up.
I did have a backup of this database but I can't get the server to start
without a mdf file.

Yes, you can't restore master unless you have a working copy of SQL
Server. Sometimes that means running the install with special
parameters. That is covered in Books Online in the section "How to:
Install SQL Server 2005 from the Command Prompt", subheading
"Rebuilding System Databases, Rebuilding the Registry". But since it
is starting, though in sing user mode, you hopefully can avoid that.

Post by Sammy
Thats why I had to restore that database on to another data server just to
get the mdf file, ldf files.
Then I used these and placed them in the sql data directory. But now as I
said its always in single user mode

Have you actually restored from the correct backup yet? If not, well
that requires single user mode so you should be able to do that at
least. Then see what happens.

Roy Harvey
Beacon Falls, CT

Sammy

2008-09-18 17:51:01 UTC

Hi Roy,

Yes the file I used from the backup was the original master db.
It says single user mode for the master database but people can connect to
other databases. If I leave this as it is until say the weekend can you see
any problems from this setting for the master database

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 09:41:01 -0700, Sammy

Yes, you can't restore master unless you have a working copy of SQL
Server. Sometimes that means running the install with special
Install SQL Server 2005 from the Command Prompt", subheading
"Rebuilding System Databases, Rebuilding the Registry". But since it
is starting, though in sing user mode, you hopefully can avoid that.

Have you actually restored from the correct backup yet? If not, well
that requires single user mode so you should be able to do that at
least. Then see what happens.
Roy Harvey
Beacon Falls, CT

Roy Harvey (SQL Server MVP)

2008-09-18 19:51:06 UTC

On Thu, 18 Sep 2008 10:51:01 -0700, Sammy

Post by Sammy
Hi Roy,
Yes the file I used from the backup was the original master db.
It says single user mode for the master database but people can connect to
other databases. If I leave this as it is until say the weekend can you see
any problems from this setting for the master database

Sorry if I misunderstood, I was talking about the server being in
single user mode, you were talking about master.

Have you looked at the DATABASEPROPERTYEX page in Books Online? (Note
that the page was updated since the version of BOL that shipped with
SQL Server 2005. It is always worth keeping BOL up to date by
downloading and installing the latest version from Microsoft.) The
property UserAccess might be of interest. And then there is ALTER
DATABASE and the db_user_access_option, { SINGLE_USER |
RESTRICTED_USER | MULTI_USER }.

Roy Harvey
Beacon Falls, CT

Sammy

2008-09-18 20:29:01 UTC

Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already tried the
alter database command and I get error can't change master to etc.

Yes normally you can only get master database in single user mode by using
the -m parameter and no one can access the server apart from the admin. But
in this case everyone who has permisions can access the databases as they did
before. We have sharepoint installed so didn't want to risk overwriting it if
doing a fresh install. So sharepoint is back up so could take a backup of the
whole farm as well, just this darn single user mode for the master database
to resolve.

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 10:51:01 -0700, Sammy

Sorry if I misunderstood, I was talking about the server being in
single user mode, you were talking about master.
Have you looked at the DATABASEPROPERTYEX page in Books Online? (Note
that the page was updated since the version of BOL that shipped with
SQL Server 2005. It is always worth keeping BOL up to date by
downloading and installing the latest version from Microsoft.) The
property UserAccess might be of interest. And then there is ALTER
DATABASE and the db_user_access_option, { SINGLE_USER |
RESTRICTED_USER | MULTI_USER }.
Roy Harvey
Beacon Falls, CT

Roy Harvey (SQL Server MVP)

2008-09-18 20:54:57 UTC

On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already tried the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps someone else
will join the discussion.

Roy Harvey
Beacon Falls, CT

Tibor Karaszi

2008-09-19 07:04:06 UTC

Perhaps the SQL Server service account has some limited permissions on the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested by Roy but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already tried the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps someone else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Sammy

2008-09-21 07:06:01 UTC

Oh I missed that I checked the actual mdf system and admin had full
permissions have added the Sql account that runs the service but that account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited permissions on the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested by Roy but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already tried the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps someone else
will join the discussion.
Roy Harvey
Beacon Falls, CT

John Bell

2008-09-21 09:01:46 UTC

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had full
permissions have added the Sql account that runs the service but that account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited permissions on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested by Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already tried the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps someone else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi

What does sp_helpdb 'master' return?

John

Sammy

2008-09-21 12:30:01 UTC

Hi John, thanks,

sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)

so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE, Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics,
IsAutoUpdateStatistics,90

The only error message in the log I receive is below. apart frrom that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.

'Titan\SQLservice' is the servce account that runs sql and sql agent it has
sa rights and local admin

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited permissions on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested by Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already tried the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps someone else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Sammy

2008-09-21 18:21:01 UTC

sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE, Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart frrom that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'Titan\SQLservice' is the servce account that runs sql and sql agent it has
sa rights and local admin

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited permissions on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested by Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already tried the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps someone else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Tim

2008-10-01 15:59:01 UTC

Hello,
I am experiencing nearly the same issue as Sammy. I changed to Single User
mode from the GUI (i.e. DB properties box), not using the command line switch
-m. Now when I choose Multi User mode from the GUI, a message appears that
says 'Option Multi-User cannot be set in database master." I've tried adding
the -m switch and then removing it to see if that would jumpstart things, but
it didnt. Any ideas?

sp_helpdb and sys.databases seem to get the database access type in
different ways. It could mean that if something uses the same method as
sys.databases you may have problems.
John

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited permissions on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested by Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already
tried
the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps someone else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Kalen Delaney

2008-10-01 16:09:55 UTC

Hi Tim

There is a difference between setting single user for a single database, and
setting it for the entire SQL Server instance. The -m command line flag is
used to set the entire instance to single-user. I recommend you don't use
this flag unless you are absolutely confident in what you are doing.

To set a particular database to single-user, you can use the ALTER DATABASE
command, or the properties box in the GUI.

You cannot change the properties of the master database. My guess is you
changed some other database to single user, not master.
You can run this query to see what state each db is in:

SELECT name, user_access_desc
FROM sys.databases
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Tim
Hello,
I am experiencing nearly the same issue as Sammy. I changed to Single User
mode from the GUI (i.e. DB properties box), not using the command line switch
-m. Now when I choose Multi User mode from the GUI, a message appears that
says 'Option Multi-User cannot be set in database master." I've tried adding
the -m switch and then removing it to see if that would jumpstart things, but
it didnt. Any ideas?

sp_helpdb and sys.databases seem to get the database access type in
different ways. It could mean that if something uses the same method as
sys.databases you may have problems.
John

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart frrom that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'Titan\SQLservice' is the servce account that runs sql and sql agent
it
has
sa rights and local admin

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had full
permissions have added the Sql account that runs the service but
that
account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited
permissions
on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested
by
Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already
tried
the
alter database command and I get error can't change master to etc.

Well you have a situation beyond my experience. Perhaps
someone
else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Tim

2008-10-01 17:01:01 UTC

Thank you Karen, however it is indeed Master that is set to Single-User, via
the GUI. When I run the command you listed, here is what is returned for
master. MULTI_USER is listed for all others.

master SINGLE_USER

Post by Kalen Delaney
Hi Tim
There is a difference between setting single user for a single database, and
setting it for the entire SQL Server instance. The -m command line flag is
used to set the entire instance to single-user. I recommend you don't use
this flag unless you are absolutely confident in what you are doing.
To set a particular database to single-user, you can use the ALTER DATABASE
command, or the properties box in the GUI.
You cannot change the properties of the master database. My guess is you
changed some other database to single user, not master.
SELECT name, user_access_desc
FROM sys.databases
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

sp_helpdb and sys.databases seem to get the database access type in
different ways. It could mean that if something uses the same method as
sys.databases you may have problems.
John

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart frrom that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'Titan\SQLservice' is the servce account that runs sql and sql agent
it
has
sa rights and local admin

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had full
permissions have added the Sql account that runs the service but
that
account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited
permissions
on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested
by
Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already
tried
the
alter database command and I get error can't change master to
etc.

Well you have a situation beyond my experience. Perhaps
someone
else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Roy Harvey (SQL Server MVP)

2008-10-01 17:38:19 UTC

Karen? Who's Karen?
Kalen is the one who responded to your issue....
;-)

Roy Harvey
Beacon Falls, CT

Post by Tim
Thank you Karen, however it is indeed Master that is set to Single-User, via
the GUI. When I run the command you listed, here is what is returned for
master. MULTI_USER is listed for all others.
master SINGLE_USER

Tim

2008-10-01 17:49:01 UTC

Whoops, my mistake ;) Sorry Kalen.

Any ideas on how to get Master back in multi-user mode?

Post by Roy Harvey (SQL Server MVP)
Karen? Who's Karen?
Kalen is the one who responded to your issue....
;-)
Roy Harvey
Beacon Falls, CT

Kalen Delaney

2008-10-01 18:05:49 UTC

Did you read my reply?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Tim
Whoops, my mistake ;) Sorry Kalen.
Any ideas on how to get Master back in multi-user mode?

Post by Roy Harvey (SQL Server MVP)
Karen? Who's Karen?
Kalen is the one who responded to your issue....
;-)
Roy Harvey
Beacon Falls, CT

Kalen Delaney

2008-10-01 17:47:31 UTC

So perhaps after you started your server in single user mode, you never took
it OUT of single user mode. (This is one of the reasons why I don't
recommend using that option.)

Try making another connection... can you get in?

Stop your server, and restart normally.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

sp_helpdb and sys.databases seem to get the database access type in
different ways. It could mean that if something uses the same method as
sys.databases you may have problems.
John

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart frrom that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'Titan\SQLservice' is the servce account that runs sql and sql agent
it
has
sa rights and local admin

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had full
permissions have added the Sql account that runs the service but
that
account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited
permissions
on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested
by
Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but already
tried
the
alter database command and I get error can't change master to
etc.

Well you have a situation beyond my experience. Perhaps
someone
else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Tim

2008-10-01 18:10:09 UTC

Not being able to take it out of single user mode is the exact problem I'm
trying to resolve. We had to put master in Single User mode to restore it,
but now can't take it back out. We've rebooted several times. This issue
has been ongoing for several days. It actually isn't preventing multiple
connections strangely enough, but I'm suspicious that it's causing other
issues.

Post by Kalen Delaney
So perhaps after you started your server in single user mode, you never took
it OUT of single user mode. (This is one of the reasons why I don't
recommend using that option.)
Try making another connection... can you get in?
Stop your server, and restart normally.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

sp_helpdb and sys.databases seem to get the database access type in
different ways. It could mean that if something uses the same method as
sys.databases you may have problems.
John

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart frrom that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'Titan\SQLservice' is the servce account that runs sql and sql agent
it
has
sa rights and local admin

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had
full
permissions have added the Sql account that runs the service but
that
account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited
permissions
on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already
suggested
by
Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but
already
tried
the
alter database command and I get error can't change master
to
etc.

Well you have a situation beyond my experience. Perhaps
someone
else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Kalen Delaney

2008-10-01 18:48:58 UTC

What version are you using? (You should always give us that information
first thing.)

To restore master, you have to put the server in single user mode, not the
master database.

On my SQL Server 2005, I have tried all kinds of things and I cannot get my
master database into single user mode, even when the server is started in
single user mode. So my guess is you are using an earlier version. If that
is the case, there is a fix that involves directly updating the sysdatabases
table, but it carries many risks of its own.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Tim
Not being able to take it out of single user mode is the exact problem I'm
trying to resolve. We had to put master in Single User mode to restore it,
but now can't take it back out. We've rebooted several times. This issue
has been ongoing for several days. It actually isn't preventing multiple
connections strangely enough, but I'm suspicious that it's causing other
issues.

Post by Tim
Thank you Karen, however it is indeed Master that is set to
Single-User,
via
the GUI. When I run the command you listed, here is what is returned for
master. MULTI_USER is listed for all others.
master SINGLE_USER

Post by Kalen Delaney
Hi Tim
There is a difference between setting single user for a single
database,
and
setting it for the entire SQL Server instance. The -m command line
flag
is
used to set the entire instance to single-user. I recommend you don't use
this flag unless you are absolutely confident in what you are doing.
To set a particular database to single-user, you can use the ALTER DATABASE
command, or the properties box in the GUI.
You cannot change the properties of the master database. My guess is you
changed some other database to single user, not master.
SELECT name, user_access_desc
FROM sys.databases
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Tim
Hello,
I am experiencing nearly the same issue as Sammy. I changed to
Single
User
mode from the GUI (i.e. DB properties box), not using the command
line
switch
-m. Now when I choose Multi User mode from the GUI, a message
appears
that
says 'Option Multi-User cannot be set in database master." I've
tried
adding
the -m switch and then removing it to see if that would jumpstart
things,
but
it didnt. Any ideas?

sp_helpdb and sys.databases seem to get the database access type in
different ways. It could mean that if something uses the same
method
as
sys.databases you may have problems.
John

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart
frrom
that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'Titan\SQLservice' is the servce account that runs sql and sql agent
it
has
sa rights and local admin

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had
full
permissions have added the Sql account that runs the service
but
that
account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited
permissions
on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already
suggested
by
Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but
already
tried
the
alter database command and I get error can't change
master
to
etc.

Well you have a situation beyond my experience. Perhaps
someone
else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Kalen Delaney

2008-10-01 18:59:10 UTC

I take it back... if you were not running SQL 2005, you wouldn't have been
able to run that query I gave you.
I cannot figure out HOW master got in single user mode... and cannot imagine
how to get it out.

Although, I will admit I did not try doing a restore of master. Maybe there
was something weird in the database you restored.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Kalen Delaney
What version are you using? (You should always give us that information
first thing.)
To restore master, you have to put the server in single user mode, not the
master database.
On my SQL Server 2005, I have tried all kinds of things and I cannot get
my master database into single user mode, even when the server is started
in single user mode. So my guess is you are using an earlier version. If
that is the case, there is a fix that involves directly updating the
sysdatabases table, but it carries many risks of its own.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Tim
Thank you Karen, however it is indeed Master that is set to
Single-User,
via
the GUI. When I run the command you listed, here is what is returned for
master. MULTI_USER is listed for all others.
master SINGLE_USER

Post by Kalen Delaney
Hi Tim
There is a difference between setting single user for a single
database,
and
setting it for the entire SQL Server instance. The -m command line
flag
is
used to set the entire instance to single-user. I recommend you don't use
this flag unless you are absolutely confident in what you are doing.
To set a particular database to single-user, you can use the ALTER DATABASE
command, or the properties box in the GUI.
You cannot change the properties of the master database. My guess is you
changed some other database to single user, not master.
SELECT name, user_access_desc
FROM sys.databases
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Tim
Hello,
I am experiencing nearly the same issue as Sammy. I changed to
Single
User
mode from the GUI (i.e. DB properties box), not using the command
line
switch
-m. Now when I choose Multi User mode from the GUI, a message
appears
that
says 'Option Multi-User cannot be set in database master." I've
tried
adding
the -m switch and then removing it to see if that would jumpstart
things,
but
it didnt. Any ideas?

sp_helpdb and sys.databases seem to get the database access type in
different ways. It could mean that if something uses the same
method
as
sys.databases you may have problems.
John

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in
brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611,
Collation=Latin1_General_CI_AS, SQLSortOrder=0,
IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart
frrom
that
everything looks like its working fine.
Login failed for user 'Titan\SQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'Titan\SQLservice' is the servce account that runs sql and sql agent
it
has
sa rights and local admin

Post by Sammy
Oh I missed that I checked the actual mdf system and admin
had
full
permissions have added the Sql account that runs the service
but
that
account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited
permissions
on
the master mfd and/or ldf
files? that should be seen in the errorlog (as already
suggested
by
Roy
but you didn't reply to
that).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
in

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but
already
tried
the
alter database command and I get error can't change
master
to
etc.

Well you have a situation beyond my experience. Perhaps
someone
else
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

Roy Harvey (SQL Server MVP)

2008-10-01 19:55:06 UTC

On Wed, 1 Oct 2008 11:59:10 -0700, "Kalen Delaney"

Post by Kalen Delaney
I take it back... if you were not running SQL 2005, you wouldn't have been
able to run that query I gave you.
I cannot figure out HOW master got in single user mode... and cannot imagine
how to get it out.

I wasn't able to figure it out for the poster who started this thread,
but now I don't feel so bad about it!

Post by Kalen Delaney
Although, I will admit I did not try doing a restore of master. Maybe there
was something weird in the database you restored.

The person who had the problem in the first place restored master on a
different server under a different database name, then copied the mdf
and ldf files over to the target server where master was too trashed
for SQL Server to start. SQL Server then started, and other databases
were restored and running fine, but master stayed in singe user mode.

Roy Harvey
Beacon Falls, CT

Kalen Delaney

2008-10-01 20:02:56 UTC

I probably should have read the whole original thread before jumping in
here...
This is a strange one, no doubt about it!
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
www.SQLTuners.com

Post by Roy Harvey (SQL Server MVP)
On Wed, 1 Oct 2008 11:59:10 -0700, "Kalen Delaney"

I wasn't able to figure it out for the poster who started this thread,
but now I don't feel so bad about it!

Post by Kalen Delaney
Although, I will admit I did not try doing a restore of master. Maybe there
was something weird in the database you restored.

Roy Harvey (SQL Server MVP)

2008-10-01 20:39:47 UTC

On Wed, 1 Oct 2008 13:02:56 -0700, "Kalen Delaney"

Post by Kalen Delaney
I probably should have read the whole original thread before jumping in
here...
This is a strange one, no doubt about it!

A few bits if important information surfaced in another thread, so
even reading the entire thing might not have made the sequence of
events clear. That thread was titled SQL Disaster Recovery Query and
started Sept 21.

Roy Harvey
Beacon Falls, CT

hesaguy

2011-05-24 14:44:30 UTC

Sammy wrote on 09/21/2008 14:21 ET

Post by Sammy
sp_helpdb does show as multiuser
but running select * from sys.databases where name ='master
shows as SINGLE_USE

Post by Sammy
Hi John, thanks
sp_help says its multiuser but on the GUI for master it has in bracket
(Single User
so looks like it is multi-user just the gui dispalying incorrectl
name,db_size,owner,dbid,created,status,compatibility_leve
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE, Updateability=READ_WRITE
UserAccess=MULTI_USER, Recovery=SIMPLE, Versiona1
Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics
IsAutoUpdateStatistics,9
The only error message in the log I receive is below. apart frrom tha
everything looks like its working fine
Login failed for user 'TitanSQLservice'. [CLIENT: <local machine>
Error: 18456, Severity: 14, State: 16
'TitanSQLservice' is the servce account that runs sql and sql agent it ha
sa rights and local admi

"Sammy" wrote i

messag

news

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had ful
permissions have added the Sql account that runs the service bu

tha

Post by Sammy
accoun
is currently an admin so that does not look like the issue

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limite

permissions o

Post by Tibor Karaszi
the master mfd and/or ld
files? that should be seen in the errorlog (as already suggeste

by Ro

Post by Tibor Karaszi
but you didn't reply t
that)
Tibor Karaszi, SQL Server MV
http://www.karaszi.com/sqlserver/default.as
http://sqlblog.com/blogs/tibor_karasz
"Roy Harvey (SQL Server MVP)

wrote in messag

Post by Tibor Karaszi
news

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Samm

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, bu

already trie

Post by Roy Harvey (SQL Server MVP)

Post by Sammy
th
alter database command and I get error can't chang

master to etc

Post by Roy Harvey (SQL Server MVP)
Well you have a situation beyond my experience. Perhap

someone els

Post by Roy Harvey (SQL Server MVP)
will join the discussion
Roy Harve
Beacon Falls, C

H
What does sp_helpdb 'master' return
Joh

I had the same problem which clearly is some kind of bug i cant see where th
DatabasePropertyEx('master','UserAccess') inside the SP takes its data from
Couldn't find it somewhere like Registry nor in any table...

Anyway, i solved this by backup of master and restoring it, using the sam
instance, as new_master, service shutdown, moved old master files to anothe
folder and rename new_master.mdf -> master.mdf and new_master.ldf -
mastlog.ldf
Service restart and all, at least looks, normal
Problem cause may be something that Microsoft should look into.

g***@gmail.com

2015-03-23 07:51:08 UTC

Post by Sammy
sp_helpdb does show as multiuser,
but running select * from sys.databases where name ='master'
shows as SINGLE_USER

Post by Sammy
Hi John, thanks,
sp_help says its multiuser but on the GUI for master it has in brackets
(Single User)
so looks like it is multi-user just the gui dispalying incorrectly
name,db_size,owner,dbid,created,status,compatibility_level
master,5.75 MB,sa,1,Apr 8 2003,Status=ONLINE, Updateability=READ_WRITE,
UserAccess=MULTI_USER, Recovery=SIMPLE, Versiona1,
Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics,
IsAutoUpdateStatistics,90
The only error message in the log I receive is below. apart frrom that
everything looks like its working fine.
Login failed for user 'TitanSQLservice'. [CLIENT: <local machine>]
Error: 18456, Severity: 14, State: 16.
'TitanSQLservice' is the servce account that runs sql and sql agent it has
sa rights and local admin

"Sammy" wrote in

message

Post by Sammy
Oh I missed that I checked the actual mdf system and admin had full
permissions have added the Sql account that runs the service but

that

Post by Sammy
account
is currently an admin so that does not look like the issue .

Post by Tibor Karaszi
Perhaps the SQL Server service account has some limited

permissions on

Post by Tibor Karaszi
the master mfd and/or ldf
files? that should be seen in the errorlog (as already suggested

by Roy

Post by Tibor Karaszi
but you didn't reply to
that).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Roy Harvey (SQL Server MVP)"

wrote in message

Post by Roy Harvey (SQL Server MVP)
On Thu, 18 Sep 2008 13:29:01 -0700, Sammy

Post by Sammy
Thanks Ron, I'll check the DATABASEPROPERTYEX out, but

already tried

Post by Roy Harvey (SQL Server MVP)

Post by Sammy
the
alter database command and I get error can't change

master to etc.

Post by Roy Harvey (SQL Server MVP)
Well you have a situation beyond my experience. Perhaps

someone else

Post by Roy Harvey (SQL Server MVP)
will join the discussion.
Roy Harvey
Beacon Falls, CT

Hi
What does sp_helpdb 'master' return?
John

I had the same problem which clearly is some kind of bug i cant see where the
DatabasePropertyEx('master','UserAccess') inside the SP takes its data from.
Couldn't find it somewhere like Registry nor in any table....
Anyway, i solved this by backup of master and restoring it, using the same
instance, as new_master, service shutdown, moved old master files to another
folder and rename new_master.mdf -> master.mdf and new_master.ldf ->
mastlog.ldf.
Service restart and all, at least looks, normal.
Problem cause may be something that Microsoft should look into.

Hesaguy, thanks, your solution happened to be the fastest. It helped.

s***@sealedair.com

2016-06-29 16:13:48 UTC