SQL Transaction Log

[Shelia_Galler]

Yet another question on our new implementation! We are seeing the following warning message on our Enterprise Vault server:

Event ID:41016

The Directory database transaction log has used 91% of its allocated space.

The information in this database is at risk until the database has been backed up.

Review your SQL database backup procedures and make any changes needed to ensure that backups happen in a timely fashion.

Ensure that your database is adequately sized for the transactions taking place during an archive run.

Also check that there is enough disk space available to accommodate any growth in your database and database transaction log files.


We are running EV 6.0 w/SP1 on Windows 2003 Enterprise Edition and Microsoft Exchange 2003 SP2 in a clustered environment.

Anyone see this before or have any ideas?

[TonySterling]

Yes, I have seen this when the backup's were not truncating the transaction log. You can use the steps below to truncate it, but you will want to make sure your SQL Maint. is truncating these during backups.


Truncating transaction log with SQL2000

AFTER THE TRUNCATE COMPLETES BACKUP THE DATABASES!!


Use enterprise manager, right click the database, choose properties.

Click the Transaction Log tab. Make note of the FILE NAME.

This is the logical file name of the transaction log.

Close the properties window.

In enterprise manager, make sure the correct db is selected,

click Tools-SQL Query Analyzer.

Verify that the correct database is listed at the top of the screen and/or in the command window.

Copy the commands below and run them one line at a time.

For the Backup Log command, DATABASE NAME is the name listed in the enterprise manager list of databases.



checkpoint

BACKUP LOG "evjcemcvaultstore" with truncate_only --NOTE: use the quotes.

dbcc shrinkfile (jcemcvaultstorelog70) --NOTE: use the parentheses

[TonySterling · Accepted solution]

Important to note in my example:
evjcemcvaultstore = database name
jcemcvaultstorelog70 = logical file name

checkpoint

BACKUP LOG "evjcemcvaultstore" with truncate_only --NOTE: use the quotes.

dbcc shrinkfile (jcemcvaultstorelog70) --NOTE: use the parentheses


:)

[Micah_Wyenn_2]

Sheila,

You're not alone in this one. Most people are so glad that they got EV up and running that they forget about performing maintanence on their SQL db's. There's two things you need to do in order to correctly solve this problem.

First is to create a backup schedule that will get all of the important db's to EV (that's all the EV* dbs, master, msdb, and Enterprise* db's). I recommend using netbackup, or backup exec as I've never seen issue with their SQL agents fubaring one of my EV db's. It's pretty quick and intuitive too (at least backup exec 10d is).

Second, you should create maintanence plans for all of the above db's. Your sql admin will give you a much better idea which options will work best for your environment.

By default, all the EV db's should start with a 10% auto-growth option turned on. You may want to increase this if you can't do backups on an ongoing basis.

You can also set the threshold for the warning (giving you the error above) to be higher.

The transaction logs should have the auto-shrink option turned on (at least that's what my neophyte SQL skills tell me). You should also notice that the transaction log reduces in size after you do backups, as the agents should integrate the transaction logs in to the db.

micah