Log file Problem

Hi EveryBody...
I am using SQL server 2000 and i was working on Strore procedure and
manupulating tables.Sql Server shows me messages that ur log size is
Increased
and as a result it does not allow me to do any manupulations.so
I de attached the database and rename it and increase its log file.
Now ,I try to re attached the database,but Server show me
messages ,Its
not valid log file.
Then i rename it to its origional name,but the problem remains the
same.
Its not a valid log file.
then i move it to some other place and try to attacah but its not
again.
after that i move this log and mdf files to other Ms SQL server.But
problem
is not solved.
Plz help in this regard.
You can try sp_attach_single_file_db. If that doesn't work, you should restore from your most recent
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Me On Line" <jawadsmail@.gmail.com> wrote in message
news:1178171246.741385.324940@.h2g2000hsg.googlegro ups.com...
> Hi EveryBody...
> I am using SQL server 2000 and i was working on Strore procedure and
> manupulating tables.Sql Server shows me messages that ur log size is
> Increased
> and as a result it does not allow me to do any manupulations.so
> I de attached the database and rename it and increase its log file.
> Now ,I try to re attached the database,but Server show me
> messages ,Its
> not valid log file.
> Then i rename it to its origional name,but the problem remains the
> same.
> Its not a valid log file.
> then i move it to some other place and try to attacah but its not
> again.
> after that i move this log and mdf files to other Ms SQL server.But
> problem
> is not solved.
> Plz help in this regard.
>
|||The next question would be - Why do you mess around with your sql server
logs like that? To me it makes no sense. From your description it sounds
like your logfile was full and it wasn't allowed to grow, but why don't
you just backup (or truncate) the logfile?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Tibor Karaszi wrote:
> You can try sp_attach_single_file_db. If that doesn't work, you should
> restore from your most recent backup.
>

Log File Problem

Hi EveryBody...
I am using SQL server 2000 and i was working on Strore procedure and
manupulating tables.Sql Server shows me messages that ur log size is
Increased
and as a result it does not allow me to do any manupulations.so
I de attached the database and rename it and increase its log file.
Now ,I try to re attached the database,but Server show me
messages ,Its
not valid log file.

Then i rename it to its origional name,but the problem remains the
same.
Its not a valid log file.
then i move it to some other place and try to attacah but its not
again.

after that i move this log and mdf files to other Ms SQL server.But
problem
is not solved.
Plz help in this regard.Meonline (jawadsmail@.gmail.com) writes:

Quote:

Originally Posted by

I am using SQL server 2000 and i was working on Strore procedure and
manupulating tables.Sql Server shows me messages that ur log size is
Increased and as a result it does not allow me to do any
manupulations.so I de attached the database and rename it and increase
its log file. Now ,I try to re attached the database,but Server show me
messages ,Its not valid log file.
>
Then i rename it to its origional name,but the problem remains the
same.
Its not a valid log file.
then i move it to some other place and try to attacah but its not
again.
>
after that i move this log and mdf files to other Ms SQL server.But
problem is not solved.

Looks you got yourself into things you should not have touched. I don't
see what the point would with renaming the database files, if you ran
out of disk space? The normal procedure would be to add more disk, or
add a new log file on a second partition. Or simply investigate whether
it was reasonable that your procedure resulted in such an increase in
log space consumption.

Anwyay, if you detached the databse cleanly, you should be able to
reattach it with sp_attach_single_file_db.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Log file Problem

Hi EveryBody...
I am using SQL server 2000 and i was working on Strore procedure and
manupulating tables.Sql Server shows me messages that ur log size is
Increased
and as a result it does not allow me to do any manupulations.so
I de attached the database and rename it and increase its log file.
Now ,I try to re attached the database,but Server show me
messages ,Its
not valid log file.
Then i rename it to its origional name,but the problem remains the
same.
Its not a valid log file.
then i move it to some other place and try to attacah but its not
again.
after that i move this log and mdf files to other Ms SQL server.But
problem
is not solved.
Plz help in this regard.You can try sp_attach_single_file_db. If that doesn't work, you should restore from your most recent
backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Me On Line" <jawadsmail@.gmail.com> wrote in message
news:1178171246.741385.324940@.h2g2000hsg.googlegroups.com...
> Hi EveryBody...
> I am using SQL server 2000 and i was working on Strore procedure and
> manupulating tables.Sql Server shows me messages that ur log size is
> Increased
> and as a result it does not allow me to do any manupulations.so
> I de attached the database and rename it and increase its log file.
> Now ,I try to re attached the database,but Server show me
> messages ,Its
> not valid log file.
> Then i rename it to its origional name,but the problem remains the
> same.
> Its not a valid log file.
> then i move it to some other place and try to attacah but its not
> again.
> after that i move this log and mdf files to other Ms SQL server.But
> problem
> is not solved.
> Plz help in this regard.
>|||The next question would be - Why do you mess around with your sql server
logs like that? To me it makes no sense. From your description it sounds
like your logfile was full and it wasn't allowed to grow, but why don't
you just backup (or truncate) the logfile?
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Tibor Karaszi wrote:
> You can try sp_attach_single_file_db. If that doesn't work, you should
> restore from your most recent backup.
>

Log file Problem

