常用的SQL SERVER处理语句

---------------------------------清理数据库日志-----------------------------------------------------------
DBCC SHRINKFILE (N'test_log' , 10)
第一个参数是日志文件名，第二个参数是目标文件大小，以M为单位。

如果出现了“由于最小日志空间要求,无法收缩日志文件 2”错误提示，一般情况下是做了订阅发布
---------------------------------查询数据库名字-----------------------------------------------------------
SELECT NAME, size FROM sys.database_files
---------------------------------查询表的记录行数-----------------------------------------------------------
select  a.name as '表名',b.rows as '表数据行数'
from sysobjects a inner join sysindexes b
on a.id = b.id
where   a.type = 'u'
and b.indid in (0,1)
--and a.name not like 't%'
order by b.rows desc
-----------更改事务隔离级别，使用提交读快照，该模式下会读取事务已经提交更改后的数据，但是会造成事务如果执行失败，再次之前读取的数据会丢失的情况-------------
ALTER DATABASE TicketDistribution SET READ_COMMITTED_SNAPSHOT ON  
---------------------------------杀掉数据当前所有连接-----------------------------------------------------------
DECLARE @SQL VARCHAR(MAX);
SET @SQL=''

SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID)
FROM master..sysprocesses
WHERE dbid=DB_ID('DB_CommonTotal');

EXEC(@SQL);
GO
ALTER DATABASE DB_CommonTotal SET SINGLE_USER;

---------------------------------订阅发布未完全删除后的处理-----------------------------------------------------------
 SELECT name,log_reuse_wait_desc FROM sys.databases where name='his'
 DBCC LOGINFO('his')
EXEC sp_removedbreplication 'his'
DBCC SHRINKFILE (N'histmp_Log' , 1, TRUNCATEONLY)
---------------------------------解锁帐户-----------------------------------------------------------
‘帐户当前被锁定,所以用户 'sa' 登录失败。系统管理员无法将该帐户解锁’解决方法
如果短时间内不停连接，就会被SQL SERVER误认为是这是攻击，会将此账号锁定。
要用windows身份验证登录，在查询分析器里输入：
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'password' unlock, check_policy = off,
check_expiration = off ;
GO
--------------------------------插入时间时判断跟系统内记录是否有重叠--------------------------------------------------------
declare @starttime datetime
declare @endtime datetime
declare @MinParValue int
declare @MaxParValue int
set @starttime='2014-6-17'
set @endtime='2014-6-17'
set @MinParValue=10
set @MaxParValue=20

--step 1 判断表中是否有记录 如果没有记录则直接插入
select count(1) from [CommissionSetting]
--step 2 判断表中是否存在最大区间,如果记录为0,并且step 1中的记录大于1,即不存在最大日期区间则进入step3
select count(1) from [CommissionSetting] where StartDate=(select min(StartDate) from [CommissionSetting]) and EndDate=(select Max(EndDate) from [CommissionSetting])
--step 3 与传入的日期进行判断，判断是否是最大日期区间,如果记录大于0则表示日期区间为最大日期区间,可以使用.若为等于0则表示不是最大日期区间,进入step4
select count(1) from [CommissionSetting] where @starttime<=(select min(StartDate) from [CommissionSetting]) and @endtime>=(select Max(EndDate) from [CommissionSetting])
--step 4 与表中日期和充值卡进行对比判断
select * from [CommissionSetting] where ((StartDate<=@starttime and EndDate>=@starttime) or (StartDate<=@endtime and EndDate>=@endtime) or (@starttime<=StartDate and EndDate<=@endtime))
 and ((MinParValue<=@MinParValue and MaxParValue>=@MinParValue) or (MinParValue<=@MaxParValue and MaxParValue>=@MaxParValue) or (@MinParValue<=MinParValue and MaxParValue<=@MaxParValue))
-----------------------------------------------查询SQL耗时-------------------------------------------------------------------------
 SET STATISTICS PROFILE ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
/*--你的SQL脚本开始*/
SELECT [TestCase] FROM [TestCaseSelect]
/*--你的SQL脚本结束*/
SET STATISTICS PROFILE OFF
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
-----------------------------------------------查询缺少的索引并构造索引生成语句------------------------------------------------------
SELECT
avg_user_impact AS average_improvement_percentage,
avg_total_user_cost AS average_cost_of_query_without_missing_index,
'CREATE INDEX ix_' + [statement] +
ISNULL(equality_columns, '_') +
ISNULL(inequality_columns, '_') + ' ON ' + [statement] +
' (' + ISNULL(equality_columns, ' ') +
ISNULL(inequality_columns, ' ') + ')' +
ISNULL(' INCLUDE (' + included_columns + ')', '')
AS create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN
sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats c ON
b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 40
-------------------------------------------------查询表大小-----------------------------------------------------------------------------
SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC
-------------------------------------------------重置存储过程计划-----------------------------------------------------------------------------
exec sp_recompile @objname='DokAs_GetCustomerServerStatistics'
 -----------------------------------------------查询资源占用------------------------------------------------------------------------------

 SELECT TOP 10 c.client_net_address   会话IP,der.session_id 会话ID,[wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',  [start_time] AS '开始时间',  [status] AS '状态',
  [command] AS '命令',  dest.[text] AS 'sql语句',  DB_NAME([database_id]) AS '数据库名',
  [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 [wait_resource] AS '等待的资源', [reads] AS '物理读次数', [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数', [row_count] AS '返回结果行数' FROM sys.[dm_exec_requests] AS der
 CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
 LEFT JOIN sys.dm_exec_connections c(nolock) ON c.session_id=der.session_id
 WHERE der.[session_id]>50 AND DB_NAME(der.[database_id])='his'  
 ORDER BY [cpu_time] DESC

   select client_net_address, *, c.local_net_address,c.connect_time,s.host_name,s.program_name,s.login_name  from
sys.dm_exec_connections c inner join sys.dm_exec_sessions  s on c.session_id=s.session_id
order by c.session_id   

 
---------------------------------------------------缺失索引--------------------------------------------------------
SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle = 2
---------------------------------------------------无用索引-------------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(O.Schema_ID) AS SchemaName
, OBJECT_NAME(I.object_id) AS TableName
, I.name AS IndexName
INTO #TempNeverUsedIndexes
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempNeverUsedIndexes
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(O.Schema_ID) AS SchemaName
, OBJECT_NAME(I.object_id) AS TableName
, I.NAME AS IndexName
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id
AND I.index_id = S.index_id
AND DATABASE_ID = DB_ID()
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0
AND I.name IS NOT NULL
AND S.object_id IS NULL'
SELECT * FROM #TempNeverUsedIndexes
ORDER BY DatbaseName, SchemaName, TableName, IndexName
DROP TABLE #TempNeverUsedIndexes

--------------------------经常被大量更新，但是却基本不适用的索引项--------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
INTO #TempUnusedIndexes
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes
SELECT TOP 20
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND i.name IS NOT NULL
ORDER BY s.user_updates DESC'
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
DROP TABLE #TempUnusedIndexes
--------------------------------------------------------------