Red Gate forums :: View topic - Log shipping flushing the buffer...??
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

Log shipping flushing the buffer...??

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
swirl80



Joined: 23 Oct 2008
Posts: 21

PostPosted: Tue Sep 22, 2009 8:12 pm    Post subject: Log shipping flushing the buffer...?? Reply with quote

Something i've noticed recently is that whenever we restore our log files (every 30mins) to what is our Live Reports server, it seems to flush the buffer and procedural cache. There's nothing in the script to do a dropcleanbuffers / freeproccache so i'm baffled!!!

So the cache is building up for 30mins (up to about 6.5GB) then flushes again, obviously this will affect performance but i'm stumped as to what or why this is happening.

Is this something redgate have in their sqlbackup extended stored procedure??

The process basically kills all users in order to get exclusive access to restore the log and loops through all databases. I thought it may have been the killusers part but tested that and it doesn't seem to flush.

SQL Server 2005 Std (9.0.4226) - 2x Quad Core, 8GB RAM
Windows Server 2003 Std SP2

any ideas???
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Wed Sep 23, 2009 2:56 am    Post subject: Reply with quote

I think that's what happens by design when you restore a full/log backup on SQL Server.

I did some quick tests on SQL Server 2000, and the buffer cache drops all cached data related to the database whose full/log backup you are now restoring. Makes sense since it does not know if the data it holds in the cache is now consistent with that in the database.

I can confirm that SQL Backup itself does not explicitly clear the cache during a restore.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
swirl80



Joined: 23 Oct 2008
Posts: 21

PostPosted: Wed Sep 23, 2009 9:20 am    Post subject: Reply with quote

thanks for the info, suppose that does make sense i just wanted to confirm that there wasn't anything wrong with the server as i've recently built and deployed it like without noticing this!

I've tested on the old server we took out of production last week and got the same results.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group