Hi EveryBody...
I am using SQL server 2000 and i was working on Strore procedure and
manupulating tables.Sql Server shows me messages that ur log size is
Increased
and as a result it does not allow me to do any manupulations.so
I de attached the database and rename it and increase its log file.
Now ,I try to re attached the database,but Server show me
messages ,Its
not valid log file.
Then i rename it to its origional name,but the problem remains the
same.
Its not a valid log file.
then i move it to some other place and try to attacah but its not
again.
after that i move this log and mdf files to other Ms SQL server.But
problem
is not solved.
Plz help in this regard.You can try sp_attach_single_file_db. If that doesn't work, you should resto
re from your most recent
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Me On Line" <jawadsmail@.gmail.com> wrote in message
news:1178171246.741385.324940@.h2g2000hsg.googlegroups.com...
> Hi EveryBody...
> I am using SQL server 2000 and i was working on Strore procedure and
> manupulating tables.Sql Server shows me messages that ur log size is
> Increased
> and as a result it does not allow me to do any manupulations.so
> I de attached the database and rename it and increase its log file.
> Now ,I try to re attached the database,but Server show me
> messages ,Its
> not valid log file.
> Then i rename it to its origional name,but the problem remains the
> same.
> Its not a valid log file.
> then i move it to some other place and try to attacah but its not
> again.
> after that i move this log and mdf files to other Ms SQL server.But
> problem
> is not solved.
> Plz help in this regard.
>|||The next question would be - Why do you mess around with your sql server
logs like that? To me it makes no sense. From your description it sounds
like your logfile was full and it wasn't allowed to grow, but why don't
you just backup (or truncate) the logfile?
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Tibor Karaszi wrote:
> You can try sp_attach_single_file_db. If that doesn't work, you should
> restore from your most recent backup.
>

Log File Management

I am working with a client on a production OLTP database using Full
Recovery model. I normally work in datawarehouse environments with
Simple Recovery turned on. They are performing nightly Full Backups and
hourly log backups Monday through Friday. Sunday night is a normal full
backup followed by a log backup and shrinking the log file. Everyweek
the log grows from 200MB on Monday Morning to 12GB on Friday. I have
already recommended not shrinking the log files on Sunday nights as it
only introduces overhead each day during peak hours while the database
expands the log files it previously shrank. My question is why does
this file continue to grow throughout the week? I would expect that
after each log file backup, the log segments that have been backed up
should become available to be reused. I don't see that they are issuing
a checkpoint, and I'm wondering if that is the issue. Can anyone shed
light on this. The information I have found in BOL and online would
indicate that my understanding is correct and the log files should be
being reused after the backup. I just want to be sure before I go
searching for issues with open transactions, etc... Thanks for any and
all help...
Kevinkevin.karlin@.gmail.com wrote:
> I am working with a client on a production OLTP database using Full
> Recovery model. I normally work in datawarehouse environments with
> Simple Recovery turned on. They are performing nightly Full Backups and
> hourly log backups Monday through Friday. Sunday night is a normal full
> backup followed by a log backup and shrinking the log file. Everyweek
> the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> already recommended not shrinking the log files on Sunday nights as it
> only introduces overhead each day during peak hours while the database
> expands the log files it previously shrank. My question is why does
> this file continue to grow throughout the week? I would expect that
> after each log file backup, the log segments that have been backed up
> should become available to be reused. I don't see that they are issuing
> a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> light on this. The information I have found in BOL and online would
> indicate that my understanding is correct and the log files should be
> being reused after the backup. I just want to be sure before I go
> searching for issues with open transactions, etc... Thanks for any and
> all help...
> Kevin
>
Are they doing index rebuilds or some sort of importing during the week?
The log backups will flush out any committed transactions. Reindexing
(or index defragging) generates a LOT of transactional activity.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> > I am working with a client on a production OLTP database using Full
> > Recovery model. I normally work in datawarehouse environments with
> > Simple Recovery turned on. They are performing nightly Full Backups and
> > hourly log backups Monday through Friday. Sunday night is a normal full
> > backup followed by a log backup and shrinking the log file. Everyweek
> > the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> > already recommended not shrinking the log files on Sunday nights as it
> > only introduces overhead each day during peak hours while the database
> > expands the log files it previously shrank. My question is why does
> > this file continue to grow throughout the week? I would expect that
> > after each log file backup, the log segments that have been backed up
> > should become available to be reused. I don't see that they are issuing
> > a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> > light on this. The information I have found in BOL and online would
> > indicate that my understanding is correct and the log files should be
> > being reused after the backup. I just want to be sure before I go
> > searching for issues with open transactions, etc... Thanks for any and
> > all help...
> >
> > Kevin
> >
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> > I am working with a client on a production OLTP database using Full
> > Recovery model. I normally work in datawarehouse environments with
> > Simple Recovery turned on. They are performing nightly Full Backups and
> > hourly log backups Monday through Friday. Sunday night is a normal full
> > backup followed by a log backup and shrinking the log file. Everyweek
> > the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> > already recommended not shrinking the log files on Sunday nights as it
> > only introduces overhead each day during peak hours while the database
> > expands the log files it previously shrank. My question is why does
> > this file continue to grow throughout the week? I would expect that
> > after each log file backup, the log segments that have been backed up
> > should become available to be reused. I don't see that they are issuing
> > a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> > light on this. The information I have found in BOL and online would
> > indicate that my understanding is correct and the log files should be
> > being reused after the backup. I just want to be sure before I go
> > searching for issues with open transactions, etc... Thanks for any and
> > all help...
> >
> > Kevin
> >
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> > I am working with a client on a production OLTP database using Full
> > Recovery model. I normally work in datawarehouse environments with
> > Simple Recovery turned on. They are performing nightly Full Backups and
> > hourly log backups Monday through Friday. Sunday night is a normal full
> > backup followed by a log backup and shrinking the log file. Everyweek
> > the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> > already recommended not shrinking the log files on Sunday nights as it
> > only introduces overhead each day during peak hours while the database
> > expands the log files it previously shrank. My question is why does
> > this file continue to grow throughout the week? I would expect that
> > after each log file backup, the log segments that have been backed up
> > should become available to be reused. I don't see that they are issuing
> > a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> > light on this. The information I have found in BOL and online would
> > indicate that my understanding is correct and the log files should be
> > being reused after the backup. I just want to be sure before I go
> > searching for issues with open transactions, etc... Thanks for any and
> > all help...
> >
> > Kevin
> >
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||kevin.karlin@.gmail.com wrote:
> The only thing that doesn't appear to be happening that I normally
> implement on my datawarehouse boxes is issuing a checkpoint. I know
> that has to be done when you're in Simple Recovery mode, but I don't
> find reference to it being required when in Full Recovery mode. Could
> that be the problem?
>
Where are you getting the notion that you have to issue checkpoints?
Those should happen automatically in Simple mode. In the other modes,
backing up the transaction log performs the truncation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It seems I wasn't entirely clear in my second post. I normally work in
a DW environment not an OLTP environment. We don't have a need for
anything but Simple Recovery. The box that is having the problem is an
OLTP database using Full Recovery. I know that in Simple mode the
server will issue checkpoints automatically. However, my experience has
been that issuing a checkpoint right before a log truncation ensured
you got the most space released. This goes way back to version 6.5 as I
recall, so it may have become unnecessary at some point, but it is
still a habit. As far as the other recovery modes go - that was my
question, I couldn't find any documentation that said a checkpoint was
necessary in Full Recovery mode, but I wanted to be sure that that
wasn't the issue. At this point it seems that my understanding of what
should be happening is correct, now I need to find out what is
preventing the log backups from freeing the space.
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> >
> > The only thing that doesn't appear to be happening that I normally
> > implement on my datawarehouse boxes is issuing a checkpoint. I know
> > that has to be done when you're in Simple Recovery mode, but I don't
> > find reference to it being required when in Full Recovery mode. Could
> > that be the problem?
> >
> Where are you getting the notion that you have to issue checkpoints?
> Those should happen automatically in Simple mode. In the other modes,
> backing up the transaction log performs the truncation.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Log File Management

