我们一个数据库是用在餐饮上的,数据库现在超级大,目前有近20G,但是如果使用导入导出功能重建数据库之后,只有1G不到,使用sp_space 发现是个别明细表非常大,使用了以下方法,但是不行,看那位大虾走过路过,帮忙解决一下。/******************************方法一************************************/
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GOprint '第二步, 把数据库的恢复模式设成”简单”:'
ALTER DATABASE accdbyl SET RECOVERY SIMPLE
GOprint '第三步, 运行checkpoint指令, 把dirty page写进数据库:'
CHECKPOINT
GO
print '第四步, 截断日志: '
BACKUP LOG accdbyl WITH NO_LOG
GOprint '第五步, 记录一下日志名为下一步做准备:'SELECT Name
FROM sysfiles
WHERE name LIKE '%LOG%'
GO
print '第六步, 收缩日志文件, 把不用的空间释放给操作系统:'
DBCC SHRINKFILE ('Accdb_log', 0)
GOprint '第七步, 验证一下日志大小是否达到所需大小了:'
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
/******************************方法二************************************/
declare @strCmd as varchar(1000)
declare crCmd cursor
for
Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10) as strCmd
From SysIndexes
Where ID Not IN (Select ID From SYSObjects Where xType='S' )
OPEN crCmd
FETCH NEXT FROM crCmd
INTO @strCmdWHILE @@FETCH_STATUS = 0
BEGIN
print '开始处理' + @strCmd + ' ...'
FETCH NEXT FROM crCmd into @strCmd
execute (@strCmd)
end
CLOSE crCmd
DEALLOCATE crCmd
--貌似清理索引碎片使用,但是仍旧不行,数据库仍旧很大/******************************方法三************************************/
print '********************************************************************************'
print '* 重建索引并压缩数据库 *'
print '******************************************************************************** '
declare @strCmd as varchar(1000),
@strCmd1 as varchar(1000)
declare crCmd cursor
for
Select 'DBCC dbreindex ('+ name +','+ char(39)+char(39) +',100)' as strCmd
from sysobjects
where xtype='u' and name ='t_cashrec'
OPEN crCmd
FETCH NEXT FROM crCmd
INTO @strCmdWHILE @@FETCH_STATUS = 0
BEGIN
print '开始处理' + @strCmd + ' ...'
FETCH NEXT FROM crCmd into @strCmd
execute (@strCmd)
end
CLOSE crCmd
DEALLOCATE crCmd
print '开始压缩系统日志..'
DUMP TRANSACTION db_name() WITH NO_LOG
BACKUP LOG db_name() WITH NO_LOG
print '开始压缩数据库'
DBCC SHRINKDATABASE(db_name())
DBCC SHRINKFILE(1) print'********************************************************************************'
print '* 完 成 *'
print '******************************************************************************** '--运行了一个查询表索引空间的语句如下:select object_name(id) as tablename,
8*reserved/1024 as reserved,
rtrim(8*dpages/1024)+'Mb' as used,
8*(reserved-dpages)/1024 as unused,
8*dpages/1024-rows/1024*minlen/1024 as free,
rows,indid,name from sysindexes
--where indid=1
order by used desc
T_ConsumeRec
64 64Mb 0 6 2465589 3 IDX_PK T_IncomeRec
6 4Mb 1 2 47732 1 PK_T_IncomeRec T_ConsumeRec
47 47Mb 0 5 2465589 2 PK_T_ConsumeRec T_ConsumeRec
45 45Mb 0 6 2465589 6 IX_T_ConsumeRec_2 T_IncomeDay
25 3Mb 21 2 45072 0 T_IncomeDay T_ConsumeRec
35 35Mb 0 5 2465589 4 IX_T_ConsumeRec T_ConsumeRec
35 35Mb 0 5 2465589 5 IX_T_ConsumeRec_1 T_ChkVldAccount
262 33Mb 228 32 29425 0 T_ChkVldAccount T_CashRec
2 2Mb 0 0 152945 2 IDX_PK T_CashRec
2 2Mb 0 1 152945 3 IX_T_CashRec T_CashRec
2 2Mb 0 0 152945 4 IX_T_CashRec_1 T_Customers
3 2Mb 1 2 6194 1 PK_T_Customers T_IncomeDay
1 1Mb 0 0 45602 2 PK_T_IncomeDay T_ConsumeRec
15203 1929Mb 13273 1765 2465540 0 T_ConsumeRec T_CashRec
783 102Mb 681 90 152915 0 T_CashRec
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GOprint '第二步, 把数据库的恢复模式设成”简单”:'
ALTER DATABASE accdbyl SET RECOVERY SIMPLE
GOprint '第三步, 运行checkpoint指令, 把dirty page写进数据库:'
CHECKPOINT
GO
print '第四步, 截断日志: '
BACKUP LOG accdbyl WITH NO_LOG
GOprint '第五步, 记录一下日志名为下一步做准备:'SELECT Name
FROM sysfiles
WHERE name LIKE '%LOG%'
GO
print '第六步, 收缩日志文件, 把不用的空间释放给操作系统:'
DBCC SHRINKFILE ('Accdb_log', 0)
GOprint '第七步, 验证一下日志大小是否达到所需大小了:'
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
/******************************方法二************************************/
declare @strCmd as varchar(1000)
declare crCmd cursor
for
Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10) as strCmd
From SysIndexes
Where ID Not IN (Select ID From SYSObjects Where xType='S' )
OPEN crCmd
FETCH NEXT FROM crCmd
INTO @strCmdWHILE @@FETCH_STATUS = 0
BEGIN
print '开始处理' + @strCmd + ' ...'
FETCH NEXT FROM crCmd into @strCmd
execute (@strCmd)
end
CLOSE crCmd
DEALLOCATE crCmd
--貌似清理索引碎片使用,但是仍旧不行,数据库仍旧很大/******************************方法三************************************/
print '********************************************************************************'
print '* 重建索引并压缩数据库 *'
print '******************************************************************************** '
declare @strCmd as varchar(1000),
@strCmd1 as varchar(1000)
declare crCmd cursor
for
Select 'DBCC dbreindex ('+ name +','+ char(39)+char(39) +',100)' as strCmd
from sysobjects
where xtype='u' and name ='t_cashrec'
OPEN crCmd
FETCH NEXT FROM crCmd
INTO @strCmdWHILE @@FETCH_STATUS = 0
BEGIN
print '开始处理' + @strCmd + ' ...'
FETCH NEXT FROM crCmd into @strCmd
execute (@strCmd)
end
CLOSE crCmd
DEALLOCATE crCmd
print '开始压缩系统日志..'
DUMP TRANSACTION db_name() WITH NO_LOG
BACKUP LOG db_name() WITH NO_LOG
print '开始压缩数据库'
DBCC SHRINKDATABASE(db_name())
DBCC SHRINKFILE(1) print'********************************************************************************'
print '* 完 成 *'
print '******************************************************************************** '--运行了一个查询表索引空间的语句如下:select object_name(id) as tablename,
8*reserved/1024 as reserved,
rtrim(8*dpages/1024)+'Mb' as used,
8*(reserved-dpages)/1024 as unused,
8*dpages/1024-rows/1024*minlen/1024 as free,
rows,indid,name from sysindexes
--where indid=1
order by used desc
T_ConsumeRec
64 64Mb 0 6 2465589 3 IDX_PK T_IncomeRec
6 4Mb 1 2 47732 1 PK_T_IncomeRec T_ConsumeRec
47 47Mb 0 5 2465589 2 PK_T_ConsumeRec T_ConsumeRec
45 45Mb 0 6 2465589 6 IX_T_ConsumeRec_2 T_IncomeDay
25 3Mb 21 2 45072 0 T_IncomeDay T_ConsumeRec
35 35Mb 0 5 2465589 4 IX_T_ConsumeRec T_ConsumeRec
35 35Mb 0 5 2465589 5 IX_T_ConsumeRec_1 T_ChkVldAccount
262 33Mb 228 32 29425 0 T_ChkVldAccount T_CashRec
2 2Mb 0 0 152945 2 IDX_PK T_CashRec
2 2Mb 0 1 152945 3 IX_T_CashRec T_CashRec
2 2Mb 0 0 152945 4 IX_T_CashRec_1 T_Customers
3 2Mb 1 2 6194 1 PK_T_Customers T_IncomeDay
1 1Mb 0 0 45602 2 PK_T_IncomeDay T_ConsumeRec
15203 1929Mb 13273 1765 2465540 0 T_ConsumeRec T_CashRec
783 102Mb 681 90 152915 0 T_CashRec
解决方案 »
- 为什么我的SQL SERVER(MSSQLSERVER)服务启动不了
- sql语句中where条件中的字段存在则作为条件,没有则不作为条件,语法怎么写?
- sql 出错
- 一个SQL 语句如何写!~!~!~!~!~!~!~!~!
- SQL参数代入问题?
- 我又遇到了一个很难解决的问题啊,不知道SQL语句这样写........请各位帮忙!!!!!!!!!!!!!!!!!!!!!
- 求日期的计算
- 急急急,无法连接到本地的sql2000
- sql(连表查询)
- 谁人能帮我解答:本人在导入数据库时出现不能导入某些表的错误信息。
- 请教…MSSQL2005的灵异问题~
- 跪求report service xml做数据源问题解答,高手进
declare crCmd cursor
for
Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10) as strCmd
From SysIndexes
Where ID Not IN (Select ID From SYSObjects Where xType='S' ) OPEN crCmd
FETCH NEXT FROM crCmd
INTO @strCmd WHILE @@FETCH_STATUS = 0
BEGIN
print '开始处理' + @strCmd + ' ...'
FETCH NEXT FROM crCmd into @strCmd
execute (@strCmd)
end
CLOSE crCmd
DEALLOCATE crCmd