asked
Sql services not getting Restarted
Hi, In sqlserver to shrink the tempdb I issued a command, alter database tempdb modifyfile (name = 'tempdev',size = 0) commands completed successfully. alter database tempdb modifyfile (name = 'templog',size = 0) commands completed successfully. after doing this I opened command prompt and issued net stop mssqlserver services have been stopped successfully. I have issued net start mssqlserver after this it is trowing error a service specific error occurred 1814 I am not able to start the services pls suggest on this. waiting for the response........ Thanks in advance.
The error is related to the fact, that SQL Server instance cannot create a TEMP DB. It seems to be related to the fact, that you have set the size of TEMP DB to 0. So it cannot create a TEMP DB of size 0. Why you have set the initial size of temp db to 0? You can try to fix the issue by starting the sql server from command line with `-c -f` startup options. Then try to connect to the server, specify a correct temp db size. After that stop the service and start it normally. Didn't tested this scenario, so maybe will not work for this kind of issue.
Hi pavel, Actucally the tempDB size has been grown drastically so I issued that command to shrink the tempDB. Do let me know is it the problem with mentioning size as 0 or not. Please guide me how to use the options through command prompt which you mentioned. I am using SQL server 2008. Many Thanks.
If you intended to recycle the services anyway then that would have been the easiest way to shrink tempdb. It is always recreated at server start up to its default size - which of course you have set to 0 unfortunately.
If tempdb has grown this much, it's because of operations in your system. You're going to have to address those operations or tempdb will simply grow again and you'll be back in the same situation.
tempdb is arguably one of the most important databases on SQL Server.
