question

WonAustin avatar image
WonAustin asked

SQL Server 2017 with Always On High Availability tempdb log file high disk io

We have SQL Server 2017 with Always On High Availability. Two days ago, we noticed that the disk io is significantly higher than normal. Normally it is at most 15MB/S, but it has been about 100MB/S. We checked the Activity Monitor and noticed that the tempdb templog.ldf has about 100MB/S Written. We tried to fail over to the back up server and fail back to the main server, but it did not work. We estimate that overall performance of the SQL server is down about 20% because of that. How do we get it fixed? Thanks.

tempdb
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

I'd be looking to see if something is running that is using tempdb, possibly with an open transaction? Use a script like sp_whoisactive to see what is running.

Is the tempdb log growing?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

@WonAustin

So, what changed two days ago??? Who deployed new or modified code or a new index and what was it? Or did someone make a change to the server settings?

You need to find out what's going on with TempDB first if 100MB/S is different than your baseline. You do have a baseline, right? ;)

I also agree with @Kev Riley above... using a script like sp_whoisactive to find out what the code is that's running right now.

And, seriously... if you don't know what your normal settings for the server is or you don't know what your baselines are, you really need to make a change so that you can prove to someone else what it is and to help you to decide where to look.


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.