Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

One of my databases was growing in size a few gigs every day. That was very unusual since it is still in development phase. After small research i discovered that the Even Log table was almost the size of the database (100gb) so i cleared it. I ran a script to check the table sizes in this database and everything went back to normal. That is, no ridiculously fat table. However, the general size of the database didn't change despite the diet to the problematic table.

Any suggestions?

BTW, I am using SQL SERVER 2012

thanks, David

share|improve this question
3  
Do you mean 2008 R2 or 2012? –  Paul White Mar 2 at 4:39
2  
What is the "Even Log" table you refer to? –  datagod Mar 2 at 4:55
2  
There is no version 2012 R2 - there is 2008 R2, or 2012 (with or without SP1) .... –  marc_s Mar 2 at 21:41

2 Answers 2

I would be willing to bet that your database is full recovery and that you are not taking transaction log backups. Log backups are required for the transaction log to clear and reuse space. Otherwise it will just continue to grow and grow.

share|improve this answer

SQL Server files are essentially containers to hold your data, like a bucket. This bucket, of course, will grow to accommodate your data within it. However, once it grows, it will remain that size unless you shrink the file itself, even if you empty out data. Like so:

enter image description here

You can see how much space is empty in your database files with the following queries(borrowed from Glenn Berry's DMV script:

SELECT f.name AS [File Name] 
, f.physical_name AS [Physical Name]
, CAST((f.size/128.0) AS decimal(15,2)) AS [Total Size in MB]
, CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS decimal(15,2)) AS [Available Space In MB]
, [file_id]
, fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
   LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
   ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

To reduce your file size, you'll need to use the DBCC SHRINKFILE command to actually reduce the file size. However, keep the following caveats in mind:

  1. You can't shrink the file beyond the point that you have data in your file (so if you have 10 GB of actual data, you can't shrink it beyond that).
  2. Be mindful of whether or not you actually need to shrink. If your data volume will grow to this size again, you are not helping your database by shrinking those files. Work has to be done by both SQL Server and the host OS to grow a data file out. Paul Randall talks more about it here.
share|improve this answer
4  
As well as your point two, the shrink commands can lead to significant internal fragmentation which over time can impact performance badly - which is why you should never perform regular shrinks for "maintenance". Of course the significance of this differs between production and dev/test environments so his mileage may vary and in this specific instance the one-off shrink is unlikely to be a bad idea. –  David Spillett Mar 2 at 10:30
    
thank you. That solved the problem. I wanted to vote up but for some reason i need to be at least 15 points in order to do that, strange... –  David Barel Mar 5 at 1:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.