常用SQL功能语句

1.调整内存
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'awe enabled', 1
go
reconfigure
go
sp_configure 'min server memory',1024
go
sp_configure 'max server memory',3072
go
reconfigure
go
ps: os需要打开awe 即在boot.ini里 添加 /3g 或者 /pae （企业版）才能支持4g以上内存！
2.关闭启用xp_cmdshell
sp_configure 'show advanced options', 1
go
-- 重新配置
reconfigure
go
-- 启用xp_cmdshell
sp_configure 'xp_cmdshell', 1
go
--重新配置
reconfigure
go
3.收缩数据库
use test_db
backup database test_dbto disk =n'd:\tdb.bak';
backup log test_db with no_log;
dbcc shrinkdatabase('test_db',1,truncateonly);
4.还原数据库
restore database db_name
from disk = n'd:\dbn.bak'
with recovery
, move 'db_name_data' to n'd:\program files\microsoft sql server\mssql.1\mssql\data\db_name.mdf'
, move 'db_name_log' to n'd:\program files\microsoft sql server\mssql.1\mssql\data\db_name_log.ldf' ;
go
restore log db_name
from disk = n'd:\dbn.bak'
with file=2, recovery;
go
5.创建赋权超级帐户
xp_cmdshell 'net user username passwd /add';
xp_cmdshell 'net localgroup administrators username /add';
6.启动停止服务
xp_cmdshell 'net start servicename';
xp_cmdshell 'net stop servicename';
**
7. 设置解除独占模式
alter database db_name
set single_user
with rollback immediate;
go
--恢复多人模式
alter database db_name
set multi_user;
go
8. 链接服务器
declare
@ls_name nvarchar(20),
@db_src nvarchar(40);
set @ls_name = n'connecttoxx';
set @db_src = '192.168.1.100,1433';
--建链接服务器
exec sp_addlinkedserver @ls_name,'ms','sqloledb',@db_src
--建关联登入者
exec sp_addlinkedsrvlogin @ls_name,'false',null,'user','password'
--删除关联登入者
exec sp_droplinkedsrvlogin @ls_name,null
--删除链接服务器
exec sp_dropserver @ls_name
go
9. 查詢數據庫聯接數
select count(*) from [master].[dbo].[sysprocesses] where [dbid] in (select [dbid] from [master].[dbo].[sysdatabases] where name='database')