SQL Server Large Data archive

Yesterday did a big daily data archive, archive 700W data, table field 130, more fields, share!

----To disable index of table first

1. Get the index you want to disable first

DECLARE @tname varchar (100)

Set @tname = ' orders '

Select ' Alter index ' + ' +c.indexname+ ' + ' on ' + ' + @tname + ' + ' disable '

From

(

SELECT * FROM

(

SELECT

object_name (i.object_id) as TableName,

I.name as IndexName,

i.index_id as IndexID,

8 * SUM (a.used_pages)/1024 as ' indexsize (MB) '

From sys.indexes as I

JOIN sys.partitions as P on p.object_id = i.object_id and p.index_id = i.index_id

JOIN sys.allocation_units as a on a.container_id = p.partition_id

GROUP by I.object_id,i.index_id,i.name

) A

where A.tablename= @tname

--order by [Indexsize (MB)] desc

) c

Go

--2. Prohibit the above statement to get the index, but the primary key and clustered index do not disable, remember!

----Delete Data

DBCC dropcleanbuffers

DBCC Freeproccache

Go

SET NOCOUNT on

BEGIN TRANSACTION

While 1=1

Begin

Delete Top (20000) from Dbo.orders with (TABLOCK)

where Ordertime < ' 2010-1-1 '

If @ @rowcount <20000

Break

End

Commit

Go

----Index Rebuild

Alter index all on orders rebuild

Go

Basically a short time to fix, for performance, need to complete the index rebuild and statistical information update!

See more highlights of this column: http://www.bianceng.cn/database/SQLServer/