Solved

SQL Server database percentage transaction log high

Posted on 2013-05-16
4
813 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
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:Scott Pletcher
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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 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