Q

Improve SQL Server performance by creating a transaction log file

Creating an additional file for databases and transaction logs can not only provide additional space, it can also improve database performance. Monitoring and Administration expert Kevin Kline explains how.

When you create a second location for a transaction log, how does SQL handle it? How do I make sure the database starts to write transactions to the new file? I created the second log file because we have run out of disk space.
Creating an additional file for databases and transaction logs can not only provide additional space, it can also improve database performance. Note that performance can improve for a database when you create multiple files on distinct hard disk or RAID devices. But performance will not improve for a transaction log when you do the same thing. Why? As it turns out, transaction logs only write to the last active page of the transaction log at any given moment. Thus, the transaction log can only write on one file at any given time. SQL Server will automatically start to write to the new file as space on the original file is consumed. You will not need to do any sort of manual switch over. Also, make sure you have a good backup routine in place, so your transaction log will be frequently cleaned out. In my production systems, my rule of thumb is to do a full database backup nightly and transaction log backups every 15 minutes.

 


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in August 2005

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close