tempdb is full

Question

tempdb is full

DBA.k

Ten Centuries

Points: 1105
More actions
October 6, 2015 at 5:15 am

#305274

Hello experts,
Message
Executed as user: NT AUTHORITY\SYSTEM. The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed.
in my sql server agent job i have the above error, this type of errors i got some of multiple jobs. anyone kindly give permanent solutions.
Thanks in advance ....

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Increase space in TempDB, set it to autogrow, manually increase the file size, increase space on the TempDB drive if its at capacity.

Perry Whittle SSC Guru Points: 233821 More actions · Answer 2

mahi123 (10/6/2015)
Hello experts,
Message
Executed as user: NT AUTHORITY\SYSTEM. The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed.
in my sql server agent job i have the above error, this type of errors i got some of multiple jobs. anyone kindly give permanent solutions.
Thanks in advance ....

you'll need to try and increase the t-log space for tempdb. If you try to use the GUI this will likely fail so try the following (replace ? with the number of MBs you wish the new maxsize to be)

ALTER DATABASE [tempdb] MODIFY FILE (name=templog, maxsize = ?MB)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

DBA.k Ten Centuries Points: 1105 More actions · Answer 3

Thank you for yours respond , but my tempdb data file is already unrestricted growth only

Ant-Green SSC Guru Points: 113445 More actions · Answer 4

The error is related to the transaction log file.

Is that unrestricted also? If so you may be hitting the disk capacity and need a bigger disk.

Lowell SSC Guru Points: 323504 More actions · Answer 5

mahi123 (10/6/2015)
Thank you for yours respond , but my tempdb data file is already unrestricted growth only

then you are out of disk space on the drive? you might need to add additional files, on other drives, to allow it to have more space.

if it's a drive on a SAN share, you might need to ask your network guys to allocate more space.

if it got this big once, it will continue to do so, as you've got processes that need a lot of temp space.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBA.k Ten Centuries Points: 1105 More actions · Answer 6

Thank you for yours response

In Temdb database properties there are 4 data files and 2 log files which is unrestricted growth, and disk space is assigned to D: drive which is still i have 50GB free space ..

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

2 log files? How come?

Only 1 log file will be used until it becomes full then the other one will take over as logs a sequential so no need to have more than 1 in a normal operational DB.

OK its unrestricted growth but what is the growth increment? If its a percentage, whats the size of the current file?

What else is on D?

Perry Whittle SSC Guru Points: 233821 More actions · Answer 8

the t-log file is the problem, the error clearly states this!

Please post results of the following;

PowerShell query

Get-Volume | ?{$_.drivetype -EQ "Fixed"} | ft DriveLetter,FileSystemLabel,HealthStatus,SizeRemaining,Size

TSQL query

SELECTname

, size / 128 AS SizeMBs

, max_size / 128 AS MaxSize

, case is_percent_growth

WHEN 0 THEN CAST(growth / 128 AS VARCHAR(20)) + 'MBs'

ELSE CAST(growth AS VARCHAR(20)) + '%'

END AS Growth

FROM sys.master_files

WHERE database_id = DB_ID('tempdb')

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

DBA.k Ten Centuries Points: 1105 More actions · Answer 9

Hi Thank you for yours help, as per your query result is in my db like below one

Name Sizembsmaxsizegrowth

tempdev2048 2048 100MBs

templog1000030000500MBs

tempdev_22048 0 100MBs

tempdev_32048 0 100MBs

tempdev_42048 0 100MBs

templog11000030000500MBs

Perry Whittle SSC Guru Points: 233821 More actions · Answer 10

where are the results from the powershell query

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