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 (https://blog.sqlauthority.com)
114 Comments. Leave new
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
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
delete all the temp files
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.