SQL 7 and transaction logs

[Johan Karlsson]

Hi,

this is probably a very simple question, but I can't seem to figure it out.
I have a database on an SQL Server 7. The dataportion is small (around 10
MB), but the transaction log accidentally grew to 650 MB.
Is there any way to make this file smaller. I did run shrink database and
around 630 MB is marked as free space.

Since the original size of the transaction log was 3 MB and it was set to
autogrow by ten percent, the amount of virtual logfiles must be large, and
thats not good for performance, right?

Thanks
/Johan

[Itzik Ben-Gan]

The log can be truncated only from the end, only in full virtual log file
units, and only if reusable (backed up, not active). You might be
experiencing a situation where the log's active pointer is in the last
virtual log file. To verify this, BACKUP the log, and run DBCC
LOGINFO(<db_name>), which returns a list of VLFs. Check the status column.
To be able to truncate the log, the last VLFs should have a 0 status. If the
active pointer of the log is in the last VLF, shoot dummy transactions
(e.g., a loop that updates a table's columns to itself), until you notice
the pointer cycling to the beginning of the log (first VLF gets a status of
2). DBCC SHRINKFILE and BACKUP LOG should now successfully shrink the log.

--
BG
SQL Server MVP
http://www.sql.co.il

"Johan Karlsson" <0241....@telia.com> wrote in message
news:uWbEnfjQCHA.4228@tkmsftngp08...

[Johan Karlsson]

Hi,

That explain a great deal.
Looking at the result of DBCC LOGINFO I guess you're right. Status = 2 must
mean active since its the only one that have status = 2.

FileID FileSize StartOffset FSeqNo Status Parity
CreateTime
2 15990784 672792576 215 2 64
2002-05-17 10:41:22.733

Thanks for your quick response!
/Johan

"Itzik Ben-Gan" <itz...@hi-tech.co.il> skrev i meddelandet
news:#W#9qmjQCHA.3360@tkmsftngp11...

[Johan Karlsson]

Hi,

DBCC LOGINFO seems to be working in sql Server 7 only. Has it been removed
from Sql Server 2000? What is its replacement?

Thanks
/Johan

"Itzik Ben-Gan" <itz...@hi-tech.co.il> wrote in message
news:#W#9qmjQCHA.3360@tkmsftngp11...

[Johan Karlsson]

Never mind,

my bad, it did work...

/Johan

"Johan Karlsson" <johan.k...@adnome.se> wrote in message
news:O2BBcJpQCHA.2108@tkmsftngp13...

[Itzik Ben-Gan]

Shrinking the log in SQL Server 2000 should work. Actually, SQL Server 2000
does internally something similar to what I suggested you to do in 7.0
manually.

--
BG
SQL Server MVP
http://www.sql.co.il

"Johan Karlsson" <johan.k...@adnome.se> wrote in message
news:O2BBcJpQCHA.2108@tkmsftngp13...