Comments
Sort by recent activity
Hi 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 / comments
Hi 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.
SELE...
Hi Ed
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 / comments
Hi Ed
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 ve...
Ed
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 / comments
Ed
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...
Ed
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 / comments
Ed
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.
Y...
Ed
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 / comments
Ed
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 w...
Hi Ed
Can I in the first instance point you towards this post that has quite a lot of detail regarding getting data out of SQL Monitor using T-SQL. http://www.red-gate.com/MessageBoard/vi ... hp?t=12594
Best regards, Fiona / comments
Hi Ed
Can I in the first instance point you towards this post that has quite a lot of detail regarding getting data out of SQL Monitor using T-SQL.http://www.red-gate.com/MessageBoard/vi ... hp?t=1...
Thank you very much for your feedback.
Fiona (SQL Monitor Development Team) / comments
Thank you very much for your feedback.
Fiona (SQL Monitor Development Team)
gmjones - Would you be able to email through your log files to fiona.gazeley@red-gate.com
The following article explains how to get your log files. http://www.red-gate.com/supportcenter/c ... LogFilesKB
Many thanks
Fiona / comments
gmjones - Would you be able to email through your log files to fiona.gazeley@red-gate.com
The following article explains how to get your log files.http://www.red-gate.com/supportcenter/c ... LogFil...
I appreciate this is an old forum post, but I came across it whilst reviewing open bug reports.
I don't know if you are still using SQL Monitor and if so are aware of the new Custom Metrics feature? It's purpose is exactly for this type of alert.
I had a quick play and came up with the following T-SQL that you can use in a custom metric which would then force an alert if a new DB was created.
SELECT
CASE
WHEN create_date >= DATEADD(d, -1, GETDATE()) THEN 1
ELSE 0
END
FROM sys.databases
WHERE database_id = DB_ID()
If you create the custom metric and run it against "All User Databases" and then set up an alert if the value goes past "0" then this will alert for new databases created in the last day.
I appreciate this may not make sense if you haven't used the custom metrics feature, in which case I encourage you to check out the information on this feature in our support center. http://www.red-gate.com/supportcenter/C ... 469829.htm
Hope that's of some help.
Fiona / comments
I appreciate this is an old forum post, but I came across it whilst reviewing open bug reports.
I don't know if you are still using SQL Monitor and if so are aware of the new Custom Metrics feature...
The good news is we are working on this feature right now and it is due for release in version 3.0 of SQL Monitor.
We have an early access program (EAP) for version 3.0 and the next EAP release (due in the next couple of weeks) will contain a first implementation of this feature.
If you would like to participate, please email eapmonitor@red-gate.com.
Best regards,
Fiona / comments
The good news is we are working on this feature right now and it is due for release in version 3.0 of SQL Monitor.
We have an early access program (EAP) for version 3.0 and the next EAP release (du...