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 helphttp://www.nigelrivett.net/TransactionLogFileGrows_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 have tried all through enterprise manager and dbcc
shrinkfile but does not help it is not the datalog of the
log file getting big but the unused space in the log
files which needs to be truncated to a smaller size any
ideads
>--Original Message--
>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
>.
>|||Did you read the links that were posted? Have you checked to see if you
have any open transactions with DBCC OPENTRAN()? Do you have Truncate Log
on Checkpoint turned on?
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>I have tried all through enterprise manager and dbcc
> shrinkfile but does not help it is not the datalog of the
> log file getting big but the unused space in the log
> files which needs to be truncated to a smaller size any
> ideads
>
>>--Original Message--
>>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
>>.|||Yes i checked both and read all the articles but
does not solve it
basically it is something like this
transaction log space 2553.55
used: 43.25
unused:2510.3
so I am worried as how to get that unused spcae from log
file back and none of the soln seem to work.
>--Original Message--
>Did you read the links that were posted? Have you
checked to see if you
>have any open transactions with DBCC OPENTRAN()? Do you
have Truncate Log
>on Checkpoint turned on?
>--
>Andrew J. Kelly SQL MVP
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>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
>>.
>
>.
>|||Did you have a look with DBCC LOGINFO to see where the active VLF is? If it
is at the end you can not shrink it until you get it wrapped towards the
beginning. One of those links has a script for 7.0 to do this.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:161601c4f79e$6f5a9f80$a501280a@.phx.gbl...
> Yes i checked both and read all the articles but
> does not solve it
> basically it is something like this
> transaction log space 2553.55
> used: 43.25
> unused:2510.3
> so I am worried as how to get that unused spcae from log
> file back and none of the soln seem to work.
>
>>--Original Message--
>>Did you read the links that were posted? Have you
> checked to see if you
>>have any open transactions with DBCC OPENTRAN()? Do you
> have Truncate Log
>>on Checkpoint turned on?
>>--
>>Andrew J. Kelly SQL MVP
>>
>><anonymous@.discussions.microsoft.com> wrote in message
>>news:021b01c4f787$1d1fb780$a601280a@.phx.gbl...
>>I have tried all through enterprise manager and dbcc
>> shrinkfile but does not help it is not the datalog of
> the
>> log file getting big but the unused space in the log
>> files which needs to be truncated to a smaller size any
>> ideads
>>
>>--Original Message--
>>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
>>.
>>
>>.|||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)