question

Naveen Kumar avatar image
Naveen Kumar 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.
sql-server-2008tempdbsql-server-servicestartup-options
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tempdb is arguably one of the most important databases on SQL Server.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.