SQLServer常用操作系列二---------库属性及系统数

一、数据库属性相关

select SERVERPROPERTY('edition');
select DATABASEPROPERTY;

--查数据库ID
select database_id,NAME from sys.databases where name='adventureworks2012';
select db_id('adventureworks2012')

checkpoint;


--查看哪些数据库处于简单恢复模式:
select name from sys.databases where recovery_model_desc='simple';
select DATABASEPROPERTYEX('msdb','recovery');
select name,recovery_model,recovery_model_desc from sys.databases;

--查询当前连接使用的协议
select net_transport from sys.dm_exec_connections where session_id=@@SPID;
select * from sys.dm_exec_sessions where is_user_process=1 and writes>0;

--查看数据库空间
use AdventureWorks2012
go
dbcc showfilestats
go
dbcc showcontig
go
dbcc showcontig ('dbo.awbuildversion')
sp_spaceused
go

--查看数据库文件信息
select * from sys.database_files;
--查看数据库元数据
sp_help
--每个本地卷的可用磁盘空间大小：
xp_fixeddrives;

--手动收缩
dbcc shrinkdatabase(adventureworksDW2012,25);
select * from sys.change_tracking_databases;

--查看db_id
sp_helpdb 
go

--查看系统数据库重要的参数列
select name,database_id,SUSER_SNAME(owner_sid) as owner,create_date,user_access_desc,state_desc
from sys.databases
where database_id <=4;

--查看数据库文件使用情况：
select * from sys.dm_db_file_space_usage;

--分离数据库
exec sp_detach_db <dbname>;
--附加数据库
create database dbname 
    on <filespec>
  for {attach | attach_rebuild_log }

--查看数据库文件
select name,physical_name as currentLocation,state_desc
from sys.master_files
where database_id=DB_ID('AdventureWorks2012');

--查看当前数据库兼容级别：
select compatibility_level from sys.databases
where name='dbname';
--改变兼容级别
alter database <dbname> set compatibility_level =<compatiblity-level>;

--查找最早的、打开的事务和没有处理的事务
dbcc opentran;
--清除重复事务：
sp_repldone

--查看虚拟日志
dbcc loginfo[(dbname)]


--切换正在还原的库为正常状态：
restore database accounting with recovery

--查看数据库最大连接数：
 select @@connections
 
查看当前连接数：
SELECT * FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN (SELECT [DBID]FROM [Master].[dbo].[SYSDATABASES] WHERE NAME='databaseName')
其中，'databaseName'填写真实的数据库名称

--查看连接情况
select session_id as spid,connect_time,last_read,last_write,
most_recent_sql_handle from sys.dm_exec_connections
where session_id in (52,53);

--sys.configurations中查询服务器配置选项信息：
select * from sys.cdonfigurations

--sp_configure查询服务器配置选项信息：
sp_configure

--使用sp_configure修改服务器配置选项：
use master;
go
exec sp_configure 'show advanced option','1';
go
exec sp_configure 'recovery interval','30';
reconfigure with override;

tempDB

#sys.dm_db_task_space_usage了解每个任务消耗的tempdb空间

--查看tempdb的空间使用情况
select session_id,DB_NAME(database_id) [数据库名], user_objects_alloc_page_count, internal_objects_alloc_page_count
from sys.dm_db_session_space_usage where session_id>50;

--query outputs the five executing tasks that make the most use of tempdb;
select top 5 * from sys.dm_db_session_space_usage
order by (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC

use tempdb
go
select * from sys.dm_db_file_space_usage
go


#查询占用tempdb最大的5项任务
select top 5 * from sys.dm_db_session_space_usage
order by (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC

#定期查询tempdb使用情况：
select sum(user_object_reserved_page_count)*8 as user_objects_kb,
    sum(internal_object_reserved_page_count)*8 as internal_objects_kb,
    sum(version_store_reserved_page_count)*8 as version_store_kb,
    sum(unallocated_extent_page_count)*8 as freespace_kb
from sys.dm_db_file_space_usage
where database_id=2

MSDB使用例子

#### 删除历史记录
1. 删除旧的历史记录
　　每次执行备份或还原操作后会向备份和还原历史记录表添加额外的行。因此，如果实例中的数据库备份非常频繁，建议定期执行 sp_delete_backuphistory，通过删除早于指定日期的备份集条目，减小备份和还原历史记录表的大小。

 USE msdb;
 GO
 EXECsp_delete_backuphistory @oldest_date = '2013-06-30';

2. 删除特定数据库的所有历史记录
　　sp_delete_database_backuphistory 将从备份和还原历史记录表中删除有关指定数据库的所有历史记录。

 USE msdb;
 GO
 EXECsp_delete_database_backuphistory @database_name = 'db01';

exec sp_purge_jobhistory @oldest_date='2016-07-05'
exec sp_delete_backuphistory @oldest_date='2016-07-05'
exec sp_maintplan_delete_log null,null,'2016-07-05'

四、注意事项
1. 使用存储过程 sp_delete_backuphistory 和 sp_delete_database_backuphistory  只是删除了msdb数据库中的某些记录。即使所有历史记录都已被删除，物理备份介质也会保留下来。

2. 在将一个数据库恢复到另一个实例时，msdb中的备份历史不会复制过去。

验证备份语句
RESTORE VERIFYONLY FROM  DISK = N'C:\Backup\db01.bak'

可以使用 DBCC OPENTRAN 来检查在某一特定时间数据库中是否有一个活动的事务。