• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • Last Modified:

SQL Server database percentage transaction log high

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
Allanore
Asked:
Allanore
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
AllanoreAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
jogosCommented:
"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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now