August 26, 2016 at 1:50 am
Hey PoSh experts,
So I need to get the max size setting of all SQL databases in the primary filegroup, excluding the system databases and the tempdb. I was told I'll have to loop through the DB's to get the maxsize setting of the database, then add them up. No. You have to go into the the filegroup -> files and get the max size there. I could not for the life of me work out how I can do this, will I did, but it didnt work properly...
foreach($blah in $Files)
{
$Server.Databases.filegroups.files | select Name, Maxsize | Format-Table -AutoSize
}
Now I have limited experience with foreach loops, I gotta learn to use them, but this gave me the result 10 times. I know why, I just didnt know how to stop it from doing that. The other issue was it included the system and tempdb databases (Tempdb has multiple datafiles). At one point I created an array which I thought I could use to exclude the files....
$ExcludeFiles = @("TempDB", "master", "model", "MSDB")
...if this was done correctly, then I could not get it to work in the foreach. I also had trouble getting it to be effective in a pipe.
After much tooing an froing(?), I ended up with this...
# Ok, so the below code will get the max size of the user databases (leaving out system databases and tempdb), and adds it together.
$TotalDataFileSize = ($Server.Databases.filegroups.files | select Name, Maxsize |Where-Object {($_.name -notlike '*TempDB*'-and $_.name -notlike 'master' -and $_.name -notlike '*model*' -and $_.name -notlike '*MSDB*')} | Measure-Object -Sum Maxsize).Sum
# Now it converts it from KB to MB.
$TotalDataInMB = $TotalDataFileSize / 1MB
# This variable is the total in MB.
$TotalDataInMB
Learning tonnes, but it ain't always belly laughs! TBH, this has been a frustrating experience with smo. I have to do the same for the logs, but they seem much easier to access and play with, so I do not think I'll have as much trouble.
Is there a better and neater way of doing this? Is this amateur hour? Or just another way of skinning a cat?
Regards,
D.
August 26, 2016 at 5:14 am
I can't really work out how your code produces anything, but I'm not a Powershell expert by any means.
I assume your $server object is an SMO server object that you have already created.
If so, this ($Server.Databases.filegroups.files) would return nothing, as Filegroups is a property of an individual database, not the Databases collection.
Try something like this:
foreach ($db in $Server.databases | where-object {$_.IsSystemObject -eq $False})
{
foreach ($fg in $db.Filegroups)
{
$fg.files | measure-object maxsize -sum
}
}
This isn't a full solution, it's just something to (hopefully) point you in the right direction, and there may be the odd syntax error, as I'm posting this from a device without access to Powershell.
Even if it does work, there are flaws because an unlimited max size will just return -1, which will completely skew you sums.
August 26, 2016 at 6:06 am
...and note that $_.IsSystemObject -eq $False still returns the Northwind DB???
August 26, 2016 at 9:12 pm
I guess I don't understand why anyone would use PoSh for such a thing when it's so easy to do in SQL Server itself. This also covers any NDF files you may have.
SELECT DBName = DB_NAME(database_id)
,LogicalName = name
,MaxSize = CASE WHEN max_size >= 0 THEN CAST(max_size/128 AS VARCHAR(20))+' MB' ELSE 'Unlimited' END
FROM sys.master_files
WHERE database_id > 4 AND type_desc = 'ROWS'
ORDER BY DBName,LogicalName
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2016 at 5:56 am
Jeff Moden (8/26/2016)
I guess I don't understand why anyone would use PoSh for such a thing when it's so easy to do in SQL Server itself. This also covers any NDF files you may have.
SELECT DBName = DB_NAME(database_id)
,LogicalName = name
,MaxSize = CASE WHEN max_size >= 0 THEN CAST(max_size/128 AS VARCHAR(20))+' MB' ELSE 'Unlimited' END
FROM sys.master_files
WHERE database_id > 4 AND type_desc = 'ROWS'
ORDER BY DBName,LogicalName
;
Looks more elegant to me.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
August 28, 2016 at 5:20 pm
Ian,
I assume your $server object is an SMO server object that you have already created.
If so, this ($Server.Databases.filegroups.files) would return nothing, as Filegroups is a property of an individual database, not the Databases collection.
...Yes that was correct, In future I'll add such code just for completeness. Thank you for your reply, as it happens you were very close but it did not sum. But your answer still helped me out. I think I was a bit burned out on Friday not to think if this myself, but your code did indeed point me in the right direction. I ended up with...
$UserDBs = $Server.Databases | Where-Object {$_.IsSystemObject -eq $False}
$SumOfMaxSize = ($UserDBs.filegroups.files | Measure-Object -Sum Maxsize).sum
...on Friday I had tried incorporating IsSystemObject, but could not work out how to get it to work when piping, I see where I want wrong not. So this gets me what I need, and much better than before.
Jeff & Gaz, yes I know what you mean, I suppose I could have invoked SQL and ran the command submitted, I suppose I've become a bit to determined to do the whole thing in Powershell, but as it is part of automation I kind of prefer it.
Thank you all for your replies, when I have finished I'll come back and talk about what I ended up automating.
Regards,
D.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply