Deleting temporary cache

Undo for SQL Server.

Moderator: eddie davis

Deleting temporary cache

Postby DBA72 » Mon Aug 07, 2006 12:56 pm

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?
DBA72
 
Posts: 12
Joined: Tue Aug 01, 2006 2:08 pm

Postby Brian Donahue » Mon Aug 07, 2006 6:46 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Script I use

Postby DBA72 » Tue Aug 08, 2006 8:42 am

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
*/
DBA72
 
Posts: 12
Joined: Tue Aug 01, 2006 2:08 pm

Postby Brian Donahue » Tue Aug 08, 2006 2:02 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Log Rescue

Who is online

Users browsing this forum: No registered users and 0 guests