Common SQL Function Statement _mssql

1. Adjust Memory
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 need to open awe that boot.ini add/3g or/PAE (Enterprise Edition) to support more than 4G memory!
2. Turn off enable xp_cmdshell
sp_configure ' show advanced options ', 1
Go
--Reconfigure
Reconfigure
Go
--Enable xp_cmdshell
sp_configure ' xp_cmdshell ', 1
Go
--Reconfigure
Reconfigure
Go
3. Shrinking the database
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 the database
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. Create a weighting Super account
xp_cmdshell ' net user username Passwd/add ';
xp_cmdshell ' net localgroup Administrators Username/add ';
6. Start stop service
xp_cmdshell ' net start servicename ';
xp_cmdshell ' net stop servicename ';
**
7. Set unlock Exclusive mode
ALTER DATABASE db_name
SET Single_user
With ROLLBACK IMMEDIATE;
Go
--Restoring multiple-person mode
ALTER DATABASE db_name
SET Multi_user;
Go
8. Linked server
Declare
@LS_Name nvarchar (20),
@DB_Src nvarchar (40);
Set @LS_Name = N ' Connecttoxx ';
Set @DB_Src = ' 192.168.1.100,1433 ';
--Build a linked server
EXEC sp_addlinkedserver @LS_Name, ' MS ', ' SQLOLEDB ', @DB_Src
--Building Associated login
EXEC sp_addlinkedsrvlogin @LS_Name, ' false ', NULL, ' user ', ' password '
--Delete the associated login
EXEC sp_droplinkedsrvlogin @LS_Name, NULL
--Removing linked servers
EXEC sp_dropserver @LS_Name
Go
9. Search data by library contact numbers
SELECT COUNT (*) from [Master]. [dbo]. [sysprocesses] WHERE [DBID] In (SELECT [DBID] from [master].[ DBO]. [sysdatabases] WHERE name= ' database '