I am working with a client on a production OLTP database using Full
Recovery model. I normally work in datawarehouse environments with
Simple Recovery turned on. They are performing nightly Full Backups and
hourly log backups Monday through Friday. Sunday night is a normal full
backup followed by a log backup and shrinking the log file. Everyweek
the log grows from 200MB on Monday Morning to 12GB on Friday. I have
already recommended not shrinking the log files on Sunday nights as it
only introduces overhead each day during peak hours while the database
expands the log files it previously shrank. My question is why does
this file continue to grow throughout the week? I would expect that
after each log file backup, the log segments that have been backed up
should become available to be reused. I don't see that they are issuing
a checkpoint, and I'm wondering if that is the issue. Can anyone shed
light on this. The information I have found in BOL and online would
indicate that my understanding is correct and the log files should be
being reused after the backup. I just want to be sure before I go
searching for issues with open transactions, etc... Thanks for any and
all help...
Kevinkevin.karlin@.gmail.com wrote:
> I am working with a client on a production OLTP database using Full
> Recovery model. I normally work in datawarehouse environments with
> Simple Recovery turned on. They are performing nightly Full Backups and
> hourly log backups Monday through Friday. Sunday night is a normal full
> backup followed by a log backup and shrinking the log file. Everyweek
> the log grows from 200MB on Monday Morning to 12GB on Friday. I have
> already recommended not shrinking the log files on Sunday nights as it
> only introduces overhead each day during peak hours while the database
> expands the log files it previously shrank. My question is why does
> this file continue to grow throughout the week? I would expect that
> after each log file backup, the log segments that have been backed up
> should become available to be reused. I don't see that they are issuing
> a checkpoint, and I'm wondering if that is the issue. Can anyone shed
> light on this. The information I have found in BOL and online would
> indicate that my understanding is correct and the log files should be
> being reused after the backup. I just want to be sure before I go
> searching for issues with open transactions, etc... Thanks for any and
> all help...
> Kevin
>
Are they doing index rebuilds or some sort of importing during the week?
The log backups will flush out any committed transactions. Reindexing
(or index defragging) generates a LOT of transactional activity.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||There is a nightly reindex scheduled, but I believe that the growth is
happening primarily during the day. Monday 6am log files were 200Mb,
Monday 3pm log files were about 1.5GB. Reindexing would happen at 11pm,
so that would not have impacted the log file size yet. There is no
importing happening as this is a production OLTP database. The essence
of my question i: in a perfect world, if the transactions were all
being committed immediately the hourly log backup should free up log
space for use again.
An example of how I think things are supposed to work:
Assume that the log was sized at 2GB at 8AM.
>From 8am to 9am transactions generated 200MB of log file entries.
Of the 200MB of log entries 180MB are committed.
At 9am a log backup occurs, the 180MB should be backed up and then
freed for use again.
At 9:01am there should be 1.98GB free in the log file (20MB used).
This does not seem to be happening on this system. Unfortunately this
is an off the shelf package that has some custom GUI interfaces added,
so there is plenty of opportunity for these transactions to be held
open because of bad coding. Before I go back and start looking under
rocks for problems I thought it prudent to be sure I knew how the
mechanics were supposed to be working and that something basic in the
backup process wasn't causing the logs to continue to grow.
The only thing that doesn't appear to be happening that I normally
implement on my datawarehouse boxes is issuing a checkpoint. I know
that has to be done when you're in Simple Recovery mode, but I don't
find reference to it being required when in Full Recovery mode. Could
that be the problem?
Thanks
Kevin
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Are they doing index rebuilds or some sort of importing during the week?
> The log backups will flush out any committed transactions. Reindexing
> (or index defragging) generates a LOT of transactional activity.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||kevin.karlin@.gmail.com wrote:
> The only thing that doesn't appear to be happening that I normally
> implement on my datawarehouse boxes is issuing a checkpoint. I know
> that has to be done when you're in Simple Recovery mode, but I don't
> find reference to it being required when in Full Recovery mode. Could
> that be the problem?
>
Where are you getting the notion that you have to issue checkpoints?
Those should happen automatically in Simple mode. In the other modes,
backing up the transaction log performs the truncation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It seems I wasn't entirely clear in my second post. I normally work in
a DW environment not an OLTP environment. We don't have a need for
anything but Simple Recovery. The box that is having the problem is an
OLTP database using Full Recovery. I know that in Simple mode the
server will issue checkpoints automatically. However, my experience has
been that issuing a checkpoint right before a log truncation ensured
you got the most space released. This goes way back to version 6.5 as I
recall, so it may have become unnecessary at some point, but it is
still a habit. As far as the other recovery modes go - that was my
question, I couldn't find any documentation that said a checkpoint was
necessary in Full Recovery mode, but I wanted to be sure that that
wasn't the issue. At this point it seems that my understanding of what
should be happening is correct, now I need to find out what is
preventing the log backups from freeing the space.
Tracy McKibben wrote:
> kevin.karlin@.gmail.com wrote:
> Where are you getting the notion that you have to issue checkpoints?
> Those should happen automatically in Simple mode. In the other modes,
> backing up the transaction log performs the truncation.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Log file increase very fast

I installed SQL server 2000 on one NT4.0 server and one Win2000 server. The
SQL server on NT4.0 is working fine. It's log file is always less than 5MB.
But on Win2000 I think there are some problems because the log file is
increasing very fast,now it is 25000MB.The two SQL server almost do same
work and configuration are same. Any help will be appreciated.Charms,
Have you checked the database settings on both servers . You may have one
server set to a higher recovery model.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Charms Zhou" <charmszhou@.hotmail.com> wrote in message
news:uWzADRraDHA.2580@.TK2MSFTNGP09.phx.gbl...
> I installed SQL server 2000 on one NT4.0 server and one Win2000 server.
The
> SQL server on NT4.0 is working fine. It's log file is always less than
5MB.
> But on Win2000 I think there are some problems because the log file is
> increasing very fast,now it is 25000MB.The two SQL server almost do same
> work and configuration are same. Any help will be appreciated.
>|||Also, perhaps you are doing index rebuilds on one machine but not the other?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Greg Obleshchuk" <greg@.ag-software.com> wrote in message
news:unhttPsaDHA.2632@.TK2MSFTNGP09.phx.gbl...
> Charms,
> Have you checked the database settings on both servers . You may have one
> server set to a higher recovery model.
>
> --
> I hope this helps
> regards
> Greg O MCSD
> http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
> Builder, the quickest way to document your database
> http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
> Property Extended properties manager for SQL 2000
> http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
> program
> http://www.ag-software.com. Free programming tools
>
> "Charms Zhou" <charmszhou@.hotmail.com> wrote in message
> news:uWzADRraDHA.2580@.TK2MSFTNGP09.phx.gbl...
> > I installed SQL server 2000 on one NT4.0 server and one Win2000 server.
> The
> > SQL server on NT4.0 is working fine. It's log file is always less than
> 5MB.
> > But on Win2000 I think there are some problems because the log file is
> > increasing very fast,now it is 25000MB.The two SQL server almost do same
> > work and configuration are same. Any help will be appreciated.
> >
> >
>

Log file growing very large?

Hello All,
All you SQL dba guru's.
I have na MSDE server. All seems to be working with app but I looked at the
server log file and saw that it has grown to 65 gigs. The mdf file is only
50 megs.
What is causing the log file to grow so large.?
Can I just dump the log file?
Thanks, Bill
You are using FULL recovery mode, and you've never performed a backup or
dumped the transaction logs?
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hi,
Add on to Aaron,
Looks like the recovery model for your database is FULL or
BULK_LOGGED.Please change the recovery model for the database to
SIMPLE if your database is non production. Use the below command to set the
database to SIMPLE.
ALTER database <DBNAME> set recovery SIMPLE
If you do not have the 65 G to take a backup or if you do not want the
transaction log backup. You could truncate the transaction
log and shrink the LDF file.
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail since
you do not have hard disk space)
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If you need to keep the recovery model to FULL then schedule a transaction
log backup in frequent intervals (atleast 30 minutes once).
This will keep the log file size under control.
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hello and thanks,
does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
clear out and reduce the size of the log as part of its process?
Or do I need to do a "backup log <dbname> with truncate_only" also?
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Add on to Aaron,
> Looks like the recovery model for your database is FULL or
> BULK_LOGGED.Please change the recovery model for the database to
> SIMPLE if your database is non production. Use the below command to set
the
> database to SIMPLE.
> ALTER database <DBNAME> set recovery SIMPLE
> If you do not have the 65 G to take a backup or if you do not want the
> transaction log backup. You could truncate the transaction
> log and shrink the LDF file.
> backup log <dbname> with truncate_only
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> If you need the transaction log backup do:-
> backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail
since
> you do not have hard disk space)
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> Now execute the below command to see log file size and usage.
> DBCC SQLPERF(LOGSPACE)
>
> Note:
> If you need to keep the recovery model to FULL then schedule a transaction
> log backup in frequent intervals (atleast 30 minutes once).
> This will keep the log file size under control.
> Thanks
> Hari
> MCDBA
>
> "Bill D" <Bill@.test.com> wrote in message
> news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> the
> only
>
|||Hi,
Either one will do.
If you need the transaction log backup execute below;
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:ONVLKt6gEHA.632@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hello and thanks,
> does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
> clear out and reduce the size of the log as part of its process?
> Or do I need to do a "backup log <dbname> with truncate_only" also?
> Thanks,
> Bill
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> the
> since
transaction[vbcol=seagreen]
at
>

