SQL server 全连接 sql server连接命令

=======================================================================

SQL本地实例链接其他实例, 调用其它实例的数据库中的表
方法一: SMSS中
  1. 实例--服务器对象--链接服务器--右键--新建链接服务器
  2. 弹出页面--常规选项卡--链接服务器名称(随意)--提供程序(Microsoft OLE DB Provider for SQL Server)--数据源(对方实例的名称,即计算机名)
                  --安全性选项卡--使用此安全上下文建立连接(输入对方SQL Server的登陆帐号和密码)
                  --服务器选项--RPC(改为True)--RPC Out(改为True)
  3. 调用方法: 链接服务器名.数据库名.用户名(dbo).表名

方法二:

exec sp_addlinkedserver '链接服务器名', '', 'SQLOLEDB', '对方实例地址'
  exec sp_addlinkedsrvlogin '链接服务器名', 'false',null,'登录用户名', '密码'

  调用方法同上.

=======================================================================

SET STATISTICS TIME ON/OFF  --这个开关能输出SQL语句各阶段所消耗的时间
SET STATISTICS IO ON/OFF  --这个开关能够输出语句做的物理和逻辑读取次数，这个开关对SQL执行计划的性能调优非常重要: 逻辑读次数越小越好。 
SET STATISTICS PROFILE ON/OFF  --这个开关能返回语句执行详情, 即执行计划以及语句运行在每一步的实际返回行数

========================================================================

SQL Server Profiler 筛选某一操作:
  第一步, SSMS-工具-SQL Server Profiler-连接数据库-文件-新建跟踪-运行;
  第二步, 筛选某一操作: 跟踪窗体-右键属性-事件选择-列筛选器-找到"TextData"-类似于"%select%".   --也可以筛选insert\update等操作
              想筛选应用程序可以在列筛选器中选择"Application Name"输入程序名和通配符进行过滤.

========================================================================

use master;
restore   database   111111   
  from   disk='d:\111111-20160929.bak'     --c:\a.bak是备份文件名   
  with   move   '111111'   to   'd:\111111\111111.mdf'     --将111111恢复到文件d:\111111\111111.mdf   
          ,move   '111111_log'   to   'd:\111111\111111_log.ldf'    , --将111111_log恢复到文件d:\111111\111111\111111.ldf  
NOUNLOAD, REPLACE, STATS = 10 
GO                                                                                          --RESTORE FILELISTONLY FROM DISK = N'd:\111111-20160929.bak'

=======================================================

查看备份文件中的信息（DB的逻辑文件名）

restore filelistonly from disk='E:\CustomsGrab_backup_2021_05_14_010000_5783410.bak'

=========================================================================

PS:普通正常DB还原命令：

RESTORE DATABASE [222222] FROM
DISK = N'e:/222222-20131202.bak' WITH FILE = 1,
NOUNLOAD, REPLACE, STATS = 10 
GO

 
=========================================================================

查询21.36上的数据库连接数和详细连接情况：

SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoAdmin'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoAnalysis'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoAuth'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiinfoBanking'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiinfoColumn'
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoDSdata'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoMegaData'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoPrice'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoMegaData'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoProduct'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoStandardConfig'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoStock'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoTargetPrice'
SELECT count(*) FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoUsers'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50
=================排查mssql连接=============================
--查询DB与某IP地址的连接情况:
select Session_id, client_net_address '客户端IP',local_net_address '服务器的IP',client_tcp_port '客户端端口号',net_packet_size '数据包的大小',num_reads '读次数', num_writes '写次数' 
  from sys.dm_exec_connections 
    where client_net_address='192.168.10.32' order by  num_writes desc --and client_tcp_port IN (57914,51608)--根据spid查询锁表的SQL语句
SELECT DISTINCT
'进程ID' = STR(a.spid, 4)
, '进程ID状态' = CONVERT(CHAR(10), a.status)
, '死锁进程ID' = STR(a.blocked, 2)
, '工作站名称' = CONVERT(CHAR(20), a.hostname)
, '执行命令的用户' = CONVERT(CHAR(20), SUSER_NAME(a.uid))
, '数据库名' = CONVERT(CHAR(10), DB_NAME(a.dbid))
, '应用程序名' = CONVERT(CHAR(10), a.program_name)
, '正在执行的命令' = CONVERT(CHAR(16), a.cmd)
, '登录名' = a.loginame
, '执行语句' = b.text
FROM master..sysprocesses a CROSS APPLY
sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.blocked IN ( SELECT blocked
FROM master..sysprocesses ) and a.spid IN (select request_session_id from sys.dm_tran_locks where resource_type='OBJECT')

=========================================================================

kill进程占用的DB

USE master
go
DECLARE @Sql NVARCHAR(max)
SET @Sql=''
select @Sql=@Sql+'kill '+cast(spid as varchar(50))+';' from sys.sysprocesses where dbid=DB_ID('数据库名称')
EXEC(@Sql)

脱机DB

alter database [BaiInfoBase] set Offline with ROLLBACK IMMEDIATE

查询进程信息PID等
EXEC sp_who2;
杀掉PID进程
KILL <SPID>;

RESTORE database BaiInfoBase with norecovery

======================================================================

无法删除作业，约束冲突，解决方案：

--打开SQL查询， 
use msdb 
select * from sysmaintplan_plans --查看作业ID
delete from dbo.sysmaintplan_log where plan_id = 'C0A996D6-3A11-4E6C-A814-BB91B0ACBDAF'
delete from sysmaintplan_subplans where plan_id = 'C0A996D6-3A11-4E6C-A814-BB91B0ACBDAF'
DELETE FROM sysmaintplan_plans WHERE [ID]= 'C0A996D6-3A11-4E6C-A814-BB91B0ACBDAF'
--然后再到SQL SERVER 代理-----作业----即可删除

======================================================================

命令导入大量SQL数据：
例: sqlcmd -s localhost -U sa -P bai0323* -d CustomsGrab -i D:\IE_Customs_Record20200316.sql

格式: sqlcmd  -S localhost -U username -P password -d dbname -i db.sql

=======================================================================

SELECT TOP 10
 [session_id],
 [request_id],
  [start_time] AS '开始时间',
  [status] AS '状态',
  [command] AS '命令',
  dest.[text] AS 'sql语句', 
  DB_NAME([database_id]) AS '数据库名',
  [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 [wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [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 
 WHERE [session_id]>50 AND DB_NAME(der.[database_id])='BaiInfoERP'  
 ORDER BY [cpu_time] DESC

=================================================================

--当前的数据库用户连接
 USE master
 GO
--如果要指定数据库就把注释去掉
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 AND DB_NAME([dbid])='BaiInfoERP'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

=================================================================

清理数据库日志

USE [master]  
GO  
ALTER DATABASE InformationSrv SET RECOVERY SIMPLE WITH NO_WAIT  
GO  
ALTER DATABASE InformationSrv SET RECOVERY SIMPLE  
GO  
USE InformationSrv  
GO  
DBCC SHRINKFILE (N'InformationSrv_log' , 0,TRUNCATEONLY)  
GO  
USE [master]  
GO  
ALTER DATABASE InformationSrv SET RECOVERY FULL WITH NO_WAIT  
GO  
ALTER DATABASE InformationSrv SET RECOVERY FULL  
GO

  
--上面的“数据库日志文件名称”用下面SQL的查询结果
--查询指定数据库的日志文件名称  

USE InformationSrv   
GO  
SELECT name FROM SYS.database_files WHERE type_desc='LOG'

==================================================================

USE [master]  
GO  
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE WITH NO_WAIT  
GO  
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE  
GO  
USE [数据库名]  
GO  
DBCC SHRINKFILE (N'[数据库日志文件名称]' , 0,TRUNCATEONLY)  
GO  
USE [master]  
GO  
ALTER DATABASE [数据库名] SET RECOVERY FULL WITH NO_WAIT  
GO  
ALTER DATABASE [数据库名] SET RECOVERY FULL  
GO

  
--上面的“数据库日志文件名称”用下面SQL的查询结果
--查询指定数据库的日志文件名称  
USE [数据库名]   
GO  
SELECT name FROM SYS.database_files WHERE type_desc='LOG'

============================================================

创建查询 , 提示目录名无效

在目录:C:\Documents and Settings\Administrator\Local Settings\Temp 下, 创建目录名称为1, 如果1存在创建目录名称为2.

===============================================================

MSSQL从TXT或CSV源还原数据

1、使用查询语句获取数据，例“SELECT *  FROM [InformationSrv].[dbo].[Tb_PriceList] where PriceDate>='2021-08-01'”，查询结果保存成txt或csv格式文件。
2、还原：DB-右键-导入数据-选择平面文件源（高级-宽度500要选足够）-选择目标db-选择源表和源视图（编辑映射-勾选启用标识插入）-开始执行

===============================================================

查询附加数据库的进度

EXEC sp_who2;  #查看进程相应的PID

SELECT
session_id, request_id, start_time, status, command
, percent_complete, estimated_completion_time,wait_time, cpu_time, total_elapsed_time, scheduler_id
,sql_handle
--,statement_start_offset, statement_end_offset, plan_handle
, database_id, user_id --,connection_id
, blocking_session_id
, wait_type, last_wait_type, wait_resource, open_transaction_count, open_resultset_count, transaction_id
--, context_info
--, task_address
, reads, writes, logical_reads
--, text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_isolation_level, lock_timeout, deadlock_priority, row_count, prev_error, nest_level, granted_query_memory, executing_managed_code, group_id, query_hash, query_plan_hash
FROM sys.dm_exec_requests WHERE session_id=56

（最后的session_id修改成查询到的PID，执行后process_percent、percent_complete值为进度百分比）

======================================================================

--查看被锁表：

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName 
from sys.dm_tran_locks where resource_type='OBJECT'

(注释：spid 锁表进程 ，tableName 被锁表名）

--解锁：

declare @spid int 
Set @spid = 244 --锁表进程spid
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)

--根据spid查询锁表的SQL语句

SELECT DISTINCT
'进程ID' = STR(a.spid, 4)
, '进程ID状态' = CONVERT(CHAR(10), a.status)
, '死锁进程ID' = STR(a.blocked, 2)
, '工作站名称' = CONVERT(CHAR(10), a.hostname)
, '执行命令的用户' = CONVERT(CHAR(10), SUSER_NAME(a.uid))
, '数据库名' = CONVERT(CHAR(10), DB_NAME(a.dbid))
, '应用程序名' = CONVERT(CHAR(10), a.program_name)
, '正在执行的命令' = CONVERT(CHAR(16), a.cmd)
, '登录名' = a.loginame
, '执行语句' = b.text
FROM master..sysprocesses a CROSS APPLY
sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.blocked IN ( SELECT blocked
FROM master..sysprocesses ) and a.spid IN (120,112,424,319,236)

========================================================================

查看最慢的sql：

SELECT TOP 5 total_worker_time, last_worker_time,
max_worker_time, min_worker_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC

==============================================================================

更新扩展nvarchar字段长度为200 #nvarchar(200) ：

use [数据库名]
alter table 表名
alter COLUMN 列名 nvarchar(200) not null

=============================================================================

sql清空表数据的三种方式：

truncate table 表名  --(表内数据都不要了)删除所有数据，保留表结构，不能撤销还原
drop 表名  -–(表格整个不要了)删除表，数据和表结构一起删除
delete 表名  -–(删除某些行)是逐行删除速度极慢，不适合大量数据删除

=========================================================================

当 IDENTITY_INSERT 设置为 OFF 时,不能向表 ‘xxxxxx’ 中的标识列插入显式值

set identity_insert 表名 ON;
insert ........
set identity_insert 表名 OFF;

==========================================================================
日志增长过大原因定位

第一步, 查询DB的LOG日志状态:

DBCC SQLPERF(LOGSPACE)
GO
select name, recovery_model_desc, log_reuse_wait, log_reuse_wait_desc
    from sys.databases
go
--log_resuse_wait_desc中的列显示的参数状态说明:
--NOTHING, 当前有一个或多个可重用的虚拟日志文件
--CHECKPOINT, 自上次日志截断后未出现检查点,或者日志头部尚未跨一个虚拟日志文件移动 范围
--LOG_BACKUP, 要求日志备份将日志标头前移,完成备份后日志标头前移,并且一些日志空间可能会重新可用
--ACTIVE_BACKUP_OR_RESTORE, 数据备份或还原正在进行中
--ACTIVE_TRANSACTION, 事务处于活动状态(有一个长时间运行的事务或事务被延迟卡住回滚受阻)
--DATABASE_MIRRORING, 数据库镜像暂停或者在高性能模式下镜像数据库明显滞后于主体数据库
--REPLICATION, 在事务复制过程中,与发布相关的事务仍未传递到分发数据库
--DATABASE_SNAPSHOT_CREATION, 正在创建数据库快照
--LOG_SCAN, 正在进行日志扫描
--OTHER_TRANSIENT, 此值当前未使用.

--第二步, 检查当前数据库中最久未被提交的活动事务:

dbcc opentran
go

--第三步,查询这个连接是什么程序建立的,以及这个连接最后一条命令内容:

select st.text,t2.*
    from sys.dm_exec_sessions as t2, sys.dm_exec_connections as t1
        cross apply sys.dm_exec_sql_text(t1.most_recent_sql_handle) as st
    where t1.session_id = t2.session_id
         and t1.session_id > 50

--第四步, 结合第二和第三步查询出来的session值,来终止掉进程:

kill [session_id]

===================================================================
--查看当前的数据库用户连接有多少

USE master
GO
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50

--前10个最耗CPU时间的会话

SELECT TOP 10
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[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
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'
ORDER BY [cpu_time] DESC

--查询前10个最耗CPU时间的SQL语句

SELECT TOP 10dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50
ORDER BY [cpu_time] DESC

--查询会话中有多少个事务在等待 

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

--查询CPU值占用高的语句

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC