473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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
Dec 20 '07 #1
6 6296
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
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
"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Steve | last post by:
Hi; I have a table with a TEXT datatype. Its a comment field. Right now the users who put in singlequotes are killing the web front end. The programmer responsible is fixing this issue but...
2
by: Murtix Van Basten | last post by:
Hi, I have dumped a very large database from mysql (using mysqldump program) as a raw sql file. The reason was, convert this database to a MSSQL database. Since mysqldump creates the file as raw...
4
by: Andrew Chanter | last post by:
I am developing a SQL database to cover operations that were previously handled in a spreadsheet, and need to create a view or procedure that presents data into a matrix format similar to what the...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
0
by: Steve Dyte | last post by:
I'm trying to script stored procedures/functions and tables in Visual Studio 2005 by dragging the objects from the server explorer to the solution explorer. Problem is, it's generating the...
3
by: Peter Newman | last post by:
In running vb.net 2003 and am trying to run a stored procedure Dim InputString() As String InputString = Split(InputParm, ";") ' set the query commands STR_SQLCOMMAND.CommandText =...
5
by: Mike | last post by:
I'm having trouble accessing SQL2005 Standard Edition as a second instance of SQL Server where the first instance is SQL 2000 Enterprise Edition. I installed SQL 2005 as a named instance...
2
by: rsyring | last post by:
I have a TSQL script that restores a production database to my local development machine. Every time I do a restore, I have to go into Enterprise Manager and set the recover model to simple to...
8
by: David Lozzi | last post by:
I'm fairly new to ASP.Net 2.0 SQLDatasource objects. It defaults using TSQL statments for the SELECT, INSERT, UPDATE, DELETE commands, which is great and it works. However, I've always been taught...
3
by: Tawfiq | last post by:
Hi, We have shifted one of our data processing to a new instance of SQL 2005. For this a new DB was created in SQL2005 and it is not an upgrade of SQL200 DB. The data processing application...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.