SQL Server Performance Forum – Threads Archive
unrestricted templog
Env.: SQL2K5 on Win2003I checked the templog to grow unrestricted but I see it is set to 2,097,152 MB. The problem occured when the tempdb log file reached to 2GB and I got the following error:
Msg 9002, Level 17, State 4, Line 109
The transaction log for database ‘tempdb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
The value in log_reuse_wait_desc column in sys.databases is ACTIVE_TRANSACTION. I tried to shrink the log file but it didn’t work. To resolve the problem temporarily, I added another log file. 1. Is the 2,097,152 MB the maximum amount for the log size for a database?
2. Why I couldn#%92t shrink the log file?
3. How can I resolve the issue without adding another log file? Does a checkpoint solve the problem?
CanadaDBA
When it is set up unrestricted you should not get the 9002 error unless you ran out of space on the drive where tempdb is on…. I don’t think there is any hard limitations for any db log file size…
If you have active transactions in the log you can’t shrink… Run BACKUP LOG TEMPDB WITH TRUNCATE_ONLY… Truncating the log just removes inactive entries form the log and then shrink the log file using dbcc shrinkfile command… Managing the Size of the Transaction Log File
http://msdn2.microsoft.com/en-us/library/ms365418.aspx
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
MohammedU.
Moderator
SQL-Server-Performance.com
There is plenty space available on the disk; over 200GB. I tested again on the tempdb and once on another database. I checked for Unrestricted File Growth but after pressing OK and get back to see the results it shows the log file: By 10 percent, restricted growth to 2,097,152 MB. Here is the results for @@Version:
Microsoft SQL Server 2005 – 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) I checked Model database and it was set to unrestricted. Then I changed it to restrict to 100MB and OK. And then changed it back to unrestricted and pressed OK. Surprisingly, I saw it is restricted to 2,09,152 MB! There is a modeling application that uses certain databases to calculate some results. The tempdb recovery model is set to Simple. Any idea? CanadaDBA
What edition of sql server you are using?
Run the profiler while setting up the unrestricted growth and see what is causing this change…
MohammedU.
Moderator
SQL-Server-Performance.com
why don’t you just manually set the log file size to 2-3GB or so
instead of using the auto growth otherwise this seem to be a strange message
there is contiguous requirement for file growth
Hi,
Even if you have free space to grow the tempdb you will get this message;it depends upon the tempdb usage from your application,db maintanance activity you perform (and how frequantly), analyse your system and usage for tempdb and set the max size by altering database and set the Growth option by MB and not in % http://msdn2.microsoft.com/en-us/library/ms175527.aspx http://msdn2.microsoft.com/en-us/library/ms176029.aspx
Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com
The care of tempdb has to be increased in SQL 2005 as it is used extensively, as suggested you have to increase the size rather than leaving to default values in autogrowth, refer to Hemant’s links in this case that explains the features.
There is no limit on the log size of tempdb and you have to take care of queries too as they are main culprits. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>