Eventually you’ll be faced with running out of space on a SQL server. Usually, before you get more drive space, you’re asked to find out if you can get rid of any information you’re holding onto in the server. You come up with a list of tables in databases that you feel you could remove for now, since you have a backup of them.
You do have a backup, right?
The problem is, you only want to delete those tables that will really make a difference. But how can you tell which ones will free the most space if you were to delete them?
If you want to find the size of your tables, then you’ll need to know about the built in stored procedure sp_spaceUsed. You pass it the table name in questions, and it returns the number of rows, now much space is reserved (in KB), how much of that is used by the data, how much is used by indexes, and how much is pre-allocated space.
exec sp_spaceused @Name
That’s all well and good, but I have thousands of tables to deal with. How can I run that for all of the tables in a database, and sort it by size?
DECLARE @Name NVARCHAR(100) CREATE TABLE #tableInfo (Name NVARCHAR(100) , RowsCount INT , ReservedKB VARCHAR(15) , DataKB VARCHAR(15) , IndexKB VARCHAR(15) , UnusedKB VARCHAR(15) ) -- Run a cursor through all of the tables DECLARE tableCursor CURSOR FOR SELECT name FROM dbo.sysobjects WHERE type = 'U' OPEN tableCursor FETCH NEXT FROM tableCursor INTO @Name WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #tableInfo EXEC dbo.sp_spaceused @Name FETCH NEXT FROM tableCursor INTO @Name END --end loop --clean up CLOSE tableCursor DEALLOCATE tableCursor UPDATE #tableInfo SET ReservedKB = REPLACE(ReservedKB, ' KB', '') , DataKB = REPLACE(DataKB, ' KB', '') , IndexKB = REPLACE(IndexKB, ' KB', '') , UnusedKB = REPLACE(UnusedKB, ' KB', '') SELECT * FROM #tableInfo ORDER BY DataKB DESC DROP TABLE #tableInfo
You should now have a nice table showing you your largest tables first. This can also be useful for tracking how tables grow over time. If you were to use this to write a report monthly, you could compare one month to the next and see which tables are growing more rapidly… That would be useful if you had multiple hard drives in your server. You could physically allocate your largest growing databases to your largest discs.
Useful information indeed!
Well, if you have any questions, please let me know. I’m here to help you learn all about SQL!