Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt
.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Showing posts with label growing. Show all posts
Showing posts with label growing. Show all posts
Friday, March 30, 2012
LOG of TEMPDB are growing
Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> > Hi,
> > The log of the TEMPDB database is growing and growing. What should i do ?
> > What did i arrive to this situation?
> > For information we are using SSIS and OLAP, maybe it will help to answer.
> > Thanks.
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>> Hi,
>> The log of the TEMPDB database is growing and growing. What should i do ?
>> What did i arrive to this situation?
>> For information we are using SSIS and OLAP, maybe it will help to answer.
>> Thanks.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> > Hi,
> > The log of the TEMPDB database is growing and growing. What should i do ?
> > What did i arrive to this situation?
> > For information we are using SSIS and OLAP, maybe it will help to answer.
> > Thanks.
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>> Hi,
>> The log of the TEMPDB database is growing and growing. What should i do ?
>> What did i arrive to this situation?
>> For information we are using SSIS and OLAP, maybe it will help to answer.
>> Thanks.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
LOG of TEMPDB are growing
Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.
Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.
|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>
|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
sql
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.
Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.
|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>
|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
sql
Log just keeps growing!
> Hi,
>
> My 600Mb database continually has a 4Gb log file with it, which is a bit
of
> a pain as we have a very small number of inserts during the day so there's
> no need for the log to be this big.
>
> We do have a load process that loads approx 150,000 - 200,000 rows
running
> once a week, but, even still I can't see why the log grows as it does.
Every
> night we have a full backup running.
>
> So, I've got a couple of questions:
>
> 1) - What backup mode do I need to have set on the DB to ensure once
> it's backed up the log is then truncated correctly? I'm guessing the log
> itself is being truncated but the actual file isn't being shrunk?
>
> 2) - I keep detaching the DB and then reattaching it without a log
to
> get rid of the huge log file. Is this the best way to keep this huge log
> under control or am I risking data corruption?
>
> 3) - Are there any jobs I can run during the day that can keep this
> log under control?
>
> Any advice appreciated.
>
> Thanks
>
>You can use simple recovery mode. For more information check these out:
http://www.support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=272318
http://www.support.microsoft.com/?id=317375
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>|||Bear in mind, the transaction log supports point-in-time database recovery.
If you need this functionality, truncating the log frequently during the day
will cause a problem.
What you should do is schedule frequent transaction log backups (to disk if
you plan point-in-time recovery, then make sure to back those up to tape
quickly, so you can clear off disk space). To recover the newly freed
space, schedule a job to run fairly frequently in the database that does
dbcc shrinkfile (<database_name>_log, 0) (in SQL 7, do not put the ,0 in).
Anyhow, here is your detail on backup modes:
Full recovery model (select into/bulk copy disabled, trunc. log on chkpt.
disabled) -- This is the default. It enables you to do point-in-time
restores, but the transaction log will grow unless you back it up frequently
to disk, and then shrink the log file afterwards.
Bulk logged recovery model (select into/bulk copy enabled, trunc. log on
chkpt. disabled) -- This improves the performance of bulk data loads and
other data processes (eg create index) other than simple INSERTs, UPDATEs
and DELETEs by only minimally logging the event. If you plan to backup the
transaction log so you can restore from it later (point-in-time restore),
you need to a full or differential database backup AFTER EVERY database
operation other than a INSERT, UPDATE or DELETE. You still need to backup
the log frequently, and schedule dbcc shrinkfile to return the inactive
space to the OS
Simple Recovery model (trunc. log on chkpt and select/into bulk copy
enabled). Not only are major database operations not logged, the
transaction log is automatically emptied out at regular intervals
(checkpoints). The only backups you can restore from here are full database
backups or differential backups. You may need to still run dbcc shrinkfile
to keep the log file under control.
If you have more questions, you can try emailing me, I'm not sure if my
account is still getting spammed out of control or not.
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andy_mcdba@.yahoo.com
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
Andy_mcdba@.yahoo.com gets filled up to the account
limit with spam every couple of hours now so replies may
not be possible. I will remove this disclaimer once every
ISP involved with relaying the spam can help me out.
*******************************************************************
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>
>
> My 600Mb database continually has a 4Gb log file with it, which is a bit
of
> a pain as we have a very small number of inserts during the day so there's
> no need for the log to be this big.
>
> We do have a load process that loads approx 150,000 - 200,000 rows
running
> once a week, but, even still I can't see why the log grows as it does.
Every
> night we have a full backup running.
>
> So, I've got a couple of questions:
>
> 1) - What backup mode do I need to have set on the DB to ensure once
> it's backed up the log is then truncated correctly? I'm guessing the log
> itself is being truncated but the actual file isn't being shrunk?
>
> 2) - I keep detaching the DB and then reattaching it without a log
to
> get rid of the huge log file. Is this the best way to keep this huge log
> under control or am I risking data corruption?
>
> 3) - Are there any jobs I can run during the day that can keep this
> log under control?
>
> Any advice appreciated.
>
> Thanks
>
>You can use simple recovery mode. For more information check these out:
http://www.support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=272318
http://www.support.microsoft.com/?id=317375
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>|||Bear in mind, the transaction log supports point-in-time database recovery.
If you need this functionality, truncating the log frequently during the day
will cause a problem.
What you should do is schedule frequent transaction log backups (to disk if
you plan point-in-time recovery, then make sure to back those up to tape
quickly, so you can clear off disk space). To recover the newly freed
space, schedule a job to run fairly frequently in the database that does
dbcc shrinkfile (<database_name>_log, 0) (in SQL 7, do not put the ,0 in).
Anyhow, here is your detail on backup modes:
Full recovery model (select into/bulk copy disabled, trunc. log on chkpt.
disabled) -- This is the default. It enables you to do point-in-time
restores, but the transaction log will grow unless you back it up frequently
to disk, and then shrink the log file afterwards.
Bulk logged recovery model (select into/bulk copy enabled, trunc. log on
chkpt. disabled) -- This improves the performance of bulk data loads and
other data processes (eg create index) other than simple INSERTs, UPDATEs
and DELETEs by only minimally logging the event. If you plan to backup the
transaction log so you can restore from it later (point-in-time restore),
you need to a full or differential database backup AFTER EVERY database
operation other than a INSERT, UPDATE or DELETE. You still need to backup
the log frequently, and schedule dbcc shrinkfile to return the inactive
space to the OS
Simple Recovery model (trunc. log on chkpt and select/into bulk copy
enabled). Not only are major database operations not logged, the
transaction log is automatically emptied out at regular intervals
(checkpoints). The only backups you can restore from here are full database
backups or differential backups. You may need to still run dbcc shrinkfile
to keep the log file under control.
If you have more questions, you can try emailing me, I'm not sure if my
account is still getting spammed out of control or not.
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andy_mcdba@.yahoo.com
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
Andy_mcdba@.yahoo.com gets filled up to the account
limit with spam every couple of hours now so replies may
not be possible. I will remove this disclaimer once every
ISP involved with relaying the spam can help me out.
*******************************************************************
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>
Log is growing crazy when run DBCC INDEXDEFRAG or DBREINDEX
We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql
Wednesday, March 28, 2012
Log Grow
Hi,
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1o Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2o Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1o Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2o Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>
Log Grow
Hi,
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1º Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2º Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1º Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2º Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Log Files Growing out of control
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Monday, March 26, 2012
Log file won't shrink
We're having a problem with one log file which refuses to be shrunk.
First off we have a problem with the fact that the log keeps growing way too
much, something that I have to look into, but besides that, I've tried to
shrink it down as it will end up eating up all the available disk space,
eventually.
I heard that DBCC SHRINKFILE will create dummy transactions in order to get
the log file to recycle, so that it will shrink, but it doesn't seem to want
to do that.
I've tried performing BACKUP LOG with various options and then trying to
shrink the log file, all to no avail.
Any hints or suggestions welcome as to how I can force this overgrown log
file back down to a more reasonable size.
Michael MacGregor
Database Architect
Are you using the syntax:
DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>From BOL:
TRUNCATEONLY
Causes any unused space in the files to be released to the operating
system and shrinks the file to the last allocated extent, reducing the
file size without moving any data. No attempt is made to relocate rows
to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
WITH
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Michael MacGregor wrote:
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way too
> much, something that I have to look into, but besides that, I've tried to
> shrink it down as it will end up eating up all the available disk space,
> eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to get
> the log file to recycle, so that it will shrink, but it doesn't seem to want
> to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
|||Michael,
Is your log unrestricted? If it is then give it a maximum otherwise (under
SQL2005 and maybe SQL2000) it will keep on growing.
Chris
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uNI12wd$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way
> too much, something that I have to look into, but besides that, I've tried
> to shrink it down as it will end up eating up all the available disk
> space, eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to
> get the log file to recycle, so that it will shrink, but it doesn't seem
> to want to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
>
|||Yes.
<tootsuite@.gmail.com> wrote in message
news:1162403350.700370.279630@.e64g2000cwd.googlegr oups.com...
> Are you using the syntax:
> DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
> system and shrinks the file to the last allocated extent, reducing the
> file size without moving any data. No attempt is made to relocate rows
> to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
> WITH
> DBCC SHRINKFILE
> (
> { 'file_name' | file_id }
> { [ , EMPTYFILE ]
> | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
> }
> )
> [ WITH NO_INFOMSGS ]
>
> Michael MacGregor wrote:
>
|||I'm actually not bothered by that, what is concerning me right now is that I
can't get it to shrink, the continual growth issue is not a concern right
now, as I believe I know what is causing that anyway and I have to deal with
that rather than restrict the log growth.
I have queries sys.databases and it is telling me that the
log_reuse_wait_desc is REPLICATION, but so far am going round in circles
trying to figure out why and what to do about it.
Michael MacGregor
Database Architect
|||Fixed it! There was a replication transaction still open.
Michael MacGregor
Database Architect
sql
First off we have a problem with the fact that the log keeps growing way too
much, something that I have to look into, but besides that, I've tried to
shrink it down as it will end up eating up all the available disk space,
eventually.
I heard that DBCC SHRINKFILE will create dummy transactions in order to get
the log file to recycle, so that it will shrink, but it doesn't seem to want
to do that.
I've tried performing BACKUP LOG with various options and then trying to
shrink the log file, all to no avail.
Any hints or suggestions welcome as to how I can force this overgrown log
file back down to a more reasonable size.
Michael MacGregor
Database Architect
Are you using the syntax:
DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>From BOL:
TRUNCATEONLY
Causes any unused space in the files to be released to the operating
system and shrinks the file to the last allocated extent, reducing the
file size without moving any data. No attempt is made to relocate rows
to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
WITH
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Michael MacGregor wrote:
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way too
> much, something that I have to look into, but besides that, I've tried to
> shrink it down as it will end up eating up all the available disk space,
> eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to get
> the log file to recycle, so that it will shrink, but it doesn't seem to want
> to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
|||Michael,
Is your log unrestricted? If it is then give it a maximum otherwise (under
SQL2005 and maybe SQL2000) it will keep on growing.
Chris
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uNI12wd$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way
> too much, something that I have to look into, but besides that, I've tried
> to shrink it down as it will end up eating up all the available disk
> space, eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to
> get the log file to recycle, so that it will shrink, but it doesn't seem
> to want to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
>
|||Yes.
<tootsuite@.gmail.com> wrote in message
news:1162403350.700370.279630@.e64g2000cwd.googlegr oups.com...
> Are you using the syntax:
> DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
> system and shrinks the file to the last allocated extent, reducing the
> file size without moving any data. No attempt is made to relocate rows
> to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
> WITH
> DBCC SHRINKFILE
> (
> { 'file_name' | file_id }
> { [ , EMPTYFILE ]
> | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
> }
> )
> [ WITH NO_INFOMSGS ]
>
> Michael MacGregor wrote:
>
|||I'm actually not bothered by that, what is concerning me right now is that I
can't get it to shrink, the continual growth issue is not a concern right
now, as I believe I know what is causing that anyway and I have to deal with
that rather than restrict the log growth.
I have queries sys.databases and it is telling me that the
log_reuse_wait_desc is REPLICATION, but so far am going round in circles
trying to figure out why and what to do about it.
Michael MacGregor
Database Architect
|||Fixed it! There was a replication transaction still open.
Michael MacGregor
Database Architect
sql
Log file won't shrink
We're having a problem with one log file which refuses to be shrunk.
First off we have a problem with the fact that the log keeps growing way too
much, something that I have to look into, but besides that, I've tried to
shrink it down as it will end up eating up all the available disk space,
eventually.
I heard that DBCC SHRINKFILE will create dummy transactions in order to get
the log file to recycle, so that it will shrink, but it doesn't seem to want
to do that.
I've tried performing BACKUP LOG with various options and then trying to
shrink the log file, all to no avail.
Any hints or suggestions welcome as to how I can force this overgrown log
file back down to a more reasonable size.
Michael MacGregor
Database ArchitectAre you using the syntax:
DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>From BOL:
TRUNCATEONLY
Causes any unused space in the files to be released to the operating
system and shrinks the file to the last allocated extent, reducing the
file size without moving any data. No attempt is made to relocate rows
to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
WITH
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Michael MacGregor wrote:
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way t
oo
> much, something that I have to look into, but besides that, I've tried to
> shrink it down as it will end up eating up all the available disk space,
> eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to ge
t
> the log file to recycle, so that it will shrink, but it doesn't seem to wa
nt
> to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect|||Michael,
Is your log unrestricted? If it is then give it a maximum otherwise (under
SQL2005 and maybe SQL2000) it will keep on growing.
Chris
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uNI12wd$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way
> too much, something that I have to look into, but besides that, I've tried
> to shrink it down as it will end up eating up all the available disk
> space, eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to
> get the log file to recycle, so that it will shrink, but it doesn't seem
> to want to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
>|||Yes.
<tootsuite@.gmail.com> wrote in message
news:1162403350.700370.279630@.e64g2000cwd.googlegroups.com...
> Are you using the syntax:
> DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
> system and shrinks the file to the last allocated extent, reducing the
> file size without moving any data. No attempt is made to relocate rows
> to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
> WITH
> DBCC SHRINKFILE
> (
> { 'file_name' | file_id }
> { [ , EMPTYFILE ]
> | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY
} ] ]
> }
> )
> [ WITH NO_INFOMSGS ]
>
> Michael MacGregor wrote:
>|||I'm actually not bothered by that, what is concerning me right now is that I
can't get it to shrink, the continual growth issue is not a concern right
now, as I believe I know what is causing that anyway and I have to deal with
that rather than restrict the log growth.
I have queries sys.databases and it is telling me that the
log_reuse_wait_desc is REPLICATION, but so far am going round in circles
trying to figure out why and what to do about it.
Michael MacGregor
Database Architect|||Michael MacGregor wrote:
> I'm actually not bothered by that, what is concerning me right now is that
I
> can't get it to shrink, the continual growth issue is not a concern right
> now, as I believe I know what is causing that anyway and I have to deal wi
th
> that rather than restrict the log growth.
> I have queries sys.databases and it is telling me that the
> log_reuse_wait_desc is REPLICATION, but so far am going round in circles
> trying to figure out why and what to do about it.
> Michael MacGregor
> Database Architect
>
That would seem to be telling you that this database is involved in
replication, which would explain why the log won't truncate.
Transactions can't be flushed from the log if they haven't been
replicated yet.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I gathered that from the info in BOL, but I haven't figured out how to flush
that, or stop the replication.
Michael MacGregor
Database Architect|||Fixed it! There was a replication transaction still open.
Michael MacGregor
Database Architect
First off we have a problem with the fact that the log keeps growing way too
much, something that I have to look into, but besides that, I've tried to
shrink it down as it will end up eating up all the available disk space,
eventually.
I heard that DBCC SHRINKFILE will create dummy transactions in order to get
the log file to recycle, so that it will shrink, but it doesn't seem to want
to do that.
I've tried performing BACKUP LOG with various options and then trying to
shrink the log file, all to no avail.
Any hints or suggestions welcome as to how I can force this overgrown log
file back down to a more reasonable size.
Michael MacGregor
Database ArchitectAre you using the syntax:
DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>From BOL:
TRUNCATEONLY
Causes any unused space in the files to be released to the operating
system and shrinks the file to the last allocated extent, reducing the
file size without moving any data. No attempt is made to relocate rows
to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
WITH
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Michael MacGregor wrote:
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way t
oo
> much, something that I have to look into, but besides that, I've tried to
> shrink it down as it will end up eating up all the available disk space,
> eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to ge
t
> the log file to recycle, so that it will shrink, but it doesn't seem to wa
nt
> to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect|||Michael,
Is your log unrestricted? If it is then give it a maximum otherwise (under
SQL2005 and maybe SQL2000) it will keep on growing.
Chris
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uNI12wd$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way
> too much, something that I have to look into, but besides that, I've tried
> to shrink it down as it will end up eating up all the available disk
> space, eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to
> get the log file to recycle, so that it will shrink, but it doesn't seem
> to want to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
>|||Yes.
<tootsuite@.gmail.com> wrote in message
news:1162403350.700370.279630@.e64g2000cwd.googlegroups.com...
> Are you using the syntax:
> DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
> system and shrinks the file to the last allocated extent, reducing the
> file size without moving any data. No attempt is made to relocate rows
> to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
> WITH
> DBCC SHRINKFILE
> (
> { 'file_name' | file_id }
> { [ , EMPTYFILE ]
> | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY
} ] ]
> }
> )
> [ WITH NO_INFOMSGS ]
>
> Michael MacGregor wrote:
>|||I'm actually not bothered by that, what is concerning me right now is that I
can't get it to shrink, the continual growth issue is not a concern right
now, as I believe I know what is causing that anyway and I have to deal with
that rather than restrict the log growth.
I have queries sys.databases and it is telling me that the
log_reuse_wait_desc is REPLICATION, but so far am going round in circles
trying to figure out why and what to do about it.
Michael MacGregor
Database Architect|||Michael MacGregor wrote:
> I'm actually not bothered by that, what is concerning me right now is that
I
> can't get it to shrink, the continual growth issue is not a concern right
> now, as I believe I know what is causing that anyway and I have to deal wi
th
> that rather than restrict the log growth.
> I have queries sys.databases and it is telling me that the
> log_reuse_wait_desc is REPLICATION, but so far am going round in circles
> trying to figure out why and what to do about it.
> Michael MacGregor
> Database Architect
>
That would seem to be telling you that this database is involved in
replication, which would explain why the log won't truncate.
Transactions can't be flushed from the log if they haven't been
replicated yet.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I gathered that from the info in BOL, but I haven't figured out how to flush
that, or stop the replication.
Michael MacGregor
Database Architect|||Fixed it! There was a replication transaction still open.
Michael MacGregor
Database Architect
Log file won't shrink
We're having a problem with one log file which refuses to be shrunk.
First off we have a problem with the fact that the log keeps growing way too
much, something that I have to look into, but besides that, I've tried to
shrink it down as it will end up eating up all the available disk space,
eventually.
I heard that DBCC SHRINKFILE will create dummy transactions in order to get
the log file to recycle, so that it will shrink, but it doesn't seem to want
to do that.
I've tried performing BACKUP LOG with various options and then trying to
shrink the log file, all to no avail.
Any hints or suggestions welcome as to how I can force this overgrown log
file back down to a more reasonable size.
Michael MacGregor
Database ArchitectAre you using the syntax:
DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>From BOL:
TRUNCATEONLY
Causes any unused space in the files to be released to the operating
system and shrinks the file to the last allocated extent, reducing the
file size without moving any data. No attempt is made to relocate rows
to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
WITH
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Michael MacGregor wrote:
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way too
> much, something that I have to look into, but besides that, I've tried to
> shrink it down as it will end up eating up all the available disk space,
> eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to get
> the log file to recycle, so that it will shrink, but it doesn't seem to want
> to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect|||Michael,
Is your log unrestricted? If it is then give it a maximum otherwise (under
SQL2005 and maybe SQL2000) it will keep on growing.
Chris
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uNI12wd$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way
> too much, something that I have to look into, but besides that, I've tried
> to shrink it down as it will end up eating up all the available disk
> space, eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to
> get the log file to recycle, so that it will shrink, but it doesn't seem
> to want to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
>|||Yes.
<tootsuite@.gmail.com> wrote in message
news:1162403350.700370.279630@.e64g2000cwd.googlegroups.com...
> Are you using the syntax:
> DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>>From BOL:
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
> system and shrinks the file to the last allocated extent, reducing the
> file size without moving any data. No attempt is made to relocate rows
> to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
> WITH
> DBCC SHRINKFILE
> (
> { 'file_name' | file_id }
> { [ , EMPTYFILE ]
> | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
> }
> )
> [ WITH NO_INFOMSGS ]
>
> Michael MacGregor wrote:
>> We're having a problem with one log file which refuses to be shrunk.
>> First off we have a problem with the fact that the log keeps growing way
>> too
>> much, something that I have to look into, but besides that, I've tried to
>> shrink it down as it will end up eating up all the available disk space,
>> eventually.
>> I heard that DBCC SHRINKFILE will create dummy transactions in order to
>> get
>> the log file to recycle, so that it will shrink, but it doesn't seem to
>> want
>> to do that.
>> I've tried performing BACKUP LOG with various options and then trying to
>> shrink the log file, all to no avail.
>> Any hints or suggestions welcome as to how I can force this overgrown log
>> file back down to a more reasonable size.
>> Michael MacGregor
>> Database Architect
>|||I'm actually not bothered by that, what is concerning me right now is that I
can't get it to shrink, the continual growth issue is not a concern right
now, as I believe I know what is causing that anyway and I have to deal with
that rather than restrict the log growth.
I have queries sys.databases and it is telling me that the
log_reuse_wait_desc is REPLICATION, but so far am going round in circles
trying to figure out why and what to do about it.
Michael MacGregor
Database Architect|||Michael MacGregor wrote:
> I'm actually not bothered by that, what is concerning me right now is that I
> can't get it to shrink, the continual growth issue is not a concern right
> now, as I believe I know what is causing that anyway and I have to deal with
> that rather than restrict the log growth.
> I have queries sys.databases and it is telling me that the
> log_reuse_wait_desc is REPLICATION, but so far am going round in circles
> trying to figure out why and what to do about it.
> Michael MacGregor
> Database Architect
>
That would seem to be telling you that this database is involved in
replication, which would explain why the log won't truncate.
Transactions can't be flushed from the log if they haven't been
replicated yet.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I gathered that from the info in BOL, but I haven't figured out how to flush
that, or stop the replication.
Michael MacGregor
Database Architect|||Fixed it! There was a replication transaction still open.
Michael MacGregor
Database Architect
First off we have a problem with the fact that the log keeps growing way too
much, something that I have to look into, but besides that, I've tried to
shrink it down as it will end up eating up all the available disk space,
eventually.
I heard that DBCC SHRINKFILE will create dummy transactions in order to get
the log file to recycle, so that it will shrink, but it doesn't seem to want
to do that.
I've tried performing BACKUP LOG with various options and then trying to
shrink the log file, all to no avail.
Any hints or suggestions welcome as to how I can force this overgrown log
file back down to a more reasonable size.
Michael MacGregor
Database ArchitectAre you using the syntax:
DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>From BOL:
TRUNCATEONLY
Causes any unused space in the files to be released to the operating
system and shrinks the file to the last allocated extent, reducing the
file size without moving any data. No attempt is made to relocate rows
to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
WITH
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Michael MacGregor wrote:
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way too
> much, something that I have to look into, but besides that, I've tried to
> shrink it down as it will end up eating up all the available disk space,
> eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to get
> the log file to recycle, so that it will shrink, but it doesn't seem to want
> to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect|||Michael,
Is your log unrestricted? If it is then give it a maximum otherwise (under
SQL2005 and maybe SQL2000) it will keep on growing.
Chris
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uNI12wd$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> We're having a problem with one log file which refuses to be shrunk.
> First off we have a problem with the fact that the log keeps growing way
> too much, something that I have to look into, but besides that, I've tried
> to shrink it down as it will end up eating up all the available disk
> space, eventually.
> I heard that DBCC SHRINKFILE will create dummy transactions in order to
> get the log file to recycle, so that it will shrink, but it doesn't seem
> to want to do that.
> I've tried performing BACKUP LOG with various options and then trying to
> shrink the log file, all to no avail.
> Any hints or suggestions welcome as to how I can force this overgrown log
> file back down to a more reasonable size.
> Michael MacGregor
> Database Architect
>|||Yes.
<tootsuite@.gmail.com> wrote in message
news:1162403350.700370.279630@.e64g2000cwd.googlegroups.com...
> Are you using the syntax:
> DBCC SHRINKFILE (fileName, TRUNCATEONLY)
>>From BOL:
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
> system and shrinks the file to the last allocated extent, reducing the
> file size without moving any data. No attempt is made to relocate rows
> to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
> WITH
> DBCC SHRINKFILE
> (
> { 'file_name' | file_id }
> { [ , EMPTYFILE ]
> | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
> }
> )
> [ WITH NO_INFOMSGS ]
>
> Michael MacGregor wrote:
>> We're having a problem with one log file which refuses to be shrunk.
>> First off we have a problem with the fact that the log keeps growing way
>> too
>> much, something that I have to look into, but besides that, I've tried to
>> shrink it down as it will end up eating up all the available disk space,
>> eventually.
>> I heard that DBCC SHRINKFILE will create dummy transactions in order to
>> get
>> the log file to recycle, so that it will shrink, but it doesn't seem to
>> want
>> to do that.
>> I've tried performing BACKUP LOG with various options and then trying to
>> shrink the log file, all to no avail.
>> Any hints or suggestions welcome as to how I can force this overgrown log
>> file back down to a more reasonable size.
>> Michael MacGregor
>> Database Architect
>|||I'm actually not bothered by that, what is concerning me right now is that I
can't get it to shrink, the continual growth issue is not a concern right
now, as I believe I know what is causing that anyway and I have to deal with
that rather than restrict the log growth.
I have queries sys.databases and it is telling me that the
log_reuse_wait_desc is REPLICATION, but so far am going round in circles
trying to figure out why and what to do about it.
Michael MacGregor
Database Architect|||Michael MacGregor wrote:
> I'm actually not bothered by that, what is concerning me right now is that I
> can't get it to shrink, the continual growth issue is not a concern right
> now, as I believe I know what is causing that anyway and I have to deal with
> that rather than restrict the log growth.
> I have queries sys.databases and it is telling me that the
> log_reuse_wait_desc is REPLICATION, but so far am going round in circles
> trying to figure out why and what to do about it.
> Michael MacGregor
> Database Architect
>
That would seem to be telling you that this database is involved in
replication, which would explain why the log won't truncate.
Transactions can't be flushed from the log if they haven't been
replicated yet.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I gathered that from the info in BOL, but I haven't figured out how to flush
that, or stop the replication.
Michael MacGregor
Database Architect|||Fixed it! There was a replication transaction still open.
Michael MacGregor
Database Architect
Friday, March 23, 2012
Log File Size reduction
Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
>
and|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
>
and|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>
Log File Size reduction
Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
> > Dear All,
> >
> > Our database log file in SQL 2000 is growing and filling the disk.
> >
> > What are the option to reduce the log file size which is existing?
> >
> > We tried the options :
> >
> > DBCC Shrinkfile
> > SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> >
> > However the file size remained same.
> >
> > Are we making any mistake? Which is the best way to reduce the file size
and
> > keep the size to a preset limit (without affecting the trasaction after
> > reaching the limit)
> >
> > Regards
> >
> > Sathian
> >
> >
> >|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
SathianHi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
> Tomasz B.
> "Sathian" wrote:
> > Dear All,
> >
> > Our database log file in SQL 2000 is growing and filling the disk.
> >
> > What are the option to reduce the log file size which is existing?
> >
> > We tried the options :
> >
> > DBCC Shrinkfile
> > SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> >
> > However the file size remained same.
> >
> > Are we making any mistake? Which is the best way to reduce the file size
and
> > keep the size to a preset limit (without affecting the trasaction after
> > reaching the limit)
> >
> > Regards
> >
> > Sathian
> >
> >
> >|||If this is production server, and recovery mode=full, I will
1. backup transaction_log dbname to disk='filename'
2. dbcc shrinkfile logical_db name
3. schedule to backup transaction_log depand on needed + recovery purpose
--soonyu
"Sathian" wrote:
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>
Log File Size grows enormously
Hi,
I have a database with data file size 26 MB and log file
just growing. The log file size is 1.2GB. It increases by
110MB in half a day. I am taking transaction log backups
every half an hour. But still the log file size keep
growing. This is happening for the past 20 days. DBCC
OPENTRAN command gives the following:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
My SQL VERSION is: 8.00.818 (Intel X86) running in Win 2K
with SP4
What action should I take to control the log file size?
The below link gives a patch for SQL 7.0.
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;240039
But what to do with SQL 2K with SP3a?Here's some additional links that may be of use :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
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=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
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
HTH
Ryan Waight, MCDBA, MCSE
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:081101c3ae85$1584e460$a501280a@.phx.gbl...
> Hi,
> I have a database with data file size 26 MB and log file
> just growing. The log file size is 1.2GB. It increases by
> 110MB in half a day. I am taking transaction log backups
> every half an hour. But still the log file size keep
> growing. This is happening for the past 20 days. DBCC
> OPENTRAN command gives the following:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> My SQL VERSION is: 8.00.818 (Intel X86) running in Win 2K
> with SP4
> What action should I take to control the log file size?
> The below link gives a patch for SQL 7.0.
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;240039
> But what to do with SQL 2K with SP3a?
>|||I have implemented all the recommendations mentioned in
the links. I am taking regular log backup (every 30
mins). But still the log file size is 1110MB even when no
users are connected.
While using dbcc shrinkfile, i am getting the following
msg:
Cannot shrink log file 2 (Gsap_Log) because all logical
log files are in use.
DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
Execution of the SP sp_repldone gives "The database is
not published."
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Can anyone help me in truncating my log file?
Regards,
Mangai
>--Original Message--
>Here's some additional links that may be of use :-
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Tran Log
>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=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>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
>
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Mangai" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081101c3ae85$1584e460$a501280a@.phx.gbl...
>> Hi,
>> I have a database with data file size 26 MB and log
file
>> just growing. The log file size is 1.2GB. It increases
by
>> 110MB in half a day. I am taking transaction log
backups
>> every half an hour. But still the log file size keep
>> growing. This is happening for the past 20 days. DBCC
>> OPENTRAN command gives the following:
>> Replicated Transaction Information:
>> Oldest distributed LSN : (1134:527:1)
>> Oldest non-distributed LSN : (1134:1041:4)
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> My SQL VERSION is: 8.00.818 (Intel X86) running in Win
2K
>> with SP4
>> What action should I take to control the log file size?
>> The below link gives a patch for SQL 7.0.
>> http://support.microsoft.com/default.aspx?scid=kb;EN-
>> US;240039
>> But what to do with SQL 2K with SP3a?
>>
>
>.
>|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:090a01c3af1b$40eeb770$a301280a@.phx.gbl...
> I have implemented all the recommendations mentioned in
> the links. I am taking regular log backup (every 30
> mins). But still the log file size is 1110MB even when no
> users are connected.
> While using dbcc shrinkfile, i am getting the following
> msg:
> Cannot shrink log file 2 (Gsap_Log) because all logical
> log files are in use.
> DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> Execution of the SP sp_repldone gives "The database is
> not published."
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
>
> Can anyone help me in truncating my log file?
> Regards,
> Mangai
>
> >--Original Message--
> >Here's some additional links that may be of use :-
> >
> >http://www.support.microsoft.com/?id=256650 INF: How
> to Shrink the SQL
> >Server 7.0 Tran Log
> >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=272318 INF:
> Shrinking Log in SQL
> >Server 2000 with DBCC SHRINKFILE
> >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
> >
> >
> >
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Mangai" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:081101c3ae85$1584e460$a501280a@.phx.gbl...
> >> Hi,
> >> I have a database with data file size 26 MB and log
> file
> >> just growing. The log file size is 1.2GB. It increases
> by
> >> 110MB in half a day. I am taking transaction log
> backups
> >> every half an hour. But still the log file size keep
> >> growing. This is happening for the past 20 days. DBCC
> >> OPENTRAN command gives the following:
> >>
> >> Replicated Transaction Information:
> >> Oldest distributed LSN : (1134:527:1)
> >> Oldest non-distributed LSN : (1134:1041:4)
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> My SQL VERSION is: 8.00.818 (Intel X86) running in Win
> 2K
> >> with SP4
> >>
> >> What action should I take to control the log file size?
> >> The below link gives a patch for SQL 7.0.
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;EN-
> >> US;240039
> >>
> >> But what to do with SQL 2K with SP3a?
> >>
> >>
> >
> >
> >.
> >
I have a database with data file size 26 MB and log file
just growing. The log file size is 1.2GB. It increases by
110MB in half a day. I am taking transaction log backups
every half an hour. But still the log file size keep
growing. This is happening for the past 20 days. DBCC
OPENTRAN command gives the following:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
My SQL VERSION is: 8.00.818 (Intel X86) running in Win 2K
with SP4
What action should I take to control the log file size?
The below link gives a patch for SQL 7.0.
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;240039
But what to do with SQL 2K with SP3a?Here's some additional links that may be of use :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
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=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
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
HTH
Ryan Waight, MCDBA, MCSE
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:081101c3ae85$1584e460$a501280a@.phx.gbl...
> Hi,
> I have a database with data file size 26 MB and log file
> just growing. The log file size is 1.2GB. It increases by
> 110MB in half a day. I am taking transaction log backups
> every half an hour. But still the log file size keep
> growing. This is happening for the past 20 days. DBCC
> OPENTRAN command gives the following:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> My SQL VERSION is: 8.00.818 (Intel X86) running in Win 2K
> with SP4
> What action should I take to control the log file size?
> The below link gives a patch for SQL 7.0.
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;240039
> But what to do with SQL 2K with SP3a?
>|||I have implemented all the recommendations mentioned in
the links. I am taking regular log backup (every 30
mins). But still the log file size is 1110MB even when no
users are connected.
While using dbcc shrinkfile, i am getting the following
msg:
Cannot shrink log file 2 (Gsap_Log) because all logical
log files are in use.
DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
Execution of the SP sp_repldone gives "The database is
not published."
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Can anyone help me in truncating my log file?
Regards,
Mangai
>--Original Message--
>Here's some additional links that may be of use :-
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Tran Log
>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=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>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
>
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Mangai" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081101c3ae85$1584e460$a501280a@.phx.gbl...
>> Hi,
>> I have a database with data file size 26 MB and log
file
>> just growing. The log file size is 1.2GB. It increases
by
>> 110MB in half a day. I am taking transaction log
backups
>> every half an hour. But still the log file size keep
>> growing. This is happening for the past 20 days. DBCC
>> OPENTRAN command gives the following:
>> Replicated Transaction Information:
>> Oldest distributed LSN : (1134:527:1)
>> Oldest non-distributed LSN : (1134:1041:4)
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> My SQL VERSION is: 8.00.818 (Intel X86) running in Win
2K
>> with SP4
>> What action should I take to control the log file size?
>> The below link gives a patch for SQL 7.0.
>> http://support.microsoft.com/default.aspx?scid=kb;EN-
>> US;240039
>> But what to do with SQL 2K with SP3a?
>>
>
>.
>|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:090a01c3af1b$40eeb770$a301280a@.phx.gbl...
> I have implemented all the recommendations mentioned in
> the links. I am taking regular log backup (every 30
> mins). But still the log file size is 1110MB even when no
> users are connected.
> While using dbcc shrinkfile, i am getting the following
> msg:
> Cannot shrink log file 2 (Gsap_Log) because all logical
> log files are in use.
> DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> Execution of the SP sp_repldone gives "The database is
> not published."
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
>
> Can anyone help me in truncating my log file?
> Regards,
> Mangai
>
> >--Original Message--
> >Here's some additional links that may be of use :-
> >
> >http://www.support.microsoft.com/?id=256650 INF: How
> to Shrink the SQL
> >Server 7.0 Tran Log
> >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=272318 INF:
> Shrinking Log in SQL
> >Server 2000 with DBCC SHRINKFILE
> >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
> >
> >
> >
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Mangai" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:081101c3ae85$1584e460$a501280a@.phx.gbl...
> >> Hi,
> >> I have a database with data file size 26 MB and log
> file
> >> just growing. The log file size is 1.2GB. It increases
> by
> >> 110MB in half a day. I am taking transaction log
> backups
> >> every half an hour. But still the log file size keep
> >> growing. This is happening for the past 20 days. DBCC
> >> OPENTRAN command gives the following:
> >>
> >> Replicated Transaction Information:
> >> Oldest distributed LSN : (1134:527:1)
> >> Oldest non-distributed LSN : (1134:1041:4)
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> My SQL VERSION is: 8.00.818 (Intel X86) running in Win
> 2K
> >> with SP4
> >>
> >> What action should I take to control the log file size?
> >> The below link gives a patch for SQL 7.0.
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;EN-
> >> US;240039
> >>
> >> But what to do with SQL 2K with SP3a?
> >>
> >>
> >
> >
> >.
> >
Log file size growing rapidly.. increased by 1 GB every week
Hi,
My sql server database log file size growing rapidly, 1 GB by every week. I
have a service running every 2 hours which deletes and inserts some data,
say around 5000 records for every two hours.
I know the source of the problem is deleting and inserting records for every
2 hours. I want to go for a densive mechansim for this, that is: i don't
want to change the service code (for example using TRUNCATE statement
instead of DELETE statement) for any reason.
So I am thinking to write sql job whose job is:
Backup transaction log and shrink log file.
This job runs every day at 4:00 AM.
I alreday have a full backup job which runs at 3:00 AM daily. Probably I
will add the above commnads (backup transaction log and then shrink log
file) to the full backup job schedule as another step.
Does any one experainced this? is it write thing to shrink file every day?
Thanks,
VenkatVenkat
What is recovery model of your db? Its possible it has FULL recovery mode.
If so, please start performing BACKUP LOG to move an inactive transactions
to be moved on the disk. I'd suggest you read about recovery models in the
BOL to understand their impact on the database...
"Venkat" <venkatarao.v@.gmail.com> wrote in message
news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> Hi,
> My sql server database log file size growing rapidly, 1 GB by every week.
> I have a service running every 2 hours which deletes and inserts some
> data, say around 5000 records for every two hours.
> I know the source of the problem is deleting and inserting records for
> every 2 hours. I want to go for a densive mechansim for this, that is: i
> don't want to change the service code (for example using TRUNCATE
> statement instead of DELETE statement) for any reason.
> So I am thinking to write sql job whose job is:
> Backup transaction log and shrink log file.
> This job runs every day at 4:00 AM.
> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> will add the above commnads (backup transaction log and then shrink log
> file) to the full backup job schedule as another step.
> Does any one experainced this? is it write thing to shrink file every day?
> Thanks,
> Venkat
>|||... and to understand the implications of doing a regular shrink, check out:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive transactions
> to be moved on the disk. I'd suggest you read about recovery models in the
> BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every day?
>> Thanks,
>> Venkat
>|||Example with "Leaking roof and bucket" is great, easy to understand :-)
--
Regards,
anxcomp|||Thanks :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
> Example with "Leaking roof and bucket" is great, easy to understand :-)
> --
> Regards,
> anxcomp|||Hi Uri Dimant, Thsnaks for the reply.
Yes the recovery model is FULL
As I mentioned in my post, i have maintennace plan, which will backup the
database every day at 3:00 AM
So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
log file) then this will address the log file size issue?
Or
I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
LOG?
Thanks,
Venkat
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive
> transactions to be moved on the disk. I'd suggest you read about recovery
> models in the BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every
>> day?
>> Thanks,
>> Venkat
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Tibor,
Thanks for the reply. Yes I read your post before posting the request here.
Your explanation is good on this topic. But I have one question:
Even if you allocate enough space to log file, say 20 GB to log file, after
some time some one has to go and clean it up before it overshoots the
allocated size.
So is the reason for the automated job to freeup the log file space.
From customers angle: Instead of realying upon DBA or some one to moitor
this log file and clean it up, they would like the system to take care this
automatically. So this is the reason why people want to go for regular
shrink of the log file to freeup the space
Please reply with your comments.
Thanks,
Venkat
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
> Thanks :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Ben Nevarez,
So backup database command does not automatically backup log file?
What is the difference between shrinking log file manually and automatically
(when I say automatically, I might shrink log file once in a Week by
scheduling a monthly job)
Thanks,
venkat
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> Venkat,
> Just schedule the transaction log backup job. Do not schedule any
> shrinkfile
> operation.
> Once you have performed a transaction log backup you can do a shrink file
> operation manually, but only once.
> Hope this helps,
> Ben Nevarez
>
>
> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
>> BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery
>> > mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about
>> > recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every
>> >> week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is:
>> >> i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
>> >> I
>> >> will add the above commnads (backup transaction log and then shrink
>> >> log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>|||If you have a Full Backup that does not backup the transaction log. You need
to specify a Transaction Log Backup.
What I meant by manually is that you will need to shrink the file only once.
Do not schedule any job.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically
> (when I say automatically, I might shrink log file once in a Week by
> scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> >
> > Venkat,
> >
> > Just schedule the transaction log backup job. Do not schedule any
> > shrinkfile
> > operation.
> >
> > Once you have performed a transaction log backup you can do a shrink file
> > operation manually, but only once.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Venkat" wrote:
> >
> >> Hi Uri Dimant, Thsnaks for the reply.
> >>
> >> Yes the recovery model is FULL
> >>
> >> As I mentioned in my post, i have maintennace plan, which will backup the
> >> database every day at 3:00 AM
> >> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> >> log file) then this will address the log file size issue?
> >>
> >> Or
> >> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
> >> BACKUP
> >> LOG?
> >>
> >> Thanks,
> >> Venkat
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> >> > Venkat
> >> > What is recovery model of your db? Its possible it has FULL recovery
> >> > mode.
> >> > If so, please start performing BACKUP LOG to move an inactive
> >> > transactions to be moved on the disk. I'd suggest you read about
> >> > recovery
> >> > models in the BOL to understand their impact on the database...
> >> >
> >> >
> >> >
> >> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> >> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> >> Hi,
> >> >> My sql server database log file size growing rapidly, 1 GB by every
> >> >> week.
> >> >> I have a service running every 2 hours which deletes and inserts some
> >> >> data, say around 5000 records for every two hours.
> >> >>
> >> >> I know the source of the problem is deleting and inserting records for
> >> >> every 2 hours. I want to go for a densive mechansim for this, that is:
> >> >> i
> >> >> don't want to change the service code (for example using TRUNCATE
> >> >> statement instead of DELETE statement) for any reason.
> >> >>
> >> >> So I am thinking to write sql job whose job is:
> >> >> Backup transaction log and shrink log file.
> >> >>
> >> >> This job runs every day at 4:00 AM.
> >> >>
> >> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
> >> >> I
> >> >> will add the above commnads (backup transaction log and then shrink
> >> >> log
> >> >> file) to the full backup job schedule as another step.
> >> >>
> >> >> Does any one experainced this? is it write thing to shrink file every
> >> >> day?
> >> >>
> >> >> Thanks,
> >> >> Venkat
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
Yes, this is what the BACKUP LOG command does, it empties the log. And, of course, this is something
we schedule.
If you don't want to do backup log, then set the database in simple recovery model. Then SQL Server
will truncate/empty the log every time a checkpoint occurs (something that happens pretty regularly
in the database).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:uIutBC8ZIHA.4160@.TK2MSFTNGP03.phx.gbl...
> Hi Tibor,
> Thanks for the reply. Yes I read your post before posting the request here. Your explanation is
> good on this topic. But I have one question:
> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
> So is the reason for the automated job to freeup the log file space.
> From customers angle: Instead of realying upon DBA or some one to moitor this log file and clean
> it up, they would like the system to take care this automatically. So this is the reason why
> people want to go for regular shrink of the log file to freeup the space
> Please reply with your comments.
> Thanks,
> Venkat
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
>> Thanks :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||> So backup database command does not automatically backup log file?
Backup database do include some log records. That that isn't the important question. The important
question is whether backup database empties the log. And the answer is: "It doesn't".
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
That is what I tried to explain in my "leaking roof" analogy. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:e4g41F8ZIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
>> Venkat,
>> Just schedule the transaction log backup job. Do not schedule any shrinkfile
>> operation.
>> Once you have performed a transaction log backup you can do a shrink file
>> operation manually, but only once.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> >> will add the above commnads (backup transaction log and then shrink log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>
>
My sql server database log file size growing rapidly, 1 GB by every week. I
have a service running every 2 hours which deletes and inserts some data,
say around 5000 records for every two hours.
I know the source of the problem is deleting and inserting records for every
2 hours. I want to go for a densive mechansim for this, that is: i don't
want to change the service code (for example using TRUNCATE statement
instead of DELETE statement) for any reason.
So I am thinking to write sql job whose job is:
Backup transaction log and shrink log file.
This job runs every day at 4:00 AM.
I alreday have a full backup job which runs at 3:00 AM daily. Probably I
will add the above commnads (backup transaction log and then shrink log
file) to the full backup job schedule as another step.
Does any one experainced this? is it write thing to shrink file every day?
Thanks,
VenkatVenkat
What is recovery model of your db? Its possible it has FULL recovery mode.
If so, please start performing BACKUP LOG to move an inactive transactions
to be moved on the disk. I'd suggest you read about recovery models in the
BOL to understand their impact on the database...
"Venkat" <venkatarao.v@.gmail.com> wrote in message
news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> Hi,
> My sql server database log file size growing rapidly, 1 GB by every week.
> I have a service running every 2 hours which deletes and inserts some
> data, say around 5000 records for every two hours.
> I know the source of the problem is deleting and inserting records for
> every 2 hours. I want to go for a densive mechansim for this, that is: i
> don't want to change the service code (for example using TRUNCATE
> statement instead of DELETE statement) for any reason.
> So I am thinking to write sql job whose job is:
> Backup transaction log and shrink log file.
> This job runs every day at 4:00 AM.
> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> will add the above commnads (backup transaction log and then shrink log
> file) to the full backup job schedule as another step.
> Does any one experainced this? is it write thing to shrink file every day?
> Thanks,
> Venkat
>|||... and to understand the implications of doing a regular shrink, check out:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive transactions
> to be moved on the disk. I'd suggest you read about recovery models in the
> BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every day?
>> Thanks,
>> Venkat
>|||Example with "Leaking roof and bucket" is great, easy to understand :-)
--
Regards,
anxcomp|||Thanks :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
> Example with "Leaking roof and bucket" is great, easy to understand :-)
> --
> Regards,
> anxcomp|||Hi Uri Dimant, Thsnaks for the reply.
Yes the recovery model is FULL
As I mentioned in my post, i have maintennace plan, which will backup the
database every day at 3:00 AM
So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
log file) then this will address the log file size issue?
Or
I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
LOG?
Thanks,
Venkat
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> Venkat
> What is recovery model of your db? Its possible it has FULL recovery mode.
> If so, please start performing BACKUP LOG to move an inactive
> transactions to be moved on the disk. I'd suggest you read about recovery
> models in the BOL to understand their impact on the database...
>
> "Venkat" <venkatarao.v@.gmail.com> wrote in message
> news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> My sql server database log file size growing rapidly, 1 GB by every week.
>> I have a service running every 2 hours which deletes and inserts some
>> data, say around 5000 records for every two hours.
>> I know the source of the problem is deleting and inserting records for
>> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> don't want to change the service code (for example using TRUNCATE
>> statement instead of DELETE statement) for any reason.
>> So I am thinking to write sql job whose job is:
>> Backup transaction log and shrink log file.
>> This job runs every day at 4:00 AM.
>> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> will add the above commnads (backup transaction log and then shrink log
>> file) to the full backup job schedule as another step.
>> Does any one experainced this? is it write thing to shrink file every
>> day?
>> Thanks,
>> Venkat
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Tibor,
Thanks for the reply. Yes I read your post before posting the request here.
Your explanation is good on this topic. But I have one question:
Even if you allocate enough space to log file, say 20 GB to log file, after
some time some one has to go and clean it up before it overshoots the
allocated size.
So is the reason for the automated job to freeup the log file space.
From customers angle: Instead of realying upon DBA or some one to moitor
this log file and clean it up, they would like the system to take care this
automatically. So this is the reason why people want to go for regular
shrink of the log file to freeup the space
Please reply with your comments.
Thanks,
Venkat
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
> Thanks :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||Venkat,
Just schedule the transaction log backup job. Do not schedule any shrinkfile
operation.
Once you have performed a transaction log backup you can do a shrink file
operation manually, but only once.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Uri Dimant, Thsnaks for the reply.
> Yes the recovery model is FULL
> As I mentioned in my post, i have maintennace plan, which will backup the
> database every day at 3:00 AM
> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> log file) then this will address the log file size issue?
> Or
> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
> LOG?
> Thanks,
> Venkat
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> > Venkat
> > What is recovery model of your db? Its possible it has FULL recovery mode.
> > If so, please start performing BACKUP LOG to move an inactive
> > transactions to be moved on the disk. I'd suggest you read about recovery
> > models in the BOL to understand their impact on the database...
> >
> >
> >
> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> Hi,
> >> My sql server database log file size growing rapidly, 1 GB by every week.
> >> I have a service running every 2 hours which deletes and inserts some
> >> data, say around 5000 records for every two hours.
> >>
> >> I know the source of the problem is deleting and inserting records for
> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
> >> don't want to change the service code (for example using TRUNCATE
> >> statement instead of DELETE statement) for any reason.
> >>
> >> So I am thinking to write sql job whose job is:
> >> Backup transaction log and shrink log file.
> >>
> >> This job runs every day at 4:00 AM.
> >>
> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
> >> will add the above commnads (backup transaction log and then shrink log
> >> file) to the full backup job schedule as another step.
> >>
> >> Does any one experainced this? is it write thing to shrink file every
> >> day?
> >>
> >> Thanks,
> >> Venkat
> >>
> >
> >
>
>|||Hi Ben Nevarez,
So backup database command does not automatically backup log file?
What is the difference between shrinking log file manually and automatically
(when I say automatically, I might shrink log file once in a Week by
scheduling a monthly job)
Thanks,
venkat
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> Venkat,
> Just schedule the transaction log backup job. Do not schedule any
> shrinkfile
> operation.
> Once you have performed a transaction log backup you can do a shrink file
> operation manually, but only once.
> Hope this helps,
> Ben Nevarez
>
>
> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
>> BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery
>> > mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about
>> > recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every
>> >> week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is:
>> >> i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
>> >> I
>> >> will add the above commnads (backup transaction log and then shrink
>> >> log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>|||If you have a Full Backup that does not backup the transaction log. You need
to specify a Transaction Log Backup.
What I meant by manually is that you will need to shrink the file only once.
Do not schedule any job.
Hope this helps,
Ben Nevarez
"Venkat" wrote:
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically
> (when I say automatically, I might shrink log file once in a Week by
> scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
> >
> > Venkat,
> >
> > Just schedule the transaction log backup job. Do not schedule any
> > shrinkfile
> > operation.
> >
> > Once you have performed a transaction log backup you can do a shrink file
> > operation manually, but only once.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "Venkat" wrote:
> >
> >> Hi Uri Dimant, Thsnaks for the reply.
> >>
> >> Yes the recovery model is FULL
> >>
> >> As I mentioned in my post, i have maintennace plan, which will backup the
> >> database every day at 3:00 AM
> >> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
> >> log file) then this will address the log file size issue?
> >>
> >> Or
> >> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the
> >> BACKUP
> >> LOG?
> >>
> >> Thanks,
> >> Venkat
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
> >> > Venkat
> >> > What is recovery model of your db? Its possible it has FULL recovery
> >> > mode.
> >> > If so, please start performing BACKUP LOG to move an inactive
> >> > transactions to be moved on the disk. I'd suggest you read about
> >> > recovery
> >> > models in the BOL to understand their impact on the database...
> >> >
> >> >
> >> >
> >> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
> >> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
> >> >> Hi,
> >> >> My sql server database log file size growing rapidly, 1 GB by every
> >> >> week.
> >> >> I have a service running every 2 hours which deletes and inserts some
> >> >> data, say around 5000 records for every two hours.
> >> >>
> >> >> I know the source of the problem is deleting and inserting records for
> >> >> every 2 hours. I want to go for a densive mechansim for this, that is:
> >> >> i
> >> >> don't want to change the service code (for example using TRUNCATE
> >> >> statement instead of DELETE statement) for any reason.
> >> >>
> >> >> So I am thinking to write sql job whose job is:
> >> >> Backup transaction log and shrink log file.
> >> >>
> >> >> This job runs every day at 4:00 AM.
> >> >>
> >> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably
> >> >> I
> >> >> will add the above commnads (backup transaction log and then shrink
> >> >> log
> >> >> file) to the full backup job schedule as another step.
> >> >>
> >> >> Does any one experainced this? is it write thing to shrink file every
> >> >> day?
> >> >>
> >> >> Thanks,
> >> >> Venkat
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
Yes, this is what the BACKUP LOG command does, it empties the log. And, of course, this is something
we schedule.
If you don't want to do backup log, then set the database in simple recovery model. Then SQL Server
will truncate/empty the log every time a checkpoint occurs (something that happens pretty regularly
in the database).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:uIutBC8ZIHA.4160@.TK2MSFTNGP03.phx.gbl...
> Hi Tibor,
> Thanks for the reply. Yes I read your post before posting the request here. Your explanation is
> good on this topic. But I have one question:
> Even if you allocate enough space to log file, say 20 GB to log file, after some time some one has
> to go and clean it up before it overshoots the allocated size.
> So is the reason for the automated job to freeup the log file space.
> From customers angle: Instead of realying upon DBA or some one to moitor this log file and clean
> it up, they would like the system to take care this automatically. So this is the reason why
> people want to go for regular shrink of the log file to freeup the space
> Please reply with your comments.
> Thanks,
> Venkat
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:5281B2E6-A3DC-43E6-BF11-EAF3F24BD2DC@.microsoft.com...
>> Thanks :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:1663555F-8A8E-4772-9C74-D4DA6F5FB6A6@.microsoft.com...
>> Example with "Leaking roof and bucket" is great, easy to understand :-)
>> --
>> Regards,
>> anxcomp
>|||> So backup database command does not automatically backup log file?
Backup database do include some log records. That that isn't the important question. The important
question is whether backup database empties the log. And the answer is: "It doesn't".
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
That is what I tried to explain in my "leaking roof" analogy. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Venkat" <venkatarao.v@.gmail.com> wrote in message news:e4g41F8ZIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi Ben Nevarez,
> So backup database command does not automatically backup log file?
> What is the difference between shrinking log file manually and automatically (when I say
> automatically, I might shrink log file once in a Week by scheduling a monthly job)
> Thanks,
> venkat
> "Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
> news:8A0140FD-A4D4-47F1-AA53-329A6F57BE3A@.microsoft.com...
>> Venkat,
>> Just schedule the transaction log backup job. Do not schedule any shrinkfile
>> operation.
>> Once you have performed a transaction log backup you can do a shrink file
>> operation manually, but only once.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Venkat" wrote:
>> Hi Uri Dimant, Thsnaks for the reply.
>> Yes the recovery model is FULL
>> As I mentioned in my post, i have maintennace plan, which will backup the
>> database every day at 3:00 AM
>> So now if I include the BACKUP LOG in maintenance plan (and NO Shrinking
>> log file) then this will address the log file size issue?
>> Or
>> I should include DBCC SHRINKFILE(N'MyDb_log' , 0) also along with the BACKUP
>> LOG?
>> Thanks,
>> Venkat
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eVZW1IkZIHA.4332@.TK2MSFTNGP04.phx.gbl...
>> > Venkat
>> > What is recovery model of your db? Its possible it has FULL recovery mode.
>> > If so, please start performing BACKUP LOG to move an inactive
>> > transactions to be moved on the disk. I'd suggest you read about recovery
>> > models in the BOL to understand their impact on the database...
>> >
>> >
>> >
>> > "Venkat" <venkatarao.v@.gmail.com> wrote in message
>> > news:uWnh13jZIHA.4712@.TK2MSFTNGP05.phx.gbl...
>> >> Hi,
>> >> My sql server database log file size growing rapidly, 1 GB by every week.
>> >> I have a service running every 2 hours which deletes and inserts some
>> >> data, say around 5000 records for every two hours.
>> >>
>> >> I know the source of the problem is deleting and inserting records for
>> >> every 2 hours. I want to go for a densive mechansim for this, that is: i
>> >> don't want to change the service code (for example using TRUNCATE
>> >> statement instead of DELETE statement) for any reason.
>> >>
>> >> So I am thinking to write sql job whose job is:
>> >> Backup transaction log and shrink log file.
>> >>
>> >> This job runs every day at 4:00 AM.
>> >>
>> >> I alreday have a full backup job which runs at 3:00 AM daily. Probably I
>> >> will add the above commnads (backup transaction log and then shrink log
>> >> file) to the full backup job schedule as another step.
>> >>
>> >> Does any one experainced this? is it write thing to shrink file every
>> >> day?
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >
>> >
>>
>
Subscribe to:
Posts (Atom)