Turn off SQL Server before defragmenting your hard drive
Posted by Joe on December 15, 2008
Had a fun time this morning with a client’s machine where they defragmented the hard drive on the server over the weekend. One would expect this to be pretty benign. An interesting side effect occurred that I can’t yet explain. The main database ran out of filegroups space in the ‘PRIMARY’ filegroup. The database was set to autogrow, but it wouldn’t autogrow. A manual grow yielded this message:
MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file
Most references to operating system error 1450 yielded results pertaining to a backup. That wasn’t the case here. After quite a bit of searching for information, I came across a blog by Imran Shaikh that basically said this can result from a highly fragmented data file. That was indeed the case with this data file. We turned off SQL Server, ran a defrag again, and turned SQL Server back on. The data file was once again autogrowing.
I don’t have a real thorough understanding of what happened. My suspicion is that while the original defrag occurred, it couldn’t defrag the database file because it was in use. That may have boxed in the data file so it couldn’t autogrow any more. It’s a bit strange if that’s the case because I would have expected the file to grow elsewhere on the disk, but it didn’t. So, lesson learned: if you’re going to defragment your server’s hard drive, turn off SQL Server.
