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.

I have just partitioned an existing table.

The database size was 5GB with 14 million records prior to the partition operation. After the partition operation moved rows to the new FileGroup, I noticed the database size increased to 8GB.

Can someone explain why that happened?

share|improve this question

migrated from stackoverflow.com Jan 31 '12 at 12:59

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

2 Answers 2

One reason is that new data was allocated but old not cleared. This is partially because it is not available immediatly, the other reason may be that SQL Server has to wait until partitioning finishes to delete the old table. if you are considered by that (which I find funny - heck, it is even funny partitioning a very small table on a tiny database of 5gb) then you may shrink the file to reclaim the space. THAT SAID: perforamnce oriented databases should not autogrow to start with ;)

share|improve this answer
    
Thanks Tom. I Got the 5GB back by shrinking it. –  Gunarathinam Jan 31 '12 at 9:17

Some reasons are:

  • If you have many indexes - the partitioning key may be included to all the indexes as a part of primary or clustered key
  • You moved data from some files into some other files - what then happened with old files?
  • You set filegrowth of some new files as big as it makes new files bigger, but your DB still has only 5 Gb of data in it
share|improve this answer
    
hi Oleg, I given Filegrowth as 10MB –  Gunarathinam Jan 31 '12 at 7:54
    
I never grow fils on prouction databases. Maaybe becvause I have multi gigabyte loads daily. 10mb is a fvery fragmented file. –  TomTom Jan 31 '12 at 10:15
    
@TomTom so do I, better occupy all the available place instead of worrying of it 8-) –  Oleg Dok Jan 31 '12 at 11:07
    
@Gunarathinam So, it was my p.2 8-) –  Oleg Dok Jan 31 '12 at 11:07
    
@Oleg Dok: yes, you do - in the real world of large databases. You often even plan disc layout for speed, and do not fill up all space to get better IO. Try working in the range of 1tb+ databases. I work on a project we egen have dedicated hardware for ONE database (4 schemas, 21tb planned size) to handle the load. Still takes 6 hours per day to load and process data in the night. –  TomTom Jan 31 '12 at 12:08

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.