log file getting larger**

Hi
I'm working with SQL server 2000
and I have a question about maintenancing a db:
I have dat and log files located in D: drive
and my drive was getting full and specially the
size of db's log file is larger,to solve
my problem I added another log file which is located in
another drive with more free space,and I shrink the
db periodically,
Now,Is there another way to prevent getting the db
larger (for example someway to clear some old information
of log file) ?
or another ways?
any help would be greatly thankful.Hi,
How to shrink the existing log file:
1. Perform a Transaction log backup (BACKUP LOG - refer books online)
2. Use DBCC SHRINKFILE on trasnaction log file
Have a look into the below link;
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272318
Check the Recovery model you are using for that database. If it is FULL and
your data is not critical then change the Recovery model to SIMPLE. Simple
recovery model will clear the Transaction log after commiting.
If you need the recovery model as FULL then,
Schedule a Transaction log backup based on your data growth, This can be
used when point in time recovery.
This will ensure that ur log file wont gow much.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||RM
If you don't cary about the data you can set recovery mode to SIMPLE
,otherwise do a regular BACKUP LOG in order to keep the size of the log and
a possibility recover the data at point of time.
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||Hi,
If it's SQL Server Ent. Edition default db recovery model is full, so when you create a new database it is always in full recovery model. So if you don't need point in time recovery and your db is not a mission critical OLTP database i recommend you to change the recovery model to simple.
If not then perform a regular log backups. And i recommend you to seperate log file to another physical disk partition.
Adjust your log file size to appopriate size to keep virtual log file number as low as possible.
Regards..
"RM" wrote:
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>

log file getting larger**

Hi
I'm working with SQL server 2000
and I have a question about maintenancing a db:
I have dat and log files located in D: drive
and my drive was getting full and specially the
size of db's log file is larger,to solve
my problem I added another log file which is located in
another drive with more free space,and I shrink the
db periodically,
Now,Is there another way to prevent getting the db
larger (for example someway to clear some old information
of log file) ?
or another ways?
any help would be greatly thankful.Hi,
How to shrink the existing log file:
1. Perform a Transaction log backup (BACKUP LOG - refer books online)
2. Use DBCC SHRINKFILE on trasnaction log file
Have a look into the below link;
http://support.microsoft.com/defaul...b;EN-US;q272318
Check the Recovery model you are using for that database. If it is FULL and
your data is not critical then change the Recovery model to SIMPLE. Simple
recovery model will clear the Transaction log after commiting.
If you need the recovery model as FULL then,
Schedule a Transaction log backup based on your data growth, This can be
used when point in time recovery.
This will ensure that ur log file wont gow much.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||RM
If you don't cary about the data you can set recovery mode to SIMPLE
,otherwise do a regular BACKUP LOG in order to keep the size of the log and
a possibility recover the data at point of time.
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>|||Hi,
If it's SQL Server Ent. Edition default db recovery model is full, so when y
ou create a new database it is always in full recovery model. So if you don'
t need point in time recovery and your db is not a mission critical OLTP dat
abase i recommend you to ch
ange the recovery model to simple.
If not then perform a regular log backups. And i recommend you to seperate l
og file to another physical disk partition.
Adjust your log file size to appopriate size to keep virtual log file number
as low as possible.
Regards..
"RM" wrote:

> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>

log file getting larger**

Hi
I'm working with SQL server 2000
and I have a question about maintenancing a db:
I have dat and log files located in D: drive
and my drive was getting full and specially the
size of db's log file is larger,to solve
my problem I added another log file which is located in
another drive with more free space,and I shrink the
db periodically,
Now,Is there another way to prevent getting the db
larger (for example someway to clear some old information
of log file) ?
or another ways?
any help would be greatly thankful.
Hi,
How to shrink the existing log file:
1. Perform a Transaction log backup (BACKUP LOG - refer books online)
2. Use DBCC SHRINKFILE on trasnaction log file
Have a look into the below link;
http://support.microsoft.com/default...;EN-US;q272318
Check the Recovery model you are using for that database. If it is FULL and
your data is not critical then change the Recovery model to SIMPLE. Simple
recovery model will clear the Transaction log after commiting.
If you need the recovery model as FULL then,
Schedule a Transaction log backup based on your data growth, This can be
used when point in time recovery.
This will ensure that ur log file wont gow much.
Thanks
Hari
MCDBA
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>
|||RM
If you don't cary about the data you can set recovery mode to SIMPLE
,otherwise do a regular BACKUP LOG in order to keep the size of the log and
a possibility recover the data at point of time.
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opsapfoztqhqligo@.msnews.microsoft.com...
> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>
|||Hi,
If it's SQL Server Ent. Edition default db recovery model is full, so when you create a new database it is always in full recovery model. So if you don't need point in time recovery and your db is not a mission critical OLTP database i recommend you to ch
ange the recovery model to simple.
If not then perform a regular log backups. And i recommend you to seperate log file to another physical disk partition.
Adjust your log file size to appopriate size to keep virtual log file number as low as possible.
Regards..
"RM" wrote:

> Hi
> I'm working with SQL server 2000
> and I have a question about maintenancing a db:
> I have dat and log files located in D: drive
> and my drive was getting full and specially the
> size of db's log file is larger,to solve
> my problem I added another log file which is located in
> another drive with more free space,and I shrink the
> db periodically,
> Now,Is there another way to prevent getting the db
> larger (for example someway to clear some old information
> of log file) ?
> or another ways?
> any help would be greatly thankful.
>

