July 31, 2013 at 2:04 pm
I understand that it is highly undesirable to have autoshrink on a database, since this would cause severe fragmentation.
I have now inherited databases with autoshrink turned ON
Once I turn OFF autoshrink
I imagine that the index fragmentation could be reduced by running the reorganize/rebuild indexes
My question here would be , what about the fragmentation in the datafiles that was caused by the autoshrink ON.
Could this be removed somehow ??
July 31, 2013 at 2:35 pm
The defrag of the clustered index or the heap will defrag the data in the data files.
If you are referring to the disk fragmentation - that has to be done with an OS level tool.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2013 at 2:52 pm
Gerard Silveira (7/31/2013)
My question here would be , what about the fragmentation in the datafiles that was caused by the autoshrink ON.Could this be removed somehow ??
Yes, but with a catch - it is an OS level file issue. You would need to defrag the actual disk volume WITH SQL SERVER STOPPED. If the SQL service is still accessing the mdf file, the disk defrag tool will not be able to rearrage it.
Have a good backup available in case the disk defrag tool does something ugly to the mdf file.
July 31, 2013 at 3:42 pm
tim_harkin (7/31/2013)
Gerard Silveira (7/31/2013)
My question here would be , what about the fragmentation in the datafiles that was caused by the autoshrink ON.Could this be removed somehow ??
Yes, but with a catch - it is an OS level file issue. You would need to defrag the actual disk volume WITH SQL SERVER STOPPED. If the SQL service is still accessing the mdf file, the disk defrag tool will not be able to rearrage it.
Have a good backup available in case the disk defrag tool does something ugly to the mdf file.
Most of the modern defrag tools are capable of defragging files that are in use. Not sure how they do it, but they can. I agree with having a current copy of the database.
July 31, 2013 at 7:55 pm
Thanks ...your answers are much appreciated
August 1, 2013 at 9:23 am
Lynn Pettis (7/31/2013)
tim_harkin (7/31/2013)
Gerard Silveira (7/31/2013)
My question here would be , what about the fragmentation in the datafiles that was caused by the autoshrink ON.Could this be removed somehow ??
Yes, but with a catch - it is an OS level file issue. You would need to defrag the actual disk volume WITH SQL SERVER STOPPED. If the SQL service is still accessing the mdf file, the disk defrag tool will not be able to rearrage it.
Have a good backup available in case the disk defrag tool does something ugly to the mdf file.
Most of the modern defrag tools are capable of defragging files that are in use. Not sure how they do it, but they can. I agree with having a current copy of the database.
True many of the modern tools can defrag a database file while in use. I would recommend not using any of them unless they have been certified by Microsoft for use on SQL Server while in use.
But to be safe, you should have a backup of the database before defragging it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 1, 2013 at 9:57 am
regarding SQL Server database fragmentation, I'd use Ola Hallengren's utility:
August 2, 2013 at 7:10 am
Honestly I use the defrag utility that comes with Windows. Just make sure you have a db backup as a just in case and also make sure no heavy SQL Server activities are going on within SQL Server.
August 2, 2013 at 10:02 am
I stopped using windows defrag many years ago after discovering that it seldom defragged properly and often caused more fragmentation.
After that I started using a tool called Perfectdisk - very very good. But it costs.
I now use Defraggler (free for personal use, minimal cost for enterprise). Defraggler works near on par with Perfectdisk.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply