常用SQL功能语句 |
|
本文标签：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 =ND:\TDB.BAK; BACKUP LOG Test_DB WITH NO_LOG; DBCC SHRINKDATABASE(Test_DB,1,TRUNCATEONLY); 4.还原数据库 RESTORE DATABASE DB_Name FROM DISK = ND:\DBN.BAK WITH RECOVERY , MOVE DB_Name_Data TO ND:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_Name.mdf , MOVE DB_Name_Log TO ND:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_Name_log.ldf ; GO RESTORE LOG DB_Name FROM DISK = ND:\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 = NConnectTOXX; 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) |
