分类目录归档：SQL Server

sql server中查看对象

发表于 2011 年 4 月 9 日由惜分飞

1、查询所有数据库
select * from sys.databases;

2、查询对象
select * from sysobjects;

3、复制表结构和数据
SELECT * INTO t_1 FROM master.dbo.spt_monitor;

4、查询视图
SELECT * FROM sys.views;

5、查询列
select * from sys.columns;

6、查询索引
select * from sys.indexes;

发表在 SQL Server | 评论关闭

由于文件组 ‘DEFAULT’ 中的磁盘空间不足，无法为数据库 ‘TEMPDB’ 分配新页

发表于 2011 年 4 月 9 日由惜分飞

今天接到一个朋友的问题，sql server报“由于文件组 ‘DEFAULT’ 中的磁盘空间不足，无法为数据库 ‘TEMPDB’ 分配新页”，按照错误提示应该是tempdb空间不足，让其查看tempdb对应数据库文件大小为不到4g，查看其对应文件为自增长，硬盘空间还有40g多点，那应该没有问题。后来她说该文件所处位置的盘是fat32的，那问题确定是fat32最大支持文件大小是4g。解决办法:
1、让tempdb中的文件恢复默认值，设置其最大值
1）重启sql server服务，tempdb一般会自动变为初始化大小
2）如果1）失败，使用下面语句
dbcc shrinkfile (tempdev, 10)
dbcc shrinkfile (templog, 10)

2）然后设置tempdev和templog的最大大小为4000m

2、添加数据文件和日志文件，并设置其最大值为4000m

3、修改程序，从根源上解决问题
分析产生该问题原因
询问朋友后，原来是她对一个6kw的表和1kw的表进行关联，然后取得需要的数据插入到另一张表中，因为涉及的数据量非常大，所有导致tempdb数据库飞速增大，超过系统文件大小限制。导致文章开始的错误，光添加文件，对于这么大数据量的程序来说还是不能解决问题。应该在程序上去优化，而不是靠添加tempdb中文件的大小和数量来解决问题。
添加程序的提交次数，而不是所有数据一次提交，放在一个事物中。
我根据程序需求模拟的写了两种处理过程的方法
模拟环境：

--创建模拟表
SELECT id,name INTO t_1 FROM sys.sysobjects;
SELECT object_id id,name INTO t_2 FROM sys.columns;
--创建跟踪表
CREATE TABLE t_s (id int DEFAULT 0,date datetime DEFAULT getdate());
INSERT INTO t_s  VALUES(0,getdate());
--创建需要插入数据表
    SELECT t1.name n1,
           t2.name n2,
           t1.id
           INTO test_1
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id 
    AND 1=2;

方法一：
特点：1、优点执行效率比较高，无需排序和插入跟踪表
2、缺点：不能查看程序执行进度，如果异常终止，必须重新执行

create PROCEDURE [dbo].[INSERT_d_1]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,
           t2.name,
           t1.id
    FROM   t_1 t1,
           t_2 t2
    WHERE  t1.id = t2.id
    ORDER BY
           t1.id
open c1                       
fetch next from c1 into @name1,@name2,@id 
WHILE @@fetch_status=0
BEGIN
	INSERT INTO test_1 VALUES(@name1,@name2,@id)
    FETCH next FROM c1 INTO @name1,@name2,@id
END
CLOSE c1                  
DEALLOCATE c1

方法二：
特点：1、可以跟踪程序的执行进度查询t_s表，如果异常终止可以写其他程序继续
2、程序使用二重游标，执行效率比较低

create PROCEDURE [dbo].[INSERT_d_2]
AS
DECLARE @name1 varchar(4000)
DECLARE @name2 varchar(4000)
DECLARE @id int
DECLARE c1 CURSOR  
FOR
    SELECT t1.name,          
           t1.id
    FROM   t_1 t1
    ORDER BY t1.id

open c1                       
fetch next from c1 into @name1,@id 
WHILE @@fetch_status=0
BEGIN
	--二重循环
	DECLARE c2 CURSOR FOR 
	SELECT t2.name FROM t_2 t2 WHERE t2.id=@id
	OPEN c2
	FETCH next FROM c2 INTO @name2
	WHILE @@FETCH_STATUS=0
	BEGIN
		INSERT INTO test_1 VALUES(@name1,@name2,@id)
		UPDATE t_s SET id=@id,date=getdate()
		FETCH next FROM c2 INTO @name2
	END
	CLOSE c2
	DEALLOCATE c2
    FETCH next FROM c1 INTO @name1,@id
