1

I've just inherried a SQL Server, where the database file growth was set to 1MB. So there are a lot of databases (>25) that have grown in small increments until now (currently totalling 300 GB).

So I thought the hard disk must be really fragmented, and ordered lots of downtime to run Disk Optimize (having shut down SQL Server). But it finished in 1 minute, saying the disks were not fragmented.

I noticed that it has been set with Scheduled optimization = On.

Is Windows 2016 able to run Disk Optimize with SQL Server running?

I've been searching high and low and all places say you should manually grow your databases in big chunks to avoid fragmentation. This seems less important if you can accept that Windows runs Disk Optimize once a week. The SP_Blitz tool from Brent Ozar puts out a warning if you have 1MB growth size.

Henrik Staun Poulsen
  • 143
  • 1
  • 5

1 Answers1

1

Every since windows had that functionality it actualyl worked on locked files. Even before the one they bought (i think) allowed that. There is a special API in the kernel allowing files to be moved without the regular file system API for that.

So, the answer is yes.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • thank you very much for your help. As a result I think I'll need to change our default setting of growing by 1GB, For small databases, 10 or 100 MB will do. Or should it be 16MB or 128 MB (a binary number)? – Henrik Staun Poulsen Apr 19 '18 at 07:35
  • 1
    Depends ;) I am a binary person too. I would go with 128. – TomTom Apr 19 '18 at 07:37
  • is that because Disk Optimize has an easier job moving binary sized files? I'm asking because I do not know much about how it works, and I do not know how to Google it. – Henrik Staun Poulsen Apr 19 '18 at 07:39
  • 1
    No, it is because I am thinking binary. Doing tons of programming, so it is natuarl for me. At the end Optimize does not care - it moves 4kb (in standard formatting) sectors, which is the smallest allocation unit in NTFS / REFS – TomTom Apr 19 '18 at 07:40