By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,078 Members | 1,846 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,078 IT Pros & Developers. It's quick & easy.

SQL2005: Running TSQL commands in single-user mode

P: n/a
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
Dec 20 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Dec 20 '07 #2

P: n/a
Plamen Ratchev (Pl****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 20 '07 #3

P: n/a
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Plamen Ratchev (Pl****@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

Dec 21 '07 #4

P: n/a
Mike (hu***@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 21 '07 #5

P: n/a
On Dec 21, 10:29*pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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.
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=master, 3=model and 4=msdb).
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...)

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

Mike.
Dec 27 '07 #6

P: n/a
Mike (hu***@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 27 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.