DBA Days: Killing TempDB softly

Don’t make me regret publishing this

This really will take a server down immediately. You can restart it, as we learned during DBA Days, but… If you also go ahead and make this an Agent job that runs at startup, there are additional challenges.

Like every time Agent starts up, your server goes down. Score one for Express Edition, I suppose.

I mean it. Don’t put this on a server that anyone cares about. Don’t make it an Agent job. Don’t make that job a startup job. It’s horrible.

So what does it do?

It uses an undocumented command, DBCC WRITEPAGE, to overwrite values stored on your data pages. This causes problems for SQL Server for a couple different reasons. Depending on which pages get hit by WRITEPAGE, it could be critical database information. Boot pages, PFS, GAM, SGAM, etc. If those are bad, your server is pretty helpless to give up any information whatsoever.

If it hits user data pages, SQL will recognize that it’s serving up bad data and warn you about corruption. The second part assumes that you’ve got your database PAGE VERIFICATION option set to CHECKSUM, and that you’re alerting for 823, 824, and 825 errors that warn you about torn pages and other I/O related corruption issues.

We’re taking information from sys.dm_db_database_page_allocations, which is also undocumented, so double the fun, and feeding it into our DBCC WRITEPAGE command using dynamic SQL. No, sp_executesql won’t protect you from this.

We need a database ID, a file ID, and a page ID. We need a start and offset, and we need the value we want to overwrite our data with in hexadecimal format.

In our case, we’re using 138, which is just like, the most punkest-rockest number around.

So there.

Take that, parents.

Abandon all hope

So, uh, here it is.

Thanks for reading!

Previous Post
DBA Days: Chaos Sloth
Next Post
DBA Days: Pre-maintenance Window Rituals

31 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.