Log file cant acquire connection while working offline

hi

As I trun Work offline - true, my connection manager for log file says-it cant acquire connection while work offline is true.Where as other oledb connections work fine.

Even it tried to get around by putting DelayValidation as true, but didnt work.

Is there is anyother setting that has be set.

Thanks and Regards

Rahul Kumar

Hello, are you referring to messages you see when you open the package with 'Work Offline'=true?

When you say "work fine" do you mean you can open the package and do not see any messages related to the OLEDB connections?

Thank you

|||

Are there any tasks using the connection managers that apparently don't throw any errors?

-Jamie

|||

I think this thread is related... I think the error is received while executing the package:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005760&SiteID=1

|||

Hi Craig,

I mean, when i run the package without having log file connection(which uses file connection) and work offline-true,delayvalidation-true with oledb connections, package returns no error and does the purpose.

Where as if i use log file with file connectin manager with above conditions as true,this file connection manager throws the error that it cant acquire connection.

Now if oledb connections can acquire connection,why does file connection manager fails.

Thanks and Regards

Rahul Kumar

|||

hi jamie

yes other which donot throw errors are olddb connections for my source and destination.

Thanks and regards

Rahul Kumar

|||

yes Rafaels

Both are on similar lines.But fact is problem still exists and i have given it many tests.

Log file cant acquire connection while working offline

hi

As I trun Work offline - true, my connection manager for log file says-it cant acquire connection while work offline is true.Where as other oledb connections work fine.

Even it tried to get around by putting DelayValidation as true, but didnt work.

Is there is anyother setting that has be set.

Thanks and Regards

Rahul Kumar

Hello, are you referring to messages you see when you open the package with 'Work Offline'=true?

When you say "work fine" do you mean you can open the package and do not see any messages related to the OLEDB connections?

Thank you

|||

Are there any tasks using the connection managers that apparently don't throw any errors?

-Jamie

|||

I think this thread is related... I think the error is received while executing the package:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1005760&SiteID=1

|||

Hi Craig,

I mean, when i run the package without having log file connection(which uses file connection) and work offline-true,delayvalidation-true with oledb connections, package returns no error and does the purpose.

Where as if i use log file with file connectin manager with above conditions as true,this file connection manager throws the error that it cant acquire connection.

Now if oledb connections can acquire connection,why does file connection manager fails.

Thanks and Regards

Rahul Kumar

|||

hi jamie

yes other which donot throw errors are olddb connections for my source and destination.

Thanks and regards

Rahul Kumar

|||

yes Rafaels

Both are on similar lines.But fact is problem still exists and i have given it many tests.

Locks causing site slow.

