September 23, 2014 at 6:26 am
Hi Experts,
One of our server all TempDB datafiles were in one drive and it got full. There was not space in that drive and no free space available in files to shrink .We added a new file in another drive .
Can you please help me find the root cause of TempDB filling up? From where I need to start?
There was not jobs ,no processes holding tempdb at that point.
TIA
September 23, 2014 at 6:59 am
If you didn't have active monitoring/tracing on activity running on the server it will be impossible to get the root cause. There is basically no way to see how/what (DML-)actions were executed in the past.
September 23, 2014 at 8:16 am
we killed oldest active transaction and suddenly all data files got 99% free space and we were able to release more than 120GB.
Can any experts here help me find why the database files showed no free space when the transaction waas active?
September 23, 2014 at 9:57 am
If the drive got full is because you had an open transaction. Like it was said before, open transactions, like temp tables DMLs, can fill your tempdb. It grows until the transaction is closed and if you don't have enough space, it will bring your SQL instance down.
Your problem was due a poorly designed query.
Monitor your MS-SQL instance or check your code and look for temp tables where you hold huge data sets or perform heavy DMLs.
September 23, 2014 at 2:48 pm
Ratheesh.K.Nair (9/23/2014)
we killed oldest active transaction and suddenly all data files got 99% free space and we were able to release more than 120GB.
Did you look at the code the transaction was executing before you killed it? It could have created huge temptable(s), or sorting an extreme dataset, or.... And did you look at the user hat was executing the killed action or from what host the action was coming from? These info could give you a starting point for your investigation.
September 24, 2014 at 10:52 am
Ratheesh.K.Nair (9/23/2014)
Can any experts here help me find why the database files showed no free space when the transaction waas active?
You should have asked that question when your transaction was still active.
Please read this: Troubleshooting Insufficient Disk Space in tempdb
September 25, 2014 at 7:40 am
Alexander Suprun (9/24/2014)
Ratheesh.K.Nair (9/23/2014)
Can any experts here help me find why the database files showed no free space when the transaction waas active?You should have asked that question when your transaction was still active.
Please read this: Troubleshooting Insufficient Disk Space in tempdb
Thanks a lot Alex.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply