常用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 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’)