SqlServer资料收集

发表于：2011-08-23 13:31:46

一、SQL Server访问时常见错误
1.1、SQL Server不存在或访问被拒绝
（1）、能否PING通服务器（检查网络）
基础网络知识：
序号 目标 现象 结果
1 127.0.0.1 能通 说明TCP/IP说明没有问题
2 本机网卡IP 能通 说明TCP/IP已与网卡正确绑定
3 对方IP或网关（同一网段） 能通 说明网线和对方设置都没有问题
4 对方IP或网关（同一网段） 不通 作如下软硬件方面的检查


硬件：
1：网卡问题：插槽坏了，网卡没插好，网卡坏了，网口坏了；  
2：网线问题：水晶头，水晶头与网卡的接触不好（劣质水晶头），网线内部断了，或者没有插网线；
软件：
1：网卡驱动；
2：网卡设置（IP，掩码，网关，DNS）； 
3：错误的设置路由（win2000/win2003）；
4：防火墙的原因（包括XP自带或者第三方的）；
5：如果TCP/IP协议的[卸载]选项是灰色的，说明WinXP 系统的TCP/IP协议已经被卸载。PING 127.0.0.1的时候就会出现错误,此时无法通过重装TCP/IP协议还原系统的TCP/IP，可以利用NETSH命令的重置TCP/IP协议堆栈功能
格式如下 
c:\>netsh 
netsh>interface 
netsh interface>ip 
netsh interface ip>RESET C:\RESETTCPIP.LOG 
说明：
NETSH 在重置TCP/IP堆栈的时候必须指定一个记录文件 
其中C:\RESETTCPIP.LOG 为一个记录文件.用来记录NETSH 在重置过程中的所有操作 
这样重置以后TCP/IP协议就可以回复为正常时候的状态了。
6：如何自动分配IP
用ipconfig /release,ipconfig /renew命令看看能不能获取IP，可运行ipconfig/all查看，如果显示了DHCP Server的地址，而ip地址仍然为0.0.0.0，那么就运行"services.msc",确保[DHCP Client]服务已经启动。
（2）、能否TELNET通服务器（检查SQL Server服务及端口）
运行"services.msc"，确保服务器上TELNET服务启用的情况下，运行telnet 192.168.0.1 1433，1433是SQL Server 2000对于TCP/IP的默认侦听端口。如果有问题，则提示“……无法打开连接,连接失败"。
　　如果这一步有问题，应该检查以下选项：
² 服务器是否启动了SQL Server 2000服务；
² 检查服务器端的网络配置，在服务器上打开：开始>程序>Microsoft SQL Server>服务器网络实用工具，检查是否启用TCP/IP协议，以及TCP/IP协议的端口是否与上面TELNET时的端口一致。同样，如果存在防火墙的话，也要打开该端口。
² 检查服务器是否在侦听SQL Server的TCP/IP端口：在服务器命令行下面输入netstat -a -n 或者是netstat -an，在结果列表里看是否有类似 tcp 127.0.0.1 1433 listening 的项。如果没有，则需要给SQL Server 2000打上至少sp3的补丁。其实在服务器端，可以按照《SQL Server 2000 基础（1）——安装》中的方法查看当前补丁的版本。
如果以上都没问题，再次TELNET测试，将会看到屏幕一闪之后光标在命令行窗口左上角闪动，这表明TELNET测试成功。
但有的时候TELNET通了，上面的检查全正常但还是报这个错，后来我发现用JDBC可以连接上，于是，就猜想可能是ODBC的原因（查询分析器用的是ODBC驱动连到数据库的），就装了个MDAC2.8，然后再去控制面板中用ODBC数据源测试，还是不通，无意间改了一下数据源中的客户端配置，将其中的端口由动态改为固定，就可以连接成功了，至于其中原因还没有分析清楚。
（3）、检查客户端的网络配置
在客户端打开：开始>程序>Microsoft SQL Server>客户端网络使用工具。
检查是否启用TCP/IP协议，以及TCP/IP协议的端口是否与服务器端的一致。
附注：在连接本地服务器时，通常使用的是命名管道协议(在服务器网络实用工具里可以看到启用的协议有这个)，默认端口是445，因此在本地能连通是不能说明什么问题的，连接远程服务器是使用的TCP/IP协议。
1.2、无法连接到服务器,用户xxx登陆失败
错误产生的原因有两种：
（1）密码错误，如果密码中有字母，可检查大小写；
（2）由于SQL Server使用了"仅 Windows"的身份验证方式，因此用户无法使用SQL Server的登录帐户(如sa )进行连接，解决办法如下：
企业管理器>服务器>编辑SQL Server注册属性>使用windows身份验证，以连接上SQL Server；
企业管理器>服务器>属性>安全性，以设置身份验证模式为SQL Server和Windows；
重新启动SQL Server服务，用SQL Server用户重新连接。
以上设置可通过修改注册表来实现，如下：
运行REGEDIT，打开注册表；
打开到
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
将右侧窗口的”LoginMode”项的值从1改为2；
重新启动SQL Server服务，用SQL Server用户重新连接。
以下注册表键:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode的值决定了SQL Server将采取何种身份验证模式.
1.表示使用Windows身份验证模式 ；
2.表示使用混合模式（Windows身份验证和SQL Server身份验证）。
1.3、提示连接超时
遇到这个错误，表示客户端已经找到了这台服务器且可以连接，不过是由于连接的时间大于允许的时间而导致出错。
一般当用户在Internet上用客户端连接服务器时，如果网速慢，有可能会导致以上的超时错误。某些情况下，由于局域网的网络问题，也可能会导致这样的错误。
默认情况下，通过企业管理器注册另外一台SQL Server的超时设置是4秒，而查询分析器是15秒，这也是为什么在企业管理器里发生错误的可能性比较大的原因。
要解决这样的错误，可以修改客户端的连接超时设置，如下：
企业管理器中的设置：企业管理器>工具>选项>高级>连接设置>登录超时；
查询分析器中的设置：查询分析器>工具>选项>连接>登录超时。
1.4、SQL Server 2000无法打开1433端口
如果你是WINXP 或WIN2003,那么一定要安装SQL SERVER SP3及以上的补丁。
注意：SP4补丁执行的时候是解压，要在解压后的目录中执行setup.bat才是真正的安装。
二、禁止别的电脑访问本机的SQL Server服务器
（1）设置用户密码（包括WINDOWS和SQL SERVER），不提供用户及密码给不想让其访问的用户；
（2）防火墙中设置限制，只允许指定IP访问你的SQL Server服务器端口(默认1433)，当然，从更为安全的角度来考虑，应该把1433端口改成其他的端口；
（3）如果使用SQL Server 2005，还可以通过端点限制的方法来实现，此方法要求一块专门的网卡，所有可以连接SQL Server的客户端均通过此网卡接入（假设此网卡的IP是192.168.1.1）：
1． 在“SQL Server 配置管理器”的“SQL Server 2005网络配置中”，禁止除TCP/IP之外的所有协议；
2． 使用如下的T-SQL禁止默认的TCP端点
ALTER ENDPOINT [TSQL Default TCP]
STATE = STOPPED
3． 使用如下的T-SQL建立新的TCP端点和授权
USE master
GO
-- 建立一个新的端点
CREATE ENDPOINT [TSQL User TCP]
STATE = STARTED
AS TCP(
  LISTENER_PORT = 1433,
  LISTENER_IP = (192.168.1.1) -- 侦听的网络地址
)
FOR TSQL()
GO
-- 授予所有登录(或者指定登录)使用此端点的连接权限
GRANT CONNECT ON ENDPOINT::[TSQL User TCP]
TO [public]
完成上述配置之后，只有通过网络地址配置为192.168.1.1的网卡接入的客户端才能访问SQL Server；另外，如果只授予指定登录对端点的连接权限，则只有指定的登录才能接入SQL Server实例。
（4）当SQL Server 2005升级到SP2或者更高的版本的时候，还可以通过服务器级的DDL触发器来实现控制。
执行下面的T-SQL后，将使除IP地址为192.168.1.1之外的客户端连接失败。
USE master
GO
CREATE TRIGGER tr_LoginCheck
ON ALL SERVER
FOR LOGON
AS
IF EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(15)') <> '192.168.1.1'
  ROLLBACK TRAN
GO

  

 

 

 

一、如何断开用户连接
1.1、停掉SQL SERVER服务 
此操作对整个SQL SERVER服务器都生效，有如下几种方法：
（1）SQL Server服务管理器；
（2）控制面板>>管理工具>>服务，或者，运行services.msc；
（3）命令提示行下的net stop mssqlserver /y命令，在SQL SERVER中可这样写：
（4）SQL语句：ShutDown，虽然也是最终停掉SQL SERVER服务，不过这个命令有一些参数可供选择（如：WITH NOWAIT），更加人性化一些，详见SQL SERVER BOL；
或者exec master..xp_cmdshell 'net stop mssqlserver /y'
1.2、设置单用户模式
SINGLE_USER 每次允许一个用户连接到数据库，所有其它用户连接均中断。
中断连接的时间段由 ALTER DATABASE 语句的WITH <termination>子句控制，如果省略了 termination 子句，那么将允许事务自主提交或回滚。
新的连接尝试将被拒绝。即使设置此选项的用户注销，数据库仍保持 SINGLE_USER 模式。这时，其他用户（但只能是一个）可以连接到数据库。
此操作只对当前数据库生效，语句如下：
alter database db_name set single_user  
把用户模式设回数据库默认状态，语句如下：
alter database db_name set multi_user  
错误方法：
脱机，因为当前有连接在线，而脱机是一个要求无连接的操作，
在企业管理器中给数据库脱机，出现如下错误：
错误5070：如果其他用户正在使用数据库“test”，则数据库状态不能更改
ALTE DATABASE语句失败。
sp_dboption命令失败。
在查询分析器中，在master运行alter database语句，出现如下错误：
ALTER DATABASE 语句失败。
有关数据库选项设置的细节，请见《SQL Server 2000 管理——选项设置》series。
1.3、KILL
此操作对整个SQL SERVER服务器都生效，但可以根据查看的SPID，有选择的KILL掉需要断开的连接，注意KILL是不能取消自己的进程的，如果需要自己也断开，退出连接即可。方法如下：
exec sp_who
--或者
exec sp_who2
--或者
select * from master..sysprocesses
--然后
kill 111 --假如要kill的连接SPID为111
1.4、物理方法
此操作对整个SQL SERVER服务器都生效。
比如：拨掉网线，禁用网卡，关掉所在网络的交换机等一切可以断开网络的方法都行。
二、SQL SERVER连接限制
（1）设置SQL SERVER的最大连接数，就是建立的连接数（connection）
企业管理器>服务器>属性>连接，0为默认设置（即不受限，32767）；
或者
exec sp_configure N'user connections',0 —-需要重启服务生效
注意：这里的连接数是指不大于等于，即小于设置的值。
将连接最大数设为１，重启SQL SERVER，连接时提示“一般网络性错误，ConnectionOpen(PreLoginHandshake())，请验证SQL SERVER 是否在运行...”，这样就出现自己也连不上的情况，最大为1，也就是连接数只能是小于1的整数，那就是0个。解决方法如下：
1、停止sql服务
2、用单用户模式启动SQL SERVER
在命令行模式下输入sqlservr –c -f -m或者输入sqlservr -m
sqlservr.exe在SQL SERVER安装目录下，可以搜一下，我的是在如下路径：
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
3、用osql登录重新配置:
osql /U"sa" /P"密码" /Q"exec sp_configure N'user connections', 0"  
4、然后重新启动操作系统。
（2）设置SQL SERVER的最大线程数，连接或者其他SQL SERVER操作（比如select），都是以线程的形式在工作的（多线程）。
有关服务器选项设置的细节，请见《SQL Server 2000 管理——选项设置》series。
（3）假设SQL SERVER服务器只允许最多10个客户端联接，超过１０个则不允许连接，如何控制？
这种情况只出现在两层的时候，三层是通过中间的一个或几个CONN来实现的，不能达到连接限制的，当然可以通过程序以计数的形式来控制，但不是通过SQL SERVER。
（4）查年SQL Server当前连接数
以下各种方法都无法准确地获取到SQL Server的当前ACTIVE连接数，不知道在SQL Server 2005中这样的情况有没有改善。
1、系统变量：
SQL Server提供了一些系统变量返回连接值，个人觉得，很容易产生误解。
@@CONNECTIONS 返回自上次启动SQL Server以来连接或试图连接的次数。
@@MAX_CONNECTIONS 返回SQL Server上允许的同时用户连接的最大数。返回的数不必为当前配置的数值。
2、系统存储过程
SP_WHO /SP_WHO2
提供关于当前SQL Server 用户和进程的信息。可以筛选返回的信息，如下：
列出所有活动的用户：SP_WHO 'active'
列出某个特定用户的信息：SP_WHO 'sa'
3、系统表
sysprocesses
sysprocesses 表中保存关于运行在SQL Server上的进程的信息。这些进程可以是客户端进程或系统进程。连接与进程是一以多的关系，而且这个系统表中的系统是个累计量，而不是最新的统计量。
sysperfinfo
包括一个SQL Server表示法的内部性能计数器，可通过 Windows NT 性能监视器显示。
同样，这里得到的信息也只是所有时间内的累计量。

 

 

 

 

