June 12, 2014 at 11:08 am
Hi guys ,
We have once Database which is growing so fast and user told me that this database should not have this much big size and it doesn't have actual this amount of data. here is some details .
SQL server 2005
Database Actual Size - 62026.25MB ( Which is not suppose to be as per users information)
MDF file - Initial Size set 50563 MB with 100 MB growth Unlimited
LDF file - Initial Size is 11464 MB By 100 MB, Limited to 2097152 MB
here is result from sp_spaceused..
database_size unallocated space
62026.25 MB 5606.16 MB
reserved 46035544 KB
data
29495448 KB
index_size
16522752 KB
unused
17344 KB
since Initial size is set up 50563 MB that is why it is showing big even tough that much data is not filled ?
What could be the reason for growing so fast? how to find out what is taking larger space?
what action should take to stop growing fast?
Thanks for help
June 12, 2014 at 12:02 pm
Users say a lot of things...
A few questions to help you sort this out:
1) Do you track your DB growth? Do you have any idea how fast this DB is growing?
2) What is the Recovery_model? If FULL are you doing transaction log backups? You have a 60GB DB with a 12GB transaction log that seams kind of high.
To get an idea of what tables/indexes are taking up the most space you could run this:
with cte as
(
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
cte.TableName,
cast((cte.pages)/1024 as decimal(10,3)) as TableSizeInMB,
cast(((CASE
WHEN cte.used_pages_count > cte.pages THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by used_pages_count desc
That should help you get an idea of where the growth is occurring.
-- Itzik Ben-Gan 2001
June 12, 2014 at 12:12 pm
Thanks Alan ,
This Database used to be in simple recovery model and I did change it to Full now. I am taking T-log backup and full backup . here is top table and Index size from your query. whet he is saying is that data space as well as index space grew by 500% when the actual record count grew by 15% .
TableSizeInMB IndexSizeInMB
682.000 741.875
277.000737.117
252.000275.758
252.000275.594
229.000250.539
223.000243.781
220.000240.680
82.000892.813
82.000892.344
75.000811.781
73.000789.617
72.000779.625
65.000730.953
65.000726.852
63.000705.180
61.000687.898
61.000683.188
63.000464.469
62.000455.758
48.000549.047
48.000548.930
48.000548.797
48.000548.656
48.000548.297
55.000407.883
55.000407.195
54.000399.820
62.000316.625
78.000172.438
32.00033.258
25.00015.547
June 13, 2014 at 12:36 pm
This Database used to be in simple recovery model and I did change it to Full now. I am taking T-log backup and full backup .
No, no... My apologies if I was not clear. You want to set it back to simple unless you need to do point in time backups/restores. If you are NOT doing that then there is absolutely no reason to set your DB to full recovery mode. Take a look at this article for more details about recovery models.
The reason I asked is because: If your DB was in simple mode then the log should not get as big as it did. In simple mode the transaction logs will grow but SQL Server maintains the log file size for you. If you are in FULL recovery mode then the log file is only shrunk after you do a transaction log backup. If you are in simple mode and the transaction log is getting that big it could be because of a few things (e.g. open transactions).
For a good transaction log strategy I would suggest this free book (free pdf verison):
SQL Server Transaction Log Management by Tony Davis and Gail Shaw
[what] he is saying is that data space as well as index space grew by 500% when the actual record count grew by 15% .
This goes back to my original question - where is this person getting these metrics? Data and index space on what grew by 500% on what? ... and when? Was the DB size growing at by, say, 10% a month and then blew up by 500% last month? Last week? If so, did anything change during those times... that is what you need to sort out.
I noticed that those indexes are pretty large (several that are 500MB+) - are you storing images or blobs in your db?
-- Itzik Ben-Gan 2001
June 13, 2014 at 4:58 pm
logicinside22 (6/12/2014)
whet he is saying is that data space as well as index space grew by 500% when the actual record count grew by 15%
This sounds like page and extent splits. How often are you rebuilding or reorganizing indexes?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2014 at 9:05 am
Hi Jeff
Re-Org Index and Update Statistics job running Every night
June 18, 2014 at 9:14 am
Hi Alan,
thank you for detailed information I really appreciate your time to write in more details.
So according to User Prd and QA database Size was pretty much same because both were in Simple recovery mode. He is comparing Data with QA database so according to him record count is just 10-15% more in production database compare to QA and Index size is larger . This is production database but not highly active .
No Image or BLOB Data is stored on DB.
But Thanks again..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply