DBfile usage: tables

Showing posts with label tables. Show all posts

Thursday, March 29, 2012

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production serve
Thank
Har
MCDB
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and adde
some indexes to them
This caused the mdf file size to increase from 82GB to 109GB. Becaus
our development and training boxes do not have enough disk space,
tried to shrink the file so that the database can fit into th
development box when I do a restore
I created a job to run the following at 3AM
DBCC SHRINKFILE (N'Nu_Data', 85000
At 6AM, the job crashed with the following error
Transaction (Process ID 66) was deadlocked on lock resources wit
another process and has been chosen as the deadlock victim. Rerun th
transaction
Our database is running transactional replication. Do I need to paus
the replication and set the database in single user mode befor
executing the DBCC SHRINKFILE (N'N_Data', 85000)
alter database N
set single_user with ROLLBACK IMMEDIATE
g
DBCC SHRINKFILE (N'Nu_Data', 85000
g
alter database N
set multi_use
g

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
goHi,
Execute DBCC SHRINKFILE when there is no active transacton in progress for t
hat database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a no
rmal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database.
Preferably do not schedule this as a job, rather do it manually if it is a
production server
Thanks
Hari
MCDBA
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go

DBCC SHRINKFILE

A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go
Hi,
Execute DBCC SHRINKFILE when there is no active transacton in progress for that database. Check this by executing
DBCC OPENTRAN(DBNAME). It can be a replication process or it can can be a normal user connecting to the server and using some DML.
So execute the SHRINKFILE command when there is no activity in the database. Preferably do not schedule this as a job, rather do it manually if it is a production server
Thanks
Hari
MCDBA
-- KC wrote: --
A few days ago, I made some structure changes to 5 tables and added
some indexes to them.
This caused the mdf file size to increase from 82GB to 109GB. Because
our development and training boxes do not have enough disk space, I
tried to shrink the file so that the database can fit into the
development box when I do a restore.
I created a job to run the following at 3AM:
DBCC SHRINKFILE (N'Nu_Data', 85000)
At 6AM, the job crashed with the following error.
Transaction (Process ID 66) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
Our database is running transactional replication. Do I need to pause
the replication and set the database in single user mode before
executing the DBCC SHRINKFILE (N'N_Data', 85000)?
alter database Nu
set single_user with ROLLBACK IMMEDIATE
go
DBCC SHRINKFILE (N'Nu_Data', 85000)
go
alter database Nu
set multi_user
go
sql

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

We deleted alot of Temp tables in our DB that we did not need anylonger. Afterwards, I ran DBCC SHRINKDATABASE and left the leave space at 10%. The DB shrank from 8GB to 6.5GB. Sunday nights we run the Maintenance plan wizard which rebuilds the Reorganizes Data and Index pages and changes free space to 10%. When I cam in on Monday the DB grew back to 8GB. The DB on our Test environment is not setup to run the Maint. plan and stays at 6.5GB. Has anyone ever heard of this or might know a solution to keep the DB at 6.5GB as on the Test server? Thank you."Don" <donolwert@.hotmail.com> wrote in message
news:243A6EFD-2F1A-44D3-80C9-57409859E59B@.microsoft.com...
> We deleted alot of Temp tables in our DB that we did not need anylonger.
Afterwards, I ran DBCC SHRINKDATABASE and left the leave space at 10%. The
DB shrank from 8GB to 6.5GB. Sunday nights we run the Maintenance plan
wizard which rebuilds the Reorganizes Data and Index pages and changes free
space to 10%. When I cam in on Monday the DB grew back to 8GB. The DB on
our Test environment is not setup to run the Maint. plan and stays at 6.5GB.
Has anyone ever heard of this or might know a solution to keep the DB at
6.5GB as on the Test server? Thank you.
Perhaps you could schedule the DBCC SHRINKDATABASE to run after your index
rebuild process? It's highly likely that the database expansion is required
to handle the index rebuilds.
Stevesql

DBCC SHRINKDATABASE

quote:

> We deleted alot of Temp tables in our DB that we did not need anylonger.

Afterwards, I ran DBCC SHRINKDATABASE and left the leave space at 10%. The
DB shrank from 8GB to 6.5GB. Sunday nights we run the Maintenance plan
wizard which rebuilds the Reorganizes Data and Index pages and changes free
space to 10%. When I cam in on Monday the DB grew back to 8GB. The DB on
our Test environment is not setup to run the Maint. plan and stays at 6.5GB.
Has anyone ever heard of this or might know a solution to keep the DB at
6.5GB as on the Test server? Thank you.
Perhaps you could schedule the DBCC SHRINKDATABASE to run after your index
rebuild process? It's highly likely that the database expansion is required
to handle the index rebuilds.
Steve

Sunday, March 25, 2012

DBCC SHOWCONTIG shows no result

Hello
Trying to get some fragmentation info back on SQL2000 tables but no results
are returned. Is this a bug?
DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
ALL_INDEXES, NO_INFOMSGS
DBCC SHOWCONTIG ('MsgAudit_CBOT')
DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
-- all above tables exist and have clustered index with millions of rows.
tks
all queries produce no result:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
-- cranfield, DBAAre you certain you really execute the queries? Press the "execute" button? Also, can you try using
OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> Hello
> Trying to get some fragmentation info back on SQL2000 tables but no results
> are returned. Is this a bug?
> DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> ALL_INDEXES, NO_INFOMSGS
> DBCC SHOWCONTIG ('MsgAudit_CBOT')
> DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> -- all above tables exist and have clustered index with millions of rows.
> tks
>
> all queries produce no result:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA|||I am certain query is executed. Have also tried osql - still no results, just
as below. My SQL2005 table returns results but NOT SQL2000
1> use eqmclog
2> go
1> dbcc showcontig('providersourceinfo')
2> go
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
1>
very strange this...
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Are you certain you really execute the queries? Press the "execute" button? Also, can you try using
> OSQL.EXE? That DBCC should give you an error message if the table doesn't exist.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> > Hello
> >
> > Trying to get some fragmentation info back on SQL2000 tables but no results
> > are returned. Is this a bug?
> >
> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> > ALL_INDEXES, NO_INFOMSGS
> >
> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >
> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >
> > -- all above tables exist and have clustered index with millions of rows.
> >
> > tks
> >
> >
> > all queries produce no result:
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
>|||What happens if you omit the single quotes? As in DBCC SHOWCONTIG
(MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
2000 BOL, it does not indicate the use of single quotes around the object
name, but does in the SQL Server 2005 topics.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>I am certain query is executed. Have also tried osql - still no results,
>just
> as below. My SQL2005 table returns results but NOT SQL2000
> 1> use eqmclog
> 2> go
> 1> dbcc showcontig('providersourceinfo')
> 2> go
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> 1>
> very strange this...
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Are you certain you really execute the queries? Press the "execute"
>> button? Also, can you try using
>> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> exist.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> > Hello
>> >
>> > Trying to get some fragmentation info back on SQL2000 tables but no
>> > results
>> > are returned. Is this a bug?
>> >
>> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> > ALL_INDEXES, NO_INFOMSGS
>> >
>> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >
>> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >
>> > -- all above tables exist and have clustered index with millions of
>> > rows.
>> >
>> > tks
>> >
>> >
>> > all queries produce no result:
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA|||no, omitting quotes makes no difference. MS uses quotes themselves in their
BOL samples.
Just to make sure I have the correct tables:
select 'DBCC SHOWCONTIG ('+name+')'
from sysobjects
where type = 'u'
DBCC SHOWCONTIG (DataRecoveryLog)
DBCC SHOWCONTIG (ProviderSourceInfo)
DBCC SHOWCONTIG (MsgAudit_CBOT)
DBCC SHOWCONTIG (MsgAudit_KCBT)
DBCC SHOWCONTIG (MsgAudit_MGEX)
DBCC SHOWCONTIG (MsgAudit_WCE)
DBCC SHOWCONTIG (MsgAudit_JADE)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
-- cranfield, DBA
"Gail Erickson [MS]" wrote:
> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> 2000 BOL, it does not indicate the use of single quotes around the object
> name, but does in the SQL Server 2005 topics.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >I am certain query is executed. Have also tried osql - still no results,
> >just
> > as below. My SQL2005 table returns results but NOT SQL2000
> >
> > 1> use eqmclog
> > 2> go
> > 1> dbcc showcontig('providersourceinfo')
> > 2> go
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > 1>
> >
> > very strange this...
> > --
> > -- cranfield, DBA
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Are you certain you really execute the queries? Press the "execute"
> >> button? Also, can you try using
> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> exist.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> > Hello
> >> >
> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> > results
> >> > are returned. Is this a bug?
> >> >
> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> > ALL_INDEXES, NO_INFOMSGS
> >> >
> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >
> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >
> >> > -- all above tables exist and have clustered index with millions of
> >> > rows.
> >> >
> >> > tks
> >> >
> >> >
> >> > all queries produce no result:
> >> >
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> >
> >> >
> >> >
> >> > --
> >> > -- cranfield, DBA
> >>
>
>|||Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
TABLERESULTS, you get text back (use the correct tab in QA results).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> no, omitting quotes makes no difference. MS uses quotes themselves in their
> BOL samples.
> Just to make sure I have the correct tables:
> select 'DBCC SHOWCONTIG ('+name+')'
> from sysobjects
> where type = 'u'
> DBCC SHOWCONTIG (DataRecoveryLog)
> DBCC SHOWCONTIG (ProviderSourceInfo)
> DBCC SHOWCONTIG (MsgAudit_CBOT)
> DBCC SHOWCONTIG (MsgAudit_KCBT)
> DBCC SHOWCONTIG (MsgAudit_MGEX)
> DBCC SHOWCONTIG (MsgAudit_WCE)
> DBCC SHOWCONTIG (MsgAudit_JADE)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> --
> -- cranfield, DBA
>
> "Gail Erickson [MS]" wrote:
>> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> 2000 BOL, it does not indicate the use of single quotes around the object
>> name, but does in the SQL Server 2005 topics.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no rights
>> Download the latest version of Books Online from
>> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >I am certain query is executed. Have also tried osql - still no results,
>> >just
>> > as below. My SQL2005 table returns results but NOT SQL2000
>> >
>> > 1> use eqmclog
>> > 2> go
>> > 1> dbcc showcontig('providersourceinfo')
>> > 2> go
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > 1>
>> >
>> > very strange this...
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Are you certain you really execute the queries? Press the "execute"
>> >> button? Also, can you try using
>> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> exist.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> > Hello
>> >> >
>> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> > results
>> >> > are returned. Is this a bug?
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >
>> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >
>> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >
>> >> > -- all above tables exist and have clustered index with millions of
>> >> > rows.
>> >> >
>> >> > tks
>> >> >
>> >> >
>> >> > all queries produce no result:
>> >> >
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > -- cranfield, DBA
>> >>
>>|||Hi Tibor
I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
not return results for empty tables.
doh!
--
-- cranfield, DBA
"Tibor Karaszi" wrote:
> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
> TABLERESULTS, you get text back (use the correct tab in QA results).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
> > no, omitting quotes makes no difference. MS uses quotes themselves in their
> > BOL samples.
> >
> > Just to make sure I have the correct tables:
> >
> > select 'DBCC SHOWCONTIG ('+name+')'
> > from sysobjects
> > where type = 'u'
> >
> > DBCC SHOWCONTIG (DataRecoveryLog)
> > DBCC SHOWCONTIG (ProviderSourceInfo)
> > DBCC SHOWCONTIG (MsgAudit_CBOT)
> > DBCC SHOWCONTIG (MsgAudit_KCBT)
> > DBCC SHOWCONTIG (MsgAudit_MGEX)
> > DBCC SHOWCONTIG (MsgAudit_WCE)
> > DBCC SHOWCONTIG (MsgAudit_JADE)
> >
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> >
> > --
> > -- cranfield, DBA
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
> >> 2000 BOL, it does not indicate the use of single quotes around the object
> >> name, but does in the SQL Server 2005 topics.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no rights
> >> Download the latest version of Books Online from
> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
> >>
> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
> >> >I am certain query is executed. Have also tried osql - still no results,
> >> >just
> >> > as below. My SQL2005 table returns results but NOT SQL2000
> >> >
> >> > 1> use eqmclog
> >> > 2> go
> >> > 1> dbcc showcontig('providersourceinfo')
> >> > 2> go
> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> > system administrator.
> >> > 1>
> >> >
> >> > very strange this...
> >> > --
> >> > -- cranfield, DBA
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Are you certain you really execute the queries? Press the "execute"
> >> >> button? Also, can you try using
> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
> >> >> exist.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
> >> >> > Hello
> >> >> >
> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
> >> >> > results
> >> >> > are returned. Is this a bug?
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
> >> >> > ALL_INDEXES, NO_INFOMSGS
> >> >> >
> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
> >> >> >
> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
> >> >> >
> >> >> > -- all above tables exist and have clustered index with millions of
> >> >> > rows.
> >> >> >
> >> >> > tks
> >> >> >
> >> >> >
> >> >> > all queries produce no result:
> >> >> >
> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
> >> >> > system administrator.
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > -- cranfield, DBA
> >> >>
> >>
> >>
> >>
>|||Ouch... That was not obvious! Thanks for posting back. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cranfield" <alan_cranfield@.msn.co.za> wrote in message
news:FF387413-A310-4994-8A65-B118987CBF42@.microsoft.com...
> Hi Tibor
> I have found the problem! These tables have 0 rows. DBCC SHOWCONTIG does
> not return results for empty tables.
> doh!
> --
> -- cranfield, DBA
>
> "Tibor Karaszi" wrote:
>> Can you run it in the pubs database? I ran your SELECT (which produces the DBCC commands) in pubs
>> against a SQL2K (8.00.194), and it produced results. Also, be careful to note that without
>> TABLERESULTS, you get text back (use the correct tab in QA results).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> news:97BDC55E-5F6E-4025-B576-0441777497DB@.microsoft.com...
>> > no, omitting quotes makes no difference. MS uses quotes themselves in their
>> > BOL samples.
>> >
>> > Just to make sure I have the correct tables:
>> >
>> > select 'DBCC SHOWCONTIG ('+name+')'
>> > from sysobjects
>> > where type = 'u'
>> >
>> > DBCC SHOWCONTIG (DataRecoveryLog)
>> > DBCC SHOWCONTIG (ProviderSourceInfo)
>> > DBCC SHOWCONTIG (MsgAudit_CBOT)
>> > DBCC SHOWCONTIG (MsgAudit_KCBT)
>> > DBCC SHOWCONTIG (MsgAudit_MGEX)
>> > DBCC SHOWCONTIG (MsgAudit_WCE)
>> > DBCC SHOWCONTIG (MsgAudit_JADE)
>> >
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> > DBCC execution completed. If DBCC printed error messages, contact your
>> > system administrator.
>> >
>> >
>> >
>> > --
>> > -- cranfield, DBA
>> >
>> >
>> > "Gail Erickson [MS]" wrote:
>> >
>> >> What happens if you omit the single quotes? As in DBCC SHOWCONTIG
>> >> (MsgAudit_CBOT)? Looking at the syntax for DBCC statements in SQL Server
>> >> 2000 BOL, it does not indicate the use of single quotes around the object
>> >> name, but does in the SQL Server 2005 topics.
>> >>
>> >> --
>> >> Gail Erickson [MS]
>> >> SQL Server Documentation Team
>> >> This posting is provided "AS IS" with no warranties, and confers no rights
>> >> Download the latest version of Books Online from
>> >> http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
>> >>
>> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> news:2FAEA2D1-46ED-4248-BB68-8C0B62B90E52@.microsoft.com...
>> >> >I am certain query is executed. Have also tried osql - still no results,
>> >> >just
>> >> > as below. My SQL2005 table returns results but NOT SQL2000
>> >> >
>> >> > 1> use eqmclog
>> >> > 2> go
>> >> > 1> dbcc showcontig('providersourceinfo')
>> >> > 2> go
>> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> > system administrator.
>> >> > 1>
>> >> >
>> >> > very strange this...
>> >> > --
>> >> > -- cranfield, DBA
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Are you certain you really execute the queries? Press the "execute"
>> >> >> button? Also, can you try using
>> >> >> OSQL.EXE? That DBCC should give you an error message if the table doesn't
>> >> >> exist.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "Cranfield" <alan_cranfield@.msn.co.za> wrote in message
>> >> >> news:A7E3358D-4814-49C6-9E48-451F6CB20EA1@.microsoft.com...
>> >> >> > Hello
>> >> >> >
>> >> >> > Trying to get some fragmentation info back on SQL2000 tables but no
>> >> >> > results
>> >> >> > are returned. Is this a bug?
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.[MsgAudit_CBOT]') WITH FAST, TABLERESULTS,
>> >> >> > ALL_INDEXES, NO_INFOMSGS
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('MsgAudit_CBOT')
>> >> >> >
>> >> >> > DBCC SHOWCONTIG ('dbo.MsgAudit_CBOT')
>> >> >> >
>> >> >> > -- all above tables exist and have clustered index with millions of
>> >> >> > rows.
>> >> >> >
>> >> >> > tks
>> >> >> >
>> >> >> >
>> >> >> > all queries produce no result:
>> >> >> >
>> >> >> > DBCC execution completed. If DBCC printed error messages, contact your
>> >> >> > system administrator.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > -- cranfield, DBA
>> >> >>
>> >>
>> >>
>> >>

DBCC SHOWCONTIG question

Hi Freinds,
SQL 2000
I have performance issues on my database and got to this point that DBCC
SHOWCONTIG result for my tables are:
DBCC SHOWCONTIG scanning 'dup_source_title' table...
Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 19296
- Extents Scanned.......................: 2429
- Extent Switches.......................: 2428
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
- Logical Scan Fragmentation ..............: 0.06%
- Extent Scan Fragmentation ...............: 1.03%
- Avg. Bytes Free per Page................: 7265.6
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 180459
- Extents Scanned.......................: 22605
- Extent Switches.......................: 23105
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
- Logical Scan Fragmentation ..............: 3.78%
- Extent Scan Fragmentation ...............: 12.67%
- Avg. Bytes Free per Page................: 656.4
- Avg. Page Density (full)................: 91.89%
is this good result? bad result? which statistic is not good and need
tuning? and how , what should I do to tune it?
Thanks in advance,
PatThey are both fine in regards to fragmentation but the first one is only 10%
full where as the second is 90% full. There is no right or wrong number for
fullness since it depends a lot on how the index is used. But 10% is
probably way too low of a fill factor. Have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have performance issues on my database and got to this point that DBCC
> SHOWCONTIG result for my tables are:
> DBCC SHOWCONTIG scanning 'dup_source_title' table...
> Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 19296
> - Extents Scanned.......................: 2429
> - Extent Switches.......................: 2428
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
> - Logical Scan Fragmentation ..............: 0.06%
> - Extent Scan Fragmentation ...............: 1.03%
> - Avg. Bytes Free per Page................: 7265.6
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
> Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 180459
> - Extents Scanned.......................: 22605
> - Extent Switches.......................: 23105
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
> - Logical Scan Fragmentation ..............: 3.78%
> - Extent Scan Fragmentation ...............: 12.67%
> - Avg. Bytes Free per Page................: 656.4
> - Avg. Page Density (full)................: 91.89%
> is this good result? bad result? which statistic is not good and need
> tuning? and how , what should I do to tune it?
> Thanks in advance,
> Pat
>|||Thank you very much for the answer,
I am struggling with the performance now and need to do smt to get the db
running.
What does Scan Density [Best Count:Actual Count] show? Should it be low ?
High?
Where are the main places that Ihave to look to tune the database?
Thanks again ina advance ,
Pat
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
> They are both fine in regards to fragmentation but the first one is only
> 10% full where as the second is 90% full. There is no right or wrong
> number for fullness since it depends a lot on how the index is used. But
> 10% is probably way too low of a fill factor. Have a look at these:
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||The 2nd link I gave you is all about DBCC SHOWCONTIG and should answer most
if not all of your questions on that topic. The other links are extremely
beneficial as well. But fragmentation is probably not the first place you
should look if you are having performance issues. Have a look at these to
help you narrow down the culprits:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance Checklist
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:ej0DZfcLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Thank you very much for the answer,
> I am struggling with the performance now and need to do smt to get the db
> running.
> What does Scan Density [Best Count:Actual Count] show? Should it be low ?
> High?
> Where are the main places that Ihave to look to tune the database?
> Thanks again ina advance ,
> Pat
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
>

Thursday, March 22, 2012

DBCC SHOWCONTIG

How can I see the SHOWCONTIG on all my tables in my database. Is there quick
way?
search online for ms_foreachtable.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:uu1nIWcsHHA.3556@.TK2MSFTNGP05.phx.gbl...
> How can I see the SHOWCONTIG on all my tables in my database. Is there
> quick way?
>
|||Just do a 'dbcc showcontig' with no parameters and it will process all
tables (SQL 2000 onwards). Add WITH ALL_INDEXES to pick up all the
non-clustered indexes too, and WITH TABLERESULTS for easier to correlate
output. Look in Example E I put in BOL for DBCC SHOWCONTIG for a script that
processes all tables.
In SQL 2005, checkout the new DMV, sys.dm_db_index_physical_stats, which has
similar options and can be set to run on all databases.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
<msnews.microsoft.com> wrote in message
news:uu1nIWcsHHA.3556@.TK2MSFTNGP05.phx.gbl...
> How can I see the SHOWCONTIG on all my tables in my database. Is there
> quick way?
>

DBCC SHOWCONTIG

How can I see the SHOWCONTIG on all my tables in my database. Is there quick
way?search online for ms_foreachtable.
--
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:uu1nIWcsHHA.3556@.TK2MSFTNGP05.phx.gbl...
> How can I see the SHOWCONTIG on all my tables in my database. Is there
> quick way?
>|||Just do a 'dbcc showcontig' with no parameters and it will process all
tables (SQL 2000 onwards). Add WITH ALL_INDEXES to pick up all the
non-clustered indexes too, and WITH TABLERESULTS for easier to correlate
output. Look in Example E I put in BOL for DBCC SHOWCONTIG for a script that
processes all tables.
In SQL 2005, checkout the new DMV, sys.dm_db_index_physical_stats, which has
similar options and can be set to run on all databases.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
<msnews.microsoft.com> wrote in message
news:uu1nIWcsHHA.3556@.TK2MSFTNGP05.phx.gbl...
> How can I see the SHOWCONTIG on all my tables in my database. Is there
> quick way?
>

DBCC SHOWCONTIG

How can I see the SHOWCONTIG on all my tables in my database. Is there quick
way?search online for ms_foreachtable.
TheSQLGuru
President
Indicium Resources, Inc.
<msnews.microsoft.com> wrote in message
news:uu1nIWcsHHA.3556@.TK2MSFTNGP05.phx.gbl...
> How can I see the SHOWCONTIG on all my tables in my database. Is there
> quick way?
>|||Just do a 'dbcc showcontig' with no parameters and it will process all
tables (SQL 2000 onwards). Add WITH ALL_INDEXES to pick up all the
non-clustered indexes too, and WITH TABLERESULTS for easier to correlate
output. Look in Example E I put in BOL for DBCC SHOWCONTIG for a script that
processes all tables.
In SQL 2005, checkout the new DMV, sys.dm_db_index_physical_stats, which has
similar options and can be set to run on all databases.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
<msnews.microsoft.com> wrote in message
news:uu1nIWcsHHA.3556@.TK2MSFTNGP05.phx.gbl...
> How can I see the SHOWCONTIG on all my tables in my database. Is there
> quick way?
>sql

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 ***

Sunday, March 11, 2012

DBCC INDEXDEFRAG fills the log

Hi:
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions are
highly appreciated.
Thanks
Turning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks

DBCC INDEXDEFRAG fills the log

Hi:
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions ar
e
highly appreciated.
ThanksTurning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks

DBCC INDEXDEFRAG fills the log

Hi:
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions are
highly appreciated.
ThanksTurning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks

DBCC INDEXDEFRAG

Hi Guru,
One of my vendor database has about 500 tables where owner not belong
to dbo. I'm having a hard time to do online defrag from M-F because my
code is not working. Please look at my code and modify it when owner
not belong to DBO.
USE ObjectManager
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[uspOM_OnlineFragmentation]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.uspOM_OnlineFragmentation
@.dbname sysname
AS
SET NOCOUNT ON
CREATE TABLE #UserTables(
[id] INT,
[name] VARCHAR(60)
)
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
IndexId INT, [Level] INT, Pages INT, [Rows] INT,
MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes FLOAT,
AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
ActualCount INT, LogicalFragmentation FLOAT,
ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
[id] INT IDENTITY,
ObjectName VARCHAR(255),
IndexName VARCHAR(255),
ScanDensity FLOAT
)
--Identify all user tables in the current database to analyze
-- fragmentation
INSERT INTO #UserTables([id], [name])
EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
xtype = ''U'' and [name] <> ''dtproperties''')
--SELECT [id], [name] INTO #UserTables
-- FROM sysobjects
-- WHERE type = 'U'
-- ORDER BY [id]
--Determine fragmentation of every user table/index
DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #UserTables
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id], @.name=[name]
FROM #UserTables
WHERE [id] > @.id
INSERT INTO #FragmentationResult
EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
ALL_INDEXES, TABLERESULTS')
SET @.TableCnt = @.TableCnt - 1
END
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE IndexID NOT IN(0,255)
AND ScanDensity < 40 --Scan Density is low
AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult
--Defragment tables/indexes with high fragmentation
DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id]
, @.oname = ObjectName
, @.iname = IndexName
, @.sdensity = ScanDensity
FROM #Defragmentation
WHERE [id] > @.id
PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
+ '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
--DBCC DBREINDEX(@.oname, @.iname)
DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
SET @.TableCnt = @.TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks,
Silaphet,
Hi Silaphet
It's not the code that needs to change, but the roles assigned to whomever
is running this code.
DBO is not a role. It is a user name. You can assign the users who need to
run this to the db_owner or db_ddladmin role. Table owners can only defrag
or showcontig on their own tables.
FROM BOL:
Permissions
DBCC INDEXDEFRAG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database role, and the table owner,
and are not transferable.
DBCC SHOWCONTIG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database roles, and the table
owner, and are not transferable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<smounkhaty@.bremer.com> wrote in message
news:1133496455.993570.168870@.g44g2000cwa.googlegr oups.com...
> Hi Guru,
> One of my vendor database has about 500 tables where owner not belong
> to dbo. I'm having a hard time to do online defrag from M-F because my
> code is not working. Please look at my code and modify it when owner
> not belong to DBO.
> USE ObjectManager
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[uspOM_OnlineFragmentation]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROC dbo.uspOM_OnlineFragmentation
> @.dbname sysname
> AS
> SET NOCOUNT ON
> CREATE TABLE #UserTables(
> [id] INT,
> [name] VARCHAR(60)
> )
> --Create temporary table to hold DBCC SHOWCONTIG output
> CREATE TABLE #FragmentationResult(
> ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
> IndexId INT, [Level] INT, Pages INT, [Rows] INT,
> MinimumRecordSize INT, MaximumRecordSize INT,
> AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
> ExtentSwitches INT, AverageFreeBytes FLOAT,
> AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
> ActualCount INT, LogicalFragmentation FLOAT,
> ExtentFragmentation FLOAT
> )
> --Create temporary table to hold tables/indexes that require
> -- defragmentation
> CREATE TABLE #Defragmentation(
> [id] INT IDENTITY,
> ObjectName VARCHAR(255),
> IndexName VARCHAR(255),
> ScanDensity FLOAT
> )
> --Identify all user tables in the current database to analyze
> -- fragmentation
> INSERT INTO #UserTables([id], [name])
> EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
> xtype = ''U'' and [name] <> ''dtproperties''')
> --SELECT [id], [name] INTO #UserTables
> -- FROM sysobjects
> -- WHERE type = 'U'
> -- ORDER BY [id]
> --Determine fragmentation of every user table/index
> DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #UserTables
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id], @.name=[name]
> FROM #UserTables
> WHERE [id] > @.id
> INSERT INTO #FragmentationResult
> EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
> ALL_INDEXES, TABLERESULTS')
> SET @.TableCnt = @.TableCnt - 1
> END
> --Determine user tables/indexes that require defragmentation
> INSERT INTO #Defragmentation
> SELECT ObjectName, IndexName, ScanDensity
> FROM #FragmentationResult
> WHERE IndexID NOT IN(0,255)
> AND ScanDensity < 40 --Scan Density is low
> AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
> AND PAGES > 8 --Not a very small table
> DROP TABLE #FragmentationResult
> --Defragment tables/indexes with high fragmentation
> DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id]
> , @.oname = ObjectName
> , @.iname = IndexName
> , @.sdensity = ScanDensity
> FROM #Defragmentation
> WHERE [id] > @.id
> PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
> PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
> + '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
> --DBCC DBREINDEX(@.oname, @.iname)
> DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
> SET @.TableCnt = @.TableCnt - 1
> END
> --Release resources
> DROP TABLE #UserTables
> DROP TABLE #Defragmentation
> SET NOCOUNT OFF
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Thanks,
> Silaphet,
>

