One of our customers for my company asked a great question today. She wanted to know why her SQL Server database was filling up so fast even though she hadn’t added much content to her website and thought there was a problem on our end. She had increased the database size a couple hundred megabytes each time, and it filled up in a few hours. Of course this didn’t sound right to me, and to find out what was happening, I knew I had to iterate through each table to find the size and get an idea of what might be going on. Being busy and lazy, I searched the Internet for a quick solution but didn’t find any, so I decided to go to lunch. I figured that I would sit down and work through the problem later, but when I got back from lunch, my coworker figured it out and shared the solution with our staff which I will now share with you. It’s a few T-SQL statements which can be executed in a query window. You create a temp table, populate it by using the sp_msforeachtable stored procedure and then query it to find the result.
CREATE TABLE #Temp ( name nvarchar(128), [rows] char(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18) ) INSERT INTO #Temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT TOP 1 * FROM #Temp ORDER BY CAST(REPLACE(reserved,' kb','') AS int) DESC
That’s it. The query above will tell you which table is the largest in the database. With it, my coworker found out that there was a logs table and was able to inform our customer that something in her application was causing this table to fill up very fast.