Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.
My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>
|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>> Hi,
>>
>> Can anyone explain below why the different explanation in terms of S and
>> IS
>> lock on the table? Or simply an errata in the 2005 BOL or the best
>> practice?
>>
>> · From SQL 2005 BOL,
>>
>> Scanning Modes
>> The mode in which the function is executed determines the level of
>> scanning
>> performed to obtain the statistical data that is used by the function.
>> mode
>> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
>> page chains for the allocation units that make up the specified
>> partitions of
>> the table or index. Unlike DBCC SHOWCONTIG that generally requires a
>> shared
>> (S) table lock, sys.dm_db_index_physical_stats requires only an
>> Intent-Shared
>> (IS) table lock, regardless of the mode that it runs in. For more
>> information
>> about locking, see Lock Modes.
>> · From
>> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>>
>> This problem has been resolved in SQL Server 2005. In SQL Server 2005,
>> all
>> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby
>> allowing
>> concurrent DML operations.
>>
>

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions o
f
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Share
d
(IS) table lock, regardless of the mode that it runs in. For more informatio
n
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/pr...
ovements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You m
ight want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and I
S
> lock on the table? Or simply an errata in the 2005 BOL or the best practic
e?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scannin
g
> performed to obtain the statistical data that is used by the function. mod
e
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions
of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a share
d
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Sha
red
> (IS) table lock, regardless of the mode that it runs in. For more informat
ion
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/pr...provements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowin
g
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>sql

Local copy of Remote Database?

Is there are direct and simply way to backup a database hosted on a
remote machine to my local workstation without involving sharing out
the hardware(eg: A:\ or some folder in local hdd) or even creating new
accounts which has the admin rights?If you are taking a backup, the backup path is always related to the
server, so you either can backup on a local drive OR backup the data on
a network share (UNC) where both machines have access to. Another copy
of a local database can be done using the DTS wizrad and copying all
objects and data via SQL Server objects copy.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||You can use net use to map a drive and use it for backup.
Regards
Amish Shah

Local copy of Remote Database?

Is there are direct and simply way to backup a database hosted on a
remote machine to my local workstation without involving sharing out
the hardware(eg: A:\ or some folder in local hdd) or even creating new
accounts which has the admin rights?If you are taking a backup, the backup path is always related to the
server, so you either can backup on a local drive OR backup the data on
a network share (UNC) where both machines have access to. Another copy
of a local database can be done using the DTS wizrad and copying all
objects and data via SQL Server objects copy.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||You can use net use to map a drive and use it for backup.
Regards
Amish Shah