Again T-Log size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Again T-Log size

Author

Topic

CanadaDBA

583 Posts

Posted - 2005-01-06 : 09:28:02

My TLog size is big because I run maintenance plan every night. The DB is set to Full mode. Although, I get a full backup after the maintenance plan but still the size is big. Shouldn't it reduce the TLog size?

DBCC SQLPERF (LogSpace)

showes the log size as 2587.43 MB and Log space used as 1.07%.

If I use the following script AFTER the maintenance plan and BEFORE the full backup, does it reduce the size?


alter database db set recover simple
go
dbcc shrinkfile (tran_log, TargetSize)
go
checkpoint
go
dbcc shrinkfile (tran_log, TargetSize)
alter database db set recovery full
go

Canada DBA

CanadaDBA

583 Posts

Posted - 2005-01-06 : 12:33:04

If I don't want to use that script, what is the suggestion to avoid the large TLog? Remember that I get a full backup every night and also run maintenance plan (optimization and reindex) every night. Is it possible to order these nightly works in a way that prevent to increase the TLog size? for example:

1. do a full backup
2. do the maint. plan (optimization & reindexing)
3. set the DB to simple mode
4. do a full backup
5. set the DB in full recovery mode

Does it make sense?

Canada DBA

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-06 : 12:37:40

>> I get a full backup after the maintenance plan but still the size is big. Shouldn't it reduce the TLog size?
No.

see
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2005-01-06 : 18:25:30

If you have got the disk space available then don't worry about the size of the .LDF file. SQL needs that much working space, so "shrinking" it will only cause it to grow back again - at some cost of performance ad physical disk fragmentation each time.

If the transaction log BACKUP filesize is a problem then perhaps setting to simple BEFORE doing the Reindex etc. might be a solution - but you lose the ability to roll-forward to a point-in-time until you set the database back to FULL, do a transaction backup and then do a full backup.

As per your numbers list I think the correct sequence would be

1. Do transaction backup
2. Set DB to SIMPLE
3. Do Reindex / Optimisations / etc.
4. Set DB to FULL
5. Do Transaction Backup
6. Do Full Backup

During the interval between 1 and 6 you have no point-in-time recovery

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources