Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2007-03-26 : 15:38:34
|
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’t shrink the log file?3. How can I resolve the issue without adding another log file? Does a checkpoint solve the problem?CanadaDBA |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-03-26 : 18:23:50
|
probably you have a max size set on the log file of 2gb. if you don't allow it to get bigger than that, sql server will respect your wishes and issue an error msg instead. :)1. you ask if 2 terabytes is the max size of a log file. if your tlog is really that big, you need to do some tlog backups more often... :)2. looks like you couldn't shrink it because there was an outstanding transaction that hadn't been committed. is there a rogue app in your system that is BEGINning transactions without COMMITting them?3. to resolve this, I think you need to figure out what caused the tlog to get big. it may be that 2gb is not enough space for the largest transaction in your system. or it may be that you aren't doing tlog backups often enough. or it may be a rogue app.
www.elsasoft.org |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2007-03-27 : 11:03:18
|
| 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 |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-27 : 11:25:25
|
| in the sql 2005, additional tempdb log files cannot go beyond 2gb. I ran into the exact same problem some time ago. there is no resolution to it.RegardsParesh MotiwalaBoston, USA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2007-03-27 : 11:30:14
|
Hi Paresh,Do you have any link for this?My tempdb database has two data files and one log file. I tried another database with 1 data and 1 log file and got the same issue.quote:
Originally posted by pareshmotiwala
in the sql 2005, additional tempdb log files cannot go beyond 2gb. I ran into the exact same problem some time ago. there is no resolution to it.RegardsParesh MotiwalaBoston, USA
Canada DBA |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-27 : 15:58:13
|
| Hi CanDBA, no I don't have a link. But even on this forum, nobody had a resolution.I work for a very very large org, we have over 1000 DBAs, nobody had idea.Also, one more thing I found is that if you delete a file, chances are its ghost still remains in the master dataabse and will possibly not let you re-use the same name. e.g. I created a templog2.ldf, later I found I could not expand it indefinitely so I deleted it. Again, to prove that it does not expand indefinitely, I tried to prove it to my boss by attempting to create templog2.ldf. Guess what, I could not create it. So I created a templog3.ldf and proved my point and then deleted it. Later I tried creating the file with templog3.ldf and it says this file is already in use by tempdb. Stragely, this does not happen in user databases.Further, one of my colleagues recommended I put an extra file in model database. But guess what, you cannot alter the model database to add its structure....RegardsParesh MotiwalaBoston, USA |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-28 : 05:44:09
|
| Report this behaviour (... chances are its ghost still remains in the master database) to member PaulRandal (Microsoft), and he'll find a good home for it! |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-28 : 09:38:41
|
| Thanks Andrew, I will take the initiative and talk to the MS people. I do have direct access to them.RegardsParesh MotiwalaBoston, USA |
|
|
|
|
|
|
|
|
unrestricted templog