Hi,
I have an application that has reported deadlocks on the
sql server. I ran a profiler on it to capture deadlocks.
Can anyone indicate the best way to prevent deadlocks, any
sites and ideas, any advice would be welcome!
Thanxs!To prevent deadlocks.
1. Access resources in the same order... Choose an order for tables, and
have everyone use the tables in the same order .
2. Keep your transactions short... The longer your transaction , the longer
locks are held, the more likely you will bump into someone in this bad way..
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"claudy" <anonymous@.discussions.microsoft.com> wrote in message
news:095b01c3c3d3$69d023f0$a401280a@.phx.gbl...
> Hi,
> I have an application that has reported deadlocks on the
> sql server. I ran a profiler on it to capture deadlocks.
> Can anyone indicate the best way to prevent deadlocks, any
> sites and ideas, any advice would be welcome!
> Thanxs!
>|||It would be useful to provide trace when writing on deadlocks, because it can occur on single page as well in one select and another ,for example, delete statement. In this situation only indexing strategy can help
Use dbcc traceon (1204) from Query Analyzer to create trace in SQL Log filsql
Showing posts with label ran. Show all posts
Showing posts with label ran. Show all posts
Tuesday, March 27, 2012
deadlocking
Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>sql
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>sql
deadlocking
Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
Richard
The only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>
|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
Richard
The only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>
|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
deadlocking
Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 milli
on
> rows of data. A large table with a lot of wide columns. The script was r
an
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me tha
t
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 milli
on
> rows of data. A large table with a lot of wide columns. The script was r
an
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me tha
t
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
Sunday, March 25, 2012
Deadlock: Trace flag 1205, 1204
I want to log deadlocks. In query analyzer I ran dbcc
traceon(1205, 1204) on two different SQL Server 2000, SP3a
boxes. Stopped SQL Services and restarted on each box.
Created deadlocks on both boxes via the problem
application. Deadlocks are being written to sql server
logs on one box but not the other.
What is the difference and how can I tell if 1205 and 1204
trace flags are active?dbcc tracestatus(-1)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mike Mullane" <mike.mullane@.hpinc.com> wrote in message
news:082101c38915$62471510$a301280a@.phx.gbl...
> I want to log deadlocks. In query analyzer I ran dbcc
> traceon(1205, 1204) on two different SQL Server 2000, SP3a
> boxes. Stopped SQL Services and restarted on each box.
> Created deadlocks on both boxes via the problem
> application. Deadlocks are being written to sql server
> logs on one box but not the other.
> What is the difference and how can I tell if 1205 and 1204
> trace flags are active?
>|||Hi Mike,
Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays the status of all
currently enabled trace flags by specifying a value of -1.
Please make sure that you problem application can make deadlock every time
when you execute it. Here is a deadlock example, please to perform the on
both SQL Server using Query Analyzer and check to see if the deadlock is
recorded in both SQL Server's log.
Create a simple deadlock in pubs in two Query Analyzer windows.
Window 1:
dbcc traceon(3605)
dbcc traceon(1204)
begin tran update authors set contract = contract
Window 2: begin tran update titles set ytd_sales = ytd_sales
Window 1: update titles set ytd_sales = ytd_sales
Window 2: update authors set contract = contract
It works on my side and I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello Michael,
Perfect advice. I am able to recreate locks using your
example and validate the they are being written to the
log. However, I'm was having issues getting DBCC
TRACESTATUS(-1) or DBCC TRACESTATUS(1204) to behave as
described. When I run it I get: "Trace option(s) not
enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator."
So I ran "DBCC TRACEON" and then aftter running that I
ran "DBCC TRACESTATUS(-1)" and I get "TraceFlag Status
-- --
1204 1" which is what I want. So, it seems that the
order needed is "DBCC TRACEON(1204)" then "DBCC TRACEON"
must be run before "DBCC TRACESTATUS(-1)" will list.
Thanks for your help. I've learned a bit.
Mike
>--Original Message--
>Hi Mike,
>Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays
the status of all
>currently enabled trace flags by specifying a value of -1.
>Please make sure that you problem application can make
deadlock every time
>when you execute it. Here is a deadlock example, please
to perform the on
>both SQL Server using Query Analyzer and check to see if
the deadlock is
>recorded in both SQL Server's log.
>Create a simple deadlock in pubs in two Query Analyzer
windows.
>Window 1:
>dbcc traceon(3605)
>dbcc traceon(1204)
>begin tran update authors set contract = contract
>Window 2: begin tran update titles set ytd_sales =ytd_sales
>Window 1: update titles set ytd_sales = ytd_sales
>Window 2: update authors set contract = contract
>It works on my side and I am standing by for your
response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>
traceon(1205, 1204) on two different SQL Server 2000, SP3a
boxes. Stopped SQL Services and restarted on each box.
Created deadlocks on both boxes via the problem
application. Deadlocks are being written to sql server
logs on one box but not the other.
What is the difference and how can I tell if 1205 and 1204
trace flags are active?dbcc tracestatus(-1)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Mike Mullane" <mike.mullane@.hpinc.com> wrote in message
news:082101c38915$62471510$a301280a@.phx.gbl...
> I want to log deadlocks. In query analyzer I ran dbcc
> traceon(1205, 1204) on two different SQL Server 2000, SP3a
> boxes. Stopped SQL Services and restarted on each box.
> Created deadlocks on both boxes via the problem
> application. Deadlocks are being written to sql server
> logs on one box but not the other.
> What is the difference and how can I tell if 1205 and 1204
> trace flags are active?
>|||Hi Mike,
Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays the status of all
currently enabled trace flags by specifying a value of -1.
Please make sure that you problem application can make deadlock every time
when you execute it. Here is a deadlock example, please to perform the on
both SQL Server using Query Analyzer and check to see if the deadlock is
recorded in both SQL Server's log.
Create a simple deadlock in pubs in two Query Analyzer windows.
Window 1:
dbcc traceon(3605)
dbcc traceon(1204)
begin tran update authors set contract = contract
Window 2: begin tran update titles set ytd_sales = ytd_sales
Window 1: update titles set ytd_sales = ytd_sales
Window 2: update authors set contract = contract
It works on my side and I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello Michael,
Perfect advice. I am able to recreate locks using your
example and validate the they are being written to the
log. However, I'm was having issues getting DBCC
TRACESTATUS(-1) or DBCC TRACESTATUS(1204) to behave as
described. When I run it I get: "Trace option(s) not
enabled for this connection. Use 'DBCC TRACEON()'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator."
So I ran "DBCC TRACEON" and then aftter running that I
ran "DBCC TRACESTATUS(-1)" and I get "TraceFlag Status
-- --
1204 1" which is what I want. So, it seems that the
order needed is "DBCC TRACEON(1204)" then "DBCC TRACEON"
must be run before "DBCC TRACESTATUS(-1)" will list.
Thanks for your help. I've learned a bit.
Mike
>--Original Message--
>Hi Mike,
>Thanks for Linchi's help. DBCC TRACESTATUS(-1) displays
the status of all
>currently enabled trace flags by specifying a value of -1.
>Please make sure that you problem application can make
deadlock every time
>when you execute it. Here is a deadlock example, please
to perform the on
>both SQL Server using Query Analyzer and check to see if
the deadlock is
>recorded in both SQL Server's log.
>Create a simple deadlock in pubs in two Query Analyzer
windows.
>Window 1:
>dbcc traceon(3605)
>dbcc traceon(1204)
>begin tran update authors set contract = contract
>Window 2: begin tran update titles set ytd_sales =ytd_sales
>Window 1: update titles set ytd_sales = ytd_sales
>Window 2: update authors set contract = contract
>It works on my side and I am standing by for your
response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>
Saturday, February 25, 2012
DBREINDEX and Transaction Log
I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>|||DBREINDEX is transaction protected. If it failed, the you had an internal ro
llback. This means that
you cannot resume the process. What did you reindex? What indexes does the t
able have? In general,
if you have a clustered index, you need same amount of free space in the dat
abase as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amou
nt (roughly) of space
available for the transaction log (as well for each non-clustered index). On
e thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_
logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't t
ransaction protected).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...[vbc
ol=seagreen]
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used mo
re than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any change
s to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage
.
>[/vbcol]|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
more than 5 GB of Log[vbcol=seagreen]
changes to the DB table in the[vbcol=seagreen]
usage.[vbcol=seagreen]
>
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>|||DBREINDEX is transaction protected. If it failed, the you had an internal ro
llback. This means that
you cannot resume the process. What did you reindex? What indexes does the t
able have? In general,
if you have a clustered index, you need same amount of free space in the dat
abase as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amou
nt (roughly) of space
available for the transaction log (as well for each non-clustered index). On
e thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_
logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't t
ransaction protected).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...[vbc
ol=seagreen]
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used mo
re than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any change
s to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage
.
>[/vbcol]|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
more than 5 GB of Log[vbcol=seagreen]
changes to the DB table in the[vbcol=seagreen]
usage.[vbcol=seagreen]
>
DBREINDEX and Transaction Log
I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.
Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>
|||DBREINDEX is transaction protected. If it failed, the you had an internal rollback. This means that
you cannot resume the process. What did you reindex? What indexes does the table have? In general,
if you have a clustered index, you need same amount of free space in the database as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amount (roughly) of space
available for the transaction log (as well for each non-clustered index). One thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't transaction protected).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used more than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any changes to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage.
>
|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...[vbcol=seagreen]
more than 5 GB of Log[vbcol=seagreen]
changes to the DB table in the[vbcol=seagreen]
usage.
>
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.
Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>
|||DBREINDEX is transaction protected. If it failed, the you had an internal rollback. This means that
you cannot resume the process. What did you reindex? What indexes does the table have? In general,
if you have a clustered index, you need same amount of free space in the database as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amount (roughly) of space
available for the transaction log (as well for each non-clustered index). One thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't transaction protected).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used more than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any changes to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage.
>
|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...[vbcol=seagreen]
more than 5 GB of Log[vbcol=seagreen]
changes to the DB table in the[vbcol=seagreen]
usage.
>
DBREINDEX and Transaction Log
I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>|||DBREINDEX is transaction protected. If it failed, the you had an internal rollback. This means that
you cannot resume the process. What did you reindex? What indexes does the table have? In general,
if you have a clustered index, you need same amount of free space in the database as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amount (roughly) of space
available for the transaction log (as well for each non-clustered index). One thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't transaction protected).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used more than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any changes to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage.
>|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> >I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log
> >space. Suddenly it needed more free space and the DBREINDEX failed.
> >
> > Is it somehow possible to resume the process? There hasn't been any
changes to the DB table in the
> > mean time. If I can't do that then how could I estimate the log file
usage.
> >
>
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>|||DBREINDEX is transaction protected. If it failed, the you had an internal rollback. This means that
you cannot resume the process. What did you reindex? What indexes does the table have? In general,
if you have a clustered index, you need same amount of free space in the database as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amount (roughly) of space
available for the transaction log (as well for each non-clustered index). One thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't transaction protected).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used more than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any changes to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage.
>|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> >I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log
> >space. Suddenly it needed more free space and the DBREINDEX failed.
> >
> > Is it somehow possible to resume the process? There hasn't been any
changes to the DB table in the
> > mean time. If I can't do that then how could I estimate the log file
usage.
> >
>
Sunday, February 19, 2012
dbo prefix
When I try to do a remote query as follow
select * from server1.retail..customers
the query fails, but if ran the same query with dbo
select * from server1.retail.dbo.customers, then it works.
Why is this happening, I thought dbo. or .. was the same.
Thanks in advanceTom,
>> Why is this happening, I thought dbo. or .. was the same.
No.If you login to SQL Server using the login 'tom' and then you say select
* from server1.retail..customers, it looks for 'customers' which is created
by the objectowner 'tom'.Normally, all database objects should be created by
'dbo' in order to avoid this confusion.Even otherwise, its a good practice
to prefix the objectowner name explicitly as in select * from dbo.customers.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"tom" <tom@.hotmail.com> wrote in message
news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
> When I try to do a remote query as follow
> select * from server1.retail..customers
> the query fails, but if ran the same query with dbo
> select * from server1.retail.dbo.customers, then it works.
> Why is this happening, I thought dbo. or .. was the same.
> Thanks in advance
>|||I am sa on the server and all the objects are owned by dbo.
thanks for you help
>--Original Message--
>Tom,
>> Why is this happening, I thought dbo. or .. was the
same.
>No.If you login to SQL Server using the login 'tom' and
then you say select
>* from server1.retail..customers, it looks
for 'customers' which is created
>by the objectowner 'tom'.Normally, all database objects
should be created by
>'dbo' in order to avoid this confusion.Even otherwise,
its a good practice
>to prefix the objectowner name explicitly as in select *
from dbo.customers.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"tom" <tom@.hotmail.com> wrote in message
>news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
>> When I try to do a remote query as follow
>> select * from server1.retail..customers
>> the query fails, but if ran the same query with dbo
>> select * from server1.retail.dbo.customers, then it
works.
>> Why is this happening, I thought dbo. or .. was the
same.
>> Thanks in advance
>>
>
>.
>|||Tom,
Its because you are querying a remote server.Always use fully qualified
names when working with objects on linked servers including the object owner
name,in this case, 'dbo'.In linked servers, there is no support for implicit
resolution of .. to the dbo owner name for tables .
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"tom" <tom@.hotmail.com> wrote in message
news:011e01c36e49$c1123610$a301280a@.phx.gbl...
> I am sa on the server and all the objects are owned by dbo.
> thanks for you help
>
> >--Original Message--
> >Tom,
> >
> >> Why is this happening, I thought dbo. or .. was the
> same.
> >
> >No.If you login to SQL Server using the login 'tom' and
> then you say select
> >* from server1.retail..customers, it looks
> for 'customers' which is created
> >by the objectowner 'tom'.Normally, all database objects
> should be created by
> >'dbo' in order to avoid this confusion.Even otherwise,
> its a good practice
> >to prefix the objectowner name explicitly as in select *
> from dbo.customers.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"tom" <tom@.hotmail.com> wrote in message
> >news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
> >> When I try to do a remote query as follow
> >>
> >> select * from server1.retail..customers
> >>
> >> the query fails, but if ran the same query with dbo
> >>
> >> select * from server1.retail.dbo.customers, then it
> works.
> >>
> >> Why is this happening, I thought dbo. or .. was the
> same.
> >>
> >> Thanks in advance
> >>
> >>
> >
> >
> >.
> >
select * from server1.retail..customers
the query fails, but if ran the same query with dbo
select * from server1.retail.dbo.customers, then it works.
Why is this happening, I thought dbo. or .. was the same.
Thanks in advanceTom,
>> Why is this happening, I thought dbo. or .. was the same.
No.If you login to SQL Server using the login 'tom' and then you say select
* from server1.retail..customers, it looks for 'customers' which is created
by the objectowner 'tom'.Normally, all database objects should be created by
'dbo' in order to avoid this confusion.Even otherwise, its a good practice
to prefix the objectowner name explicitly as in select * from dbo.customers.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"tom" <tom@.hotmail.com> wrote in message
news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
> When I try to do a remote query as follow
> select * from server1.retail..customers
> the query fails, but if ran the same query with dbo
> select * from server1.retail.dbo.customers, then it works.
> Why is this happening, I thought dbo. or .. was the same.
> Thanks in advance
>|||I am sa on the server and all the objects are owned by dbo.
thanks for you help
>--Original Message--
>Tom,
>> Why is this happening, I thought dbo. or .. was the
same.
>No.If you login to SQL Server using the login 'tom' and
then you say select
>* from server1.retail..customers, it looks
for 'customers' which is created
>by the objectowner 'tom'.Normally, all database objects
should be created by
>'dbo' in order to avoid this confusion.Even otherwise,
its a good practice
>to prefix the objectowner name explicitly as in select *
from dbo.customers.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"tom" <tom@.hotmail.com> wrote in message
>news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
>> When I try to do a remote query as follow
>> select * from server1.retail..customers
>> the query fails, but if ran the same query with dbo
>> select * from server1.retail.dbo.customers, then it
works.
>> Why is this happening, I thought dbo. or .. was the
same.
>> Thanks in advance
>>
>
>.
>|||Tom,
Its because you are querying a remote server.Always use fully qualified
names when working with objects on linked servers including the object owner
name,in this case, 'dbo'.In linked servers, there is no support for implicit
resolution of .. to the dbo owner name for tables .
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"tom" <tom@.hotmail.com> wrote in message
news:011e01c36e49$c1123610$a301280a@.phx.gbl...
> I am sa on the server and all the objects are owned by dbo.
> thanks for you help
>
> >--Original Message--
> >Tom,
> >
> >> Why is this happening, I thought dbo. or .. was the
> same.
> >
> >No.If you login to SQL Server using the login 'tom' and
> then you say select
> >* from server1.retail..customers, it looks
> for 'customers' which is created
> >by the objectowner 'tom'.Normally, all database objects
> should be created by
> >'dbo' in order to avoid this confusion.Even otherwise,
> its a good practice
> >to prefix the objectowner name explicitly as in select *
> from dbo.customers.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"tom" <tom@.hotmail.com> wrote in message
> >news:0d9401c36e47$15161d10$a501280a@.phx.gbl...
> >> When I try to do a remote query as follow
> >>
> >> select * from server1.retail..customers
> >>
> >> the query fails, but if ran the same query with dbo
> >>
> >> select * from server1.retail.dbo.customers, then it
> works.
> >>
> >> Why is this happening, I thought dbo. or .. was the
> same.
> >>
> >> Thanks in advance
> >>
> >>
> >
> >
> >.
> >
Subscribe to:
Posts (Atom)