kobewang

这次升级真是太折腾了。大大影响了平台的正常使用，把自己也搞的疲惫不堪。
sql2000升级2005有几种服务。
一种是在2000的基础上默认升级实例。另一种 是通过备份还原的形式。
我用的是备份还原。刚开始升级后，运行速度非常快，网站访问也很 顺畅。
但是过会之后，就会越来越卡，越来越慢，严重的超时现象。也不占cpu，就是
很卡，到最后不得已重启服务器。重启后又恢复正常。但是之后有开始了。
查阅了很多资料，最后 还是很可能在一个问题上 索引的重聚和索引碎片的整理。
我用了DBCC INDEXDEFRAG(‘dbname’,'tablename’),这样一个个执行表，很费力，
最后看了文章有用sp_updatestats 更新所有的。更新之后 至今还没有发现之前的
那种卡。说明数据库升级总算成功了。总算是平台正常了。
2005性能 还是有很大的提升的。其实主要还是用他的rownumber来提高分页查询的速度
下面用个自己的分页存储过程：

ALTER  PROCEDURE [dbo].[P_PAGE] (
@CurrentPage int,
@PageSize int,
@TableName varchar(1000),
@KeyField varchar(1000),
@Fields varchar(1000),
@Condition1 varchar(2000),
@Condition2 varchar(1000),
@PageCount int output,
@RecordCount int output,
@sql nvarchar(4000) output
)AS
declare @num numeric(8,2),@pz numeric(8,2),@rpz int
set @sql = 'select @RecordCount=count(id) from '+@TableName+' '+@Condition1
exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
set @pz = @PageSize
set @num = @RecordCount / @pz
set @rpz = @num
if @num > @rpz set @rpz = @rpz + 1
select @PageCount = @rpz
if @CurrentPage < 1
begin
	set @CurrentPage = 1
end
if @CurrentPage > @PageCount and @PageCount >0
begin
	set @CurrentPage = @PageCount
end
print @CurrentPage
if(@CurrentPage=1)
begin
	set @sql = 'select top '+convert(varchar,@PageSize)+' '+@Fields+' from '+@TableName+' '+@Condition1+' '+@Condition2
end
else
begin
set @sql='SELECT '+@Fields+' FROM (SELECT '+@Fields+', ROW_NUMBER() OVER(ORDER BY id asc) AS rownum FROM '+@TableName+' ) as Tbl
  WHERE rownum BETWEEN '+convert(varchar,(@CurrentPage-1)*@PageSize+1)+' AND '+convert(varchar,@CurrentPage*@PageSize)
end
exec sp_executesql @sql

另附2005 索引重聚proc
 
GO
/****** 对象:  StoredProcedure [dbo].[db2005_indexes]    脚本日期: 01/05/2012 09:32:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[db2005_indexes]
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)
 
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT '不能运行系统数据库'
RETURN
END
 
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 10 AND si.rows > 0 
 
CREATE TABLE #templist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
 
OPEN tables
 
FETCH NEXT
FROM tables
INTO @tableidchar
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
INSERT INTO #templist
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END
 
CLOSE tables
DEALLOCATE tables
 
SELECT * FROM #templist w--here indexname like '%destine%'

DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #templist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
 
SELECT '开始重新生成或者重组索引 ' + CONVERT(VARCHAR,GETDATE())
 
OPEN indexes
 
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
 
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON
 
if(@frag>30)
begin
SELECT @execstr = 'DBCC DBREINDEX (' + '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT '正在执行: '
--SELECT(@execstr)
print @execstr
EXEC (@execstr)
 
end
else if (@frag<30)
begin
SELECT @execstr = 'DBCC INDEXDEFRAG ('+db_name()+','+ '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) +  '''' +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT '正在执行: '
--SELECT(@execstr)
print @execstr
EXEC (@execstr)
end 
 
SET QUOTED_IDENTIFIER OFF
 
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END
 
CLOSE indexes
DEALLOCATE indexes
 
SELECT '完成结束索引' + CONVERT(VARCHAR,GETDATE())
 
DROP TABLE #templist

(1060) 指定的服务未安装 错误
总结2条 一个是计算机名要大写 二个是 master.mdb log 要删除 或者 安装程序的整个文件夹删干净

1.在SQL Server企业管理器中,右击要收缩的数据库,在弹出的菜单中选择”所有任务”子菜单下的”收缩数据库”项。

2.在”收缩后文件中的最大可用空间”中输入收缩后数据库中剩余的可用空间量。选择“在收缩前将页移到文件起始位置”，使释放的文件空间保留在数据库文件中，并使包含数据的页移到数据库文件的起始位置。

3.点击“调度”命令创建或更改自动收缩数据库的频率和时间。点击更改按钮对调度进行具体的设置。在弹出的窗口中选择调度发生的时机。选择“反复出现”，点击右侧的更改按钮，设定调度发生的频率和每日发生的频率以及持续时间。

4.在步骤2中，还可以选择对单个的文件进行收缩。方法是点击“文件”，在“数据库文件”列表中选择一个要收缩的文件，在“收缩操作”中选择一种收缩文件的方式点击确定按钮。

7.除了手工设置数据库的收缩外，还可以设置让数据库自动收缩。双击要收缩的数据库图标，在数据对话框的“选项”页面中选中“自动收缩”选项，点击“确定”按钮。

8.通过本例我们学习了如何收缩数据库以及如何对数据库收缩进行调度设置。HOHO

9.通过语句我们也可以来收缩数据库，AIS20071219151707是数据库名，在查询分析器中执行。

DBCC SHRINKDATABASE(AIS20071219151707)

backup log AIS20071219151707 with no_log

注:

SQL Server 2000允许收缩数据库的每个文件以除掉未使用的页面,数据文件和事务日志文件都能被收缩。收缩可以成组进行，也可以个别进行。可以指定时间间隔使得数据库在给定的时间自动收缩，这时收缩操作是在后台发生的，不会影响任何数据库用户的活动。当使用了ALTER DATABASE 的AUTO_SHRINK 选项设置数据库的自动收缩（或者是系统存储过程sp_dboption）时，每当数据库中有大量的空闲空间时，数据库就会收缩，然而如果要移除的空闲空间百分比不能被设置的话则空闲空间将会尽可能地被释放。

不能收缩比其最初的规模小的完全的数据库，所以假定一个数据库在创建时的初始大小是10M，然后增长到了100M，如果此时数据库中所有的数据都被删除了，则它能收缩到的最小规模也是10M，不过使用DBCC SHRINKFILE语句可以将单个数据库文件收缩到小于初始规模的大小，如果使用这种方法就必须对每个文件进行操作而不能对整个数据库收缩。

事务日志文件的收缩有固定的界线，虚拟日志的规模决定了可能的减少。因此日志文件决不能收缩到比虚拟日志文件更小的规模，收缩事务日志文件将删除未使用的虚拟日志文件，但会留下至少一个虚拟日志文件。

SQL Server 2000中DBCC SHRINK DATABASE或DBCC SHRINKFILE操作会试图立即将事务日志文件收缩到要求的尺寸（在取整的条件下）.应该在收缩文件之前就截取好日志文件已减少逻辑日志文件的大小，并且标记出不含有任何逻辑日志的虚拟日志。

不需要删除，不就是日志文件太大，想瘦身嘛，很简单。

首先用右键选择该数据库，然后点属性，在属性页里面选择“选项”选项卡，然后你能看到，故障还原模型里面默认的是“完全”，改成“简单”，确定

然后收缩数据库（最好单个文件的收缩），这个时候你能看到，日志文件大幅度减少了，之后再次把故障还原模式修改成“完全”即可

这种方法绝对不会产生文件丢失，也没有任何安全问题，放心使用吧，呵呵！

首先应该确定是谁慢的，往往是程序处理方面的问题而不是数据库的问题。
程序方面应该尽可能的减少数据查询返回的内容，比如可以查询返回ID，然后再根据ID一条一条的查询具体内容，看似慢了，在数据量达的时候快很多

对于数据可以参照下面几点
1、优化SQL语句，SQL语句对查询速度影响最大
2、对于经常查询的字段作索引。但是这样会增加修改时的压力
4、优化SQLServer，比如给其分配固定的内存，预先分配查询内存，调整CPU使用率等。
5、优化硬件资源，比如使用更高的服务器或者硬盘，独立安排数据库的数据文件和索引文件，将数据文件分布于不同的物理硬盘上等等
6、考虑使用分布数据库或者对大表进行拆分

一、定时将服务器中SQL SERVER 2000数据库备份到局域网中另一台电脑上

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

--实现功能:定时将服务器中SQL SERVER 2000数据库备份到局域网中另一台电脑上
--Full BackUp
--Use Master
CREATE proc bakup_DataBase
as
Declare @strPsw varchar(50)
Declare @strUsr varchar(50)
Declare @strCmdShell varchar(300)
Declare @strDataBaseName varchar(20)
Declare @FullFileName Varchar(200)
Declare @FileFlag varchar(50)
Declare @ToFileName varchar(200)
Declare @SQLStr varchar(500)
Declare @SQLStr2 varchar(500)
Declare @FlagDel varchar(20)
Set @FileFlag=replace(replace(convert(char(20),getdate(),20),':','-'),' ','-')     --备份的文件命名规则:日期-时间.bak
Set @strUsr='Administrator\Administrator' --需填写域名\用户名（目标机器的Windows登陆名)
Set @strPsw='soondy'--需填写Windows登陆密码(如:Soondy)
Set @strCmdShell= 'net use \\192.168.2.178\c$ ' + @strPsw + ' /user:' +@strUsr --需填写IP(目标机器的IP地址，如:192.168.2.178)
Set @strDataBaseName='Soondy'--填写数据库名称(如:Soondy)
Set @FullFileName='E:\SoondyTest\'+@FileFlag+'.BAK'--需填写本地备份临时文件的目录，因为需要先备份到本地再copy到目标机器(如:保存目录为E:\SoondyTest\)
Set @ToFileName='\\192.168.2.178\c$\Test\' --需填写IP(目标机器的IP地址)以及保存的目录(如:\\192.168.2.178\c$\Test\)
Set @FlagDel='True'--填写True表示删除本地的备份临时文件，填写False或其他字符表示保留该文件
Set @SQLStr='copy '+@FullFileName+' '+@ToFileName
Set @SQLStr2='del ' +@FullFileName
BackUp DataBase  @strDataBaseName  To Disk=  @FullFileName   with init
exec master..xp_cmdshell  @strCmdShell--尝试连接到目标机器
exec Master..xp_cmdshell @SQLStr --拷贝到目标机器上
if (@FlagDel ='True') exec master.. xp_cmdshell @SQLStr2--删除本地的备份临时文件

报 The Network Adapter could not establish the connection 异常的所有遇到过的原因，并提供了图形化的排查向导。
不过根据经验，没有升级到SP4的可能性最高。可以直接看第6部分。
常见的错误为： The Network Adapter could not establish the connection
1 检查SQL SERVER 是否允许远程访问.
具体步骤:
1)打开”企业管理器”,打开控制台根目录>SQL Server 组>数据库
2)在相应”数据库”上单击右键,选择”属性”
3)选择”连接”选项卡,检查”远程服务器连接”下,RPC服务是否选择.

2 使用telnet IP地址 1433,系统是否提示连接出错 比如 telnet 127.0.0.1 1433 如果出现黑屏，
这端口正常 如系统提示出错 检查是否防火墙屏蔽了SQL SERVER 或 java IDE 的网络访问端口
如果是,关闭防火墙,重新启动SQL SERVER和java IDE,进行测试, 如果系统仍提示上述错误,尝试下列步骤

3 检查SQL SERVER 端口号及是否启用了TCP/IP协议
具体步骤: 1)打开”企业管理器”,打开控制台根目录>SQL Server 组>数据库
2)在相应”数据库”上单击右键,选择”属性”
3)选择”常规”选项卡,点击”网络配置”,如启用的协议中无”TCP/IP协议”将其加入
4)选择”TCP/IP协议”,点击”属性”,检查其端口号是否为1433
5)如端口号为1433将其修改为其它端口号,修改jdbc连接语句,将端口号同样改为新启用的端口号,
如jdbc:microsoft:sqlserver://server_name:1400(假设新端口号为 1400)
4 以下的方法你每种的去试试:
1)检查一下sql server的connection有没有full.
2)如果是认证的问题. 可到sql enterprice manager->指定server->内容->安全性. 把验证改为 SQL server 及 windows.
3)点选属性检查一下port是否正确.
4)执行%MSSQL_HOME%\80\Tools\Binn\SVRNETCN.exe 把TCP/IP启用.
5)关掉防火墙.
6)把SQL server update到sp4: http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5
经我验证，一般升级到sp4就好用了！ 注意升级的方法，不是双击运行就可以了，
这个SP4是解压缩程序，你要指定一个目录，推荐c:\,解压缩后，
需要转到那个目录运行里面的setup.bat才是真正的安装。不知道为何微软不直接调用那个。！

SQL code远程连接sql server 2000服务器的解决方案

二 在Dos或命令行下输入telnet 服务器IP 端口，看能否连通。

如telnet 202.114.100.100 1433
通常端口值是1433，因为1433是sql server 2000的对于Tcp/IP的默认侦听端口。如果有问题，通常这一步会出问题。通常的提示
是“……无法打开连接,连接失败“。
如果这一步有问题，应该检查以下选项。

三 检查客户端设置

程序-> Microsoft SQL Server -> 客户端网络使用工具。像在服务器网络实用工具里一样，确保客户端tcp/ip协议启用，并且
默认端口为1433(或其他端口，与服务器端保持一致就行)。

四 在企业管理器里或查询那分析器连接测试

企业管理器-> 右键SQlserver组-> 新建sqlserver注册-> 下一步-> 写入远程IP-> 下一步-> 选Sqlserver登陆-> 下一步-> 写入
登陆名与密码(sa,password)-> 下一步-> 下一步-> 完成
查询分析器-> 文件-> 连接-> 写入远程IP-> 写入登录名和密码(sa,password)-> 确定
通常建议在查询分析器里做，因为默认情况下，通过企业管理器注册另外一台SQL Server的超时设置是4秒，而查询分析器是
15秒。

修改默认连接超时的方法:
企业管理器-> 工具-> 选项-> 在弹出的“SQL Server企业管理器属性“窗口中，点击“高级“选项卡-> 连接设置-> 在 登录超时
(秒) 后面的框里输入一个较大的数字
查询分析器-> 工具-> 选项-> 连接-> 在 登录超时(秒) 后面的框里输入一个较大的数字
通常就可以连通了，如果提示错误，则进入下一步。

五 错误产生的原因通常是由于SQL Server使用了“仅 Windows”的身份验证方式，因此用户无法使用SQL Server的登录帐户
(如 sa )进行连接。解决方法如下所示:

1 在服务器端使用企业管理器，并且选择“使用 Windows 身份验证“连接上 SQL Server。
2 展开“SQL Server组“，鼠标右键点击SQL Server服务器的名称，选择“属性“，再选择“安全性“选项卡。
3 在“身份验证“下，选择“SQL Server和 Windows “。
4 重新启动SQL Server服务。(在dos或命令行下面net stop mssqlserver停止服务，net start mssqlserver启动服务，也是一种快捷的方法)。

前几天遇到一个棘手的问题，每天下载6~7万的数据并更新到数据库，
原先的实现方法是这样的：先下载源数据到本地txt文件，然后读取本地文件并逐条进行插入
。但是 这样就产生了问题，首先 就是效率问题 ，一条数据一秒的话，70000/3600 将近19个小时才能全部搞掂，太耗时
其次：对数据库和文件频繁的读写 给系统带来了压力，会发现时不时的挂掉的现象。必须改进，老贺忽然说起 用bulk insert批量导入数据库可以解决这个问题。

 bulk insert table_temp FROM 'E:\2010-4-27.txt' with 
( FIELDTERMINATOR =',', ROWTERMINATOR = ' ', KEEPIDENTITY, ROWS_PER_BATCH=1000 )

执行速度果然 很快。但是bulk insert也有不如意的地方 我们需要灵活的处理。这我遇到的这个问题中 源文件txt 是只有一个字段的 而目标表是有多个字段的。这时侯 我们需要对txt文件处理，我的方法是将txt的内容批量替换成多个以逗号的空字段。然后插入到一个新建的临时表中。为什么是临时表，因为有了临时表 我们可以做很多想要的处理，再将临时表的内容批量insert到目标表。速度也是 灰常快的 。

–游标卡尺使用demo –表结构

create table test ( 	id int, 	name nvarchar(10)

–测试数据

 insert into test values(1,'a')
 insert into test values(2,'b')
 insert into test values(3,'c')
 insert into test values(4,'d')

alter proc proc_CursorDemo as declare @name nvarchar(10)
 --定义游标
declare test_cursor cursor local for   select [name] from  test
--打开游标
open test_cursor fetch next from test_cursor into @name
 while @@fetch_status=0 begin
	print @name
fetch next from test_cursor into @name 	 end
 --关闭游标
 close test_cursor
 --测试 exec proc_CursorDemo