You can look what the datafiles contain (data, indexes or free space) using the system stored procedure sys.sp_spaceused. If you have a lot of unusesed space which you want to reclaim, you can shrink files. Don't do that if the filegrowth is reoccuring, then you should keep the file sizes at the high level. But if you have a one-off job which has used up a lot of space in your datafiles, you might want to shrink the files. You could also have a look at the growth-operations on your files to see when they occured. I usually use the Standard report Disk Usage for the database for that, because I can never remember which management view contains which information :)
There's also a Disk Usage By Table report that shows the space taken up by each table. If the data file has grown a lot and recently it's probably one of the biggest tables that's caused it.
Building on what Magnus suggested, once you run the Disk Usage Report (Databases -> YourDB -> Reports -> Standard Reports -> Disk Usage by Top Tables) you will know which table or tables were growing. SQL doesn't natively log information that will tell you what caused the growth, but if you know it will grow again you can start up sql profiler to find out. Select RPC Procedures, SQL Statements, SQL Batches and then filter by those tables in the top usage. This will return any query that impacts your filtered table and give you more insight as to what is causing the growth.
19 People are following this question.
