北京泰达建设有限公司网站,星沙做网站,网页设计模板图片大全,学做网站从零开始文章目录 查询慢sql的方法sqlserver备份全备差异备日志备ldf备份事务备份 注意事项SQL Server 还原全备还原差异备份还原日志备/尾日志还原事务日志还原备份还原中的问题还原失败#xff0c;需要某些权限重命名sql Server数据库名称失败 作业迁移单个迁移批量迁移 登陆账号迁移… 文章目录 查询慢sql的方法sqlserver备份全备差异备日志备ldf备份事务备份 注意事项SQL Server 还原全备还原差异备份还原日志备/尾日志还原事务日志还原备份还原中的问题还原失败需要某些权限重命名sql Server数据库名称失败 作业迁移单个迁移批量迁移 登陆账号迁移批量迁移连接服务器记录数据库的DDL数据库重命名查询所有数据库的大小查询当前数据库的大小数据库显示“正在还原”的处理方法数据库发生死锁怎么处理查看被锁进程号及被锁表名解锁创建杀掉引起死锁进程的存储过程 sqlserver存储过程杀掉数据库中死锁 查询慢sql的方法
1.whoisactive
安装方法
http://whoisactive.com/downloads/下载地址 将下载好的zip包放到sqlserver服务器中 文件-打开-文件-下载好的zip包-在查询窗口点击执行 新建一个查询窗口输入sp_whoisactive获取当前运行的所有sql语句 使用方法
输入sp_whoisactive获取当前运行的所有sql语句 查看当前所有的sql中观察运行时间较长时间的影响到其他业务的或者影响这个服务器性能导致其他的sql堆积的sql就给停止掉 停止方法
whoisactive查看的sql中可以看到session_id记录下慢sql的sessionId 使用命令 kill session_id 的方式将慢sql的进行杀掉 2.自己编写的脚本
脚本命令 当数据库出现性能问题时这个脚本能够直观的看到数据库中SQL 的运行状态快速找到执行缓慢的语句。这是我使用最频繁的脚本之一.
SELECTes.session_id,database_nameDB_NAME(er.database_id),er.cpu_time,er.reads,er.writes,er.logical_reads,login_name,er.status,blocking_session_id,wait_type,wait_resource,wait_time,individual_querySUBSTRING(qt.text,(er.statement_start_offset/2)1,((CASE WHEN er.statement_end_offset-1 THEN LEN(CONVERT(NVARCHAR(MAX),qt.text))* 2 ELSE er.statement_end_offset END-er.statement_start_offset)/2)1),parent_queryqt.text,program_name,host_name,nt_domain,start_time,DATEDIFF(MS,er.start_time,GETDATE())as duration,(SELECT query_plan FROM sys.dm_exec_query_plan (er.plan_handle))AS query_plan
FROMsys.dm_exec_requests erINNER JOIN sys.dm_exec_sessions es ON er.session_ides.session_idCROSS APPLY sys.dm_exec_sql_text (er.sql_handle)AS qt
WHEREes.session_id 50 AND es.session_Id NOT IN(SPID)
ORDER BY1, 2重要信息
logical_reads:逻辑读衡量语句的执行开销。如果大于10w说明此语句开销很大。可以检查下索引是否合理
status进程的状态。running 表示正在运行sleeping 表示处于睡眠中未运行任何语句suspend 表示等待runnable 等待cpu 调度
blocking_session_id: 如果不为0,例如 60 。表示52号进程正在被60阻塞。50 进程必须等待60执行完成才能执行下面的语句
host_name 发出请求的服务器名
program_name:发出请求的应用程序名
duration: 请求的执行时间
3、sqlserver profile
打开sqlserver profiler
常规选项中勾选保存到表自己设置一个表
设置容量大小最大行数。单位是千行。大概设置10w条就设置值为100
事件选择-勾选显示所有事件
Security Audit选项中反勾选LoginLogout
session中反勾选退出连接(existingConnection)
stored procedures(存储过程) 勾选RPC completed 和 SP stmtCompleted
TSQL 勾选 SQLBatchcompleted和SQLstmtcomple
选择列筛选器-》duration-》大于等于3000(此处单位为毫秒)意思是筛选执行3秒以上的sql记录
点击运行就可以看到记录的慢sql了。
sqlserver备份
全备
全备代码 记得修改相关路径USE [master]
GO/****** Object: StoredProcedure [dbo].[sp_full_BackupDB] Script Date: 2020/2/12 18:55:20 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE procedure [dbo].[sp_full_BackupDB](DBName varchar(50))
as
begindeclare sCommandText varchar(255)declare startTime varchar(255)declare endTime varchar(255)declare tmp_file_name varchar(255)declare tmp_file_name_2 varchar(255)declare base_dir varchar(255)declare base_dir_2 varchar(255)declare now_day varchar(255)declare rarcmd varchar(255) declare rarfile varchar(255) declare tmp_prefix varchar(255) set tmp_prefix test_backset startTime convert(varchar,GETDATE( ) , 120)set sCommandText echo 开始备份 完全备份 DBName startTime D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandText---- 正式备份-- 类似 2018-01-02-1407 是 年-月-日-时分 用于日志备份-- set now_day substring( replace(replace(replace(CONVERT(varchar, getdate() , 120 ),-,-), ,-),:,) , 1, 15)-- 类似 2018-01-02 是 年月日时分 用于完整、差异备份set now_day replace(CONVERT(varchar, getdate() , 111 ),/,-) set base_dir D:\bak_db\quanbei_db\-- 全备 master压缩备份 生成类似 TongCheng_B2B_DB_TC_master_full_2018-05-28.bakset tmp_file_name base_dir tmp_prefix DBName _master_full_ now_day .bakBACKUP DATABASE [master] TO DISK tmp_file_name with STATS 1,compression-- 全备 model压缩备份 生成类似 TongCheng_B2B_DB_TC_model_full_2018-05-28.bakset tmp_file_name base_dir tmp_prefix DBName _model_full_ now_day .bakBACKUP DATABASE [model] TO DISK tmp_file_name with STATS 1,compression-- 全备 msdb压缩备份 生成类似 TongCheng_B2B_DB_TC_msdb_full_2018-05-28.bakset tmp_file_name base_dir tmp_prefix DBName _msdb_full_ now_day .bakBACKUP DATABASE [msdb] TO DISK tmp_file_name with STATS 1,compression-- 全备 该用户数据库 生成类似 TongCheng_B2B_DB_TC_full_2018-05-28.bakset tmp_file_name base_dir tmp_prefix DBName _full_ now_day .bakBACKUP DATABASE DBName TO DISK tmp_file_name with STATS 1,compression -- 停止1分钟waitfor delay 00:01:00----- 结束备份set endTime convert(varchar,GETDATE( ) , 120)set sCommandText echo 结束备份 完全备份 DBName endTime D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandTextset sCommandText echo ------------------- D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandTextend
GO
创建过存储过程之后执行全备命令
exec [sp_full_BackupDB] 库名
等待执行完毕即可差异备
差异备代码记得修改相关路径和名字USE [master]
GO/****** Object: StoredProcedure [dbo].[sp_differential_BackupDB] Script Date: 2020/2/12 18:57:43 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE procedure [dbo].[sp_differential_BackupDB](DBName varchar(50))
as
begindeclare sCommandText varchar(255)declare startTime varchar(255)declare endTime varchar(255)declare tmp_file_name varchar(255)declare tmp_file_name_2 varchar(255)declare tmp_file_name_3 varchar(255)declare base_dir varchar(255)declare base_dir_2 varchar(255)declare base_dir_3 varchar(255)declare now_day varchar(255)declare rarcmd varchar(255) declare rarfile varchar(255) declare tmp_prefix varchar(255) set tmp_prefix test_differential_backset startTime convert(varchar,GETDATE( ) , 120)set sCommandText echo 开始备份 差异备份 DBName startTime D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandText---- 正式备份-- 类似 2018-01-02 是 年月日时分 用于完整、差异备份set now_day replace(CONVERT(varchar, getdate() , 111 ),/,-) set base_dir D:\bak_db\differential_db\-- 差异备份 该用户数据库 生成类似 TongCheng_B2B_DB_TC_differential_2018-05-28.bakset tmp_file_name base_dir tmp_prefix DBName _differential_ now_day .bakBACKUP DATABASE DBName TO DISK tmp_file_name with differential , STATS 1,compression -- 停止1分钟waitfor delay 00:01:00----- 结束备份set endTime convert(varchar,GETDATE( ) , 120)set sCommandText echo 结束备份 差异备份 DBName endTime D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandTextset sCommandText echo ------------------- D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandTextend
GO
差异备执行方式
exec [sp_differential_BackupDB] 库名
等待执行完毕即可日志备
日志备代码记得修改相关路径USE [master]
GO/****** Object: StoredProcedure [dbo].[sp_tlog_BackupDB] Script Date: 2020/2/12 18:58:56 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE procedure [dbo].[sp_tlog_BackupDB](DBName varchar(50))
as
begindeclare sCommandText varchar(255)declare startTime varchar(255)declare endTime varchar(255)declare tmp_file_name varchar(255)declare base_dir varchar(255)declare tlog_time varchar(255)declare tmp_prefix varchar(255) set tmp_prefix test_log_backset startTime convert(varchar,GETDATE( ) , 120)set sCommandText echo 开始备份 日志备份 DBName startTime D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandText---- 正式备份-- 类似 2017-12-21-1000 set tlog_time substring( replace(replace(replace(CONVERT(varchar, getdate() , 120 ),-,-), ,-),:,) , 1, 15)set base_dir D:\bak_db\tlog_db\-- 日志备份 该用户数据库 生成类似 account_tlog_2017-12-20-1000.bakset tmp_file_name base_dir tmp_prefix DBName _tlog_ tlog_time .bakBACKUP log DBName TO DISK tmp_file_name with STATS 1,compression----- 结束备份set endTime convert(varchar,GETDATE( ) , 120)set sCommandText echo 结束备份 日志备份 DBName endTime D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandTextset sCommandText echo ------------------- D:\bak_db\backup_detail.txtexec xp_cmdshell sCommandTextend
GO
执行代码
exec [sp_tlog_BackupDB] 库名
等待执行完毕即可ldf备份
进行尾日志还原的时候mdf不可用ldf可用的情况下需要将ldf备份然后进行还原。(前提是做过全备等备份然后进行还原)尾日志备份代码 修改相关路径和库名即可BACKUP LOG 库名 TO DISKND:\bak_db\tlog_db2\tail_log.bak WITH INIT,NO_TRUNCATE;等待执行完毕即可事务备份
如果误操作进行节点还原需要有事务备份然后进行节点还原。此操作需要一些前置条件支持前置条件
1、数据库右键属性在选项中查看数据恢复模式为“完整”
2、在数据误操作之前进行过完整数据备份备份方法
1、命令行操作方式
BACKUP LOG [test] TO DISK ND:\bak_db\testback.trn WITH NOFORMAT, NOINIT, NAME Ntest-完整 数据库 备份, SKIP, NOREWIND, NOUNLOAD, STATS 102、图形化操作
a/选择要恢复的数据库右键-“任务”-“备份”
b/在弹出的备份对话框中备份类型选择“事务日志”并添加备份文件“log.trn”-------备份类型事务日志目标磁盘删除原有的路径添加到自定义的路径。
c/点击确定完成事务日志备份注意事项
1、sqlserver profile的记录慢sql时间单位问题 在数据表中记录的微妙。但是设置的那个地方填写的是毫秒
2、数据库属性中恢复模式问题 一定要选择完整才能进行全备和事务备份等。
3、做全备、差异备的时候一定要停掉所有的连接关闭job。否则恢复的时候就会显示恢复中这是sqlserver的事务未提交问题
4、重启sqlserver服务器的时候一定要关闭数据库服务关闭job等。防止数据库事务未提交完毕就重启会导致数据库在恢复中。
SQL Server 还原
全备还原
还原代码 修改相应的路径即可USE [master]
RESTORE DATABASE [test] FROM DISK ND:\bak_db\quanbei_db\test_backtest_full_2020-02-12.bakWITH FILE 1, NORECOVERY,NOUNLOAD,STATS 5 --如果此处不是最后一步还原的话使用NORECOVERY如果此处是最后一步就需要使用RECOVERY否则会出问题
GO等待执行完毕即可完成全备还原差异备份还原
差异备还原代码修改相对路径即可
USE [master]
RESTORE DATABASE [test] FROM DISK ND:\bak_db\differential_db\test_differential_backtest_differential_2020-02-12.bakWITH FILE 1, NORECOVERY,NOUNLOAD,STATS 5 --如果此处不是最后一步还原的话使用NORECOVERY如果此处是最后一步就需要使用RECOVERY否则会出问题
GO等待执行完毕即可完成差异备份还原日志备/尾日志还原
日志备份尾日志备份的还原都用该命令即可。记得修改相对应的路径USE [master]
RESTORE LOG [test] FROM DISK ND:\bak_db\tlog_db2\tail_log.bakWITH FILE 1, RECOVERY,NOUNLOAD,STATS 5 --如果此处不是最后一步还原的话使用NORECOVERY如果此处是最后一步就需要使用RECOVERY否则会出问题
GO --- 如果上面报错可以尝试将WITH后面的内容都删掉只保留RECOVERY等待执行完毕即可完成日志备份还原事务日志还原
1、可视化操作
a.打开数据库还原页面检查源数据库及目标数据库是否正确检查完整备份及日志备份是否完整并点击“时间线”进入时间线选择
b.在弹出的时间线对话框中选择“特定日期和时间”并将时间设置为误删之前时间点如16:20
c.再次检查目标数据库及备份集信息无误点击“选项”进入选项卡勾选“覆盖现有数据库”及“关闭现有连接”复选框然后确定直至还原成功2、命令行操作
USE [master]
ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE --- 修改数据库为单用户模式
-- 还原前进行尾日志备份
BACKUP LOG [test] TO DISK ND:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\test_LogBackup_2020-02-12_19-24-21.bakWITH NOFORMAT, NOINIT, NAME Ntest_LogBackup_2020-02-12_19-24-21, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS 5-- 还原全备备份
RESTORE DATABASE [test] FROM DISK ND:\bak_db\quanbei_db\test_backtest_full_2020-02-12.bakWITH FILE 2, NORECOVERY, NOUNLOAD, REPLACE, STATS 5-- 还原事务日志备份并设置还原到的时间节点
RESTORE LOG [test] FROM DISK ND:\bak_db\testback.trnWITH FILE 1, NOUNLOAD, STATS 5, STOPAT N2020-02-12T18:30:00ALTER DATABASE [test] SET MULTI_USER
GO等待还原成功即可备份还原中的问题
还原失败需要某些权限
问题描述 SQL Server 阻止了对组件 ‘xp_cmdshell’ 的 过程’sys.xp_cmdshell’ 的访问因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 ‘xp_cmdshell’。有关启用 ‘xp_cmdshell’ 的详细信息请参阅 SQL Server 联机丛书中的 “外围应用配置器”。 EXEC sp_configure show advanced options, 1;
RECONFIGURE;
EXEC sp_configure xp_cmdshell, 1;
RECONFIGURE;重命名sql Server数据库名称失败
问题描述 消息 5030级别 16状态 2第 1 行无法用排他锁锁定该数据库以执行该操作。 视图模式解决
1、在对象资源管理器中连接到 SQL Server 数据库引擎实例然后展开该实例。
2、右键单击要更改的数据库再单击“属性”。
3、在“数据库属性”对话框中单击“选项”页。
4、在“限制访问”选项中选择“单用户”。
5、如果其他用户连接到数据库将出现“打开的连接”消息。若要更改属性并关闭所有其他连接请单击“是”。
6、重命名完了再限制访问的属性改回原来的就可以啦命令行模式解决:
--1.执行SQL
ALTER DATABASE db_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--修改为单用户模式
--2.然后关闭所有的查询窗口重命名你的数据库名称
--3.执行SQL
ALTER DATABASE db_database SET MULTI_USER
--再修改为多用户模式作业迁移
单个迁移
在ssms中打开作业文件夹右键要迁移的作业编写作业脚本为-create到-新的查询窗口将生成的脚本全部赋值下来去目标数据库上面新建查询窗口执行刚才生成的脚本有可能迁移过后的脚本是禁用状态
作业文件夹右键-管理计划-根据时间说明将对应的job开启。或者逐个点击开启问题
1、执行失败可能原因是源服务器使用的windows账号登陆的导致无法执行这个时候需要将登陆账号(owner_login_name)改为本地的账号
本地登陆名-》ssms中安全性-登录名下所有启用的账户此处尽量保证源db的用户和目标db的用户一致。批量迁移
在ssms中打开作业文件夹选中作业文件夹然后按F7出现对象管理器详细信息界面按着ctrl键多选要迁移的作业job右键-编写作业脚本为-create到-新的查询窗口将生成的脚本全部复制到迁移目标数据库上面新建查询窗口执行刚才生成的脚本就可以完成批量迁移有可能迁移过后的脚本是禁用状态
作业文件夹右键-管理计划-根据时间说明将对应的job开启。或者逐个点击开启问题
1、执行失败可能原因是源服务器使用的windows账号登陆的导致无法执行这个时候需要将登陆账号(owner_login_name)改为本地的账号
本地登陆名-》ssms中安全性-登录名下所有启用的账户此处尽量保证源db的用户和目标db的用户一致。登陆账号迁移
批量迁移脚本
---SQL 2005以上版本适用--找到了解决办法.
--1.在A服务器上执行
USE master
GO
IF OBJECT_ID (sp_hexadecimal) IS NOT NULLDROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimalbinvalue varbinary(256),hexvalue varchar (514) OUTPUT
AS
DECLARE charvalue varchar (514)
DECLARE i int
DECLARE length int
DECLARE hexstring char(16)
SELECT charvalue 0x
SELECT i 1
SELECT length DATALENGTH (binvalue)
SELECT hexstring 0123456789ABCDEF
WHILE (i length)
BEGINDECLARE tempint intDECLARE firstint intDECLARE secondint intSELECT tempint CONVERT(int, SUBSTRING(binvalue,i,1))SELECT firstint FLOOR(tempint/16)SELECT secondint tempint - (firstint*16)SELECT charvalue charvalue SUBSTRING(hexstring, firstint1, 1) SUBSTRING(hexstring, secondint1, 1)SELECT i i 1
ENDSELECT hexvalue charvalue
GOIF OBJECT_ID (sp_help_revlogin) IS NOT NULLDROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin login_name sysname NULL AS
DECLARE name sysname
DECLARE type varchar (1)
DECLARE hasaccess int
DECLARE denylogin int
DECLARE is_disabled int
DECLARE PWD_varbinary varbinary (256)
DECLARE PWD_string varchar (514)
DECLARE SID_varbinary varbinary (85)
DECLARE SID_string varchar (514)
DECLARE tmpstr varchar (1024)
DECLARE is_policy_checked varchar (3)
DECLARE is_expiration_checked varchar (3)DECLARE defaultdb sysnameIF (login_name IS NULL)DECLARE login_curs CURSOR FORSELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins lON ( l.name p.name ) WHERE p.type IN ( S, G, U ) AND p.name sa
ELSEDECLARE login_curs CURSOR FORSELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins lON ( l.name p.name ) WHERE p.type IN ( S, G, U ) AND p.name login_name
OPEN login_cursFETCH NEXT FROM login_curs INTO SID_varbinary, name, type, is_disabled, defaultdb, hasaccess, denylogin
IF (fetch_status -1)
BEGINPRINT No login(s) found.CLOSE login_cursDEALLOCATE login_cursRETURN -1
END
SET tmpstr /* sp_help_revlogin script
PRINT tmpstr
SET tmpstr ** Generated CONVERT (varchar, GETDATE()) on SERVERNAME */
PRINT tmpstr
PRINT
WHILE (fetch_status -1)
BEGINIF (fetch_status -2)BEGINPRINT SET tmpstr -- Login: namePRINT tmpstrIF (type IN ( G, U))BEGIN -- NT authenticated account/groupSET tmpstr CREATE LOGIN QUOTENAME( name ) FROM WINDOWS WITH DEFAULT_DATABASE [ defaultdb ]ENDELSE BEGIN -- SQL Server authentication-- obtain password and sidSET PWD_varbinary CAST( LOGINPROPERTY( name, PasswordHash ) AS varbinary (256) )EXEC sp_hexadecimal PWD_varbinary, PWD_string OUTEXEC sp_hexadecimal SID_varbinary,SID_string OUT-- obtain password policy stateSELECT is_policy_checked CASE is_policy_checked WHEN 1 THEN ON WHEN 0 THEN OFF ELSE NULL END FROM sys.sql_logins WHERE name nameSELECT is_expiration_checked CASE is_expiration_checked WHEN 1 THEN ON WHEN 0 THEN OFF ELSE NULL END FROM sys.sql_logins WHERE name nameSET tmpstr CREATE LOGIN QUOTENAME( name ) WITH PASSWORD PWD_string HASHED, SID SID_string , DEFAULT_DATABASE [ defaultdb ]IF ( is_policy_checked IS NOT NULL )BEGINSET tmpstr tmpstr , CHECK_POLICY is_policy_checkedENDIF ( is_expiration_checked IS NOT NULL )BEGINSET tmpstr tmpstr , CHECK_EXPIRATION is_expiration_checkedENDENDIF (denylogin 1)BEGIN -- login is denied accessSET tmpstr tmpstr ; DENY CONNECT SQL TO QUOTENAME( name )ENDELSE IF (hasaccess 0)BEGIN -- login exists but does not have accessSET tmpstr tmpstr ; REVOKE CONNECT SQL TO QUOTENAME( name )ENDIF (is_disabled 1)BEGIN -- login is disabledSET tmpstr tmpstr ; ALTER LOGIN QUOTENAME( name ) DISABLEENDPRINT tmpstrENDFETCH NEXT FROM login_curs INTO SID_varbinary, name, type, is_disabled, defaultdb, hasaccess, denyloginEND
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO批量迁移执行方法
exec master..sp_help_revlogin执行上面的语句可以生成创建账号登录名的脚本命令注意一般情况下就迁移自己创建的即可其他的上面登录名可以不用迁移。NT开头的和windows登陆的两种可以忽略这个这次执行命令可以看到只有一个自建登录名test_login。 这个脚本无法生成sa需要自己创建修改密码等 总结 使用这个办法有2个好处。 1。可以批量同步所有需要的登录账号 2.由于产生的SID是相同的不会有孤立账号的问题。在ALWAY ON环境下我们就需要采用这种方式。不然没有ALWAYS ON 切换后都需要手动处理 孤立账号的 批量迁移连接服务器 然后把生成的脚本COPY到新的服务器上面执行就可以了。在建立完成后一定要测试链接服务器是不是可用 总结
批量迁移链接服务器的方法和批量迁移定时作业的方法机会是一样的。
记录数据库的DDL
使用脚本记录下函数存储过程触发器表结构的修改
-- Table to store the data
CREATE TABLE DDLLog(id INT NOT NULL IDENTITY(1,1),event_type sysname,object_id int,object_name sysname,change_date datetime,changed_by sysname);
GOCREATE TRIGGER tr_DDLLog
ON DATABASE
FOR DDL_PROCEDURE_EVENTS,DDL_FUNCTION_EVENTS,DDL_VIEW_EVENTS,DDL_TRIGGER_EVENTS,DDL_TABLE_EVENTS
AS
BEGININSERT INTO DDLLog(event_type, object_id, object_name, change_date, changed_by)VALUES (EVENTDATA().value((/EVENT_INSTANCE/EventType)[1], NVARCHAR(255)),EVENTDATA().value((/EVENT_INSTANCE/ObjectId)[1], INT),EVENTDATA().value((/EVENT_INSTANCE/ObjectName)[1], NVARCHAR(255)),getdate(),ORIGINAL_LOGIN());
END测试
--创建存储过程
CREATE PROCEDURE ChangeTest ASPRINT 1;
GO
--修改存储过程
ALTER PROCEDURE ChangeTest ASPRINT 1;
GO
--删除存储过程
DROP PROCEDURE ChangeTest;
GO结果 总结
当下次发生问题时可以自己心中有数。
数据库重命名
问题描述 在数据库重命名的时候平时最经常使用的就是sp_renamedb 或者是在SSMS工具中右键进行重命名。这样操作简单快捷。但是有一个问题就是对应的mdf,ldf文件名字不会跟着改变.而且数据库的逻辑文件名也不会跟着改变。 最佳实践 修改数据库的逻辑文件名字
ALTER DATABASE Mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATEALTER DATABASE [Mydb] MODIFY FILE (NAMENMydb, NEWNAMENMydbNew)
GO
ALTER DATABASE [Mydb] MODIFY FILE (NAMENMydb_log, NEWNAMENMydbNewlog)
GO修改物理文件名字
分离数据库
USE [master]
GO
EXEC master.dbo.sp_detach_db dbname NMydb
GO修改物理文件名字 找到db对应的ldf和mdf的路径进行重命名
附加数据库
USE [master]
GO
CREATE DATABASE MydbNew ON
( FILENAME NC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MydbNew.mdf ),
( FILENAME NC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MydbNew_log.ldf )FOR ATTACH
GO设置为多用户
ALTER DATABASE MydbNew SET MULTI_USER检查
--查看数据库的文件名 修改最后的db名字
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id DB_ID(NMydbNew)总结 修改数据库名字是一个很常见的操作直接改名很容易但是可能会以后留下隐患。 查询所有数据库的大小
with fs
as
(select database_id, type, size * 8.0 / 1024 sizefrom sys.master_files
)
select name,(select cast(round(sum(size),2) as numeric(15,2)) from fs where type 0 and fs.database_id db.database_id) DataFileSizeMB,(select cast(round(sum(size),2) as numeric(15,2)) from fs where type 1 and fs.database_id db.database_id) LogFileSizeMB
from sys.databases db
order by 2 desc;
查询当前数据库的大小
EXEC sp_spaceused
数据库显示“正在还原”的处理方法
1新建查询复制下面代码修改数据库名后执行
--恢复并且回到可访问状态要执行RESTORE database 数据库名 with recovery2点击对象资源管理器-数据库-右键-刷新 再次展开就OK了。
数据库发生死锁怎么处理
查看被锁进程号及被锁表名
SELECT request_session_id spid, --进程号OBJECT_NAME(resource_associated_entity_id) tableName --被锁表名
FROM sys.dm_tran_locks
WHERE resource_type OBJECT解锁
KILL [进程号]创建杀掉引起死锁进程的存储过程
create proc dbo.p_killspid dbname varchar(200) --要关闭进程的数据库名
as declare sql nvarchar(500) declare spid nvarchar(20)declare #tb cursor for select spidcast(spid as varchar(20)) from master.dbo.sysprocesses where dbiddb_id(dbname) open #tb fetch next from #tb into spid while fetch_status0 begin exec(kill spid) fetch next from #tb into spid end close #tb deallocate #tb
go
sqlserver存储过程杀掉数据库中死锁
Create proc p_lockinfo kill_lock_spid bit1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示 show_spid_if_nolock bit1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示 as declare count int,s nvarchar(1000),i int select ididentity(int,1,1),标志, 进程IDspid,线程IDkpid,块进程IDblocked,数据库IDdbid, 数据库名db_name(dbid),用户IDuid,用户名loginame,累计CPU时间cpu, 登陆时间login_time,打开事务数open_tran, 进程状态status, 工作站名hostname,应用程序名program_name,工作站进程IDhostprocess, 域名nt_domain,网卡地址net_address into #t from( select 标志死锁的进程, spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1a.spid,s20from master..sysprocesses a join ( select blocked from master..sysprocesses group by blocked )b on a.spidb.blocked where a.blocked0union all select |_牺牲品_, spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1blocked,s21from master..sysprocesses a where blocked0 )a order by s1,s2 select countrowcount,i1if count0 and show_spid_if_nolock1begin insert #t select 标志正常的进程, spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time, open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master..sysprocesses set countrowcount end if count0 begin create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255)) if kill_lock_spid1begin declare spid varchar(10),标志 varchar(10) while icount begin select spid进程ID,标志标志 from #t where idi insert #t1 exec(dbcc inputbuffer(spid)) if 标志死锁的进程 exec(kill spid) set ii1 end end else while icount begin select sdbcc inputbuffer(cast(进程ID as varchar)) from #t where idi insert #t1 exec(s) set ii1 end select a.*,进程的SQL语句b.EventInfo from #t a join #t1 b on a.idb.id end使用
非原创
在mester中创建存储过程
存储过程执行命令
exec master.dbo.p_lockinfo 0,0 ---显示死锁的进程,不显示正常的进程
exec master.dbo.p_lockinfo 1,0 ---杀死死锁的进程,不显示正常的进程
exec master.dbo.p_lockinfo 0,1 ---显示死锁的进程,显示正常的进程
exec master.dbo.p_lockinfo 1,1 ---杀死死锁的进程,显示正常的进程
