Showing posts with label command. Show all posts
Showing posts with label command. Show all posts
Thursday, March 29, 2012
DBCC Shrinkfile
I ran a dbcc shrinkfile after deleting data in order to take thte db down in size and it has been running for 2 days. Can I canel the command? Will the file be partially shrunk? Last year when we did this I ran a re-index command. Will I have to run a bakup or truncate log command? I have been trying to get the client to upgrade from SQL 7 but they just have not done it yet.follow up question. If I cancel how long will it take to finish the cancel operation?
DBCC Shrinkfile
What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
DBCC Shrinkfile
What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the f
ile.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl
..
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? ThanksHassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||It moves data pages from the end of the file towards then beginning of the f
ile.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl
..
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
DBCC Shrinkfile
What happens underlying the DBCC Shrinkfile statement ? What moves around
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? Thanks
Hassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||It moves data pages from the end of the file towards then beginning of the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
,etc.. ? Trying to figure out why it takes so long for this command to run
,, what does it do ? Thanks
Hassan, you really should pick up a copy of Kalen Delaney's Inside SQL
Server 2000. It answers all of the questions you've had recently, and then
some. It really is the best money you'll spend if you want to learn the
guts of SQL Server...
On 3/19/05 1:14 AM, in article OllldrELFHA.3988@.tk2msftngp13.phx.gbl,
"Hassan" <fatima_ja@.hotmail.com> wrote:
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||It moves data pages from the end of the file towards then beginning of the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||I forgot to mention, that all of this data movement and space
allocation/deallocation is logged, so your transaction log can grow very
large as a result as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
|||Kalen also has a nice article in this month's SQL mag... But in addition to
Itzik's comment it is really a 2 part operation, first it move either rows,
or whole pages from the end of the file to the front of the file, then it
( by default) releases the space from the end of the file to the OS... One
warning however, is that this can cause fragmentation of your data, which
may ( or may not ) affect your performance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OllldrELFHA.3988@.tk2msftngp13.phx.gbl...
> What happens underlying the DBCC Shrinkfile statement ? What moves around
> ,etc.. ? Trying to figure out why it takes so long for this command to run
> ,, what does it do ? Thanks
>
Tuesday, March 27, 2012
DBCC ShrinkDatabase on large DB
Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
DBCC ShrinkDatabase on large DB
Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateo
nly')
go
dbcc shrinkfile('logical_ldf_name','truncateo
nly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian KingHave you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateo
nly')
go
dbcc shrinkfile('logical_ldf_name','truncateo
nly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
DBCC ShrinkDatabase on large DB
Hi,
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian King
Have you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
sql
I am attempting to release disk space back to the Operating System, but
the DBCC ShrinkDatabase command does not complete (in under 24 hours).
We have a 300GB database. We have just truncated tables containing
archive data, and wish to return approximatley 100GB free space back to
Windows.
Using the TRUNCATEONLY option returns quickly, but does not release any
space back to Windows.
What is the best way to release this space ?
Thanks in advance
Ian King
Have you tried to run DBCC SHRINKFILE?
For more details please refer to the BOL
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||Hi Ian,
Can you execute the SHRINKFILE command seperately for MDF and LDF when
database is set to single user mode.
Set the database to Single User:-
Alter database <dbname> set single_user with rollback immediate
-- Now perform the full database backup and Transaction log backup
backup database <dbname> to disk='d:\backup\dbname.bak' with init
go
backup log <dbname> to disk='d:\backup\dbname.trn'
-- Now shrink the MDF file
dbcc shrinkfile('logical_mdf_name','truncateonly')
go
dbcc shrinkfile('logical_ldf_name','truncateonly')
go
-- See the MDF and LDF size using
sp_helpdb master
or alse use:-
sp_spaceused @.updateusage='true' -- for data size and index
go
dbcc sqlperf(logspace) -- log size
- set the database to multiuser
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
|||As the others have suggested shinkfile will allow you to shrink in smaller
chuncks... Since you will going through 300GB it will take a while...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ian King" <idking@.telkomsa.net> wrote in message
news:ccqu13$lmh$1@.ctb-nnrp2.saix.net...
> Hi,
> I am attempting to release disk space back to the Operating System, but
> the DBCC ShrinkDatabase command does not complete (in under 24 hours).
> We have a 300GB database. We have just truncated tables containing
> archive data, and wish to return approximatley 100GB free space back to
> Windows.
> Using the TRUNCATEONLY option returns quickly, but does not release any
> space back to Windows.
> What is the best way to release this space ?
> Thanks in advance
> Ian King
>
sql
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
>
>
>
>
DBCC SHRINKDATABASE
In my company, we use this command to shink databse, but it was not like our
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry Mu
See DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:
> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry Mu
See DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:
> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>
DBCC SHRINKDATABASE
In my company, we use this command to shink databse, but it was not like our
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry MuSee DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:
> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Ekrem Ã?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>sql
expectation after it was done because the size was reduced less than
expectation. Could someone tell me if there are some factor to impact this
operation? What can I do to shrink much more size of database. Thanks.
Jerry MuSee DBCC SHRINKFILE, section 'The File Does Not Shrink' on BOL.
Run the query listed there to see if sufficient free space is available
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;
Hope this helps,
Ben Nevarez
Senior Database Administrator
"Iter" wrote:
> In my company, we use this command to shink databse, but it was not like our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also, see the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Ekrem Ã?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Are you sure there is free space in the database? What is your goal in
shrinking the database? Reclaiming disk space temporarily? Unless your
database is large only because of a very unusual data load, and that data is
now gone, I would not allocate that disk space to something else just yet.
If your database is large because of normal activity, then shrinking it will
only mean that later it has to grow again, and you do not want this to be an
unexpected event that happens during peak activity, because you will have a
lot of unhappy users. Better off leaving the file large...
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>|||Also I second the recommendation to read Tibor's article, posted by Ekrem.
It echoes my suggestions but in much more detail.
A
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:75F13788-9781-4DE1-8BEF-AA39A6977CEB@.microsoft.com...
> In my company, we use this command to shink databse, but it was not like
> our
> expectation after it was done because the size was reduced less than
> expectation. Could someone tell me if there are some factor to impact this
> operation? What can I do to shrink much more size of database. Thanks.
> Jerry Mu
>sql
Sunday, March 25, 2012
DBCC SHRINK DATABASE TIMED OUT
Hi all,
mydbcc schrink database command failed to shrink
a datbase of 461 gb on sql server 2005 CTP
here's the error. pls help
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
thanks
joey
have you seen ken's blog http://blogs.msdn.com/khen1234/archive/2005/10/20/483015.aspx
and tibor's article http://www.karaszi.com/sqlserver/info_dont_shrink.asp
dbcc shrink database
Morning,
Is there a specific command/clause in the DBCC SHRINKDATABASE command where I can force a database to release free space to the operating system ?
Cheers,
DuncanMorning,
Is there a specific command/clause in the DBCC SHRINKDATABASE command where I can force a database to release free space to the operating system ?
Cheers,
Duncan
yes . use TRUNCATEONLY.
from BOL:
TRUNCATEONLY
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.|||See BOL for it
DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY )
TRUNCATEONLY will reduce all free space until last extension allocated.
Else, try
DBCC SHRINKDATABASE ( database_name , 1 )
to release all less 1 percent|||Thanks fellas but I've tried both of those options and the database in question has automatically grabbed any existing space as free space. Both commands completed after only a few seconds.
FYI - The database is set to autogrow but only in very small amounts. The database grabs far more free space.
Is it worth turning off autogrow or is it possible that the database has been corrupted in some way ?
Any ideas ?
Is there a specific command/clause in the DBCC SHRINKDATABASE command where I can force a database to release free space to the operating system ?
Cheers,
DuncanMorning,
Is there a specific command/clause in the DBCC SHRINKDATABASE command where I can force a database to release free space to the operating system ?
Cheers,
Duncan
yes . use TRUNCATEONLY.
from BOL:
TRUNCATEONLY
Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.|||See BOL for it
DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY )
TRUNCATEONLY will reduce all free space until last extension allocated.
Else, try
DBCC SHRINKDATABASE ( database_name , 1 )
to release all less 1 percent|||Thanks fellas but I've tried both of those options and the database in question has automatically grabbed any existing space as free space. Both commands completed after only a few seconds.
FYI - The database is set to autogrow but only in very small amounts. The database grabs far more free space.
Is it worth turning off autogrow or is it possible that the database has been corrupted in some way ?
Any ideas ?
Dbcc Showcontig
I am looking for coding that runs dbcc showcontig on all user databases in one command on a SQL Server. The coding I have listed below will grab the names of user databases, but still runs the showcontig on the master db. I have tried using sp_MSforeachdb, but that performs the showcontig on both system and user databases. Thx!
P.S. If possible this command can be used on SQL 7.0 and 2K.
USE master
DECLARE dnames_cursor CURSOR
FOR
SELECT NAME FROM sysdatabases
where name not in ('master', 'model', 'tempdb', 'pubs', 'Northwind', 'msdb')
OPEN dnames_cursor
DECLARE @.dbname sysname
FETCH NEXT FROM dnames_cursor INTO @.dbname
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.dbname = RTRIM(@.dbname)
set nocount on
select 'ZZ_Date; ', getdate()
set nocount on
Declare @.My_dbname char(30)
exec ('use ' + @.dbname)
select @.My_dbname = @.dbname
print 'ZZ_Name; ' + @.My_dbname
dbcc showcontig
END
FETCH NEXT FROM dnames_cursor INTO @.dbname
END
CLOSE dnames_cursor
DEALLOCATE dnames_cursorMay check this DB Journal (http://www.databasejournal.com/features/mssql/article.php/1442901) link for more information.sql
P.S. If possible this command can be used on SQL 7.0 and 2K.
USE master
DECLARE dnames_cursor CURSOR
FOR
SELECT NAME FROM sysdatabases
where name not in ('master', 'model', 'tempdb', 'pubs', 'Northwind', 'msdb')
OPEN dnames_cursor
DECLARE @.dbname sysname
FETCH NEXT FROM dnames_cursor INTO @.dbname
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.dbname = RTRIM(@.dbname)
set nocount on
select 'ZZ_Date; ', getdate()
set nocount on
Declare @.My_dbname char(30)
exec ('use ' + @.dbname)
select @.My_dbname = @.dbname
print 'ZZ_Name; ' + @.My_dbname
dbcc showcontig
END
FETCH NEXT FROM dnames_cursor INTO @.dbname
END
CLOSE dnames_cursor
DEALLOCATE dnames_cursorMay check this DB Journal (http://www.databasejournal.com/features/mssql/article.php/1442901) link for more information.sql
Thursday, March 22, 2012
dbcc showcontig
When I run command: dbcc showcontig with all_indexes, I got a lot of indexes w/ high logical fragmentation
So I ran dbcc dbreindex on all the indexes
When I re-ran showcontig to verify each table individually, it reported 0% frag per index
For one last time, I ran a full report of the entire database, the logicalfrag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_indexes have different results for logicalfrag100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages).
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read the
> whitepaper below for full details:
> http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
> > 100% fragmentation is only possible with tiny indexes (i.e. several
pages).
> > Can you give some examples of the output you're comparing?
> >
> > You don't need to fix fragmentation on all indexes - it depends on your
> > access patterns whether fragmentation will affect your performance. Read
the
> > whitepaper below for full details:
> >
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > Regards.
> >
>
So I ran dbcc dbreindex on all the indexes
When I re-ran showcontig to verify each table individually, it reported 0% frag per index
For one last time, I ran a full report of the entire database, the logicalfrag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_indexes have different results for logicalfrag100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages).
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read the
> whitepaper below for full details:
> http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
> > 100% fragmentation is only possible with tiny indexes (i.e. several
pages).
> > Can you give some examples of the output you're comparing?
> >
> > You don't need to fix fragmentation on all indexes - it depends on your
> > access patterns whether fragmentation will affect your performance. Read
the
> > whitepaper below for full details:
> >
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> >
> > Regards.
> >
>
dbcc showcontig
When I run command: dbcc showcontig with all_indexes, I got a lot of indexe
s w/ high logical fragmentation.
So I ran dbcc dbreindex on all the indexes.
When I re-ran showcontig to verify each table individually, it reported 0% f
rag per index.
For one last time, I ran a full report of the entire database, the logicalfr
ag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_inde
xes have different results for logicalfrag?100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages)
.
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read t
he
> whitepaper below for full details:
> http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
pages).
the
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
>
s w/ high logical fragmentation.
So I ran dbcc dbreindex on all the indexes.
When I re-ran showcontig to verify each table individually, it reported 0% f
rag per index.
For one last time, I ran a full report of the entire database, the logicalfr
ag quoted the original numbers of 100%.
Why would dbcc showcontig all_indexes and dbcc showcontig (tblname) all_inde
xes have different results for logicalfrag?100% fragmentation is only possible with tiny indexes (i.e. several pages).
Can you give some examples of the output you're comparing?
You don't need to fix fragmentation on all indexes - it depends on your
access patterns whether fragmentation will affect your performance. Read the
whitepaper below for full details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:AEA21E32-7345-4600-B116-434F1D3E398D@.microsoft.com...
> When I run command: dbcc showcontig with all_indexes, I got a lot of
indexes w/ high logical fragmentation.
> So I ran dbcc dbreindex on all the indexes.
> When I re-ran showcontig to verify each table individually, it reported 0%
frag per index.
> For one last time, I ran a full report of the entire database, the
logicalfrag quoted the original numbers of 100%.
> Why would dbcc showcontig all_indexes and dbcc showcontig (tblname)
all_indexes have different results for logicalfrag?
>|||***Here's the result w/ dbcc showcontig with all_indexes****
DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6797.0
- Avg. Page Density (full)................: 16.02%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5335.0
- Avg. Page Density (full)................: 34.09%
DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7207.0
- Avg. Page Density (full)................: 10.96%
****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
Paul S Randal [MS] wrote:
> 100% fragmentation is only possible with tiny indexes (i.e. several pages)
.
> Can you give some examples of the output you're comparing?
> You don't need to fix fragmentation on all indexes - it depends on your
> access patterns whether fragmentation will affect your performance. Read t
he
> whitepaper below for full details:
> http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
> Regards.
>|||Interesting - there must be a corner case in one of the algorithms for
single-page indexes, but that's not something I'd consider fixing in an SP
as you shouldn't even bother about fragmentation with an index under say
1000 pages - it's just not worth it.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Achieng O" <snow.barb@.verizon.net> wrote in message
news:#gC0eCO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> ***Here's the result w/ dbcc showcontig with all_indexes****
> DBCC SHOWCONTIG scanning 'DEDUCTRULE' table...
> Table: 'DEDUCTRULE' (158623608); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 6797.0
> - Avg. Page Density (full)................: 16.02%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 100.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****BUT WHEN I RUN dbcc showcontig (ACCESSPROFILE) with all_indexes****
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 5335.0
> - Avg. Page Density (full)................: 34.09%
> DBCC SHOWCONTIG scanning 'ACCESSPROFILE' table...
> Table: 'ACCESSPROFILE' (162099618); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 1
> - Extents Scanned.......................: 1
> - Extent Switches.......................: 0
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 7207.0
> - Avg. Page Density (full)................: 10.96%
> ****HOW COME THE STATS FOR LOGICALFRAG CHANGED?*****
> Paul S Randal [MS] wrote:
pages).
the
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
>
dbcc reindex issue - - I dont understand!
Hi Folks,
SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :
dbcc dbreindex ('tablename')
go
for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%
Second output is from after the reindex:
DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%
Following are my concerns:
The following numbers are all higher after reindex than before reindex:
pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.
scan density is lower after reindex than before reindex
Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.
Am I missing something?
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***Did you not already post this message before, or am i experiencing deja
vu?
Your database id's are different, which means that you ran the
showcontig command on a different database. Did you make a backup and
restore it?
Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
With this scan density, defragmentation may be no be very useful.
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
I've also seen this that the reserved space for the table increases
and almost double. My speculation have been that space is reserved
for future reindex operations, but I have not dug into it.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, I did repost this because i did not get any answers the first time.
Yes, I did restore the backup of a database and created a new database.
*** Sent via Developersdex http://www.developersdex.com ***
Wednesday, March 21, 2012
DBCC READPAGE
The syntax for the DBCC READPAGE command is
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt =
{ 0 | 1} ])
Does anyone know what the "formatstr" parameter is used for, and what are is
options?
Thanks
Barry McAuslinIt's an undocumented command used for testing internally - what are you
trying to do with it?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
is
returns the whole page as text. This is proving to be a bit slow over a
WAN. I would like to get the GAM with as little network traffic as
possible.
So why do I want the GAM? I am working on a tool for SQL Server that Oracle
has that I found quite useful. If you go to www.sqlfe.com you will see what
it is.
Thanks
Barry McAuslin
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
rights.
to get pages in binary form. I can forward you contact details if you're
interested in licensing on-disk internals information etc.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
Oracle
what
http://www.sqlfe.com/contact.asp
Thanks
Barry
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
way
rights.
Microsoft SQL Server 2000. I checked about the licensing details and we're
not currently entering into any new agreements for this information.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:#yCW18i4DHA.2692@.TK2MSFTNGP09.phx.gbl...
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt =
{ 0 | 1} ])
Does anyone know what the "formatstr" parameter is used for, and what are is
options?
Thanks
Barry McAuslinIt's an undocumented command used for testing internally - what are you
trying to do with it?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
quote:
> The syntax for the DBCC READPAGE command is
> DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt =
> { 0 | 1} ])
> Does anyone know what the "formatstr" parameter is used for, and what are
is
quote:|||I am trying to get the GAM in binary form. I am using DBCC PAGE but this
> options?
> Thanks
> Barry McAuslin
>
returns the whole page as text. This is proving to be a bit slow over a
WAN. I would like to get the GAM with as little network traffic as
possible.
So why do I want the GAM? I am working on a tool for SQL Server that Oracle
has that I found quite useful. If you go to www.sqlfe.com you will see what
it is.
Thanks
Barry McAuslin
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
quote:
> It's an undocumented command used for testing internally - what are you
> trying to do with it?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
quote:|||DBCC READPAGE won't do what you want. Unfortunately there's no supported way
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
=[QUOTE]
are[QUOTE]
> is
>
to get pages in binary form. I can forward you contact details if you're
interested in licensing on-disk internals information etc.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
quote:
> I am trying to get the GAM in binary form. I am using DBCC PAGE but this
> returns the whole page as text. This is proving to be a bit slow over a
> WAN. I would like to get the GAM with as little network traffic as
> possible.
> So why do I want the GAM? I am working on a tool for SQL Server that
Oracle
quote:
> has that I found quite useful. If you go to www.sqlfe.com you will see
what
quote:|||Sure, you can contact me via the details on this page
> it is.
> Thanks
> Barry McAuslin
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> rights.
printopt[QUOTE]
> =
> are
>
http://www.sqlfe.com/contact.asp
Thanks
Barry
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
quote:
> DBCC READPAGE won't do what you want. Unfortunately there's no supported
way
quote:
> to get pages in binary form. I can forward you contact details if you're
> interested in licensing on-disk internals information etc.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
quote:|||An update for you - the printopt parameter is explained on pg 249 of Inside
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
this[QUOTE]
> Oracle
> what
you[QUOTE]
> printopt
what[QUOTE]
>
Microsoft SQL Server 2000. I checked about the licensing details and we're
not currently entering into any new agreements for this information.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:#yCW18i4DHA.2692@.TK2MSFTNGP09.phx.gbl...
quote:
> Sure, you can contact me via the details on this page
> http://www.sqlfe.com/contact.asp
> Thanks
> Barry
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> way
> rights.
> this
a[QUOTE]
see[QUOTE]
> you
> what
>
DBCC READPAGE
The syntax for the DBCC READPAGE command is
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])
Does anyone know what the "formatstr" parameter is used for, and what are is
options?
Thanks
Barry McAuslinIt's an undocumented command used for testing internally - what are you
trying to do with it?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The syntax for the DBCC READPAGE command is
> DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt => { 0 | 1} ])
> Does anyone know what the "formatstr" parameter is used for, and what are
is
> options?
> Thanks
> Barry McAuslin
>|||I am trying to get the GAM in binary form. I am using DBCC PAGE but this
returns the whole page as text. This is proving to be a bit slow over a
WAN. I would like to get the GAM with as little network traffic as
possible.
So why do I want the GAM? I am working on a tool for SQL Server that Oracle
has that I found quite useful. If you go to www.sqlfe.com you will see what
it is.
Thanks
Barry McAuslin
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> It's an undocumented command used for testing internally - what are you
> trying to do with it?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > The syntax for the DBCC READPAGE command is
> >
> > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt
=> > { 0 | 1} ])
> >
> > Does anyone know what the "formatstr" parameter is used for, and what
are
> is
> > options?
> >
> > Thanks
> >
> > Barry McAuslin
> >
> >
>|||DBCC READPAGE won't do what you want. Unfortunately there's no supported way
to get pages in binary form. I can forward you contact details if you're
interested in licensing on-disk internals information etc.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I am trying to get the GAM in binary form. I am using DBCC PAGE but this
> returns the whole page as text. This is proving to be a bit slow over a
> WAN. I would like to get the GAM with as little network traffic as
> possible.
> So why do I want the GAM? I am working on a tool for SQL Server that
Oracle
> has that I found quite useful. If you go to www.sqlfe.com you will see
what
> it is.
> Thanks
> Barry McAuslin
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > It's an undocumented command used for testing internally - what are you
> > trying to do with it?
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > The syntax for the DBCC READPAGE command is
> > >
> > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
printopt
> => > > { 0 | 1} ])
> > >
> > > Does anyone know what the "formatstr" parameter is used for, and what
> are
> > is
> > > options?
> > >
> > > Thanks
> > >
> > > Barry McAuslin
> > >
> > >
> >
> >
>|||Sure, you can contact me via the details on this page
http://www.sqlfe.com/contact.asp
Thanks
Barry
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> DBCC READPAGE won't do what you want. Unfortunately there's no supported
way
> to get pages in binary form. I can forward you contact details if you're
> interested in licensing on-disk internals information etc.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > I am trying to get the GAM in binary form. I am using DBCC PAGE but
this
> > returns the whole page as text. This is proving to be a bit slow over a
> > WAN. I would like to get the GAM with as little network traffic as
> > possible.
> >
> > So why do I want the GAM? I am working on a tool for SQL Server that
> Oracle
> > has that I found quite useful. If you go to www.sqlfe.com you will see
> what
> > it is.
> >
> > Thanks
> >
> > Barry McAuslin
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > It's an undocumented command used for testing internally - what are
you
> > > trying to do with it?
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > > The syntax for the DBCC READPAGE command is
> > > >
> > > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
> printopt
> > => > > > { 0 | 1} ])
> > > >
> > > > Does anyone know what the "formatstr" parameter is used for, and
what
> > are
> > > is
> > > > options?
> > > >
> > > > Thanks
> > > >
> > > > Barry McAuslin
> > > >
> > > >
> > >
> > >
> >
> >
>|||An update for you - the printopt parameter is explained on pg 249 of Inside
Microsoft SQL Server 2000. I checked about the licensing details and we're
not currently entering into any new agreements for this information.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:#yCW18i4DHA.2692@.TK2MSFTNGP09.phx.gbl...
> Sure, you can contact me via the details on this page
> http://www.sqlfe.com/contact.asp
> Thanks
> Barry
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> > DBCC READPAGE won't do what you want. Unfortunately there's no supported
> way
> > to get pages in binary form. I can forward you contact details if you're
> > interested in licensing on-disk internals information etc.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > > I am trying to get the GAM in binary form. I am using DBCC PAGE but
> this
> > > returns the whole page as text. This is proving to be a bit slow over
a
> > > WAN. I would like to get the GAM with as little network traffic as
> > > possible.
> > >
> > > So why do I want the GAM? I am working on a tool for SQL Server that
> > Oracle
> > > has that I found quite useful. If you go to www.sqlfe.com you will
see
> > what
> > > it is.
> > >
> > > Thanks
> > >
> > > Barry McAuslin
> > >
> > > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > > news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > > It's an undocumented command used for testing internally - what are
> you
> > > > trying to do with it?
> > > > --
> > > > Paul Randal
> > > > Dev Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > > > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > > > The syntax for the DBCC READPAGE command is
> > > > >
> > > > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
> > printopt
> > > => > > > > { 0 | 1} ])
> > > > >
> > > > > Does anyone know what the "formatstr" parameter is used for, and
> what
> > > are
> > > > is
> > > > > options?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Barry McAuslin
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])
Does anyone know what the "formatstr" parameter is used for, and what are is
options?
Thanks
Barry McAuslinIt's an undocumented command used for testing internally - what are you
trying to do with it?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The syntax for the DBCC READPAGE command is
> DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt => { 0 | 1} ])
> Does anyone know what the "formatstr" parameter is used for, and what are
is
> options?
> Thanks
> Barry McAuslin
>|||I am trying to get the GAM in binary form. I am using DBCC PAGE but this
returns the whole page as text. This is proving to be a bit slow over a
WAN. I would like to get the GAM with as little network traffic as
possible.
So why do I want the GAM? I am working on a tool for SQL Server that Oracle
has that I found quite useful. If you go to www.sqlfe.com you will see what
it is.
Thanks
Barry McAuslin
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> It's an undocumented command used for testing internally - what are you
> trying to do with it?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > The syntax for the DBCC READPAGE command is
> >
> > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt
=> > { 0 | 1} ])
> >
> > Does anyone know what the "formatstr" parameter is used for, and what
are
> is
> > options?
> >
> > Thanks
> >
> > Barry McAuslin
> >
> >
>|||DBCC READPAGE won't do what you want. Unfortunately there's no supported way
to get pages in binary form. I can forward you contact details if you're
interested in licensing on-disk internals information etc.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> I am trying to get the GAM in binary form. I am using DBCC PAGE but this
> returns the whole page as text. This is proving to be a bit slow over a
> WAN. I would like to get the GAM with as little network traffic as
> possible.
> So why do I want the GAM? I am working on a tool for SQL Server that
Oracle
> has that I found quite useful. If you go to www.sqlfe.com you will see
what
> it is.
> Thanks
> Barry McAuslin
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > It's an undocumented command used for testing internally - what are you
> > trying to do with it?
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > The syntax for the DBCC READPAGE command is
> > >
> > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
printopt
> => > > { 0 | 1} ])
> > >
> > > Does anyone know what the "formatstr" parameter is used for, and what
> are
> > is
> > > options?
> > >
> > > Thanks
> > >
> > > Barry McAuslin
> > >
> > >
> >
> >
>|||Sure, you can contact me via the details on this page
http://www.sqlfe.com/contact.asp
Thanks
Barry
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> DBCC READPAGE won't do what you want. Unfortunately there's no supported
way
> to get pages in binary form. I can forward you contact details if you're
> interested in licensing on-disk internals information etc.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > I am trying to get the GAM in binary form. I am using DBCC PAGE but
this
> > returns the whole page as text. This is proving to be a bit slow over a
> > WAN. I would like to get the GAM with as little network traffic as
> > possible.
> >
> > So why do I want the GAM? I am working on a tool for SQL Server that
> Oracle
> > has that I found quite useful. If you go to www.sqlfe.com you will see
> what
> > it is.
> >
> > Thanks
> >
> > Barry McAuslin
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > It's an undocumented command used for testing internally - what are
you
> > > trying to do with it?
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > > The syntax for the DBCC READPAGE command is
> > > >
> > > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
> printopt
> > => > > > { 0 | 1} ])
> > > >
> > > > Does anyone know what the "formatstr" parameter is used for, and
what
> > are
> > > is
> > > > options?
> > > >
> > > > Thanks
> > > >
> > > > Barry McAuslin
> > > >
> > > >
> > >
> > >
> >
> >
>|||An update for you - the printopt parameter is explained on pg 249 of Inside
Microsoft SQL Server 2000. I checked about the licensing details and we're
not currently entering into any new agreements for this information.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
news:#yCW18i4DHA.2692@.TK2MSFTNGP09.phx.gbl...
> Sure, you can contact me via the details on this page
> http://www.sqlfe.com/contact.asp
> Thanks
> Barry
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23jcn5Oe4DHA.4068@.tk2msftngp13.phx.gbl...
> > DBCC READPAGE won't do what you want. Unfortunately there's no supported
> way
> > to get pages in binary form. I can forward you contact details if you're
> > interested in licensing on-disk internals information etc.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > news:e9hNovW4DHA.1816@.TK2MSFTNGP12.phx.gbl...
> > > I am trying to get the GAM in binary form. I am using DBCC PAGE but
> this
> > > returns the whole page as text. This is proving to be a bit slow over
a
> > > WAN. I would like to get the GAM with as little network traffic as
> > > possible.
> > >
> > > So why do I want the GAM? I am working on a tool for SQL Server that
> > Oracle
> > > has that I found quite useful. If you go to www.sqlfe.com you will
see
> > what
> > > it is.
> > >
> > > Thanks
> > >
> > > Barry McAuslin
> > >
> > > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > > news:uCDDh9S4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > > It's an undocumented command used for testing internally - what are
> you
> > > > trying to do with it?
> > > > --
> > > > Paul Randal
> > > > Dev Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > > "Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> wrote in message
> > > > news:eVNSzGK4DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > > > > The syntax for the DBCC READPAGE command is
> > > > >
> > > > > DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [,
> > printopt
> > > => > > > > { 0 | 1} ])
> > > > >
> > > > > Does anyone know what the "formatstr" parameter is used for, and
> what
> > > are
> > > > is
> > > > > options?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Barry McAuslin
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
DBCC PROCCACHE Question.
What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair Company
If your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:
> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>
>
|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...[vbcol=seagreen]
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair Company
If your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:
> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>
>
|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...[vbcol=seagreen]
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
DBCC PROCCACHE Question.
What exactly does this command show you. I need to know what the numbers it
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair CompanyIf your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:
> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> > What exactly does this command show you. I need to know what the numbers
> > it
> > shows actually mean. I have been having SERIOUS performance issues with
> > my
> > SQL cluster and just by looking at the numbers from this command it looks
> > like my stored proc cache just grows and grows. I think...
> >
> > Are high numbers from this command a bad thing or just normal?
> >
> > Any help appreciated.
> >
> > TIA
> > Cary LaPora
> > Database Administrator
> > The Lancair Company
>
>|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
>> If that is the case look inside syscacheobjects to see if you have actual
>> plan reuse.
>> dbcc cachestats -- will show you hit ratios
>> dbcc memorystatus -- will show you how memory is divided inside SQL
>> Server
>> (for SQL Server controlled memory grants)
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Cary" <Cary@.discussions.microsoft.com> wrote in message
>> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>> > What exactly does this command show you. I need to know what the
>> > numbers
>> > it
>> > shows actually mean. I have been having SERIOUS performance issues
>> > with
>> > my
>> > SQL cluster and just by looking at the numbers from this command it
>> > looks
>> > like my stored proc cache just grows and grows. I think...
>> >
>> > Are high numbers from this command a bad thing or just normal?
>> >
>> > Any help appreciated.
>> >
>> > TIA
>> > Cary LaPora
>> > Database Administrator
>> > The Lancair Company
>>
shows actually mean. I have been having SERIOUS performance issues with my
SQL cluster and just by looking at the numbers from this command it looks
like my stored proc cache just grows and grows. I think...
Are high numbers from this command a bad thing or just normal?
Any help appreciated.
TIA
Cary LaPora
Database Administrator
The Lancair CompanyIf your proccache keeps growing that basically means you are not reusing
your query plans properly. That in and of itself can be a major performance
issue but this is simply one aspect of what could be a host of issues. You
may want to have a look at these:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||If that is the case look inside syscacheobjects to see if you have actual
plan reuse.
dbcc cachestats -- will show you hit ratios
dbcc memorystatus -- will show you how memory is divided inside SQL Server
(for SQL Server controlled memory grants)
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> What exactly does this command show you. I need to know what the numbers
> it
> shows actually mean. I have been having SERIOUS performance issues with
> my
> SQL cluster and just by looking at the numbers from this command it looks
> like my stored proc cache just grows and grows. I think...
> Are high numbers from this command a bad thing or just normal?
> Any help appreciated.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company|||Thanks for the info guys.
Gert,
I don't suppose you can point me to any help about those commands. I can't
seem to find them in the T-SQL help and I'm not really sure exactly what they
are telling me.
TIA
Cary LaPora
Database Administrator
The Lancair Company
"Gert E.R. Drapers" wrote:
> If that is the case look inside syscacheobjects to see if you have actual
> plan reuse.
> dbcc cachestats -- will show you hit ratios
> dbcc memorystatus -- will show you how memory is divided inside SQL Server
> (for SQL Server controlled memory grants)
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
> > What exactly does this command show you. I need to know what the numbers
> > it
> > shows actually mean. I have been having SERIOUS performance issues with
> > my
> > SQL cluster and just by looking at the numbers from this command it looks
> > like my stored proc cache just grows and grows. I think...
> >
> > Are high numbers from this command a bad thing or just normal?
> >
> > Any help appreciated.
> >
> > TIA
> > Cary LaPora
> > Database Administrator
> > The Lancair Company
>
>|||Sure see:
INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/default.aspx?scid=kb;en-us;271624
I will digg up some info on cachestats, not handy right now.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0D87AA70-9DF1-447B-8562-60B02AACADDB@.microsoft.com...
> Thanks for the info guys.
> Gert,
> I don't suppose you can point me to any help about those commands. I
> can't
> seem to find them in the T-SQL help and I'm not really sure exactly what
> they
> are telling me.
> TIA
> Cary LaPora
> Database Administrator
> The Lancair Company
> "Gert E.R. Drapers" wrote:
>> If that is the case look inside syscacheobjects to see if you have actual
>> plan reuse.
>> dbcc cachestats -- will show you hit ratios
>> dbcc memorystatus -- will show you how memory is divided inside SQL
>> Server
>> (for SQL Server controlled memory grants)
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Cary" <Cary@.discussions.microsoft.com> wrote in message
>> news:330BED46-1B4B-4CA1-9ECF-E070EF2599B2@.microsoft.com...
>> > What exactly does this command show you. I need to know what the
>> > numbers
>> > it
>> > shows actually mean. I have been having SERIOUS performance issues
>> > with
>> > my
>> > SQL cluster and just by looking at the numbers from this command it
>> > looks
>> > like my stored proc cache just grows and grows. I think...
>> >
>> > Are high numbers from this command a bad thing or just normal?
>> >
>> > Any help appreciated.
>> >
>> > TIA
>> > Cary LaPora
>> > Database Administrator
>> > The Lancair Company
>>
Subscribe to:
Posts (Atom)