question

vivekchandra09 avatar image
vivekchandra09 asked

SQL Server databases become too big after adding

space on D and F drive. The space added were 250 GB and 500 Gb respectively. After that many of our databases grew 500%. What could be the reason?
space issue2008 r2
1 comment
10 |1200 characters needed characters left characters exceeded

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

500% of what? How big were the databases previously? Did they consume all of the space? I'm not sure I could see a reason for the database(s) to spontaneously explode in growth. There'd be a reason for the growth.
1 Like 1 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
- Check the growth setting for your datafiles, probably to high or setting is percent growth - check if you have temporary tables created in your database which are not cleaned-up, mostly a copy of a live table - check if index rebuild is done without the "sort_in_tempdb" option
10 |1200 characters needed characters left characters exceeded

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

narendba avatar image
narendba answered
you can find the individual file details internal space related stuff using the below script. select NAME = left(a.NAME,15), [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)), [FILE_MAXSIZE_MB] = convert(decimal(12,2),round(a.maxsize/128.000,2)),[FILE_GROWTH] = convert(decimal(12,2),round(a.growth/128.000,2)) from dbo.sysfiles a
10 |1200 characters needed characters left characters exceeded

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.