Transaction Log  
Author Message
dancinlindy





PostPosted: Tue Apr 20 08:31:10 CDT 2004 Top

SQL Server >> Transaction Log

I am running Windows server 2000 w/SQL Server 2000. I
have an LDF file that is 30 GB. I am running a full
recovery model backup. I think this is a little too large
to be healthy. Any ideas how to control this thing from
getting larger?

Thanks in advance,

Darryl L.

SQL Server304  
 
 
Hari





PostPosted: Tue Apr 20 08:31:10 CDT 2004 Top

SQL Server >> Transaction Log Hi,

You have to backup the transaction log in frequent intervals if you are
using FULL recovery model. FULL recovery model is used on critical
databases to do a POINT_IN_TIME recovery incase of crash or wrong updates of
data.

How to reduce the size:

1. Backup the tranasction log

BACKUP LOG <DBNAME> to disk='d:\backup\dbname.TRN'

2. Shrink the tranasction log file to minimum size

DBCC SHRINKFILE('Logical_log_file_name',Truncateonly')


The above 2 steps will reduce the log file size, after this schedule a
transaction log backup every 1 hour or so...
This will ensure that your transaction log file wont pile up.
Other best approach is incase if your data is not production data or less
critical set the database recovery model to simple. In this model
transaction log will be cleared after the checkpoint.

There is good article in Tibers site:-

http://www.karaszi.com/sqlserver/info_dont_shrink.asp

Thanks
Hari
MCDBA



> I am running Windows server 2000 w/SQL Server 2000. I
> have an LDF file that is 30 GB. I am running a full
> recovery model backup. I think this is a little too large
> to be healthy. Any ideas how to control this thing from
> getting larger?
>
> Thanks in advance,
>
> Darryl L.


 
 
Darryl





PostPosted: Tue Apr 20 09:15:57 CDT 2004 Top

SQL Server >> Transaction Log Thanks for responding so quickly. I must be doing
something wrong however. In Query Analyzer I entered the
command you sent me. This is what I entered:
DBCC SHRINKFILE('k:\kronos\log\WFT_log.ldf'Truncateonly)

It tells me it can't locate file in sysfiles.

Any ideas?

Thanks,

Darryl


>-----Original Message-----
>Hi,
>
>You have to backup the transaction log in frequent
intervals if you are
>using FULL recovery model. FULL recovery model is used
on critical
>databases to do a POINT_IN_TIME recovery incase of crash
or wrong updates of
>data.
>
>How to reduce the size:
>
>1. Backup the tranasction log
>
> BACKUP LOG <DBNAME> to disk='d:\backup\dbname.TRN'
>
>2. Shrink the tranasction log file to minimum size
>
> DBCC SHRINKFILE('Logical_log_file_name',Truncateonly')
>
>
>The above 2 steps will reduce the log file size, after
this schedule a
>transaction log backup every 1 hour or so...
>This will ensure that your transaction log file wont
pile up.
>Other best approach is incase if your data is not
production data or less
>critical set the database recovery model to simple. In
this model
>transaction log will be cleared after the checkpoint.
>
>There is good article in Tibers site:-
>
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>Thanks
>Hari
>MCDBA
>

message

>> I am running Windows server 2000 w/SQL Server 2000. I
>> have an LDF file that is 30 GB. I am running a full
>> recovery model backup. I think this is a little too
large
>> to be healthy. Any ideas how to control this thing from
>> getting larger?
>>
>> Thanks in advance,
>>
>> Darryl L.
>
>
>.
>
 
 
Tibor





PostPosted: Tue Apr 20 09:24:48 CDT 2004 Top

SQL Server >> Transaction Log You should specify the logical file name, not the physical file name.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp



> Thanks for responding so quickly. I must be doing
> something wrong however. In Query Analyzer I entered the
> command you sent me. This is what I entered:
> DBCC SHRINKFILE('k:\kronos\log\WFT_log.ldf'Truncateonly)
>
> It tells me it can't locate file in sysfiles.
>
> Any ideas?
>
> Thanks,
>
> Darryl
>
>
> >-----Original Message-----
> >Hi,
> >
> >You have to backup the transaction log in frequent
> intervals if you are
> >using FULL recovery model. FULL recovery model is used
> on critical
> >databases to do a POINT_IN_TIME recovery incase of crash
> or wrong updates of
> >data.
> >
> >How to reduce the size:
> >
> >1. Backup the tranasction log
> >
> > BACKUP LOG <DBNAME> to disk='d:\backup\dbname.TRN'
> >
> >2. Shrink the tranasction log file to minimum size
> >
> > DBCC SHRINKFILE('Logical_log_file_name',Truncateonly')
> >
> >
> >The above 2 steps will reduce the log file size, after
> this schedule a
> >transaction log backup every 1 hour or so...
> >This will ensure that your transaction log file wont
> pile up.
> >Other best approach is incase if your data is not
> production data or less
> >critical set the database recovery model to simple. In
> this model
> >transaction log will be cleared after the checkpoint.
> >
> >There is good article in Tibers site:-
> >
> >http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> >
> >Thanks
> >Hari
> >MCDBA
> >

> message

> >> I am running Windows server 2000 w/SQL Server 2000. I
> >> have an LDF file that is 30 GB. I am running a full
> >> recovery model backup. I think this is a little too
> large
> >> to be healthy. Any ideas how to control this thing from
> >> getting larger?
> >>
> >> Thanks in advance,
> >>
> >> Darryl L.
> >
> >
> >.
> >


 
 
Darryl





PostPosted: Tue Apr 20 14:11:14 CDT 2004 Top

SQL Server >> Transaction Log How do I get the logical name?

>-----Original Message-----
>You should specify the logical file name, not the
physical file name.
>
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>


>> Thanks for responding so quickly. I must be doing
>> something wrong however. In Query Analyzer I entered
the
>> command you sent me. This is what I entered:
>> DBCC SHRINKFILE
('k:\kronos\log\WFT_log.ldf'Truncateonly)
>>
>> It tells me it can't locate file in sysfiles.
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Darryl
>>
>>
>> >-----Original Message-----
>> >Hi,
>> >
>> >You have to backup the transaction log in frequent
>> intervals if you are
>> >using FULL recovery model. FULL recovery model is
used
>> on critical
>> >databases to do a POINT_IN_TIME recovery incase of
crash
>> or wrong updates of
>> >data.
>> >
>> >How to reduce the size:
>> >
>> >1. Backup the tranasction log
>> >
>> > BACKUP LOG <DBNAME> to disk='d:\backup\dbname.TRN'
>> >
>> >2. Shrink the tranasction log file to minimum size
>> >
>> > DBCC SHRINKFILE
('Logical_log_file_name',Truncateonly')
>> >
>> >
>> >The above 2 steps will reduce the log file size, after
>> this schedule a
>> >transaction log backup every 1 hour or so...
>> >This will ensure that your transaction log file wont
>> pile up.
>> >Other best approach is incase if your data is not
>> production data or less
>> >critical set the database recovery model to simple. In
>> this model
>> >transaction log will be cleared after the checkpoint.
>> >
>> >There is good article in Tibers site:-
>> >
>> >http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>> >
>> >Thanks
>> >Hari
>> >MCDBA
>> >

in
>> message

>> >> I am running Windows server 2000 w/SQL Server 2000.
I
>> >> have an LDF file that is 30 GB. I am running a full
>> >> recovery model backup. I think this is a little too
>> large
>> >> to be healthy. Any ideas how to control this thing
from
>> >> getting larger?
>> >>
>> >> Thanks in advance,
>> >>
>> >> Darryl L.
>> >
>> >
>> >.
>> >
>
>
>.
>
 
 
Sue





PostPosted: Tue Apr 20 14:40:36 CDT 2004 Top

SQL Server >> Transaction Log You can execute sp_helpfile in your database to get the
logical file names, physical file names, etc for all files
for your database. Name column in the results is the logical
file.

-Sue

On Tue, 20 Apr 2004 12:11:14 -0700, "Darryl"


>How do I get the logical name?
>
>>-----Original Message-----
>>You should specify the logical file name, not the
>physical file name.
>>
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>


>>> Thanks for responding so quickly. I must be doing
>>> something wrong however. In Query Analyzer I entered
>the
>>> command you sent me. This is what I entered:
>>> DBCC SHRINKFILE
>('k:\kronos\log\WFT_log.ldf'Truncateonly)
>>>
>>> It tells me it can't locate file in sysfiles.
>>>
>>> Any ideas?
>>>
>>> Thanks,
>>>
>>> Darryl
>>>
>>>
>>> >-----Original Message-----
>>> >Hi,
>>> >
>>> >You have to backup the transaction log in frequent
>>> intervals if you are
>>> >using FULL recovery model. FULL recovery model is
>used
>>> on critical
>>> >databases to do a POINT_IN_TIME recovery incase of
>crash
>>> or wrong updates of
>>> >data.
>>> >
>>> >How to reduce the size:
>>> >
>>> >1. Backup the tranasction log
>>> >
>>> > BACKUP LOG <DBNAME> to disk='d:\backup\dbname.TRN'
>>> >
>>> >2. Shrink the tranasction log file to minimum size
>>> >
>>> > DBCC SHRINKFILE
>('Logical_log_file_name',Truncateonly')
>>> >
>>> >
>>> >The above 2 steps will reduce the log file size, after
>>> this schedule a
>>> >transaction log backup every 1 hour or so...
>>> >This will ensure that your transaction log file wont
>>> pile up.
>>> >Other best approach is incase if your data is not
>>> production data or less
>>> >critical set the database recovery model to simple. In
>>> this model
>>> >transaction log will be cleared after the checkpoint.
>>> >
>>> >There is good article in Tibers site:-
>>> >
>>> >http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>>> >
>>> >Thanks
>>> >Hari
>>> >MCDBA
>>> >

>in
>>> message

>>> >> I am running Windows server 2000 w/SQL Server 2000.
>I
>>> >> have an LDF file that is 30 GB. I am running a full
>>> >> recovery model backup. I think this is a little too
>>> large
>>> >> to be healthy. Any ideas how to control this thing
>from
>>> >> getting larger?
>>> >>
>>> >> Thanks in advance,
>>> >>
>>> >> Darryl L.
>>> >
>>> >
>>> >.
>>> >
>>
>>
>>.
>>

 
 
Darryl





PostPosted: Wed Apr 21 10:47:26 CDT 2004 Top

SQL Server >> Transaction Log Well I ran the TruncateOnly command and all I gained was
1mb.It's still 29 GB. What else can I try? Perhaps delete
the file? Frustrating!

Thanks in advance

>-----Original Message-----
>You can execute sp_helpfile in your database to get the
>logical file names, physical file names, etc for all
files
>for your database. Name column in the results is the
logical
>file.
>
>-Sue
>
>On Tue, 20 Apr 2004 12:11:14 -0700, "Darryl"

>
>>How do I get the logical name?
>>
>>>-----Original Message-----
>>>You should specify the logical file name, not the
>>physical file name.
>>>
>>>--
>>>Tibor Karaszi, SQL Server MVP
>>>http://www.karaszi.com/sqlserver/default.asp
>>>
>>>

in

>>>> Thanks for responding so quickly. I must be doing
>>>> something wrong however. In Query Analyzer I entered
>>the
>>>> command you sent me. This is what I entered:
>>>> DBCC SHRINKFILE
>>('k:\kronos\log\WFT_log.ldf'Truncateonly)
>>>>
>>>> It tells me it can't locate file in sysfiles.
>>>>
>>>> Any ideas?
>>>>
>>>> Thanks,
>>>>
>>>> Darryl
>>>>
>>>>
>>>> >-----Original Message-----
>>>> >Hi,
>>>> >
>>>> >You have to backup the transaction log in frequent
>>>> intervals if you are
>>>> >using FULL recovery model. FULL recovery model is
>>used
>>>> on critical
>>>> >databases to do a POINT_IN_TIME recovery incase of
>>crash
>>>> or wrong updates of
>>>> >data.
>>>> >
>>>> >How to reduce the size:
>>>> >
>>>> >1. Backup the tranasction log
>>>> >
>>>> > BACKUP LOG <DBNAME> to
disk='d:\backup\dbname.TRN'
>>>> >
>>>> >2. Shrink the tranasction log file to minimum size
>>>> >
>>>> > DBCC SHRINKFILE
>>('Logical_log_file_name',Truncateonly')
>>>> >
>>>> >
>>>> >The above 2 steps will reduce the log file size,
after
>>>> this schedule a
>>>> >transaction log backup every 1 hour or so...
>>>> >This will ensure that your transaction log file wont
>>>> pile up.
>>>> >Other best approach is incase if your data is not
>>>> production data or less
>>>> >critical set the database recovery model to simple.
In
>>>> this model
>>>> >transaction log will be cleared after the
checkpoint.
>>>> >
>>>> >There is good article in Tibers site:-
>>>> >
>>>>
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>>>> >
>>>> >Thanks
>>>> >Hari
>>>> >MCDBA
>>>> >

wrote
>>in
>>>> message

>>>> >> I am running Windows server 2000 w/SQL Server
2000.
>>I
>>>> >> have an LDF file that is 30 GB. I am running a
full
>>>> >> recovery model backup. I think this is a little
too
>>>> large
>>>> >> to be healthy. Any ideas how to control this
thing
>>from
>>>> >> getting larger?
>>>> >>
>>>> >> Thanks in advance,
>>>> >>
>>>> >> Darryl L.
>>>> >
>>>> >
>>>> >.
>>>> >
>>>
>>>
>>>.
>>>
>
>.
>
 
 
Sue





PostPosted: Wed Apr 21 14:14:53 CDT 2004 Top

SQL Server >> Transaction Log No...don't delete the log file. That's never a good idea.
Did you check for open transactions? You can do this using
dbcc opentran
Make sure you follow the steps Hari outlined:
backup the log then do the dbcc shrinkfile.

-Sue

On Wed, 21 Apr 2004 08:47:26 -0700, "Darryl"


>Well I ran the TruncateOnly command and all I gained was
>1mb.It's still 29 GB. What else can I try? Perhaps delete
>the file? Frustrating!
>
>Thanks in advance
>
>>-----Original Message-----
>>You can execute sp_helpfile in your database to get the
>>logical file names, physical file names, etc for all
>files
>>for your database. Name column in the results is the
>logical
>>file.
>>
>>-Sue
>>
>>On Tue, 20 Apr 2004 12:11:14 -0700, "Darryl"

>>
>>>How do I get the logical name?
>>>
>>>>-----Original Message-----
>>>>You should specify the logical file name, not the
>>>physical file name.
>>>>
>>>>--
>>>>Tibor Karaszi, SQL Server MVP
>>>>http://www.karaszi.com/sqlserver/default.asp
>>>>
>>>>

>in

>>>>> Thanks for responding so quickly. I must be doing
>>>>> something wrong however. In Query Analyzer I entered
>>>the
>>>>> command you sent me. This is what I entered:
>>>>> DBCC SHRINKFILE
>>>('k:\kronos\log\WFT_log.ldf'Truncateonly)
>>>>>
>>>>> It tells me it can't locate file in sysfiles.
>>>>>
>>>>> Any ideas?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Darryl
>>>>>
>>>>>
>>>>> >-----Original Message-----
>>>>> >Hi,
>>>>> >
>>>>> >You have to backup the transaction log in frequent
>>>>> intervals if you are
>>>>> >using FULL recovery model. FULL recovery model is
>>>used
>>>>> on critical
>>>>> >databases to do a POINT_IN_TIME recovery incase of
>>>crash
>>>>> or wrong updates of
>>>>> >data.
>>>>> >
>>>>> >How to reduce the size:
>>>>> >
>>>>> >1. Backup the tranasction log
>>>>> >
>>>>> > BACKUP LOG <DBNAME> to
>disk='d:\backup\dbname.TRN'
>>>>> >
>>>>> >2. Shrink the tranasction log file to minimum size
>>>>> >
>>>>> > DBCC SHRINKFILE
>>>('Logical_log_file_name',Truncateonly')
>>>>> >
>>>>> >
>>>>> >The above 2 steps will reduce the log file size,
>after
>>>>> this schedule a
>>>>> >transaction log backup every 1 hour or so...
>>>>> >This will ensure that your transaction log file wont
>>>>> pile up.
>>>>> >Other best approach is incase if your data is not
>>>>> production data or less
>>>>> >critical set the database recovery model to simple.
>In
>>>>> this model
>>>>> >transaction log will be cleared after the
>checkpoint.
>>>>> >
>>>>> >There is good article in Tibers site:-
>>>>> >
>>>>>
>>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>>>>> >
>>>>> >Thanks
>>>>> >Hari
>>>>> >MCDBA
>>>>> >

>wrote
>>>in
>>>>> message

>>>>> >> I am running Windows server 2000 w/SQL Server
>2000.
>>>I
>>>>> >> have an LDF file that is 30 GB. I am running a
>full
>>>>> >> recovery model backup. I think this is a little
>too
>>>>> large
>>>>> >> to be healthy. Any ideas how to control this
>thing
>>>from
>>>>> >> getting larger?
>>>>> >>
>>>>> >> Thanks in advance,
>>>>> >>
>>>>> >> Darryl L.
>>>>> >
>>>>> >
>>>>> >.
>>>>> >
>>>>
>>>>
>>>>.
>>>>
>>
>>.
>>