Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

database file size growing but rows / tables not Expand / Collapse
Author
Message
Posted Wednesday, October 19, 2011 9:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:22 AM
Points: 10, Visits: 10
Not sure where to post this, but first off let me say this not a transaction log issue. The short explanation is that I have a 19 GB .mdf file really only has 4 GB worth of data in it, but thinks it is full.

And now the long explanation:
My issue is that I have a database with about 4 GBs worth of data in it, but when I use sp_spaceused (even with @updateusage='true') it gives me this:

reserved      data           index_size   unused
------------- -------------- ------------ -----------
19876160 KB 19471376 KB 388184 KB 16600 KB



But when I use the Disk Usage by Table standard report or dba_spaceused that I found here:
http://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm

The sum of everything for all tables is less than 4 GB.

The size of the .mdf file is about 19 GB now, it grew about 2 GB OVERNIGHT! There may have been a few new records but nowhere near 2 GB worth!

And here is the first ten rows returned by dba_spaceused:
Schema    TabName                    Rows      ReserverMB   DataMB    Index_SizeMB    UnusedMB
--------- -------------------------- --------- ------------ --------- --------------- --------
dbo tbl_order_audits 8889261 910.484 780.883 129.289 0.313
dbo tbl_orders 169413 193.742 140.219 53.008 0.516
dbo tbl_attribute_answers 454588 71.094 36.805 33.938 0.352
dbo tbl_alerts 261031 39.023 35.664 3.211 0.148
dbo tbl_financial_alert_log 168355 36.195 34.125 1.883 0.188
dbo tbl_alert_log 167005 36.188 34.086 1.898 0.203
dbo tbl_audit 404237 31.383 31.273 0.070 0.039
dbo tbl_customers 153515 28.945 28.875 0.063 0.008
dbo tbl_memory_widget_usage 103140 27.289 27.094 0.141 0.055
dbo tbl_Partner_Invt_xref 124024 23.125 6.117 16.789 0.219

Post #1192960
Posted Wednesday, October 19, 2011 9:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Any table with blobs? or datatype (MAX)?


Been doing lots of deletes lately?
Post #1192964
Posted Wednesday, October 19, 2011 9:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:22 AM
Points: 10, Visits: 10
No blobs (text / image, etc.), and the usage of the database hasn't changed. Its a database for our ecommerce site and there wasn't anything last night that would have run to cause 2 GBs worth of growth.
Post #1192970
Posted Wednesday, October 19, 2011 9:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Any process that drop and reloads something?

Do you reindex all tables in a job?


Do you have heaps?
Post #1192973
Posted Wednesday, October 19, 2011 9:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 5,879, Visits: 13,010
whats the growth factor on the database mdf file?

when you back the database up, how big is the backup?


---------------------------------------------------------------------

Post #1192980
Posted Wednesday, October 19, 2011 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:22 AM
Points: 10, Visits: 10
I have a job that rebuilds indexes that are more than 20% fragmented and it runs every night, but it's been running for over a year now. There is nothing that drops or reloads tables. Nothing new has been put in place in the last few days, when this started occuring.

The database is set to auto-grow by 10% and for the past couple of years had been steadily growing, but growing from say 3.5 GB to 4.0 GB.

If I do a full backup right now to a new file, the backup file is the same size as the .mdf (about 20 GB).

I even tried restoring yesterday's backup to a new database and then set the Recovery model to simple on the new database, tried shrinking it, etc. But the new database was still the same bloated size.

If I restore a backup from a week ago the restored database is under 4 GB
Post #1192989
Posted Wednesday, October 19, 2011 10:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
That can't be. When did the backup go from 3-4 Gb to 20?
Post #1192993
Posted Wednesday, October 19, 2011 10:13 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 5,879, Visits: 13,010
you had 2GB worth of growth because of the growth factor of 10%, the mdf was almost 20Gb, so it grew by 2GB. When it was only 3GB it would have grown by only 0.3GB. Thats the danger of % growth factors, the amount of growth rises exponentially.

If your backup is 19GB big, thats how much data you have in the database.

did the reindex change the fill factor? Otherwise a lot of data is being added to your database.


---------------------------------------------------------------------

Post #1193006
Posted Wednesday, October 19, 2011 10:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Ninja's_RGR'us (10/19/2011)

Do you have heaps?
Post #1193008
Posted Wednesday, October 19, 2011 10:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 23, 2012 10:22 AM
Points: 10, Visits: 10
I should probably have prefaced this post with the fact that I'm developer, not a dba.

I'm not sure what you mean by heaps. Do you mean like in memory tables? Such as DECLARE @tbl TABLE (...)? If so then yes but not anything since this started happening.

Friday the database was 4 GB, Monday it was 17+.
The only thing new in the database besides our routine stuff is the dba_spaceused proc and a view. Could a view (non-indexed) cause ridiculous growth overnight?

If there is really that much data in the database, then how do I find it?
Post #1193019
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse