Solved

SQL Server database percentage transaction log high

Posted on 2013-05-16
4
749 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

12 Experts available now in Live!

Get 1:1 Help Now