I'm running SQL Server 2005 and when I run defrag.exe I find that my main database file has well over 1000 fragmentations in a 37GB file. I've tried running defrag but it always reports back that it cannot defrag the file. There is about
54% free space on the drive and I stopped all SQL services before trying to defrag but no luck.
Any suggestions on how to defrag this file?
One thing I did think of and tried on a test database was to backup the database, detach it, delete the files and then do a restore of the database. That seems to have worked but I haven't fully tested the database after restore. Does this approach
make sense or is there some risk I haven't thought of? It sure seems faster than doing a defrag and it does test my disaster recovery abilities.
Thanks in advance,
View Complete Post