分类目录归档:SQL Server

动态删除sqlserver分区

我的表是按天分区,一个月一个分区文件
1.根据时间范围逐个找到分区id,然后分离此分区到一个临时表,然后把这个临时表drop掉(也可转移备份走),代码如下:
[sql]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[DeletePartitionByDate]
@fromDate datetime,
@toDate datetime
AS
BEGIN
DECLARE @i datetime;
DECLARE @month varchar(16);
DECLARE @fg varchar(32);
DECLARE @theDay varchar(10);
DECLARE @sql nvarchar(max);
DECLARE @PARTITION_DEL_NO int; –NVARCHAR(4)
SET @i = @fromDate;

WHILE @i < @toDate
BEGIN
SET @month = convert(varchar(4),year(@i))+right(’0′+convert(varchar(2),month(@i)),2);
–set @theDay = convert(varchar(4),year(@i))+right(’0′+convert(varchar(2),month(@i)),2)+right(’0′+convert(varchar(2),day(@i)),2);

set @fg = ‘bpr’+@month;

–找出特定日期数据所在分区的的分区号
SELECT @PARTITION_DEL_NO = $PARTITION.DatePartitionFunction(@i);

–建临时分区表
DROP TABLE tmp_staging_ukd_fact

set @sql = ‘CREATE TABLE [dbo].[tmp_staging_ukd_fact](
[domain_id] [int] NOT NULL,
[keyword_id] [int] NOT NULL,
[position] [smallint] NULL,
[datetime] [datetime] NULL,
[up] [int] NULL,
[client_id] [int] NULL,
[batch_id] [int] NULL
) ON ‘+Convert(nvarchar,@fg);
exec sp_executesql @sql;

–为临时表创建聚族索引
set @sql = N’CREATE CLUSTERED INDEX [tmp_staging_ukd_fact_CT_UKD_Fact] ON [dbo].[tmp_staging_ukd_fact]
(
[datetime] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ‘+Convert(nvarchar,@fg);
exec sp_executesql @sql;

–分离分区到临时表
ALTER TABLE [baidupr].[dbo].[ukd_fact] SWITCH PARTITION @PARTITION_DEL_NO TO [baidupr].[dbo].[tmp_staging_ukd_fact] PARTITION @PARTITION_DEL_NO;

–清理临时表数据
–TRUNCATE TABLE dbo.[tmp_staging_ukd_fact];

set @i = dateadd(day, 1, @i);
end

END

[/sql]
exec dbo.DeletePartitionByDate ’2012-10-01′, ’2012-10-29′;
运行结果类似如下:

2. 另写一存储过程放在计划任务里,每个月的第一天调用来处理上上个月的数据,代码如下:
[sql]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: czhan
– Create date: 2012-11-30
– Description: clean old partition every month
– =============================================
CREATE PROCEDURE CleanOldDataMonthly
AS
BEGIN
SET NOCOUNT ON;
–delete non-cluster-index
drop index idx_ukd on dbo.ukd_fact;
drop index idx_position on dbo.ukd_fact;

–clean the data of last month
declare @fromDate datetime;
declare @toDate datetime;
set @toDate = dateadd(month,-1, GETDATE());
set @fromDate = dateadd(month,-2, GETDATE());

exec dbo.DeletePartitionByDate @fromDate, @toDate;

–create non-cluster-index
CREATE NONCLUSTERED INDEX [idx_ukd] ON [dbo].[ukd_fact]
(
[domain_id] ASC,
[keyword_id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [idx_position] ON [dbo].[ukd_fact]
(
[position] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [DatePartitionScheme]([datetime]);
END
GO

[/sql]

SQL Server计划任务不能执行的问题

多天以前写了一个存储过程用来定时汇总新增加的用户信息,以提高查询速度。但后来发现一直没有执行。
重新查看一下发现少写了选择数据库的语句:use mydb
于是加上了,手动执行一下,但还提示相同的错误。找了大半天,发现原来修改后没有保存,t-sql没升效。
晕,几点启示:
1.用户习惯问题,一般修改后点“确定”代表保存信息,不知道这一点该不该算到微软的头上;
2.在布置计划任务后一定要手动点一下“执行”测试一下,尽量让测试环境贴近产品环境;
3.遇到这种情况可以再一步一步地新建任务,看看变化情况;
4.注意日志变化情况,没有任何变化的情况下应该能想到代码是否没改变;