July 31, 2008 at 10:48 am
Transaction log file of the tempdb is growing quickly and causing a drive space issue for me. As a short term solution my DBAs have created a job that truncates the transaction log file periodically. I do not know the reason for the abnormal file growth.
Objective
a) I would like to fiund out what exactly is causing this.
Question:
a) How do I know what is causing this issue?
I would like to pin point the cause. Any script, tool will be very helpful. It's SQL 2000 environment running on win2k3.
Please post if you need any more information.
Thank you in advance for your help.
masroof
July 31, 2008 at 12:18 pm
SQL Server has three recovery models. Each recovery model defines how transactions are dealt with in the transaction log.
Simple Recovery: transactions are cleared automatically so the space can be reused.
Full Recovery: transactions are kept until a log backup is performed.
Bulk-Log Recover: same as full, except certain bulk operations are minimally logged.
If your database is setup in simple recovery, the transaction log will grow to the largest size needed. There is no need to perform backups (in fact, you can't).
If your database is setup in full recovery or bulk-logged, the transaction log will continue to grow until you perform a backup. If you are not currently backing up the transaction log you can either switch the database to simple or implement log backups. Log backups should be performed as frequently as required by the business requirements. This can be anything from every minute up to once a day, but is usually something like every 15\30\60 minutes.
You should not implement a process that truncates and shrinks the log files. This will cause performance issues because the transaction log will just grow again and the constant truncating\shrinking will cause fragmentation.
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
July 31, 2008 at 12:37 pm
tempdb should always be in simple mode. If it's growing large, you have a large transaction, or open transactions.
dbcc opentran will look for open ones.
For large ones, not much you can do unless you can break into multiple transactions. Any idea what uses a lot of tempdb space? Any large sorts?
July 31, 2008 at 3:44 pm
Tempdb Recovery Model Is SET to SIMPLE & Cannot Be Modified.
Refer : ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ce4053fb-e37a-4851-b711-8e504059a780.htm
Also Refer : For other questions
August 11, 2008 at 3:26 am
Hi All,
Recently I came across the same issue on one of our client server.
The tempdb started increasing at a rate of 3MB/sec.Then i transferred the growth of it to another drive through secondary datafile.But still it started eating that drives space and as a SHORT TERM resolution to stop the growth of the tempdb we rebooted the server.
Can anyone provide me why this happens and how to avoid it permenantly??
I can provide you the following details about my server.
1.'tempdb' is in SIMPLE recovery mode itself.
2. There are no open transactions running at that time.
3.Even to the new drive where I kept secondary logfiles is not having drivespace issue also.
4.'tempdb' is set to grow 'Unrestricted'.
5. No Critical errors have been noticed in EVENT LOGS.
Please help me why this sudden growth may happen and how to avoid it.
Thanks in Advance
Vinu
August 12, 2008 at 6:55 pm
I used to work somewhere where SQL2k would occasionally do this...
No open transactions, truncate log & shrinkdb would do nothing. In the end all we could do was keep an eye out for it and when tempdb started growing we would schedule a restart of the DB service that evening.
Never got to the bottom of it 🙁
June 30, 2009 at 2:53 pm
Hi,
i want a script that it should notify us when any opentransactions running more than 30 seconds
February 11, 2010 at 8:53 pm
Hey Steve,
I know this is an old posting but the problem of tempdb log growth seems to be a perennial issue.
Why should tempdb be set to SIMPLE recovery model?
In SQL 2000 what is the best way to find out why it is growing so big?
March 31, 2010 at 10:44 am
peter-970097 (2/11/2010)
Hey Steve,I know this is an old posting but the problem of tempdb log growth seems to be a perennial issue.
Why should tempdb be set to SIMPLE recovery model?
In SQL 2000 what is the best way to find out why it is growing so big?
Hi Peter,
I am going through a similar situation, I have an SP that is failing during an insertation into a temporary table. Here are some commands that you can run in single or in a query:
use tempdb
go
-- Reports information about a specified database or all databases.
exec sp_helpdb 'tempdb'
go
-- Displays the number of rows, disk space reserved, and disk space used by a table in the current database, or displays the disk space reserved and used by the entire database.
EXEC sp_spaceused
Go
-- Review tempdb database system objects
SELECT name
FROM tempdb..sysobjects
Go
-- Undocumentated command from microsoft
dbcc showfilestats
go
-- Checks the consistency of disk space allocation structures for a specified database
dbcc checkalloc ('tempdb')
go
-- Checks the allocation and structural integrity of all tables (in the current database) in the specified filegroup.
DBCC CHECKFILEGROUP
go
-- Displays fragmentation information for the data and indexes of the specified table.
dbcc showcontig
go
-- Provides statistics about the use of transaction-log space in all databases.
dbcc sqlperf(logspace)
go
April 10, 2012 at 7:26 am
This is old story, but does anybody has ever resolved this?
As we know checkpoint on tempdb is issued when logfile gets filled by 70%.
I'm executing:
dbcc opentran
DBCC SQLPERF(logspace)
dbcc opentran
And here is what I receive:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Database Name Log Size (MB) Log Space Used (%) Status
-----------------------------------------------------------------------
tempdb 556.80469 95.857361 0
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It looks like for some reason checkpoint is not issued.
Any ideas how to fix this?
April 10, 2012 at 7:30 am
Are you sure that checkpoint is not getting issued or could it be that checkpoint is getting issued but the log space is not getting marked reusable?
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2012 at 7:34 am
I think so. Because if I do it myself, on next moment I get something like this:
Database Name Log Size (MB) Log Space Used (%) Status
-------------------------------------------------------------------------------
tempdb 556.80469 1.9500393 0
April 11, 2012 at 12:31 pm
This is a somewhat 'known' issue with the tempdb transaction log. By default the system does not truncate the completed transactions in the tempdb transaction log until it hits 70% usage (that's buried in BOL somewhere). Unfortunately this can cause a 'race' condition for the cleaning out of the tempdb transaction log and applications filling it up. True a checkpoint should clean it out but does not all of the time - sometimes you'll need to do a backup transaction/truncate_only on tempdb to clean things up.
We have implemented a script that runs every 15 minutes using DBCC SQLPERF(LOGSPACE) coupled with logic to truncate the inactive part of the tempdb log just for this type of situation. It emails us when it happens at 40% and pages us when it hits 85% - yes we do have some 'killer' transactions ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply