September 2, 2008 at 2:05 pm
I'm running sql server 2005 x64 enterprise edition and my tempdb file is in full recovery model.
I have tried the following;
exec sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
update sysdatabases
set status = 8 where dbid = 2 --(tempdb)
At this point I get an error telling me ad-hoc queries are not allowed.
Anyone have any suggestions how to get my tempdb into simple recovery mode.
Thanks!
September 2, 2008 at 2:10 pm
ALTER DATABASE tempdb SET RECOVERY SIMPLE;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 2, 2008 at 2:16 pm
thanks, for the response.
i had already tried that and get an error that recovery model of tempdb cannot be changed.
September 2, 2008 at 2:55 pm
Sue,
Please put server in single user mode and use DAC to update the system tables in 2005 as mentioned by someone here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89594&SiteID=1
The server also has to be started in single-user mode, for the updates to be allowed (using the -m flag). By itself, DAC will allow you to see system tables, but to update them, you also need to have the server started in single-user mode.
HTH
MJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply