数据库cpu过大处理方法

-------杀掉占用进程
select DB_NAME(dbid) as dbname,*
from master..sysprocesses
where spid > 50 and spid <> @@spid and cpu > 1000000use 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;
GoDECLARE @SQL VARCHAR(MAX);SET @SQL=''SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)FROM master..sysprocessesWHERE 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 masterdeclare @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'