January 23, 2013 at 12:02 am
Comments posted to this topic are about the item Capturing Baselines on SQL Server: Where's My Space?
January 23, 2013 at 1:16 am
Nice article, Erin.
In my test, [vs].[logical_volume_name] is blanc !
Win2008R2 / SQL2012DE (64-bit) SP1 (11.0.3000.0) with fixed drives.
So I suggest altering the last query to :
SELECT DISTINCT
( case [vs].[logical_volume_name]
when '' then vs.volume_mount_point
else [vs].[logical_volume_name]
end ) AS 'Drive'
, [vs].[available_bytes] / 1048576 AS 'MBFree'
FROM [sys].[master_files] AS f
CROSS APPLY [sys].[dm_os_volume_stats]([f].[database_id], [f].[file_id]) AS vs
ORDER BY Drive ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2013 at 1:31 am
Great article, Erin.
Also, lots of useful code in there.
Thanks for sharing.
ALZDBA (1/23/2013)
Nice article, Erin.In my test, [vs].[logical_volume_name] is blanc !
Win2008R2 / SQL2012DE (64-bit) SP1 (11.0.3000.0) with fixed drives.
Johan, you get blank logical_volume_name because your disk doesn't have a label. Try assigning a label and you'll see it appear in the results.
-- Gianluca Sartori
January 23, 2013 at 2:30 am
Gianluca Sartori (1/23/2013)
Great article, Erin.Also, lots of useful code in there.
Thanks for sharing.
ALZDBA (1/23/2013)
Nice article, Erin.In my test, [vs].[logical_volume_name] is blanc !
Win2008R2 / SQL2012DE (64-bit) SP1 (11.0.3000.0) with fixed drives.
Johan, you get blank logical_volume_name because your disk doesn't have a label. Try assigning a label and you'll see it appear in the results.
Checked it and ... You're right, Gianluca !
None of our servers have labels assigned to their drives. :blink:
Never had a use for it, except for documentation purposes, which always seem to be outdated by reality ....
( because of "temporary using this drive for" ... :crazy: )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2013 at 10:03 am
If you're going to capture per-file data in the first place, I'd highly recommend also capturing sys.dm_io_virtual_file_stats() data - then you can see which files/databases/disks are most "used" by a few metrics.
January 23, 2013 at 11:52 am
Nadrek (1/23/2013)
If you're going to capture per-file data in the first place, I'd highly recommend also capturing sys.dm_io_virtual_file_stats() data - then you can see which files/databases/disks are most "used" by a few metrics.
If you're looking for information about I/O activity, virtual file stats are absolutely what you need. For this post I targeted file size and used space for purposes of trending growth in order to create data-backed estimates for capacity planning. But if you want information about file access in terms of reads, writes, bytes read, and bytes written...as well as latency, then you should definitely capture virtual file stats. Thanks!
January 23, 2013 at 5:08 pm
The Data Collection does it for you as well - the snapshots.disk_usage table contains db space info.
August 13, 2013 at 9:52 am
Great stuff here Erin. One minor item I found. Listing 2 has this as part of the script the following line:
; INSERT [BaselineData2012].[dbo].[FileInfo] (
I beleive this should be
; INSERT [BaselineData].[dbo].[FileInfo] (
I recently took on a role in a new company, and I am setting this up in our dev and test environments right now. Thanks!
August 13, 2013 at 3:43 pm
You're right! It should be:
; INSERT [BaselineData].[dbo].[FileInfo] (
I will see if I can get the script updated, thanks for the catch!
July 22, 2014 at 6:22 am
Hi Erin,
I am not able to use your script. I am trying to capture 'AdventureWorks' data file and log file growth every week, using SQL agent job. Can you help step by steps please?
July 22, 2014 at 11:04 am
Hi-
The script to capture the information is in the post. What are you having a problem with?
Erin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply