Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
Kumar
Kumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
sql
Showing posts with label scheduled. Show all posts
Showing posts with label scheduled. Show all posts
Tuesday, March 27, 2012
DBCC shrinkdatabase , transaction log shrink
Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 420
00]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file
.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 420
00]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file
.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
DBCC shrinkdatabase , transaction log shrink
Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 42000]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>
Sunday, March 11, 2012
DBCC INDEXDEFRAG aquires exclusive locks on a table
Hello!
I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular basis. I
have noticed that when indexing one of the non-clustered indexes on
particular table, process acquired exclusive(X) lock on the entire table
thus blocking any SELECTs etc.
When I was running my sample tests, I was observing IX lock on table in
question. I am not sure why exclusive lock was acquired by SQL Server job.
I was wondering if anybody experienced similar behavior.
Thanks,
Igorimarchenko wrote:
> Hello!
> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
> basis. I have noticed that when indexing one of the non-clustered
> indexes on particular table, process acquired exclusive(X) lock on
> the entire table thus blocking any SELECTs etc.
> When I was running my sample tests, I was observing IX lock on
> table in question. I am not sure why exclusive lock was acquired by
> SQL Server job. I was wondering if anybody experienced similar
> behavior.
> Thanks,
> Igor
IX is just an Intent lock. Form BOL: "Indicates the intention of a
transaction to modify some (but not all) resources lower in the
hierarchy by placing X locks on those individual resources. IX is a
superset of IS." Indexing places exclusive locks on a table, whereas
indexdefrag does not. Am I understanding your question correctly?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Sorry if I didn't make myself clear.
The problem is that IX lock is eventually being escalated into X lock. I
was under impression that DBCC INDEXDEFRAG issues series of short
transactions and never places X lock on entire table. Any thoughts are
greatly appreciated.
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23OdFlDc1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> imarchenko wrote:
>> Hello!
>> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
>> basis. I have noticed that when indexing one of the non-clustered
>> indexes on particular table, process acquired exclusive(X) lock on
>> the entire table thus blocking any SELECTs etc.
>> When I was running my sample tests, I was observing IX lock on
>> table in question. I am not sure why exclusive lock was acquired by
>> SQL Server job. I was wondering if anybody experienced similar
>> behavior.
>> Thanks,
>> Igor
> IX is just an Intent lock. Form BOL: "Indicates the intention of a
> transaction to modify some (but not all) resources lower in the hierarchy
> by placing X locks on those individual resources. IX is a superset of IS."
> Indexing places exclusive locks on a table, whereas indexdefrag does not.
> Am I understanding your question correctly?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||imarchenko wrote:
> David,
> Sorry if I didn't make myself clear.
> The problem is that IX lock is eventually being escalated into X
> lock. I was under impression that DBCC INDEXDEFRAG issues series of
> short transactions and never places X lock on entire table. Any
> thoughts are greatly appreciated.
My understanding is that DBCC INDEXDEFRAG is an online operation whereas
CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
short transactions to make its changes, those changes could require
varying levels of locks on the underlying table.
Maybe someone else can offer additional information on lock escalation
with the command.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
modification) lock that is incompatible with any other locks whereas DBCC
INDEXDEFRAG starts with X lock on row/page level (IX on table level) that is
being escalated into X table lock under certain circumstances (I suspect it
is table size/defragmantation level related).
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
> short transactions to make its changes, those changes could require
> varying levels of locks on the underlying table.
> Maybe someone else can offer additional information on lock escalation
> with the command.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there should
only be IX lock at the table level. I'll ask around and will post back if I get any reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that is
> incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page level
> (IX on table level) that is being escalated into X table lock under certain circumstances (I
> suspect it is table size/defragmantation level related).
> Igor
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is an
>> offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>|||Thanks a lor Tibor for looking into this. Please let me know if you will
need more details. Computer is running SQL Server 2000 SP4 on Windows 2003
EE.
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>level. AFAIK, there should only be IX lock at the table level. I'll ask
>around and will post back if I get any reply.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas DBCC
>> INDEXDEFRAG starts with X lock on row/page level (IX on table level) that
>> is being escalated into X table lock under certain circumstances (I
>> suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
>> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
>> short transactions to make its changes, those changes could require
>> varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||I got it confirmed that the escalation does occur, which is a bug. Here's a quote from my contact at
MS:
"Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL locks
and eventually escalate to a table lock. The KB article number is 907250 but
it hasn't been released yet. There is a hotfix available already."
So, I'd contact PSS on this, so you can get the hotfix if you find you need it. Or wait for the KB
to be released to you can read more details about it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will need more details.
> Computer is running SQL Server 2000 SP4 on Windows 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there
>>should only be IX lock at the table level. I'll ask around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that
>> is incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page
>> level (IX on table level) that is being escalated into X table lock under certain circumstances
>> (I suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is
>> an offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
locks on pages its moved - this eventually causes the next requested X page
lock to escalate to an X table lock.
A hotfix is available through PSS - it's not made it to the web yet. There
will also be a KB article but it hasn't made it out yet either. You should
be able to reference case SRX050805601805 with PSS and the fix will be
provided free of charge.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will
> need more details. Computer is running SQL Server 2000 SP4 on Windows
> 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG
>> using short transactions to make its changes, those changes could
>> require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||Tibor,
Thanks a lot for finding the answer so promptly!
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CXN4vo1FHA.2880@.TK2MSFTNGP12.phx.gbl...
>I got it confirmed that the escalation does occur, which is a bug. Here's a
>quote from my contact at MS:
> "Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL
> locks
> and eventually escalate to a table lock. The KB article number is 907250
> but
> it hasn't been released yet. There is a hotfix available already."
> So, I'd contact PSS on this, so you can get the hotfix if you find you
> need it. Or wait for the KB to be released to you can read more details
> about it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Thanks a lot!
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Could you please explain what NL is?
Thanks,
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>
I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular basis. I
have noticed that when indexing one of the non-clustered indexes on
particular table, process acquired exclusive(X) lock on the entire table
thus blocking any SELECTs etc.
When I was running my sample tests, I was observing IX lock on table in
question. I am not sure why exclusive lock was acquired by SQL Server job.
I was wondering if anybody experienced similar behavior.
Thanks,
Igorimarchenko wrote:
> Hello!
> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
> basis. I have noticed that when indexing one of the non-clustered
> indexes on particular table, process acquired exclusive(X) lock on
> the entire table thus blocking any SELECTs etc.
> When I was running my sample tests, I was observing IX lock on
> table in question. I am not sure why exclusive lock was acquired by
> SQL Server job. I was wondering if anybody experienced similar
> behavior.
> Thanks,
> Igor
IX is just an Intent lock. Form BOL: "Indicates the intention of a
transaction to modify some (but not all) resources lower in the
hierarchy by placing X locks on those individual resources. IX is a
superset of IS." Indexing places exclusive locks on a table, whereas
indexdefrag does not. Am I understanding your question correctly?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Sorry if I didn't make myself clear.
The problem is that IX lock is eventually being escalated into X lock. I
was under impression that DBCC INDEXDEFRAG issues series of short
transactions and never places X lock on entire table. Any thoughts are
greatly appreciated.
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23OdFlDc1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> imarchenko wrote:
>> Hello!
>> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
>> basis. I have noticed that when indexing one of the non-clustered
>> indexes on particular table, process acquired exclusive(X) lock on
>> the entire table thus blocking any SELECTs etc.
>> When I was running my sample tests, I was observing IX lock on
>> table in question. I am not sure why exclusive lock was acquired by
>> SQL Server job. I was wondering if anybody experienced similar
>> behavior.
>> Thanks,
>> Igor
> IX is just an Intent lock. Form BOL: "Indicates the intention of a
> transaction to modify some (but not all) resources lower in the hierarchy
> by placing X locks on those individual resources. IX is a superset of IS."
> Indexing places exclusive locks on a table, whereas indexdefrag does not.
> Am I understanding your question correctly?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||imarchenko wrote:
> David,
> Sorry if I didn't make myself clear.
> The problem is that IX lock is eventually being escalated into X
> lock. I was under impression that DBCC INDEXDEFRAG issues series of
> short transactions and never places X lock on entire table. Any
> thoughts are greatly appreciated.
My understanding is that DBCC INDEXDEFRAG is an online operation whereas
CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
short transactions to make its changes, those changes could require
varying levels of locks on the underlying table.
Maybe someone else can offer additional information on lock escalation
with the command.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
modification) lock that is incompatible with any other locks whereas DBCC
INDEXDEFRAG starts with X lock on row/page level (IX on table level) that is
being escalated into X table lock under certain circumstances (I suspect it
is table size/defragmantation level related).
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
> short transactions to make its changes, those changes could require
> varying levels of locks on the underlying table.
> Maybe someone else can offer additional information on lock escalation
> with the command.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there should
only be IX lock at the table level. I'll ask around and will post back if I get any reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that is
> incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page level
> (IX on table level) that is being escalated into X table lock under certain circumstances (I
> suspect it is table size/defragmantation level related).
> Igor
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is an
>> offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>|||Thanks a lor Tibor for looking into this. Please let me know if you will
need more details. Computer is running SQL Server 2000 SP4 on Windows 2003
EE.
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>level. AFAIK, there should only be IX lock at the table level. I'll ask
>around and will post back if I get any reply.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas DBCC
>> INDEXDEFRAG starts with X lock on row/page level (IX on table level) that
>> is being escalated into X table lock under certain circumstances (I
>> suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
>> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
>> short transactions to make its changes, those changes could require
>> varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||I got it confirmed that the escalation does occur, which is a bug. Here's a quote from my contact at
MS:
"Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL locks
and eventually escalate to a table lock. The KB article number is 907250 but
it hasn't been released yet. There is a hotfix available already."
So, I'd contact PSS on this, so you can get the hotfix if you find you need it. Or wait for the KB
to be released to you can read more details about it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will need more details.
> Computer is running SQL Server 2000 SP4 on Windows 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there
>>should only be IX lock at the table level. I'll ask around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that
>> is incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page
>> level (IX on table level) that is being escalated into X table lock under certain circumstances
>> (I suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is
>> an offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
locks on pages its moved - this eventually causes the next requested X page
lock to escalate to an X table lock.
A hotfix is available through PSS - it's not made it to the web yet. There
will also be a KB article but it hasn't made it out yet either. You should
be able to reference case SRX050805601805 with PSS and the fix will be
provided free of charge.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will
> need more details. Computer is running SQL Server 2000 SP4 on Windows
> 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG
>> using short transactions to make its changes, those changes could
>> require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||Tibor,
Thanks a lot for finding the answer so promptly!
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CXN4vo1FHA.2880@.TK2MSFTNGP12.phx.gbl...
>I got it confirmed that the escalation does occur, which is a bug. Here's a
>quote from my contact at MS:
> "Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL
> locks
> and eventually escalate to a table lock. The KB article number is 907250
> but
> it hasn't been released yet. There is a hotfix available already."
> So, I'd contact PSS on this, so you can get the hotfix if you find you
> need it. Or wait for the KB to be released to you can read more details
> about it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Thanks a lot!
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Could you please explain what NL is?
Thanks,
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>
Saturday, February 25, 2012
DBCC dbreindex and Backup Size
We have two one-time scheduled jobs running the DBCC below, this happened to
be Mon and Tuesday after the full backup on Sunday:
DBCC dbreindex (My Table,'',90)
The table being DBCC'ed are rather big, can be 30GB in total.
Our backup size increases in those two days. Our differential backups on Mon
and Tuesdays are usually significantly lower. I wonder someone can relate
this to DBCC DBReindex or any other possibilites.
Thanks
TO
> Our backup size increases in those two days.
Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
after the operation?
If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
with the reindex).
If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
been logged.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>
|||There are regular large amount of inserts (because of ETL process)
***everyday***, no noticeable different for those days though.
We have full db backup on Sunday and Diff db backup daily.
Should I not run the DBCC REINDEX with fill factor of 90 a day after full
backup?
>You have shuffled (= modified) a lot of data with the reindex).
Can you further explain? It sounds like this may be the answer.
The database is 58 GB and the diff for the day is 55 GB. Same day last week
was 26.961 GB.
BTW, 27 GB diff is way too big of size to me. What strategy I can use to
save our backup size, without jeopardizing or compromising our recoverability
(longer restoring is acceptable)?
Thanks.
"Tibor Karaszi" wrote:
> Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
> after the operation?
> If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
> with the reindex).
> If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
> been logged.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
>
|||That is a logged operation, so it will expand your transaction log. Take a
peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
your scenario.
"C TO" wrote:
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>
|||Forgot to mention that the recovery mode is "Simple", my bad. We have no
logged operation.
Is there any DBCC + Backup strategy can be implement?
Thanks.
"Dennis Forbes" wrote:
[vbcol=seagreen]
> That is a logged operation, so it will expand your transaction log. Take a
> peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> your scenario.
> "C TO" wrote:
|||Ahh, okay.
The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
the reindex routines before your full backup.
"C TO" wrote:
[vbcol=seagreen]
> Forgot to mention that the recovery mode is "Simple", my bad. We have no
> logged operation.
> Is there any DBCC + Backup strategy can be implement?
> Thanks.
> "Dennis Forbes" wrote:
|||>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
Yes, the index is rebuilt, hence all the data in the table has been modified (assuming there's a
clustered index on the table). All that data is included in the diff backup.
Can you be more selective with your rebuilds? Also, perhaps DBCC INDEXDEFRAG will suit you better?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:91C42295-D1E9-4545-990D-43AEDF876184@.microsoft.com...[vbcol=seagreen]
> There are regular large amount of inserts (because of ETL process)
> ***everyday***, no noticeable different for those days though.
> We have full db backup on Sunday and Diff db backup daily.
> Should I not run the DBCC REINDEX with fill factor of 90 a day after full
> backup?
> Can you further explain? It sounds like this may be the answer.
>
> The database is 58 GB and the diff for the day is 55 GB. Same day last week
> was 26.961 GB.
> BTW, 27 GB diff is way too big of size to me. What strategy I can use to
> save our backup size, without jeopardizing or compromising our recoverability
> (longer restoring is acceptable)?
> Thanks.
>
>
> "Tibor Karaszi" wrote:
|||Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
was reindexing the tables with just about one third of the 59 GB database
size. Assuming this is true, the marked pages are 28GB. Are these all index
pages?
"Dennis Forbes" wrote:
[vbcol=seagreen]
> Ahh, okay.
> The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> the reindex routines before your full backup.
> "C TO" wrote:
|||You probably have a clustered index on the table, and a clustered index
contains the entirity of the data (a clustered index is the data), so in that
case the answer would be yes.
"C TO" wrote:
[vbcol=seagreen]
> Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
> was reindexing the tables with just about one third of the 59 GB database
> size. Assuming this is true, the marked pages are 28GB. Are these all index
> pages?
>
> "Dennis Forbes" wrote:
be Mon and Tuesday after the full backup on Sunday:
DBCC dbreindex (My Table,'',90)
The table being DBCC'ed are rather big, can be 30GB in total.
Our backup size increases in those two days. Our differential backups on Mon
and Tuesdays are usually significantly lower. I wonder someone can relate
this to DBCC DBReindex or any other possibilites.
Thanks
TO
> Our backup size increases in those two days.
Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
after the operation?
If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
with the reindex).
If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
been logged.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>
|||There are regular large amount of inserts (because of ETL process)
***everyday***, no noticeable different for those days though.
We have full db backup on Sunday and Diff db backup daily.
Should I not run the DBCC REINDEX with fill factor of 90 a day after full
backup?
>You have shuffled (= modified) a lot of data with the reindex).
Can you further explain? It sounds like this may be the answer.
The database is 58 GB and the diff for the day is 55 GB. Same day last week
was 26.961 GB.
BTW, 27 GB diff is way too big of size to me. What strategy I can use to
save our backup size, without jeopardizing or compromising our recoverability
(longer restoring is acceptable)?
Thanks.
"Tibor Karaszi" wrote:
> Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
> after the operation?
> If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
> with the reindex).
> If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
> been logged.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
>
|||That is a logged operation, so it will expand your transaction log. Take a
peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
your scenario.
"C TO" wrote:
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>
|||Forgot to mention that the recovery mode is "Simple", my bad. We have no
logged operation.
Is there any DBCC + Backup strategy can be implement?
Thanks.
"Dennis Forbes" wrote:
[vbcol=seagreen]
> That is a logged operation, so it will expand your transaction log. Take a
> peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> your scenario.
> "C TO" wrote:
|||Ahh, okay.
The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
the reindex routines before your full backup.
"C TO" wrote:
[vbcol=seagreen]
> Forgot to mention that the recovery mode is "Simple", my bad. We have no
> logged operation.
> Is there any DBCC + Backup strategy can be implement?
> Thanks.
> "Dennis Forbes" wrote:
|||>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
Yes, the index is rebuilt, hence all the data in the table has been modified (assuming there's a
clustered index on the table). All that data is included in the diff backup.
Can you be more selective with your rebuilds? Also, perhaps DBCC INDEXDEFRAG will suit you better?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:91C42295-D1E9-4545-990D-43AEDF876184@.microsoft.com...[vbcol=seagreen]
> There are regular large amount of inserts (because of ETL process)
> ***everyday***, no noticeable different for those days though.
> We have full db backup on Sunday and Diff db backup daily.
> Should I not run the DBCC REINDEX with fill factor of 90 a day after full
> backup?
> Can you further explain? It sounds like this may be the answer.
>
> The database is 58 GB and the diff for the day is 55 GB. Same day last week
> was 26.961 GB.
> BTW, 27 GB diff is way too big of size to me. What strategy I can use to
> save our backup size, without jeopardizing or compromising our recoverability
> (longer restoring is acceptable)?
> Thanks.
>
>
> "Tibor Karaszi" wrote:
|||Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
was reindexing the tables with just about one third of the 59 GB database
size. Assuming this is true, the marked pages are 28GB. Are these all index
pages?
"Dennis Forbes" wrote:
[vbcol=seagreen]
> Ahh, okay.
> The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> the reindex routines before your full backup.
> "C TO" wrote:
|||You probably have a clustered index on the table, and a clustered index
contains the entirity of the data (a clustered index is the data), so in that
case the answer would be yes.
"C TO" wrote:
[vbcol=seagreen]
> Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
> was reindexing the tables with just about one third of the 59 GB database
> size. Assuming this is true, the marked pages are 28GB. Are these all index
> pages?
>
> "Dennis Forbes" wrote:
DBCC dbreindex and Backup Size
We have two one-time scheduled jobs running the DBCC below, this happened to
be Mon and Tuesday after the full backup on Sunday:
DBCC dbreindex (My Table,'',90)
The table being DBCC'ed are rather big, can be 30GB in total.
Our backup size increases in those two days. Our differential backups on Mon
and Tuesdays are usually significantly lower. I wonder someone can relate
this to DBCC DBReindex or any other possibilites.
Thanks
TO> Our backup size increases in those two days.
Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
after the operation?
If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
with the reindex).
If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
been logged.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||There are regular large amount of inserts (because of ETL process)
***everyday***, no noticeable different for those days though.
We have full db backup on Sunday and Diff db backup daily.
Should I not run the DBCC REINDEX with fill factor of 90 a day after full
backup?
>You have shuffled (= modified) a lot of data with the reindex).
Can you further explain? It sounds like this may be the answer.
The database is 58 GB and the diff for the day is 55 GB. Same day last week
was 26.961 GB.
BTW, 27 GB diff is way too big of size to me. What strategy I can use to
save our backup size, without jeopardizing or compromising our recoverability
(longer restoring is acceptable)?
Thanks.
"Tibor Karaszi" wrote:
> > Our backup size increases in those two days.
> Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
> after the operation?
> If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
> with the reindex).
> If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
> been logged.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> >
> > We have two one-time scheduled jobs running the DBCC below, this happened to
> > be Mon and Tuesday after the full backup on Sunday:
> >
> > DBCC dbreindex (My Table,'',90)
> >
> > The table being DBCC'ed are rather big, can be 30GB in total.
> >
> > Our backup size increases in those two days. Our differential backups on Mon
> > and Tuesdays are usually significantly lower. I wonder someone can relate
> > this to DBCC DBReindex or any other possibilites.
> >
> > Thanks
> > TO
> >
> >
>|||That is a logged operation, so it will expand your transaction log. Take a
peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
your scenario.
"C TO" wrote:
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||Forgot to mention that the recovery mode is "Simple", my bad. We have no
logged operation.
Is there any DBCC + Backup strategy can be implement?
Thanks.
"Dennis Forbes" wrote:
> That is a logged operation, so it will expand your transaction log. Take a
> peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> your scenario.
> "C TO" wrote:
> >
> > We have two one-time scheduled jobs running the DBCC below, this happened to
> > be Mon and Tuesday after the full backup on Sunday:
> >
> > DBCC dbreindex (My Table,'',90)
> >
> > The table being DBCC'ed are rather big, can be 30GB in total.
> >
> > Our backup size increases in those two days. Our differential backups on Mon
> > and Tuesdays are usually significantly lower. I wonder someone can relate
> > this to DBCC DBReindex or any other possibilites.
> >
> > Thanks
> > TO
> >
> >|||Ahh, okay.
The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
the reindex routines before your full backup.
"C TO" wrote:
> Forgot to mention that the recovery mode is "Simple", my bad. We have no
> logged operation.
> Is there any DBCC + Backup strategy can be implement?
> Thanks.
> "Dennis Forbes" wrote:
> > That is a logged operation, so it will expand your transaction log. Take a
> > peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> > your scenario.
> >
> > "C TO" wrote:
> >
> > >
> > > We have two one-time scheduled jobs running the DBCC below, this happened to
> > > be Mon and Tuesday after the full backup on Sunday:
> > >
> > > DBCC dbreindex (My Table,'',90)
> > >
> > > The table being DBCC'ed are rather big, can be 30GB in total.
> > >
> > > Our backup size increases in those two days. Our differential backups on Mon
> > > and Tuesdays are usually significantly lower. I wonder someone can relate
> > > this to DBCC DBReindex or any other possibilites.
> > >
> > > Thanks
> > > TO
> > >
> > >|||>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
Yes, the index is rebuilt, hence all the data in the table has been modified (assuming there's a
clustered index on the table). All that data is included in the diff backup.
Can you be more selective with your rebuilds? Also, perhaps DBCC INDEXDEFRAG will suit you better?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:91C42295-D1E9-4545-990D-43AEDF876184@.microsoft.com...
> There are regular large amount of inserts (because of ETL process)
> ***everyday***, no noticeable different for those days though.
> We have full db backup on Sunday and Diff db backup daily.
> Should I not run the DBCC REINDEX with fill factor of 90 a day after full
> backup?
>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
>
> The database is 58 GB and the diff for the day is 55 GB. Same day last week
> was 26.961 GB.
> BTW, 27 GB diff is way too big of size to me. What strategy I can use to
> save our backup size, without jeopardizing or compromising our recoverability
> (longer restoring is acceptable)?
> Thanks.
>
>
> "Tibor Karaszi" wrote:
>> > Our backup size increases in those two days.
>> Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before
>> of
>> after the operation?
>> If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of
>> data
>> with the reindex).
>> If it is a log backup, then the same goes, you have modified a lot of data. These modifications
>> have
>> been logged.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "C TO" <CTO@.discussions.microsoft.com> wrote in message
>> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
>> >
>> > We have two one-time scheduled jobs running the DBCC below, this happened to
>> > be Mon and Tuesday after the full backup on Sunday:
>> >
>> > DBCC dbreindex (My Table,'',90)
>> >
>> > The table being DBCC'ed are rather big, can be 30GB in total.
>> >
>> > Our backup size increases in those two days. Our differential backups on Mon
>> > and Tuesdays are usually significantly lower. I wonder someone can relate
>> > this to DBCC DBReindex or any other possibilites.
>> >
>> > Thanks
>> > TO
>> >
>> >
>>|||Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
was reindexing the tables with just about one third of the 59 GB database
size. Assuming this is true, the marked pages are 28GB. Are these all index
pages?
"Dennis Forbes" wrote:
> Ahh, okay.
> The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> the reindex routines before your full backup.
> "C TO" wrote:
> > Forgot to mention that the recovery mode is "Simple", my bad. We have no
> > logged operation.
> >
> > Is there any DBCC + Backup strategy can be implement?
> >
> > Thanks.
> >
> > "Dennis Forbes" wrote:
> >
> > > That is a logged operation, so it will expand your transaction log. Take a
> > > peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> > > your scenario.
> > >
> > > "C TO" wrote:
> > >
> > > >
> > > > We have two one-time scheduled jobs running the DBCC below, this happened to
> > > > be Mon and Tuesday after the full backup on Sunday:
> > > >
> > > > DBCC dbreindex (My Table,'',90)
> > > >
> > > > The table being DBCC'ed are rather big, can be 30GB in total.
> > > >
> > > > Our backup size increases in those two days. Our differential backups on Mon
> > > > and Tuesdays are usually significantly lower. I wonder someone can relate
> > > > this to DBCC DBReindex or any other possibilites.
> > > >
> > > > Thanks
> > > > TO
> > > >
> > > >|||You probably have a clustered index on the table, and a clustered index
contains the entirity of the data (a clustered index is the data), so in that
case the answer would be yes.
"C TO" wrote:
> Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
> was reindexing the tables with just about one third of the 59 GB database
> size. Assuming this is true, the marked pages are 28GB. Are these all index
> pages?
>
> "Dennis Forbes" wrote:
> > Ahh, okay.
> >
> > The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> > the reindex routines before your full backup.
> >
> > "C TO" wrote:
> >
> > > Forgot to mention that the recovery mode is "Simple", my bad. We have no
> > > logged operation.
> > >
> > > Is there any DBCC + Backup strategy can be implement?
> > >
> > > Thanks.
> > >
> > > "Dennis Forbes" wrote:
> > >
> > > > That is a logged operation, so it will expand your transaction log. Take a
> > > > peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> > > > your scenario.
> > > >
> > > > "C TO" wrote:
> > > >
> > > > >
> > > > > We have two one-time scheduled jobs running the DBCC below, this happened to
> > > > > be Mon and Tuesday after the full backup on Sunday:
> > > > >
> > > > > DBCC dbreindex (My Table,'',90)
> > > > >
> > > > > The table being DBCC'ed are rather big, can be 30GB in total.
> > > > >
> > > > > Our backup size increases in those two days. Our differential backups on Mon
> > > > > and Tuesdays are usually significantly lower. I wonder someone can relate
> > > > > this to DBCC DBReindex or any other possibilites.
> > > > >
> > > > > Thanks
> > > > > TO
> > > > >
> > > > >
be Mon and Tuesday after the full backup on Sunday:
DBCC dbreindex (My Table,'',90)
The table being DBCC'ed are rather big, can be 30GB in total.
Our backup size increases in those two days. Our differential backups on Mon
and Tuesdays are usually significantly lower. I wonder someone can relate
this to DBCC DBReindex or any other possibilites.
Thanks
TO> Our backup size increases in those two days.
Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
after the operation?
If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
with the reindex).
If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
been logged.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||There are regular large amount of inserts (because of ETL process)
***everyday***, no noticeable different for those days though.
We have full db backup on Sunday and Diff db backup daily.
Should I not run the DBCC REINDEX with fill factor of 90 a day after full
backup?
>You have shuffled (= modified) a lot of data with the reindex).
Can you further explain? It sounds like this may be the answer.
The database is 58 GB and the diff for the day is 55 GB. Same day last week
was 26.961 GB.
BTW, 27 GB diff is way too big of size to me. What strategy I can use to
save our backup size, without jeopardizing or compromising our recoverability
(longer restoring is acceptable)?
Thanks.
"Tibor Karaszi" wrote:
> > Our backup size increases in those two days.
> Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before of
> after the operation?
> If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of data
> with the reindex).
> If it is a log backup, then the same goes, you have modified a lot of data. These modifications have
> been logged.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> >
> > We have two one-time scheduled jobs running the DBCC below, this happened to
> > be Mon and Tuesday after the full backup on Sunday:
> >
> > DBCC dbreindex (My Table,'',90)
> >
> > The table being DBCC'ed are rather big, can be 30GB in total.
> >
> > Our backup size increases in those two days. Our differential backups on Mon
> > and Tuesdays are usually significantly lower. I wonder someone can relate
> > this to DBCC DBReindex or any other possibilites.
> >
> > Thanks
> > TO
> >
> >
>|||That is a logged operation, so it will expand your transaction log. Take a
peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
your scenario.
"C TO" wrote:
> We have two one-time scheduled jobs running the DBCC below, this happened to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on Mon
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||Forgot to mention that the recovery mode is "Simple", my bad. We have no
logged operation.
Is there any DBCC + Backup strategy can be implement?
Thanks.
"Dennis Forbes" wrote:
> That is a logged operation, so it will expand your transaction log. Take a
> peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> your scenario.
> "C TO" wrote:
> >
> > We have two one-time scheduled jobs running the DBCC below, this happened to
> > be Mon and Tuesday after the full backup on Sunday:
> >
> > DBCC dbreindex (My Table,'',90)
> >
> > The table being DBCC'ed are rather big, can be 30GB in total.
> >
> > Our backup size increases in those two days. Our differential backups on Mon
> > and Tuesdays are usually significantly lower. I wonder someone can relate
> > this to DBCC DBReindex or any other possibilites.
> >
> > Thanks
> > TO
> >
> >|||Ahh, okay.
The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
the reindex routines before your full backup.
"C TO" wrote:
> Forgot to mention that the recovery mode is "Simple", my bad. We have no
> logged operation.
> Is there any DBCC + Backup strategy can be implement?
> Thanks.
> "Dennis Forbes" wrote:
> > That is a logged operation, so it will expand your transaction log. Take a
> > peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> > your scenario.
> >
> > "C TO" wrote:
> >
> > >
> > > We have two one-time scheduled jobs running the DBCC below, this happened to
> > > be Mon and Tuesday after the full backup on Sunday:
> > >
> > > DBCC dbreindex (My Table,'',90)
> > >
> > > The table being DBCC'ed are rather big, can be 30GB in total.
> > >
> > > Our backup size increases in those two days. Our differential backups on Mon
> > > and Tuesdays are usually significantly lower. I wonder someone can relate
> > > this to DBCC DBReindex or any other possibilites.
> > >
> > > Thanks
> > > TO
> > >
> > >|||>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
Yes, the index is rebuilt, hence all the data in the table has been modified (assuming there's a
clustered index on the table). All that data is included in the diff backup.
Can you be more selective with your rebuilds? Also, perhaps DBCC INDEXDEFRAG will suit you better?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:91C42295-D1E9-4545-990D-43AEDF876184@.microsoft.com...
> There are regular large amount of inserts (because of ETL process)
> ***everyday***, no noticeable different for those days though.
> We have full db backup on Sunday and Diff db backup daily.
> Should I not run the DBCC REINDEX with fill factor of 90 a day after full
> backup?
>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
>
> The database is 58 GB and the diff for the day is 55 GB. Same day last week
> was 26.961 GB.
> BTW, 27 GB diff is way too big of size to me. What strategy I can use to
> save our backup size, without jeopardizing or compromising our recoverability
> (longer restoring is acceptable)?
> Thanks.
>
>
> "Tibor Karaszi" wrote:
>> > Our backup size increases in those two days.
>> Can you elaborate further? What type of backup? Database, diff or log? Is the backup large before
>> of
>> after the operation?
>> If a diff backup is larger than usual it is expected. You have shuffled (= modified) a lot of
>> data
>> with the reindex).
>> If it is a log backup, then the same goes, you have modified a lot of data. These modifications
>> have
>> been logged.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "C TO" <CTO@.discussions.microsoft.com> wrote in message
>> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
>> >
>> > We have two one-time scheduled jobs running the DBCC below, this happened to
>> > be Mon and Tuesday after the full backup on Sunday:
>> >
>> > DBCC dbreindex (My Table,'',90)
>> >
>> > The table being DBCC'ed are rather big, can be 30GB in total.
>> >
>> > Our backup size increases in those two days. Our differential backups on Mon
>> > and Tuesdays are usually significantly lower. I wonder someone can relate
>> > this to DBCC DBReindex or any other possibilites.
>> >
>> > Thanks
>> > TO
>> >
>> >
>>|||Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
was reindexing the tables with just about one third of the 59 GB database
size. Assuming this is true, the marked pages are 28GB. Are these all index
pages?
"Dennis Forbes" wrote:
> Ahh, okay.
> The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> the reindex routines before your full backup.
> "C TO" wrote:
> > Forgot to mention that the recovery mode is "Simple", my bad. We have no
> > logged operation.
> >
> > Is there any DBCC + Backup strategy can be implement?
> >
> > Thanks.
> >
> > "Dennis Forbes" wrote:
> >
> > > That is a logged operation, so it will expand your transaction log. Take a
> > > peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> > > your scenario.
> > >
> > > "C TO" wrote:
> > >
> > > >
> > > > We have two one-time scheduled jobs running the DBCC below, this happened to
> > > > be Mon and Tuesday after the full backup on Sunday:
> > > >
> > > > DBCC dbreindex (My Table,'',90)
> > > >
> > > > The table being DBCC'ed are rather big, can be 30GB in total.
> > > >
> > > > Our backup size increases in those two days. Our differential backups on Mon
> > > > and Tuesdays are usually significantly lower. I wonder someone can relate
> > > > this to DBCC DBReindex or any other possibilites.
> > > >
> > > > Thanks
> > > > TO
> > > >
> > > >|||You probably have a clustered index on the table, and a clustered index
contains the entirity of the data (a clustered index is the data), so in that
case the answer would be yes.
"C TO" wrote:
> Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
> was reindexing the tables with just about one third of the 59 GB database
> size. Assuming this is true, the marked pages are 28GB. Are these all index
> pages?
>
> "Dennis Forbes" wrote:
> > Ahh, okay.
> >
> > The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> > the reindex routines before your full backup.
> >
> > "C TO" wrote:
> >
> > > Forgot to mention that the recovery mode is "Simple", my bad. We have no
> > > logged operation.
> > >
> > > Is there any DBCC + Backup strategy can be implement?
> > >
> > > Thanks.
> > >
> > > "Dennis Forbes" wrote:
> > >
> > > > That is a logged operation, so it will expand your transaction log. Take a
> > > > peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
> > > > your scenario.
> > > >
> > > > "C TO" wrote:
> > > >
> > > > >
> > > > > We have two one-time scheduled jobs running the DBCC below, this happened to
> > > > > be Mon and Tuesday after the full backup on Sunday:
> > > > >
> > > > > DBCC dbreindex (My Table,'',90)
> > > > >
> > > > > The table being DBCC'ed are rather big, can be 30GB in total.
> > > > >
> > > > > Our backup size increases in those two days. Our differential backups on Mon
> > > > > and Tuesdays are usually significantly lower. I wonder someone can relate
> > > > > this to DBCC DBReindex or any other possibilites.
> > > > >
> > > > > Thanks
> > > > > TO
> > > > >
> > > > >
DBCC dbreindex and Backup Size
We have two one-time scheduled jobs running the DBCC below, this happened to
be Mon and Tuesday after the full backup on Sunday:
DBCC dbreindex (My Table,'',90)
The table being DBCC'ed are rather big, can be 30GB in total.
Our backup size increases in those two days. Our differential backups on Mon
and Tuesdays are usually significantly lower. I wonder someone can relate
this to DBCC DBReindex or any other possibilites.
Thanks
TO> Our backup size increases in those two days.
Can you elaborate further? What type of backup? Database, diff or log? Is th
e backup large before of
after the operation?
If a diff backup is larger than usual it is expected. You have shuffled (= m
odified) a lot of data
with the reindex).
If it is a log backup, then the same goes, you have modified a lot of data.
These modifications have
been logged.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> We have two one-time scheduled jobs running the DBCC below, this happened
to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on M
on
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||There are regular large amount of inserts (because of ETL process)
***everyday***, no noticeable different for those days though.
We have full db backup on Sunday and Diff db backup daily.
Should I not run the DBCC REINDEX with fill factor of 90 a day after full
backup?
>You have shuffled (= modified) a lot of data with the reindex).
Can you further explain? It sounds like this may be the answer.
The database is 58 GB and the diff for the day is 55 GB. Same day last week
was 26.961 GB.
BTW, 27 GB diff is way too big of size to me. What strategy I can use to
save our backup size, without jeopardizing or compromising our recoverabilit
y
(longer restoring is acceptable)?
Thanks.
"Tibor Karaszi" wrote:
> Can you elaborate further? What type of backup? Database, diff or log? Is
the backup large before of
> after the operation?
> If a diff backup is larger than usual it is expected. You have shuffled (=
modified) a lot of data
> with the reindex).
> If it is a log backup, then the same goes, you have modified a lot of data
. These modifications have
> been logged.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
>|||That is a logged operation, so it will expand your transaction log. Take a
peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
your scenario.
"C TO" wrote:
> We have two one-time scheduled jobs running the DBCC below, this happened
to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on M
on
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||Forgot to mention that the recovery mode is "Simple", my bad. We have no
logged operation.
Is there any DBCC + Backup strategy can be implement?
Thanks.
"Dennis Forbes" wrote:
[vbcol=seagreen]
> That is a logged operation, so it will expand your transaction log. Take a
> peek at the BULK_LOGGED recovery model to see if perhaps it makes sense fo
r
> your scenario.
> "C TO" wrote:
>|||Ahh, okay.
The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
the reindex routines before your full backup.
"C TO" wrote:
[vbcol=seagreen]
> Forgot to mention that the recovery mode is "Simple", my bad. We have no
> logged operation.
> Is there any DBCC + Backup strategy can be implement?
> Thanks.
> "Dennis Forbes" wrote:
>|||>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
Yes, the index is rebuilt, hence all the data in the table has been modified
(assuming there's a
clustered index on the table). All that data is included in the diff backup.
Can you be more selective with your rebuilds? Also, perhaps DBCC INDEXDEFRAG
will suit you better?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:91C42295-D1E9-4545-990D-43AEDF876184@.microsoft.com...[vbcol=seagreen]
> There are regular large amount of inserts (because of ETL process)
> ***everyday***, no noticeable different for those days though.
> We have full db backup on Sunday and Diff db backup daily.
> Should I not run the DBCC REINDEX with fill factor of 90 a day after full
> backup?
>
> Can you further explain? It sounds like this may be the answer.
>
> The database is 58 GB and the diff for the day is 55 GB. Same day last wee
k
> was 26.961 GB.
> BTW, 27 GB diff is way too big of size to me. What strategy I can use to
> save our backup size, without jeopardizing or compromising our recoverabil
ity
> (longer restoring is acceptable)?
> Thanks.
>
>
> "Tibor Karaszi" wrote:
>|||Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
was reindexing the tables with just about one third of the 59 GB database
size. Assuming this is true, the marked pages are 28GB. Are these all index
pages?
"Dennis Forbes" wrote:
[vbcol=seagreen]
> Ahh, okay.
> The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> the reindex routines before your full backup.
> "C TO" wrote:
>|||You probably have a clustered index on the table, and a clustered index
contains the entirity of the data (a clustered index is the data), so in tha
t
case the answer would be yes.
"C TO" wrote:
[vbcol=seagreen]
> Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX j
ob
> was reindexing the tables with just about one third of the 59 GB database
> size. Assuming this is true, the marked pages are 28GB. Are these all inde
x
> pages?
>
> "Dennis Forbes" wrote:
>
be Mon and Tuesday after the full backup on Sunday:
DBCC dbreindex (My Table,'',90)
The table being DBCC'ed are rather big, can be 30GB in total.
Our backup size increases in those two days. Our differential backups on Mon
and Tuesdays are usually significantly lower. I wonder someone can relate
this to DBCC DBReindex or any other possibilites.
Thanks
TO> Our backup size increases in those two days.
Can you elaborate further? What type of backup? Database, diff or log? Is th
e backup large before of
after the operation?
If a diff backup is larger than usual it is expected. You have shuffled (= m
odified) a lot of data
with the reindex).
If it is a log backup, then the same goes, you have modified a lot of data.
These modifications have
been logged.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
> We have two one-time scheduled jobs running the DBCC below, this happened
to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on M
on
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||There are regular large amount of inserts (because of ETL process)
***everyday***, no noticeable different for those days though.
We have full db backup on Sunday and Diff db backup daily.
Should I not run the DBCC REINDEX with fill factor of 90 a day after full
backup?
>You have shuffled (= modified) a lot of data with the reindex).
Can you further explain? It sounds like this may be the answer.
The database is 58 GB and the diff for the day is 55 GB. Same day last week
was 26.961 GB.
BTW, 27 GB diff is way too big of size to me. What strategy I can use to
save our backup size, without jeopardizing or compromising our recoverabilit
y
(longer restoring is acceptable)?
Thanks.
"Tibor Karaszi" wrote:
> Can you elaborate further? What type of backup? Database, diff or log? Is
the backup large before of
> after the operation?
> If a diff backup is larger than usual it is expected. You have shuffled (=
modified) a lot of data
> with the reindex).
> If it is a log backup, then the same goes, you have modified a lot of data
. These modifications have
> been logged.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:86C5C2CC-F1E8-4148-88D4-EADFC2599D05@.microsoft.com...
>|||That is a logged operation, so it will expand your transaction log. Take a
peek at the BULK_LOGGED recovery model to see if perhaps it makes sense for
your scenario.
"C TO" wrote:
> We have two one-time scheduled jobs running the DBCC below, this happened
to
> be Mon and Tuesday after the full backup on Sunday:
> DBCC dbreindex (My Table,'',90)
> The table being DBCC'ed are rather big, can be 30GB in total.
> Our backup size increases in those two days. Our differential backups on M
on
> and Tuesdays are usually significantly lower. I wonder someone can relate
> this to DBCC DBReindex or any other possibilites.
> Thanks
> TO
>|||Forgot to mention that the recovery mode is "Simple", my bad. We have no
logged operation.
Is there any DBCC + Backup strategy can be implement?
Thanks.
"Dennis Forbes" wrote:
[vbcol=seagreen]
> That is a logged operation, so it will expand your transaction log. Take a
> peek at the BULK_LOGGED recovery model to see if perhaps it makes sense fo
r
> your scenario.
> "C TO" wrote:
>|||Ahh, okay.
The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
the reindex routines before your full backup.
"C TO" wrote:
[vbcol=seagreen]
> Forgot to mention that the recovery mode is "Simple", my bad. We have no
> logged operation.
> Is there any DBCC + Backup strategy can be implement?
> Thanks.
> "Dennis Forbes" wrote:
>|||>>You have shuffled (= modified) a lot of data with the reindex).
> Can you further explain? It sounds like this may be the answer.
Yes, the index is rebuilt, hence all the data in the table has been modified
(assuming there's a
clustered index on the table). All that data is included in the diff backup.
Can you be more selective with your rebuilds? Also, perhaps DBCC INDEXDEFRAG
will suit you better?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:91C42295-D1E9-4545-990D-43AEDF876184@.microsoft.com...[vbcol=seagreen]
> There are regular large amount of inserts (because of ETL process)
> ***everyday***, no noticeable different for those days though.
> We have full db backup on Sunday and Diff db backup daily.
> Should I not run the DBCC REINDEX with fill factor of 90 a day after full
> backup?
>
> Can you further explain? It sounds like this may be the answer.
>
> The database is 58 GB and the diff for the day is 55 GB. Same day last wee
k
> was 26.961 GB.
> BTW, 27 GB diff is way too big of size to me. What strategy I can use to
> save our backup size, without jeopardizing or compromising our recoverabil
ity
> (longer restoring is acceptable)?
> Thanks.
>
>
> "Tibor Karaszi" wrote:
>|||Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX job
was reindexing the tables with just about one third of the 59 GB database
size. Assuming this is true, the marked pages are 28GB. Are these all index
pages?
"Dennis Forbes" wrote:
[vbcol=seagreen]
> Ahh, okay.
> The DBCC DBREINDEX is marking the pages as changed. Perhaps you should run
> the reindex routines before your full backup.
> "C TO" wrote:
>|||You probably have a clustered index on the table, and a clustered index
contains the entirity of the data (a clustered index is the data), so in tha
t
case the answer would be yes.
"C TO" wrote:
[vbcol=seagreen]
> Does that explain the 28 GB increases in backup size? the DBCC DBREINDEX j
ob
> was reindexing the tables with just about one third of the 59 GB database
> size. Assuming this is true, the marked pages are 28GB. Are these all inde
x
> pages?
>
> "Dennis Forbes" wrote:
>
Sunday, February 19, 2012
DBCC CHECKDB Problem
I run the DBCC for a particular database in Query
Analyzer, and it takes 3 minutes. I take the same SQL
script and place it in a job to be scheduled in Enterprise
Manager, and it takes 1 hour. Why? Please help.
Is there anything else running when you run the job? DBCC CHECKDB takes out
locks on the objects it checks, so it might have to wait for locks to be
released by other connections.
Jacco Schalkwijk
SQL Server MVP
"Canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:555501c42d25$d09852e0$a601280a@.phx.gbl...
> I run the DBCC for a particular database in Query
> Analyzer, and it takes 3 minutes. I take the same SQL
> script and place it in a job to be scheduled in Enterprise
> Manager, and it takes 1 hour. Why? Please help.
|||There was nothing running. To isolate, I made a copy of
the database and renamed it. There was nothing hitting
the database when the DBCC was running during the two
different scenarios.
>--Original Message--
>Is there anything else running when you run the job? DBCC
CHECKDB takes out
>locks on the objects it checks, so it might have to wait
for locks to be
>released by other connections.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Canaries" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:555501c42d25$d09852e0$a601280a@.phx.gbl...
Enterprise
>
>.
>
|||Try adding SET NOCOUNT ON at the beginning of the job step.
Andrew J. Kelly SQL MVP
"Canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:555501c42d25$d09852e0$a601280a@.phx.gbl...
> I run the DBCC for a particular database in Query
> Analyzer, and it takes 3 minutes. I take the same SQL
> script and place it in a job to be scheduled in Enterprise
> Manager, and it takes 1 hour. Why? Please help.
|||I checked out the connection properties for Query Analyzer
and used the same settings for the job. It still takes
the long time.
>--Original Message--
>Try adding SET NOCOUNT ON at the beginning of the job
step.
>--
>Andrew J. Kelly SQL MVP
>
>"Canaries" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:555501c42d25$d09852e0$a601280a@.phx.gbl...
Enterprise
>
>.
>
Analyzer, and it takes 3 minutes. I take the same SQL
script and place it in a job to be scheduled in Enterprise
Manager, and it takes 1 hour. Why? Please help.
Is there anything else running when you run the job? DBCC CHECKDB takes out
locks on the objects it checks, so it might have to wait for locks to be
released by other connections.
Jacco Schalkwijk
SQL Server MVP
"Canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:555501c42d25$d09852e0$a601280a@.phx.gbl...
> I run the DBCC for a particular database in Query
> Analyzer, and it takes 3 minutes. I take the same SQL
> script and place it in a job to be scheduled in Enterprise
> Manager, and it takes 1 hour. Why? Please help.
|||There was nothing running. To isolate, I made a copy of
the database and renamed it. There was nothing hitting
the database when the DBCC was running during the two
different scenarios.
>--Original Message--
>Is there anything else running when you run the job? DBCC
CHECKDB takes out
>locks on the objects it checks, so it might have to wait
for locks to be
>released by other connections.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Canaries" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:555501c42d25$d09852e0$a601280a@.phx.gbl...
Enterprise
>
>.
>
|||Try adding SET NOCOUNT ON at the beginning of the job step.
Andrew J. Kelly SQL MVP
"Canaries" <anonymous@.discussions.microsoft.com> wrote in message
news:555501c42d25$d09852e0$a601280a@.phx.gbl...
> I run the DBCC for a particular database in Query
> Analyzer, and it takes 3 minutes. I take the same SQL
> script and place it in a job to be scheduled in Enterprise
> Manager, and it takes 1 hour. Why? Please help.
|||I checked out the connection properties for Query Analyzer
and used the same settings for the job. It still takes
the long time.
>--Original Message--
>Try adding SET NOCOUNT ON at the beginning of the job
step.
>--
>Andrew J. Kelly SQL MVP
>
>"Canaries" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:555501c42d25$d09852e0$a601280a@.phx.gbl...
Enterprise
>
>.
>
Subscribe to:
Posts (Atom)