Can SQL remain online for an OS defrag?

[DairyProgrammer]

Can I run a windows defrag while SQL server is online?

Thx

 

[Catadmin]

Given that defrag physically moves files on the hard drive, I would say definately not. Not unless you want to risk corrupting your SQL Server install. I usually run Defrag with all else closed to avoid file corruption.

Now, if SQL Server is on a different logical or physical drive than the drive you are using defrag on, then it might be possible. Definately possible if on a different physical drive, not really sure about a different logical drive if the physical drive is the same.

Still, better safe than sorry, I say. And make sure you backup SQL Server and your data files before you do the defrag from a CYA point of view.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[Corran007]

Yes it can. however. The actual database files are in use so cannot be defraged or moved around. so it will only defrag the space that is not being used.

I would stop the sql server before doing it to improve results.

Even if sqlserver itself is stopped, it may not completly defrag. I had a test server that was badly fragmanted. Stopping SQL server and runing defrag helped some but it was still fragmented. I had a very large database and backup on it. I was only able to imporve it by copying them off, defraging then copying them back.

 

[mrdenny]

If your database file growth is done right, the drive with the database files on it should have none or almost no fragmention of the files. This is because the files should grow a decent amount so that they don't need to grow very often, and there should be nothing else on the drive but the database files. And nothing should ever be removed from the drive, so there should be no fragmention.

All that said if you do defrag a drive with SQL the database files won't be defraged unless the SQL Service is stopped as Corran said.

If you shrink your database files on a regular basis (which you don't need to do but many people do anyway) then the database file can become fragmented and may need to be defragged.

If you defrag a database file you will want to defrag all indexes stored within that file, as the index may now not be as defragmented as needed.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)