THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

  • sp_spaceused Alternative


          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:


  • Speaking at University of Virginia

    The University of Virginia is having a fall conference that is free and open to the public on Tuesday November 13th. My good friend Andy Leonard and I will be speaking on various SQL Server related topics so if you are in the area and available that day you are most welcome to stop by. You can find more information and register for the conference here.

    Andrew J. Kelly

  • Backup File Naming Convention

    I have been asked this many times before and again just recently so I figured why not blog about it. None of this information outlined here is rocket science or even new but it is an area that I don’t think people put enough thought into before implementing.  Sure everyone choses some format but it often doesn’t go far enough in my opinion to get the most bang for the buck. This is the format I prefer to use:

    ServerName_InstanceName = Pretty self explanatory but lets look at it. Let’s say that the machine name is M432 and the instance is Dev2008. That would normally be W432\Dev2008 however I don’t like special characters so I change it to W432_Dev2008.  If it was a default instance it would be W432_W432. Some people (including myself) prefer to leave off the Server Name if it is a default instance but that is up to you. Since the default instance is always the name of the server it’s still pretty clear where it came from.

    BackupType = FULL, DIFF or LOG.  Nothing more is needed here.

    DBName = Full name of the database. One note here is that if the name has spaces I like to replace the space with some other valid character and some people prefer to remove the space altogether. Spaces in an object name is a whole debate in itself and I wont go there now Smile.

    DateTimeStamp = yyyymmddhhmmss. This allows me to know exactly when the backup started just by looking at the name and makes it unique as well. I don’t know any one who takes two backups of the same db in less than a second so this convention works to avoid file name conflicts.

    _nn = The individual number associated with backing up to multiple files for a single backup operation. Typically backing up to multiple files for a FULL or DIFF backup can be more effecient with larger dbs so appending a number from 01 to nn ensures uniqueness as the rest of the name will be the same. If it is a single file then you can simply use 01 or omit that part altogether.

    .xxx = I also like to use the extension to identify the tool used to create the backup file. For native SQL Server backups I use .bak regardless of the type of backup (Log, Diff or Full). For backups done using a 3rd party utility such as the one from Red-Gate I would use .sqb and so on.

    This naming convention allows me to do several things. First it ensures each backup file will have a unique name. Second it allows me with a quick glance to see where the backup originated, what type of backup it is, which database it is for, when the backup started and which tool was used to create the backup.  So again there is nothing particularly new to this approach but I often see the naming falling short of this and generally only having the DB name and timestamp. Why not take the extra few steps to ensure you get the most out of your naming that you can. The code to generate the whole file name is pretty simple and can be done dynamically so why not go this route? OK there are always exceptions so let’s not start a debate war Smile.

    Have fun,


  • Speaking at SQL Saturday #146


    For any of you up in the New England area that are looking for some good and free SQL Server training you may want to check out the SQL Saturday this fall in southern NH. More specifically the event will be held in Nashua NH on October 20th 2012. There is a wonderful cast of speakers including myself (shameless plug Smile ) with a wide range of topics of which I am sure everyone can find a few topics they are interested in.  I hope to see some familiar faces from my old stomping ground and a few new ones as well. See you all there.


  • Speaking in Raleigh NC 9-20-2011


    If you are in the area I will be speaking at the Triangle SQL Server Users Group meeting on Tuesday the 20th of September 2011. The session is on TempDB Best Practices and all of the details for the topic, directions etc. can be found here.  Be sure to register so they have enough food.

    Thanks and hope to see you all there,


  • Speaking at the Charlotte Users Group


    I will be giving a presentation in between Earthquakes and Hurricanes in Charlotte NC. this Wednesday the 31st of August on Understanding Indexes in SQL Server 2008. If you are in the area please drop by and say hello. You can find out more information and register for the event here.


  • Finding IP Addresses With CMS


    I was just tasked with putting together a list of all the IP Addresses of the SQL Servers that one of my clients deals with on a daily basis. In this case there was over 30 of them and while I could ping each one individually I found an easier way that you might find useful as well if you are in the same circumstance. I figured there must be a column in one of the DMV’s that shows the IP Address and sure enough the first DMV I looked at had just what I was after. The local_net_address column of the sys.dm_exec_connections DMV shows the following information as taken from BooksOnLine:

    Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider. Is nullable.

    So chances are on a server with several connections you will get what you are looking for with the following query:

    SELECT DISTINCT local_net_address
        FROM sys.dm_exec_connections
            WHERE local_net_address IS NOT NULL ;

    Now this doesn’t do me much better than pinging each server unless I have a way to query them all at once. That is where the under utilized feature of SQL 2008 comes into play called the Central Management Servers.  I won’t bore you with my own explanation of that feature as it is well documented already. Here is one place to look.  But suffice to say that with this feature I can register all my servers and run a single query against them all at once. I then copied the results and pasted them into a spreadsheet and there you go. This is a pretty simple concept but I know people look for this enough that I thought I would share it.



  • Speaking in Richmond VA


    I meant to blog about this many days ago but such is life right Smile.  In a few hours I will jump into my truck (wish it was the Harley) and drive up to Richmond Va. to see my good friend Andy Leonard and the other folks at the Richmond SQL Server Users Group. I will be speaking on the topic of Understanding Indexes In SQL Server 2008 and hope to see a bunch of you there. Directions and more details are listed on the web site.



    SolidQ Home:                       SolidQ Blog:

    SolidQ Courses:                     The SolidQ Journal:

  • The latest edition of the SolidQ Journal is now available


    The SolidQ Journal is published monthly by SolidQ and packed with lots of good information related to SQL Server and related topics. If you haven’t already signed up to get these each month then have a look here.  It’s a free resource that only cost’s you the time to read it Smile.



  • Speaking in Columbia SC


    I just wanted to let anyone in the area that I will be presenting a session at the Midlands PASS Chapter in Columbia SC. on Tuesday night the 14th of June 2011.  The title is “Understanding Indexes in SQL Server 2008” and you can find more details here.  Hope to see you all there. Make sure you RSVP so there is enough food Smile.


  • SQL Server 2008 R2 Analysis Services Operations Guide

    Some members of the SQL CAT Team just released an Operations Guide for SQL 2008 R2 Analysis Services that can be found here. While I am not a SSAS person by any means this looks pretty cool and worth while for anyone who does work with it so have a look.

    Andrew J. Kelly

  • New Book on the Query Optimizer


    A friend of mine Benjamin Nevarez has written a book on the inner workings of the Query Optimizer and it was just released. I read a preview and was definitely impressed so I expect the full version to be just as good. More details can be found here and it is available at Amazon here as well. I hope you enjoy it.


  • Practical Performance Monitoring and Tuning


    My next performance class will be in San Francisco, CA on May 9th thru the 11th. I haven’t been to San Francisco in about 10 years and I am looking forward to getting back there. The material is a bit refined over the first class and I added a few more real world examples so it should be a fun and educational 3 days. If you want to spend a few days with me in San Fran have a look here for more details: 

           Practical Performance Monitoring and Tuning



  • System Center Advisor Collection Details


    The System Center Advisor team (formally Project Atlanta) now lists some pre-release documentation on exactly what information they collect and how you can view it before it gets uploaded. So if you are wondering what they collect you can have a look at the downloadable spreadsheet and see for yourself. Obviously this may change as it is pre-release stage and I expect more items to be collected as time goes on but this is a great start. Have a look


  • Speaking at SQL Saturday #71


    I am getting prepped to head up to Boston on Friday the 1st for the SQL Saturday #71.  I am really looking forward to seeing the New England folks as it has been a few years since I left.  I just hope this crazy Noreaster storm doesn’t mess up my flights. The north east is supposed to get between 6 to 12” of snow with high winds. Not a good combination for flying Smile.  I hope to see you all there.


This Blog


Privacy Statement