February 29, 2008 at 6:58 pm
Sorry to be a pain. I haven't really completed my course with sql server (sql 2005).
A stored procedure does this:
- read and update a few tables in user database of the same server and linked server as well
- in doing so, it creates temporary work tables, variables in the system database tempdb
If this stored procedure crashes due to "log file of tempdb full", of course (I am a support person) I need to clean up all user tables.
But how about the tempdb? Will Sql server do all the clean up for me - I mean deleting the temp tables, removing those log entries in limbo. If not, what does it means if I do not restart sql server?
Thanks a lot
February 29, 2008 at 7:29 pm
You can attempt to run DBCC ShrinkFile('tempdev_log') to recover space in the tempdb log file or you can restart the sql server. If you tempdb log file is full you will not be able to do anything in tempdb without recovering space or expanding the log file. Eventually it will be shrunk by SQL Server, but you can't wait for that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 29, 2008 at 9:02 pm
Jack Corbett (2/29/2008)
You can attempt to run DBCC ShrinkFile('tempdev_log') to recover space in the tempdb log file .................
Thanks for comment. Am I suppose NOT to shrink tempdb while the server is running? Someone mentioned I could quite easily corrupt my database.
February 29, 2008 at 9:08 pm
I would actually run the CheckPoint command and let SQL Server take care of itself. I posted this on your other post about the tempdb problem.
Running DBCC ShrinkFile is going to negatively impact the systems performance, but if you are not running due to a full log file what is the negative? It is either that or restart SQL Server.
You really need to get the developer to fix the root cause, overuse/incorrect use of temp tables, cursors and long running transactions. If the developer can't fix it you need to bring someone in who can.
I am getting tired, it is late here and my tolerance for people unleashing bad code on others has been worn thin.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply