Link to home
Start Free TrialLog in
Avatar of Nana Hemaa
Nana HemaaFlag for United States of America

asked on

OSQL to execute sql command

Have to execute below commands @ sql command prompt.   Have to use OSQL cmd (for older version of SQL)
 instead of SQlcmd  (SQLCMD –S localhost –E) any inputs how?
------------------------------------
USE MASTER
3> ALTER DATABASE tempdb MODIFY FILE
4> (NAME = tempdev, FILENAME = 'C:\tempdb.mdf')
5> GO
6> quit
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

and what is the issue?
what I see is a GO missing after the "use master", which actually is not needed unless your login would point to another db
Please try this -

USE [MASTER] 
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\tempdb.mdf')
GO

Open in new window

Avatar of Nana Hemaa

ASKER

I do not get the command prompt to type this command.  
when I type
SQLCMD –S localhost –E  I get  "cannot generate SSPI context"
Help please  ...SQL server is down.  No tempdb....trying to create tempdb from OSQL command prompt
if sql server is down, you cannot connect to it (except with the -A flag eventually)
very likely you need to update the registry startup parameters.
please post the errorlog file of sql server
meant database is down.  I can access server ok..  SQl service will not start -- no tempdb
What error are you getting when you trying to run the service?
The service starts and stops on its own. it does not stay started
SQl service will not start -- no tempdb
SQL Server doesn't need tempdb to start. In fact it recreates tempdb database every time it restarts. Your issue must be any other thing else.
Check the SQL Server error log file for errors.
How is the disk free space?
It is looking for temp log files in sql log
CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
   WARNING: problem activating all tempdb files.  See previous errors.  Restart server with -f to correct the situation.
You can try the -f option as suggested but you should check why the tempdb couldn't be created.
It might be for not enough disk space or the SQL Server service account doesn't have permission in the folder or even the folder doesn't exist (if the latest you just need to recreate the folder).
lots of space., account has admin access, folder exist.  the mdf file is created.  I am going to create the log file to see if that helps
I am going to create the log file to see if that helps
Don't bother because it won't work.
Check also in the Windows Event Viewer for errors.
does the tempdb need a log file?
Yes it does but a log file can't be recreated as you want to do.
Only SQL Server engine is able to do that.
--so all I had to do was issue the commands below and the log file gets created as well with the mdf file?

USE [MASTER]
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\tempdb.mdf')
GO
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forget about those commands. It's useless to spend time on that because if SQL Server can't start it won't be possible to run those commands.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad that you are able to start the SQL Services.
Thanks all for your help
How did you create the log file?