Analysis Shows Incorrect Log File Size Values
EdCarden
Posts: 138 Silver 2
I juts checked the Analysis for Log Size for my primary DB on the server I am monitoring and I know for an absolute fact that SQL MOnitor is not showing the correct log file size values.
Looking at the Analysis for a range of the last 30 days it shows the log file hitting a max size of around 4GB with one exception where on one day it got much larger.
I know for a fact that the log file routinely is much larger on Sundays. Unless SQL Monitor was not running and according to the Anaylsis Chart it recorded data for the days in question, why would SQL Monitor not show the correct log file size???
The only reason I know the log file size is wrong is because I have my own custom SQL job that captures log file size and perecent used every hour. Using that data I discovered that SQL Monitors info for at least the Log file is wrong.
Any ideaas on why this is? I would speculate that of SQL Monitor is capturing the Log file size only at specific time(s) during the day then its possible the log file is redcued back down before SQL Monitor could capture its true size.
My job runs every hour and it shows the log file going up to around 18-20GB while SQL Monitor is showing it peaking at 4GB which is the minimiumum or initial size of the log file.
Thoughts? Ideas?
BTW - Of all the days the log file size was larger an not caught by SQL Monitor there was one day when SQL monitor caught the file at the larger size but all the other days showed the log file getting no larger then its intial size f 4GB.
Thanks
Looking at the Analysis for a range of the last 30 days it shows the log file hitting a max size of around 4GB with one exception where on one day it got much larger.
I know for a fact that the log file routinely is much larger on Sundays. Unless SQL Monitor was not running and according to the Anaylsis Chart it recorded data for the days in question, why would SQL Monitor not show the correct log file size???
The only reason I know the log file size is wrong is because I have my own custom SQL job that captures log file size and perecent used every hour. Using that data I discovered that SQL Monitors info for at least the Log file is wrong.
Any ideaas on why this is? I would speculate that of SQL Monitor is capturing the Log file size only at specific time(s) during the day then its possible the log file is redcued back down before SQL Monitor could capture its true size.
My job runs every hour and it shows the log file going up to around 18-20GB while SQL Monitor is showing it peaking at 4GB which is the minimiumum or initial size of the log file.
Thoughts? Ideas?
BTW - Of all the days the log file size was larger an not caught by SQL Monitor there was one day when SQL monitor caught the file at the larger size but all the other days showed the log file getting no larger then its intial size f 4GB.
Thanks
Comments
We get the values from a windows performance counter. You can see these by running Performance Monitor and selecting the counter "Log File(s) Size (KB)" from the "SQLServer:Databases" object.
You can also see this value by running the query:
select * from sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:databases'
AND counter_name = 'Log File(s) Size (KB)'
Out of interest, what query are you using to get the data?
We collect quite frequently (minutely) but because it's considered a "stable sample" we only store the value if it is different to the previous one.
Does this shed any light on the matter?
Thanks
Fiona
Another thing to check - have you tried running the analysis graph for a shorter time period - specifically over the hours where you know there is an issue.
In version 2.3 the graph resolution was lower (this is improved in v3.0) and therefore it may have resulted in the data not being displayed as expected when viewing a longer period.
Thanks, Fiona
The T_SQL code used to get the log info is as follows:
Yes
Since there is no more followup on my replies to the above questions does that mean that the RedGate answer to this is "Upgrade To SQL Monitor 3" ? If I do upgrade to SQL Monitor 3 does that mean I will then see more accurate Log file metrics or does it mean that future capturing of Log FIle metrics will be accurate but past days (what has already been captured) will remain inaccurate?
I believe the info being captured by SQL Monitor 2.X is wrong because it was not actually getting that data as frequently as it was supposed to. Thats because it seems to get the Log File Size right at certain points in time but is off, way off in some cases for the in-between times.
Comments?
Sorry for lack of reply yesterday. I was trying to get some good comparison information for v2.3 but hitting a few problems.
However I did some comparisons for v3.
I set up a custom metric using the DBCC code you provided (changed only so that it returns a single numeric value).
I then did some manipulation to get my log to change size (specifically I shrunk the db so the log file didn't have any extra space in it, and then ran a large insert with a commit at the end).
The custom metric code was:
CREATE TABLE #perf
(
dbname NVARCHAR(50),
logsize FLOAT,
logspaceused FLOAT,
statuscol INT
)
INSERT INTO #perf(dbname, logsize, logspaceused, statuscol)
EXEC ('DBCC SQLPERF(logspace)')
SELECT logsize * 1024.0
FROM #perf
WHERE dbname = db_name()
(note that I am using the "logsize" field from the DBCC SQLPerf results).
Both the custom metric (collecting every minute) and the built in Log Size metric tracked the log size in the same way, showing a big drop when I shrunk the database and then an increase when I ran my insert query.
Therefore I am confident that after upgrade to v3 the data will be collected and displayed correctly.
I am unsure at this point whether it's the v2.3 graphing that is causing problems, or the underlying data capture. Which one is it will affect the behaviour on upgrade.
I will do some more investigation today and get back to you.
Please note that because v3 is a major upgrade, you must have a support and upgrades package. Further details can be found here
http://www.red-gate.com/SupportCenter/C ... _Upgrading
Best regards
Fiona
I managed to do a similar exercise in SQL Monitor v2.3.
The actions I took were:
- Shrink the database
- Run a long insert query within a transaction and a commit at the end.
I saw results very similar to those with v3.0 I.e. it plotted the change in "log size" as expected (initially a decrease and then a rise).
The frequency of data collection was every 15 seconds so it seems unlikely that it would consistently miss the peak value.
However, please note because "log size" is considered a "stable sample", the value is only stored when it does not match the preceding one.
None of this however helps explain what you are seeing. Therefore could you:
- Confirm that you are using SQL Monitor v2.3
- Confirm that the metric you are looking at in Analysis is "Log size"
- Confirm that your own query is using the "logsize" field from the DBCC SQLPERF(logspace) output
In addition it's probably worth running Performance Monitor during the period in which you are expecting the log size to grow. Select the counter "Log File(s) Size (KB)" from the "SQLServer:Databases" object.
Once we have that information, we can hopefully figure out what is happening.
Thanks, Fiona
I do still have an active support plan for SQL Monitor so the upgrade to version 3 is not an additional cost in terms of dollars just in terms of time and effort which is a resource I am short on currently. That’s why I prefer a solution to sticks with not upgrading at least not for the short term. Then again a fix for version 2.3 that is lengthy and involved would be equally undesirable so in the end I may not have a choice but to upgrade. That said because you are not able to reproduce the problem in 2.3 that leads me to believe that my own problem with SQL Monitor getting/storing incorrect log size values will not go away after an upgrade making the upgrade effort of no value in as far as this issue goes.
I have given you the T-SQL I use to get the Log file metrics and so the only question left to answer is verifying what value(s) I am storing and the answer is all. I store all values returned by the code. The size I quote is form the ‘Log Size (MB)’ column from the results the DBCC call returns. I capture this info every 15 minutes which based on a prior posting by you, is less often then when SQL Monitor captures the log metrics so if anything my own stored results should be less accurate than those of SQL Monitor.
I will await to hear back from you on what you find out once you’ve completed the rest of the steps you’ve outlined in your last post.
Thanks for your help
Ed
Can you provide the following information so we can establish if it's an issue with the graphing or the data collection.
Run the following query to retrieve data for the relevant period.
SELECT *
FROM data.Cluster_SqlServer_Database_Storage_StableSamples_View
WHERE CollectionDate_DateTime BETWEEN '2012-03-01' AND '2012-03-28'
If you can adjust the from and to date as relevant to your situation and also filter by say the field Cluster_Name so we only get data for the server you are interested in. Alternatively can you let us know what the relevant server name is.
If you can then run an analysis graph for the period in which you are seeing the wrong values and click on "Export..." and also email that to us.
It would also be useful if you can run Performance Monitor against your server during the period in which you expect the log size to change to confirm the results are as you expect (details are in my previous posts).
Please can you email the all the results to fiona.gazeley@red-gate.com
It's also worth emailing us the log files so we can determine if anything was preventing collection during that period. Please see the following article on where to get your log files.
http://www.red-gate.com/supportcenter/c ... LogFilesKB
Many thanks
Fiona
To add to the above post, can you also email across the output (for the relevant time period) from your custom script.
Many thanks
Fiona
Done.
Done in a separet email (because I missed this post when I replied to the prior request).
Thanks