We are trying to manage our transaction log files a little bit
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
It's designed to do transaction log backups at five minute intervals, or
when the log reaches 70% full. You could modify it to do longer
intervals, or just use it as-is.
To answer your question, the alert will fire, and a backup will run, but
that reindexing activity will likely be inside an uncommitted
transaction, and the backup won't be able to flush it out of the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> > We are trying to manage our transaction log files a little bit
> > differently as we found out in many cases our tran log files have grown
> > way too big. The db's are set to full recovery mode and we do take
> > tran log backups daily at night but that is not sufficient and because
> > we have the tran log to grow with no limit it is getting really big.
> >
> > So what I am planning to do is set a maximum size for the tran log and
> > then also set up an alert to back up the log when it is 70% full.
> > However the one thing that I am not clear about is how SQL Server
> > handles the log, for example let's say if I set the max size of tran
> > log to 200MB and let's say that there is a transaction or a process
> > that rebuilds indexes that would take more than 200MB to complete the
> > entire taks would SQL Server give me an error or will it kick off the
> > alert and do a log back up and continue with the process?
> >
> > In that scenario will I have to set the max size of the tran log larger
> > than the largest transaction size that my application may run?
> >
> > Any help or advice in this regard will be greatly appreciated.
> > Thanks
> >
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
> It's designed to do transaction log backups at five minute intervals, or
> when the log reaches 70% full. You could modify it to do longer
> intervals, or just use it as-is.
> To answer your question, the alert will fire, and a backup will run, but
> that reindexing activity will likely be inside an uncommitted
> transaction, and the backup won't be able to flush it out of the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Showing posts with label logs. Show all posts
Showing posts with label logs. Show all posts
Monday, March 12, 2012
Need advice on managing the size of Tran logs and log files
We are trying to manage our transaction log files a little bit
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
differently as we found out in many cases our tran log files have grown
way too big. The db's are set to full recovery mode and we do take
tran log backups daily at night but that is not sufficient and because
we have the tran log to grow with no limit it is getting really big.
So what I am planning to do is set a maximum size for the tran log and
then also set up an alert to back up the log when it is 70% full.
However the one thing that I am not clear about is how SQL Server
handles the log, for example let's say if I set the max size of tran
log to 200MB and let's say that there is a transaction or a process
that rebuilds indexes that would take more than 200MB to complete the
entire taks would SQL Server give me an error or will it kick off the
alert and do a log back up and continue with the process?
In that scenario will I have to set the max size of the tran log larger
than the largest transaction size that my application may run?
Any help or advice in this regard will be greatly appreciated.
Thanksshub wrote:
> We are trying to manage our transaction log files a little bit
> differently as we found out in many cases our tran log files have grown
> way too big. The db's are set to full recovery mode and we do take
> tran log backups daily at night but that is not sufficient and because
> we have the tran log to grow with no limit it is getting really big.
> So what I am planning to do is set a maximum size for the tran log and
> then also set up an alert to back up the log when it is 70% full.
> However the one thing that I am not clear about is how SQL Server
> handles the log, for example let's say if I set the max size of tran
> log to 200MB and let's say that there is a transaction or a process
> that rebuilds indexes that would take more than 200MB to complete the
> entire taks would SQL Server give me an error or will it kick off the
> alert and do a log back up and continue with the process?
> In that scenario will I have to set the max size of the tran log larger
> than the largest transaction size that my application may run?
> Any help or advice in this regard will be greatly appreciated.
> Thanks
>
Have a look at my backup script:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||Thanks for sharing your code, I will take a look at it and see how I
could implement it.
I just wanted to clarify your response, since the backup won't be able
to flush out the log because of uncomitted transaction will I possibly
get an error of transaction being full unless I set the max size
greater than the size what the rebuild/or any other process would take.
What is the best way to handle such operations without setting the max
size of tran log file to a large number?
I just did few tests and it appears after I do rebuilding of indexes
it seems the transaction log file grows approximately to the size of
the data file, so does that mean that I would need to set the max size
for tran log to be greater than that?
Thanks
Tracy McKibben wrote:
> shub wrote:
> Have a look at my backup script:
> http://realsqlguy.com/twiki/bin/vie...realsqlguy.com|||shub wrote:
> Thanks for sharing your code, I will take a look at it and see how I
> could implement it.
> I just wanted to clarify your response, since the backup won't be able
> to flush out the log because of uncomitted transaction will I possibly
> get an error of transaction being full unless I set the max size
> greater than the size what the rebuild/or any other process would take.
> What is the best way to handle such operations without setting the max
> size of tran log file to a large number?
>
Reindexing generates a lot of transactional activity, and those
transactions tend to be large. Bottom line is, if the transaction log
isn't large enough to hold a transaction, you will receive an error, and
the process generating the transaction will fail.
That said, you have a couple of options:
1. Don't rebuild every index, only rebuild those that are fragmented.
I have another script on my site that will automate this process for
you. Consider running this script once per week, with a fragmentation
threshold of 20-30%.
2. If you're not doing log shipping, you might consider putting the
database into Simple mode before reindexing. This won't reduce the size
of the log, but it will help flush out the committed transactions. If
you're doing frequent t-log backups (5 minute intervals), Simple mode
won't really gain you anything.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Friday, March 9, 2012
Need advice about setting file limit, file grow
Seeking advice here.
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
Tnt
You need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt
|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
[vbcol=seagreen]
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup? If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...[vbcol=seagreen]
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
Tnt
You need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt
|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
[vbcol=seagreen]
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup? If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...[vbcol=seagreen]
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
Need advice about setting file limit, file grow
Seeking advice here.
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduc
e
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one i
n
> Feb and recently Nov). Running the transaction backup doesn't seem to red
uce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers tha
t
> connect to this database (it won't do any good if the IIS is still pointin
g
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
[vbcol=seagreen]
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log wil
l
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
>|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...[vbcol=seagreen]
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduc
e
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one i
n
> Feb and recently Nov). Running the transaction backup doesn't seem to red
uce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers tha
t
> connect to this database (it won't do any good if the IIS is still pointin
g
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
[vbcol=seagreen]
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log wil
l
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
>|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...[vbcol=seagreen]
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>
Need advice about setting file limit, file grow
Seeking advice here.
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
> > Seeking advice here.
> >
> > We have a database that consists of about 440 mb (mdf) and the logs of the
> > database amount to almost 14 GB (ldf).
> >
> >
> > We schedule to run the transaction log every night (I used to GUI when we
> > set up).
> >
> >
> > During the last year, we have seen the logs spike up to 42 GB twice (one in
> > Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> > the size. The only time we could get it to reduce size is running the
> > command below, but the problem is we have to shutdown both IIS servers that
> > connect to this database (it won't do any good if the IIS is still pointing
> > to it).
> >
> >
> > backup log bbnc to disk='E:\bbnc_log3.log'
> >
> > dbcc shrinkfile (Portal_log, 2)
> >
> > We are considering setting a limit for the file size. Right now it is set
> > to "Automatic grow file", by 10 percent. Maximum file growth to
> > "Unrestricted file growth". What are the cons of setting a limit for the
> > size?
> >
> > What is the best setting for us?
> >
> >
> > Thanks in advance.
> >
> > Tnt|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>> You need to contain your run-away transactions. If you run a very large
>> transaction that uses up a lot of transaction log space, your tran log
>> will
>> grow large no matter how often you back up the tran log.
>> Linchi
>> "tnt" wrote:
>> > Seeking advice here.
>> >
>> > We have a database that consists of about 440 mb (mdf) and the logs of
>> > the
>> > database amount to almost 14 GB (ldf).
>> >
>> >
>> > We schedule to run the transaction log every night (I used to GUI when
>> > we
>> > set up).
>> >
>> >
>> > During the last year, we have seen the logs spike up to 42 GB twice
>> > (one in
>> > Feb and recently Nov). Running the transaction backup doesn't seem to
>> > reduce
>> > the size. The only time we could get it to reduce size is running the
>> > command below, but the problem is we have to shutdown both IIS servers
>> > that
>> > connect to this database (it won't do any good if the IIS is still
>> > pointing
>> > to it).
>> >
>> >
>> > backup log bbnc to disk='E:\bbnc_log3.log'
>> >
>> > dbcc shrinkfile (Portal_log, 2)
>> >
>> > We are considering setting a limit for the file size. Right now it is
>> > set
>> > to "Automatic grow file", by 10 percent. Maximum file growth to
>> > "Unrestricted file growth". What are the cons of setting a limit for
>> > the
>> > size?
>> >
>> > What is the best setting for us?
>> >
>> >
>> > Thanks in advance.
>> >
>> > Tnt
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
> > Seeking advice here.
> >
> > We have a database that consists of about 440 mb (mdf) and the logs of the
> > database amount to almost 14 GB (ldf).
> >
> >
> > We schedule to run the transaction log every night (I used to GUI when we
> > set up).
> >
> >
> > During the last year, we have seen the logs spike up to 42 GB twice (one in
> > Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> > the size. The only time we could get it to reduce size is running the
> > command below, but the problem is we have to shutdown both IIS servers that
> > connect to this database (it won't do any good if the IIS is still pointing
> > to it).
> >
> >
> > backup log bbnc to disk='E:\bbnc_log3.log'
> >
> > dbcc shrinkfile (Portal_log, 2)
> >
> > We are considering setting a limit for the file size. Right now it is set
> > to "Automatic grow file", by 10 percent. Maximum file growth to
> > "Unrestricted file growth". What are the cons of setting a limit for the
> > size?
> >
> > What is the best setting for us?
> >
> >
> > Thanks in advance.
> >
> > Tnt|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>> You need to contain your run-away transactions. If you run a very large
>> transaction that uses up a lot of transaction log space, your tran log
>> will
>> grow large no matter how often you back up the tran log.
>> Linchi
>> "tnt" wrote:
>> > Seeking advice here.
>> >
>> > We have a database that consists of about 440 mb (mdf) and the logs of
>> > the
>> > database amount to almost 14 GB (ldf).
>> >
>> >
>> > We schedule to run the transaction log every night (I used to GUI when
>> > we
>> > set up).
>> >
>> >
>> > During the last year, we have seen the logs spike up to 42 GB twice
>> > (one in
>> > Feb and recently Nov). Running the transaction backup doesn't seem to
>> > reduce
>> > the size. The only time we could get it to reduce size is running the
>> > command below, but the problem is we have to shutdown both IIS servers
>> > that
>> > connect to this database (it won't do any good if the IIS is still
>> > pointing
>> > to it).
>> >
>> >
>> > backup log bbnc to disk='E:\bbnc_log3.log'
>> >
>> > dbcc shrinkfile (Portal_log, 2)
>> >
>> > We are considering setting a limit for the file size. Right now it is
>> > set
>> > to "Automatic grow file", by 10 percent. Maximum file growth to
>> > "Unrestricted file growth". What are the cons of setting a limit for
>> > the
>> > size?
>> >
>> > What is the best setting for us?
>> >
>> >
>> > Thanks in advance.
>> >
>> > Tnt
Subscribe to:
Posts (Atom)