December 24, 2009 at 12:12 am
Hi All
Strange question I guess, but...
I have a view A on database A which references a table on database B.
If I insert data via the view, which database gets the transaction log entry?
If the answer is B, then I can temporarily use this method. If it's A, I've got to think again sooner rather than later.
(Bit of background - I have a table which is being inserted with many millions of records on a daily basis, and is just a stage table, and so not required. Database A is full logged, and so the t-log i filling up quicker than my 6 hourly t-log backups can cope with sensibly. Database B is simple logged, and so doesn't suffer from th same issues. I don't need to keep the t-log entries, as the table itself doesn't need to be backed up. I don't want to hard code database names into the TSQL, as the code needs to be transportable from one database to another very quickly - multi client system with separate databases per client.)
Cheers
Ben
December 24, 2009 at 12:41 am
Ben Kentzer (12/24/2009)
If I insert data via the view, which database gets the transaction log entry?
The one that's got the table.
What gets logged are data changes. Since views (ignoring indexed views) don't store data there's nothing to log when an insert is done to a view. The insert gets passed down to the base tables which is where the actual data change happens. Hence it's the DB that actually has the tables that gets the transaction log activity.
6 hours is a long time for log backups. Since the log's getting larger than you like with that frequency of backups, why not just make the backups more frequent. Like every hour. Additional bonus is that you won't be at risk of losing 6 hours of data if the DB fails and the log file is destroyed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2009 at 12:51 am
Hi Gail
Thanks for the speedy response - you've confirmed that it is as I had hoped:-)
The tables that I'm building up are basically being put into a holding area before shipping across to another database server, and aren't backed up on this server (other than for structure, really) and so I don't need to bloat the main warehouse transaction log with millions of un-necessary transactions. This second database is set up with the Simple recovery model, and so the log entries are essentially lost once committed which is fine.
Less transactions means that the log doesn't grow as much, although I take your point about the t-log backup frequency and will look at this as well - thank you. The only thing in my defence ( 😉 ) is that there are 2 job sequences on each database which finish at 5am and 11am, and so the T-Logs are backed up at 6am and noon. There's then a diff at 6pm 6 days a week with a full backup on a Saturday.
Thank you
Ben
December 24, 2009 at 1:31 am
Ben Kentzer (12/24/2009)
This second database is set up with the Simple recovery model, and so the log entries are essentially lost once committed which is fine.
Well, the log records will be discarded upon a checkpoint after the transaction has committed. Simple recovery doesn't mean that the log won't grow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 24, 2009 at 1:51 am
I think that's close enough for me.
The Warehouse database log has grown to 4Gb overnight, whereas running essentially the same processes using the alternate database with the simple logs has got to 57Mb, so it's a lot better although probably not perfect!
Thanks very much
Ben
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply