In this article, we will see how to change default data file and transaction log files location in SQL Server.
By default database files are stored in DATA folder of respective instance.
It is always best practice to to maintain data and transaction log files in different disks in order to get the following advantages:
1. Minimize data loss.
2. High Performance.
3. To avoid disk space regular issues.
4. To avoid page corruption issues.
Best Practices:
- When creating new mdf and ldf files, it is always best practice to pre-size them to minimize auto growth events.
- Data files should be placed on their own disks. Best practice to mention auto growth to 1GB.
- ldf files should be placed on their own disks.
- bak and trn backup files should be located on their own disks.
Change default data file and transaction log files location in SQL Server
1.Now, we are going configure following paths as default data and transaction log locations respectively.
E:\sql_data E:\sql_log
2. Grant read and write permissions on these folders to SQL Server service accounts as shown in the video.
3. Now connect to SQL Server and right click on SQL Server Instance and then select properties.
4. Then select database settings and go database default locations. Then, change the data and log directories path from default to specific path as shown below.
5. Click ok.
6. Verify by creating a database. Then select the new database properties and then files tab, you will find the new data and transaction file locations which are different from the default location.