SQL2005: Running TSQL commands in single-user mode

I'm having trouble running TSQL commands when SQL2005 is in single-
user mode.

I've restarted SQL with -m, -c, -T3608 set in the startup options.

I can get into Config manager OK, but as soon as I try to start a New
Query it complains that the DB is in single-user mode and there's
already someone connected to the DB.

Is there something I need to turn off? If not, how am I supposed to
run queries in single-user mode? What I want to do is move the model
and msdb databases but I can't run the commands to do this until I get
past this problem.

Thanks for any assistance you can give on this.

Mike

Re: SQL2005: Running TSQL commands in single-user mode

Hi Mike,

A few things to try:

1). Make sure to stop the SQL Server Agent service before connecting to the
instance. If you have it running it uses the connection blocking your
access.
2). Use sp_who to find the session ID of the active connection and use KILL
to terminate it.
3). If all fails you can open a dedicated administrator connection:
http://msdn2.microsoft.com/en-us/library/ms189595.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: SQL2005: Running TSQL commands in single-user mode

Plamen Ratchev (Plamen@SQLStudio.com) writes:
> 2). Use sp_who to find the session ID of the active connection and use
> KILL to terminate it.

Kind of difficult when Mike can't connect...

> 3). If all fails you can open a dedicated administrator connection:
> http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Using the DAC definitely seems to be the best bet in this case. Mike
already has the server in single-user mode.

I am glad to see that you finally got the startup options working, Mike.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL2005: Running TSQL commands in single-user mode

"Erland Sommarskog" wrote in message
news:Xns9A0CEDE5828E5Yazorman@127.0.0.1...
> Plamen Ratchev (Plamen@SQLStudio.com) writes:
>> 2). Use sp_who to find the session ID of the active connection and use
>> KILL to terminate it.
>
> Kind of difficult when Mike can't connect...
>

You are correct the way I wrote it... :) I meant more along the lines of
using DAC to run sp_who/kill and then use regular connection once available.

Plamen Ratchev
http://www.SQLStudio.com

Re: SQL2005: Running TSQL commands in single-user mode

On Dec 20, 10:15=A0pm, Erland Sommarskog wrote:

> I am glad to see that you finally got the startup options working, Mike.

I've got even further now!

I used Management Studio to connect; but when you go in you mustn't
connect straight away as the object browser maintains a SQL connection
(no idea why it needs to). Then you can just open "Database Engine
Query" and bob's your uncle.

I've moved the msdb and model databases now, and successfully. But I
have a further problem.

I inadvertently moved tempdb and templog while the SQL service wasn't
running. I've now moved them back from whence they came, and SQL
starts fine with the -m;-c;-T3608 flags set. But when I take these
options out, SQL refuses to start with the following error in
ERRORLOG:

2007-12-21 11:49:38.88 Server Microsoft SQL Server 2005 -
9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2007-12-21 11:49:38.88 Server (c) 2005 Microsoft Corporation.
2007-12-21 11:49:38.88 Server All rights reserved.
2007-12-21 11:49:38.88 Server Server process ID is 4712.
2007-12-21 11:49:38.88 Server Authentication mode is MIXED.
2007-12-21 11:49:38.88 Server Logging SQL Server messages in file
'D:\MSSQL\LOG\ERRORLOG'.
2007-12-21 11:49:38.88 Server This instance of SQL Server last
reported using a process ID of 4512 at 21/12/2007 11:43:54 (local)
21/12/2007 11:43:54 (UTC). This is an informational message only; no
user action is required.
2007-12-21 11:49:38.88 Server Registry startup parameters:
2007-12-21 11:49:38.88 Server -d D:\MSSQL\DATA\master.mdf
2007-12-21 11:49:38.88 Server -e D:\MSSQL\LOG\ERRORLOG
2007-12-21 11:49:38.88 Server -l C:\MSSQL\Logs\mastlog.ldf
2007-12-21 11:49:38.88 Server SQL Server is starting at normal
priority base (=3D7). This is an informational message only. No user
action is required.
2007-12-21 11:49:38.88 Server Detected 8 CPUs. This is an
informational message; no user action is required.
2007-12-21 11:49:39.04 Server Set AWE Enabled to 1 in the
configuration parameters to allow use of more memory.
2007-12-21 11:49:39.28 Server Using dynamic lock allocation.
Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per
node. This is an informational message only. No user action is
required.
2007-12-21 11:49:39.29 Server Attempting to initialize Microsoft
Distributed Transaction Coordinator (MS DTC). This is an informational
message only. No user action is required.
2007-12-21 11:49:40.32 Server Attempting to recover in-doubt
distributed transactions involving Microsoft Distributed Transaction
Coordinator (MS DTC). This is an informational message only. No user
action is required.
2007-12-21 11:49:40.32 Server Database mirroring has been enabled
on this instance of SQL Server.
2007-12-21 11:49:40.32 spid5s Starting up database 'master'.
2007-12-21 11:49:40.34 spid5s SQL Trace ID 1 was started by login
"sa".
2007-12-21 11:49:40.34 spid5s Starting up database
'mssqlsystemresource'.
2007-12-21 11:49:40.34 spid5s The resource database build version
is 9.00.3042. This is an informational message only. No user action is
required.
2007-12-21 11:49:40.35 spid5s Server name is 'MYSERVER
\MYINSTANCE'. This is an informational message only. No user action is
required.
2007-12-21 11:49:40.35 spid9s Starting up database 'model'.
2007-12-21 11:49:40.37 spid9s Clearing tempdb database.
2007-12-21 11:49:40.37 spid9s Error: 913, Severity: 16, State: 8.
2007-12-21 11:49:40.37 spid9s Could not find database ID 2.
Database may not be activated yet or may be in transition. Reissue the
query once the database is available. If you do not think this error
is due to a database that is transitioning its state and this error
continues to occur, contact your primary support provider. Please have
available for review the Microsoft SQL Server error log and any
additional information relevant to the circumstances when the error
occurred.
2007-12-21 11:49:40.37 spid9s Could not create tempdb. You may
not have enough disk space available. Free additional disk space by
deleting other files on the tempdb drive and then restart SQL Server.
Check for additional errors in the event log that may indicate why the
tempdb files could not be initialized.
2007-12-21 11:49:40.37 spid9s SQL Trace was stopped due to server
shutdown. Trace ID =3D '1'. This is an informational message only; no
user action is required.

