0

Unable to connect to SQL Server because:

'tempdb transaction log was full due to active transaction'.

There was no way to login to SQL Server to troubleshoot. only option was to restart SQL Server.

I want to know ow to avoid this issue in future. The log file and data files were limited to max size. But is there a way I can be notified before tempdb log file reaches maximum? Is there a way i can get alert or monitor tempdb space usage when it reaches 85% full get notified. this issue cause a big impact as well. so any advise how it can avoided in future will be very helpful.

Dale K
  • 25,246
  • 15
  • 42
  • 71
anu
  • 11
  • 3
  • 2
    Did you try connecting using the DAC? Maybe you need to allow a larger log file to support the workload being used? – Stu Feb 24 '22 at 18:47
  • sorry. did not try that way. can you please help on how i can connect using DAC. its a production server. Thank you for the advise on larger log file. On that note how can i know how much i need to keep the max size. currently it grew from 4GB to 50GB in one day in 6 hours. max size was 50GB. but have changed to larger size now – anu Feb 24 '22 at 20:48
  • @anu - you can find information (both enabling and using) on the DAC at https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-ver15. – Ben Thul Feb 24 '22 at 21:18

0 Answers0