July 18, 2005 at 2:51 am
We sometimes get the following messages in the log of a SQL2K server:
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
The tempdb and log files are set to grow automatically by 10% and there is 60GB of free space on the drive. The db + log is only 5GB in size. We normally ignore these messages thinking that the message was generated while de file was in the process of expanding. Recently I noticed that the file properties for the db and log file were changed to grow ‘In megabytes: 1’. Last week I changed it back to ‘By percent: 10’, but this morning I again got the tempdb is full message and the file properties were changed back to ‘In megabytes: 1’.
Has any body experieced this and was this done automatically by SQLserver?
July 18, 2005 at 10:36 am
It sounds like:
1. you really don't have permission to change the settings or
2. you have an application that is changing the settings.
-SQLBill
July 19, 2005 at 3:37 am
I am sysadmin and can certainly change the dboptions. I also checked afterwards to make sure they were changed. I also doubt that this financial app changed it, but then apps are made by developers and developers are just human....... But of course, SQLserver is also just an app...
July 19, 2005 at 3:44 am
Every time SQL server is started, tempdb is recreated from the model database. You need to modify the properties of model so that the changes you make will survive a restart of the SQL Server service.
July 19, 2005 at 5:20 am
Server has not been re-rebooted or SQLserver re-started and tempdb settings are good.
July 19, 2005 at 6:26 am
That's not completely true.....while TEMPDB is recreated everytime, it's settings are 'sticky'. My settings for TEMPDB are different than my settings for MODEL, but everytime I reboot my server or restart the SQL services...TEMPDB is recreated with it's last settings.
I have noticed that if I just click in the window for setting MB and it resets the number to 1. For example, if it is currently at 1000 MB and I click in the window, it 'resets' to 1 and I have to retype the full number (1000, 2000, whatever).
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply