Deleting temporary cache

DBA72

I am doing some testing of SQL Log Rescue. I am testing several scenarios of how we could use this tool and what I am doing is checking what you can see in a scenario of different recovery models.

My tests are not giving me what I expect and I think this is because the LDF is copied to the computer running log rescue. So when I change some settings in the db that I am analyzing (e.g. run checkpoint and truncate log) and then refresh the project in Log Rescue, I still see the transactions when I expect not to see any.

Can anyone tell me how I delete this local data?

Brian Donahue

Hello,

Log Rescue does extract information from the LDF file and copies it to the local workstation. This information is help in the %TMP% location (usually in your user profile). I'm not sure if cleaning it out from there will help, but by all means give it a try if you think it will solve the problem.

DBA72

Thanks for your answer Brian. I checked the files that are created in the temp directory but they seem to get deleted when I close the application. For some reason I am not getting the results I expect from my test. Here is what I am doing In QA (I've added in comments). Can you explain why I still see transactions in a database with recovery model of simple after I've cleared the active part of the log?

create database RedGateRecovery1
go
use RedGateRecovery1
go
create table table1 (col1 int, col2 int, col3 int, inserted datetime default getdate())
go
--populate table
declare @val1 int, @val2 int, @val3 int, @cnt int
set @cnt = 0

while @cnt < 1000
begin
set @val1 = round((rand()*100000),0)
set @val2 = round((rand()*100000),0)
set @val3 = round((rand()*100000),0)

insert RedGateRecovery1.dbo.table1 (col1,col2,col3) select @val1, @val2, @val3

set @cnt = @cnt + 1
end

--check data
select * from RedGateRecovery1.dbo.table1

/***SCENARIO 1***/
alter database RedGateRecovery1
set recovery simple
go
sp_dboption 'RedGateRecovery1'
go

/***Initial backup***/
backup database RedGateRecovery1 to disk = 'D:\Data\MSSQL\Backup\RedGateRecovery\RGRecoveryTest_FULL.bak'
with init
go

/***Random transactions***/
update RedGateRecovery1.dbo.table1
set col1 = 1, col2 = 2, col3 = 3
where col1 between 00000 and 50000
go

/*
CHECK THE SQL RESCUE TOOL NOW AND SEE THE TRANSACTIONS
*/

/**Clear active part of log***/
checkpoint
go
backup log RedGateRecovery1 with truncate_only
go

/*
AT THIS POINT I SHOULD BE ABLE TO RERUN SQL RESCUE AND TOLD THAT THERE ARE NO TRANSACTIONS TO SEE BUT I STILL SEE THE TRANSACTION
*/

Brian Donahue

I think it's because you need to create the database, create the table, do a full backup, populate the table, and then run log rescue, specifying the full backup file that you created earlier.

It probably also doesn't help that tthe database is apparently using the simple recovery model. Log Rescue works best when the recovery model is full because the transactions are retained in the log.