Page tree
Skip to end of metadata
Go to start of metadata

查看数据库版本

SELECT @@VERSION ;



查看数据库里所有的表/视图

SELECT name from DRMS_Logging_rms_sunac_local_443.sys.tables ; 
select * from sys.tables ;
select * from sys.views ;


查询所有数据库

SELECT * FROM Master..SysDatabases ORDER BY dbid ;


查询数据库对应的操作系统文件

SELECT * FROM dbname.dbo.sysfiles ;


查看数据库大小

EXEC sp_spaceused 
@updateusage = 'TRUE' ;



查看数据库备份记录

SELECT database_name, type, MAX(backup_finish_date) AS backup_finish_date
  FROM msdb.dbo.backupset
 GROUP BY database_name, type 


查看数据库当前时间

select GETDATE()



查看数据库当前运行的session及SQL

SELECT   s.session_id, 
            r.status, 
            r.blocking_session_id                                 'Blk by', 
            r.wait_type, 
            wait_resource, 
            r.wait_time / (1000.0)                             'Wait Sec', 
            r.cpu_time, 
            r.logical_reads, 
            r.reads, 
            r.writes, 
            r.total_elapsed_time / (1000.0)                    'Elaps Sec', 
            Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                    ((CASE r.statement_end_offset 
                        WHEN -1 
                        THEN Datalength(st.TEXT) 
                        ELSE r.statement_end_offset 
                        END - r.statement_start_offset) / 2) + 1) AS statement_text, 
            Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
                    '') AS command_text, 
            r.command, 
            s.login_name, 
            s.host_name, 
            s.program_name, 
            s.last_request_end_time, 
            s.login_time, 
            r.open_transaction_count 
FROM     sys.dm_exec_sessions AS s 
              JOIN sys.dm_exec_requests AS r 
            ON r.session_id = s.session_id 
            CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
--WHERE    r.session_id != @@SPID 
ORDER BY r.cpu_time desc, r.status, 
            r.blocking_session_id, 
            s.session_id 



耗时最长的SQL

SELECT st.text, qp.query_plan, qs.*
  FROM (SELECT TOP 50 *
          FROM sys.dm_exec_query_stats
         ORDER BY total_worker_time DESC) AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
 WHERE qs.max_worker_time > 300
    OR qs.max_elapsed_time > 300


TOP CPU SQL

SELECT TOP 50


[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]


)='BPMProduct'
ORDER BY


[cpu_time]


DESC


set max server memory

SP_CONFIGURE 'show advanced options',1 ;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE ;
GO
SP_CONFIGURE 'show advanced options',0 ;
GO
RECONFIGURE;
GO

最小模式启动SQLServer

以最小模式启动SQLServer实例,修改内存限制
考虑到有多个应用从不同的服务器连接本SQLServer实例,首先做一些外围处理(禁止应用程序连接本实例),避免它们干扰修复过程
– 关闭本地服务器(连接该实例)的应用程序,
– 对于跨服务器访问的,在防火墙中关闭SQLServer端口


--1.开启一个cmd窗口 窗口1,-f最小模式启动实例
cd /D C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe

sqlservr.exe -f -sMSSQLSERVER
MSSQLSERVER 默认实例名,可修改为你实际实例名修改

--2.cmd窗口2 (窗口1运行后)立即运行下面命令进入 命令行模式
sqlcmd -E -sMSSQLSERVER -S localhost,11433
--修复,就本故障来说,修改内存最大值为不限制,
EXEC sp_configure 'show advanced options', '1' RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory', 2147483647 RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'show advanced options', '0' RECONFIGURE WITH OVERRIDE;
GO

查看备份及恢复进度

SELECT session_id as SPID, command, a.text AS Query,
start_time, percent_complete,
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

SQLCMD语法

C:\Users\T1_Chaofeng.li>sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 11.0.2100.60 NT x64
Copyright (c) 2012 Microsoft. All rights reserved.


usage: Sqlcmd [-U login id] [-P password]




[-S


server


] [-H hostname] [-E


trusted connection Windows认证]


[-N Encrypt Connection][-C Trust Server Certificate]




[-d use database name] [-l login timeout] [-t query timeout]




[-h headers] [-s colseparator] [-w screen width]




[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]




[-c cmdend] [-L[c] list servers[clean output]]




[-q "cmdline query"] [-Q


"cmdline query" and exit]


[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]




[-u unicode output] [-r[0|1] msgs to stderr]




[-i inputfile] [-o outputfile] [-z new password]




[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]




[-k[1|2] remove[replace] control characters]




[-y variable length type display width]




[-Y fixed length type display width]




[-p[1] print statistics[colon format]]




[-R use client regional setting]




[-K application intent]




[-M multisubnet failover]




[-b


On error batch abort]


[-v var = "value"...] [-A dedicated admin connection]




[-X[1] disable commands, startup script, environment variables [and exit]]




[-x disable variable substitution]




[-? show syntax summary]






  • No labels