Sorry for such a basic question but what exactly is a Repeatable read as far
as transaction Isolation levels go. I understand the concept of serializable,
read committed & read uncommitted but i can't find any documentation that
describes the repeatable read level. How does this isolation level affect
concurrency. Sorry for such a stoopid question but it woud really help if I
got the low down once & for all. Many thanks in advance.
Ant
Repeatable read and serializable are very similar, and your question is far
from stupid.
In repeatable read, SQL Server will hold locks on the data you have read
until the end of the transaction. (Normally, in read committed, locks on
data you have read are released as soon as you are done reading.) So no one
can change change the data until you commit or rollback. Updates and deletes
are not permitted.
Serializable prevents INSERTS of new data that satisfy a condition that you
used for searching.
Example
BEGIN TRAN
SELECT * FROM titles
WHERE price < 5
-- suppose the above returns 2 rows; in repeatable read, those two rows are
now locked and no one can change them
-- until you commit or rollback; but someone in another connection could
insert a whole new row with price < 5
-- So, in serializable you are absolutely guaranteed that if you run the
exact SELECT again, you will get the same result:
SELECT * FROM titles
WHERE price < 5
-- But, in repeatable read, you might get more rows back, because of
inserts.
COMMIT TRAN
In repeatable read, SQL Server locks the data you read.
In serializable, SQL Server locks the data and the 'gaps' between the data.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:78571FC8-E575-4D1C-BA59-E07C2C176A80@.microsoft.com...
> Sorry for such a basic question but what exactly is a Repeatable read as
> far
> as transaction Isolation levels go. I understand the concept of
> serializable,
> read committed & read uncommitted but i can't find any documentation that
> describes the repeatable read level. How does this isolation level affect
> concurrency. Sorry for such a stoopid question but it woud really help if
> I
> got the low down once & for all. Many thanks in advance.
> Ant
|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant
|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Friday, March 9, 2012
Repeatable read. What is it exactly...
Sorry for such a basic question but what exactly is a Repeatable read as far
as transaction Isolation levels go. I understand the concept of serializable
,
read committed & read uncommitted but i can't find any documentation that
describes the repeatable read level. How does this isolation level affect
concurrency. Sorry for such a stoopid question but it woud really help if I
got the low down once & for all. Many thanks in advance.
AntRepeatable read and serializable are very similar, and your question is far
from stupid.
In repeatable read, SQL Server will hold locks on the data you have read
until the end of the transaction. (Normally, in read committed, locks on
data you have read are released as soon as you are done reading.) So no one
can change change the data until you commit or rollback. Updates and deletes
are not permitted.
Serializable prevents INSERTS of new data that satisfy a condition that you
used for searching.
Example
BEGIN TRAN
SELECT * FROM titles
WHERE price < 5
-- suppose the above returns 2 rows; in repeatable read, those two rows are
now locked and no one can change them
-- until you commit or rollback; but someone in another connection could
insert a whole new row with price < 5
-- So, in serializable you are absolutely guaranteed that if you run the
exact SELECT again, you will get the same result:
SELECT * FROM titles
WHERE price < 5
-- But, in repeatable read, you might get more rows back, because of
inserts.
COMMIT TRAN
In repeatable read, SQL Server locks the data you read.
In serializable, SQL Server locks the data and the 'gaps' between the data.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:78571FC8-E575-4D1C-BA59-E07C2C176A80@.microsoft.com...
> Sorry for such a basic question but what exactly is a Repeatable read as
> far
> as transaction Isolation levels go. I understand the concept of
> serializable,
> read committed & read uncommitted but i can't find any documentation that
> describes the repeatable read level. How does this isolation level affect
> concurrency. Sorry for such a stoopid question but it woud really help if
> I
> got the low down once & for all. Many thanks in advance.
> Ant|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>
as transaction Isolation levels go. I understand the concept of serializable
,
read committed & read uncommitted but i can't find any documentation that
describes the repeatable read level. How does this isolation level affect
concurrency. Sorry for such a stoopid question but it woud really help if I
got the low down once & for all. Many thanks in advance.
AntRepeatable read and serializable are very similar, and your question is far
from stupid.
In repeatable read, SQL Server will hold locks on the data you have read
until the end of the transaction. (Normally, in read committed, locks on
data you have read are released as soon as you are done reading.) So no one
can change change the data until you commit or rollback. Updates and deletes
are not permitted.
Serializable prevents INSERTS of new data that satisfy a condition that you
used for searching.
Example
BEGIN TRAN
SELECT * FROM titles
WHERE price < 5
-- suppose the above returns 2 rows; in repeatable read, those two rows are
now locked and no one can change them
-- until you commit or rollback; but someone in another connection could
insert a whole new row with price < 5
-- So, in serializable you are absolutely guaranteed that if you run the
exact SELECT again, you will get the same result:
SELECT * FROM titles
WHERE price < 5
-- But, in repeatable read, you might get more rows back, because of
inserts.
COMMIT TRAN
In repeatable read, SQL Server locks the data you read.
In serializable, SQL Server locks the data and the 'gaps' between the data.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:78571FC8-E575-4D1C-BA59-E07C2C176A80@.microsoft.com...
> Sorry for such a basic question but what exactly is a Repeatable read as
> far
> as transaction Isolation levels go. I understand the concept of
> serializable,
> read committed & read uncommitted but i can't find any documentation that
> describes the repeatable read level. How does this isolation level affect
> concurrency. Sorry for such a stoopid question but it woud really help if
> I
> got the low down once & for all. Many thanks in advance.
> Ant|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>
Repeatable read. What is it exactly...
Sorry for such a basic question but what exactly is a Repeatable read as far
as transaction Isolation levels go. I understand the concept of serializable,
read committed & read uncommitted but i can't find any documentation that
describes the repeatable read level. How does this isolation level affect
concurrency. Sorry for such a stoopid question but it woud really help if I
got the low down once & for all. Many thanks in advance.
AntRepeatable read and serializable are very similar, and your question is far
from stupid.
In repeatable read, SQL Server will hold locks on the data you have read
until the end of the transaction. (Normally, in read committed, locks on
data you have read are released as soon as you are done reading.) So no one
can change change the data until you commit or rollback. Updates and deletes
are not permitted.
Serializable prevents INSERTS of new data that satisfy a condition that you
used for searching.
Example
BEGIN TRAN
SELECT * FROM titles
WHERE price < 5
-- suppose the above returns 2 rows; in repeatable read, those two rows are
now locked and no one can change them
-- until you commit or rollback; but someone in another connection could
insert a whole new row with price < 5
-- So, in serializable you are absolutely guaranteed that if you run the
exact SELECT again, you will get the same result:
SELECT * FROM titles
WHERE price < 5
-- But, in repeatable read, you might get more rows back, because of
inserts.
COMMIT TRAN
In repeatable read, SQL Server locks the data you read.
In serializable, SQL Server locks the data and the 'gaps' between the data.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:78571FC8-E575-4D1C-BA59-E07C2C176A80@.microsoft.com...
> Sorry for such a basic question but what exactly is a Repeatable read as
> far
> as transaction Isolation levels go. I understand the concept of
> serializable,
> read committed & read uncommitted but i can't find any documentation that
> describes the repeatable read level. How does this isolation level affect
> concurrency. Sorry for such a stoopid question but it woud really help if
> I
> got the low down once & for all. Many thanks in advance.
> Ant|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>
as transaction Isolation levels go. I understand the concept of serializable,
read committed & read uncommitted but i can't find any documentation that
describes the repeatable read level. How does this isolation level affect
concurrency. Sorry for such a stoopid question but it woud really help if I
got the low down once & for all. Many thanks in advance.
AntRepeatable read and serializable are very similar, and your question is far
from stupid.
In repeatable read, SQL Server will hold locks on the data you have read
until the end of the transaction. (Normally, in read committed, locks on
data you have read are released as soon as you are done reading.) So no one
can change change the data until you commit or rollback. Updates and deletes
are not permitted.
Serializable prevents INSERTS of new data that satisfy a condition that you
used for searching.
Example
BEGIN TRAN
SELECT * FROM titles
WHERE price < 5
-- suppose the above returns 2 rows; in repeatable read, those two rows are
now locked and no one can change them
-- until you commit or rollback; but someone in another connection could
insert a whole new row with price < 5
-- So, in serializable you are absolutely guaranteed that if you run the
exact SELECT again, you will get the same result:
SELECT * FROM titles
WHERE price < 5
-- But, in repeatable read, you might get more rows back, because of
inserts.
COMMIT TRAN
In repeatable read, SQL Server locks the data you read.
In serializable, SQL Server locks the data and the 'gaps' between the data.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:78571FC8-E575-4D1C-BA59-E07C2C176A80@.microsoft.com...
> Sorry for such a basic question but what exactly is a Repeatable read as
> far
> as transaction Isolation levels go. I understand the concept of
> serializable,
> read committed & read uncommitted but i can't find any documentation that
> describes the repeatable read level. How does this isolation level affect
> concurrency. Sorry for such a stoopid question but it woud really help if
> I
> got the low down once & for all. Many thanks in advance.
> Ant|||Kalen, thanks for your clear answer. I see the light! (I think)
So in fact, repeatable reads return more consistent data than read committed
as repeatable read locks the returning set until the transaction has either
been committed or rolled back,
:-
as opposed to read committed, which only locks the (committed) records
whilst the records are being read, therefore, the records in your recordset
may be updated by other transactions whilst it's sitting there in front of
you. correct?
Thanks for your help
Ant|||Hi Ant
We're only talking about shared locks here for data that has been read (with
a SELECT). So there really isn't a concept of the rows themselves being
committed.
Rows that are modified acquire EXCLUSIVE locks, and exclusive locks are
always held until the end of the transaction, no matter what isolation level
you are in.
But your final statement is true. In read committed level, you can read a
record, and someone can change it while you decide what to do with it.
On the other hand, if you are in repeatable read or serializable level, you
are preventing all access to that row, and that can have a negative impact
on performance.
There are always tradeoffs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7E7D398B-4FA9-4337-98CC-A0408FA2D494@.microsoft.com...
> Kalen, thanks for your clear answer. I see the light! (I think)
> So in fact, repeatable reads return more consistent data than read
> committed
> as repeatable read locks the returning set until the transaction has
> either
> been committed or rolled back,
> :-
> as opposed to read committed, which only locks the (committed) records
> whilst the records are being read, therefore, the records in your
> recordset
> may be updated by other transactions whilst it's sitting there in front of
> you. correct?
> Thanks for your help
> Ant
>
Wednesday, March 7, 2012
repair_rebuild and the transaction log
We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
Thanks
Part of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
Thanks
Part of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]
repair_rebuild and the transaction log
We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talkin
g
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
>
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talkin
g
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
>
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]
repair_rebuild and the transaction log
We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
> > We have a large-ish database (160GB) which grew to over 300GB last
weekend
> > when the maintenance job failed. Half of it is unused but I can't shrink
it
> > due to index errors and when I run checkdb it also reports index errors.
I
> > would like to run checkdb with repair_rebuild but there is not much room
for
> > the log file to grow now the DB has gotten so big - it will max out
around
> > 8-10GB.
> >
> > My questions are:
> >
> > Will repair_rebuild need the log file to grow any larger than 8GB on a
160GB
> > database? If so can I just move the log file to a drive with more room?
> >
> > I can't copy the DB to another server and test out the repair_rebuild -
does
> > anyone have an idea how long it will take on a 160GB database? Are we
talking
> > several hours or days?
> >
> > Is DBCC reindex a better option in my case?
> >
> > Thanks
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
> > We have a large-ish database (160GB) which grew to over 300GB last
weekend
> > when the maintenance job failed. Half of it is unused but I can't shrink
it
> > due to index errors and when I run checkdb it also reports index errors.
I
> > would like to run checkdb with repair_rebuild but there is not much room
for
> > the log file to grow now the DB has gotten so big - it will max out
around
> > 8-10GB.
> >
> > My questions are:
> >
> > Will repair_rebuild need the log file to grow any larger than 8GB on a
160GB
> > database? If so can I just move the log file to a drive with more room?
> >
> > I can't copy the DB to another server and test out the repair_rebuild -
does
> > anyone have an idea how long it will take on a 160GB database? Are we
talking
> > several hours or days?
> >
> > Is DBCC reindex a better option in my case?
> >
> > Thanks
Subscribe to:
Posts (Atom)