Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Size is shwoing Much larger then Actual Data Expand / Collapse
Author
Message
Posted Thursday, June 12, 2014 11:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
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
Post #1580187
Posted Thursday, June 12, 2014 12:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1580206
Posted Thursday, June 12, 2014 12:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
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
Post #1580209
Posted Friday, June 13, 2014 12:36 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:18 AM
Points: 562, Visits: 2,617
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1580706
Posted Friday, June 13, 2014 4:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1580759
Posted Wednesday, June 18, 2014 9:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
Hi Jeff
Re-Org Index and Update Statistics job running Every night


Aim to inspire rather than to teach.
SQL Server DBA
Post #1583018
Posted Wednesday, June 18, 2014 9:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:46 PM
Points: 321, Visits: 1,279
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
Post #1583035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse