Nana Hemaa
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
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
Please try this -
USE [MASTER]
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\tempdb.mdf')
GO
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"
when I type
SQLCMD –S localhost –E I get "cannot generate SSPI context"
please follow this support note from MS about that error:
https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message
https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message
ASKER
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
very likely you need to update the registry startup parameters.
please post the errorlog file of sql server
ASKER
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?
ASKER
The service starts and stops on its own. it does not stay started
SQl service will not start -- no tempdbSQL 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?
ASKER
It is looking for temp log files in sql log
ASKER
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.
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).
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).
ASKER
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 helpsDon't bother because it won't work.
Check also in the Windows Event Viewer for errors.
ASKER
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.
Only SQL Server engine is able to do that.
ASKER
--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
USE [MASTER]
GO
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\tempdb.mdf')
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
actually it will with sqlcmd/osql
http://www.xtivia.com/start-sql-server-lost-tempdb-data-files/
http://www.xtivia.com/start-sql-server-lost-tempdb-data-files/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad that you are able to start the SQL Services.
ASKER
Thanks all for your help
How did you create the log file?
what I see is a GO missing after the "use master", which actually is not needed unless your login would point to another db