Log free space question

Hi
I am using sql server 7.0 and one of DB is relatively
small. The size is about 2 MB however the log size is
about 30GB the problems is the log used space is 38 MB
but the log free space is about 37 GB so the whole backup
and restore takes some time as the toal og size is the
sum of these two.
I tried to shrink the file with truncate_only option but
cant get it to a small size
Can anyone help
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Ap" <anonymous@.discussions.microsoft.com> wrote in message
news:154201c4f773$43971110$a501280a@.phx.gbl...
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
|||Ap wrote:
> Hi
> I am using sql server 7.0 and one of DB is relatively
> small. The size is about 2 MB however the log size is
> about 30GB the problems is the log used space is 38 MB
> but the log free space is about 37 GB so the whole backup
> and restore takes some time as the toal og size is the
> sum of these two.
> I tried to shrink the file with truncate_only option but
> cant get it to a small size
> Can anyone help
Try DBCC SHRINKFILE (off hours, of course).
David Gugick
Imceda Software
www.imceda.com
|||I see the same thing all the time and the solutions provided by David
and Andrew work beautifully (I've had logs up to 6GB and this process
has worked as advertised). Order is everything, are you sure you're
running the them in the appropriate order? (Try sticking with isql or
isqlw. Enterprise Manager has it's place, however, for general
maintenance, it's often easier to issue a statement directly.)
Step One:
This will give you an idea of your usage and should provide the
information you'd posted earlier
DBCC SQLPERF(LOGSPACE)
Step Two:
If your logs are huge, check to see where the current active vlf is
located. Active vlfs have a status of two while inactives have a status
of zero. Chances are, if you're running this off hours you'll see a
single active vlf towards the very bottom of the result set.
DBCC LOGINFO
Step Three:
If this is the case, you need to BACKUP LOG WITH NO_LOG or
TRUNCATE_ONLY (synonymous) to move the vlf closer to the beginning of
the log file. This essentially trashes the transactions that are in the
log (they are no longer recoverable), so you'll want perform a BACKUP
DATABASE soon after.
BACKUP LOG DB_NAME WITH NO_LOG
BACKUP DATABASE DB_NAME TO DISK = '\\anywhere\db.bak'
Step Four:
As soon as you BACKUP NO_LOG (and the *highly* recommended BACKUP
DATABASE) you're free to shrink the log file.
DBCC SHRINKFILE(DB_LOG, 2)
Step Five:
Double check that everything went well (hasn't failed for me yet!):
DBCC SQLPERF(LOGSPACE)