Some guys are very lucky. Retrieving data from a wrong delete using PowerShell and DBCC PAGE

Ok,  (sorry for my English first)

I do not know why, but some guys are blessed by “God Luck”. I’ll start the story:
Yesterday morning a friend called me, he has a computer company that has a program that automates front cash for restaurants, bars etc. … was my first job, and we are good friends.
It was a long time since we talked and he called me to go  in his company with him because he was finishing a job and we can talk better about what we are doing now.
In There he was typing a few sheets (paper file) from a client in your program. I fact, he was creating  the database  from a client and he was putting 4 employees to type this sheets in the last week.According to him, was 10.000 sheets that they are typed in the software and he was doing some adjustements in this database.

Last week, another friend had called me and asked if there was any way to recover a data done by a delete. I told him that if he had not been in a transaction or a third tool to read log had no way to be done. But that was in my head all week, because I remebered all the posts on our teacher Paul Randal about Ghost Records and Ghost Cleanup.

I decided to play around with DBCC PAGE and Ghost records. I asked him if he had any database that I could use int the development server with enough data that I could use and had no problem. Also told him I would change a server’s trace flag. He told me that everything went well and a give me a database with some tables  to play well populated. Then I activate the Trace Flag 661 to stop the ghost cleanup process and got some tests.

About 5 o’clock, I left and he got it. But I forgot to disable the Trace Flag. It was about 9 o’clock at night, I rembered and called him to tell him to enter the dbcc traceoff  to the flag 661. He answered me and said in despair. “Laerte, has some way to recover the data made ​​by a delete? I deleted the entire table that I had done. 10,000 records. He had not backed up and not had put the transaction to delete. He told me he could put the guys again to type, but would delay a lot and he could lose your customer.

I said the same thing that I said to another friend, no transaction and no backup, only third part tool.

Then came a light and I asked. What server were you doing your job ?  He told me even if you were using. So I said, maybe you can be a lucky guy. Do not touch anything I’m coming up there

As I had stopped the process of ghost cleanuop, maybe I could recover the data or a portion of the data.

And from ther 10.000, we can recover 60%. I will create a scenario with the same conditions : ]

First the DBCC to Disable the Ghost Cleanup process :

   1: DBCC TRACEON (661,-1)

Remember, or you put the trace flag in the SQL Server Startup parameters or you have to enable it as global. Ghost Cleanup process it is not a Session Scope.

Create The Table

   1: Create table TestDelete (idsomething int identity(1,1) , NameSomething varchar(100), AddressSomething varchar(50),CitySomething varchar(50))

   2: go

   3: create clustered index idx_001 on TestDelete (idsomething)

   4: go

   5: set nocount on 

   6: INSERT INTO TestDelete (NameSomething, AddressSomething,CitySomething)

   7: SELECT 'My Name ' + cast((ABS(CHECKSUM(NEWID())) / 10000000) as CHAR),'My Address ' + cast((ABS(CHECKSUM(NEWID())) / 1000000) as CHAR),'My City ' + cast((ABS(CHECKSUM(NEWID())) / 1000000) as CHAR)

   8: GO 10000

   9: set nocount off

Then Delete The table

   1: delete from TestDelete

if you take a look at sys.dm_db_index_physical_stats  you will se the Ghost records count

   1: SELECT ghost_record_count, record_count, * FROM sys.dm_db_index_physical_stats 

   2: (DB_ID(N'test'), OBJECT_ID(N'dbo.testdelete'), NULL, NULL , 'DETAILED'); 



if you use DBCC Page, you will some info :

First : The Page marked with the number of the Ghost records.

   1: DBCC PAGE ('test', 1, 1133400, 3) with tableresults




This information will be useful for me to bring only the pages that have Ghost Records in my PowerShell script.

And the second Info is the records deleted :


let’s create the table that have the data recovered :

   1: Create table GhostRecords (idsomething int  , NameSomething varchar(100), AddressSomething varchar(50),CitySomething varchar(50))

And The PowerShell Script :

   1: $Go = $true

   2: for ( $i = 46; $i -gt 45; $i++ ) {


   4:     try {

   5:         $i

   6:         if ($i -gt 1000 -and $Go) {

   7:             $i = 1132200

   8:             $Go = $false

   9:         }

  10:         $ReturnDBCC = Invoke-Sqlcmd -ServerInstance . -Database test -Query "DBCC PAGE ('test', 1,$i, 3) with tableresults" 

  11:         $isGhostRecord = $ReturnDBCC | where { $_.field -eq 'm_ghostRecCnt' -and $_.value -gt 0}

  12:         if ($isGhostRecord -ne $null)

  13:         {

  14:             $Property = @{} 

  15:             $ReturnDBCC | % {

  16:                 if ($_.field -match 'idsomething|NameSomething|AddressSomething|CitySomething') 

  17:                 {


  19:                     $Property.Add("$($_.Field)","$($_.VALUE)")


  21:                     if ($_.field -eq 'CitySomething') 

  22:                     {

  23:                         $Object = New-Object psobject -Property $Property

  24:                         $Variable = $Object | select idsomething,NameSomething,AddressSomething,CitySomething

  25:                         $valueDataTable = Out-DataTable -InputObject $Variable

  26:                         Write-DataTable -ServerInstance . -Database test -TableName GhostRecords -Data $valuedatatable 

  27:                         $Property.Clear()

  28:                     }

  29:                 }


  31:             }


  33:         }

  34:     } catch {

  35:         Write-Host $Error[0]

  36:     }

  37: }

I started at Page 46 and Jump to 1132200 after 1000 to Faster search, and stop when starts to recieve in Posh  continuos SQL errors about the page does not exist. I tried to use dbcc ind, but some pages does not show in dbcc ind, then this is the way that  I solve this problem to know how much pages that I needed to see.

   1: $Go = $true

   2: for ( $i = 46; $i -gt 45 ; $i++ ) {


   4:     try {

   5:         $i

   6:         if ($i -gt 1000 -and $Go) {

   7:             $i = 1132200

   8:             $Go = $false

   9:         }

Then, I get the dbcc page and filter only pages that have Ghost records :

   1: $ReturnDBCC = Invoke-Sqlcmd -ServerInstance . -Database test -Query "DBCC PAGE ('test', 1,$($_.PagePID), 3) with tableresults" 

   2:     $isGhostRecord = $ReturnDBCC | where { $_.field -eq 'm_ghostRecCnt' -and $_.value -gt 0}

   3:     if ($isGhostRecord -ne $null)

Then I Create and PSObject with the line changed to columns (dbcc page with tableresults shows to you in line format) and save to a GhostRecords Table using Chad Miller´s Out-DataTable and Write-DataTable

   1: if ($_.field -eq 'CitySomething') 

   2: {

   3:     $Object = New-Object psobject -Property $Property

   4:     $Variable = $Object | select idsomething,NameSomething,AddressSomething,CitySomething

   5:     $valueDataTable = Out-DataTable -InputObject $Variable

   6:     Write-DataTable -ServerInstance . -Database test -TableName GhostRecords -Data $valuedatatable 

   7:     $Property.Clear()

   8: }

This Process took a couple minutes and we could recover 60% of the table.


Now The question, why even with Ghost Cleanup process disable I could not recovery the entire table ? I dont know .  In this scenario, I realize that DBCC ind before the delete had 178 lines and after 109.Maybe our Teacher Paul Randal can explain this Alegre

Answered By Twitter :

“@LaerteSQLDBA Nope – other things can remove them to make space, and ghost records on heaps only happen with snapshot isolation enabled”

This only works because I had forgot the disable Trace Flag 661 and The Table is not a Heap (Paul´s Tip) .Otherwise..Bye Bye..

That is because I love Backups and Begin Tran. The lack of confidence in yourself or excess is the same thing. Be Safe..


About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s