Truncate Transaction Log????

[SQLePO]

Hi, I am new to SQL. I have an SQL 2000 server that is used for my ePO database. We have regular backups sheduled using Backup exec. Do I need to backup my Transaction logs seperatly? When do I need to truncate my transcation logs and how is this done??? If anyone could suggest some good sites for SQL beginners it would be much appreciated..

Thanks in advance,

SQLePO

 

[osjohnm]

Hi

You should backup your transaction logs as often as possible depending on the importance of data. As SQLSister often says get this in writing.

When you backup the transaction log in backs up all the committed transactions and then truncates all the committed transactions and frees up the space that was being used.

To empty the logs completely(in an emergency) you could run the following command:

dbcc shrinkfile(2,0)

For this to work in SQL 2000 you would have to change the recovery mode to SIMPLE but this will not allow you to backup the transaction log which only works with FULL recovery mode. THis also means that you can't restore to a point in time.

If you setup regular transaction log backups the files will remain relatively small sinc eat every backup it will remove all the committed transactions from the log.

Does that answer your question?

For websites try the following:

http://www.sqlservercentral.com

http:/

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

Hope this helps

John

 

[SQLePO]

Thanks John, I will check the sites in a minute. The problem Is I don't know how to Back up the transaction logs to comply with our currect backup strategy.. I have never backed up the transcation logs seperatly outside of the daily backup???

Thanks very much anyway, I'm sure i can use this information somehow

 

[osjohnm]

Hi

If you want to back the transaction logs up seperately then create an individual maintenance plan for the logs.

You should be able to integrate the logs with your backup strategy without to much hassle if you want to back them up together.

See BACKUPS in BOL for more details and ideas or options

John

 

[SQLePO]

Thanks very much mate, your response has helped.

The sites are good too

Thnaks again!