There
are few things that every DBA should keep in mind about SQL Server databases
files in order to get best performance as well as smooth recovery process.
Since SQL Server Database data files actually contains all your business data and every DBA should make every effort to secure these files, When database comes online, it looks for two files (data and log files).
Log files tend to grow a lot depending upon the day to day database/business transactions. If you place data and log files on the same drive, and drive run out of space, there is a definite chance of data corruption or your database might go in suspect mode and production outage will surely take place. Being a DBA, you have to monitor the growth of these files.
if database data files get corrupted, only choice you would have is to restore the database from previous backup. However if you lose log files of databases, you can still recover your database as long as you have database data files intact, I will cover the detail of recovering databases with corrupt or no log files in my database recovery posts.
Why can't database files reside on C:\ Drive
C:\ drive is often referred as system drive, Operating system shares this drive, as described above, if database and log files grow unexpectedly (Which will happen believe me), it will swap all the space in C drive and will not let Operating system operate efficiently or should I say it will bring down the server completely - again outage and it will be very difficult to recover the space from C drive after system had a bad hurt due to space.
Also keep in mind, C:\ drive is secondary memory for Operating system, incase system is running out of memory, Operating system will use Part of C:\ drive space as a virtual memory without impacting the current running processes - with that said, you will deal with great performance lose from SQL server as well as applications attached to SQL Server if C:\ drive didn't have enough space available.
What are the consideration of Tempdb drive?
Tempdb is the most busy database when it comes to SQL Server, SQL Server Engine will use tempdb for every transaction possible, and this behavior is by design in order to achieve fast processing - I will cover detail of tempdb on a separate post, however for now, below recommendations should be considered to achieve best performance.
1. Separate Drive for Tempdb database files
2. Fast read/write drive are preferred to be used for tempdb
3. Fusion drives has fast IO read/write - whenever possible place tempdb on fusion drives
4. Best practice is to use double the space of your current memory on the system for tempdb drive.
5. I found it helpful to choose tempdb drive space 3 times of your current memory, for example if you have 16G memory, I would use tempdb drive of 48G.
6. It never hurts to be generous about tempdb drive space.
- Data files and Log files should not be on same drive
- Data files and/or Log files should not be on C:\ drive
- Tempdb should have it's own dedicated drive i.e. T:\
Since SQL Server Database data files actually contains all your business data and every DBA should make every effort to secure these files, When database comes online, it looks for two files (data and log files).
Log files tend to grow a lot depending upon the day to day database/business transactions. If you place data and log files on the same drive, and drive run out of space, there is a definite chance of data corruption or your database might go in suspect mode and production outage will surely take place. Being a DBA, you have to monitor the growth of these files.
if database data files get corrupted, only choice you would have is to restore the database from previous backup. However if you lose log files of databases, you can still recover your database as long as you have database data files intact, I will cover the detail of recovering databases with corrupt or no log files in my database recovery posts.
Why can't database files reside on C:\ Drive
C:\ drive is often referred as system drive, Operating system shares this drive, as described above, if database and log files grow unexpectedly (Which will happen believe me), it will swap all the space in C drive and will not let Operating system operate efficiently or should I say it will bring down the server completely - again outage and it will be very difficult to recover the space from C drive after system had a bad hurt due to space.
Also keep in mind, C:\ drive is secondary memory for Operating system, incase system is running out of memory, Operating system will use Part of C:\ drive space as a virtual memory without impacting the current running processes - with that said, you will deal with great performance lose from SQL server as well as applications attached to SQL Server if C:\ drive didn't have enough space available.
What are the consideration of Tempdb drive?
Tempdb is the most busy database when it comes to SQL Server, SQL Server Engine will use tempdb for every transaction possible, and this behavior is by design in order to achieve fast processing - I will cover detail of tempdb on a separate post, however for now, below recommendations should be considered to achieve best performance.
1. Separate Drive for Tempdb database files
2. Fast read/write drive are preferred to be used for tempdb
3. Fusion drives has fast IO read/write - whenever possible place tempdb on fusion drives
4. Best practice is to use double the space of your current memory on the system for tempdb drive.
5. I found it helpful to choose tempdb drive space 3 times of your current memory, for example if you have 16G memory, I would use tempdb drive of 48G.
6. It never hurts to be generous about tempdb drive space.
No comments:
Post a Comment