I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations that usually prevented me from seeing the data that I really wanted in most cases. So at some point I created my own stored procedure to view the data I was most interested in almost on a daily occurrence. Now to be totally accurate I started with code that one of the SSMS reports uses to show data and index space usage. The code was not the prettiest so I did clean it up a little and modified it to better suite my needs but the core worked so I used it. I then added some additional queries to get other data that the original query didn’t have such as the file size and growth info. I also slightly modified one of the many variations of functions available out there that format numbers with commas for better readability. In this case it is simply called dbo.commas. Normally I have a database on every instance in which I keep custom procedures / functions etc. in which I can count on certain objects to exist so I can reference them in other objects. These examples are shown with no fully qualified path to the dbo.commas UDF so the UDF and the procedure would have to exist in each database or the one that you care to use it in. While that works you should consider doing one of two alternatives. Either add a utility database like I mentioned and put the two objects there or rename the procedure to have sp_xxx and put the objects in the master database and then mark them as system objects. That way you can call the procedure from the context of any database.
OK so enough about that lets see what the procedure does and how to use it. The procedure has two parameters @DBName and @ShowFileInfo. The @DBName parameter is pretty self explanatory and will dictate which database the results are for. The second parameter is really optional as it defaults to a value = 0. If the @ShowFileInfo parameter = 0 then only one result set will be returned which contains the relevant data for each table in the database. More on that in a bit. If a value of 1 is passed in then a 2nd result set is returned that contains information related to the size of the database, it’s files and growth. See further down for more details on both result sets. These are all acceptable ways to call the stored procedure:
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks'
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 0
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 1
EXEC dbo.ListDBSpaceInfo 'Adventureworks'
EXEC dbo.ListDBSpaceInfo 'Adventureworks', 1
Here is what the results would look like if you ran it against the Adventureworks database.
The first result set lists each table showing the size in MB’s for the reserved, data and index space. The row counts and the Created and Modified dates. This is the information I find most useful the majority of the time. It orders the tables by Reserved MB but you may prefer by table name or something else. The code is easy enough to modify to sort however you like and you can even add an additional parameter to chose the sort by column. Again I use it this way most often so it is what it is.
The 2nd result set if you choose to return it will list certain information about the database and it’s files. It will show the current total size of all the data files which is pretty definitive by nature. However the next few columns are dependent on the max size and growth properties of each file. For instance if any of the data files are set to –1 which means unlimited growth the resultant column for Max Size will say Unlimited as will the Potential Free Space. If the files have a fixed size the total max size if then calculated and displayed along with that amount minus the current data usage to give you the potential free space. Basically that is how much free space the database can theoretically accommodate assuming there is enough space on the disks. The same holds true for the log file. These numbers are most useful if you have limits set but the information can give you a good idea of the overall condition of the data and log file usage.
Now I understand that none of this is information that wasn’t available to users before but I usually had to go to several different sources to get a comprehensive view like this. The purpose of this blog post was not to wow everyone with some rocket science code it was simply to share with others who may find it useful. I use it all the time so I figure there must be others who can take advantage of it as well and hopefully customize it to suite their needs like I have. Have fun everyone, here is the code: