November 20, 2017 at 4:24 pm
is there an easy way to monitor and alert when mdf ndf and ldf reach 80% capacity
November 20, 2017 at 4:58 pm
myukas - Monday, November 20, 2017 4:24 PMis there an easy way to monitor and alert when mdf ndf and ldf reach 80% capacity
80% capacity of what? Their current size or the size of the disk they live on?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2017 at 6:58 pm
80% of max usage if a limit and possibly if unlimited growth and grows by X amount not sure what X should be yet
November 20, 2017 at 9:02 pm
myukas - Monday, November 20, 2017 6:58 PM80% of max usage if a limit and possibly if unlimited growth and grows by X amount not sure what X should be yet
If it's for 80% of a DB limit, then it's easy. Have a look at sys.master_files. If you divide page counts by 128, that will give you values in megabytes.
If it's for % of disk space free because of unlimited or insanely high limit values, then you need to make a call to the operating system for disk free space information. That, of course, can be done many ways depending on which caveats you want to pay attention to. It could be done through PowerShell, xp_CmdShell, a Cmd Exec Task in a job (I supposes), or by using the undocumented xp_fixeddrives (which some people have some difficulty with "mount points" IIRC but works fine for the way our SAN is configured to present the drives to SQL Server).
The rest is just simple arithmetic play against those sources.
I do have a stored procedure I built that collects disk information from the 285 servers and other systems that I've been required to monitor but you wouldn't want to see it. You probably wouldn't want it because I did it on a dare just to tick off some serious anti-xp_CmdShell zealots that preferred to use PowerShell only and didn't know how to spell WMI by using xp_CmdShell to call PowerShell to call WMI in a loop to return the output through PowerShell only to the standard output device and then split the return of xp_CmdShell output to put in a table using persisted computed columns. 😉 It even finds "lost" removable media like CDs, memory sticks, etc. Then, to add insult to injury, I format it and color code the morning report from that derivative in HTML using XML trickery through T-SQL. Heh... it's an intentional "Tower of Babel" that breaks every sensitivity I could think of but is surprising fast. I also have 6 years of history stored in a big ol' monolithic table to tick off the people that say you have to partition a monolithic table of that size to get any performance out of it. If I ever get around to stop laughing about it, I'll rewrite it to skip the PowerShell part of it (I thinks that's slowing it down a bit) and do a partitioned view instead of a monolithic table just so I don't have to backup that which will never change after the first backup. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2017 at 7:20 am
what is the growth column showing me
November 21, 2017 at 7:26 am
there is no page counts would it be better to use size and max size if yes how
November 21, 2017 at 7:28 am
myukas - Tuesday, November 21, 2017 7:20 AMwhat is the growth column showing me
Either the amount the file will grow (if auto-grow is enabled) in 8KB pages or percentage. Check the is_percent_growth to determine which.
November 21, 2017 at 7:38 am
so i am selecting this select name,physical_name,max_size,growth,is_percent_growth from sys.master_files i think this is what i want
do you recommend putting this select in a job and run every so often and email or create some kind of trigger when things change
how do i show only mdf ndf and ldf that are 80% of max limit or are growing if they are unlimited
November 21, 2017 at 7:42 am
myukas - Tuesday, November 21, 2017 7:38 AMso i am selecting this select name,physical_name,max_size,growth,is_percent_growth from sys.master_files i think this is what i want
do you recommend putting this select in a job and run every so often and email or create some kind of trigger when things change
how do i show only mdf ndf and ldf that are 80% of max limit or are growing if they are unlimited
My recommendation would be to become a bit familiar with what's available before you guess at it's use. Look for "sys.master_files" in Google to see what the definition of each column is. Do the same for xp_fixeddrives. After that, it all becomes just simple arithmetic.
The reason why I'm not just writing a proc for you is because I'm trying to turn it into a learning opportunity. Stuff like this becomes really important as databases and the number of database instances grow. Give it a go and post some code that you've tried. We'll take a look and make some suggestions. And don't forget that the number of pages divided by 128 converts pages to megabytes.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply