List of Disks and Free Space

Recently I have been supporting our move from TFS from 2010 to TFS 2012. The database instances is stored on a server that I do not have log on rights to. During the upgrade, it became clear that one of the tables was growing at a rapid rate. This is because rows were being moved from one table to another, and there were a lot of rows to move: I came back from my Christmas break on the 31st and apparently it had been running from the 28th!

So I needed to know how much space the disk had left and whether we needed to increase it. But as I did not have access to the server I needed to check how much space we had before requesting a disk increase. By running the stored proc below I was able to check how much free space we had left on the disks.

EXEC master xp_fixeddrives

I was able to check which disk it was by running the query in my previous post

SQLBits XI Dates Announced

SQLBits XI has been announced for the 2-4th of May 2013. Having taken place in London this year, it is moving back North to the Midlands at the East Midlands Conference Centre. Apparently there are great transport links, and being smack in the centre of England I guess it makes it convenient for everyone to get to.

If you work with SQL Server in any way, and you’ve never been to SQLBits I’d highly recommend it, because

  • it takes place over a weekend, so only requires one day away from the office
  • it’s free! So aside from room and traveling expenses, it’s a very low cost conference.
  • the quality of past speakers has been very high. SQL Experts such as Brent Ozar, Buck Woody, Ola Hallengren, Kevin Kline… the list goes on. I came away with a lot of ideas to try out at work, and certainly learnt of a few valuable resources out on the web.

Try and convince your boss that it is worth your while. Research the sessions that are taking place and outline the ones that are pertinent to your professional development; does attending help you achieve any of your objectives covered in your appraisal? If there are other people in your team who might also benefit from the knowledge learned, try to have a plan for how you intend to communicate what you have learnt to the team. At the moment they are not open for registration, they are just taking session submission, but keep checking back as registration fills up quick!


Introduction to Hekaton Part One: MVCC

Hello folks, welcome to a post focusing on one of the future features of SQL Server. SQL Server 2012 is barely a year old, yet there is already talk on a feature that is to ship in the next major release of SQL Server. The current name of this feature is Hekaton, which is Greek for 100. I believe the reason for this name is because of the performance boost that one online gaming company experienced when working with Microsoft in implementing the technology preview of Hekaton.
Continue reading “Introduction to Hekaton Part One: MVCC”

Disabling Flight Recorder Logging in SSAS

Hello folks, I hope you all had a relaxing holiday break.

Welcome to a quick blog on SSAS optimisation. There’s a few quick wins that you can do w/r/t SSAS that can optimise the instance. One of these has to do with a feature called the FlightRecorder log. This log is by default set to ON, but really, unless you are trying to diagnose an issue, it doesn’t really provide any benefit to just having it switched on writing to the log location, particularly if the log location is on the same drive as the data location. To optimize your server, it’s safe to turn the FlightRecorder off and it is simple enough to turn back on and start collecting data again.

Continue reading “Disabling Flight Recorder Logging in SSAS”

SQL Server Database File Size and Free Space

(There is a more up to date version of this script here)
Hey folks, and welcome to my first proper blog post. One of the things that I like to monitor through my daily checks in the file size and the free space of each file within the databases. During a busy loading period, some databases can grow massively, so it’s important to keep an eye on the growth. Through the UI, SSMS only gives us the size of the files and what the max size is that they can grow to, which is clearly not very useful. Fortunately,  a quick query on the dbo.sysfiles of each databases that we want to monitor gives us some info:


but this isn’t entirely too useful. For starters the size is in KB. This makes sense as databases store data in 8kb sized files. Whilst it may be OK for a small database like this one, our data warehouses are far to big for us to find sizes in KB useful. Also, we can infer the remaining space, but again it’s not too helpful by having to figure this out for ourselves.

Continue reading “SQL Server Database File Size and Free Space”

Hello world.

Despite possibly the lamest title ever, this blog is about my experiences as an IT Professional working for a Microsoft workshop in London. Longer terms there is going to be a series on managing Analysis Services through PowerShell, but as my skills range from SSMS/SSIS/SSAS/MSBuild/WiX…. the list goes on, I plan to post whatever obscure things I come across that may help others who turn to Google for the answers.

My boss tells me that one of the skills he looks for in a developer is the ability to search the Internet for the answers, and when you look at Books Online and all the blogs, articles etc provided by Microsoft, he could well be on to something there. I frequently turn to the internet to find out how other people have solved a problem, and though it may not always be sensible to C&P off the internet, if it can lead you to solve the problem then it can only be a good thing. My hopes for this humble little blog is maybe to provide a solution to a problem you are having.