Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / GP 10 Database Defrag
Microsoft Dynamics GP (Archived)

GP 10 Database Defrag

(0) ShareShare
ReportReport
Posted on by Ingram Quick 65

I have a GP 10 database that is on a very fragmented (OS Level) hard drive.  I would like to run an OS Level defrag on the drive.  My SQL server is configured as follows:

 

            The mdf files are on an independent/physical RAID 5 drive

 

            The ldf files are on an independent/physical RAID 1 drive

 

            The tmp db and the OS are on their own dives as well.

 

            Data Size

 

            Production db 64 GBs

            Archive db 30 Gbs

 

The issue –

 

I would like to run an OS Level defrag on the data files; however, SQL db’s and Windows defrag tools don’t play well together.  I have already tried defragging the drive with little success (note: I did stop SQL).  What I would like to try now is to do a backup of the GP db’s delete the db’s stop SQL and then run Windows defrag.  If all goes well I would then restore by GP db’s – same location and name.  What I am not sure about is/if there will be a problem with data base permissions.  To the best of my limited SQL knowledge when restoring an SQL db the user permissions should nor be affected, however, given GP secret squirrel 007 security will I need to re-run any of user permission scripts that one would run when moving a GP db to a new server or restoring it in a db with a different name?

 

Respectfully –

 

ingram

*This post is locked for comments

  • Ingram Quick Profile Picture
    Ingram Quick 65 on at
    Re: GP 10 Database Defrag

    Richard and Beat -

    I will give this a try over the weekend and let you know what happens.  Thanks for the suggestions.

    Respectfully -

    ingram

  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    Re: GP 10 Database Defrag

    Hi Ingram,

    That local user gets created when you install your SQL server to run the services on the system by default, unless you specify some other users. This shouldn't affect you and I'ld also go the path that Richard suggested : detach and move your files off the local drives to an external unit. Just make sure that you write down the names of your Databases vs. physical files. User the SP_HELPFILE command to get the information quickly and copy that into an Excel sheet. Much easier to re-attach after all your files. Make also sure that you have current backups of all your DB's on hand in case something goes wrong.

    Good luck.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    Re: GP 10 Database Defrag

    This sounds like you have multiple instances of SQL running on this computer. Each instance will be separate and be referred to with a different name. The $MSSQLSERVER is indication of this.

  • Ingram Quick Profile Picture
    Ingram Quick 65 on at
    Re: GP 10 Database Defrag

    Not now as I have not tried you recomendations on my production db's just on a test db.  It seems that the SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER Group is used by SQL Services to perform various SQL releated tasks(?)

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    Re: GP 10 Database Defrag

    I assume you mean in Windows Explorer? I do not see where that will matter much. Users are granted rights to databases within SQL and the SQL databases are controlled by the accounts setup to control security for SQL. Is this stoppping users from doing anything?

  • Ingram Quick Profile Picture
    Ingram Quick 65 on at
    Re: GP 10 Database Defrag

    Richard -

    Thanks - I did not think about that approach.  One additional question - when I move the mdf to a diffrent drive and back the SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER Group is no longer listed in the security tab.

    Respectfully -

    ingram

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    Re: GP 10 Database Defrag

    From what you are describing there should not be a problem since you are not moving from one server to another. I would suggest not using backup files but detach and attach the databases. So the steps are:

    1) Detach Databases

    2) Copy mdf and ldf files to a safe location not on the same drive

    3) Delete them from original location

    4) Defrag the drive

    5) Copy the mdf and ldf files back to their original location

    6) Attach the databases.

    Now this is assuming your GP databases are located on a drive different then your SQL master, msbb and tempdb databases. Detaching and Attaching these databases is a totally different matter.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,430 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans