Is moving or resizing templog without connecting to the database engine possible?
-
I did something silly and I was hoping I might find help here to undo it:
I moved templog to another disk using the following code:
ALTER DATABASE TempDB MODIFY FILE(NAME = templog, FILENAME = 'e:templog.ldf')
GO
When I issued that instruction it I thought that the log was 45mb in size but actually it was 46gb - oops! The e: disk which I tried to move templog to doesn't have anything close to 46gb free so templog cannot be created on it. I discovered this when I restarted and got this message at the launch of SSMS when I try to connect to my DB:
Cannot connect to (local).
Additional information: A network-related or instance-specific error occurred while establishing a connection to SQL server. The Server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error:40 - Could not open a connection to SQL server) (Microsoft SQL Server, Error: 2)
Is there a way to get around this either by:
- Resizing templog without connecting to the database?
- Changing where the database expects to find templog without connecting to it?
- Doing something else? (ideally other than freeing up 46gb on the e: and manually moving the old templog file to there)
-
No, you have to connect to SQL to change the log settings. There's a traceflag that lets SQL just start master up, no other database.
Start SQL (from the command line) with -m and -T3608. With those 2 you'll be able to connect (one connection only) and run the alter database commands to fix tempDB. I recommend using SQLCMD as management studio uses multiple connections and hence you may be unable to get a query window to connect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi GilaMonster,
Many thanks for the advice. I tried to do the following, not sure that I'm doing what you suggested right:
- Start / Run / cmd
- Typed the following and hit enter: sqlcmd -m -t3608
That gave me the error
Sqlcmd: '-m': Missing argument. Enter '-?' for help.
Then I tried: sqlcmd -m-1 -t3608
and I got this error:
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL server [2]....
A network-related or instance-specific error occurred while establishing a connection to SQL server. Server is not found or not accessible. CHeck if instance name is correct and if SQL Server is configured to allow remote connections.
Am I doing something wrong here? Apologies - haven't used SQLCMD before.
-
the command line parameters are used to start the sqlservr.exe for the particular instance you are fixing
Check services.msc for the location of sqlservr.exe for your chosen instance.
Open a cmd prompt in the directory you see in services.msc and use the command line to start sql server (stop the service first if it is running).
once the instance is running, open another cmd prompt (leave the first one running) and use sqlcmd to connect to the instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
caspersql (10/22/2011)
Hi GilaMonster,Many thanks for the advice. I tried to do the following, not sure that I'm doing what you suggested right:
- Start / Run / cmd
- Typed the following and hit enter: sqlcmd -m -t3608
That gave me the error
Sqlcmd: '-m': Missing argument. Enter '-?' for help.
You misread my post. I said start SQL Server with the -m and -T3608 parameters and then use SQLCMD to connect to SQL Server. Two steps, not one.
First start SQL Server from the command line with -m -t3608 (sqlservr.exe). Then, once SQL Server has started up, use SQLCMD to connect to the running SQL Server and fix the tempDB log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Okay thanks GilaMonster and Perry, I think I'm getting closer to understanding what you mean. However there's one bit I still don't know how to do, any assistance would be much appreciated.
I started SQL server using the command prompt with the -m and -t3608 parameters as you suggested.
I'm having trouble with sqlcmd though. When I run cmd, then try to connect to the instance so that I can run the ALTER command, it won't let me type in my password.
In the cmd window, I type
sqlcmd -U sa -S (local)\MSSQLServerand then hit enter.My reading of this page is that I should then be able to type in my password to connect to the instance: http://msdn.microsoft.com/en-us/library/ms180944.aspx
A prompt for password appears, however the cmd window no longer allows me to type in any text. Hitting keys on my keyboard does not have any effect on the cmd window. Is there something I should be doing differently here so that I can enter my password (and the subsequent Alter database command)?
This is what I am seeing at the point where typing doesn't do anything in the cmd window anymore:
-
I've never had any trouble typing into a command window. You can specify the password on the command line -P <password> or use -E for trusted connection if your windows account is a sysadmin or member of local administrators
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
caspersql (10/23/2011)
A prompt for password appears, however the cmd window no longer allows me to type in any text. Hitting keys on my keyboard does not have any effect on the cmd window.Actually the password prompt in SQLCMD will not let you see what you are typing. That's by design and for security reasons. Just type in your password and press ENTER. If ENTER does nothing than there must be something wrong with your command prompt window or keyboard.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA -
Okay thanks thanks Gilamonster and Codebyo. Didn't realise that password was hidden by SQLCMD, that is what was happening when I thought it wasn't accepting my keyboard input.
Now however, whether I use the -P parameter and enter the password in the initial instruction or whether I do not use the -P parameter and enter the password when SQLCMD prompts for it, I get the following message:
While trying to figure out why this message is appearing, I discovered the following:
In the window where I issued the sqlservr.exe -m -T3608 command, it only waits for a connection for a short period before continuing and doing other things, among which is an attempt to create tempdb and templog, which fails because of insufficient disk space. Here is the sequence of events:
1. I enter the sqlserver.exe -m -T3608 command:
2. As expected, the server says it is waiting for a single connection. At this point, in services.msc the status of this instance changes to 'Started'
3. After a few seconds, the server tries to create TempDB, fails, and then stops waiting for a connection. At this point, in services.msc the status of this instance changes from 'Started' to not saying anything (which I assume means that it has stopped).
-
Stop Reporting Services. Something (I assume SSRS) is grabbing the sole connection to the server, connecting to a user database and forcing SQL to start up that user database and tempDB, which of course fails.
Is your windows account a member of the local administrator group or the sysadmin role in SQL Server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gilamonster - yes stopping SSRS and all the other services (eg SQLServer Agent) allowed me to maintain the open connection in the cmd window - thanks so much!
So that solved that problem. When I tried to connect I was still getting the 'Connection string is not valid [87]' error message though. So I made the following change to my sqlcmd string:
Instead of writing:
sqlcmd -U sa -S (local)\MSSQLServerPer the documentation on various ways to connect here: http://msdn.microsoft.com/en-us/library/ms188247.aspx I typed:
sqlcmd -U sa -S 127.0.0.1,14341434 was the port number which the window that I had issued the sqlservr.exe -m -T3608 command in said was open.
That allowed me to connect and enter my Alter database command which was successful (see screendum below) and I can now connect again in SSMS and use my database as usual. Thanks so much for the help folks - I'm back in action 🙂
-
If you're having troubles with other external connections you may use the following to start sql server single user mode and only accept a connection from SQLCMD
sqlservr -c -m "SQLCMD" -T 3608
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply