April 7, 2014 at 4:04 am
Morning,
I have a really odd problem and am not sure what could be the issue.
We have a database called "ih".
On Saturday the mdf file was 41.8 GIG. Today it is currently 79.1 GIG.
I've restore the DB to a new db from Saturday to compare to see where the growth is happening. That db is called "ih_restore".
I've found the following script which reports the size of each table of the database -
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT *
FROM #t
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t
DROP TABLE #t
I've run it for ih and ih_restore and can see that the "reserved" and "data" fields are growing but no extra rows - so no inserts are happening in the database?? What or why will this be happening.
Example of csv file of a table that I've exported -
From ih -
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,1011704 KB,512424 KB,498648 KB,632 KB
From ih_restore
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,119808 KB,62960 KB,56088 KB,760 KB
So the em_comm_costing rows are 384191 in both but the data field has increased to 512424 from 62960.
I'm at a bit of a loss what would cause this?
The database is being mirrored as well, but not sure if that would be effecting the size?
April 7, 2014 at 5:00 am
Ryan Keast (4/7/2014)
From ih -
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,1011704 KB,512424 KB,498648 KB,632 KB
From ih_restore
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,119808 KB,62960 KB,56088 KB,760 KB
Any change in index?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 7, 2014 at 8:46 am
Page splits in your indexes?
As there is no increase in rows, is it possible some are being update or replaced?
April 7, 2014 at 9:27 am
Looks like it's linked to my Rebuild Index Task.
Everytime I run it - the database balloons in size.
Via the GUI I have the Change Free space per page to 90%.
So is it normal for the database to double in size whilst the reindexing is happening as it got to about 106 GIG (the db is 48 Gig) before I ran out of space on the server.
April 7, 2014 at 9:42 am
During Rebuilding of index, First index creates a copy of the exisiting index (for backup purpose) and does the rebuilding process.
Are you doing rebuilding process for all index or selected index ??
April 7, 2014 at 9:44 am
1) It could be caused by HEAP table activity where you insert and delete rows over and over or update rows to be larger than they currently are.
2) By specifying 90% FREE space you are making each page only 10% full. That WILL explode out your table/index sizes!
3) Are you perchance using GUIDs?? They can lead to massive index fragmentation and extra space usage.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 7, 2014 at 11:06 am
Ryan Keast (4/7/2014)
Looks like it's linked to my Rebuild Index Task.Everytime I run it - the database balloons in size.
Via the GUI I have the Change Free space per page to 90%.
So is it normal for the database to double in size whilst the reindexing is happening as it got to about 106 GIG (the db is 48 Gig) before I ran out of space on the server.
For really large tables, I've found that changing the Fill Factor to 90% is pretty much a waste of space. If the clustered index follows the best practices of being narrow, unique, ever increasing, unchanging, then you'll not very often have to worry about page splits unless you have variable length columns that start off empty or skinny and get updated to something larger.
As for the growth in the reserved but not used area that you speak of, like someone else said above, the old index is preserved until the new index is commited for any table over 128 extents (which is only 8MB). That means that you'll have at least 110-120% growth equal to your largest clustered index during an index rebuld of the clustered indexes. If you don't have SORT IN TEMPDB = ON for the rebuild, you'll use even more space in the given database.
If you have a bunch of very wide non-clustered indexes on the table, they can be nearly as bad as the clustered index. If you're using the ALL switch during your rebuild of all indexes on a table, you stand a pretty good chance of increasing your database size by 110-120% of all the indexes on that table including but not limited to the clustered index.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply