transaction logs grow to much
-
I have multiple databases on my SQL 2000 server which most are running fine. The problem is that there is one or two databases that the transaction logs grow until I have to clear the logs more often than I would like. Is there an easy way to deal with this problem?
-
John,
Assuming that these are transactional databases, and that the growth is due to users' transactions:
If these are production databases, you should schedule transaction log backups frequently enough to keep the log size where you are comfortable with it.
If they are not production, you should be able to change them to Simple Recovery Model, which will remove log entries as they are committed.
If the log growth is due to large batch processes, there are other options, such as bulk logged, or perhaps even Simple recovery model.
Note: Simple recovery should ONLY be used if you do not need point in time recovery!
Steve
-
Yes these are production databases and due to the amount of databses the schedule transaction log backups would be very time consuming and the point in time recovery is needed. Are there any other options?
-
okay maybe I am a little confused, what is the diffrence between a transactional database and a production database?
-
Nope. If you need point in time recovery, you MUST take transaction log backups.
How many databases do you have on your server? How big? How active?
If transaction log backups cause too much of a load on your server, you really need to re-evaluate your situation! Additional servers, upgrade the server, etc.
It is possible with frequent transaction log backups, you can shrink the logs, and possibly reduce some of the load on your server.
Steve
-
Transactional : online transactions. The users are directly making changes to the data.
Batch updated : users don't make changes to the database. They only read it. Updates are handled by a batch process usually run at night.
Production: The database that the users rely on (not development or test), could be transactional or batch updated.
Steve
-
They are actually both. some are updated with transactions some are updated batch process and some are updated with both. Also the amount on each server is unfortunately not an option. The size vary from 20 - 100mb. The activity varies.
-
Based on what you've said, I wouldn't think that you would have problems running transaction log backups. I have numerous servers where I am running tlog backups every 15 minutes, including one which is our consolidated server currently housing 11 production databases which like yours, some are transactional, some are batch, and some are both. I have a single job which runs a full backup for all databases every night, and another job which runs all tlog backups every 15 minutes. So far, I've not experienced any problems with this setup.
Steve
-
The sql agent is not running on the server so I would have to do this through OSQL, would you happen to know the command line I wuld need to use for the OSQL too open the T-SQL ? Also what is the T-sql script you are using to backup and clear the transaction log?
-
Why dont you use Agent to schedule and create the jobs??
I think you can get the TSQL for the jobs through this way, eg looking at properties of jobs etc
Adam
------------------------------
Life is far too important to be taken seriously -
John,
You don't have to backup the logs for all the databases on the same schedule. Backup the logs for the two busy databases more frequently than the others.
As for the T-SQL backup commands, look up "transaction logs, backing up" in BOL. I'm not sure how you would schedule it. I'm also curious about why you wouldn't want to use SQL Agent to schedule a job.
Greg
Greg
-
also would the t-sql autoshring be something like
"USE database
GO
DBCC SHRINKFILE (database_log, 1)"
-
If your recovery model is set to "Full Recovery", which it must be to do log backups, SQL Server will automatically truncate the inactive portion of the T-Log when you backup.
Greg
Greg
-
Turn on the agent and save yourself a world of grief. There are a ton of good backup scripts here in the script library as well.
Wes
-
I hear you, but unfortunately the boss says no!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply