常用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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDB_Name.mdf"
, MOVE "DB_Name_Log" TO N"D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataDB_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"）