3

I am using SQL Server 2008 R2 version running on Windows Server 2008 R2 Standard. My database size is around 207MB. As it contained 100's of thousands of records in a table, I decided to keep only the first 10000 records and delete the remaining so as to minimise the size of the database.

I deleted the 90000 records from the database and I also rebuilt the indexes:

 DELETE FROM toptrends
 WHERE HandleID NOT IN (SELECT TOP 10000 HandleID
                        FROM toptrends
                        ORDER BY lastmodifieddatetime DESC)
 go

 ALTER INDEX ALL ON TopTrends
 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = ON);
 GO

and checked the size of the database and database_log files. The database_log file size has increased in size but the database file remained the same. I thought it should decrease

  • file size before deletion : around 207892
  • file size after deletion : around 207892(same)

  • size of database_log file 625MB (up from some 300MB)

Can't we reduce the size of the database by purging unwanted/old records from the table and rebuilding the indexes?

p.s : My database_log file has increased dramatically after purging the table and I don't want that too going large.

migrated from stackoverflow.com Mar 19 '15 at 4:36

This question came from our site for professional and enthusiast programmers.

4

Before You do Anything, Check These Links Out

When is it OK to shrink a Database?

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

How to Shrink Your Database Files

Right click on the database you want to shrink. Go to Tasks>Shrink>File and hit okay. This will shrink your data. Repeat the same steps for your log file just change file type to log.

  • 1
    Terrible advise ! I would vote against shrinking database. If you want to shrink (bearing consequences), I would recommend to go with shrink database file - dbcc shrinkfile. – Kin Shah Mar 19 '15 at 12:18
  • 1
    I didn't say he SHOULD do it, just HOW he can do it. Why is shrinking database worse than shrinking the file? – Stephan Mar 19 '15 at 13:40
  • 1
    First - people look for the accepted answer and get misguided. Second - Shrink Database will shrink all files as opposed to Shrink File just one file. So in this case the OP has problem with log file - why would he require to shrink database as opposed to just shrinking log file ? Also check Why you should not shrink your data files ?. – Kin Shah Mar 19 '15 at 13:46
  • 1
    First - I gave Venkat what he wanted. He expected his data file to get smaller and noticed his log file doubled in size, so I gave him the simplest solution. Second, I agree with you, so I'll edit my answer and add your article. – Stephan Mar 19 '15 at 14:01
  • 2
    @StephanCalderon Thanks. I think that part of what makes this site great is that we don't just answer questions - we provide experience as professionals which isn't always as simple as the asker may think. Downvote removed – LowlyDBA Mar 19 '15 at 14:11
7

When you're faced with a database being too large, for whatever reason (growth is more than what you expected, you're running out of disk space, you run out of buffer pool space in memory) there's a few questions you probably want to ask yourself before taking action:

  1. What happened to cause the database to be too large?
  2. Where exactly am I seeing problems?
  3. What steps can I take now to alleviate my immediate space concern?
  4. What steps do I need to take to keep this from happening again in the future?

A lot of times, growth is to be expected if people are using the system that adds data to the database. This is, after all, the purpose of a database. However, understanding the root cause will be key in addressing the issue long-term, so doing that work up front will save time later. This may mean you need to take a look at use patterns using DMVs or other mechanisms.

The next thing you'll want to determine is where you're seeing the problem. If you've separated out your data from log drives you should be able to easily tell where you're seeing growth. If it's in the log file, figure out how you can better address the log file growth (more frequent log file backups?). If it's the database, your options become a bit more plentiful. If it's the Temp db, figure out what is causing temp db growth.

Once you determine what caused the data growth, you can take steps to determine how to immediately deal with the problem and address it long term. Deleting data out of a database can be a pretty drastic step if you are running with logging on (and you should be). For one thing, the log file will grow because it has to keep information about the deletes in there, until you're able to back up the log file. For another thing, as you noticed, SQL Server won't automatically adjust the size of the files when you remove data. From a long-term point of view, you can assume that if the database ended up a certain size through normal usage it will get there again... so spending time shrinking and growing the files would be wasteful.

To immediately address the issue there are a couple of things you can do:

  • If you are absolutely sure the database won't just grow again, you can shrink your database. You won't want to do this because the database is probably going to grow again, and it's not a long term solution that helps address the actual problem.
  • You can enable Page or Row compression to make the data smaller, if you aren't experiencing CPU pressure. The general trade off is that while Row and Page compression reduce size on disk (and therefore memory usage) it takes CPU cycles to compress. You also need to make sure that new objects added to the database are compressed.
  • You can attempt to limit the amount of data being entered into the database.

By not only pinpointing the cause but determining a course of action for the long term, you'll be better prepared for the future and may even save yourself a panic.

  • 1
    The proper correct answer, IMHO – Nelz Mar 19 '15 at 14:32
  • My requirement is such that a back ground program keeps on adding records to database every hour and this should go obsolete after a week. so I want to remove them. in other words I just have to keep only 7 days of data at anypoint of time. So Whats the best solution at this point? I can atleast remove data to save disk space which inturn i expect the size of data file to go smaller but is actually not. – Venkat Mar 19 '15 at 18:38
  • 1
    Removing data won't make the file size smaller. Think of it this way, the database is a box where you put data into it. It's sized large enough when it's first created to allow all the data you think you'll need, but when it's not it has to grow larger. When you delete data out of the database (or take something out of the box) it doesn't actually make the database smaller. It just now has enough space to allow more data into it before it has to grow again. – Sean Long Mar 19 '15 at 18:43
  • You'll have to decide how to control the amount of data going in and how long it persists based on your business needs (historical data, reporting, etc.) and come up with a solution that balances that out with your space requirements. No one here is going to tell you the exact answer or give you a script to "fix" this problem because it's not a simple problem, it's an engineering issue that needs to be carefully dealt with. – Sean Long Mar 19 '15 at 18:45
  • thanks seanLong. It cleared my doubts. I certainly see even after 24 hours my database has not grown, even though I see more than 5000 new records have been inserted. – Venkat Mar 19 '15 at 20:19
3

You need to look into shrinking your sql database. It doesn't dynamically increase and decrease the file size with each little data change. You'll need to check your growth settings to see how yours is going to behave.

As you search for shrinking your database, you'll find many suggestions on why this is probably a bad idea.

The nice thing is, if you leave your database file as is, as you add more data, it won't increase any larger until you exceed the current size setting. The upside to this is you don't waste any resources on increasing the file size, so there are performance gains.

Also, the log file increased because of all of the activity of deleting files. You could restore to a point in time since the log has maintained all this activity. You can do a backup of the log file to shrink it.

  • What do you mean by current size setting ? are you suggesting me to leave the database size as it is ? – Venkat Mar 19 '15 at 3:32
  • He means looking at the database file settings (your .mdf and .ldf files) for the database you want to shrink. There will be an initial size setting and the autogrowth settings. This tells SQL Server how to allocate disk space for the database. If you're low on space, then you should limit the autogrowth settings. Regarding the data purge, if you want to minimize log growth, you could simply select the rows you want into a new table, drop the old table and rename the new table to the old. – Queue Mann Mar 19 '15 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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