delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.

> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.

> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
David

delete all rows from a specific partition in SQL 2005?

Hi,
does it possible to truncate a specific partition on a partitionned table?
I have a large table where each partition contains 50 million of rows, I
have to truncate 1 partition (which is 1 year) and then refill it.
its a data warehouse, so there is no concurrent usage, its during my loading
process.
does the delete statement is good enough?
thanks.
Jerome."Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
>
ALTER TABLE ... SWITCH can be used to switch the partition with an empty
table, which can then be truncated.
David|||interesting...
for the moment this command works fine:
alter table ProdTable switch partition 6 to DummyTable
this "truncate" my partition 6 of my prodtable, which is exactly what I
want.
This is my scenario...
1 I load new content to an empty partitionned table. when the loading is
completed; I move the current production data of the targeted partition to
my dummy table; Now the prodtable doesn't contains the year I want to load;
I move the data from the temporary partitionned table to the prodtable;
now my prodtable contain the new content.
This reduce the downtime for my users and, in case of failure, preserve the
current content because I move the data only at the end.
does it a good scenario?
but this cause to fill a partitionned table because I can switch only data
from 2 partitionned tables, I can't switch from non-partitionned to a
partitionned one.
now a question... does filling a partitionned table using SSIS cause an
overhead in the insert statement?
thanks.
Jerome.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O99MI3OiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> does it possible to truncate a specific partition on a partitionned
>> table?
>> I have a large table where each partition contains 50 million of rows, I
>> have to truncate 1 partition (which is 1 year) and then refill it.
>> its a data warehouse, so there is no concurrent usage, its during my
>> loading process.
>> does the delete statement is good enough?
> ALTER TABLE ... SWITCH can be used to switch the partition with an empty
> table, which can then be truncated.
> David
>|||Use the SWITCH operator to move the partition to a staging table and then
truncate the staging table.
--
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:egxmKnOiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> does it possible to truncate a specific partition on a partitionned table?
> I have a large table where each partition contains 50 million of rows, I
> have to truncate 1 partition (which is 1 year) and then refill it.
> its a data warehouse, so there is no concurrent usage, its during my
> loading process.
> does the delete statement is good enough?
> thanks.
> Jerome.
>|||"Jeje" <willgart@.hotmail.com> wrote in message
news:eijqvmQiGHA.4200@.TK2MSFTNGP05.phx.gbl...
> interesting...
> for the moment this command works fine:
> alter table ProdTable switch partition 6 to DummyTable
> this "truncate" my partition 6 of my prodtable, which is exactly what I
> want.
> This is my scenario...
> 1 I load new content to an empty partitionned table. when the loading is
> completed; I move the current production data of the targeted partition to
> my dummy table; Now the prodtable doesn't contains the year I want to
> load; I move the data from the temporary partitionned table to the
> prodtable;
> now my prodtable contain the new content.
> This reduce the downtime for my users and, in case of failure, preserve
> the current content because I move the data only at the end.
> does it a good scenario?
Yes.
> but this cause to fill a partitionned table because I can switch only data
> from 2 partitionned tables, I can't switch from non-partitionned to a
> partitionned one.
Sure you can. The non-partitioned table must be on the same file group as
the target partition, and it must have a check constraint on the
partitioning columns that guarantees that all the rows in the table are
belong in that partition. But it is often easier to use a partitioned
staging table for loading, switching and truncating.
> now a question... does filling a partitionned table using SSIS cause an
> overhead in the insert statement?
>
Some, perhaps. It depends on the distributation of the data across
partitions, I suppose.
Davidsql

Delete & create a partition

Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.
|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:

> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>
|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:

Delete & create a partition

Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
--
Arnold
"Dan Guzman" wrote:
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) with
> like schema and indexes. You can then drop or truncate the staging table to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> > Hello All,
> >
> > I am working on a hugeee table partionned in 20.
> >
> > Working on one partition at one time, I need to drop and re-create a
> > partition before inserting treated data.
> >
> > Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> > any
> > alternative like truncate partion maybe...
> >
> > Thanks !!
> > Arnold.
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> To effectively truncate a partition, SWITCH the desired partition into a
>> staging table. The staging table needs to be on the same filegroup(s)
>> with
>> like schema and indexes. You can then drop or truncate the staging table
>> to
>> permanently remove the data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "r.no" <rno@.discussions.microsoft.com> wrote in message
>> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>> > Hello All,
>> >
>> > I am working on a hugeee table partionned in 20.
>> >
>> > Working on one partition at one time, I need to drop and re-create a
>> > partition before inserting treated data.
>> >
>> > Anyone know if I can drop then re-create a partion ? if yes, How. if
>> > no,
>> > any
>> > alternative like truncate partion maybe...
>> >
>> > Thanks !!
>> > Arnold.

Delete & create a partition

Hello All,
I am working on a hugeee table partionned in 20.
Working on one partition at one time, I need to drop and re-create a
partition before inserting treated data.
Anyone know if I can drop then re-create a partion ? if yes, How. if no, any
alternative like truncate partion maybe...
Thanks !!
Arnold.> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
To effectively truncate a partition, SWITCH the desired partition into a
staging table. The staging table needs to be on the same filegroup(s) with
like schema and indexes. You can then drop or truncate the staging table to
permanently remove the data.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
> Hello All,
> I am working on a hugeee table partionned in 20.
> Working on one partition at one time, I need to drop and re-create a
> partition before inserting treated data.
> Anyone know if I can drop then re-create a partion ? if yes, How. if no,
> any
> alternative like truncate partion maybe...
> Thanks !!
> Arnold.|||But what do I do after doing the truncate on the staging table ? how do I go
back to main table ? i need some kind of "switch back" to original table
partition ?
Arnold
"Dan Guzman" wrote:

> To effectively truncate a partition, SWITCH the desired partition into a
> staging table. The staging table needs to be on the same filegroup(s) wit
h
> like schema and indexes. You can then drop or truncate the staging table
to
> permanently remove the data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "r.no" <rno@.discussions.microsoft.com> wrote in message
> news:85E5D494-3BDE-4B68-9B52-04ED3A9B1C43@.microsoft.com...
>|||After the switch out, the source partition will still exist with the same
boundaries but will be empty. No need to switch anything back.
Hope this helps.
Dan Guzman
SQL Server MVP
"r.no" <rno@.discussions.microsoft.com> wrote in message
news:33DB4230-786C-4661-9905-9A23D5CE3C84@.microsoft.com...[vbcol=seagreen]
> But what do I do after doing the truncate on the staging table ? how do I
> go
> back to main table ? i need some kind of "switch back" to original table
> partition ?
> --
> Arnold
>
> "Dan Guzman" wrote:
>

Defrag a partition with a SQL database

Is it problematic at all to defrag a partition conaining a SQL database?
You will have to shut down the SQL instance first, otherwise the
defrag software won't touch it.
On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
wrote:

>Is it problematic at all to defrag a partition conaining a SQL database?
|||"bradsbulkmail@.comcast.net" wrote:

> You will have to shut down the SQL instance first, otherwise the
> defrag software won't touch it.
> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
> wrote:
>
There's a third-party tool, Diskeeper, that allows you to defrag while the
files are online.
|||F_clef wrote:
> "bradsbulkmail@.comcast.net" wrote:
>
> There's a third-party tool, Diskeeper, that allows you to defrag while the
> files are online.
The very thought scares the bejeezus out of me. The notion that
something is moving chunks of my database file around WHILE SQL Server
is moving data pages around just seems EXTREMELY risky to me...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||What is Microsoft's official support position on the use of 'on-line'
defrag tools (specifically diskeeper). If the database corrupts and this
tool is running, Would microsoft invalidate the support agreement?
thanks
Andrew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPMRA3q$GHA.4604@.TK2MSFTNGP02.phx.gbl...
>
> I agree. The defrag tool vendors can claim as much as they want that this
> is OK. I just wouldn't do it. Defragging file system should very rarely be
> necessary unless one misuse autogrow and shrink, so for these rare
> occasions, I'd definitely would stop SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:454A30E0.3020609@.realsqlguy.com...
>
|||U guys are too paranoid. Live a little... that's why we have backups!
"Tibor Karaszi" wrote:

>
> I agree. The defrag tool vendors can claim as much as they want that this is OK. I just wouldn't do
> it. Defragging file system should very rarely be necessary unless one misuse autogrow and shrink, so
> for these rare occasions, I'd definitely would stop SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A30E0.3020609@.realsqlguy.com...
>
|||F_clef wrote:
> U guys are too paranoid. Live a little... that's why we have backups!
>
Explain that to our thousands of users around the world, when our 240GB
database gets eaten by DisKeeper and we're down for 4 hours...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455B5770.5060504@.realsqlguy.com...
> F_clef wrote:
> Explain that to our thousands of users around the world, when our 240GB
> database gets eaten by DisKeeper and we're down for 4 hours...
I was going to say. Backups are great... but we're a 24x7 shop.
Experimentation is discouraged. :-)

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||The defrag vendors will say they use Microsoft APIs and there can't be
corruption. Personally, I use PerfectDisk from Raxco Software and have
never had a problem with SQL servers...who knows, maybe I'm just lucky,
but I know MS engineers also use the product. But I can certainly
understand the hesitancy...
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:455B5770.5060504@.realsqlguy.com...
> I was going to say. Backups are great... but we're a 24x7 shop.
> Experimentation is discouraged. :-)
>
|||The defrag vendors will say they use Microsoft APIs and there can't be
corruption. Personally, I use PerfectDisk from Raxco Software and have
never had a problem with SQL servers...who knows, maybe I'm just lucky,
but I know MS engineers also use the product. But I can certainly
understand the hesitancy...
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:455B5770.5060504@.realsqlguy.com...
> I was going to say. Backups are great... but we're a 24x7 shop.
> Experimentation is discouraged. :-)
>

Defrag a partition with a SQL database

Is it problematic at all to defrag a partition conaining a SQL database?You will have to shut down the SQL instance first, otherwise the
defrag software won't touch it.
On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
wrote:

>Is it problematic at all to defrag a partition conaining a SQL database?|||"bradsbulkmail@.comcast.net" wrote:

> You will have to shut down the SQL instance first, otherwise the
> defrag software won't touch it.
> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
> wrote:
>
>
There's a third-party tool, Diskeeper, that allows you to defrag while the
files are online.|||Contig from sysinternals does the trick.
F_clef wrote:
> "bradsbulkmail@.comcast.net" wrote:
>
> There's a third-party tool, Diskeeper, that allows you to defrag while the
> files are online.|||F_clef wrote:
> "bradsbulkmail@.comcast.net" wrote:
>
> There's a third-party tool, Diskeeper, that allows you to defrag while the
> files are online.
The very thought scares the bejeezus out of me. The notion that
something is moving chunks of my database file around WHILE SQL Server
is moving data pages around just seems EXTREMELY risky to me...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> The very thought scares the bejeezus out of me. The notion that something is moving chunks
of my
> database file around WHILE SQL Server is moving data pages around just see
ms EXTREMELY risky to
> me...
I agree. The defrag tool vendors can claim as much as they want that this is
OK. I just wouldn't do
it. Defragging file system should very rarely be necessary unless one misuse
autogrow and shrink, so
for these rare occasions, I'd definitely would stop SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A30E0.3020609@.realsqlguy.co
m...
> F_clef wrote:
> The very thought scares the bejeezus out of me. The notion that something
is moving chunks of my
> database file around WHILE SQL Server is moving data pages around just see
ms EXTREMELY risky to
> me...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||What is Microsoft's official support position on the use of 'on-line'
defrag tools (specifically diskeeper). If the database corrupts and this
tool is running, Would microsoft invalidate the support agreement?
thanks
Andrew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPMRA3q$GHA.4604@.TK2MSFTNGP02.phx.gbl...
>
> I agree. The defrag tool vendors can claim as much as they want that this
> is OK. I just wouldn't do it. Defragging file system should very rarely be
> necessary unless one misuse autogrow and shrink, so for these rare
> occasions, I'd definitely would stop SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:454A30E0.3020609@.realsqlguy.com...
>|||U guys are too paranoid. Live a little... that's why we have backups!
"Tibor Karaszi" wrote:

