September 22, 2009 at 8:20 am
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???
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
September 22, 2009 at 1:01 pm
I would recommend to place your question also on a RedGate SQL Backup forum, usually they are very responsive.
September 22, 2009 at 1:12 pm
thanks, i have done so lets hope something comes from it 😀
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
September 23, 2009 at 2:17 am
FYI
Response:
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
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
September 23, 2009 at 6:15 am
Thank you for update.
September 23, 2009 at 6:47 am
is this SQL2000 or 2005.
In 2005 it is standard behaviour to clear down the whole proc cache for a restore, which is kinda annoying. Can you restore your logs less frequently?
You will see these types of message in the errorlog
2009-09-20 20:00:02.240 spid67 SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-09-20 20:00:02.270 spid67 SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2009-09-20 20:00:02.270 spid67 SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply