August 30, 2011 at 9:36 am
Hi Experts,
What to do when the Tempdb is full with out Restarting the server or Shrinking the Tempdb. Is there any techniques to resolve this issue.
Please don't tell that add more space or add a data file to the tempdb.
Your response is really appreciated.
Thanking you in advance
Jags.
August 30, 2011 at 10:10 am
What is the exact error message you get?
Is the db set to a max size or is can it grow unlimited but the drive full?
August 30, 2011 at 11:07 am
Tempdb contains user objects (temporary tables, table variables) and internal objects (hash tables, bitmaps and so on).
If your tempdb fills because you create lots of temp tables, just drop them.
If it fills because of internal objects, it could be due to a massive SORT / HASH used in a query plan. In this case, I advise using this script[/url] to identify the offending query and fix its plan by adding indexes or other tuning measures.
If you want to shrink tempdb without cycling the instance, you can use DBCC FREESYSTEMCACHE('ALL') and then issue the shrink command.
Hope this helps
Gianluca
-- Gianluca Sartori
August 30, 2011 at 11:12 am
You can easily add space to tempdb by right clicking the database in SSMS and changing the size of files if you don't have autogrow set.
August 31, 2011 at 3:24 am
Hello,
See, there are lots of other way to solve this TempDB full issue but better and convenient way is that restarting the SQL Server Service which you not want to prefer.
1) Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE)
2) Move TempDB from one drive to another drive. This will also required SQL service to restart.
3) Or you can delete the log(.ldf) file from disk and add another as below.
As I followed this recently therefore elaborating the steps:-
1) Right click one particular database
2) Select detach
4) Go to folder where that particular database mdf and ldf files are present
5) delete only the ldf file
6) Right click Databases in object explorer window of Sql server management studio. (Databases) is present as first item in the tree.
7) Click Attach…
8) Click Add…
9) GO to same database mdf file whose ldf you just deleted in the locate database files window just opened
after clicking add button.
10) Two row gets just visible one for mdf other for ldf i.e. log file ,——–Click the log file row….2'nd row———–
11) Click the remove button
12) 2'nd row disappears
13) Click ok.
By following above steps you delete old log files having size in giga bytes .
And allow the system to create a new log file for you of size less than 600 KB.
I have recovered 20 gb disk space by using this technique.
Please follow this and do update accordingly.
Thanks.
SurendraP
August 31, 2011 at 3:39 am
Surendra Patle (8/31/2011)
...3) Or you can delete the log(.ldf) file from disk and add another as below.
As I followed this recently therefore elaborating the steps:-
1) Right click one particular database
2) Select detach
4) Go to folder where that particular database mdf and ldf files are present
5) delete only the ldf file
6) Right click Databases in object explorer window of Sql server management studio. (Databases) is present as first item in the tree.
7) Click Attach…
8) Click Add…
9) GO to same database mdf file whose ldf you just deleted in the locate database files window just opened
after clicking add button.
10) Two row gets just visible one for mdf other for ldf i.e. log file ,——–Click the log file row….2'nd row———–
11) Click the remove button
12) 2'nd row disappears
13) Click ok.
This "works" only for databases with simple recovery model, but could be a disaster for a database in full recovery. Be warned that deleting a log file prevents point in time recovery and breaks the log chain.
In full recovery, if a log file is big, take log backups more frequently.
In simple recovery, you could try to shrink the log files individually, but they will probably grow again.
In other words, don't follow this advice.
-- Gianluca Sartori
August 31, 2011 at 5:00 am
Surendra,
System dbs cannot be deleted via the method you listed. Actually, no db can be deleted with that method unless you take the database offline or stop the services. And System dbs cannot be taken offline.
Mr.SQL DBA, I agree with Gianluca. Do NOT delete your log files. Ever. Not even with regular databases, no matter what Recovery Mode they use.
There are people here who know a lot more about the exact consequences than me, but I do know this. By deleting the log, you could lose a lot, including your job. It's a bad practice to follow, even for the desperate.
If TempDB is full, there are reasons. Auto-grow is off, hard drive is full, etc. If it's the former, adding size in the database properties via T-SQL (lookup ALTER DATABASE in Books Online) or the GUI will help. If it's the later, adding another disk to your SAN / NAS and moving TempDB away from the other databases might help. I say might, because TempDB might be caught up with a horrible query that will chew up whatever space you give the DB. So, as others have suggested, try to find out what queries are running when Temp DB gets full.
Lastly, as Lutz suggested, post the error message. It might not be the TempDB that's causing you issues, even if the error message suggests that it is. There is a couple of "tempDB" messages that actually mean something else. So if you post the message, we can give you better advice on where to look.
September 1, 2011 at 5:39 am
A few weeks ago I had the situation that the logfile from the tempdb was full.
I was not be able to log on again and we restarted sqlserver.
Are there other possibilities in this case?
Dewes van Lohuizen,
DBA at CSC Netherlands
Private interest: www.mikondo.nl
September 1, 2011 at 8:07 am
Dewes van Lohuizen (9/1/2011)
A few weeks ago I had the situation that the logfile from the tempdb was full.I was not be able to log on again and we restarted sqlserver.
Are there other possibilities in this case?
Please start a separate thread if this is a different question. Short answer is that logging on shouldn't require tempdb unless you have some DDL triggering taking place. At the very least the DAC should allow a connection to the system.
September 1, 2011 at 8:19 am
Sorry.... and thank you Steve.
Dewes van Lohuizen,
DBA at CSC Netherlands
Private interest: www.mikondo.nl
September 2, 2011 at 8:57 am
If you have TempDB that is full, you do not want it to grow (or don't have the space), you do not want to add another file on another drive (or do not have another drive), you cannot shrink...
So really the only thing you are looking for is how to clear out allocated space turning it into un-allocated space? To clarify, you do not want to change the placement, or size of the file itself, or restart the server?
Local temp tables should be cleared out when the transaction is completed. Check that in any procedure call you drop your table as suggested above. If you are using global temp tables, try to find anything hanging on to the table to keep it from getting dropped and ... well... judgment call on what to do with it.
If you cannot use the scripts above, you would then have to look at your code, find out where the tables taking the most room come from, or find out what else is putting stuff into temp db such as a bad order clause that could use an index somewhere.
In case they can be of help:
exec tempdb.dbo.sp_spaceused
select * from tempdb.sys.tables where datediff(day,create_date,getdate()) > 1 -- might show a table that is open for over a day, should not see anything at all most likely won't be of too much use.
exec tempdb.dbo.sp_spaceused |objectID| -- create a loop to go through and see what is taking the most space
September 5, 2011 at 10:30 am
Hello Mr SQLDBA
As you posted 1) You don't want to shrink the log
2) You don't want to restart sql service
3) You don't want to add tempdb file or disk
4) You Don't want to implement tempdb best practice as suggested by
But still you want to shrink the temp db log. Well it looks like an interview question 🙂
Then try to find out the transactions that are causing the tempdb growth. Use DBCC OPENTRAN.
Try to review the script and fine tune them.
Check if there any unnecessary use of temp table. Find if there any queries or joins which can be fine tune to minimize the data volume reads. In short try to minimize the tempdb usage
Let me know if this helps you or you have any other concerns
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply