Solved

SQL Server database percentage transaction log high

Posted on 2013-05-16
4
759 Views
Last Modified: 2013-05-21
Im getting alerts on my SQL server that the TEMPDB transaction log is reaching a high percentage: 97%. What is the best way to fix this issue?

SQL Server 2005
0
Comment
Question by:Allanore
  • 2
4 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39172783
If that is % used of the log, then increase the size of the tempdb log file yourself, before the system needs to do it dynamically.

How much to increase it depends on its current size and how much space is left on the drive.

You can see the current size of the log using this:

EXEC tempdb.dbo.sp_helpfile


Then, determine how much bigger the log needs to be, and run this command:

ALTER DATABASE tempdb
MODIFY FILE ( NAME = templog, SIZE = <new_log_size_you_want>KB )
0
 

Author Comment

by:Allanore
ID: 39172849
Is it better to just increase the space or set up a backup plan to remove the unused transaction files? I do not get this alert that often.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39173022
Hopefully you have only one transaction file on your tempdb db, and it's required, so it can't be removed.

You will only get the alert when tempdb activity is very high ... but, you need to pre-adjust your system so that it can handle high periods of activity *without* needing to dynamically extend any tempdb file, *especially* the log file.  While waiting for the log to grow, all db activity is paused, and on tempdb, that could pause almost every task on the instance.
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 39174387
"What is the best way to fix this issue?"

Besides making the file bigger you can also use this as a moment to review your system while tempdb is heavily used.

For example think about having more than one file for tempdb, is it on right disks
http://msdn.microsoft.com/en-us/library/ms175527(v=sql.90).aspx

And don't forget to ask yourself the question, do I often have this problem or is it just now (because of index rebuild through tempdb).  Can you pinpoint it to a moment or the cummulation of some processes that can be rescheduled to a better moment . Smaller transactions, lesser temp-tables, better indexes or sorting/grouping only on the places you realy need it .....    Since you are using 2005 your environment is going for a while .... think that encountering this bottleneck can be just a signal that also on other resources you gradually are going to hit the limits.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now