Pls guide me in solving the below issue..
Our site sometimes is working too slow and sometimes it works fine,Its is java with sqlserver as backend.
Its in OLTP for banking ..web enabled.
Server Config is as under
Db Server. + Appl Server (Javaw)
--
Db Size is : 6 gb
Server : Compaq Proliant ML350 G2
CPU : Intel PIII 1.133 GHz * 2
Ram : 2 GB (512*4)
3 * 36.4 GB 10K U3 HP
1.174 TB (6 x 146.8 GB 1² with standard internal hot plug drive cage + (2 x 146.8 GB 1² ) with optional ML3xx Internal Two Bay Hot Plug SCSI Drive Cage)
Log and Data files are on same hdd : Raid Level 5..
Web server : Same config with JRun and IIS.(also some schedules running on which hits db after every 10 mins in jave for email etc)
We are unable to trace the issues...site sometimes is dead..and sometimes works fine.
I check db proc's and indexes..it is not proper and sometimes one or two processes are getting blocked(but this happen in 3-4 days)...Usuage of tempdb is also high..but we had schedule our processes which truncates tempdb time to time..I had advised some indexes + optimzied most of the procedures..(this is in test server)
--
and after all we had installed OS + IIS of web server..WE HAD CHECKD ONE THING..WHEN SITE IS SLOW ITS WORKING WITH JRUN(DIFFT PORT) BUT WITH IIS ITS DEAD SLOW...
They are uusing xml with Jsp and java...
--
as Per perf counter of db server: Cpu usage is low,full scan / sec(sometimes its high around 6+),pages / sec =2 or 3/recompile / sec(always 0.00123...etc))
pls help me ..in solving the following issues.
1) We r not able to figure out whether db is slow or is there any db server issues...(optimzied queries and indexes are still on test server)
2) is there any web server issues..wot things we need to check with IIS/Jrun or java..
3) If there is any deadlock or blocking session on one procedure..will it hamper the performance of entire site or only web pages or processes related with the tables used in procedures becomes locked and slow...
4) Is it advisable to restart sql server..this thing clients DBA are doing more often whenver sites becomes slow...or may be webserver ..and after this they found site works fine.
Pls help me in finding solution or forward to concerned expertise persons.
WE ARE TRYING TO FIGURE OUT PROBLEM FROM LAST 10 DAYS BUT FAILED...sanjay
<http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
"sanjay" <sanjay_agn@.rediffmail.com> wrote in message
news:FFA51457-AB7C-42AC-8022-7A27E3CA797C@.microsoft.com...
> Pls guide me in solving the below issue..
> Our site sometimes is working too slow and sometimes it works fine,Its is
java with sqlserver as backend.
> Its in OLTP for banking ..web enabled.
> Server Config is as under
> Db Server. + Appl Server (Javaw)
> --
> Db Size is : 6 gb
> Server : Compaq Proliant ML350 G2
> CPU : Intel PIII 1.133 GHz * 2
> Ram : 2 GB (512*4)
> 3 * 36.4 GB 10K U3 HP
> 1.174 TB (6 x 146.8 GB 1² with standard internal hot plug drive cage + (2
x 146.8 GB 1² ) with optional ML3xx Internal Two Bay Hot Plug SCSI Drive
Cage)
> Log and Data files are on same hdd : Raid Level 5..
> Web server : Same config with JRun and IIS.(also some schedules running on
which hits db after every 10 mins in jave for email etc)
> We are unable to trace the issues...site sometimes is dead..and sometimes
works fine.
> I check db proc's and indexes..it is not proper and sometimes one or two
processes are getting blocked(but this happen in 3-4 days)...Usuage of
tempdb is also high..but we had schedule our processes which truncates
tempdb time to time..I had advised some indexes + optimzied most of the
procedures..(this is in test server)
> --
> and after all we had installed OS + IIS of web server..WE HAD CHECKD ONE
THING..WHEN SITE IS SLOW ITS WORKING WITH JRUN(DIFFT PORT) BUT WITH IIS ITS
DEAD SLOW...
> They are uusing xml with Jsp and java...
> --
> as Per perf counter of db server: Cpu usage is low,full scan /
sec(sometimes its high around 6+),pages / sec =2 or 3/recompile / sec(always
0.00123...etc))
>
> pls help me ..in solving the following issues.
> 1) We r not able to figure out whether db is slow or is there any db
server issues...(optimzied queries and indexes are still on test server)
> 2) is there any web server issues..wot things we need to check with
IIS/Jrun or java..
> 3) If there is any deadlock or blocking session on one procedure..will it
hamper the performance of entire site or only web pages or processes related
with the tables used in procedures becomes locked and slow...
> 4) Is it advisable to restart sql server..this thing clients DBA are doing
more often whenver sites becomes slow...or may be webserver ..and after this
they found site works fine.
>
> Pls help me in finding solution or forward to concerned expertise persons.
> WE ARE TRYING TO FIGURE OUT PROBLEM FROM LAST 10 DAYS BUT FAILED...
>|||To determine if you have any deadlocks enable the following trace flags
DBCC TRACEON (1204,3605,-1)
This will output any deadlock info into the SQL ErrorLog
--
HTH
Ryan Waight, MCDBA, MCSE
"sanjay" <sanjay_agn@.rediffmail.com> wrote in message
news:FFA51457-AB7C-42AC-8022-7A27E3CA797C@.microsoft.com...
> Pls guide me in solving the below issue..
> Our site sometimes is working too slow and sometimes it works fine,Its is
java with sqlserver as backend.
> Its in OLTP for banking ..web enabled.
> Server Config is as under
> Db Server. + Appl Server (Javaw)
> --
> Db Size is : 6 gb
> Server : Compaq Proliant ML350 G2
> CPU : Intel PIII 1.133 GHz * 2
> Ram : 2 GB (512*4)
> 3 * 36.4 GB 10K U3 HP
> 1.174 TB (6 x 146.8 GB 1² with standard internal hot plug drive cage + (2
x 146.8 GB 1² ) with optional ML3xx Internal Two Bay Hot Plug SCSI Drive
Cage)
> Log and Data files are on same hdd : Raid Level 5..
> Web server : Same config with JRun and IIS.(also some schedules running on
which hits db after every 10 mins in jave for email etc)
> We are unable to trace the issues...site sometimes is dead..and sometimes
works fine.
> I check db proc's and indexes..it is not proper and sometimes one or two
processes are getting blocked(but this happen in 3-4 days)...Usuage of
tempdb is also high..but we had schedule our processes which truncates
tempdb time to time..I had advised some indexes + optimzied most of the
procedures..(this is in test server)
> --
> and after all we had installed OS + IIS of web server..WE HAD CHECKD ONE
THING..WHEN SITE IS SLOW ITS WORKING WITH JRUN(DIFFT PORT) BUT WITH IIS ITS
DEAD SLOW...
> They are uusing xml with Jsp and java...
> --
> as Per perf counter of db server: Cpu usage is low,full scan /
sec(sometimes its high around 6+),pages / sec =2 or 3/recompile / sec(always
0.00123...etc))
>
> pls help me ..in solving the following issues.
> 1) We r not able to figure out whether db is slow or is there any db
server issues...(optimzied queries and indexes are still on test server)
> 2) is there any web server issues..wot things we need to check with
IIS/Jrun or java..
> 3) If there is any deadlock or blocking session on one procedure..will it
hamper the performance of entire site or only web pages or processes related
with the tables used in procedures becomes locked and slow...
> 4) Is it advisable to restart sql server..this thing clients DBA are doing
more often whenver sites becomes slow...or may be webserver ..and after this
they found site works fine.
>
> Pls help me in finding solution or forward to concerned expertise persons.
> WE ARE TRYING TO FIGURE OUT PROBLEM FROM LAST 10 DAYS BUT FAILED...
>

Locks and Blocking

