LOG File....?

[jawan]

Truncate of Log file regularly... Does it effect (increase) the activity or speed....?

 

[tlbroadbent]

It may affect the speed because the log file doesn't need to grow if it is truncated. However, truncating the log on a production database is bad practice. You should be making log backups which will truncate the log and allow recovery in the event of data loss such as a system crash.

You may want to read faq183-1784 - "How Do I Develop a Backup and Restore Strategy?" if you don't have a backup/restore process or disaster plan in place.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[jawan]

if i backup log file every day, Is it best way to speedup database....?

Right now after every 6-hours i am making backup..is it enough?

Basicaly question is Regularly bakingup log file will increase the efficency of database or not.....?

 

[tlbroadbent]

Backups and truncating log do very little to improve perforamnce except as I mentioned in my first post. If you want to improve performance, you need to analyze indexing, query plans, cache hit ratio, processor usage, etc. to detrmine where improvements can be made. Click on the following link to view a web page related to performance. It contains a number of links to performance resources.

http://tlbroadbent.home.attbi.com/sql/ss_performance.htm

One of the best sites for information on SQL Server performance is SQL-Server-Performance.Com.

http://www.sql-server-performance.com/default.asp

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLSister]

Regularly backing up the transaction log will keep it from growing until you have no space left on the server. However, it will not speed things up. As to whether every six hours is often enough, ask your management if they can afford to lose 6 hours of data. I believe most of us backup the transaction log more frequently than that, but it does depend on how mmany transactions the system has, typically the more transactions, the more frequently you will want to back up the transaction log. And remember backing up the transaction log is not the same thing as backing up the database itself. These are two differnt backups.

AS Terry points out, there are many things to consider when trying to improve performance. Poor indexing and table design and query design are among the most common. You may also need to consider if you need more hardware. Besides the links which Terry gives, I suggest you look at some books on performance tuning as it becomes an extremely complex subject. The following FAQ has some suggestions in this area.
Useful Reference Books for SQL Server Professionals
faq183-3324