SQL Server Performance Forum – Threads Archive
Database Files are 85% fragmented
Hi everyone, I was told to the best way to correct this is to dump and restore the database. Is this correct or is there anything else that I can try? Thanks in advance. Sanette SQL DeveloperJustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen – A friend
In general to reduce the index fragmentation is to reindex and for the files means you need to run DBCC CHECKDB & CHECKCATALOG to make sure there is no inconsistency. Make sure regular dbcc checks and maint.plans are in place to reduce the performance overhead. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I think Sanette is talking about Operating System file fragmentation. In order to remove this, you need to stop SQL Server and run disk defragmenter. There is one that comes with Windows 2000 and above. Tom Pullen
DBA, Oxfam GB
But no need to drop and restore, I guess. Frank
http://www.insidesql.de
Thanks Satya, so just to confirm if I run DBCC CHECKDB it will fix the defragmentation of the files? Has it got an overhead on the server or can I run it at any time? Sanette SQL Developer
JustEnough Software Corporation
Walking on water and coding of a spec is easy, as long as both are frozen – A friend
If your query is related to the files follow as suggested by Thomas. And to defragment those files you must stop the SQL server in order to work out the DISK DEFRAGMENTOR. Its not recommended to use this OS utility on database drive.
BTW what is the current issue with defragment of files? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
And when it comes to overhead, both OS de-fragmentation and database de-fragmentation are resource intesive and hence are recommended to run when there is less activity on the server.
Sannette, Refer thru this valuable linkhttp://www.sql-server-performance.com/sql_fragmentation.asp which explains the scenario. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>