网络服务器 Redis Angular Kotlin xml inheritance knockoutjs handlebarsjs 郑州网站建设 两个正态分布相乘 oracle显示所有数据库 wps临时文件 反函数的二阶导数 mysql 连接 java实例 java正则表达式用法 java运行环境 javaabstract java连接mysql的jar包 java安装与配置 java代码 java异常 java语言运算符 java游戏开发教程 ntscan 广告代码 java小程序 忧思华光玉攻略 电脑必备软件排行榜 计算机网络自顶向下 ps选择反向快捷键 js获取父节点 winhex教程 绿幕抠图 mp4剪切合并大师 sendto函数 正则表达式数字 airdrop是什么 羽化快捷键 软媒u盘启动
当前位置: 首页 > 学习教程  > 编程语言

sql server数据库常用脚本记录

2020/8/31 12:44:29 文章标签:

数据库cpu过大处理方法

-------杀掉占用进程
select DB_NAME(dbid) as dbname,*
from master..sysprocesses
where spid > 50 and spid <> @@spid and cpu > 1000000
 
use master
declare @sql varchar(100)
while 1=1
begin
select top 1 @sql = 'kill '+cast(spid as varchar(5))
from master..sysprocesses
where spid > 50 and spid <> @@spid and cpu > 1000000
if @@rowcount = 0
break
exec(@sql)
End

cpu消耗过大排序

SELECT TOP 10 
qs.query_hash AS ID,
SUBSTRING(qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))*2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS [Statement],
qt.text AS [SQL],
DB_NAME(qt.dbid) AS DBName,
qs.execution_count,
qs.total_worker_time AS total_cpu_time,
qs.total_worker_time/qs.execution_count AS average_cpu_time,
qs.total_elapsed_time,
qs.total_elapsed_time/qs.execution_count AS average_elapsed_time,
--qs.total_rows,
--qs.total_rows/qs.execution_count AS average_rows,
qs.total_logical_reads,
qs.total_logical_reads/qs.execution_count AS average_logical_reads,
qs.total_physical_reads,
qs.total_physical_reads/qs.execution_count AS average_physical_reads,
qs.total_logical_writes,
qs.total_logical_writes/qs.execution_count AS average_logical_writes,
qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) as qt
ORDER BY average_cpu_time DESC;

各数据库内存使用情况

-----查询数据库实例下各数据库内存使用情况
SELECT   
CASE database_id   
    WHEN 32767 THEN 'ResourceDb'   
    ELSE db_name(database_id) END AS Database_name  
,count(*) AS cached_pages_count  
,count(*)*8/1024 AS cached_space_in_mb  
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb  
FROM sys.dm_os_buffer_descriptors(nolock)  
GROUP BY db_name(database_id) ,database_id  
ORDER BY cached_pages_count DESC;

最消耗cpu资源的语句和数据库

---查询数据库实例下最消耗cpu资源的语句和数据库
SELECT TOP 50
        [Average CPU used] = total_worker_time / qs.execution_count
        ,[Total CPU used] = total_worker_time
        ,[Execution count] = qs.execution_count
        ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
        ,[Parent Query] = qt.text
        ,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
--WHERE DB_NAME(qt.dbid) = 'pnl'  -- Filter on a given database.
ORDER BY [Average CPU used] DESC;

sql server 2008 清空数据库中ldf日志文件

---- 将Whir_InternalSystem替换为您要操作的数据库即可
USE [master]
ALTER DATABASE [Whir_InternalSystem] 
SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [Whir_InternalSystem] 
SET RECOVERY SIMPLE
GO
USE [Whir_InternalSystem]
DECLARE @logname VARCHAR(150)
SELECT @logname = NAME
FROM   sys.database_files
WHERE  NAME LIKE'%log'
DBCC SHRINKFILE(@logname, 11, TRUNCATEONLY)
GO
USE [master]
ALTER DATABASE [Whir_InternalSystem]
SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [Whir_InternalSystem] 
SET RECOVERY FULL
GO

收缩temdb脚本

USE [tempdb]
GO
dbcc freesystemcache('ALL')
Go
DBCC SHRINKfile(N'tempdev',2)--收缩到MB 
GO

数据库单用户改成正常模式语句

--更改下面的的数据库名称,执行脚本,单用户改成正常模式
USE master;
Go
 DECLARE @SQL VARCHAR(MAX);
 SET @SQL=''
 SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)
 FROM master..sysprocesses
 WHERE dbid=DB_ID('数据库名');
EXEC(@SQL);
GO 
ALTER DATABASE  数据库名 SET MULTI_USER;
go

紧急模式

–紧急模式

alter database 数据库名 set EMERGENCY 

–恢复紧急模式

ALTER DATABASE 数据库名 SET ONLINE 

查询数据库下所有DBCC异常的库

use master
go
DECLARE @SQL VARCHAR(MAX), @TableName VARCHAR(100)
SELECT @SQL = ''

DECLARE CRS_SQL CURSOR FOR 
SELECT name FROM sysdatabases 
WHERE name NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') ORDER BY name
OPEN CRS_SQL 
FETCH NEXT FROM CRS_SQL INTO @TableName 
WHILE( @@FETCH_STATUS = 0 )
BEGIN 
   SELECT @SQL = @SQL + 'use ' + @TableName + ' dbcc checkdb with no_infomsgs' + CHAR(13) + CHAR(10)
   FETCH NEXT FROM CRS_SQL INTO @TableName
END
CLOSE CRS_SQL 
DEALLOCATE CRS_SQL 

EXEC (@SQL)

kill杀掉某一个客户锁死情况

select DB_NAME(dbid) as dbname,*
from master..sysprocesses
where spid > 50 and spid <> @@spid and DB_NAME(dbid) = '数据库名';

use master

declare @sql varchar(100)
while 1=1
begin
select top 1 @sql = 'kill '+cast(spid as varchar(5))
from master..sysprocesses
where spid > 50 and spid <> @@spid and DB_NAME(dbid) = '数据库名'
if @@rowcount = 0
break
exec(@sql)
end

数据库C盘日志新生成脚本

EXEC sp_cycle_errorlog ;

查数据库账套数

SELECT COUNT(*) FROM sys.databases

RDS给大账号授权master建表权限

--yun要为大账号用户名
EXEC sp_rds_master_ddl_perms 'yun', 'grant' 

RDS给大账号添加数据库权限

-- yun要为大账号用户、test 为授权的库名
EXEC sp_rds_changedbowner @loginame = 'yun',@db_name  = 'test'

RDS授权数据库

-- test 为授权的库名、username 要改成rds的用户名
USE test
CREATE USER username FOR LOGIN username

RDS授权master库里面的表

--增删改查权限
EXEC sp_rds_master_dml_perms '授权账号名称', '授权的master里面的表名称', 'SELECT,INSERT,UPDATE,DELETE';
--举例
EXEC sp_rds_master_dml_perms '大账号', 'CMGMPlugInRegisterInfo', 'SELECT,INSERT,UPDATE,DELETE'; 

RDS授权所有master库里面的表,先查,在复制执行

Select 'EXEC sp_rds_master_dml_perms '' 大账号'', ''' + name +
''', ''SELECT,INSERT,UPDATE,DELETE'''
FROM SysObjects Where XType='U' and name not like '_$$_u_%' and name not like 'trace_xe%'

RDS数据库联机语句

–rds数据库

sp_rds_set_db_online '数据库名'

–esc数据库

ALTER DATABASE  数据库名 SET online WITH ROLLBACK AFTER 0

分离数据库

USE master
go
DECLARE @SQL VARCHAR(MAX);
SET @SQL=''
SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)
FROM master..sysprocesses
WHERE dbid=DB_ID('数据库名');
EXEC(@SQL);
GO   --先杀掉连接,在分离
exec sys.sp_detach_db  数据库名  --分离数据库
go 

kill掉数据库连接

USE master
go
DECLARE @SQL VARCHAR(MAX);
SET @SQL=''
SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)
FROM master..sysprocesses
WHERE dbid=DB_ID('数据库名');
EXEC(@SQL);
GO

RDS数据库修改名称

EXEC sp_rds_rename_database 'oldname','newname'

本文链接: http://www.dtmao.cc/news_show_150033.shtml

附件下载

相关教程

    暂无相关的数据...

共有条评论 网友评论

验证码: 看不清楚?