Thursday, March 8, 2012

DBCC INDEXDEFRAG

Hi Guru,
One of my vendor database has about 500 tables where owner not belong
to dbo. I'm having a hard time to do online defrag from M-F because my
code is not working. Please look at my code and modify it when owner
not belong to DBO.
USE ObjectManager
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[uspOM_OnlineFragmentation]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.uspOM_OnlineFragmentation
@.dbname sysname
AS
SET NOCOUNT ON
CREATE TABLE #UserTables(
[id] INT,
[name] VARCHAR(60)
)
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
IndexId INT, [Level] INT, Pages INT, [Rows] INT,
MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes FLOAT,
AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
ActualCount INT, LogicalFragmentation FLOAT,
ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
[id] INT IDENTITY,
ObjectName VARCHAR(255),
IndexName VARCHAR(255),
ScanDensity FLOAT
)
--Identify all user tables in the current database to analyze
-- fragmentation
INSERT INTO #UserTables([id], [name])
EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
xtype = ''U'' and [name] <> ''dtproperties''')
--SELECT [id], [name] INTO #UserTables
-- FROM sysobjects
-- WHERE type = 'U'
-- ORDER BY [id]
--Determine fragmentation of every user table/index
DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #UserTables
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id], @.name=[name]
FROM #UserTables
WHERE [id] > @.id
INSERT INTO #FragmentationResult
EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
ALL_INDEXES, TABLERESULTS')
SET @.TableCnt = @.TableCnt - 1
END
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE IndexID NOT IN(0,255)
AND ScanDensity < 40 --Scan Density is low
AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult
--Defragment tables/indexes with high fragmentation
DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id]
, @.oname = ObjectName
, @.iname = IndexName
, @.sdensity = ScanDensity
FROM #Defragmentation
WHERE [id] > @.id
PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
+ '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
--DBCC DBREINDEX(@.oname, @.iname)
DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
SET @.TableCnt = @.TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks,
Silaphet,Hi Silaphet
It's not the code that needs to change, but the roles assigned to whomever
is running this code.
DBO is not a role. It is a user name. You can assign the users who need to
run this to the db_owner or db_ddladmin role. Table owners can only defrag
or showcontig on their own tables.
--
FROM BOL:
Permissions
DBCC INDEXDEFRAG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database role, and the table owner,
and are not transferable.
DBCC SHOWCONTIG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database roles, and the table
owner, and are not transferable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<smounkhaty@.bremer.com> wrote in message
news:1133496455.993570.168870@.g44g2000cwa.googlegroups.com...
> Hi Guru,
> One of my vendor database has about 500 tables where owner not belong
> to dbo. I'm having a hard time to do online defrag from M-F because my
> code is not working. Please look at my code and modify it when owner
> not belong to DBO.
> USE ObjectManager
> GO
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[uspOM_OnlineFragmentation]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROC dbo.uspOM_OnlineFragmentation
> @.dbname sysname
> AS
> SET NOCOUNT ON
> CREATE TABLE #UserTables(
> [id] INT,
> [name] VARCHAR(60)
> )
> --Create temporary table to hold DBCC SHOWCONTIG output
> CREATE TABLE #FragmentationResult(
> ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
> IndexId INT, [Level] INT, Pages INT, [Rows] INT,
> MinimumRecordSize INT, MaximumRecordSize INT,
> AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
> ExtentSwitches INT, AverageFreeBytes FLOAT,
> AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
> ActualCount INT, LogicalFragmentation FLOAT,
> ExtentFragmentation FLOAT
> )
> --Create temporary table to hold tables/indexes that require
> -- defragmentation
> CREATE TABLE #Defragmentation(
> [id] INT IDENTITY,
> ObjectName VARCHAR(255),
> IndexName VARCHAR(255),
> ScanDensity FLOAT
> )
> --Identify all user tables in the current database to analyze
> -- fragmentation
> INSERT INTO #UserTables([id], [name])
> EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
> xtype = ''U'' and [name] <> ''dtproperties''')
> --SELECT [id], [name] INTO #UserTables
> -- FROM sysobjects
> -- WHERE type = 'U'
> -- ORDER BY [id]
> --Determine fragmentation of every user table/index
> DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #UserTables
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id], @.name=[name]
> FROM #UserTables
> WHERE [id] > @.id
> INSERT INTO #FragmentationResult
> EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
> ALL_INDEXES, TABLERESULTS')
> SET @.TableCnt = @.TableCnt - 1
> END
> --Determine user tables/indexes that require defragmentation
> INSERT INTO #Defragmentation
> SELECT ObjectName, IndexName, ScanDensity
> FROM #FragmentationResult
> WHERE IndexID NOT IN(0,255)
> AND ScanDensity < 40 --Scan Density is low
> AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
> AND PAGES > 8 --Not a very small table
> DROP TABLE #FragmentationResult
> --Defragment tables/indexes with high fragmentation
> DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id]
> , @.oname = ObjectName
> , @.iname = IndexName
> , @.sdensity = ScanDensity
> FROM #Defragmentation
> WHERE [id] > @.id
> PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
> PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
> + '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
> --DBCC DBREINDEX(@.oname, @.iname)
> DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
> SET @.TableCnt = @.TableCnt - 1
> END
> --Release resources
> DROP TABLE #UserTables
> DROP TABLE #Defragmentation
> SET NOCOUNT OFF
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Thanks,
> Silaphet,
>

DBCC INDEXDEFRAG

Hi Guru,
One of my vendor database has about 500 tables where owner not belong
to dbo. I'm having a hard time to do online defrag from M-F because my
code is not working. Please look at my code and modify it when owner
not belong to DBO.
USE ObjectManager
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(i
d,
N'IsProcedure') = 1)
drop procedure [dbo].[uspOM_OnlineFragmentation]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.uspOM_OnlineFragmentation
@.dbname sysname
AS
SET NOCOUNT ON
CREATE TABLE #UserTables(
[id] INT,
[name] VARCHAR(60)
)
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
IndexId INT, [Level] INT, Pages INT, [Rows] INT,
MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes FLOAT,
AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
ActualCount INT, LogicalFragmentation FLOAT,
ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
[id] INT IDENTITY,
ObjectName VARCHAR(255),
IndexName VARCHAR(255),
ScanDensity FLOAT
)
--Identify all user tables in the current database to analyze
-- fragmentation
INSERT INTO #UserTables([id], [name])
EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
xtype = ''U'' and [name] <> ''dtproperties''')
--SELECT [id], [name] INTO #UserTables
-- FROM sysobjects
-- WHERE type = 'U'
-- ORDER BY [id]
--Determine fragmentation of every user table/index
DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #UserTables
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id], @.name=[name]
FROM #UserTables
WHERE [id] > @.id
INSERT INTO #FragmentationResult
EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
ALL_INDEXES, TABLERESULTS')
SET @.TableCnt = @.TableCnt - 1
END
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE IndexID NOT IN(0,255)
AND ScanDensity < 40 --Scan Density is low
AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult
--Defragment tables/indexes with high fragmentation
DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id]
, @.oname = ObjectName
, @.iname = IndexName
, @.sdensity = ScanDensity
FROM #Defragmentation
WHERE [id] > @.id
PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
+ '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
--DBCC DBREINDEX(@.oname, @.iname)
DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
SET @.TableCnt = @.TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks,
Silaphet,Hi Silaphet
It's not the code that needs to change, but the roles assigned to whomever
is running this code.
DBO is not a role. It is a user name. You can assign the users who need to
run this to the db_owner or db_ddladmin role. Table owners can only defrag
or showcontig on their own tables.
FROM BOL:
Permissions
DBCC INDEXDEFRAG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database role, and the table owner,
and are not transferable.
DBCC SHOWCONTIG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database roles, and the table
owner, and are not transferable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<smounkhaty@.bremer.com> wrote in message
news:1133496455.993570.168870@.g44g2000cwa.googlegroups.com...
> Hi Guru,
> One of my vendor database has about 500 tables where owner not belong
> to dbo. I'm having a hard time to do online defrag from M-F because my
> code is not working. Please look at my code and modify it when owner
> not belong to DBO.
> USE ObjectManager
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY
(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[uspOM_OnlineFragmentation]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROC dbo.uspOM_OnlineFragmentation
> @.dbname sysname
> AS
> SET NOCOUNT ON
> CREATE TABLE #UserTables(
> [id] INT,
> [name] VARCHAR(60)
> )
> --Create temporary table to hold DBCC SHOWCONTIG output
> CREATE TABLE #FragmentationResult(
> ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
> IndexId INT, [Level] INT, Pages INT, [Rows] INT,
> MinimumRecordSize INT, MaximumRecordSize INT,
> AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
> ExtentSwitches INT, AverageFreeBytes FLOAT,
> AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
> ActualCount INT, LogicalFragmentation FLOAT,
> ExtentFragmentation FLOAT
> )
> --Create temporary table to hold tables/indexes that require
> -- defragmentation
> CREATE TABLE #Defragmentation(
> [id] INT IDENTITY,
> ObjectName VARCHAR(255),
> IndexName VARCHAR(255),
> ScanDensity FLOAT
> )
> --Identify all user tables in the current database to analyze
> -- fragmentation
> INSERT INTO #UserTables([id], [name])
> EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHE
RE
> xtype = ''U'' and [name] <> ''dtproperties''')
> --SELECT [id], [name] INTO #UserTables
> -- FROM sysobjects
> -- WHERE type = 'U'
> -- ORDER BY [id]
> --Determine fragmentation of every user table/index
> DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #UserTables
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id], @.name=[name]
> FROM #UserTables
> WHERE [id] > @.id
> INSERT INTO #FragmentationResult
> EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
> ALL_INDEXES, TABLERESULTS')
> SET @.TableCnt = @.TableCnt - 1
> END
> --Determine user tables/indexes that require defragmentation
> INSERT INTO #Defragmentation
> SELECT ObjectName, IndexName, ScanDensity
> FROM #FragmentationResult
> WHERE IndexID NOT IN(0,255)
> AND ScanDensity < 40 --Scan Density is low
> AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
> AND PAGES > 8 --Not a very small table
> DROP TABLE #FragmentationResult
> --Defragment tables/indexes with high fragmentation
> DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id]
> , @.oname = ObjectName
> , @.iname = IndexName
> , @.sdensity = ScanDensity
> FROM #Defragmentation
> WHERE [id] > @.id
> PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
> PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
> + '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
> --DBCC DBREINDEX(@.oname, @.iname)
> DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
> SET @.TableCnt = @.TableCnt - 1
> END
> --Release resources
> DROP TABLE #UserTables
> DROP TABLE #Defragmentation
> SET NOCOUNT OFF
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Thanks,
> Silaphet,
>

