April 3, 2012 at 7:48 am
I am hoping some of you out there will be able to give some direction to a task I have taken upon myself. I am an accidental DBA who is keen to learn and search but I need assistance with this.
We have several locations with an SQL 2008 (some have R2) database. Support for the 'data' is by a 3rd party (in the form of patches containing TSQL scripts) but the SQL engine we are free to support because the 3rd party will not (maintenance plans, backups, restores, etc). The databases are not major (size between 250Mb and 5.5Gb) and are not mission critical (downtime can be arranged during 'holiday' periods). Log backups are not used so the database is in simple recovery model. Currently there are no maintenance plans but my first concern is that most of the databases have autogrowth set to 1Mb - probably from when the original databases were setup in SQL 2000. I would like to have a plan of action to rectify this and try to remove/reduce file fragmentation that may have been generated over the 3 years since migration to SQL 2008.
I have learnt the commands to alter the file size (giving space to grow) and alter the autogrowth and plan to monitor (perhaps with email alerts) when this is getting tight to grow the file manually rather than rely on autogrowth.
There is a reindex patch from the 3rd party that looks good but this also reduces the log file to 1Mb so I aim to alter this file size as well to allow growth.
At the same time I was planning on running the Check Database Integrety, Update Statistics and Clean Up History maintenance tasks.
I plan to perform these tasks manually (apart from the maintenance plans).
I can't create anything that will add something into the database.
My questions:
I guess there is no way in knowing how fragmented the database files are. Am I correct?
There is talk on the forums about file defraging but when would be the best time to perform this? Should it be after the file size changing to ensure the new free space is included?
I am wondering if it would be simpler to resize the file, detach the database then reattach? I have the luxury of creating downtime. Would this recreate the database file but without the file fragmentation?
lastly, Would it make any difference when the initial maintenance tasks were run (before or after file growth and defrag).
I would appreciate some directions. I have tried searching the forums for answers but there is so much to look through and I think most of the posts regarding defrag discuss the defragging of indexes, which we have covered, not how to correct a long overdue 'bad autogrowth' setup.
Many thanks in advance
April 3, 2012 at 8:06 am
Drenlin (4/3/2012)
I guess there is no way in knowing how fragmented the database files are. Am I correct?
A standard windows file defrag tool can tell you that. Run it while the database is detached or SQL shut down. Be very, very, very careful with 'online' defrag tools.
You're not on a SAN?
There is talk on the forums about file defraging but when would be the best time to perform this? Should it be after the file size changing to ensure the new free space is included?
When you can take the database offline or shut SQL down for a long time. I'd probably do it after the fixing of the file size.
I am wondering if it would be simpler to resize the file, detach the database then reattach? I have the luxury of creating downtime. Would this recreate the database file but without the file fragmentation?
No. Detaching then reattaching the file doesn't change the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 8:19 am
I guessed the standard defrag tools would give a HDD fragmentation but was unsure if any way to tell about the actual SQL datafile fragmentaion. Sorry for not being clearer but you have prettymuch confirmed.
Maybe RAID drives but local to server, not SAN.
Will abandon thought of reattaching.
Will perform corrections with small downtime then plan bigger downtime for defragging.
Many thanks
April 3, 2012 at 8:41 am
Forgot to ask - Do I defrag with the SQL service running or stopped?
This article suggests you have to stop the service.
http://www.bradmcgehee.com/2011/06/do-you-ever-physically-defragment-your-sql-server-mdf-ldf-files/
but I imagine there are also articles that say otherwise.
If you had to, would you stop the SQL service?
The question is to all, not just Gail
Thanks again
April 3, 2012 at 8:53 am
GilaMonster (4/3/2012)
A standard windows file defrag tool can tell you that. Run it while the database is detached or SQL shut down. Be very, very, very careful with 'online' defrag tools.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 8:59 am
Note to self - Must read replies more carefully and completely :blush:
April 3, 2012 at 9:05 am
There are tools that claim to be able to defrag files while open. Some even can do what they claim. However if you use one and it's not written to handle SQL database files it can cause horrid corruption.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 11:58 am
GilaMonster (4/3/2012)
There are tools that claim to be able to defrag files while open. Some even can do what they claim. However if you use one and it's not written to handle SQL database files it can cause horrid corruption.
also have a good backup on a separate drive (a drive you are not defraging) in case Murphy strikes and kills the drive or your DB file (not likely but Murphy likes to pull those out every now and then)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply