数据库切库
切换步骤：
1、停服（如果可以停的话）
2、禁用写账号
3、kill进程
4、禁用日志备份job，自动传送日志job，日志还原job
5、手动执行备份日志存储过程，备份最后一次尾日志
6、手动执行自动传送日志备份
7、手动执行最后日志还原存储过程
8、检查最后日志还原节点
9、如果正确，改成recovery模式，通知开发人员改连接ip或者域名配置
10、如果一切验证都ok，切换完毕，如果有job启用job，后期做全备，日志备份，搭建备库等等后续工作
11、如果有问题，改回原来ip或者域名配置，启用原来写账号，重新备份数据库，还原数据库，启用日志备份、传输、还原job，等待下一次切换
1.首先查看数据库还原情况，查看指定的库的日志追加情况
select physical_device_name from msdb.dbo.backupmediafamily with(nolock) where physical_device_name like '%Summary_12_202002%' order by media_set_id desc
2.停止程序写入
3.kill进程 运行下面2段sql ,然后执行
sp_block
go
select 'kill ' + ltrim(rtrim(str(spid))) from ##block where DBname='切换库名'
然后kill掉第二个结果的数据
然后在执行一遍，查看是否有未杀死的
sp_block
go
select 'kill ' + ltrim(rtrim(str(spid))) from ##block where DBname='切换库名'
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_block] Script Date: 09/27/2013 14:17:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_block]
@status [varchar](50) = '',
@is_output [tinyint] = 1
WITH encryption
AS
/**********************
{VER EDITOR DATE COMMENT }
REMARK:
1. 系统进程本身没有connection,一个sesstion对应一个connection
2. insert data有严格的顺序
3. 如果sys.dm_exec_requests的列值为null,表明此requests已经不存在,session_status为sleeping
4. if a spid is blocking and is blocked,it belong to blocked,but the blocking count has value.
5. if the sp get error info like "The statement terminated. The maximum recursion 100 has been exhausted before statement completion.",then it indicate a deadlock happened,like this: "Transaction (Process ID 67) was deadlocked on lock resources with anot
her process and has been chosen as the deadlock victim. Rerun the transaction.
6. sleeping: last_request_end_time>=last_request_start_time
7. 有时查询结果的数据出现不一致的情况,比如type=sleeping,而status=running,原因是此sp内部多次查询的时间差
8. 不论@status输入参数如何,blocking和blocked总是显示,可以用此特性只显示blocking和blocked
9. Will only display the blocking spid's query plan.
10. Will filter current spid,but if the current spid is blocking or blocked ,it will display.
11. 系统进程被阻塞,也会显示
12. session_status 有时不匹配 request_status,原因:存在脏读;或读取时间不一致
13. 不考虑自己阻塞自己的情况
EXAMPLE:
exec sp_block '%running%'
exec sp_block '%block%'
sp_block @status ='',@is_output =2
************************/
set nocount on
declare @rowcount int
set @status=ltrim(rtrim(@status))+'%'
declare @spid_blocked table (
spid int not null
,blocking_spid int null
,blocking_spid_sort varchar(1000) null
,wait_time int null
)
declare @cte table(spid smallint ,blocking_spid smallint ,wait_time bigint )
insert @cte
select distinct spid,blocked,waittime from master.dbo.sysprocesses where blocked <>0 and spid<>blocked
insert into @spid_blocked
select a.spid
,coalesce(d.blocking_spid,c.blocking_spid,b.blocking_spid,a.blocking_spid) as blocking_spid
,ltrim(str(a.spid))+'->'+ltrim(str(a.blocking_spid))+isnull('->'+ltrim(str(b.blocking_spid)),'')+isnull('->'+ltrim(str(c.blocking_spid)),'')+isnull('->'+ltrim(str(d.blocking_spid)),'') as blocking_spid_sort
,a.wait_time/1000 as wait_time
from @cte a
left join @cte b on a.blocking_spid=b.spid
left join @cte c on b.blocking_spid=c.spid
left join @cte d on c.blocking_spid=d.spid
if @@rowcount>0
begin
declare @spid_activity table (
type nvarchar(50) not null
,spid int not null
,blocking_spid int null
,blocking_spid_sort varchar(1000) null
,blocked_count int null
,wait_time int null
,blocked_time int null --seconds
)
insert into @spid_activity(type,spid,blocking_spid,blocking_spid_sort,blocked_count,wait_time,blocked_time)
select distinct '1_blocking',blocking_spid,0,'kill '+ltrim(str(blocking_spid)),count(blocking_spid),0,max(wait_time)
from @spid_blocked group by blocking_spid
union all
select distinct '2_blocked',spid,blocking_spid,blocking_spid_sort,0,wait_time,wait_time
from @spid_blocked
select @rowcount=@@rowcount
if @is_output<>2
begin
----select * from master.dbo.dba_block_debug
if object_id('master.dbo.dba_block_debug') is null
begin
--(可删除),收集数据
create table master.dbo.dba_block_debug(
id int identity primary key,
checktime datetime default(getdate()),
type nvarchar(50) not null
,spid int not null
,blocking_spid int null
,blocking_spid_sort varchar(1000) null
,blocked_count int null
,wait_time int null
,blocked_time int null --seconds
)
end
if @rowcount>=5 and @is_output=1
begin
insert into master.dbo.dba_block_debug(type,spid,blocking_spid,blocking_spid_sort,blocked_count,wait_time,blocked_time)
select type,spid,blocking_spid,blocking_spid_sort,blocked_count,wait_time,blocked_time from @spid_activity
delete from master.dbo.dba_block_debug where id<(select max(id) as id from master.dbo.dba_block_debug)-1000
end
end
end
--------------
if object_id('tempdb.dbo.##block') is not null drop table ##block
--10 show results *************
select
case when st0.type is not null then st0.type
when se1.status='running' then '3_Running'
when se1.status='Sleeping' then '4_Sleeping'
when se1.status='Dormant' then '5_Dormant'
when se1.status='Preconnect' then '6_Preconnect'
else 'Others' end as type
,se1.session_id as spid
,isnull(st0.blocking_spid_sort,'') as blocking_spid
,isnull(str(st0.blocked_count),'') as blocked_count
,isnull(dbo.udf_Duration_IntToChar(blocked_time),'') as blocked_time
,case when pr.dbid = 0 then '' else db_name(pr.dbid) end as DBname
,se1.login_name /*,db_name(re2.database_id) as DBname --20090817yf*/
,se1.[host_name] as client_name ,co3.client_net_address as client_IP
,case when charindex('Job 0x',se1.[program_name])>0 then (
select 'SQLAgent Job: '+a.name+' Setp: '+ltrim(str(b.step_id))+' ('+b.step_name+')'
from msdb.dbo.sysjobs a with (nolock)
join msdb.dbo.sysjobsteps b with (nolock) on a.job_id=b.job_id
where right(a.job_id,12) =substring(se1.[program_name],charindex('Job 0x',se1.[program_name])+26,12)
and b.step_id=substring(se1.[program_name]
,charindex(' Step ',se1.[program_name])+6
,charindex(')',se1.[program_name])-charindex(' Step ',se1.[program_name])-6 )
)
else se1.[program_name] end as client_program
,te2.text as running_or_last_text
, ( SELECT TOP 1 SUBSTRING(te1.text,statement_start_offset/2,
( (CASE WHEN statement_end_offset= -1 THEN (LEN(CONVERT(nvarchar(max),te1.text))*2)
ELSE statement_end_offset END)-statement_start_offset)/2)) AS running_sql
--fix bug of "Difference of two datetime columns caused overflow at runtime."
,case when re2.wait_time is not null and se1.last_request_end_time>0
then 'wait '+dbo.udf_Duration_IntToChar(re2.wait_time/1000)
when re2.wait_time is null and se1.last_request_end_time>0
then 'idle '+dbo.udf_Duration_IntToChar(datediff(ss,se1.last_request_end_time,getdate()))
else 'idle from '+convert(char(20),se1.last_request_end_time,120) end as duration
,se1.status as session_status ,re2.status as request_status
,re2.command ,re2.wait_type ,re2.wait_resource
,re2.open_transaction_count as open_tran
,re2.row_count
,local_net_address as Server_IP,local_tcp_port as Port
--,se1.[program_name] as client_program --,user_name(re2.[user_id]) as UserName
,se1.client_interface_name as client_interface
,se1.host_process_id as client_process
,case when st0.blocking_spid=0 then (select query_plan from sys.dm_exec_query_plan (re2.plan_handle) )
else '' end as query_plan
,se1.login_time ,se1.last_request_end_time /*,se1.last_request_start_time --20090817yf*/
,se1.cpu_time ,se1.reads ,se1.logical_reads ,se1.Writes ,se1.is_user_process
/*,se1.memory_usage,re2.cpu_time,re2.reads,re2.logical_reads ,re2.Writes,re2.nest_level --20090817yf*/
--,percent_complete --,re2.transaction_isolation_level --转换成文字说明 --,re2.prev_error
--,dbo.udf_Duration_IntToChar(re2.total_elapsed_time/1000)as total_elapsed_time
--,re2.start_time --,re2.last_wait_type --,re2.open_resultset_count --,transaction_id
---,se1.lock_timeout --,re2.granted_query_memory --,re2.executing_managed_code
,blocked_time as blocked_time_ss
into ##block
from sys.dm_exec_sessions se1 with (nolock)
left join master.dbo.sysprocesses pr with (nolock) on se1.session_id=pr.spid
left join sys.dm_exec_requests re2 with (nolock) on se1.session_id=re2.session_id
left join sys.dm_exec_connections co3 with (nolock) on se1.session_id=co3.session_id
left join @spid_activity st0 on se1.session_id=st0.spid
outer APPLY sys.dm_exec_sql_text(re2.sql_handle) AS te1
outer APPLY sys.dm_exec_sql_text(co3.most_recent_sql_handle) AS te2
where st0.type is not null
or (se1.is_user_process=1 and se1.status like @status and se1.session_id<>@@SPID)
order by type,st0.blocked_count desc,st0.blocked_time desc,request_status desc,se1.session_id
------
if @is_output=1
begin
select * from ##block
print 'select * from ##block order by login_name'
print 'select login_name,count(*) as count0 from ##block group by login_name order by login_name '
end
else
if @is_output=2
begin
declare @min_spid int
select @min_spid=min(spid) from ##block where type in ('1_blocking','3_Running')
select isnull(stuff(@@servername,charindex('\',@@servername),len(@@servername),''),@@servername) as ServerName
,case when a.type is null then b.type else a.type end as type
,a.spid,blocked_count,blocked_time,duration,session_status
,DBname,login_name,client_name,client_IP,client_program
,case when running_sql is null then running_or_last_text else running_sql end as running_or_last_text
,command,wait_type
--,client_interface,client_process,blocking_spid,wait_resource
from (select * from ##block where type in ('1_blocking','3_Running')) a full outer join
(select case when @status like 'b%' then 'No_Blocking'--'当前无阻塞的进程!'
when @status like 'r%' then 'No_Running'--'当前无阻塞或正运行的进程!'
else 'No_Blocking_or_Running' end as type
,@min_spid as spid
) b on a.spid=b.spid
end
GO
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[udf_Duration_IntToChar] Script Date: 09/27/2013 14:19:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create FUNCTION [dbo].[udf_Duration_IntToChar]
(@duration int)
RETURNS varchar(50)
with encryption
AS
/**********************
REMARK:
1. The duration must be seconds
EXAMPLE:
declare @duration int
set @duration=3672+3600*100
select dbo.udf_Duration_IntToChar(@duration)
************************/
BEGIN
declare @duration_char varchar(50),@days varchar(30)
set @days=case when @duration>=86400 then convert(varchar(20),convert(int,@duration/86400))+'D '
else '' end
set @duration=@duration%86400
set @duration_char=@days+case when (@duration/3600)>9 then ltrim(str(@duration/3600))
else right('0'+ltrim(str(@duration/3600)),2) end +':'
+right('0'+ltrim(str((@duration%3600)/60)),2)+':'
+right('0'+ltrim(str((@duration%3600)%60)),2)
RETURN @duration_char
END
GO
4.禁用日志备份job，自动传送日志job，日志还原job （原库的2个，新的1个）
5.手动执行备份日志存储过程，备份最后一次尾日志
6.手动执行自动传送日志备份
7.手动执行最后日志还原存储过程
8.检查最后日志还原节点
9.还原库执行 use master restore database dbname with recovery
10.刷新一下，看库是否可读写状态了
11.通知开发人员改连接ip或者域名配置
