Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server database percentage transaction log high

Posted on 2013-05-16
4
Medium Priority
?
850 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 70

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 70

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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

610 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