OK, so there are several ways to crack this particular nut, some more elegant than others.
You can use WMI (oof), PowerShell (oofoof) or maybe even SQLServer DMVs (tell me more).
I’ve never really got to grips with PowerShell – its on my list, for sure – but there are some occasions where PowerShell might not work for you, for example in restricted environments where you don’t have access to the OS directly. Here’s a way of getting database and mount point space information if you only have ‘public’ access to master…
DMVs are one of my favourite things ever. There’s just so many of them these days and we’re getting more all the time. My *other* favourite thing is CROSS APPLY – also known as the ‘fairy join’ – because I don’t know what it does and yet I can use it to get information that might be hard to join up otherwise !
Let’s put these two to work together to get a list of databases files, where they are, and how much space there is free on the disk where they reside:
–All database mdf mount points and disk free space in MB
SELECT DISTINCT db_Name(f.database_id) AS [Database], volume_mount_point, (total_bytes / 1024) /1024 AS [Total MB], (available_bytes / 1024) / 1024 AS [Available MB]
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
WHERE f.file_id = 1
|
Database |
volume_mount_point |
Total MB |
Available MB |
|
tempdb |
H:\\SQLTemp\\ |
557733 |
545029 |
|
MyDB1 |
H:\\SQLData\\ |
12046999 |
346111 |
|
MyTestDatabase |
H:\\SqlData\\ |
12046999 |
346111 |
|
OtherDB1 |
H:\\SQLLogs\\ |
334638 |
142694 |
You’ll see here that I’ve taken the easy route and used file_id = 1. That’s the MDF – the primary database file – and you *must* have one of those. Oh, and I can see that someone created a database with its MDF on a mount point intended for transaction logs. I’ll follow that one up later…
A small tweak, and you’ve got the same information for log files (file_id = 2, the LDF);
|
Database |
volume_mount_point |
Total MB |
Available MB |
|
tempdb |
H:\\SQLLogs\\ |
334638 |
142694 |
|
MyDB1 |
H:\\SQLLogs\\ |
334638 |
142694 |
|
MyTestDatabase |
H:\\SQLLogs\\ |
334638 |
142694 |
|
OtherDB1 |
H:\\SQLLogs\\ |
334638 |
142694 |
That’s better – everything’s lined up nicely and where it should be, except maybe tempdb’s transaction log appears to not to be on dedicated disk. Another item for the backlog…
You might be wondering why I’ve used the DISTINCT keyword in the script. That’s because if you have multiple NDFs (secondary database files) you only really want the one entry back. I haven’t included a script for NDFs (file_id > 2) because I’m not in control of where *you* put them. Besides, giving you everything takes some of the fun out of it, don’t you think ?
Back soon…
How to **** everything up so far it ceases to be funny
So, apropos of a complete breakdown in service, here\’s a few handy tips and action points to make sure your legacy lives on in the most psychologically painful way possible.
They also have the useful side effect of demoralising your team so much that they actively consider leaving a good job and a great employer because they\’re unchallenged, unvalued, and unheard.
- When choosing a new technology for your product or service, actively consider how badly you messed it up last time, and seek at all times to replicate those \’winning\’ strategies.
- Consider how the new technology can be bent to your will.
- How will you hobble performance ?
- How will you make your new design less resilient ?
- What\’s the lowest level of function that you can provide and could you push that bar even lower ?
- When putting together your team, get the brightest minds and force them into scoping meetings that last for weeks but don\’t actually have an output.
- Getting money to do things is hard – always try to foster a \’who will pay for this?\’ mindset.
- Embrace new ways of working, but only as far as they support and perpetuate your existing business practices – no one likes change.
- If change *must* happen, make sure it\’s delayed for as long as possible, but announce delays in small increments (or better still, not at all).
- If your customer is important, make sure that everything is process-centric – consistent customer service is better than good customer service.
- You\’re a person outside of work – when you enter the workplace, don\’t let on that you\’re a functioning member of society.
- Never ask a direct question – words mean far more than actions.
- If buying a product off-the-shelf, make sure that customisations you implement mean that it can never be upgraded – you made it better, so that\’s how it\’ll stay. This is especially important if considering SaaS.
- Ask your user community what features of technology they\’d like to see, then implement policies or processes that mean those goals can never be reached.
- In the event of your solution being accidentally viable, create management and authorisation frameworks so labyrinthine that people give up trying to request enhancements or alterations.
- Remember, even a delivery of 10% of the promised functions equates to a 100% successful delivery.
- Celebrate success with members of staff who had little or no input and who in all likelihood were not even aware of your project.
- If you\’re making progress, claim that one in front of senior management – your team is all behind you.
- If you\’re not making progress, micromanage. Two-hourly status calls are a great way to focus the team on solving problems and removing time-sapping blockers.
- When preparing presentations, consider your audience. Put as many words on a slide as you can, and ensure each slide contains information on at least three unrelated topics.
- When presenting, always stand behind your screen. Make sure to say \’I\’m not just going to read from the slide\’ and then read from the slide.
- Block-book 15-seater meeting rooms well in advance, then use them for 1-to-1\’s every other week.
- Bookend each workday with all-team meetings with titles like \’DIARY-BUSTER\’ and \”ALL MUST ATTEND\’.
- Send those awkward \’bomb-shell\’ emails out of hours. Copy in as many people as you think might make it look important.
- Above all, remember there is no \’I\’ in \’Team\’. There is an \’I\’ in failure, but that\’s not an option.
This is by no means an exhaustive list, but hopefully they can help you be a better version of you if you\’re into that kind of thing. Or you could just have read it, recognised some behaviours, smiled nervously, nodded sagely and moved on.
The decision, as they say, is YOURS !
Back soon….
The Modern Dataist 