locked
System databases files location RRS feed

  • Question

  • Hi,

    I have C,D,E drives on server. Data files will be on D and Log on E. My question is what is best practice for data and log files for system databases during sql server installation selection? Should they be on C drive along with SQL Server installation or D & E? If they should not be on C then what is the reason and what is benefit to move them on other drives. I just want to know what is best practice.

    Thanks

    Saturday, May 16, 2015 5:17 AM

Answers

  • You can put the log and data files for the system databases on the same physical drive...

    I would not place a system databases on the system drive, (if the OS crashed you lose the entire instance) , but create a another physical drive for them.,...

    And do not forget to backup system databases.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Charlie Liao Thursday, May 21, 2015 8:13 AM
    • Marked as answer by Charlie Liao Monday, June 8, 2015 9:05 AM
    Saturday, May 16, 2015 6:50 AM
    Answerer
  • 1) You place system database in seperate drive,if possible kept mdf and ldf in seperate drive

    2) take backup of master,msdb,model database except tempdb.

     3)Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

    4) Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.

    database files are placed on RAID 5 arrays, which give good disk performance at a reasonable cost. However, because the tempdb is a very high-write database, a RAID 5 array isn't the proper place for it. You should put the tempdb on either a RAID 1 or RAID 10 array as they're optimized for high-write applications. If you can afford additional RAID 1 or RAID 10 arrays for each physical database file for the tempdb, you'll get increased performance.

    • Proposed as answer by Charlie Liao Thursday, May 21, 2015 8:13 AM
    • Marked as answer by Charlie Liao Monday, June 8, 2015 9:05 AM
    Saturday, May 16, 2015 4:36 PM

All replies

  • You can put the log and data files for the system databases on the same physical drive...

    I would not place a system databases on the system drive, (if the OS crashed you lose the entire instance) , but create a another physical drive for them.,...

    And do not forget to backup system databases.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Charlie Liao Thursday, May 21, 2015 8:13 AM
    • Marked as answer by Charlie Liao Monday, June 8, 2015 9:05 AM
    Saturday, May 16, 2015 6:50 AM
    Answerer
  • I tend to have all system databases on the system drive C:\Program Files.... I do have backup of master, model and msdb, of course. The exception is tempdb, which most often I move somewhere else.

    Tibor Karaszi, SQL Server MVP | web | blog

    Saturday, May 16, 2015 12:20 PM
  • 1) You place system database in seperate drive,if possible kept mdf and ldf in seperate drive

    2) take backup of master,msdb,model database except tempdb.

     3)Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

    4) Allow for tempdb files to automatically grow as required. This allows for the file to grow until the disk is full.

    database files are placed on RAID 5 arrays, which give good disk performance at a reasonable cost. However, because the tempdb is a very high-write database, a RAID 5 array isn't the proper place for it. You should put the tempdb on either a RAID 1 or RAID 10 array as they're optimized for high-write applications. If you can afford additional RAID 1 or RAID 10 arrays for each physical database file for the tempdb, you'll get increased performance.

    • Proposed as answer by Charlie Liao Thursday, May 21, 2015 8:13 AM
    • Marked as answer by Charlie Liao Monday, June 8, 2015 9:05 AM
    Saturday, May 16, 2015 4:36 PM
  • I would prefer to have them on it's own drives (like master,model,msdb) in one drive together - that way there is no dependency on other issues such as disk getting full due to some running trace files etc...

    even though - these three are system should not be quite big(except for msdb- which stores database backup information,jobs,alerts etc...), these should be relatively small but yet very important.

    tempdb should be on it's own drive - with multiple files (depending on the wait stats you are having on tempdb)and should be find Microsoft articles on this - google\bing - tempdb location and file sizing


    Hope it Helps!!

    Saturday, May 16, 2015 5:22 PM

  •  3)Set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small.

    Dinesh you cannot change recovery model of tempdb, it will always be simple.

    Gee,

    As per what suits you you can keep system databases and log files either on C drive or other system drives. Just make sure you take backup


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Saturday, May 16, 2015 6:19 PM
  • Thanks for all for their time.

    I don't think placing system databases (master,model and msdb) on different drives then C will give any benefit. Becauase if your system crashes, you have to reinstall windows and sql server and then restore system and users databases. So it looks like no reason or benefit to move these 3 system database to different drive then sql server default installation drive which is normally C. I saw some dbas move these 3 system databases to users databases data and log file but don't know what is logic behind that.

    Sunday, May 17, 2015 1:21 AM
  • So it looks like no reason or benefit to move these 3 system database to different drive then sql server default installation drive which is normally C. I saw some dbas move these 3 system databases to users databases data and log file but don't know what is logic behind that.

    One reason to place master, model, and msdb on different drives than the binaries might be for scenario where the SAN drives are replicated to a DR site via SAN storage replication.  That could reduce the RPO compared to traditional full database backup/recovery.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, May 17, 2015 4:15 AM
  • one other point is if it is clustered instance..you cannot have them in local drive...

    besides, that the other main issue would be running out of disk space on local drive..

    ideally, it should not happen but can happen.especially if you and other people happen to ru n profilers or other tracing...


    Hope it Helps!!

    Sunday, May 17, 2015 2:36 PM