question

narendba avatar image
narendba asked

If tempdb reached(Data File or Logfile) to 100% drive space what will happen?

In my environment all system database files are located in same drive and suddenly tempdb increased to 98% and cleared automatically but if tempdb increased huge and reached to drive space what will happen If the datafile or logfile are reached to full drive space then Is sql server reached restart automatically?
tempdbfiles
10 |1200

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

Tim avatar image
Tim answered
If TempDB reaches 100% in growth for the transaction log, the transaction will fail and be rolled back, thus freeing up the space in the log file for additional transactions. If the data file grows and fills up the available free space then there will not be any available 'work space' for SQL server to operate properly.
10 |1200

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

ramesh 1 avatar image
ramesh 1 answered
you sql server stops responding .
2 comments
10 |1200

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

SQL Server needs tempdb to operate. It does a world of many things within tempdb such as sorts, temp tables from code/procedures and a slew of other things. If the tempdb doesn't have the room to grow, then it can't support the needs of the sql server appropriately. I don't believe that SQL Server is restarted automatically for this particular issue. Proper sizing & placement of tempdb is always a good thing. http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx You can move tempdb to a larger volume if needed, which would require a sql server restart. If you get this error, you can restart the SQL Server and tempdb should return back to it's preset size. Hope this helps!
1 Like 1 ·
Can I have exact reason why sql server stops responding? And Is sql server restarted automatically? If it is not responding how we can overcome from it?
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.