查看数据库版本

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 mdbs.database_name,
       mdbs.type,
       mdbs.backup_finish_date ,
       mdbf.physical_name,
	   mdbmf.physical_device_name
FROM msdb.dbo.backupset mdbs ,
     msdb.dbo.backupfile mdbf ,
	 msdb.dbo.backupmediafamily mdbmf
WHERE mdbs.backup_set_id = mdbf.backup_set_id and
      mdbmf.media_set_id = mdbs.media_set_id and
	  mdbs.database_name='CN1_SUNDATA01_P'
 order by 3 desc


查看数据库当前时间

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


查看所有用户

select 
  ssp.name,ssp.type_desc,ssp.is_disabled,ssl.is_expiration_checked,ssl.is_policy_checked 
from 
  sys.server_principals ssp 
left join  
  sys.sql_logins ssl
on 
  ssp.principal_id = ssl.principal_id 
order by 2,3;



最小模式启动SQLServer

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


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

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

--2.cmd窗口2 (窗口1运行后)立即运行下面命令进入 命令行模式 
CMD> 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')

TOP SIZE TABLE

select top 10 schema_name(tab.schema_id) + '.' + tab.name as [table], 
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables tab
join sys.indexes ind 
     on tab.object_id = ind.object_id
join sys.partitions part 
     on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
     on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc;
  • No labels