SQL code

一、系统表数据字典的详细信息请查SQL SERVER BOL，这里仅列出一部分。1.1、sysservers1、查看所有本地服务器及链接服务器select * from master..sysservers1.2、sysdatabases1：查询非sa创建的所有数据库select * from master..sysdatabaseswhere sid not in(select sid from master..syslogins where name='sa')--或者select dbid, name AS DB_NAME from master..sysdatabaseswhere sid <> 0x011.3、sysobjects1：获取当前数据库中的所有用户表select name from sysobjects where xtype='U' and status>0为什么要加status>0，因为表dtproperties，虽然该表的xtype为U，实质上它是系统表。dtproperties这个表里保存的是关系图，如果没建关系图，就是空的。注意：这个表只是数据库对象的属性，如果想要看表、索引详细的属性，查看sysindexes。2：查看当前数据库中所有存储过程select name as 存储过程名称 from sysobjects where xtype='P' and status>0为什么要加status>0，是为了去掉当前数据库中的系统存储过程。注：该系统表中type与xtype的区别是什么？Type是在SQL SERVER 6.0就有的，xType在SQL SERVER 7.0才出现，Type的保留只是为了向后兼容。每种数据库对象的类型详见SQL SERVER BOL。1.4、syscolumns1：获取表或视图的所有字段，存储过程或函数的所有参数select name from syscolumns where id=object_id('表名')1.5、sysproperties1：怎么把SQL SERVER中表设计和表注释读出来--表的注释全在sysproperties里select b.name,value from sysproperties as a,sysobjects as bwhere a.id=b.id and b.name='表名'1.6、sysindexes1：根据聚集索引，快速查询表的行数SELECT rowcnt,indid FROM sysindexes WHERE id=OBJECT_ID('tableName')and indid < 2注意：使用这种方法可能不精确，因为系统的统计信息在某些时候不一定是准确的。关于统计的维护(dbcc updateusage())，详见《SQL SERVER 性能优化——查询优化》series。2：查看索引表信息selecttable_Name=sysobjects.Name,    index_Name=sysindexes.Name,    Type=sysobjects.type,    分配索引页=sysindexes.reserved,    使用索引页=sysindexes.used,    叶子层页=sysindexes.Dpages,    非叶子层页=sysindexes.used-sysindexes.Dpages,    rows=sysindexes.rowcntfrom sysindexes left outer join sysobjects on sysindexes.id=sysobjects.idwhere sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0注意：若发现非叶子层的页数为负数,最好是运行DBCC UPDATEUSAGE ('dbname','tbname','ixname')来更新一下sysindexes的信息1.7、sysloginsSQL SERVER 服务器的登录信息，比如：sa，有关登录、用户、角色的信息详见《SQL SERVER 2000 管理——安全——用户权限》。select * from sysloginsselect * from sysxloginssysxlogins是syslogins的精简版，BOL中没有说明，不推荐使用。 1.8、sysprocesses1：查看用户进程信息select spid,uid,syslogins.name,login_time,net_address from sysprocesses,syslogins where sysprocesses.sid=syslogins.sid2：查看数据库启动时间select convert(varchar(30),login_time,120) from master..sysprocesses where spid=11.9、sysdepends1：查看与某一个表相关的视图、存储过程、函数select * from sysdepends where depid=object_id('表名')--或者select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'注意：这种查法，只适用在没有with Encryption选项，即没有加密该对象时。--或者sp_depends注意：这个表的统计信息并不准确，没有什么好的办法，查询结果只可用于参考。1.10、sysmessagesSQL SERVER返回的内部错误都有在这里，可自行定义进行错误的添加，但一般我习惯于新建一个错误的表来定义自己程序中的错误。select * From master..sysmessages where error=50371.11、sysfiles、sysfilegroups1、查询当前数据库的文件使用情况select name,filename,size/128 as 'used(M)',case maxsize/128 when 0 then 'no limit' else cast(maxsize/128 as varchar(10)) end as 'total(M)' from sysfiles2、查询当前数据库的表所在文件组select distinct a.id,a.name,b.groupid,c.groupname from sysobjects a inner join sysindexes b on a.id=b.idinner join sysfilegroups c on b.groupid=c.groupidwhere a.xType='U' and a.status>0 order by a.namesysfiles1是sysfiles的精简版，BOL中没有说明，不推荐使用。二、系统视图在master数据库中有INFORMATION_SCHEMA和system_function_schema两个用户，它们的登录是<无>，这是系统内置的两个用户。INFORMATION_SCHEMA拥有自已的视图，在SQL Server 2000中没有被广泛使用，因为很多时候都可以从系统表中得到我们想要的结果，同样到了SQL Server 2005中，被广泛使用的仍然是sys所拥有的视图，SQL Server 2005的相关内容详见后续《SQL Server 2005》series文章。举例如下：1、查询某个表的哪些字段不允许为空select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNSwhere IS_NULLABLE='NO' and TABLE_NAME='stb_User'2、查询某个表的键约束select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGEwhere TABLE_NAME='stb_User'注：键约束，是指除了CHECK、NOT NULL外的约束，即PK，FK，UNIQUE，DEFAULT不是约束。system_function_schema拥有自己的函数，在SQL Server 2000中没有被广泛使用，详见《SQL Server 2000 基础——系统函数》。

 

 

 

 

 

 

转载出处： http://topic.csdn.net/u/20110823/13/23018853-5cdf-4165-add3-f6fea845cf3e.html