Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
Mitra
First, make sure that you are either backing up your database logs, or have the database set to simple mode.
You can most easily check to see the status of the database logs in enterprise manager by highlighting the database name and then selecting taskpad under the view menu. This will provide you with a graphical view of the size and of how much of the log is
full.
If it is full, switch to simple recovery under Database Properties in SQL Enterprise manager, and then choose All Tasks -> shrink database checking the shrink file option with the correct file chosen from the drop down menu on the top. (you may have to d
o this twice)
|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>
|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra

>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
>news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
>
>.
>

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
MitraFirst, make sure that you are either backing up your database logs, or have
the database set to simple mode.
You can most easily check to see the status of the database logs in enterpri
se manager by highlighting the database name and then selecting taskpad unde
r the view menu. This will provide you with a graphical view of the size an
d of how much of the log is
full.
If it is full, switch to simple recovery under Database Properties in SQL En
terprise manager, and then choose All Tasks -> shrink database checking the
shrink file option with the correct file chosen from the drop down menu on t
he top. (you may have to d
o this twice)|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx
.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra

>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
> news:130cf01c4437a$fc073b60$a001280a@.phx
.gbl...
>
>.
>

Question on shrinkingTransaction Log file

Hi All,
For some unknown reason one of our SQL Server 2000
database transaction log file grew to 25 GB before we
noticed it.
Thank god we noticed it before we ran out of space
completely.
Currently, we are left with 1 GB of free space and
the Data file size of the database is about 1.5 GB.
I looked over the help documentation on Books Online
trying to figure out how to reduce the size of the
transaction log file.
I read that i need to truncate the transaction log file
before I could shrink the size of the transaction log
file.
I read that the "active" portion of the transaction log
file can never be truncated.
How would I figure out what portion of this 25 GB
transaction log file is active?
What if all of it is, then how can I shrink the size of
the file?
At this point I MUST shrink the transaction log file,
and later try to trace what caused it to grow so big.
Please advise the best way I can shrink the size of the
transaction log file to the initial size we originally
had allocated, 100 MB.
Thank you,
MitraFirst, make sure that you are either backing up your database logs, or have the database set to simple mode
You can most easily check to see the status of the database logs in enterprise manager by highlighting the database name and then selecting taskpad under the view menu. This will provide you with a graphical view of the size and of how much of the log is full
If it is full, switch to simple recovery under Database Properties in SQL Enterprise manager, and then choose All Tasks -> shrink database checking the shrink file option with the correct file chosen from the drop down menu on the top. (you may have to do this twice)|||Hi,
After Transaction log backup the physical file will not shrink automatically
if the "Auto shrink" option is enabled. Please do not
enable this option because this will affect your OLTP processng because the
Auto shrink will take more I/O.
How to reduce the Transaction log size
1. Backup the transaction log (Use BACKUP Log dbname to
disk='c:\backup\dbname.tr1' or use enterprise manager)
or (Since you done have the hard disk space truncate the trasaction log)
backup log <dbname> with truncate_only
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Have a look into the below link.
Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://www.support.microsoft.com/?id=272318
Thanks
Hari
MCDBA
"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
> Hi All,
> For some unknown reason one of our SQL Server 2000
> database transaction log file grew to 25 GB before we
> noticed it.
> Thank god we noticed it before we ran out of space
> completely.
> Currently, we are left with 1 GB of free space and
> the Data file size of the database is about 1.5 GB.
> I looked over the help documentation on Books Online
> trying to figure out how to reduce the size of the
> transaction log file.
> I read that i need to truncate the transaction log file
> before I could shrink the size of the transaction log
> file.
> I read that the "active" portion of the transaction log
> file can never be truncated.
> How would I figure out what portion of this 25 GB
> transaction log file is active?
> What if all of it is, then how can I shrink the size of
> the file?
> At this point I MUST shrink the transaction log file,
> and later try to trace what caused it to grow so big.
> Please advise the best way I can shrink the size of the
> transaction log file to the initial size we originally
> had allocated, 100 MB.
> Thank you,
> Mitra
>|||Hi,
I am not clear about what would happen when i try to back
up a transaction log that is 25GB and I've got only 1 GB
free disk space.
Are you saying that if i run the statement you wrote:
backup log <dbname> with truncate_only
I should be okay and don't need to worry about how much
free space is available?
Thank you,
Mitra
>--Original Message--
>Hi,
>After Transaction log backup the physical file will not
shrink automatically
>if the "Auto shrink" option is enabled. Please do not
>enable this option because this will affect your OLTP
processng because the
>Auto shrink will take more I/O.
>How to reduce the Transaction log size
>1. Backup the transaction log (Use BACKUP Log dbname to
>disk='c:\backup\dbname.tr1' or use enterprise manager)
>or (Since you done have the hard disk space truncate the
trasaction log)
>backup log <dbname> with truncate_only
>3. Shrink the transaction log file.
> DBCC SHRINKFILE('logical_transaction_log file
name','truncateonly')
>Have a look into the below link.
>Shrinking the Transaction Log in SQL Server 2000 with
DBCC
>http://www.support.microsoft.com/?id=272318
>
>Thanks
>Hari
>MCDBA
>
>
>
>"Mitra Fatholahi" <mitra928@.hotmail.com> wrote in message
>news:130cf01c4437a$fc073b60$a001280a@.phx.gbl...
>> Hi All,
>> For some unknown reason one of our SQL Server 2000
>> database transaction log file grew to 25 GB before we
>> noticed it.
>> Thank god we noticed it before we ran out of space
>> completely.
>> Currently, we are left with 1 GB of free space and
>> the Data file size of the database is about 1.5 GB.
>> I looked over the help documentation on Books Online
>> trying to figure out how to reduce the size of the
>> transaction log file.
>> I read that i need to truncate the transaction log file
>> before I could shrink the size of the transaction log
>> file.
>> I read that the "active" portion of the transaction log
>> file can never be truncated.
>> How would I figure out what portion of this 25 GB
>> transaction log file is active?
>> What if all of it is, then how can I shrink the size of
>> the file?
>> At this point I MUST shrink the transaction log file,
>> and later try to trace what caused it to grow so big.
>> Please advise the best way I can shrink the size of the
>> transaction log file to the initial size we originally
>> had allocated, 100 MB.
>> Thank you,
>> Mitra
>>
>
>.
>