当前位置：首页 > 数据库 > MSSQL教程 > MS-SQL 常用命令 > 正文

MS-SQL 常用命令

来源：这里教程网 时间：2026-03-02 10:59:30 作者：
1. 将DB立即置于OFFLINE状态
ALTER DATABASE bba_pld03_20150304 SET OFFLINE with ROLLBACK IMMEDIATE;

2.如果碰到错误如“ALTER DATABASE failed because a lock could not be placed on database",则可以先运行SP_WHO，找出当前哪些SPID在需要OFFLINE的DB上。
KILL <SPID>

3.查看MS-SQL启动时间（2008 专用）
select sqlserver_start_time,* from sys.dm_os_sys_info 

4.查看MS-SQL的引擎版本号
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),SERVERPROPERTY ('edition')
select @@version


5.查看ERRORLOG的位置
USE master
GO
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
GO


6.查找SQL备份的文件位置
use MSDB
go

SELECT
bs.[database_name]
,bs.[backup_start_date]
,bs.[type]
,bs.[server_name]
,bmf.physical_device_name

FROM [msdb].[dbo].[backupset] bs inner join [msdb].[dbo].[backupmediafamily] bmf
on bs.media_set_id = bmf.media_set_id
where
bs.[database_name] = 'ConsolidatedReport'
and type = 'D'
order by backup_start_date desc

7.找出LOGIN的权限（配合使用MS-SQL提供的工具sp_help_revlogin  https://support.microsoft.com/en-us/kb/918992)
/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON

SELECT  'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context'

-- Role Members
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
        + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
        + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM    sys.server_principals AS usr1
        INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
        INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC

-- Permissions
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS
        + ']' AS '--Server Level Permissions'
FROM    sys.server_permissions AS server_permissions WITH ( NOLOCK )
        INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE   server_principals.type IN ( 'S', 'U', 'G' )
ORDER BY server_principals.name,
        server_permissions.state_desc,
        server_permissions.permission_name  


8. 找出当前Instance下suspend状态的spid的相信信息
SELECT  wt.session_id,
    ot.task_state,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.blocking_session_id,
    wt.resource_description,
    es.[host_name],
    es.[program_name]
FROM  sys.dm_os_waiting_tasks  wt  
INNER  JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address
INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id
WHERE es.is_user_process =  1 


9.找出当前逻辑读最高的查询
SELECT
TOP (25)
P.name AS [SP Name],
Deps.total_logical_reads AS [TotalLogicalReads],
deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads],
deps.execution_count,
ISNULL (deps.execution_count / DATEDIFF (Second, deps.cached_time, GETDATE
()), 0) AS [Calls/Second],
deps.total_elapsed_time,
deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time],
deps.cached_time
FROM
sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS deps ON p. [Object_id] = deps.
[Object_id]
WHERE deps. Database_id = DB_ID ()
ORDER BY deps.total_logical_reads DESC;

10.查询当前数据缓存中每个数据库的缓存大小
SELECT
COUNT(*) * 8 / 1024 AS 'Cached Size (MB)' ,
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS 'Database'
FROM
sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) , database_id
ORDER BY 'Cached Size (MB)' DESC










编辑推荐：

相关推荐

最新软件资讯

关于对sql server读写权限的控制
关于对sql server读写权限的控制

热文推荐

天极热推