January 5, 2010 at 1:55 pm
I got this error message:
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..
How can I fix it? This is production server. Would this cause time down
January 5, 2010 at 3:53 pm
Krasavita (1/5/2010)
I got this error message:The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..
How can I fix it? This is production server. Would this cause time down
Is there free drive space still on the drive where tempdb is stored?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 5, 2010 at 10:13 pm
as Shawn Melton said
1. check for free space on drive where tempdb is stored
&
2. Try to truncate log of tempdb only if no users are using tempdb database.
3. also if mdf is increased & no load is there on server(no one is using tempdb )
Try,
use tempdb
go
DBCC shrinkfile(tempdev,truncateonly)
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
January 5, 2010 at 11:23 pm
sanketahir1985 (1/5/2010)
as Shawn Melton said1. check for free space on drive where tempdb is stored
&
2. Try to truncate log of tempdb only if no users are using tempdb database.
3. also if mdf is increased & no load is there on server(no one is using tempdb )
Try,
use tempdb
go
DBCC shrinkfile(tempdev,truncateonly)
You can perform the following:
1. Take backup of your log files
2. Detatch the database.
3. rename the log file
4. attach the database again without .ldf.
Do this if you really do not need the log file.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
January 6, 2010 at 12:56 am
Try This
use tempdb
go
DBCC shrinkfile(tempdev,truncateonly)
Restarting the server will recreate the tempdb 😀
January 6, 2010 at 2:38 am
krayknot (1/5/2010)
sanketahir1985 (1/5/2010)
as Shawn Melton said1. check for free space on drive where tempdb is stored
&
2. Try to truncate log of tempdb only if no users are using tempdb database.
3. also if mdf is increased & no load is there on server(no one is using tempdb )
Try,
use tempdb
go
DBCC shrinkfile(tempdev,truncateonly)
You can perform the following:
1. Take backup of your log files
2. Detatch the database.
3. rename the log file
4. attach the database again without .ldf.
Do this if you really do not need the log file.
You can't backup the Tempdb database, You can't detach the tempdb.
If your TempDB has filled up its drive then you can either put it on drive with more space, and/or try to figure out what has caused the space to be used, it could be anything from large user queries/sorts to index rebuilds etc
Try to identify whats causing tempdb to grow.
You can try to use the shrink commands above to free up space in Tempdb without restarting the SQL Service
Gethyn Elliswww.gethynellis.com
January 6, 2010 at 2:44 am
Krasavita (1/5/2010)
I got this error message:The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..
How can I fix it? This is production server. Would this cause time down
Backup and restore on tempdb is not allowed.
Find out why is it full. What is the size of the tempdb log file? Is there any free disk space? Is fmaxsize set?
Run DBCC OPENTRAN. There must be some open transaction.
January 6, 2010 at 6:25 am
Krasavita (1/5/2010)
I got this error message:The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..
How can I fix it? This is production server. Would this cause time down
Well so far I have not seen you respond to any advice. I take that to mean one of 2 things:
1) You are still trying to fix it, with the bosses breathing down your neck
2) You have already fixed
If (2) is the reason, I would hope you have found out why it did it to begin with. The advice from Suresh is where you need to start if you have not found out why yet.
If you are still trying to fix the issue and you need an immediate fix. With downtime, restart your SQL Server service. This will recreate the tempdb MDF and LDF files, it basically flushes it out. Again you need to make sure it is not being used at the time you restart it.
A small request: It is helpful if you can respond to what you have done or have found out so far to let us know that we have helped or not helped you. As well, if someone else comes along this post they might appreciate getting an answer to help them out.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 6, 2010 at 8:04 am
Thank you, I fixed it, someone were running along a query and I reboot server and it fixed it
January 6, 2010 at 8:34 am
Krasavita (1/6/2010)
Thank you, I fixed it, someone were running along a query and I reboot server and it fixed it
You mentioned this is a production server. Do you have approval/Change request for rebooting the production server(User's will have some downtime)
January 6, 2010 at 9:27 am
krayknot (1/5/2010)
sanketahir1985 (1/5/2010)
as Shawn Melton said1. check for free space on drive where tempdb is stored
&
2. Try to truncate log of tempdb only if no users are using tempdb database.
3. also if mdf is increased & no load is there on server(no one is using tempdb )
Try,
use tempdb
go
DBCC shrinkfile(tempdev,truncateonly)
You can perform the following:
1. Take backup of your log files
2. Detatch the database.
3. rename the log file
4. attach the database again without .ldf.
Do this if you really do not need the log file.
I second GRE-452109's thoughts.
First off all you cannot backup tempdb and then it being production you cannot even detach tempdb, it would be suicidal :w00t:
Coming back to the solution, please follow any of the steps below.
1. check if the db is Set to Autogrow
2. If Autogrowth is on, then check if the disk has free space
If you answered yes to 1 and no to 2 then the solution could be any of the things below
1. Adding more space to the disk if you want a permanent fix, assuming your tempdb needs this space for day to day operations.
2.Restarting SQL Server..which may not be applicable to your case as it is Production server
3. Check activity monitor to see if there is any uncommitted transaction or some big updates happening.
4.To find the text for queries running in the background without using profiler, use the query below
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
This would help you identify the culprit.
Let us know what solution is best applicable in your case..
Hope this helps..
Thanks!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 6, 2010 at 9:34 am
I didn't knew rebooting Production was that damn easy..:w00t:
If I know my manager correctly, he would just shoot me in my arse ➡ with his .5 Caliber gun if I reboot my production server without having a proper Change Request, which details the reason for the reboot and then getting the change request Approved after a proper discussion :discuss: with all the team members of the applications that will be affected with the reboot..
Anybody on board with me on this? :hehe:
Thanks...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 7, 2010 at 2:44 am
u have to first find out if any open transaction is there.
if not u can take log backup and then shrink log file,with out zero downtime.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply