Temp db log file is growing .

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Temp db log file is growing .

23 posts, Page 1 of 3

1 2 3 »»»

Temp db log file is growing .

Rate Topic

Display Mode

Topic Options

Author

Message

tapaskumardm

Posted Tuesday, August 28, 2012 7:43 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 4:48 AM
Points: 282, Visits: 116

tempdblog file is growing to 5 gb. I dont have more space on the drive.

want a immediate solution . without restarting the server.

please somebody reply..

Post #1350936

GilaMonster

Posted Tuesday, August 28, 2012 7:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 37,725, Visits: 29,982

Add more drive space
Figure out what's preventing TempDB's log from being reused and resolve that problem
http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1350945

anthony.green

Posted Tuesday, August 28, 2012 7:51 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

add more space to the drive
or
restart SQL

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1350946

tapaskumardm

Posted Tuesday, August 28, 2012 7:57 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 4:48 AM
Points: 282, Visits: 116

Thanks,

usually it is around 2 GB. we don't have space in SAN drive to add.

I want to release it now.. Is there any way to fix it ?????

Post #1350957

anthony.green

Posted Tuesday, August 28, 2012 8:00 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

As Gail mentioned, figure out what is causing the log file to grow, otherwise if it happens again you will be in the same boat.

Something wanted the log file to grow to 5GB so if it happens again and you have shrunk the file already it will regrow to 5GB.

Then you might get away with using DBCC SHRINKFILE
http://support.microsoft.com/kb/307487

Otherwise restart SQL or add more space.

Post #1350961

tapaskumardm

Posted Tuesday, August 28, 2012 8:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 4:48 AM
Points: 282, Visits: 116

I know one job is using tempdb.

but tempdb is not releasing the space where as 4 Gb of space is not used.

I am using SQL 2005. also running replication. so temp db is always in use.

Post #1350993

tapaskumardm

Posted Tuesday, August 28, 2012 8:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 4:48 AM
Points: 282, Visits: 116

It is not happening daily basis.. once in a quater or so I faced the problem..

All the time I can not restart the server,,, right.
So, please tell me is there any option to make release the unused space on tempdb ??

Post #1350998

anthony.green

Posted Tuesday, August 28, 2012 8:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Space will not release once it has been consumed, this is due to the expensive procedure of allocating more space to files.

If it grew to 5GB it will stay at 5GB unless you manually restart SQL or if possible run DBCC SHRINKFILE as per the article I provided earlier.

Post #1350999

GilaMonster

Posted Tuesday, August 28, 2012 8:47 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 37,725, Visits: 29,982

No, it won't release space. If the log is not growing more, leave it.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1351004

durai nagarajan

Posted Wednesday, August 29, 2012 4:52 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 856, Visits: 2,115

tapaskumardm (8/28/2012)

Thanks,

usually it is around 2 GB. we don't have space in SAN drive to add.

I want to release it now.. Is there any way to fix it ?????

any problem in using DBCC SHRINKFILE?

Regards
Durai Nagarajan

Post #1351514

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions