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 即
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')
