Allanore
asked on
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
SQL Server 2005
ASKER
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 )