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.
Take that, parents.
SELECT CONVERT(VARBINARY(16), 138)
Abandon all hope
So, uh, here it is.
Thanks for reading!
USE [master] GO CREATE PROCEDURE [dbo].[sp_GoAheadAndFireMe] AS BEGIN SET NOCOUNT ON IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp CREATE TABLE #temp ( id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, cmd NVARCHAR(4000) ) INSERT [#temp] ( [cmd] ) SELECT N'DBCC WRITEPAGE (' + CAST([dddpa].[database_id] AS NVARCHAR(10)) + N', ' + CAST([dddpa].[allocated_page_file_id] AS NVARCHAR(10)) + N', ' + CAST([dddpa].[allocated_page_page_id] AS NVARCHAR(10)) + N', ' + N'0' + N', ' + N'4' + N', ' + N'0x0000008A' + N') WITH NO_INFOMSGS' AS [cmd] FROM [sys].[dm_db_database_page_allocations](2, NULL, NULL, NULL, 'DETAILED') AS [dddpa]; DECLARE @cmd NVARCHAR(4000); DECLARE @sql NVARCHAR(4000); DECLARE tempdb_killer CURSOR LOCAL FAST_FORWARD FOR SELECT [cmd] FROM [#temp] AS [t] OPEN tempdb_killer FETCH NEXT FROM tempdb_killer into @cmd WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sql = N'SELECT TOP 1000 * INTO #crap_' + SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 0, 9) + ' FROM sys.messages;' --One for the money EXEC(@cmd); EXEC(@sql); --Two for the show EXEC(@cmd); EXEC(@sql); --Three to get ready EXEC(@cmd); EXEC(@sql); --Now your server is dead FETCH NEXT FROM tempdb_killer into @cmd END CLOSE tempdb_killer DEALLOCATE tempdb_killer END;