how to shrink tran-logs of databases

hi,
work on sql server 2000.
I have serveral dbs that are either 'full' or 'simple',
their tran-logs have lots of empty space in it due to one-
time big processes, hence, I want to shrink their physical
size down.
I used the below query on several dbs, some log size got
shrinked, some didn't shrink at all. how come? I didn't
perform a backup log operation for any of the db before I
run this query:
use db1
DBCC SHRINKFILE (db1_log1_logical_name)
many thanks.
JJ
HI,
You need to perform a backup log if the database recovery model is FULL or
BULK_LOGGED. Otherwise the inactive trasnaction logs will not be removed and
though your shrinkfile command will shrink. in case if you donot need the
trasnactions you could truncate the logs
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
|||hey Hari, it's you again! thanks for all the help. I
think you are definately right.
one more question, I also want to shrink this db's data
file which is total 21 gig, with over 9 gig free. I want
to shrink off those 9 gigs. I started by shrinking down
25 mg only, it took 11 minutes! my God, for 9 gig, it
will take 66 hrs then!
how come so slow?
thanks
JJ
>--Original Message--
>HI,
>You need to perform a backup log if the database recovery
model is FULL or
>BULK_LOGGED. Otherwise the inactive trasnaction logs will
not be removed and
>though your shrinkfile command will shrink. in case if
you donot need the
>trasnactions you could truncate the logs
>backup log <dbname> with truncate_only
>go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
>Now execute the below command to see log file size and
usage.
>DBCC SQLPERF(LOGSPACE)
>Thanks
>Hari
>MCDBA
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:28e4a01c46551$c5a68f00$a601280a@.phx.gbl...
one-[vbcol=seagreen]
physical[vbcol=seagreen]
I
>
>.
>
|||JJ,
9GB of free spacein a 21GB DB is not too much. You need free space for a
database to operate properly anyway for things such as reindexing and you
always want room to add more data without growing. In any case the reason
it takes so long is that it shrinks from the end of the file and must move
any data that is near the end to a free spot at the beginning. This process
is resource intensive and can take a while depending on your hardware and if
the log files are on their own raid array or not. 11 minutes for only 25MB
does seem a little excessive but if your disks are poorly configured it's
not out of the question. Shrinking the file will also fragment your data
files and you may want to reindex afterwards. But if you shrink it too much
that will cause it to grow again<g>.
Andrew J. Kelly SQL MVP
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...[vbcol=seagreen]
> hey Hari, it's you again! thanks for all the help. I
> think you are definately right.
> one more question, I also want to shrink this db's data
> file which is total 21 gig, with over 9 gig free. I want
> to shrink off those 9 gigs. I started by shrinking down
> 25 mg only, it took 11 minutes! my God, for 9 gig, it
> will take 66 hrs then!
> how come so slow?
> thanks
> JJ
> model is FULL or
> not be removed and
> you donot need the
> usage.
> message
> one-
> physical
> I
|||Hi,
Add on to Andrews detailed description, please shrink the MDF file in Single
user mode and during reindexing make the database recovery model to
BULK_LOGGED.
Alter database <dbname> set single_user with rollback immediate
go
dbcc shrinkfile('logical_mdf_name',size_in_mb)
go
Now reindex
go
-- Multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#e9ZYzVZEHA.212@.TK2MSFTNGP12.phx.gbl...
> JJ,
> 9GB of free spacein a 21GB DB is not too much. You need free space for a
> database to operate properly anyway for things such as reindexing and you
> always want room to add more data without growing. In any case the reason
> it takes so long is that it shrinks from the end of the file and must move
> any data that is near the end to a free spot at the beginning. This
process
> is resource intensive and can take a while depending on your hardware and
if
> the log files are on their own raid array or not. 11 minutes for only 25MB
> does seem a little excessive but if your disks are poorly configured it's
> not out of the question. Shrinking the file will also fragment your data
> files and you may want to reindex afterwards. But if you shrink it too
much
> that will cause it to grow again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
>
|||Don't EVER leave the transaction log on 'full' recovery - it will absolutely eat your disks. ALWAYS have the default for ALL databases to be on 'simple'.
"JJ Wang" wrote:

