December 5, 2016 at 8:43 am
Hello,
I'm not a DBA, but I'm trying to learn more about SQL Server database administration. I'm hoping someone can help me out with this.
Every month, we track the growth of a particular database. Each month, we document the Size value from the sys.master_files table. This past month, we noticed a large jump in the size value for the database, and we're not sure what the explanation could be.
As a general discussion, if you notice sudden jumps in the database size, what are the first questions you would be asking yourself, or the first steps you would take towards troubleshooting this?
Thank you for any assistance.
Edit: Crap, forgot to fill out the topic title to something more useful than "Troubleshooting," any way for me to edit that?
December 5, 2016 at 10:20 am
tarr94 (12/5/2016)
Hello,I'm not a DBA, but I'm trying to learn more about SQL Server database administration. I'm hoping someone can help me out with this.
Every month, we track the growth of a particular database. Each month, we document the Size value from the sys.master_files table. This past month, we noticed a large jump in the size value for the database, and we're not sure what the explanation could be.
As a general discussion, if you notice sudden jumps in the database size, what are the first questions you would be asking yourself, or the first steps you would take towards troubleshooting this?
Thank you for any assistance.
Edit: Crap, forgot to fill out the topic title to something more useful than "Troubleshooting," any way for me to edit that?
One of the things to look at would be the growth increment for the file and did the file grow and that is what you see in the sudden jump in size. That's often what it is and can burn you if you have growths set up as percentages as the growth increment just gets larger and larger.
Another thing would be any processes that ran since the last size check that would be importing or doing things that could increase the file size.
Sue
December 5, 2016 at 10:46 am
First, is it the data file (type = ROWS) or the log file (type = LOG) that's grown in size?
If it's the LOG file that's ballooned, then some large data modification operation, or a larger number of smaller DML operations, must have occurred in the interim.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 5, 2016 at 10:48 am
If it's the data file that's gorwing, then I would next run a query like the following to get allocation details on every table and index in the database.
declare @object_name varchar(180) = '%'
, @schema_name varchar(180) = '%';
select *
into #T
from
(
select
@@servername as server_name,
db_name() as db_name,
SCHEMA_NAME(o.schema_id) as schema_name,
o.nameAS object_name,
i.index_id,
isnull(i.name,'HEAP') AS index_name,
p.partition_number,
i.type_descAS index_type,
case i.is_primary_key when 1 then 'PRIMARY' else '' end as is_primary_key,
case i.is_unique when 1 then 'UNIQUE' else '' end as is_unique,
case i.is_disabled when 1 then 'DISABLED' else '' end as is_disabled,
min(o.create_date) create_date,
sum(su.user_seeks + su.user_scans + su.user_lookups + su.system_seeks + su.system_scans + su.system_lookups) read_count,
max(su.last_user_seek)last_user_seek,
sum(su.user_updates + su.system_updates) update_count,
max(su.last_user_update)last_user_update,
case p.data_compression_desc when 'NONE' then 'NO' when 'PAGE' then 'PAGE' when 'ROW' then 'ROW' end + ' COMPRESSION' as data_compression_desc,
sum(p.rows) as row_count,
cast(((sum(ps.in_row_used_page_count)) * 8192.0)
/ (1024 * 1024) as numeric(12,0)) as in_row_mb,
cast(((sum(ps.row_overflow_used_page_count)) * 8192.0)
/ (1024 * 1024) as numeric(12,0)) as row_overflow_mb,
cast(((sum(ps.lob_reserved_page_count)) * 8192.0)
/ (1024 * 1024) as numeric(12,0)) as lob_reserved_mb,
cast(((sum(ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_reserved_page_count)) * 8192.0)
/ (1024 * 1024) as numeric(12,0)) as total_mb,
cast((sum(ps.in_row_used_page_count + ps.row_overflow_used_page_count + ps.lob_reserved_page_count)
/ ((select sum(size) from sys.database_files where type_desc = 'ROWS') * 1.0))*100.0 as numeric(9,1)) pct_db_size
from sys.dm_db_partition_stats ps
join sys.partitions p
on ps.partition_id = p.partition_id
join sys.objects o
on o.object_id = p.object_id
and o.is_ms_shipped = 0
and schema_name(o.schema_id) like @schema_name
and o.name like @object_name
join sys.indexes i
on p.index_id = i.index_id
and p.object_id = i.object_id
left join sys.dm_db_index_usage_stats su on su.object_id = i.object_id and su.index_id = i.index_id
group by
SCHEMA_NAME(o.schema_id),
o.name,
i.name,
i.type_desc,
p.partition_number,
p.data_compression_desc,
i.index_id,
i.is_primary_key,
i.is_unique,
i.is_disabled
) x;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 5, 2016 at 12:29 pm
Thank you for the suggestions so far!
There have been questions about whether the size increase relates to the data or log file. I can confirm that the sys.master_files.size we're looking at is a data file (type = 0, .mdf files)
December 6, 2016 at 7:25 am
tarr94 (12/5/2016)
Thank you for the suggestions so far!There have been questions about whether the size increase relates to the data or log file. I can confirm that the sys.master_files.size we're looking at is a data file (type = 0, .mdf files)
This could be caused by index maintenance. If, for example, the fragmentation on a large index finally reached a tipping point, the rebuilding of a large clustered index could cause some serious growth in the MDF file because the original CI is preserved until the new CI has been successfully created and committed.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply