Delete Database Option

When I attempt to delete a database, there is a checkbox
saying - "Delete backup and restore history for the
database".
What does this option means ? Should I select it ?
Tha
If you don't care about the database, sure.
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha
|||Hi,
What does this option means ?
When ever you perform a Backup or restore of database or transaction log a
entry will put into
MSDB..backupfile, msdb..backupmediafamily,msdb..backupmediaset
tables,msdb..restorefile,msdb..restorefilegroup,ms db..restorehistory.
These are nothing but history of your backup and restore operation. So when
when you select this option
automatically all the history will be deleted form MSDB database for the
above tables.
Should I select it ?
Since you do not require even the database, you can select this option.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha

Delete Database Option

When I attempt to delete a database, there is a checkbox
saying - "Delete backup and restore history for the
database".
What does this option means ? Should I select it ?
ThaIf you don't care about the database, sure.
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx
.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha|||Hi,
What does this option means ?
When ever you perform a Backup or restore of database or transaction log a
entry will put into
MSDB..backupfile, msdb..backupmediafamily,msdb..backupmediaset
tables,msdb..restorefile,msdb..restorefilegroup,msdb..restorehistory.
These are nothing but history of your backup and restore operation. So when
when you select this option
automatically all the history will be deleted form MSDB database for the
above tables.
Should I select it ?
Since you do not require even the database, you can select this option.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx
.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha

Delete Database Option

When I attempt to delete a database, there is a checkbox
saying - "Delete backup and restore history for the
database".
What does this option means ? Should I select it ?
ThaIf you don't care about the database, sure.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha|||Hi,
What does this option means ?
When ever you perform a Backup or restore of database or transaction log a
entry will put into
MSDB..backupfile, msdb..backupmediafamily,msdb..backupmediaset
tables,msdb..restorefile,msdb..restorefilegroup,msdb..restorehistory.
These are nothing but history of your backup and restore operation. So when
when you select this option
automatically all the history will be deleted form MSDB database for the
above tables.
Should I select it ?
Since you do not require even the database, you can select this option.
--
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:20b9d01c4597e$661e7540$a401280a@.phx.gbl...
> When I attempt to delete a database, there is a checkbox
> saying - "Delete backup and restore history for the
> database".
> What does this option means ? Should I select it ?
> Tha

deisgn question