END
CLOSE c1                  
DEALLOCATE c1

发表在 SQL Server | 评论关闭

sql server 2005镜像配置

发表于 2011 年 2 月 27 日由惜分飞

1、服务器ip地址和数据库版本
主机：192.168.1.1
备机：192.168.1.110
数据库：sql server 2005 sp4

2、主备实例互通
1）创建证书

--主机执行
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xifenfei';
GO
USE MASTER
GO
CREATE CERTIFICATE HOST_A_cert 
       WITH SUBJECT = 'A certificate for database mirroring',
       START_DATE = '11/25/2009',
       EXPIRY_DATE = '12/31/2199';
GO

--备机执行
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xifenfei';
GO
USE MASTER
GO
CREATE CERTIFICATE HOST_B_cert 
       WITH SUBJECT = 'A certificate for database mirroring',
       START_DATE = '11/25/2009',
       EXPIRY_DATE = '12/31/2199';
GO

2）创建连接的端点

--主机执行
create endpoint endpoint_mirroring
state=started
as
tcp (listener_port=5022,listener_ip=all)
for
database_mirroring
(authentication=certificate HOST_A_cert,encryption=supported,role=all);

--备机执行
create endpoint endpoint_mirroring
state=started
as
tcp (listener_port=5022,listener_ip=all)
for
database_mirroring
(authentication=certificate HOST_B_cert,encryption=supported,role=all);

3）备份证书以备建立互联

--主机执行
backup certificate HOST_A_cert to file='E:\database\key\HOST_A_cert.cer';
--备机执行
backup certificate HOST_B_cert to file='C:\database\mssql\key\HOST_B_cert.cer';

4）互换备份证书
拷贝证书文件到对象文件夹中

5）添加登录名、用户

--主机执行
create login HOST_B_login with password='20091125';
create user HOST_B_user for login HOST_B_login;
create certificate HOST_B_cert authorization HOST_B_user from file='C:\database\mssql\key\HOST_B_cert.cer';
grant connect on endpoint::endpoint_mirroring to [HOST_B_login];

--备机执行
create login HOST_A_login with password='20091125';
create user HOST_A_user for login HOST_A_login;
create certificate HOST_A_cert authorization HOST_A_user from file='E:\database\key\HOST_A_cert.cer';
grant connect on endpoint::endpoint_mirroring to [HOST_A_login];

3、建立镜像关系
1）备份还原数据库

/*尝试从刚刚使用的全备文件进行还原，在还原数据的时候需要使用”WITH NO RECOVERY“选项。*/
--主机执行
backup database test to disk='D:\sqlbackup\test091124full.bak'
backup log test to disk='D:\sqlbackup\test091124log.bak'

--备机执行
restore database acmr_10jqka from disk='D:\sqlbackup\test091124full.bak' with replace,norecovery
restore log acmr_10jqka from disk='D:\dbbackup\118_sql\acmr_10jqka091125log.bak' with norecovery

2）建立镜像

/*注意：先备机，后主机*/
--备机执行
alter database acmr_10jqka set partner='TCP://192.168.1.1:5022';
--主机执行
alter database test set partner='TCP://192.168.1.110:5022';

3）事务安全性设置

/*默认情况下，事务安全级别的设置为FULL，即“同步运行模式”，而且，SQL SERVER 2005标准版只支持同步模式。*/
/*如果关闭事务安全级别FULL模式，则可以切换到异步运行模式，该模式可使性能达到最佳。*/
--主机执行
use master
go
alter database test set partner safety full;   --事务安全，同步模式
alter database test set partner safety off;    --事务不安全，异步模式

4）切换

--主机执行下面命令后，即切换为备机角色
use master
go
alter database test set partner failover;
/*同理，也可以在备机（主体数据库）上执行上面命令，之后切换为备机（备数据库）*/

发表在 SQL Server | 评论关闭

17813235971

QQ 咨询

分类目录归档：SQL Server

sql server中查看对象

由于文件组 ‘DEFAULT’ 中的磁盘空间不足，无法为数据库 ‘TEMPDB’ 分配新页

sql server 2005镜像配置

文章分类

最近发表

分类目录归档：SQL Server

sql server中查看对象

由于文件组 ‘DEFAULT’ 中的磁盘空间不足，无法为数据库 ‘TEMPDB’ 分配新页

sql server 2005镜像配置

标签云

文章分类

最近发表