Solved

sql 2008 database has suddenly grown huge

Posted on 2014-01-18
18
541 Views
Last Modified: 2014-01-28
We've had a package for our website that uses sql for about 7 years.  The database was usually around 25 gb in size.  Suddenly it has grown to 90 gb and I can't figure out why or how to fix it.  It isn't the log that is huge - its the actual db.  I can see the largest file is "portal" but I don't know what that file is.  It isn't listed under the table view.  We haven't done anything differently with the site over the past few months - in fact we've deleted a lot of old images.  I thought about shrinking the db (even though some don't recommend that), but it looks like that would only affect about 6% of the space.  I have no clue what to do but need to reduce the size of this db quickly.
0
Comment
Question by:cindyfiller
  • 8
  • 4
  • 3
  • +2
18 Comments
 
LVL 4

Assisted Solution

by:colditzz
colditzz earned 125 total points
ID: 39790630
I believe the sp_spaceused stored procedure would help you find the table that is using the unexpected space within the database - http://technet.microsoft.com/en-us/library/ms188776.aspx - this would be where I would start looking.  Shrinking the database would only be useful to you after you have ascertained that there is space to free up, however it does look like you have already looked at the space this would save.

Hope the above helps.
0
 

Author Comment

by:cindyfiller
ID: 39790650
Hmm - it did show me that 31 gb was used by the index.   I do reindex every week - but that must not affect the size of it.  It definitely shows that shrinking would not affect the size much.
0
 
LVL 4

Expert Comment

by:colditzz
ID: 39790687
Thanks for the reply.  I am not a dba, but have a little experience with SQL.
Personally, I would check the daily backups to see exactly when the db changed dramatically in size.  Then I would attach (preferably to a different server to ensure I don't accidentally affect the current live database) the database from the night before and the night following the size change and then utilise the SP from above to see where the change originated.  Apart from this, I'm afraid I can't be much further help regarding this question.
0
 

Author Comment

by:cindyfiller
ID: 39790688
Do appreciate your comments - its pointed me to a different direction and that helps.
0
 
LVL 4

Expert Comment

by:colditzz
ID: 39790689
Thank you and I hope you resolve it successfully.
0
 
LVL 5

Assisted Solution

by:rk_india1
rk_india1 earned 125 total points
ID: 39791109
Please Check your Log file by DBCC sqlperf(logspace) command  and recovery model of the database. If the recovery model is full or no transaction log  backup is configured then transaction file is grow too much . You have to change the recovery model of the database to simple and shrink the log file .

If log file is not a concern then need to look each individual table space by the attach command to understand which table consume to much space so you can archive some old data
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39795096
This command will show you if an immediate shrink has any chance of freeing a lot of space:

DBCC SHOWFILESTATS

If UsedExtents is not significantly below TotalExtents, then shrink at this point won't help.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 125 total points
ID: 39795366
Hi,

Checking the backup size is a good idea.
select 
	top 100 -- don't want to drown in detail
	s.backup_start_date
	, s.database_name
	, s.recovery_model
	, s.backup_size
	, s.compressed_backup_size
	, round( s.backup_size / 1024.0 / 1024.0 / 1024.0, 2 ) as BackupSizeGB
	, round( s.compressed_backup_size / 1024.0 / 1024.0 / 1024.0, 2 ) as CompressedBackupSizeGB
	-- percentage compressed backup is of expected
	, round( 100.0 * s.compressed_backup_size / s.backup_size, 2 ) as PercentCompression
from msdb.dbo.backupset s
where
	s.database_name like 'model' --'YourDatabaseNameHere'
	and s.type = 'D' -- only interested in database backups
order by
	s.backup_start_date desc
;

Open in new window


http://technet.microsoft.com/en-us/library/ms186299(v=sql.105).aspx

I'm wondering (like the others) for what changed. I'm wondering what you are looking at to see that the size has changed. I'm wondering if compression was on the backups and now its not. I've seen good compression, to better than 30% of expected size. SharePoint with its images and documents not so much.

Do remember, that any index rebuild via dbcc dbreindex or similar will leave a significant amount of free space in the database afterwards. I can't find it again, but read somewhere that it was as much as 1.4 times the finished size of the index. Now shrinking to remove that space is a waste of time because a) it will undo the good work you've just done, and b) next time you rebuild this index its just going to grow again, and c) fragment the file at the file-system level.

Regards
  David
0
 

Author Comment

by:cindyfiller
ID: 39795648
I found an email where we had cleaned out images from our web site.  that was on Dec 4 and the total db size was 18 gb.  When I look at the actual mdf file it now shows 90 gb - quite a huge growth for 5 or 6 weeks.  When looking more close, I find the table itself is about 55 gb and the index is about 32 gb.  From the tools I've run shrinking it won't get more than a couple of gb.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39795687
Is the table a heap or does it have a clustered index?

Sometimes SQL Server gets "confused" with heaps and the storage goes bonkers.

If it is a heap, try adding a clustered index, then see what the new size is.
0
 

Author Comment

by:cindyfiller
ID: 39795701
Honestly I don't know - but will find out!  Our vendor installed this software and for the most part I've just ignored it on the back end - haven't had to really do anything with it.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39795881
Hi,

Referencing the email you mentioned, that means in 6 weeks database has grown. Did anything change in the application, as in the size of the images has doubled or worse?

Given Scotts point above about SQL getting confused - when was the last time you did a dbcc checkdb or database integrity check?

Scott:
Would a dbcc updateusage be appropriate here? It at least couldn't hurt anything right?

Regards
  David
0
 

Author Comment

by:cindyfiller
ID: 39795973
No nothing has changed.  That's why its so odd.  No one has loaded videos to our website or uploaded any major changes.  I'm going to check my logs tomorrow and see when I last upgraded - I don't think it was around that time.  But I'll also do the suggestions above and see what comes out.
0
 

Author Comment

by:cindyfiller
ID: 39797084
I've run the items suggested and am including the results below.  I sure am learning a lot - but still not seeing anything that would be causing the problem.  

Sp_spaceused
      Database size:      92108.50 mb
      Unallocated:      7572.66 mb
      Reserved:      86528992 kb
         Data:             55670240 kb
         Index:       29829720 kb
         Unused:      1029032 kb

Dbcc showfilestats
      Portal (mdf) – everything else looks really small
         Total extents      1335584
         Used extents      1235812

DBCC sqlperf(logspace) – 2 show up with larger log sizes
      Model
         Log size      56.30469 mb
         Log space used      92%
      BBNC (the DB that I’m concerned with)
         Log size      34.86719
         Log space used      10%
Note – recovery mode is simple

Dbcc checkdb

Running program listed by David:  Backup size is 82 gb

I checked if tables were heap or clustered index and it appears there are both in the database.  

Dbcc checkdb – the most important results are:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BBNC'.
DBCC execution completed.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 125 total points
ID: 39797174
>> I checked if tables were heap or clustered index and it appears there are both in the database. <<

Any large heaps probably need re-configured with a clustered index.

You can run this code to identify heaps (heaps will list with "Has Clus Index" as "No"):


DECLARE @has_clus_index_show_yes bit
SET @has_clus_index_show_yes = 0

SELECT
    SCHEMA_NAME(o.schema_id) AS Schema_Name,
    o.name AS Table_Name,
    Table_MB, Table_Rows,
    Table_Plus_Indexes_MB, Table_Plus_Indexes_Rows,
    Total_#_Of_Indexes,
    CASE WHEN Has_Clus_Index = 0 THEN 'No' ELSE CASE WHEN @has_clus_index_show_yes = 1 THEN 'Yes' ELSE '' END END AS Has_Clus_Index,
    FILEGROUP_NAME(fg_min) + CASE WHEN fg_min = fg_max THEN '' ELSE '/' + FILEGROUP_NAME(fg_max) END AS Filegroup,
    CASE WHEN data_compression_max = 0 THEN '' ELSE
        CASE data_compression_min WHEN 0 THEN 'none' WHEN 1 THEN 'ROW' WHEN 2 THEN 'PAGE' ELSE '?' END +
        CASE WHEN data_compression_max = data_compression_min THEN '' ELSE '/' +
        CASE data_compression_max WHEN 0 THEN 'none' WHEN 1 THEN 'ROW' WHEN 2 THEN 'PAGE' ELSE '?' END END END AS Compression,
    o.create_date AS Table_Create_Date
FROM (
    SELECT
        dps2.object_id,
        CAST(SUM(CASE WHEN dps2.index_id IN (0, 1) THEN dps2.reserved_page_count ELSE 0 END) / 128.0 AS decimal(9, 2)) AS Table_MB,
        SUM(CASE WHEN dps2.index_id IN (0, 1) THEN row_count ELSE 0 END) AS Table_Rows,
        CAST(SUM(dps2.reserved_page_count) / 128.0 AS decimal(9, 2)) AS Table_Plus_Indexes_MB,
        SUM(dps2.row_count) AS Table_Plus_Indexes_Rows,
        COUNT(DISTINCT CASE WHEN dps2.index_id > 0 THEN dps2.index_id END) AS Total_#_Of_Indexes,
          MAX(CASE WHEN dps2.index_id = 1 THEN 1 ELSE 0 END) AS [Has_Clus_Index],
          MAX(p2.data_compression_min) AS data_compression_min,
          MAX(p2.data_compression_max) AS data_compression_max,
          MAX(p2.fg_min) AS fg_min,
          MAX(p2.fg_max) AS fg_max         
    FROM sys.dm_db_partition_stats dps2 WITH (NOLOCK)
    INNER JOIN (
        SELECT
            p3.object_id, MAX(p3.rows) AS rows_max,
            MIN(p3.data_compression) AS data_compression_min, MAX(p3.data_compression) AS data_compression_max,
            MAX(au3.fg_min) AS fg_min, MAX(au3.fg_max) AS fg_max
        FROM sys.partitions p3 WITH (NOLOCK)
        INNER JOIN (
            SELECT au4.container_id, au4.type, MIN(au4.data_space_id) AS fg_min, MAX(au4.data_space_id) AS fg_max
            FROM sys.allocation_units au4 WITH (NOLOCK)
            GROUP BY au4.container_id, au4.type
        ) AS au3 ON
            au3.container_id = CASE WHEN au3.type = 2 THEN p3.partition_id ELSE p3.hobt_id END            
        GROUP BY p3.object_id        
    ) AS p2 ON
        p2.object_id = dps2.object_id
    GROUP BY
        dps2.object_id
) AS dps
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = dps.object_id
ORDER BY
      Table_Plus_Indexes_MB DESC, --from largest to smallest total table plus indexes size
    Schema_Name, Table_Name
COMPUTE SUM(Table_MB), SUM(Table_Plus_Indexes_MB)
0
 

Author Comment

by:cindyfiller
ID: 39797197
I'd probably want to check with the vendor that provides us this package before changing their indexes.   I don't see why it would matter to them - but always use precaution when changing something a vendor has done.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39797277
That makes sense.  But be aware: if you have heaps, esp. large ones, SQL can have internal issues keeping up with the correct space used and allocated by those heaps.
0
 

Author Comment

by:cindyfiller
ID: 39815429
The vendor is now working on a script to reduce the size of the database based - they said our error table has grown quite large so they are targeting that initially.  I'll close this for now  and will try to award points to everyone because all of your input helped to provide information that convinced the vendor there was an issue.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to import 7 53
How to get previous Data from SQL 34 89
Search for strings with a white space in sql 9 28
GRANT, REVOKE, DENY 4 16
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now