Shaun Mccran

My digital playground

03
O
C
T
2010

How to find out the size of data in MS SQL databases

If you are running Microsoft SQL server you may already be familiar with how the database file system can enlarge itself to astronomical proportions. There are 'Auto shrink' and 'Auto grow' options, but these are really not recommended for production environments.

So how do you go about determining how much space is being used by actual data IE records, and how much is being used by log files etc? This article will show you how to run a query that will return the size of all the tables in your database, and highlight a TSQL command that will clean up the file storage of a database.

If, like me, you have a database that appears to be much larger than the data it contains would indicate, then you probably have a large volume of data files on the disk.

There is a Transact SQL command that 'Shrinks the size of the data files in the specified database'. This will shrink the log files and the data files of the specified database. As an example I recently shrunk a 890mb database down to 15mb.

view plain print about
1DBCC SHRINKDATABASE
2 ( database_name [ ,target_percent ]
3 [ , { NOTRUNCATE | TRUNCATEONLY } ]
4 )

A full description of the command can be found on the Microsoft MSDN site here: http://msdn.microsoft.com/en-us/library/Aa258287

If the command above does not resolve your issue you can interrogate the database directly as to how many records there are in each table, and how much space each table is occupying.

view plain print about
1CREATE TABLE #temp (
2 table_name sysname ,
3 row_count int,
4 reserved_size varchar(50),
5 data_size varchar(50),
6 index_size varchar(50),
7 unused_size varchar(50))
8
9SET NOCOUNT ON
10INSERT #temp exec sp_msforeachtable 'sp_spaceused ''?'''
11
12SELECT a.table_name, a.row_count, count(*) AS col_count, a.data_size
13FROM #temp a
14    INNER JOIN information_schema.columns b on a.table_name = b.table_name
15    GROUP BY a.table_name, a.row_count, a.data_size
16    ORDER BY CAST(Replace(a.data_size, ' KB', '') AS integer) DESC
17DROP TABLE #temp

This code will create a temporary table and populate it with a set of records telling you how many records are in each table, and the size of each table.

TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
EV SSL Certificates's Gravatar I know this might sound a little arcanine but why not just stat the directory that contains the database.
# Posted By EV SSL Certificates | 13/10/10 17:13
Shaun McCran's Gravatar You could do, but often the log files are in a different place, also the server in question was a shared web server, so I didn't have access to the file system.
# Posted By Shaun McCran | 13/10/10 21:23
Back to top