SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Size is shwoing Much larger then Actual Data


Database Size is shwoing Much larger then Actual Data

Author
Message
logicinside22
logicinside22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 1420
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

Aim to inspire rather than to teach.
SQL Server DBA
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13496 Visits: 8002
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
logicinside22
logicinside22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 1420
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.000 737.117
252.000 275.758
252.000 275.594
229.000 250.539
223.000 243.781
220.000 240.680
82.000 892.813
82.000 892.344
75.000 811.781
73.000 789.617
72.000 779.625
65.000 730.953
65.000 726.852
63.000 705.180
61.000 687.898
61.000 683.188
63.000 464.469
62.000 455.758
48.000 549.047
48.000 548.930
48.000 548.797
48.000 548.656
48.000 548.297
55.000 407.883
55.000 407.195
54.000 399.820
62.000 316.625
78.000 172.438
32.000 33.258
25.000 15.547

Aim to inspire rather than to teach.
SQL Server DBA
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13496 Visits: 8002
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?

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216632 Visits: 41986
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
logicinside22
logicinside22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 1420
Hi Jeff
Re-Org Index and Update Statistics job running Every night

Aim to inspire rather than to teach.
SQL Server DBA
logicinside22
logicinside22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2008 Visits: 1420
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..

Aim to inspire rather than to teach.
SQL Server DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search