Wednesday, March 7, 2012

DBCC DBREINDEX question and problem

I have really bad database fragmentation, in some cases up to 95% fragmented. I continue to run DBCC DBREINDEX on these tables to try and fix this problem but from some reason, no matter how often I do it, I never see an increase in Scan Density. The tables in question do have more than 8 pages so I know that is not the issue. Anyone have any insight on this?

http://www.sql-server-performance.com/rd_index_fragmentation.asp

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

The above links should help you to understand the features available in SQL Server and resolve the defragmentation problems.

dbcc dbreindex on all tables/indexes and slow

This is what i ran and it was slow
declare reindex_cursor insensitive cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
and table_type = 'Base table'
declare @.table_name sysname
open reindex_cursor
fetch next from reindex_cursor into @.table_name
while (@.@.fetch_status = 0)
begin
print ('dbcc dbreindex(' + @.table_name + ')' )
exec ('dbcc dbreindex(' + @.table_name + ')' )
fetch next from reindex_cursor into @.table_name
end
close reindex_cursor
deallocate reindex_cursor
Could the problem be because of the insensitive that i mentioned in the
cursor declaration ? I can see that the DBCC is still running and not being
blocked but its way too slow. If I do cancel in between, will it rollback
everything ?For something like that I always use a STATIC cursor. You also don't need
to use dynamic sql as DBCC DBREINDEX will take a variable for the table
name. My guess why the operation is so slow is that your log and db files
probably don't have enough room in them for this and are constantly growing.
If your in full recovery mode you might want to backup your logs in between
so try something like this:
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Why you don't use an database maintance plan?
Ronald
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Now changing gears to the use of dynamic sql. How can i tell what needs a
dynamic sql and what can accept variables. Can you provide some examples and
how can i figure it out ? Also the definition of insensitive and static
seemed to be the same from BOL. Btw, i got the initial script from google
too.
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> For something like that I always use a STATIC cursor. You also don't need
> to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> name. My guess why the operation is so slow is that your log and db files
> probably don't have enough room in them for this and are constantly
growing.
> If your in full recovery mode you might want to backup your logs in
between
> so try something like this:
>
> SET NOCOUNT ON
> DECLARE @.TableName VARCHAR(100), @.Counter INT
> SET @.Counter = 1
> DECLARE curTables CURSOR STATIC LOCAL
> FOR
> SELECT Table_Name
> FROM Information_Schema.Tables
> WHERE Table_Type = 'BASE TABLE'
> OPEN curTables
> FETCH NEXT FROM curTables INTO @.TableName
> SET @.TableName = RTRIM(@.TableName)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT 'Reindexing ' + @.TableName
> DBCC DBREINDEX (@.TableName)
> SET @.Counter = @.Counter + 1
> -- Backup the Log every so often so as not to fill the log
> IF @.Counter % 10 = 0
> BEGIN
> BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
NOUNLOAD
> ,
> NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> NOFORMAT
> END
> FETCH NEXT FROM curTables INTO @.TableName
> END
> CLOSE curTables
> DEALLOCATE curTables
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>|||Sorry, I spaced on the Insensitive. As for when you can and when you can't
it isn't always spelled out but if you look at the entry in BOL it usually
gives a hint. If there are quotes around the option you can usually use a
variable.
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23CQ4liTsDHA.3416@.tk2msftngp13.phx.gbl...
> Now changing gears to the use of dynamic sql. How can i tell what needs a
> dynamic sql and what can accept variables. Can you provide some examples
and
> how can i figure it out ? Also the definition of insensitive and static
> seemed to be the same from BOL. Btw, i got the initial script from google
> too.
> Thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> > For something like that I always use a STATIC cursor. You also don't
need
> > to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> > name. My guess why the operation is so slow is that your log and db
files
> > probably don't have enough room in them for this and are constantly
> growing.
> > If your in full recovery mode you might want to backup your logs in
> between
> > so try something like this:
> >
> >
> > SET NOCOUNT ON
> >
> > DECLARE @.TableName VARCHAR(100), @.Counter INT
> >
> > SET @.Counter = 1
> >
> > DECLARE curTables CURSOR STATIC LOCAL
> > FOR
> > SELECT Table_Name
> > FROM Information_Schema.Tables
> > WHERE Table_Type = 'BASE TABLE'
> >
> > OPEN curTables
> > FETCH NEXT FROM curTables INTO @.TableName
> > SET @.TableName = RTRIM(@.TableName)
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > SELECT 'Reindexing ' + @.TableName
> >
> > DBCC DBREINDEX (@.TableName)
> >
> > SET @.Counter = @.Counter + 1
> > -- Backup the Log every so often so as not to fill the log
> > IF @.Counter % 10 = 0
> > BEGIN
> > BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
> NOUNLOAD
> > ,
> > NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> > NOFORMAT
> > END
> >
> > FETCH NEXT FROM curTables INTO @.TableName
> > END
> >
> > CLOSE curTables
> > DEALLOCATE curTables
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > > This is what i ran and it was slow
> > >
> > > declare reindex_cursor insensitive cursor for
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.TABLES
> > > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > > and table_type = 'Base table'
> > >
> > > declare @.table_name sysname
> > >
> > > open reindex_cursor
> > > fetch next from reindex_cursor into @.table_name
> > >
> > > while (@.@.fetch_status = 0)
> > > begin
> > > print ('dbcc dbreindex(' + @.table_name + ')' )
> > > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > > fetch next from reindex_cursor into @.table_name
> > > end
> > >
> > > close reindex_cursor
> > > deallocate reindex_cursor
> > >
> > > Could the problem be because of the insensitive that i mentioned in
the
> > > cursor declaration ? I can see that the DBCC is still running and not
> > being
> > > blocked but its way too slow. If I do cancel in between, will it
> rollback
> > > everything ?
> > >
> > >
> >
> >
>|||On a seperate note, I'd like to encourage you to read the excellent
whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
It will help you determine the best way to deal with fragmentation and
whetehr you need to do anything about it at all, rather than just rebuilding
every table in the database.
Regards,
Paul.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Thanks Paul, I should have included that again for completeness but I know
that link has been referred to him several times already<g>.
--
Andrew J. Kelly
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uwswsAisDHA.2244@.TK2MSFTNGP09.phx.gbl...
> On a seperate note, I'd like to encourage you to read the excellent
> whitepaper at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> It will help you determine the best way to deal with fragmentation and
> whetehr you need to do anything about it at all, rather than just
rebuilding
> every table in the database.
> Regards,
> Paul.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>

Thursday, March 29, 2012

DBCC SHRINKFILE

DBCC SHRINKFILE

DBCC SHRINKFILE

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

Sunday, March 25, 2012

DBCC SHOWCONTIG shows no result

DBCC SHOWCONTIG question

Thursday, March 22, 2012

DBCC SHOWCONTIG

DBCC SHOWCONTIG

DBCC SHOWCONTIG

dbcc reindex issue - - I dont understand!

Sunday, March 11, 2012

DBCC INDEXDEFRAG fills the log

DBCC INDEXDEFRAG fills the log

DBCC INDEXDEFRAG fills the log

DBCC INDEXDEFRAG

Thursday, March 8, 2012

DBCC INDEXDEFRAG

DBCC INDEXDEFRAG

Wednesday, March 7, 2012

DBCC DBREINDEX question and problem

dbcc dbreindex on all tables/indexes and slow

DBfile usage

Blog Archive

About Me