October 19, 2011 at 9:30 am
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
October 19, 2011 at 9:34 am
Any table with blobs? or datatype (MAX)?
Been doing lots of deletes lately?
October 19, 2011 at 9:39 am
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.
October 19, 2011 at 9:43 am
Any process that drop and reloads something?
Do you reindex all tables in a job?
Do you have heaps?
October 19, 2011 at 9:52 am
whats the growth factor on the database mdf file?
when you back the database up, how big is the backup?
---------------------------------------------------------------------
October 19, 2011 at 9:58 am
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
October 19, 2011 at 10:01 am
That can't be. When did the backup go from 3-4 Gb to 20?
October 19, 2011 at 10:13 am
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.
---------------------------------------------------------------------
October 19, 2011 at 10:15 am
Ninja's_RGR'us (10/19/2011)
Do you have heaps?
October 19, 2011 at 10:25 am
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?
October 19, 2011 at 10:29 am
Run the code found here and post the results => http://sqlskills.com/BLOGS/PAUL/post/Survey-nonclustered-index-counts-(code-to-run).aspx
Who was working during the weekend and what where they doing?
17 GB just doesn't happen overnight.
October 19, 2011 at 10:31 am
A heap is a table without a clustered index. Reindexing would not defragment the base data.
Views occupy no space, they are just queries.
heres a couple of queries to run in the database which give space per table. then you can start to see where the data is:
select o.name, sum(i.reserved) as total_in_pages
from sysobjects o inner join sysindexes i
on o.id = i.id
group by o.name
order by total_in_pages
--OR
select convert(char(30),o.name) as 'current table (with indexes)', ceiling(sum(i.reserved*8/1024)) as total_in_MB
from sysobjects o inner join sysindexes i
on o.id = i.id
where o.type = 'U'
group by o.name
order by total_in_MB desc
---------------------------------------------------------------------
October 19, 2011 at 10:35 am
george sibbald (10/19/2011)
A heap is a table without a clustered index. Reindexing would not defragment the base data.Views occupy no space, they are just queries.
heres a couple of queries to run in the database which give space per table. then you can start to see where the data is:
select o.name, sum(i.reserved) as total_in_pages
from sysobjects o inner join sysindexes i
on o.id = i.id
group by o.name
order by total_in_pages
--OR
select convert(char(30),o.name) as 'current table (with indexes)', ceiling(sum(i.reserved*8/1024)) as total_in_MB
from sysobjects o inner join sysindexes i
on o.id = i.id
where o.type = 'U'
group by o.name
order by total_in_MB desc
I'd run those for the system tables as well... just in case
October 19, 2011 at 10:37 am
The backup file is the truest indicator of data pages/extents. Whatever is being reported by the *_spaceused procs is incorrect somehow.
I wonder if you have some other structures, perhaps in a different schema or in a table you don't have rights to or is excluded from the procs you are running?
That or fillfactor is perhaps low. However that should still show up in the reserved space.
What does this return?
SELECT [System Table Name], (SELECT ROWS FROM SysIndexes S WHERE S.Indid < 2 AND S.ID = OBJECT_ID(A.[System Table Name])) AS [Total Rows], [Total Space Used in MB] FROM
(SELECT QUOTENAME(USER_NAME(so.uid)) + '.' + QUOTENAME(OBJECT_NAME(si.id)) AS [System Table Name],
CONVERT(Numeric(15,2),(((CONVERT(Numeric(15,2),SUM(si.Reserved)) * (SELECT LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total Space Used in MB]
FROM SysIndexes si (NOLOCK) INNER JOIN SysObjects so (NOLOCK)
ON si.id = so.id AND so.type IN ('U')
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(so.uid)) + '.' + QUOTENAME(OBJECT_NAME(si.id))
) as a
ORDER BY [Total Space Used in MB] DESC
October 19, 2011 at 10:39 am
Run the scripts for parts I and II from this article and let us know which script it correlates to in your server (of the two you mentioned)
http://jasonbrimhall.info/2010/05/25/space-used/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply