sqlserver管理及维护.docx
《sqlserver管理及维护.docx》由会员分享，可在线阅读，更多相关《sqlserver管理及维护.docx（10页珍藏版）》请在冰豆网上搜索。
sqlserver管理及维护
1引言
1.1编写目的
便于对数据库进展管理、维护
1.3背景
以下6个需求：
1.数据库访问IP控制
2.数据库连接情况监控语句
3.找出SQL执行耗时操作语句
4.数据库整体资源负荷语句
5.数据库中杀死访问进程语句
6.数据库存储空间情况语句
2sqlserver管理与维护
2.1需求的优化
i.数据库中杀死访问进程语句在数据库连接情况监控语句可以实现，两个合并成一个。
ii.数据库整体资源负荷语句，分别在数据库连接情况监控语句和数据库存储空间情况语句中实现,分别与另外两个合并
iii.找出SQL执行耗时操作语句，语意不清晰，建议改成：
查看sql语句的执行时间。
2.2数据库访问IP控制
2.2.1创立登录触发器，限制IP地址登录
步骤1.创立IP过滤表〔仅允许表的IP登录〕
USEcyt
GO
CREATETABLEValidIP（
IPNVARCHAR（15）,
）;
GO
步骤2.插入过滤IP。
例：
USEcyt
GO
INSERTINTOdbo.ValidIP（IP）VALUES（'192.168.1.94'）;
步骤3.创立登录触发器、
CREATETRIGGER[tr_logon_CheckIP]
ONALLSERVER
FORLOGON
AS
BEGIN
DECLAREIPNVARCHAR（15）;
SETIP=（SELECTEVENTDATA（）.value（'（/EVENT_INSTANCE/ClientHost）[1]','NVARCHAR（15）'））;
IFNOTEXISTS（SELECTIPFROM[cyt].[dbo].[ValidIP]WHEREIP=IP）
ROLLBACK;
END;
步骤4.其他限制：
一〕如果需要限制登录用户名使用如下的判断：
ORIGINAL_LOGIN（）='sa'
例：
CREATETRIGGER[tr_connection_limit]
ONALLSERVER
FORLOGON
AS
BEGIN
--限制test这个XX的连接
IFORIGINAL_LOGIN（）='test'
ROLLBACK;
END;
二〕如果需要限制数据库角色使用如下的判断：
IS_SRVROLEMEMBER（'sysadmin'）=1
例：
CREATETRIGGER[tr_logon_CheckIP]
ONALLSERVER
FORLOGON
AS
BEGIN
--限制数据库角色为sysadmin的用户登录
IFIS_SRVROLEMEMBER（'sysadmin'）=1
BEGIN
ROLLBACK;
END;
END;
2.2.2删除登录触发器
场景1.如果数据库依然能连接连接的话使用如下的语句对登录触发器进展删除：
droptrigger登录触发器名onallserver
本例中对应的删除语句：
droptriggertr_logon_CheckIPonallserver
场景2.如果数据库连接不上，在命令行通过使用DAC专用管理员连接，方法如下：
一、使用DAC专用管理员连接，在命令行输入如下命令：
sqlcmd–A–S效劳器名称
查看sqlserver效劳器名称使用如下的命令：
selectServername
本地效劳器的效劳器名称是poem-PC对应的命令如下：
sqlcmd-A-Spoem-PC
二、连接成功后输入上面删除登录触发器的语句：
droptriggertr_logon_CheckIPonallserver
go
DAC连接成功的界面如下：
DAC删除登录触发器删除成功的界面如下：
2.3数据库连接情况监控语句
开场——所有程序——Microsoftsqlserver2012——SQLServerManagementStudio
截图如下：
连接到对象资源管理器，截图如下：
点击后，连接前：
连接后：
点击后出现如下的界面：
2.4查看sql语句的执行时间
SETSTATISTICSPROFILEON
SETSTATISTICSIOON
SETSTATISTICSTIMEON
GO
/*--你的SQL脚本开场*/
SELECT*FROMtb_account〔例〕
GO
/*--你的SQL脚本完毕*/
SETSTATISTICSPROFILEOFF
SETSTATISTICSIOOFF
SETSTATISTICSTIMEOFF
2.5数据库存储空间情况语句
1.查看整个数据库存储空间的使用情况：
输入如下的命令：
sp_spaceused
返回值说明：
Ødatabase_name:
当前数据库的名称
Ødatabase_size:
当前数据库的大小
Øunallocated:
数据库的未分配空间
2.查看指定数据库表的存储空间使用情况：
输入如下的命令：
sp_spaceused表名
例：
sp_spaceusedtb_info
返回值说明：
Øname:
为其请求空间使用信息的表名
Ørows:
所请求的数据库表中现有的行数
Øreserved：
为表保存的空间总量
Ødata:
表中的数据所使用的空间量
Øindex_size:
表中的数据所使用的空间量
Øunused:
表中未用的空间量
升级会员 