May 5, 2015 at 12:02 am
Hi
I save Table size and recs. no every day. and check it some days.
...
insert into @t
exec sp_msforeachtable 'exec sp_spaceused ''?'''
...
But Today I saw sudden increase size in a table. about 128 MB in a day. (Average Growth fro this table was 4 or 5 MB in a day)
This growth was for Only 4222 Records. While for more number of records (about 7000) in yesterday we had only 2 MB GRowth!!!!
This Table information (Now):
sp_spaceused 'Table1'
Result:
name ---Rows --reserved --data
Table1--1021319--460328 KB --283104 KB
I Try to gess The reason.
I copy These new records to another table.
But The result was more strange : on new table the size of these record was : < 1 MB
I copyed All records to another table . The size was : 148 MB (while this is 283 MB in my real database)
Please guide me.
Thank you
May 5, 2015 at 12:27 am
Did you look at your transaction log to see what's going on in your database?
May 5, 2015 at 1:43 am
we dont have any special increase in log file.
what do your mean to look at transaction log? how to do it?
May 5, 2015 at 6:43 am
it could be due to index fragmentation from updates or inserts within the cluster. Moving the data all at once leads to less fragmentation, which would explain the differences in the other table. I'm not sure this is something I would sweat overly much. Why are you monitoring table size so closely?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 13, 2015 at 7:33 am
Use this to get more detail about the internal storage of your table (clustered index) and non-clustered indexes, which includes avgerage fragmentation percentage and average page density.
select
schema_name(o.schema_id)schema_name,
o.name obj_name,
i.name idx_name,
i.type_desc idx_type,
ps.alloc_unit_type_desc,
ps.record_count,
ps.ghost_record_count,
ps.forwarded_record_count,
ps.compressed_page_count,
cast(ps.avg_record_size_in_bytes as smallint)avg_recordsize_bytes,
cast(ps.avg_fragmentation_in_percent as tinyint)avg_frag_pct,
cast(ps.avg_page_space_used_in_percent as tinyint)avg_pageused_pct
from sys.dm_db_index_physical_stats
(DB_ID(), object_id('Table1'), NULL, NULL , 'SAMPLED') as ps
join sys.objects o on ps.object_id = o.object_id
join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
order by i.object_id, i.index_id;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply