How to run a query in single user mode ?
-
First time post here.
Situation
I have a SQL 2000 TEST server here with a tempdb that is very large. It has 2 data device files (tempdev and tempdev_01), one that is 9537 MB and the other is 24402 MB. Both devices are set to unrestricted file growth and to automatically grow the file. When I check the properties of the tempdb via Enterprise Manager, it shows all but 2 MB is free. The drive with the 9537 MB file is nearly out of space.
What have I tried:
I ran "backup log tempdb with truncate only" and then ran dbcc shrinkfile for the two data device files. No change. I stopped and start the SQL services and saw no change. I then researched and saw "dbcc shrinkdatabase" and "alter database" as options, but I needed to be in single user mode. So, I stopped the SQL services and opened a command prompt and ran "sqlservr.exe -m" in the binn directory. SQL started fine, but I wasn't sure how to run the dbcc shrinkdatabase or alter table commands. I tried opening Query Analyzer, connecting as sa, and I got an error saying it was in single user mode - duh.
When I ran "sqlservr.exe -m", it didn't return me to a command prompt ">". If I hit Ctrl-C, it asked if I wanted to stop the SQL server. I said yes only because I knew I needed to eventually stop it in single user mode and then restart it in multi-user mode, and I didn't know how to get back to that option if I said no.
So, do I need start SQL in single user mode and then open a second command prompt window and run sqlcmd ? Or do use Ctrl-C and say no, run sqlcmd from there and then later stop SQL ?
Any help on exact syntax would be helpful.
Thanks, JT
-
http://support.microsoft.com/kb/307487
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I had read that knowledgebase article. Can you clarify ? I have tried starting SQL with "sqlservr.exe -c -f" and still could not open QA.
-
Do you have EM connected?
Because it's single user connection you cannot have both applications connected.
I tried this on my server and it worked.
_____________
Code for TallyGenerator -
No, EM is not connected. I am wondering if a service related to a web based application is reconnecting to the database faster than I can open QA. I am familiar with taking a database offline and then ringing it back online before doing a restore. That way, if a service account was connected, we could perform the restore. How I perform the shrink in this case is escaping me.
-
-If you want to be sure about the hypothesis that another user is faster than your connect with QA, change the auditlevel to ALL connections (serverproperties\security). This way all login operations are logged.
- Don't start sqlagent.
- I've done this procedure a number of times without any issues.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply