Purged more than 100,000 records in table but size of database has not reduced

Question

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.

Answer 1

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.

Answer 2

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.

Answer 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.