Very frequently I use following command prompt script to start and stop default instance of SQL Server. Our network admin loves this commands as this is very easy.
Click Start >> Run >> type cmd to start command prompt.
Start default instance of SQL Server
net start mssqlserver
Stop default instance of SQL Server
net stop mssqlserver
Start and Stop default instance of SQL Server.
You can create batch file to execute both the commands together.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
thanks for the useful trip,
is it useful for sql on the network?its mean how can stop and start my sql on the other system?
how can i upgrade the version of sql server 2005 through command prompt. can u help me out
I love your site. Great info (I just read your article on @@IDENTITY vs. SCOPE_IDENTITY(), and it really gave me a good understanding of the differences).
However, you get the strangest comments on this site. :-P This post’s comments are definitely par for that particular course.
Pinal,
In interview every time ppl ask about the performance tunning.. can you tell me about it.. I m really screwed with the examples on websites.
Please give me real time examples as well some sites or how can I check on the SQL Server 2000
Great article!
People say you don’t need to restart SQL Server 2005, but I often need to do this to clear out the tempdb, especially before a backup.
Does anyone know how I can shrink the tempdb without restarting the SQL Service?
Thanks!
Jason
http://www.lonestarfinancing.com
One of the best sites I have ever seen for the same.
Keep it up man,
-Nisheeth Pandya
how to start or stop services SQL server by command prompt from client (client can stop or start service in server)
How to start dependent services (SQL Server Agent) from command prompt.Once MSSQLSERVER stopped Agent also get stopped .. how to start it…
i was looking for this! thanks!
Hello
Assuming that memory allocation in sql server is mostly related to pages cached. Normally in my company it takes around 6 to 10 hours to allocate the max amount of memory we have set in the settings.
Is there any performance issue associated to restarting SQL server service ? i.e. the application connecting to SQLserver will behave better or worst after restarting SQL server service ?
thanks,
dbaexp2008
i have increased the initial size of tempdb (27gb)
now i have to change it back to original size (2mb)
tried
“use tempdb dbcc shrinkfile (’tempdev’,2,TRUNCATEONLY)”
its working but the problem is as i restart the server angain it goes back to the same size(27gb)
when i try to restart server in command prompt i get following error
“”C:\>net stop mssqlserver
System error 1060 has occurred.
The specified service does not exist as an installed service.”"
could any one help me to resolve .
please send your solution to raghujchavan@gmail.com
thanks
raghu
raghujchavan@gmail.com
nice work, bro
another great tip! thanks again
Hey Thanks,
I’m a non-sql guy even though I’m inspired to learn as much as i can. My product SMS relies heavily on sql. Thanks for the stop and restart tip.
@Papillon ( No 8)
START :
To start named instance of SQL Server and SQL Server Agent run this command from command prompt:
net start “SQL Server (prod2005)”
net start “SQL Server Agent (prod2005)”
– Remember SQL Server Agent can only be start after SQL Server has started. hence follow the same order mentioned in the code.
Here “prod2005″ is the named instance, this name does not includes my machine name.
Complete name of the instance is IMRAN\prod2005 , But here we dont need complete name we just need instance name.
STOP:
Similarly to stop SQL Server and SQL Server Agent run this command from cmd prompt.
net stop “SQL Server Agent (prod2005)”
net stop “SQL Server (prod2005)”
– Remember to Stop SQL Server you first need to stop SQL Server Agent, so first stop SQL Server Agent and then Stop SQL Server, follow the same order as mentioned in the code
The same concept applies here as well, you will not use the complete name of the instance ( imran\prod2005) , but you will just use instance name with out machine name (Prod20005).
What I did, I wrote these two scripts to start and to stop SQL Server and Agent in two different bat files. When ever I want to start or stop these two services, I just double click bat files, and thats it… it does all the work.
Hope this helps,
Imran.
Can I restart one perticular instance of a database? I dont want restart whole SqlServer.
Chetan,
Do you mean, you want to take a DB offline?
Command For SQL Server Stop From Client Computer
i’m newly opened the sql server 2005,how to start the query in what way,i know about query,but what way ,hw to open the sql server 2005,pls immediately reply to me,see already i installed the sql server 2005
If you are using MS Cluster to run MSSQL2005 you should
use the cluster commands to script a stop and start of MSSQLServer. Otherwise the cluster service will think that your service has failed.
I created a cmd/bat file that I schedule to run weekly.
cluster /cluster:CLUSTERNAME group “Group 0″ /offline
cluster /cluster:CLUSTERNAME group “Group 0″ /online
This stops and starts all depdendent services for MSSQL SQL AGENT etc. in the cluster Group 0 in the correct order and keeps the services running on your primary server.
I have SQL server installed on my person pc. I had stopped the server sometime back . Now when I open the Enterprise manager and starting the server, it is giving failed error. I have tried to start it many times but it says connection failed.
I have tried to give the command(net start mssqlserver) in run but it says service could not be started.
I also tried one more way but that also didn’t work, Here is that :
Create a batch file called RestartSQL.BAT with the following commands in it, and save it on the C:\ drive:
@ECHO OFF
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT
Please help me how can we start this ?
Amit.
@Amit.
There could be many many reasons for this to happen.
I will list very important solution.
1. Go to Start
2. Go to Run
3. Type Services.msc
4(a). Search for SQL Server service ( if you have SQL Server 2005), double click this service.
4(b). Search for MSSQLServer service ( if you have SQL Server 2000), double click this service.
5. Check for Startup Type , this should not be disabled. This can be Manual or Automatic. If it is disabled, set it to manual, click apply, click Start, if the service starts, thats good, there is no more problem if not then proceed to step 6.
6. On the top of the same box, there is a LogOn Tab, click that tab, click local system, click apply click ok. If Service starts, then you have problem with the account under which Service is running, you should use a valid username and valid password.
Browse on internet for more information,
Should be straight forward.
Hope this helps,
IM.
Thank You Very much
Special thanks to Imran Mohammed Mine is Named Instance
[...] SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt [...]
Hi Dave,
I am in need to know one thing that is ther any way for intimation whenever any MSSQl Services stopped some one or server was restarted unexpected and MSSQL Server agent is not started as like when any SQL agent job failed it intimated to defined group by the help of operator.
Do you have any SQL script for above requirement, Please guide me or send me on email ID.
Please do not suggest any monitoring tools because of we have already configuring. SQL script is Just for backup of monitoring tool is failed.
Thanks in advance…
Anurag Tripathi
Don’t bother running on two lines with this, just do:
net stop mssqlserver && net start mssqlserver
I NEED TO REMOVE SQL SERVER AND INSTALL NEW SQL SERVER BUT SQL SERVER IS NOT REMOVE FROM MY COMPUTER
FATAL ERROR IS COMING.
for starting and stopping remote SQL server, we can use the command -
SC \\servername start mssqlserver
SC \\servername stop mssqlserver
For MSSQL Server agent , use
SC \\servername start sqlserveragent
SC \\servername stop sqlserveragent