Unrestricted Templog
Mar 26, 2007
Env.: SQL2K5 on Win2003
I 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
View 7 Replies
Jul 23, 2005
Right now both of these reside on my C:, but templog is over 3 1/2 GB.1) What do I need to do to move both of these to another drive on thesame system?2) Is there an easy way to purge or compact templog, or just set a sizelimit for it?Thanks,Scott
View 1 Replies
View Related
Dec 1, 2007
Templog is growing 1 GB per hour.
I've read some articles about this issue, that talk about how to shrink it.
In this case I need to find out what and why this is happening
How can I monitore it?
I know, sometimes, I exaggerate in using temporary files, in order to make reports faster.
The Tempdb size is normal.
Thanks
View 4 Replies
View Related
Aug 13, 2010
I have the following T-SQL which simply creates new database. However, in properties of this DB I see that LOG's max file size is restricted to 2 GB. Where's error?
CREATE DATABASE Bonus
ON PRIMARY
(
NAME = N'Bonus',
FILENAME = N'E:Bonus.mdf',
SIZE = 3072KB ,
[Code] ....
View 7 Replies
View Related
Feb 27, 2008
We have "unrestricted" size of log file on model database, but for some reason, all newly created databases have "restricted" sizing.
After taking a look at this, it appears that SQL Server changes the "Unrestricted" file growth setting specified on the log file in the model database to "restricted to 2 TB". This size is the maximum allowed for log files by SQL Server 2005, so this means the settings are identical?
View 1 Replies
View Related
Dec 11, 2007
I'm currently running the command below and getting this error message. I am getting it every few seconds for a couple hours now. Seems to have started when I started this SPROC below. I'm just over 2 hours into running.
Not sure what this means yet, any help is much appreciated. Desperately trying to resolve this urgent matter!
thanks once again!
mike123
"Autogrow of file 'templog' in database 'tempdb' was cancelled by user or timed out after 2813 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."
EXEC isp_ALTER_INDEX
@dbName = 'db1',
@statsMode = 'DETAILED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
View 4 Replies
View Related
Jan 10, 2001
Has anyone had this problem or know how to prevent it? I received an error message yesterday from my SQL server (v7.0 with SP2) saying that the logfile for the tempdb was full. The logfile for tempdb is set by default to unlimited growth and the drive it is on has 24GB free. What causes this error to happen?
View 3 Replies
View Related
Apr 18, 2007
I am trying to set the log file on one of our databases to unrestricted growth, but after changing the setting, saving and then going back into management studio, the setting is now back to restricted growth. I am a sysadmin within sql and a local admin on the server so permissions should not be an issue.
What is preventing the setting of unrestricted growth on the log file from staying after the save.
I do not have this problem on the data file, (the change stays), but only on the log file. it happens for any database on the server. Sql Server 2k5 enterprise edition with SP2.
thanks
View 4 Replies
View Related
