0

It takes about 3 days for the transaction log (tempdb) to get to the size of a production database (~130 GB). It has come up recently (~2 weeks ago) and there had been no problems up to that point. I'm not the owner of the database nor the author or developer of the solution that is utilizing the database.

We have several databases of the same type across several plants, so I did some comparisons, and the problem happens to be only in one plant.

  1. According to SQL Activity Monitor (Data File I/O tab), templog.ldf is being utilized almost all the time in the sense of write operations - it grows 100 MB every 3-5 minutes.

  2. When I do bcc opentran against tempdb I see one transaction open since when I restarted the instance last time. So it's been open since that time which is almost 3 days.

When I query sys.sysprocesses I actually see two SPIDs of the same age (started 3 days ago) and with the flag open_tran and both with the suspended status.

This is weird because I don't see the same thing in other databases of the same type.

  1. Both queries show up in the Active Expensive Queries (SQL Activity Monitor) and they are not letting go... (this might be because of what the query is for both: WAITFOR DELAY @delay)

  2. I set up an Extended Events session and was looking for anything tempdb, physical files growth, size changed, write ops related and I was trying to correlate the output with the moment when the templog file expands by 100 MB.

database_log_file_size_changed event appears to be associated with almost every session there is in the tempdb. I guess the log file is being utilized by almost each session AND what is interesting, the two old, 3-day sessions do not appear in the EE output (as if they did not affect the log file at all).

I'm gonna go to the 3rd party that owns the solution, and ask them what the problem might be, but I'm starting to wonder if this might have anything to do with the physical structure of the file or maybe the volume.

  1. templog shares the same volume with other dbs and other log files - no problems with any of them up to that point
  2. I'm not able to go to the Properties of the tempdb - Property Size is not available for Database (tempdb) error - even a few minutes after the restart.
  3. I'm not able to run dbcc checkdb against tempdb either as I'm getting:

Object ID 3 (object 'sys.sysrscols'): DBCC could not obtain a lock on this object because the lock request timeout period was exceeded. This object has been skipped and will not be processed.

I have no more ideas on how to investigate the tempdb and the log growing quickly issue. I'd be grateful for any hints.

Regards,

pr0t
  • 21
  • 3
  • Probably just a badly written client application which left a transaction and connection hanging open. Just `KILL` the process and move on. – Charlieface Jul 27 '22 at 12:18
  • Perhaps check out [this question on dba](https://dba.stackexchange.com/q/19870/1186). – Aaron Bertrand Jul 27 '22 at 12:39
  • Well, I wouldn't recommend killing the process without understanding what uncommitted changes you might lose by killing the connection. Certainly does seem like a bug of some type that should be addressed with the developers. Perhaps there is an assumption or dependency on some configuration value overlooked by developers or installers? – SMor Jul 27 '22 at 12:39

0 Answers0