The event log contains the following entry:

"Could not find database ID 2. Database may not be activated yet or
may be in transition. Reissue the query once the database is
available. If you do not think this error is due to a database that is
transitioning its state and this error continues to occur, contact
your primary support provider. Please have available for review the
Microsoft SQL Server error log and any additional information relevant
to the circumstances when the error occurred.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp."


I seem to be lurching from one obscure problem to another. I don't
want my database to be single-user!!

Thanks for the help so far, and thanks for any help in solving this
new conundrum.

Mike.

Re: SQL2005: Running TSQL commands in single-user mode

Mike (huttm@yahoo.co.uk) writes:
> I used Management Studio to connect; but when you go in you mustn't
> connect straight away as the object browser maintains a SQL connection
> (no idea why it needs to).

Well, if it did not have that connection, how do you think it would
be able to display the contents of databases etc?

If you don't want the Object Explorer on startup, you can change that
under Tools->Option, on the General page.

> 2007-12-21 11:49:40.37 spid9s Could not create tempdb. You may
> not have enough disk space available. Free additional disk space by
> deleting other files on the tempdb drive and then restart SQL Server.
> Check for additional errors in the event log that may indicate why the
> tempdb files could not be initialized.


That doesn't look good. When in single-user mode, have you verified
that the path for tempdb is good?

Also, if you moved the files to the new place, make sure that they are
not read-only for the service account for SQL Server? You could try
deleting the files - SQL Server will recreate them anyway.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL2005: Running TSQL commands in single-user mode

On Dec 21, 10:29=A0pm, Erland Sommarskog wrote:

> > 2007-12-21 11:49:40.37 spid9s =A0 =A0 =A0Could not create tempdb. You ma=
y
> > not have enough disk space available. Free additional disk space by
> > deleting other files on the tempdb drive and then restart SQL Server.
> > Check for additional errors in the event log that may indicate why the
> > tempdb files could not be initialized.
>
> That doesn't look good. When in single-user mode, have you verified
> that the path for tempdb is good?
>
> Also, if you moved the files to the new place, make sure that they are
> not read-only for the service account for SQL Server? You could try
> deleting the files - SQL Server will recreate them anyway.

Thanks for the reply. Hope you had an excellent Christmas too!

I've had a look in the innards of SQL.

There is no entry for tempdb is master.dbo.sysdatabases. (I have
1=3Dmaster, 3=3Dmodel and 4=3Dmsdb).
There are 2 entries in master.dbo.master_files:

SELECT name, physical_name
FROM sys.master_files
WHERE database_id =3D DB_ID('tempdb');

gives me

tempdev D:\MSSQL\Data\Tempdb.mdf
templog C:\MSSQL\Logs\Templog.ldf

Both the directories exist, and everyone has complete access to them
(just to be sure...)

I suspect I'm scuppered because the entry for tempdb in sysdatabases
has gone AWOL. Am I looking at a restore/reinstall?

Mike.

Re: SQL2005: Running TSQL commands in single-user mode

Mike (huttm@yahoo.co.uk) writes:
> I've had a look in the innards of SQL.
>
> There is no entry for tempdb is master.dbo.sysdatabases. (I have
> 1=master, 3=model and 4=msdb).

That's spooky.

> There are 2 entries in master.dbo.master_files:
>
> SELECT name, physical_name
> FROM sys.master_files
> WHERE database_id = DB_ID('tempdb');
>
> gives me
>
> tempdev D:\MSSQL\Data\Tempdb.mdf
> templog C:\MSSQL\Logs\Templog.ldf
>
> Both the directories exist, and everyone has complete access to them
> (just to be sure...)

And there is space on the disk?

> I suspect I'm scuppered because the entry for tempdb in sysdatabases
> has gone AWOL. Am I looking at a restore/reinstall?

Since I don't have access to your system, it's difficult to try
things. So the easiest way out is probably to detach all user databases
if you have not done so already, and then reinstall. If you have lots
of logins or other server-level objects, that can be a bit painful.
The alternative would be to open a case with PSS.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx