Transaction logs in SQL Server 2000 1

[gojohnnygogogogo]

hello,
I am looking in my logfiles and it says the transaction logs for a database is full.
I have tried shrinking the database.
what else can I do ?

than kyou for any info.

 

[gojohnnygogogogo]

Just reading another post on the subject.

I have a maintenance plan in place, I do a full backup once a week and backup the transaction logs twice a day.

Would a good thing to do be Truncate the transaction after the full backup, or run the dbccshrinkfile every evening, ( after the backups of the transaction logs).

 

[osjohnm]

Hi

What recovery model are you using?

John

 

[gojohnnygogogogo]

I'm not too sure.
how can I find that out ?
If something happened to the database I would restore the full backup, then all transaction logs up until the point of failure.

 

[gojohnnygogogogo]

sorry, I have sql 2000 installed beucase I have many SQL servers, some 7 and some 2000.
this problem is on a sql 7 box.

would that make a difference on what to do with the log files ?

 

[osjohnm]

Yes, it does make a difference especially in sql 2000.
Since this 7 there aren't really recovery models only two options, select/bulk copy and truncate log on checkpoint.

Is truncate log on checkpoint on?

If so then try the following cmd:

dbcc shrinkfile (2,0)
GO

That will work if truncate log is set to true. I haven't worked with 7 for a while but since you are backing up the log should shrink the inactive portion of the log file after each backup if truncate on checkpoint isn't set on.

Here is some more info from BOL (sql 7) about shrinking logs

Shrinking the Database
Microsoft® SQL Server™ allows each file within a database to be shrunk to remove unused pages. Both data and transaction log files can be shrunk. The database files can be shrunk manually, either as a group or individually. The database can also be set to shrink automatically at given intervals. This activity occurs in the background and does not affect any user activity within the database.

When the database is set to shrink automatically using the sp_dboption system stored procedure, shrinking occurs whenever a significant amount of free space is available in the database. However, the percentage of free space to be removed cannot be configured; as much free space as possible is removed. To configure the amount of free space to be removed, such as only 50 percent of the current free space in the database, use the property page within SQL Server Enterprise Manager to shrink the database.

You cannot shrink an entire database to be smaller than its size when created. Therefore, if a database was created with a size of 10 megabytes (MB) and grew to 100 MB, the smallest the database could be shrunk to, assuming all the data in the database has been deleted, is 10 MB.

However, you can shrink the individual database files smaller than their initial creation size by using the DBCC SHRINKFILE statement. You must shrink each file individually, rather than attempting to shrink the entire database.

There are fixed boundaries within which a transaction log file can be shrunk. This depends on the initial size of the transaction log and the number of virtual log files used. For example, a large initial transaction log file of 1 gigabyte (GB) may comprise five virtual log files of 200 MB each. Shrinking the transaction log file deletes unused virtual log files, but leaves at least one virtual log file. Because each virtual log file in this example is 200 MB, the transaction log can shrink only to a minimum of 200 MB. To allow a transaction log file to shrink to a smaller size, create a smaller transaction log and allow it to grow automatically, rather than creating a large transaction log file.

Shrinking a transaction log file does not shrink the file immediately but instead causes the file to be marked for later shrinking. Each time the transaction log is subsequently backed up or truncated (for example, when the trunc. log on chkpt. database option is set to true), SQL Server will attempt to shrink the transaction log file as much as possible until it reaches the desired size specified by the user. If the active portion of the transaction log is at the end of the transaction log file, the file cannot be shrunk. However, as soon as the active portion of the transaction log moved to the beginning of the file, the transaction log file can be shrunk.


--- here is some more info

Truncating the Transaction Log
When Microsoft® SQL Server™ finishes backing up the transaction log, it truncates the inactive portion of the transaction log. SQL Server then reuses this truncated, unused space in the transaction log rather than the transaction log continuing to grow and use more space. The inactive portion of the transaction log is that part of the transaction log that is no longer used during the recovery process that the database goes through when SQL Server starts, because all transactions in that part of the log are complete. Conversely, the active portion of the transaction log contains transactions that are still running and have not completed yet.

The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of these events:

The most recent checkpoint.
This represents the earliest point at which SQL Server would have to roll forward transactions during a restore operation.

The start of the oldest active transaction; a transaction that has not yet been committed or rolled back.
This represents the earliest point to which SQL Server would have to roll back transactions during recovery.

The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.
This represents the earliest point that SQL Server still has to replicate.

The start of the oldest CREATE INDEX operation whose completion log record has not been backed up yet.
Conditions for Backing Up the Transaction Log
The transaction log should not be backed up:

If the trunc. log on chkpt database option is set to TRUE; create a database or differential database backup instead.
If any nonlogged operations have occurred in the database since the last database backup was created. Create a database or differential database backup instead.
Until a database backup has been created because the transaction log contains the changes made to the database after the last backup was created.
If the transaction log has been truncated, unless a database or differential database backup is created after the transaction log truncation occurs.
If any files are added or deleted from the database; a database backup should be created immediately instead.

--------------------------------------------------------------------------------

Important When using transaction log backups, do not set the trunc. log on chkpt. database option to TRUE. Setting this option to TRUE causes the transaction log to be truncated, without backing up the truncated part of the transaction log, every time a checkpoint occurs in the database, preventing more transaction log backups from being created.

Hope this helps

One more thing have you set a growth size limit on the logs?


John

 

[gojohnnygogogogo]

thank you for all the info/.

I have left the growth limits as default ( 10%)
also I do not have truncate log on checkpoint ticked.
If I tick this will it shrink the log size after every backup . ?

 

[osjohnm]

Hi

As the info states in 7 sql server automatically shrinks the inactive portion of the log and then reuses that freed up space.

eg: your log has 30 transactions in it. 10 of those transactions have been committed at the time of the log backup. Only the space taken up by the 10 will be truncated and freed for usage again.

The biggest problem with trunc.checkpoint is that it truncates the log at different checkpoints without backing up the log. So with this enabled there is literally no transactions to backup since they are continuosly being truncated. You can backup the log but you can forget about restoring off the log backups and since you use the logs for restore purposes I don't suggest it.

"Important When using transaction log backups, do not set the trunc. log on chkpt. database option to TRUE. Setting this option to TRUE causes the transaction log to be truncated, without backing up the truncated part of the transaction log, every time a checkpoint occurs in the database, preventing more transaction log backups from being created."

If you want to try and get them smaller now then, backup the database with a full backup, set truncate.checkpoint to on, run the dbcc cmd i posted earlier, then change the trunc.checkpoint back to off.

Sometimes yopu have to start/stop sql server to release the space after a dbcc command.

Another thing, since you only do a full backup once a week, as a suggestion start doing differential backups, since if your db crashes between backups you only have log backups to restore from. since you really shouldn't use trunc.checkpoint especially in your case, rather increase the log backup schedule to backup more frequently so that it can shrink inactive portions more often.

John

 

[gojohnnygogogogo]

thanks john, that is good advice.
I can't try running that dbcc command, as I can not start / stop the services, ( I could be would need to do it on the weekend, when no-oine is using the db).
But, I will increase the transaction log backups to 4 - 6 times a day and see if that helps.
I'll re-read through this info. and get back to you if I haveany more problems.

cheers.

 

[gojohnnygogogogo]

Hello John,

I have not changed my maintenance plan to backup transaction logs every 3 hours.
do you think I should truncate the log file after every backup ?

 

[gojohnnygogogogo]

that should read , 'I Have changed my plan.'

not 'I have not change my plan'.

stupid typo.

so to recap, I backup the transaction logs every 3 hours and want to know, shall I truncate the log after every backup, to keep the size to a minimum.

thank you.

 

[osjohnm]

Hi

As mentioned earlier and from my understanding sql server 7 will auto shrink the unused space in the log without you having to tell it to.

Here are some reasons not to truncate the logs completely:

Conditions for Backing Up the Transaction Log
The transaction log should not be backed up:

If the trunc. log on chkpt database option is set to TRUE; create a database or differential database backup instead.

If any nonlogged operations have occurred in the database since the last database backup was created. Create a database or differential database backup instead.

<b>!!!Until a database backup has been created because the transaction log contains the changes made to the database after the last backup was created.!!!!<b>

If the transaction log has been truncated, unless a database or differential database backup is created after the transaction log truncation occurs.

If any files are added or deleted from the database; a database backup should be created immediately instead.

You see the major problem with you wanting to truncate the logs is that you only do a full db backup once a week. Any changes made are in your logs and if you truncate the whole log thechanges are gone!

&quot;A database or differential database backup contains a copy of the active space of the transaction log.&quot;

Since you are only doing this once a week, you are going to lose far too much data and transactions if you truncate the logs.

Maybe you should look at using differential db backups during the week which backs up all the data pages changed since the last full backup, backup the db completely once a week, backup the logs every 3hrs, then when you do the differential backup which will &quot;save&quot; the uncommitted part of the log, then you should be able to truncate the logs.

John