CRM Log and Database Placement RRS feed

  • Question

  • Hello,

    I am running CRM 4.0 update 3 and my disk utilization on C drive is extremely high.  Always above 90% (% Disk Time and Avg. Disk Queue Length).  I have a 5GB database and my log files are on the c drive and my database files are on my e drive which is another spindle.  Should I move my logs off of the C drive of my server to another spindle since the logs are always written to first.  I am trying to figure out the placement of the log files and database files. I am wondering if I should move my log files to the E drive and the datbase files back to my c drive.  I know this is a tough question becuase you dont know the environment my server is in.  I am running SQL 2005 Standard SP2 and a SBS2003 server enterprise edition.  Exchange server is not running on this server.

    Thanks for any input

    Wednesday, May 27, 2009 3:14 PM


  • Change trace location http://support.microsoft.com/kb/907490/en-us

    From IG:

    Disk Configurations and File Locations

    For the default instance of SQL Server, the default directory for both program and data files is \Program Files\Microsoft SQL Server\Mssql.1. You can specify a file path other than the default for both program and data files.


    Note   The default locations for program and data files are not necessarily the best locations. As noted earlier, for the best combination of disk fault tolerance and performance, consider the RAID specifications available from hardware vendors. You can create the Microsoft Dynamics CRM databases on your partitions, especially for these files, and specify the existing databases when you run Microsoft Dynamics CRM Server Setup. The databases created by Microsoft Dynamics CRM are noted in the “SQL Server Data File Location” section later in this chapter.


    By default, Shared Tools are installed in \Program Files\Microsoft SQL Server\90\Tools on the system drive. This folder contains the default and named files shared by all instances of SQL Server 2005. Tools include SQL Server Books Online and Dev Tools.

    SQL Server Setup also installs files in the Windows system directory. The system file location cannot be changed.

    SQL Server Program File Location

    The SQL Server program files are located in \Program Files\Microsoft SQL Server\Mssql.1\Mssql\Binn.

    The binary file location is in the root directory where Setup creates the folders that contain program files and other files that typically do not change as you use SQL Server. Although these files are not read-only, the folders do not contain data, logs, back-up files, or replication data. Therefore, the space requirements for these files should not increase as SQL Server is used.


    Important   Program files cannot be installed on a removable disk drive.


    SQL Server Data File Location

    Each SQL Server database consists of one or more database files and one or more transaction log files. Microsoft Dynamics CRM creates two databases:

        Organization_name_MSCRM. This is the organization database where Microsoft Dynamics CRM data is stored, such as all records and activities. Microsoft Dynamics CRM 4.0 Enterprise supports multiple organizations so that you can have multiple-organization databases.

        MSCRM_CONFIG. This database contains Microsoft Dynamics CRM metadata such as configuration and location information that is specific to each organization database.


    Microsoft Dynamics CRM also relies on the SQL Server system databases to store Microsoft Dynamics CRM configuration information. These databases include the master and msdb databases. The database files that accompany a database contain all its data and properties. Transaction log files contain a record of the write activity in the database, such as when a row is added, changed, or removed. Transaction log files are binary and cannot be used for auditing database activity.

    The transaction log is used for recovery, if a failure occurs, and to roll back (undo) transactions (writes) that cannot be finished. You may also periodically back up the transaction log as a way to perform an incremental backup while users are working in the application, with very low effect on available server resources.

    To have the best chance of recovery, if there is a disk failure, and the best performance for the application, put the database files and transaction log files on separate sets of physical disks. The location that you specify for a file does not have to be the original location for data files specified during SQL Server Setup. You can select an alternative location for the database and transaction log files any time that you create or change the database. For more information, see the prior note about disk fault tolerance and performance.

    If the partition that contains a database file has failed and the database has become unusable, yet the partition that contains the transaction log is still available, you can back up the transaction log for that database. This can be the last backup in your back-up set. When you restore, this transaction log backup, made after the failure, will be the last restored backup. If all transaction log backups in the back-up set are restored successfully, you will have restored all the committed (100 percent successful) transactions up to the moment of the failure. This, of course, limits the data loss.

    When the database files and transaction log files are on separate sets of disks, performance is optimized. Transaction log files can be write-intensive during periods when lots of data is being added, changed, or removed from the application.

    For example, if you have a server wherein the drive C is the system partition (the drive where the Windows and program file folders are located). The Windows pagefile is also located on drive C. Drives D and E are RAID-5 partitions on separate sets of physical disks. Select the partitioning scheme for the database files that will give you the combination of performance and disk fault tolerance that you want. Drive D contains only data files for one or more databases, and drive E contains only log files for one or more databases. If you verify that performance will decrease because one database will have much more hard disk activity than other databases, you should put them all on separate sets of disks. If you estimate that data will significantly grow over time, make sure drive D has 100 gigabytes (GB) available for the database files. Because the log files will be truncated every time that a transaction-log backup is performed, make sure drive E has 10 GB available. Specify the location of the database file to be on drive D and the transaction log file to be on drive E when you create the database.


    Note   It is best to dedicate a partition to SQL Server data files. We recommend that you do not put a data file on the same partition as a Windows pagefile because of the degree of fragmentation that will occur.


    By default, the directory where all database files and transaction log files are located is \Program Files\Microsoft SQL Server\Mssql.1\Mssql\Data. When you run SQL Server Setup, you can specify a different location as the default location for data files. The data file location is the root directory where Setup creates the folders that contain database and log files, in addition to directories for the System log, back-up, and replication data. Setup creates database and log files for the master, model, tempdb, and msdb databases. If you are selecting different locations for each file in the application, you do not have to change the default setting.


    Note   Data files cannot be installed on a file system using compression.


    Specifying file paths

    Because you can install multiple instances of SQL Server on one computer, an instance name is used in addition to the user-specified location for program and data files. For tools and other shared files, instance names are not required.

    Default-instance file path for program and data files

    For the default instance of SQL Server, the default SQL Server directory name (Mssql.1) is used as the default instance name, with the directory that you specify.

    For example, if you specify the SQL Server default instance to be installed on D:\MySqlDir, the file paths are as follows:

    D:\MySqlDir\Mssql.1\Mssql\Binn (for program files)

    D:\MySqlDir\Mssql.1\Mssql\Data (for data files)


    Note   The program and data file locations can be changed depending on the drive configuration of the computer that is running SQL Server.

    • Proposed as answer by sl_k83 Thursday, May 28, 2009 5:26 AM
    • Marked as answer by Donna EdwardsMVP Tuesday, June 30, 2009 2:59 PM
    Thursday, May 28, 2009 5:26 AM