SQL Server – Purged Over 100,000 Records but Database Size Not Reduced

sql server

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.

Best Answer

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.

Related Question