May 12, 2011 at 7:26 am
I got into an issue where in sql server ( version 2005) gets recycled automatically during peak time. Found that tempdb log was full just before sql service got recycled. Does anyone have idea how tempdb would result in recyle of sql server service.
i have seen such in version 2000 , due to a dbcc pss command(undocumented) sql server could get recycled automatically.
From ms kb article I found this link:
http://support.microsoft.com/kb/937343
but, I could not find any errors w.r.t that mentioned in kb 937343.
any ideas/suggestion in regards to this issues is really appreciated.
May 14, 2011 at 12:52 pm
Have you tried checking the logs?
Try setting the recovery model of tempdb to simple so that the space can automatically be reclaimed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 14, 2011 at 2:37 pm
Welsh Corgi (5/14/2011)
Try setting the recovery model of tempdb to simple so that the space can automatically be reclaimed.
TempDB is always in Simple recovery. Any attempt to change it returns an error
Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2011 at 3:07 pm
How have you determined that this is happening automatically? Do the SQL Server Logs show that SQL Server was shut down and then restarted? Is there any indication in the event logs that the services were stopped?
SQL Server will not just restart by itself - something (or someone) has to be initiating a shutdown and restart of the service.
Are you sure someone hasn't scheduled a restart of the server every night?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply