0

Today i had very spesific case on temp db. My temp db has 10 datafile and all datafiles was full and database locked. I shrinked temp database, files and log files but it wasnt worked. Also i closed Sql server services and i opened again but still temp db sizes was full. Only when i changed initial sizes on each datafile to so small values then i solved problem. Someone can explain why after reboot my temp db didnt release space and never temp db was like this big size. It was always about of 60G. How i can trace why i had this problem ?

microracle
  • 69
  • 1
  • 11
  • The question for me is why was your tempdb that large? Is it going to just grow that large again? As to the issue of file size, to decrease the size of the file you need to shrinkfile also. However, shrinking tempdb is counterproductive if the size will increase to that again as increasing file size is an expensive operation. If it was the result of a one time data movement then it shouldn't be a big deal. – Sean Lange Mar 08 '17 at 17:29
  • We have so much transactions thatswhy its increasing. After when i changed initial size each of datafile it was stopped to increase. – microracle Mar 08 '17 at 17:41
  • So are you going to have this many transaction again? Do you still have a question on this? – Sean Lange Mar 08 '17 at 18:02
  • My question in this normally after reboot server service temp db should release allocated space but after reboot it wasnt. Still it was full. – microracle Mar 08 '17 at 18:18
  • And we always need to do like today transactions, bulk inserts. I dont know how to manage this ? In actually why growing temp db as today, everyday we are doing transactions as today but what was changed today everything looks like normal. – microracle Mar 08 '17 at 18:25
  • If tempdb is always going to grow to that size there really isn't anything to manage. – Sean Lange Mar 08 '17 at 19:27
  • You're mixing file size and allocated space, they mean different things in SQL Server (vs Windows). Are you referring to the files getting bigger and filling up your disk? That's different from the files being the same size on disk but is getting used up by transactions/queries. – SQLmojoe Mar 08 '17 at 20:19

1 Answers1

0

Hello All and Sean Lange and SQLmojoe,

I created job which is shrink temp db files 3 hours in a 1 time and problem solved. Thanks to everybody.

microracle
  • 69
  • 1
  • 11