Click here to monitor SSC
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
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 1387
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7423
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
      Wink 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
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 1387
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7423
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44974 Visits: 39866
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
logicinside22
logicinside22
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

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

Aim to inspire rather than to teach.
SQL Server DBA
logicinside22
logicinside22
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 1387
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