Maintenance plan wizard

[jamminjaymeyer]

We are having trouble with the size of our transaction logs. If we use the Maintenance plan wizard to set up a plan to back up the database and transaction log will this make the size of the transaction log smaller. Would everyone recommend using a Maintenance plan? We are in SQL 2000.

Thanks,

 

[osjohnm]

Hi

I would definately recommend the use a maintenance plan. I am assuming that you don't do regular backups since you want to do database backups as well as transaction logs?
If not why not? Without regular backups there is no way to restore a database without them!

Anyway backing up the transaction logs won't make them smaller but will keep them relatively small. When SQL Server backs up the transaction log it truncates all records in the log that have already been committed at the time of the backup. Thereby releasing that space taken up on the disk. That helps to keep the size down. Transaction Logs can only be backed up when the FULL recovery model is selected for the database.

To fix your transaction logs right now do the following:

-- right click DBs, properties, options, set recovery model
to SIMPLE
-- run the following statment against the DB's:
dbcc shrinkfile(2,0)
this will select the trans. log and shrink it
-- check the logs to see if the sizes are smaller
-- change recovery model back to FULL
-- setup your maintenance plan

There you go, that should fix your logs.

Hope this helps

John

 

[jamminjaymeyer]

We have just made a job that backs up that database but have never done anything with the transaction log. So obviously they are getting pretty big. If I schedule the maintence plan right now it will back up the DB and only backup the transactions that occoured after the backup correct? Then it will truncate the log up to that point? I think I am understanding this correctly.

 

[MeanGreen]

Caution! Transaction logs when backed up will only back up to the point of the earliest "Open Transaction". Even if you run a Checkpoint, if a user has an open transaction since yesterday, then backing up the transaction log will not reduce the size. One step we took was to set up a stored procedure that identifies all users and their last activity. If their last activity was over 5 hours (business rule set by the company), then we kill the user and thereby close their transactions. We had to do this because Powerbuilder would not release the connection even at the menu screen created by the programmers, and we could not get our users to sign out of the application when they were done. Anyway, you can always run DBCC OPENTRANS against the database to see who has an open transaction. I just wanted you to be aware that backing up the transaction log does NOT always shrink it down. Also, if you have "SIMPLE" recovery model set up, then the database does an automatic Checkpoint of the database and the log will not be backed up then. I hope this information helps you. You can always look at BOL to gain a better understanding of what backups are all about in SQL Server.

Good Luck.