Delete a SQL Index in a table has become fragmented.

Can Anyone tell me what would happen if I delete an SQL Index in an table.
Will it re-index straight away? If so does the database become useable? will
I still be able to add and retrieve data while it re-index's?I assume you're asking about deleting and recreating an
index:
Technically, you could still work with a table using DBCC
DBREINDEX, although I wouldn't recommend it. That
command can hold locks on the table as it rebuilds the
index that would probably affect usage of the tables in
question. Personally, I schedule all reindexing off
hours and check to make sure that no one is logged in.
DBCC INDEXDEFRAG, on the other hand, is intended to be an
online operation. It will not hold locks for long
periods of time, so database usage will be relatively
unaffected.
DBCC INDEXDEFRAG may not be as effective as DBREINDEX --
the latter fully drops and recreates the index.
Check out BOL for more info on each statement.
Hope that helps.
>--Original Message--
>Can Anyone tell me what would happen if I delete an SQL
Index in an table.
>Will it re-index straight away? If so does the database
become useable? will
>I still be able to add and retrieve data while it re-
index's?
>
>.
>|||If you delete a nonclustered index, then the table and other indexes (if
applicable) are not affected.
If you delete a clustered index, then basically, the table is rebuild,
and all nonclustered indexes are rebuild. During this rebuild the
database is still usuable, but the table will be locked.
Gert-Jan
RockStdy wrote:
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable? will
> I still be able to add and retrieve data while it re-index's?|||If I use the DBReindex to reindex the index and then stop it with the
database table do a rollback? Or will I be able to process the table over a
period of time able to stop and start the DBReindex?
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable?
will
> I still be able to add and retrieve data while it re-index's?
>
>|||DBREINDEX is an all or nothing operation. If you stop it before it is
complete it will rollback to the way it was originally. DBCC INDEXDEFRAG is
what you want to use if you want to stop and restart it or if the operation
needs to be online.
--
Andrew J. Kelly
SQL Server MVP
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt2cv9$8vq$1@.news8.svr.pol.co.uk...
> If I use the DBReindex to reindex the index and then stop it with the
> database table do a rollback? Or will I be able to process the table over
a
> period of time able to stop and start the DBReindex?
>
> "RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
> news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> > Can Anyone tell me what would happen if I delete an SQL Index in an
table.
> >
> > Will it re-index straight away? If so does the database become useable?
> will
> > I still be able to add and retrieve data while it re-index's?
> >
> >
> >
>

Defraging a drive with a database on it

Hi all,
I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
contains the database has become massively fragmented in the last two months
since the system was put in. I'd like to run defrag on the drive and then
set it up as a scheduled task to run weekly to keep the fragmentation down.
I'm new to the SQL world and don't think that running defrag will mess
anything up but am worried about the "what if" factor. I'm learning SQL out
of a couple of books (no training budget). None of my books say anything
about defrag itself.
Can anyone reassure me that running defrag on a drive with a database on it
is safe? Is there anything I should look out for?
Thanks.
Dan
It is safe, as the database is stored on "regular" files (seen from the operating system's
perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
just in case. Also, consider why the files are so fragmented. For instance, read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> Hi all,
> I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> contains the database has become massively fragmented in the last two months
> since the system was put in. I'd like to run defrag on the drive and then
> set it up as a scheduled task to run weekly to keep the fragmentation down.
> I'm new to the SQL world and don't think that running defrag will mess
> anything up but am worried about the "what if" factor. I'm learning SQL out
> of a couple of books (no training budget). None of my books say anything
> about defrag itself.
> Can anyone reassure me that running defrag on a drive with a database on it
> is safe? Is there anything I should look out for?
> Thanks.
>
> Dan
>
|||Tibor,
Thanks for the quick reply. I'm feeling more comfortable about it now.
Dan
"Tibor Karaszi" wrote:

> It is safe, as the database is stored on "regular" files (seen from the operating system's
> perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
> just in case. Also, consider why the files are so fragmented. For instance, read this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
> news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
>
>

Defraging a drive with a database on it

Hi all,
I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
contains the database has become massively fragmented in the last two months
since the system was put in. I'd like to run defrag on the drive and then
set it up as a scheduled task to run weekly to keep the fragmentation down.
I'm new to the SQL world and don't think that running defrag will mess
anything up but am worried about the "what if" factor. I'm learning SQL out
of a couple of books (no training budget). None of my books say anything
about defrag itself.
Can anyone reassure me that running defrag on a drive with a database on it
is safe? Is there anything I should look out for?
Thanks.
DanIt is safe, as the database is stored on "regular" files (seen from the oper
ating system's
perspective). Just stop the SQL Server service before defragging. And do SQL
Server backups first,
just in case. Also, consider why the files are so fragmented. For instance,
read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> Hi all,
> I'm running MS SQL 2000 on a 2003 Server. I have found that the drive tha
t
> contains the database has become massively fragmented in the last two mont
hs
> since the system was put in. I'd like to run defrag on the drive and then
> set it up as a scheduled task to run weekly to keep the fragmentation down
.
> I'm new to the SQL world and don't think that running defrag will mess
> anything up but am worried about the "what if" factor. I'm learning SQL o
ut
> of a couple of books (no training budget). None of my books say anything
> about defrag itself.
> Can anyone reassure me that running defrag on a drive with a database on i
t
> is safe? Is there anything I should look out for?
> Thanks.
>
> Dan
>|||Tibor,
Thanks for the quick reply. I'm feeling more comfortable about it now.
Dan
"Tibor Karaszi" wrote:

> It is safe, as the database is stored on "regular" files (seen from the op
erating system's
> perspective). Just stop the SQL Server service before defragging. And do S
QL Server backups first,
> just in case. Also, consider why the files are so fragmented. For instance
, read this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
> news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
>
>

Defraging a drive with a database on it

Hi all,
I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
contains the database has become massively fragmented in the last two months
since the system was put in. I'd like to run defrag on the drive and then
set it up as a scheduled task to run weekly to keep the fragmentation down.
I'm new to the SQL world and don't think that running defrag will mess
anything up but am worried about the "what if" factor. I'm learning SQL out
of a couple of books (no training budget). None of my books say anything
about defrag itself.
Can anyone reassure me that running defrag on a drive with a database on it
is safe? Is there anything I should look out for?
Thanks.
DanIt is safe, as the database is stored on "regular" files (seen from the operating system's
perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
just in case. Also, consider why the files are so fragmented. For instance, read this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> Hi all,
> I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> contains the database has become massively fragmented in the last two months
> since the system was put in. I'd like to run defrag on the drive and then
> set it up as a scheduled task to run weekly to keep the fragmentation down.
> I'm new to the SQL world and don't think that running defrag will mess
> anything up but am worried about the "what if" factor. I'm learning SQL out
> of a couple of books (no training budget). None of my books say anything
> about defrag itself.
> Can anyone reassure me that running defrag on a drive with a database on it
> is safe? Is there anything I should look out for?
> Thanks.
>
> Dan
>|||Tibor,
Thanks for the quick reply. I'm feeling more comfortable about it now.
Dan
"Tibor Karaszi" wrote:
> It is safe, as the database is stored on "regular" files (seen from the operating system's
> perspective). Just stop the SQL Server service before defragging. And do SQL Server backups first,
> just in case. Also, consider why the files are so fragmented. For instance, read this:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dan Allen" <Dan Allen@.discussions.microsoft.com> wrote in message
> news:DBB7C6E1-B36C-4EA5-98EE-9ACC98C527AE@.microsoft.com...
> > Hi all,
> >
> > I'm running MS SQL 2000 on a 2003 Server. I have found that the drive that
> > contains the database has become massively fragmented in the last two months
> > since the system was put in. I'd like to run defrag on the drive and then
> > set it up as a scheduled task to run weekly to keep the fragmentation down.
> >
> > I'm new to the SQL world and don't think that running defrag will mess
> > anything up but am worried about the "what if" factor. I'm learning SQL out
> > of a couple of books (no training budget). None of my books say anything
> > about defrag itself.
> >
> > Can anyone reassure me that running defrag on a drive with a database on it
> > is safe? Is there anything I should look out for?
> >
> > Thanks.
> >
> >
> > Dan
> >
>
>