> hi,
> work on sql server 2000.
> I have serveral dbs that are either 'full' or 'simple',
> their tran-logs have lots of empty space in it due to one-
> time big processes, hence, I want to shrink their physical
> size down.
> I used the below query on several dbs, some log size got
> shrinked, some didn't shrink at all. how come? I didn't
> perform a backup log operation for any of the db before I
> run this query:
> use db1
> DBCC SHRINKFILE (db1_log1_logical_name)
> many thanks.
> JJ
>
|||That is absolutely wrong advise. The reason it eats up your disks is that
you are not doing regular log backups to keep it in check. If you need to
have the ability to recover up to the minute there is no choice but to use
Full recovery mode.
Andrew J. Kelly SQL MVP
"Beeeeeeeeeeeeves" <Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
message news:EF5BCB8B-0C14-4728-A2B3-D3E8907D7171@.microsoft.com...
> Don't EVER leave the transaction log on 'full' recovery - it will
absolutely eat your disks. ALWAYS have the default for ALL databases to be
on 'simple'.[vbcol=seagreen]
> "JJ Wang" wrote:
|||hi Andrew,
you sure always have good advices. The reason I want to
shrink this files was that we had a one time temperaroy
data expansion, this db's size should stay pretty constant
because we have a history archive db for it. and this is
just one of the files taht we need to shrink, there are
more files that have more unneeded space.
we have tran-log disk (1,0) seperate from our data disk
(raid 5). what do you mean by 'if the disk is poorly
designed'? how to judge that?
also, you mentioned: 'it takes so long is that it shrinks
from the end of the file and must move any data that is
near the end to a free spot at the beginning. ', is there
an option that we don't move the data around when
shrinking?
many thanks.
JJ

>--Original Message--
>JJ,
>9GB of free spacein a 21GB DB is not too much. You need
free space for a
>database to operate properly anyway for things such as
reindexing and you
>always want room to add more data without growing. In
any case the reason
>it takes so long is that it shrinks from the end of the
file and must move
>any data that is near the end to a free spot at the
beginning. This process
>is resource intensive and can take a while depending on
your hardware and if
>the log files are on their own raid array or not. 11
minutes for only 25MB
>does seem a little excessive but if your disks are poorly
configured it's
>not out of the question. Shrinking the file will also
fragment your data
>files and you may want to reindex afterwards. But if you
shrink it too much
>that will cause it to grow again<g>.
>--
>Andrew J. Kelly SQL MVP
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2a18601c4655a$809f44c0$a301280a@.phx.gbl...
want[vbcol=seagreen]
recovery[vbcol=seagreen]
will[vbcol=seagreen]
in[vbcol=seagreen]
or 'simple',[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before
>
>.
>
|||thanks Andrew.
backup the log doesn't shrink its physical size though.
is the best way to shrink a log is to do it right after
the backup log job (maybe as the next step in the backup
log job)?
JJ
>--Original Message--
>That is absolutely wrong advise. The reason it eats up
your disks is that
>you are not doing regular log backups to keep it in
check. If you need to
>have the ability to recover up to the minute there is no
choice but to use
>Full recovery mode.
>--
>Andrew J. Kelly SQL MVP
>
>"Beeeeeeeeeeeeves"
<Beeeeeeeeeeeeves@.discussions.microsoft.com> wrote in
>message news:EF5BCB8B-0C14-4728-A2B3-
D3E8907D7171@.microsoft.com...[vbcol=seagreen]
recovery - it will
>absolutely eat your disks. ALWAYS have the default for
ALL databases to be[vbcol=seagreen]
>on 'simple'.
or 'simple',[vbcol=seagreen]
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
before I
>
>.
>
|||I totally agree with Andrew. but thanks anyway. :-)
JJ
>--Original Message--
>Don't EVER leave the transaction log on 'full' recovery -
it will absolutely eat your disks. ALWAYS have the default
for ALL databases to be on 'simple'.[vbcol=seagreen]
>"JJ Wang" wrote:
one-[vbcol=seagreen]
physical[vbcol=seagreen]
got[vbcol=seagreen]
didn't[vbcol=seagreen]
I
>.
>
sql