DBA Days: Killing TempDB softly

Bad Idea Jeans, SQL Server

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

  • I like how even in a script specifically designed to bring down a server, you can’t bring yourself to not close and deallocate the cursor.

  • You know, it was in there, I just didn’t copy and paste everything in, it looks like. I’ll fix it eventually, though I doubt anyone running it would notice 🙂

  • “Destroying tempdb since SELECT create_date FROM sys.databases d WHERE d.database_id = 2”

  • “It’s as if a million databases started thrashing, and then suddenly went silent.”
    -paraphrased Obi-Wan Kenobi

  • You, my friend, are well and truly evil. I’m glad you aren’t my enemy…..

  • Lol! I love it! You were still reasonable enough to do tempdb, but this made me laugh. I still want to do a game where you shoot a picture of a database with you weapon of choice, all the while it writes zeros (makes holes) in a random database using writepage. If you wanted to get fired, what would be more satisfying than seeing a database being shot full of holes.

    • Before the DBA Days webcast I wrote a version that hit a copy of the StackOverflow database and demo-ed it before hitting tempdb.

      Corruption is just so fast, it’s hard to relish the moment.

      • That kind-of takes the pleasure out of it… You want the corruption to hit – and be seen to hit – and be a juggernaut to stop >:)

      • When you ran that script at DBA Days I knew that you were going to destroy everything, because that’s what you do :). You surely showed them that we New Yorkers are really good at causing mayhem LOL.

        • Thanks! That was a weird one to write. I went back and forth on trying to target an object, and whether I should generate my own tempdb activity or wait for something else to do it. I opted for instant gratification. Glad you liked it.

  • Some men just want to watch the world burn.

  • Very cool, but is there a non-evil application for this undocumented (for good reason) command or are you just trying to get Jr. DBAs fired for cutting and pasting code from the internet? Hey… No… Jimmy! STOP THAT!

  • John Nelson #2
    September 8, 2016 1:30 pm

    This is diabolical

  • I’m concerned that the related blog is “How to get a Junior DBA Job – Part 1”. Is running the script how I get the job?

  • Rofl, what is this? Day 2 in Evil Dba?

  • Mua—- and might I add –hahahahaha
    I love this sort of thing, and have frequently done things to see how badly I can annahilate a server (for test purposes).
    Excellent script which I’ll be playing with!

  • Do you think we’re robot clean
    Does this face look almost mean
    Is it time to be an android not a man

    We are 138!!! Clearly an homage to the greatest reunion of all time!!!

  • I was just watching Jason Bourne where they “used SQL to corrupt their databases”… and wow you really can use SQL to corrupt their databases. I was on my high horse over that line for a moment.

  • Danielle Paquette-Harvey
    March 7, 2017 1:22 pm

    Nice! This is exactly the script I was looking for! It works perfectly! Thanks a lot 🙂


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.