Best Practice for adding data files? RRS feed

  • Question

  • I am a part-time DBA (perhaps 20% of my daily time spent with SQL Server - 2000 and 2005, and largely self-taught).  We have dozens of databases that were either setup by consultants to support various systems/packages, or just set up using install program defaults.  Most have just one data file, while others have as many as 5 additional ndf files.  Many databases have been carried over for years (some prior to SQL 7) supporting applications and being upgraded to newer SQL versions as we retire servers.  All our SQL Server systems place SQL data on a RAID 5 data array and log files on a RAID 1 array by default.  Is there a 'Best Practice' for determining when it would be beneficial to add additional data files to a database?  All critical databases are backed up nightly, while the data warehouse and datamarts are not since they are real-time updated, and if lost/damaged would just be recreated from mainframe data.  I mention that because from what I have read about multiple data files, it seems that faster backup/restoration is an advantage of using multiple data files.  I am more interested in stability and performance enhancement.

    Also, can anyone suggest a good book on SQL Server Best Practices that would give me the guidance and real-world answers/suggestions that so far I cannot find in the books and online resources that I have accessed? Thanks!
    Friday, September 18, 2009 6:22 PM


All replies