I am working on solving performance problems for a client experiencing
frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
application is a self-developed VB6 application that is very resource
intensive. With about 150 simultaneous users CPU utilization frequently
exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
all users to exit and the server to be rebooted.
Their programming model uses ADO recordsets. Once a recordset is fetched to
the application and displayed on the form, the rsX variable is set to nothing
and the user can update the form. When the OK button is pressed on the form,
the application code does the following:
strSQL = "Select * From Contact Where 1=2"
Set rsX = Openrecordset(strSQL)
Rsx.Field1 = Form!Field1
...
...
Rsx.Fieldn = Form!Fieldn
Rsx.Update
Question: Does the grabbing of a recordset in this manner lock the whole
table since there is no individual record or page to lock? An if 20 users did
this simultaneously, would that lead to a blocking issue?
Any insight would be appreciated!
Larry Menzin
American Techsystems Corp.
These links may help. The first link is a VB link about locking -
looks like you definitely might have some locking issues.
http://msdn.microsoft.com/library/de...oidlocking.asp
http://www.sql-server-performance.co...cing_locks.asp
http://msdn.microsoft.com/library/de...on_7a_1hf7.asp
|||Do each of the tables have a valid PK Constraint defined on them? Is the
Update issued by ADO using the PK to do the update? If not it is more
likely the Update is causing the problems. That is a pretty lame way to
update rows anyway. They should create a stored procedure to do the update
and call that from the front end instead.
Andrew J. Kelly SQL MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
>I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization,
> requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched
> to
> the application and displayed on the form, the rsX variable is set to
> nothing
> and the user can update the form. When the OK button is pressed on the
> form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users
> did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.
|||Hi,
When u execute a select statement it will fetech all the columns and
rows now imagine if the table has 40 or 45 coulmns then how much
resource it will use .
u should field name and ur where clause.
Secondly when the recodset work is done close the recordset.
hope this help
from
killer
Andrew J. Kelly wrote:[vbcol=seagreen]
> Do each of the tables have a valid PK Constraint defined on them? Is the
> Update issued by ADO using the PK to do the update? If not it is more
> likely the Update is causing the problems. That is a pretty lame way to
> update rows anyway. They should create a stored procedure to do the update
> and call that from the front end instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
|||Hi
Run profiler and see what/where this is happening. You may want to look for
items in the locking event class and use the read/write/duration values to
find queries that take a long time and do a large number of I/O. You will
then be able to analyse query plans and indexes, or maybe even want to pass
the output of a trace into the index tuning wizard and see what it comes up
with.
John
"Larry Menzin" wrote:

> I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched to
> the application and displayed on the form, the rsX variable is set to nothing
> and the user can update the form. When the OK button is pressed on the form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.

Locks and Blocking

I am working on solving performance problems for a client experiencing
frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
application is a self-developed VB6 application that is very resource
intensive. With about 150 simultaneous users CPU utilization frequently
exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiring
all users to exit and the server to be rebooted.
Their programming model uses ADO recordsets. Once a recordset is fetched to
the application and displayed on the form, the rsX variable is set to nothin
g
and the user can update the form. When the OK button is pressed on the form,
the application code does the following:
strSQL = "Select * From Contact Where 1=2"
Set rsX = Openrecordset(strSQL)
Rsx.Field1 = Form!Field1
...
...
Rsx.Fieldn = Form!Fieldn
Rsx.Update
Question: Does the grabbing of a recordset in this manner lock the whole
table since there is no individual record or page to lock? An if 20 users di
d
this simultaneously, would that lead to a blocking issue?
Any insight would be appreciated!
Larry Menzin
American Techsystems Corp.These links may help. The first link is a VB link about locking -
looks like you definitely might have some locking issues.
http://msdn.microsoft.com/library/d...
idlocking.asp
http://www.sql-server-performance.c...ucing_locks.asp
http://msdn.microsoft.com/library/d... />
a_1hf7.asp|||Do each of the tables have a valid PK Constraint defined on them? Is the
Update issued by ADO using the PK to do the update? If not it is more
likely the Update is causing the problems. That is a pretty lame way to
update rows anyway. They should create a stored procedure to do the update
and call that from the front end instead.
Andrew J. Kelly SQL MVP
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...
>I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization,
> requiring
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched
> to
> the application and displayed on the form, the rsX variable is set to
> nothing
> and the user can update the form. When the OK button is pressed on the
> form,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users
> did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.|||Hi,
When u execute a select statement it will fetech all the columns and
rows now imagine if the table has 40 or 45 coulmns then how much
resource it will use .
u should field name and ur where clause.
Secondly when the recodset work is done close the recordset.
hope this help
from
killer
Andrew J. Kelly wrote:[vbcol=seagreen]
> Do each of the tables have a valid PK Constraint defined on them? Is the
> Update issued by ADO using the PK to do the update? If not it is more
> likely the Update is causing the problems. That is a pretty lame way to
> update rows anyway. They should create a stored procedure to do the updat
e
> and call that from the front end instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:0DAF194C-3019-4E7C-AB0F-691B95911D0E@.microsoft.com...|||Hi
Run profiler and see what/where this is happening. You may want to look for
items in the locking event class and use the read/write/duration values to
find queries that take a long time and do a large number of I/O. You will
then be able to analyse query plans and indexes, or maybe even want to pass
the output of a trace into the index tuning wizard and see what it comes up
with.
John
"Larry Menzin" wrote:

> I am working on solving performance problems for a client experiencing
> frequent blocks on their server which has 4 CPUs and 4GB or RAM. Their
> application is a self-developed VB6 application that is very resource
> intensive. With about 150 simultaneous users CPU utilization frequently
> exceeds 90%. Yesterday their server froze at 100% CPU utilization, requiri
ng
> all users to exit and the server to be rebooted.
> Their programming model uses ADO recordsets. Once a recordset is fetched t
o
> the application and displayed on the form, the rsX variable is set to noth
ing
> and the user can update the form. When the OK button is pressed on the for
m,
> the application code does the following:
> strSQL = "Select * From Contact Where 1=2"
> Set rsX = Openrecordset(strSQL)
> Rsx.Field1 = Form!Field1
> ...
> ...
> Rsx.Fieldn = Form!Fieldn
> Rsx.Update
> Question: Does the grabbing of a recordset in this manner lock the whole
> table since there is no individual record or page to lock? An if 20 users
did
> this simultaneously, would that lead to a blocking issue?
> Any insight would be appreciated!
> --
> Larry Menzin
> American Techsystems Corp.