question

cornpoppy avatar image
cornpoppy asked

Data files has grown to a very large size

Hi how can i check why data files has grown to a very large size in primary filegroup ?
dbafilegroupdatabase-filesdba-developerdatabase-size
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have you inserted a lot of new records recently?
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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 :)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
askcoffman avatar image
askcoffman answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.