MS SQL Server - Questions about transaction log

Author

Questions about transaction log

Antony

2006-10-24, 6:33 pm

The company I am working with use Small Bussiness Server 2003. I think it
came with SQL 2000. We got a large transaction log file. It is 15G last week
and the database file is about 5G. I did try BACKUP DATABASE, BACKUP LOG,
SHRINKFILE(<logfile>,0), SHRINKDATABASE, SHRINKFILE(<logfile>, 0,
trancateonly) followed by SHRINKFILE(<logfile>, 0, notrancate). The log only
reduce 1G.

My questions are:
1) How to make the transaction log smaller?
2) As I heard, there was a check point in the log file that kept about 2
weeks data. Is this true?
3) If a check point that kept 2 weeks data, can I modify that setting to
just kept 1 week instead?
4) As I know, after I run BACKUP LOG, the transactions inside the log file
will be processed and all commited data will be transfered to the database.
If yes, why we need the transaction file? Why it still that big? Or my
concept is wrong?

Tibor Karaszi

2006-10-24, 6:33 pm

For shrinking the log file, see http://www.karaszi.com/SQLServer/info_dont_shrink.asp, pay special
attention to the virtual log file layout and that you probably need to backup log, shrinkfile
several times.

As for your questions:

> 1) How to make the transaction log smaller?

See above.

> 2) As I heard, there was a check point in the log file that kept about 2
> weeks data. Is this true?
> 3) If a check point that kept 2 weeks data, can I modify that setting to
> just kept 1 week instead?

Point 2 is not correct, so point 3 is irrelevant.

> 4) As I know, after I run BACKUP LOG, the transactions inside the log file
> will be processed and all commited data will be transfered to the database.
> If yes, why we need the transaction file? Why it still that big? Or my
> concept is wrong?

The concept is wrong. The modifications are performed as you do them, data is modified in cache and
each modification is first reflected in the transaction log. Log records are physical written at
commit. Data pages are physical written at checkpoint (which SQL Server performs as it sees fit),
unless they need to be written earlier.
Backup log will empty the log until earliest open transaction (see DBCC OPENTRAN). That does not
make the file smaller. If you need to make the file smaller, use DBCC SHRINKFILE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Antony" <Antony@discussions.microsoft.com> wrote in message
news:A0224BDF-5589-4B92-B216- FE39D06CA7A0@microso
ft.com...
> The company I am working with use Small Bussiness Server 2003. I think it
> came with SQL 2000. We got a large transaction log file. It is 15G last week
> and the database file is about 5G. I did try BACKUP DATABASE, BACKUP LOG,
> SHRINKFILE(<logfile>,0), SHRINKDATABASE, SHRINKFILE(<logfile>, 0,
> trancateonly) followed by SHRINKFILE(<logfile>, 0, notrancate). The log only
> reduce 1G.
>
> My questions are:
> 1) How to make the transaction log smaller?
> 2) As I heard, there was a check point in the log file that kept about 2
> weeks data. Is this true?
> 3) If a check point that kept 2 weeks data, can I modify that setting to
> just kept 1 week instead?
> 4) As I know, after I run BACKUP LOG, the transactions inside the log file
> will be processed and all commited data will be transfered to the database.
> If yes, why we need the transaction file? Why it still that big? Or my
> concept is wrong?

Sponsored Links