Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Temp db log file is growing . Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, August 28, 2012 7:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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
Posted Tuesday, August 28, 2012 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 5,218, Visits: 5,072
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
Posted Tuesday, August 28, 2012 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, August 28, 2012 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 5,218, Visits: 5,072
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.




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 #1350961
Posted Tuesday, August 28, 2012 8:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, August 28, 2012 8:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Tuesday, August 28, 2012 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 5,218, Visits: 5,072
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.




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 #1350999
Posted Tuesday, August 28, 2012 8:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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
Posted Wednesday, August 29, 2012 4:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse