database file size growing but rows / tables not

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » database file size growing but rows / tables...

40 posts, Page 1 of 4

1 2 3 4 »»»

database file size growing but rows / tables not

Rate Topic

Display Mode

Topic Options

Author

Message

mwagner 55444

Posted Wednesday, October 19, 2011 9:30 AM

Grasshopper

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

Ninja's_RGR'us

Posted Wednesday, October 19, 2011 9:34 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 21,357, Visits: 9,533

Any table with blobs? or datatype (MAX)?

Been doing lots of deletes lately?

Post #1192964

mwagner 55444

Posted Wednesday, October 19, 2011 9:39 AM

Grasshopper

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

Ninja's_RGR'us

Posted Wednesday, October 19, 2011 9:43 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 21,357, Visits: 9,533

Any process that drop and reloads something?

Do you reindex all tables in a job?

Do you have heaps?

Post #1192973

george sibbald

Posted Wednesday, October 19, 2011 9:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 5,265, Visits: 11,194

whats the growth factor on the database mdf file?

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

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

Post #1192980

mwagner 55444

Posted Wednesday, October 19, 2011 9:58 AM

Grasshopper

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

Ninja's_RGR'us

Posted Wednesday, October 19, 2011 10:01 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 21,357, Visits: 9,533

That can't be. When did the backup go from 3-4 Gb to 20?

Post #1192993

george sibbald

Posted Wednesday, October 19, 2011 10:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 5,265, Visits: 11,194

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

Ninja's_RGR'us

Posted Wednesday, October 19, 2011 10:15 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 21,357, Visits: 9,533

Ninja's_RGR'us (10/19/2011)

Do you have heaps?

Post #1193008

mwagner 55444

Posted Wednesday, October 19, 2011 10:25 AM

Grasshopper

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 »

40 posts, Page 1 of 4

1 2 3 4 »»»

Permissions