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 :
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
Then Delete The table
if you take a look at sys.dm_db_index_physical_stats you will se the Ghost records count
if you use DBCC Page, you will some info :
First : The Page marked with the number of the Ghost records.
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 :
And The PowerShell Script :
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.
Then, I get the dbcc page and filter only pages that have Ghost records :
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
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
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..