3

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?

Improve this question

2 Answers 2

Reset to default
3

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 ;)

Improve this answer
0
0

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
Improve this answer
7
  • hi Oleg, I given Filegrowth as 10MB
    – Gunarathinam
    Jan 31, 2012 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, 2012 at 10:15
  • @TomTom so do I, better occupy all the available place instead of worrying of it 8-)
    – Oleg Dok
    Jan 31, 2012 at 11:07
  • @Gunarathinam So, it was my p.2 8-)
    – Oleg Dok
    Jan 31, 2012 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, 2012 at 12:08

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.