Transaction Logs Question 1

[zoeythecat]

Hi All,

We have SQL 2005 standard. We have the recovery mode set to Full. We are not performing transaction log backups. So i'm noticing the logs are building up. If we are not running transaction log backups how can I get these logs to purge or not to pile up? Would this be just as simple as changing the recovery mode to "Simple"?

TIA,
Zoey

 

[ptheriault]

If you do not backup your transaction log then the log does not truncate. If you change the recovery model to simple the log will truncate at checkpoints. However, If you need to recover your database to a point in time throughout the day then you should set up incremental transaction log backups to run durning the day.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[zoeythecat]

Paul,

Thanks for your reply. The reason we are not backing up the transaction logs is because we have a Raisers Edge database which uses it own backup/restore routines. This is the only DB we have using SQL. So if I change this mode to "Simple" I take it one log will get overwritten thoughout the day. Is this correct?

Thanks

 

[ptheriault]

SQL Server only uses one log, it doesn't matter what recovery model you select. If you change it to simple, the server will remove transactions from the log when a checkpoint is issued. In FULL recovery that doesn't happen.
If you do change it to simple you should run the SHRINKFILE command to regain the extra space the log is taking up since you haven't been backing it up.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[zoeythecat]

Thanks Paul

 

[SQLSister]

I would suggest a test of the simple mode and your backup routine on a development machine before you go this route. Using the smiple mode changes what is put inthe transaction log and not being familiar with this other product you use, I don;t know if that change would make transaction recovery not possible once you change to simple. I would under no circumstances do this on productin until testing it on development first, making some changes and running the backups inyour system and seeing if you can recover all the changes with the log set to simple. Wouldn't you rather know this will be a problem before you commit to the path rather than finding out when you are trying to recover?

Look in BOL at the command to backup the transaction log, there is an option in it to truncate the log instead of back it up. It may be that this is what you need to do instead of changing to simple mode. (Actually this is true in 2000, I'm not sure if it is the same in 2005)

Questions about posting. See faq183-874

 

[zoeythecat]

Sqlsister,

I understand what you are saying, but on this server the database program we use (RaisersEdge) is only capable of backing up once a night (unless we run a manual backup). I'm told when we need to restore they would only support us if we restored through them and not SQL. Therefore it does not matter if we backup the transaction logs throughout the day because I would not be restoring via SQL. I just don't want the logs to fill up our disk space.

Zoey

 

[zoeythecat]

Paul,

I made this change to the "Master". Should I also make this change for the "Module"?

 

[ptheriault]

The Model database is very important to SQL Server. Everytime you create a new database it is created from the model. Make sure you have at least one very good backup of that database and make a backup of it every time you make a change to it. Yes you can change the recovery model of that database to simple.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[zoeythecat]

Thanks for the information Paul. Appreciate it.

 

[zoeythecat]

Me again,

I still see these 3KB log files (1 log file per day) that accumulate from the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG even after changing the recovery mode to Simple. The information from the log is this: System Database Backup_20070306234507 . Is this normal? If so, are these log files something I can just purge manually?

 

[mrdenny]

These are the log files from the maintenence plans. These logs should be reviewed to ensure that there are no problems doing things like backing up and optimizing the databases.

After they have been reviewed they can be purged.

You should setup a maintenence plan to purge these automatically.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[zoeythecat]

Thanks Denny

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com