I have the following scenario in one of my applications.
Projects->Stages->Milestones->tasks
the above means a project has stages, stages have milestones and milestones
have tasks
I have found two possible structures in which I can design the tables.
tbl... refer to table.
schema 1
tblProjects tblStages tblMilestones tblTasks
ProjectId StageId MilestoneId taskId
ProjectId StageId MilestoneId
â?¦
â?¦
schema 2
tblProjects tblStages tblMilestones tblTasks
ProjectId ProjectId ProjectId ProjectId
StageId StageId StageId
MilestoneId MilestoneId
TakskId
I am just wondering which shema would be more appropriate.
Thanks a millionMathi
It is messy in your description
CREATE TABLE Projects
(
PrjId INT NOT NULL PRIMARY KEY,
.....
.....
)
CREATE TABLE Stages
(
StadeId INT NOT NULL PRIMARY KEY
PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
.....
)
CREATE TABLE Milestones
(
Milestid INT NOT NULL PRIMARY KEY,
StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
.....
)
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
.....
)
"Mathi" <Mathi@.discussions.microsoft.com> wrote in message
news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> I have the following scenario in one of my applications.
>
> Projects->Stages->Milestones->tasks
> the above means a project has stages, stages have milestones and
milestones
> have tasks
> I have found two possible structures in which I can design the tables.
> tbl... refer to table.
> schema 1
> tblProjects tblStages tblMilestones tblTasks
> ProjectId StageId MilestoneId taskId
> ProjectId StageId MilestoneId
> ?
> ?
> schema 2
> tblProjects tblStages tblMilestones tblTasks
> ProjectId ProjectId ProjectId ProjectId
> StageId StageId StageId
> MilestoneId MilestoneId
> TakskId
>
> I am just wondering which shema would be more appropriate.
> Thanks a million|||Correction
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELETE
CASCADE ON UPDATE CASCADE,
....
.....
)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Mathi
> It is messy in your description
> CREATE TABLE Projects
> (
> PrjId INT NOT NULL PRIMARY KEY,
> .....
> .....
> )
> CREATE TABLE Stages
> (
> StadeId INT NOT NULL PRIMARY KEY
> PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Milestones
> (
> Milestid INT NOT NULL PRIMARY KEY,
> StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
>
> "Mathi" <Mathi@.discussions.microsoft.com> wrote in message
> news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> > I have the following scenario in one of my applications.
> >
> >
> > Projects->Stages->Milestones->tasks
> > the above means a project has stages, stages have milestones and
> milestones
> > have tasks
> >
> > I have found two possible structures in which I can design the tables.
> > tbl... refer to table.
> >
> > schema 1
> >
> > tblProjects tblStages tblMilestones tblTasks
> > ProjectId StageId MilestoneId taskId
> > ProjectId StageId MilestoneId
> > ?
> > ?
> > schema 2
> >
> > tblProjects tblStages tblMilestones tblTasks
> > ProjectId ProjectId ProjectId ProjectId
> > StageId StageId StageId
> > MilestoneId MilestoneId
> > TakskId
> >
> >
> > I am just wondering which shema would be more appropriate.
> >
> > Thanks a million
>|||Thanks Uri
Basically my question was, which of the two schemas would be more
appropriate in terms of database design...
thanks
"Uri Dimant" wrote:
> Correction
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> ......
> )
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> > Mathi
> > It is messy in your description
> >
> > CREATE TABLE Projects
> > (
> > PrjId INT NOT NULL PRIMARY KEY,
> > .....
> > .....
> > )
> > CREATE TABLE Stages
> > (
> > StadeId INT NOT NULL PRIMARY KEY
> > PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
> > CASCADE ON UPDATE CASCADE,
> > .....
> > .....
> > )
> > CREATE TABLE Milestones
> > (
> > Milestid INT NOT NULL PRIMARY KEY,
> > StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> > CASCADE ON UPDATE CASCADE,
> > .....
> > .....
> > )
> > CREATE TABLE Tasks
> > (
> > TaskId INT NOT NULL PRIMARY KEY,
> > Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> > CASCADE ON UPDATE CASCADE,
> > .....
> > .....
> > )
> >
> >
> > "Mathi" <Mathi@.discussions.microsoft.com> wrote in message
> > news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> > > I have the following scenario in one of my applications.
> > >
> > >
> > > Projects->Stages->Milestones->tasks
> > > the above means a project has stages, stages have milestones and
> > milestones
> > > have tasks
> > >
> > > I have found two possible structures in which I can design the tables.
> > > tbl... refer to table.
> > >
> > > schema 1
> > >
> > > tblProjects tblStages tblMilestones tblTasks
> > > ProjectId StageId MilestoneId taskId
> > > ProjectId StageId MilestoneId
> > > â?¦
> > > â?¦
> > > schema 2
> > >
> > > tblProjects tblStages tblMilestones tblTasks
> > > ProjectId ProjectId ProjectId ProjectId
> > > StageId StageId StageId
> > > MilestoneId MilestoneId
> > > TakskId
> > >
> > >
> > > I am just wondering which shema would be more appropriate.
> > >
> > > Thanks a million
> >
> >
>
>

deisgn question

I have the following scenario in one of my applications.
Projects->Stages->Milestones->tasks
the above means a project has stages, stages have milestones and milestones
have tasks
I have found two possible structures in which I can design the tables.
tbl... refer to table.
schema 1
tblProjectstblStagestblMilestonestblTasks
ProjectIdStageIdMilestoneIdtaskId
ProjectIdStageIdMilestoneId
…
…
schema 2
tblProjectstblStagestblMilestonestblTasks
ProjectIdProjectIdProjectId ProjectId
StageIdStageIdStageId
MilestoneIdMilestoneId
TakskId
I am just wondering which shema would be more appropriate.
Thanks a million
Mathi
It is messy in your description
CREATE TABLE Projects
(
PrjId INT NOT NULL PRIMARY KEY,
.....
......
)
CREATE TABLE Stages
(
StadeId INT NOT NULL PRIMARY KEY
PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
......
)
CREATE TABLE Milestones
(
Milestid INT NOT NULL PRIMARY KEY,
StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
......
)
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
......
)
"Mathi" <Mathi@.discussions.microsoft.com> wrote in message
news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> I have the following scenario in one of my applications.
>
> Projects->Stages->Milestones->tasks
> the above means a project has stages, stages have milestones and
milestones
> have tasks
> I have found two possible structures in which I can design the tables.
> tbl... refer to table.
> schema 1
> tblProjects tblStages tblMilestones tblTasks
> ProjectId StageId MilestoneId taskId
> ProjectId StageId MilestoneId
>
> schema 2
> tblProjects tblStages tblMilestones tblTasks
> ProjectId ProjectId ProjectId ProjectId
> StageId StageId StageId
> MilestoneId MilestoneId
> TakskId
>
> I am just wondering which shema would be more appropriate.
> Thanks a million
|||Correction
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELETE
CASCADE ON UPDATE CASCADE,
.....
......
)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Mathi
> It is messy in your description
> CREATE TABLE Projects
> (
> PrjId INT NOT NULL PRIMARY KEY,
> .....
> .....
> )
> CREATE TABLE Stages
> (
> StadeId INT NOT NULL PRIMARY KEY
> PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Milestones
> (
> Milestid INT NOT NULL PRIMARY KEY,
> StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
>
> "Mathi" <Mathi@.discussions.microsoft.com> wrote in message
> news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> milestones
>
|||Thanks Uri
Basically my question was, which of the two schemas would be more
appropriate in terms of database design...
thanks
"Uri Dimant" wrote:

> Correction
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> ......
> )
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
>
>
sql

deisgn question

I have the following scenario in one of my applications.
Projects->Stages->Milestones->tasks
the above means a project has stages, stages have milestones and milestones
have tasks
I have found two possible structures in which I can design the tables.
tbl... refer to table.
schema 1
tblProjects tblStages tblMilestones tblT
asks
ProjectId StageId MilestoneId taskId
ProjectId StageId MilestoneId
…
…
schema 2
tblProjects tblStages tblMilestones tblT
asks
ProjectId ProjectId ProjectId ProjectId
StageId StageId StageId
MilestoneId MilestoneId
TakskId
I am just wondering which shema would be more appropriate.
Thanks a millionMathi
It is messy in your description
CREATE TABLE Projects
(
PrjId INT NOT NULL PRIMARY KEY,
....
.....
)
CREATE TABLE Stages
(
StadeId INT NOT NULL PRIMARY KEY
PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
CASCADE ON UPDATE CASCADE,
....
.....
)
CREATE TABLE Milestones
(
Milestid INT NOT NULL PRIMARY KEY,
StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
....
.....
)
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
CASCADE ON UPDATE CASCADE,
....
.....
)
"Mathi" <Mathi@.discussions.microsoft.com> wrote in message
news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> I have the following scenario in one of my applications.
>
> Projects->Stages->Milestones->tasks
> the above means a project has stages, stages have milestones and
milestones
> have tasks
> I have found two possible structures in which I can design the tables.
> tbl... refer to table.
> schema 1
> tblProjects tblStages tblMilestones tblTasks
> ProjectId StageId MilestoneId taskId
> ProjectId StageId MilestoneId
>
>
> schema 2
> tblProjects tblStages tblMilestones tblTasks
> ProjectId ProjectId ProjectId ProjectId
> StageId StageId StageId
> MilestoneId MilestoneId
> TakskId
>
> I am just wondering which shema would be more appropriate.
> Thanks a million|||Correction
CREATE TABLE Tasks
(
TaskId INT NOT NULL PRIMARY KEY,
Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELETE
CASCADE ON UPDATE CASCADE,
....
.....
)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
> Mathi
> It is messy in your description
> CREATE TABLE Projects
> (
> PrjId INT NOT NULL PRIMARY KEY,
> .....
> .....
> )
> CREATE TABLE Stages
> (
> StadeId INT NOT NULL PRIMARY KEY
> PrjId INT NOT NULL FOREIGN KEY REFERENCES Projects(PrjId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Milestones
> (
> Milestid INT NOT NULL PRIMARY KEY,
> StadeId INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Stages(StadeId )ON DELETE
> CASCADE ON UPDATE CASCADE,
> .....
> .....
> )
>
> "Mathi" <Mathi@.discussions.microsoft.com> wrote in message
> news:576AF7E0-78E7-4C46-9170-B3360A571E94@.microsoft.com...
> milestones
>|||Thanks Uri
Basically my question was, which of the two schemas would be more
appropriate in terms of database design...
thanks
"Uri Dimant" wrote:

> Correction
> CREATE TABLE Tasks
> (
> TaskId INT NOT NULL PRIMARY KEY,
> Milestid INT NOT NULL FOREIGN KEY REFERENCES Milestones(Milestid )ON DELE
TE
> CASCADE ON UPDATE CASCADE,
> .....
> ......
> )
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%232J7Go3hFHA.1248@.TK2MSFTNGP12.phx.gbl...
>
>

Defrag and Index

I am by no means an SQL expert, I need to figure out if doing a nightly
reindex defrags my indexes. From the data below it seems pretty obvious it
doesn't, but I am unsure.
I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
mess. Is this correct?
Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
than enthusiastic about doing an online defrag.
Do I need to do an DBCC DBREINDEX
So to recap...
1) Does night reindex, defrag?
2) Is the index below a disaster?
3) What is the best way to get this defragged? Keeping the index online
is preferred, but not necessarily required.
========================================================
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 948
- Extents Scanned.......................: 204
- Extent Switches.......................: 203
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
- Extent Scan Fragmentation ...............: 99.51%
- Avg. Bytes Free per Page................: 889.5
- Avg. Page Density (full)................: 89.01%
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned........................: 292
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 810.7
- Avg. Page Density (full)................: 89.98%
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul,
There are not to many pages, so I will not worry much. If you really want to
defrag that table, then you have to add a clustered index, or dump the data,
recreate the table and import the data.
AMB
"Paul Bergson [MVP-DS]" wrote:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious it
> doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
> than enthusiastic about doing an online defrag.
> Do I need to do an DBCC DBREINDEX
> So to recap...
> 1) Does night reindex, defrag?
> 2) Is the index below a disaster?
> 3) What is the best way to get this defragged? Keeping the index online
> is preferred, but not necessarily required.
> ========================================================> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 948
> - Extents Scanned.......................: 204
> - Extent Switches.......................: 203
> - Avg. Pages per Extent..................: 4.6
> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> - Extent Scan Fragmentation ...............: 99.51%
> - Avg. Bytes Free per Page................: 889.5
> - Avg. Page Density (full)................: 89.01%
> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> LEAF level scan performed.
> - Pages Scanned........................: 292
> - Extents Scanned.......................: 37
> - Extent Switches.......................: 36
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 810.7
> - Avg. Page Density (full)................: 89.98%
>
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||So the line
Extent Scan Fragmentation ...............: 99.51%
Is not bad?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> Paul,
> There are not to many pages, so I will not worry much. If you really want
> to
> defrag that table, then you have to add a clustered index, or dump the
> data,
> recreate the table and import the data.
>
> AMB
> "Paul Bergson [MVP-DS]" wrote:
>> I am by no means an SQL expert, I need to figure out if doing a nightly
>> reindex defrags my indexes. From the data below it seems pretty obvious
>> it
>> doesn't, but I am unsure.
>> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
>> a
>> mess. Is this correct?
>> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
>> less
>> than enthusiastic about doing an online defrag.
>> Do I need to do an DBCC DBREINDEX
>> So to recap...
>> 1) Does night reindex, defrag?
>> 2) Is the index below a disaster?
>> 3) What is the best way to get this defragged? Keeping the index
>> online
>> is preferred, but not necessarily required.
>> ========================================================>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
>> TABLE level scan performed.
>> - Pages Scanned........................: 948
>> - Extents Scanned.......................: 204
>> - Extent Switches.......................: 203
>> - Avg. Pages per Extent..................: 4.6
>> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
>> - Extent Scan Fragmentation ...............: 99.51%
>> - Avg. Bytes Free per Page................: 889.5
>> - Avg. Page Density (full)................: 89.01%
>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
>> LEAF level scan performed.
>> - Pages Scanned........................: 292
>> - Extents Scanned.......................: 37
>> - Extent Switches.......................: 36
>> - Avg. Pages per Extent..................: 7.9
>> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 810.7
>> - Avg. Page Density (full)................: 89.98%
>>
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Paul,
That number is not relevant to heaps (tables without clustered index) and is
meaningless when the index spans multiple files.
DBCC SHOWCONTIG
http://msdn2.microsoft.com/en-US/library/aa258803(SQL.80).aspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
AMB
"Paul Bergson [MVP-DS]" wrote:
> So the line
> Extent Scan Fragmentation ...............: 99.51%
> Is not bad?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> > Paul,
> >
> > There are not to many pages, so I will not worry much. If you really want
> > to
> > defrag that table, then you have to add a clustered index, or dump the
> > data,
> > recreate the table and import the data.
> >
> >
> > AMB
> >
> > "Paul Bergson [MVP-DS]" wrote:
> >
> >> I am by no means an SQL expert, I need to figure out if doing a nightly
> >> reindex defrags my indexes. From the data below it seems pretty obvious
> >> it
> >> doesn't, but I am unsure.
> >>
> >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
> >> a
> >> mess. Is this correct?
> >>
> >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> >> less
> >> than enthusiastic about doing an online defrag.
> >>
> >> Do I need to do an DBCC DBREINDEX
> >>
> >> So to recap...
> >> 1) Does night reindex, defrag?
> >> 2) Is the index below a disaster?
> >> 3) What is the best way to get this defragged? Keeping the index
> >> online
> >> is preferred, but not necessarily required.
> >>
> >> ========================================================> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> >>
> >> TABLE level scan performed.
> >>
> >> - Pages Scanned........................: 948
> >>
> >> - Extents Scanned.......................: 204
> >>
> >> - Extent Switches.......................: 203
> >>
> >> - Avg. Pages per Extent..................: 4.6
> >>
> >> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> >>
> >> - Extent Scan Fragmentation ...............: 99.51%
> >>
> >> - Avg. Bytes Free per Page................: 889.5
> >>
> >> - Avg. Page Density (full)................: 89.01%
> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> >>
> >> LEAF level scan performed.
> >>
> >> - Pages Scanned........................: 292
> >>
> >> - Extents Scanned.......................: 37
> >>
> >> - Extent Switches.......................: 36
> >>
> >> - Avg. Pages per Extent..................: 7.9
> >>
> >> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> >>
> >> - Logical Scan Fragmentation ..............: 0.00%
> >>
> >> - Extent Scan Fragmentation ...............: 0.00%
> >>
> >> - Avg. Bytes Free per Page................: 810.7
> >>
> >> - Avg. Page Density (full)................: 89.98%
> >>
> >>
> >> --
> >> Paul Bergson
> >> MVP - Directory Services
> >> MCT, MCSE, MCSA, Security+, BS CSci
> >> 2003, 2000 (Early Achiever), NT
> >>
> >> http://www.pbbergs.com
> >>
> >> Please no e-mails, any questions should be posted in the NewsGroup
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
>
>|||Paul Bergson [MVP-DS] (pbergson@.allete_nospam.com) writes:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious
> it doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
No, because that's not an index, but a heap, a table without a
clustered index. Heaps are quite prone to fragmentation, and your
table is not in the best shape with 4.6 pages per extent. But as
Alejandro pointed out the table is not that big, and it is not likely
to be a major problem.
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> less than enthusiastic about doing an online defrag.
Neither INDEXDEFRAG or DBREINDEX works on heap. You can build a clustered
index on the table and then drop it. Or you can just build a clustered index
and have it that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Definition of BCV

Could someone explain to me what the acroymn BCV means and how it is applied with an SQL database?
ThanksYou probably mean BCP, the Bulk Copy Program provided as a SQL Server utility with the SQL client tools.

You might actually mean BCV if you are using EMC storage, in that context it means Business Continuation Volume.

-PatP|||Thanks Pat for the response ... I did mean Business Continuation Volume because our client is using EMC storage. Can you explain to me what this actually is and how it is benefical as a tool to use?|||http://www.emc.com/products/product_pdfs/pdg/timefinder_pdg.pdf?emccomjsessionid=23057310987216 72334|||Actually, EMC does a better job explaining (http://www.emc.com/pdf/business_centers/windows/Microsoft_SQL.pdf) how to use BCVs with SQL Server than I can. We've never gotten them to work to my satisfaction.

The basic idea is that a BCV will allow you to mirror data in real time. In SQL 7.0 hooks were added, which were refined in SQL-2000 to allow an application to "freeze" a disk volume. This freeze would effectively flush the disk buffers to disk as of a specific point in time, almost as though the database had been stopped (to detach it). The theory is that you could mirror a BCV volume to a production volume, freeze the SQL files, break the mirror, then "thaw" the SQL files again, all without the end user knowing or caring that anything had ever happened.

Anywho, see the EMC document for a better description. While I love EMC in general, we (my firm working with EMC) were never able to make BCVs work in production.

-PatP