>
> I agree. The defrag tool vendors can claim as much as they want that this
is OK. I just wouldn't do
> it. Defragging file system should very rarely be necessary unless one misu
se autogrow and shrink, so
> for these rare occasions, I'd definitely would stop SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A30E0.302
0609@.realsqlguy.com...
>|||F_clef wrote:
> U guys are too paranoid. Live a little... that's why we have backups!
>
Explain that to our thousands of users around the world, when our 240GB
database gets eaten by DisKeeper and we're down for 4 hours...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455B5770.5060504@.realsqlguy.com...
> F_clef wrote:
> Explain that to our thousands of users around the world, when our 240GB
> database gets eaten by DisKeeper and we're down for 4 hours...
I was going to say. Backups are great... but we're a 24x7 shop.
Experimentation is discouraged. :-)

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Defrag a partition with a SQL database

Is it problematic at all to defrag a partition conaining a SQL database?You will have to shut down the SQL instance first, otherwise the
defrag software won't touch it.
On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
wrote:
>Is it problematic at all to defrag a partition conaining a SQL database?|||"bradsbulkmail@.comcast.net" wrote:
> You will have to shut down the SQL instance first, otherwise the
> defrag software won't touch it.
> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
> wrote:
> >Is it problematic at all to defrag a partition conaining a SQL database?
>
There's a third-party tool, Diskeeper, that allows you to defrag while the
files are online.|||Contig from sysinternals does the trick.
F_clef wrote:
> "bradsbulkmail@.comcast.net" wrote:
>> You will have to shut down the SQL instance first, otherwise the
>> defrag software won't touch it.
>> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
>> wrote:
>> Is it problematic at all to defrag a partition conaining a SQL database?
> There's a third-party tool, Diskeeper, that allows you to defrag while the
> files are online.|||F_clef wrote:
> "bradsbulkmail@.comcast.net" wrote:
>> You will have to shut down the SQL instance first, otherwise the
>> defrag software won't touch it.
>> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
>> wrote:
>> Is it problematic at all to defrag a partition conaining a SQL database?
> There's a third-party tool, Diskeeper, that allows you to defrag while the
> files are online.
The very thought scares the bejeezus out of me. The notion that
something is moving chunks of my database file around WHILE SQL Server
is moving data pages around just seems EXTREMELY risky to me...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> The very thought scares the bejeezus out of me. The notion that something is moving chunks of my
> database file around WHILE SQL Server is moving data pages around just seems EXTREMELY risky to
> me...
I agree. The defrag tool vendors can claim as much as they want that this is OK. I just wouldn't do
it. Defragging file system should very rarely be necessary unless one misuse autogrow and shrink, so
for these rare occasions, I'd definitely would stop SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A30E0.3020609@.realsqlguy.com...
> F_clef wrote:
>> "bradsbulkmail@.comcast.net" wrote:
>> You will have to shut down the SQL instance first, otherwise the
>> defrag software won't touch it.
>> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
>> wrote:
>> Is it problematic at all to defrag a partition conaining a SQL database?
>> There's a third-party tool, Diskeeper, that allows you to defrag while the files are online.
> The very thought scares the bejeezus out of me. The notion that something is moving chunks of my
> database file around WHILE SQL Server is moving data pages around just seems EXTREMELY risky to
> me...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||What is Microsoft's official support position on the use of 'on-line'
defrag tools (specifically diskeeper). If the database corrupts and this
tool is running, Would microsoft invalidate the support agreement?
thanks
Andrew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPMRA3q$GHA.4604@.TK2MSFTNGP02.phx.gbl...
>> The very thought scares the bejeezus out of me. The notion that something
>> is moving chunks of my database file around WHILE SQL Server is moving
>> data pages around just seems EXTREMELY risky to me...
>
> I agree. The defrag tool vendors can claim as much as they want that this
> is OK. I just wouldn't do it. Defragging file system should very rarely be
> necessary unless one misuse autogrow and shrink, so for these rare
> occasions, I'd definitely would stop SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:454A30E0.3020609@.realsqlguy.com...
>> F_clef wrote:
>> "bradsbulkmail@.comcast.net" wrote:
>> You will have to shut down the SQL instance first, otherwise the
>> defrag software won't touch it.
>> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
>> wrote:
>> Is it problematic at all to defrag a partition conaining a SQL
>> database?
>> There's a third-party tool, Diskeeper, that allows you to defrag while
>> the files are online.
>> The very thought scares the bejeezus out of me. The notion that
>> something is moving chunks of my database file around WHILE SQL Server is
>> moving data pages around just seems EXTREMELY risky to me...
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||U guys are too paranoid. Live a little... that's why we have backups!
"Tibor Karaszi" wrote:
> > The very thought scares the bejeezus out of me. The notion that something is moving chunks of my
> > database file around WHILE SQL Server is moving data pages around just seems EXTREMELY risky to
> > me...
>
> I agree. The defrag tool vendors can claim as much as they want that this is OK. I just wouldn't do
> it. Defragging file system should very rarely be necessary unless one misuse autogrow and shrink, so
> for these rare occasions, I'd definitely would stop SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:454A30E0.3020609@.realsqlguy.com...
> > F_clef wrote:
> >>
> >> "bradsbulkmail@.comcast.net" wrote:
> >>
> >> You will have to shut down the SQL instance first, otherwise the
> >> defrag software won't touch it.
> >>
> >> On Wed, 1 Nov 2006 14:26:03 -0800, CK <CK@.discussions.microsoft.com>
> >> wrote:
> >>
> >> Is it problematic at all to defrag a partition conaining a SQL database?
> >>
> >> There's a third-party tool, Diskeeper, that allows you to defrag while the files are online.
> >
> > The very thought scares the bejeezus out of me. The notion that something is moving chunks of my
> > database file around WHILE SQL Server is moving data pages around just seems EXTREMELY risky to
> > me...
> >
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
>|||F_clef wrote:
> U guys are too paranoid. Live a little... that's why we have backups!
>
Explain that to our thousands of users around the world, when our 240GB
database gets eaten by DisKeeper and we're down for 4 hours...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:455B5770.5060504@.realsqlguy.com...
> F_clef wrote:
>> U guys are too paranoid. Live a little... that's why we have backups!
> Explain that to our thousands of users around the world, when our 240GB
> database gets eaten by DisKeeper and we're down for 4 hours...
I was going to say. Backups are great... but we're a 24x7 shop.
Experimentation is discouraged. :-)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||The defrag vendors will say they use Microsoft APIs and there can't be
corruption. Personally, I use PerfectDisk from Raxco Software and have
never had a problem with SQL servers...who knows, maybe I'm just lucky,
but I know MS engineers also use the product. But I can certainly
understand the hesitancy...:)
Greg D. Moore (Strider) wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:455B5770.5060504@.realsqlguy.com...
> > F_clef wrote:
> >> U guys are too paranoid. Live a little... that's why we have backups!
> >>
> >
> > Explain that to our thousands of users around the world, when our 240GB
> > database gets eaten by DisKeeper and we're down for 4 hours...
> I was going to say. Backups are great... but we're a 24x7 shop.
> Experimentation is discouraged. :-)
>
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||The defrag vendors will say they use Microsoft APIs and there can't be
corruption. Personally, I use PerfectDisk from Raxco Software and have
never had a problem with SQL servers...who knows, maybe I'm just lucky,
but I know MS engineers also use the product. But I can certainly
understand the hesitancy...:)
Greg D. Moore (Strider) wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:455B5770.5060504@.realsqlguy.com...
> > F_clef wrote:
> >> U guys are too paranoid. Live a little... that's why we have backups!
> >>
> >
> > Explain that to our thousands of users around the world, when our 240GB
> > database gets eaten by DisKeeper and we're down for 4 hours...
> I was going to say. Backups are great... but we're a 24x7 shop.
> Experimentation is discouraged. :-)
>
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com