Truncating the Transaction Log

[Patrick]

hello,
Daily, after a database backup done in the evening, around 3am we have a lot of flat files to integrate in tables and then process.

The question is : We want to free the space used by the transation log.
Then, we use "backup log with no_log" and/or "backup log with truncate_only"
We look at the size of the transaction log with "dbcc sqlperf( logspace )" but the 'Log space used %' stays the same.

Could you give us some informations or tips on this.

Thank you from Paris
Patrick

[Paul]

You could try using DBCC SHRINKFILE - i'm not sure if this was available in SQL7 though ?

The log may not be shrinking because of very large transactions running ?

Alternatively you can remove the log file completely and it will be automatically created when you restart SQL Server at minimal (originally created at) size. I have not had a need to try this but have heard it on many occasions so if possible try on a test machine/DB first.


------------
Patrick at 6/22/01 10:29:32 AM

hello,
Daily, after a database backup done in the evening, around 3am we have a lot of flat files to integrate in tables and then process.

The question is : We want to free the space used by the transation log.
Then, we use "backup log with no_log" and/or "backup log with truncate_only"
We look at the size of the transaction log with "dbcc sqlperf( logspace )" but the 'Log space used %' stays the same.

Could you give us some informations or tips on this.

Thank you from Paris
Patrick

[nealgraves]

Patrick from Paris,

I believe the log file will stay the same size after truncating. Truncating the log file will only delete out the inactive portion of the file, getting rid of changes to the database that have been written to disk. I believe the file will have to be shrunk after truncation.

Anyone else?

Neal


------------
Patrick at 6/22/01 10:29:32 AM

hello,
Daily, after a database backup done in the evening, around 3am we have a lot of flat files to integrate in tables and then process.

The question is : We want to free the space used by the transation log.
Then, we use "backup log with no_log" and/or "backup log with truncate_only"
We look at the size of the transaction log with "dbcc sqlperf( logspace )" but the 'Log space used %' stays the same.

Could you give us some informations or tips on this.

Thank you from Paris
Patrick

[Patrick]

Paul,
In SQL7 the DBCC ShrinkFile command exits and we use it with the paramter 'truncateonly' .
In fact, our understanding is that this command acts on the files ,more at the OS level(NT). Shrinking Data and/or Log NT.files. when they are previously truncated in the database with the "backup log ..." command. But if the backup log doesn't release space, the shrink of external files stay in the same situation.

The database we are talking about will be a production DB then we can't stop and start SqlServer.

I thank you (from Paris) for your suggestions

Regards
Patrick

------------
Paul at 6/22/01 10:53:59 AM

You could try using DBCC SHRINKFILE - i'm not sure if this was available in SQL7 though ?

The log may not be shrinking because of very large transactions running ?

Alternatively you can remove the log file completely and it will be automatically created when you restart SQL Server at minimal (originally created at) size. I have not had a need to try this but have heard it on many occasions so if possible try on a test machine/DB first.


------------
Patrick at 6/22/01 10:29:32 AM

hello,
Daily, after a database backup done in the evening, around 3am we have a lot of flat files to integrate in tables and then process.

The question is : We want to free the space used by the transation log.
Then, we use "backup log with no_log" and/or "backup log with truncate_only"
We look at the size of the transaction log with "dbcc sqlperf( logspace )" but the 'Log space used %' stays the same.

Could you give us some informations or tips on this.

Thank you from Paris
Patrick

[Patrick]

Neal,

I agree with you, the log file stays at the same size after truncating (till we haven't done 'dbcc shrinkfile&#39 but even in a logfile sized at several hundreds Kb, we want to check/manage the percentage of the used part.

In Sql 6.5 we checked the % of remaining free space and then we wait for several minutes or process commands doing the truncation of the DataBase Log

We feel that the command "Backup Log with Truncate_Only" could work. But unfortunatly It doesn't. And we want to understand why or add other commands before or after to get the needed result.

Thank you for your explanations.

Regards
patrick





------------
nealgraves at 6/22/01 10:56:20 AM

Patrick from Paris,

I believe the log file will stay the same size after truncating. Truncating the log file will only delete out the inactive portion of the file, getting rid of changes to the database that have been written to disk. I believe the file will have to be shrunk after truncation.

Anyone else?

Neal


------------
Patrick at 6/22/01 10:29:32 AM

hello,
Daily, after a database backup done in the evening, around 3am we have a lot of flat files to integrate in tables and then process.

The question is : We want to free the space used by the transation log.
Then, we use "backup log with no_log" and/or "backup log with truncate_only"
We look at the size of the transaction log with "dbcc sqlperf( logspace )" but the 'Log space used %' stays the same.

Could you give us some informations or tips on this.

Thank you from Paris
Patrick