1.調(diào)整內(nèi)存 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 （企業(yè)版）才能支持4G以上內(nèi)存！ 2.關(guān)閉啟用xp_cmdshell sp_configure 'show advanced options', 1 GO -- 重新配置 RECONFIGURE GO -- 啟用xp_cmdshell sp_configure 'xp_cmdshell', 1 GO --重新配置 RECONFIGURE GO 3.收縮數(shù)據(jù)庫 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.還原數(shù)據(jù)庫 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.創(chuàng)建賦權(quán)超級帳戶 xp_cmdshell 'net user username passwd /ADD'; xp_cmdshell 'net localgroup administrators username /ADD'; 6.啟動停止服務(wù) xp_cmdshell 'net start servicename'; xp_cmdshell 'net stop servicename'; ** 7. 設(shè)置解除獨(dú)占模式 ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO --恢復(fù)多人模式 ALTER DATABASE DB_Name SET MULTI_USER; GO 8. 鏈接服務(wù)器 declare @LS_Name nvarchar(20), @DB_Src nvarchar(40); set @LS_Name = N'ConnectTOXX'; set @DB_Src = '192.168.1.100,1433'; --建鏈接服務(wù)器 EXEC sp_addlinkedserver @LS_Name,'MS','SQLOLEDB',@DB_Src --建關(guān)聯(lián)登入者 EXEC sp_addlinkedsrvlogin @LS_Name,'false',NULL,'user','password' --刪除關(guān)聯(lián)登入者 EXEC sp_droplinkedsrvlogin @LS_Name,NULL --刪除鏈接服務(wù)器 EXEC sp_dropserver @LS_Name GO 9. 查詢數(shù)據(jù)庫聯(lián)接數(shù) SELECT COUNT(*) FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN (SELECT [DBID] FROM [Master].[dbo].[SYSDATABASES] WHERE NAME='database')
