August 22, 2011 at 8:22 am
The backup strategy for a DB I have is a full backup once a day (midnight) and then a transaction log backup every 4 hours.
My problem is that the first TRN log backup after the full backup is huge. The next one has a normal size (a few MB's only)
Which is the simplest way to find out what is going on?
What I tried so far:
I checked the job history in log file viewer after I checked all the jobs, there is nothing between the full backup and TRN log backup (it is normal - I moved all the jobs before the full backup)
I defined a server side trace filtered like this: ApplicationName LIKE 'Microsoft SQL%' but, again, I couldn't find anything..
(of course the DB is online 24hrs/day but there is not too much activity between 12.00 - 4.00am...)
Is there any other way to find out what is going on than defining an unfiltered server-side trace? (inspecting the TRN log backup but without paying for expensive tools...) I am a little afraid to define such a trace, I suppose it will be quite expensive...
August 22, 2011 at 8:30 am
I'd bet a large sum of money that you have a reindexing job running during the night.
use this to update only the indexes that need it : http://sqlfool.com/2011/06/index-defrag-script-v4-1
August 22, 2011 at 8:35 am
You would loose your money Sir, because I moved very carefully all the jobs before the full backup.
And besides this, I inspected the "job history" with the log viewer after I checked all the jobs, and there is nothing there between 12.00 - 4.00 am
August 22, 2011 at 8:38 am
Never lost that bet...
Check if you have maintenance plans or ssis packages.
You have something doing a large import or index being maintained.
The logs just won't grow a lot without 1 or both of these.
August 22, 2011 at 9:13 am
virgilrucsandescu (8/22/2011)
You would loose your money Sir, because I moved very carefully all the jobs before the full backup.
Which means that the next transaction log backup after the full backup will be huge a a result of those jobs (full backups don't truncate the log)
You have some large operation (my money is on an index rebuild) between the log backup that runs before the full backup and the log backup that runs after the full backup. If you're only tracing the period after the full backup then you're only seeing half of the picture.
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
August 22, 2011 at 9:20 am
Table and Index stats updates are fully Logged and can happen nightly.
They also create a very Large tran log backup.
August 22, 2011 at 9:34 am
SanDroid (8/22/2011)
Table and Index stats updates are fully Logged and can happen nightly.They also create a very Large tran log backup.
Unlikely. The stats updates just change the statistics, not the tables and that's a tiny amount of data. To impact the log noticably a hell of a lot of stats would have to be updated.
I did a quick test. Update Statistics on a 1 million row table resulted in a log backup just over 350kB. The log itself contained row_modify log records for 6 LOB pages.
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
August 22, 2011 at 9:50 am
Now keep in mind that I have a tone of tables with very little or no data. So this will get different results that 1 big table.
SELECT COUNT(*) FROM sys.stats
--33427
SELECT COUNT(*) FROM sys.stats_columns
--77082
EXEC sp_spaceused
--data + indexes 21.8 GB
UPDATE ALL STATS WITH FULLSCAN
--30 minutes, 62 MB of logs
--that's 0.28% of the data size in the DB.
--and less than 1.9 KB per stats
--Now since most of them are empty I'd assume that the "used" stats are probably "much" bigger than this. But I don't really care to know to try and test this out.
August 22, 2011 at 9:57 am
GilaMonster (8/22/2011)
SanDroid (8/22/2011)
Table and Index stats updates are fully Logged and can happen nightly.They also create a very Large tran log backup.
Unlikely. The stats updates just change the statistics, not the tables and that's a tiny amount of data. To impact the log noticably a hell of a lot of stats would have to be updated.
I did a quick test. Update Statistics on a 1 million row table resulted in a log backup just over 350kB. The log itself contained row_modify log records for 6 LOB pages.
I have an 8GB DB that ships Tran logs for offsite DR.
It does have a lots of STATS and Indexes, but the point is the Tran log size after a stats updates (tran log backups run every 15 minutes) is always over 7GB. This is with or without a RESAMPLE.
Granted this is being caused by the high level of update, insert, and delete transactions that happen in this Database. However it IS the update stats that cause the huge Tran files to be created and does happen when the Database is not actively being updated during the late night maintenance cycle.
I would not expect the same thing to happen in a DB or Table that did not have a few million changes since the last stats update.
August 22, 2011 at 10:05 am
There is more than just the stats updates in your log then. The stats updates alone are not going to log 7GB of an 8GB database.
I would not expect the same thing to happen in a DB or Table that did not have a few million changes since the last stats update.
Why not?
Regardless of what activity there has been an update stats does the same amount of work. It reads the entire table, recomputes the statistics and replaces the old stats with the new. It's not a replace what has changed, it's a replace the entire stats blob.
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
August 22, 2011 at 10:16 am
GilaMonster (8/22/2011)
There is more than just the stats updates in your log then. The stats updates alone are not going to log 7GB of an 8GB database.I would not expect the same thing to happen in a DB or Table that did not have a few million changes since the last stats update.
Why not?
Regardless of what activity there has been an update stats does the same amount of work. It reads the entire table, recomputes the statistics and replaces the old stats with the new. It's not a replace what has changed, it's a replace the entire stats blob.
Could it be the Stored Procedures's that are being recompiled when the update stats is running?
August 22, 2011 at 10:17 am
Recompiles aren't logged.
Besides, procedures aren't recompiled when the statistics update. They recompile next time they run if they used any of the statistics that changed.
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
August 22, 2011 at 10:20 am
GilaMonster (8/22/2011)
Recompiles aren't logged.Besides, procedures aren't recompiled when the statistics update. They recompile next time they run if they used any of the statistics that changed.
Is the engine smart enough to see when stats really changed?
Let's say that you run update stats but the distribution didn't really change, nor the min / max values.
Do you still get a plan recompile at next run?
August 22, 2011 at 10:23 am
Ninja's_RGR'us (8/22/2011)
Let's say that you run update stats but the distribution didn't really change, nor the min / max values.Do you still get a plan recompile at next run?
Yes.
Getting way off topic here....
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
August 22, 2011 at 10:25 am
GilaMonster (8/22/2011)
Recompiles aren't logged.Besides, procedures aren't recompiled when the statistics update. They recompile next time they run if they used any of the statistics that changed.
Good to know. That is not very clear in the BOL.
It just says"
sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply