Have you ever struggled to figure out why your SQL Server database is using so much space? If your database is hosted on a dedicated server then perhaps it’s not an issue for you. But what if your on a server with limited storage? For example if you have a web site that is on a shared hosting plan then chances are you have a limited amount of space for your SQL Server database. If your database grows over the plan allotment then you will probably be looking at an overage fee unless you can clean up some space within the database. Regardless of where your database is hosted it’s always a good idea to keep your database growth under control.\r\n\r\nWith a SQL Server database, the transaction log is usually the first place to look at when you need to free up space. The transaction log is used for restoring the database in the event of a system crash to the very minute before the crash occurred. If your web site is not being used for ecommerce then you probably do not need “point in time” recovery. By switching the SQL Server recovery model to “Simple” you’ll be ale to free up a lot of space that was being used by the transaction log.\r\n\r\nSo let’s assume you’ve already switched the SQL Server recovery model to minimize the size of your database however your hosting provider is still telling you your database is over the allotment and you need to clean up more space. With a SQL Server database you could “shrink” it but that’s a topic for another day. So we’ll focus on the tables in the database and see if you can clean out some legacy data.\r\n\r\nThe logical question is how does one determine which tables in a SQL Server database are using the most space? Well SQL Server has a stored procedure called sp_spaceused which can show you the total size of your database or alternatively how much space a table is using. For example you can check a table called products like this:\r\n\r\nsp_spaceused products\r\n\r\n
\r\n\r\nThis is very handy however not very practical if you have a lot of tables to test. Fortunately thanks to Bill Graziano over at SQLTeam.com this important but mundane task is a now breaze. They created a SQL script called bigtables.sql that you can run to list the 25 biggest tables in your database. It will quickly and clearly show you how much space each one of your tables is using.\r\n\r\nLet’s take it a step further by creating a stored procedure to run the script. This way the functionality is always ready to run when we need it. Here is a script that creates a stored procedure called sp_bigtables. When we run the stored procedure we see these results which clearly show which tables are using the most space. The picture below only shows the first 30 tables however the script displays every table in the database.\r\n\r\n
\r\n\r\nNow that you’ve identified the biggest tables in SQL Server database you can focus on cleaning out some legacy data. Be sure to take a backup first before deleting any data from your database.\r\n\r\nOne final tip is that you should run DBCC CHECKUSAGE (‘databasename’) on your database first before running the script above so that you get the most accurate results.\r\n\r\n
\r\n\r\n
