network profile
| bio | website | SQLskills.com/blogs/paul |
|---|---|---|
| location | Redmond, WA | |
| age | 40 | |
| visits | member for | 4 years |
| seen | Mar 29 '11 at 19:07 | |
| stats | profile views | 1,542 |
I'm a consultant/trainer/author, SQL MVP, Microsoft Regional Director, and Contributing Editor of TechNet Magazine and SQL Server Magazine. And I also blog and twitter a ton (twitter.com/PaulRandal), and I'm married to Kimberly Tripp. Phew!
I was on the SQL team for almost 9 nine years - wrote a bunch of DBCC commands (e.g. INDEXDEFRAG, SHOWCONTIG, CHECKDB/repair), and ended up being responsible for the entire Core Storage Engine for SQL 2008.
I'm an expert on internals, HA, maintenance, and disaster recovery. I teach this stuff internally for Microsoft and at conferences/clients around the world.
The answer is usually 'it depends'! :-) Follow me on twitter: @paulrandal
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? But you can't avoid having them in the t-log backup files - t-log backup = everything logged since last t-log backup. |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? And even that doesn't help the OP's original question of how to reduce the size of the log backup following the index maintenance - in fact it will potentially make it bigger, depending on what operations are being done. |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? No - I would still disagree. I'd rather have more frequent log backups so they are smaller, rather than one monster one after all the maintenance is done. Having disproportionately sized log backups can lead to problems copying them across the network (e.g. for offsite backups or log shipping). If there's no user activity and no other need for the log backups, then maybe, but if the system crashes and you have to do a tail-of-the-log backup, that's going to take a lot of time that's part of your downtime. I should do a blog post about this. |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? ok - now I get what you were trying to do - not take log backups during index maintenance, but OP asked about how to make log backup following index maintenance be reduced in size - which this solution doesn't do. |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? Unless your index maintenance job doesn't do anything... |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? So how does what you do reduce the size of transaction log backups? They will contain everything since the previous log backup, unless you're breaking the log backup chain and then restarting it with the full backup. |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? Oops forgot to mention I downvoted this too because you didn't mention being in SIMPLE. Staying in BULK_LOGGED even will not change the size of the next log backup as it will pick up all data extents changed by minimally-logged operations. Wow - downvoted every answer to this so far. |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? And you must switch to SIMPLE just before 10PM, right? Otherwise the 12AM log backup will contain all the log generated between 10PM and 12AM. |
|
May 27 |
comment |
How do I reduce transaction log backup size after a full backup? I downvoted this because of the suggestion to skip diffs and go to daily fulls. Why? Fulls a 3.5GB whereas diffs are only 250MB. The backup strategy looks absolutely fine to me. Removing diffs means many GBs more storage for only a tiny, tiny speedup in restore time. |
|
May 21 |
comment |
What are the performance implications for using SQL Server replication? ok <blush> thanks |
|
May 21 |
comment |
What are the performance implications for using SQL Server replication? :-) No worries - glad to be of help! |
|
May 19 |
comment |
SQL Server 2005 Backup Failing I edited my answer to include a couple of queries that might help you figure out what backups you have. |
|
May 19 |
comment |
SQL Server Patching a Mirror configuration No. The license you have is only for the principal instance - doesn't matter if nothing's using it. |
|
May 18 |
comment |
SQL Server Patching a Mirror configuration Be careful just leaving the connections running on the secondary box if you don't have a license for it. It's only free if you fail back to the original principal within 45 days. |
|
May 7 |
comment |
SQL Server Express 2008 replication/synchronisation Absolutely. See msdn.microsoft.com/en-us/library/cc645993.aspx |
|
May 6 |
comment |
Reclaiming memory from SQL Server Ah - true - it does the trick of decommitting a little bit of physical memory - you're right! |
|
May 6 |
comment |
Reclaiming memory from SQL Server Remember this behavior is 2005 and 2008 - 2000 does not release memory on demand to the OS. |
|
May 6 |
comment |
SQL Server 2005 Mirrored DB Recovery When the original principal comes back online, it will be as the mirror - it can never promote itself as the principal again and force a failover - you always have to do that manually. |
|
May 5 |
comment |
SQL Server 2008 - 2005 Compatibility In that case as long as they don't use an 2008 features, down-level repl might work. |
