--// SAMPLE 01
=========================
1 - Include a field integer
=============================================================
Alter table tArch_PrintDocument
Add createDateInt int
2 - Insert data from CreateDate field (it will increase 1GB)
=============================================================
update tArch_PrintDocument set createDateInt = convert(varchar(50), CreateDate, 112)
3 - Create index
=============================================================
create index idx_tArch_PrintDocument01 on tArch_PrintDocument (createDateInt)
4 - Delete for each 1000 lines (to do not lock)
=============================================================
While 1=1 begin
Delete TOP (1000) from CFS_Archive.dbo.tArch_PrintDocument where createDateInt < '20080304'
if @@rowcount = 0 begin
break
end
End
--// SAMPLE 02
=========================
1 - Rename the current TB
==========================
sp_renamedb 'tArch_PrintDocument', 'tArch_PrintDocument_old'
2 - Create a new table (without indexes)
============================================
CREATE TABLE [dbo].[tArch_PrintDocument](
[Arch_PrintDocument] [uniqueidentifier] NOT NULL,
[PrintDocumentGUID] [uniqueidentifier] NOT NULL,
[PrintGUID] [uniqueidentifier] NOT NULL,
[MergeGUID] [uniqueidentifier] NULL,
[DocumentGUID] [uniqueidentifier] NULL,
[MergeDocumentGUID] [uniqueidentifier] NULL,
[PaperGUID] [uniqueidentifier] NULL,
[PrintOrder] [int] NOT NULL,
[Data] [image] NOT NULL,
[Size] [int] NOT NULL,
[Copies] [int] NOT NULL,
[PrintDocumentStatusID] [int] NOT NULL,
[EmailPrintExclude] [bit] NOT NULL,
[GetFromDownload] [bit] NULL,
[ItemGUID] [uniqueidentifier] NULL,
[CreateDate] [datetime] NOT NULL,
[CreatedBy] [uniqueidentifier] NOT NULL,
[ChangeDate] [datetime] NULL,
[ChangedBy] [uniqueidentifier] NULL
)
3 - Export by DOS
==========================
BCP "select count(*) from CFS_Archive.dbo.tArch_PrintDocument_old where createDate BETWEEN '2008-03-04 00:00:00.000' and '2015-03-04 00:00:00.000'" OUT D:\DBA\spool.csv -o D:\DBA\spool_log.txt -SReponyx -T
4 - Import by DOS
==========================
BCP CFS_Archive.dbo.tArch_PrintDocument IN D:\DBA\spool.csv -o D:\DBA\spool_log.txt -SReponyx -T
5 - Create indexes and constraints (Are these indexes necessaries? If not, don't create it)
===============================================================================================================
ALTER TABLE [dbo].[tArch_PrintDocument] ADD CONSTRAINT PK__tArch_PrintDocum__1FCDBCEB_NEW PRIMARY KEY (Arch_PrintDocument)
GO
ALTER TABLE [dbo].[tArch_PrintDocument] ADD INDEX idx_printGUID_PrintOrder_NEW (PrintGUID, PrintOrder)
GO
ALTER TABLE [dbo].[tArch_PrintDocument] ADD CONSTRAINT UQ__tArch_PrintDocum__20C1E124_NEW UNIQUE (PrintDocumentGUID)
GO
ALTER TABLE [dbo].[tArch_PrintDocument] ADD DEFAULT (newid()) FOR [Arch_PrintDocument]
GO
6 - Drop old table
=====================================
-- drop table tArch_PrintDocument_old