各位大仙~数据库日志设备还有很多空间为什么提示满呢
各位大仙~数据库日志设备还有很多空间为什么提示满呢公司数据库在做导入操作时候,提示当前要导入的库的日志满
The transaction log in database flwdb is almost full.Your transaction is being suspended until space is made available in the log.
但是进行日志截断后问题依然出现。
通过sp_helpsegment logsegment可以看到
table_name index_name indid
------------------------------ ------------------------------ ------
syslogs syslogs 0
total_size total_pages free_pages used_pages reserved_pages
----------------- --------------- --------------- --------------- ---------------
29696.0MB 15204352 15143594 60738 20
增加过日志设备,问题照旧,是在不知道为什么会出现这种不一致现象,请各位帮忙看看
另:数据库日志和数据分开存放的 各位牛人给支支招,不胜感激 是否dboption 'select into/bulkcopy/pllsort' 未打开? 在itpubs上不是回答过你了吗? 在itpubs上不是回答过你了吗?
Eisen 发表于 2011-08-29 16:03 http://bbs.chinaunix.net/images/common/back.gif
这位大哥,不好意思我才看见,'select into/bulkcopy/pllsort'打开着得。。数据库里面索引和存储过程都删了的,感觉数据库不一致,bcp时候总是提示log设备满了...挺长时间了,因为怕关闭数据库有起不起来的风险,所以还没敢重启,大哥还有什么好方法么? 本帖最后由 Eisen 于 2011-08-30 08:54 编辑
sp_helpdb flwdb显示的结果?
另外,你要导入的是一个表还是多个表?数据量有多大?最大的有多大?
另外还有dboption 'trunc log at checkpoint'是否打开? sp_helpdb flwdb显示的结果?
另外,你要导入的是一个表还是多个表?数据量有多大?最大的有多大?
另外还有d ...
Eisen 发表于 2011-08-30 08:51 http://bbs.chinaunix.net/images/common/back.gif
name db_size owner dbid created status
------------------------ ------------- ------------------------ ------ ------------------ ------------------------------------------------------------------------------------------------------
flwdb 356148.5 MB xxx 9 Apr 03, 2009 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
(1 row affected)
device_fragments size usage created free kbytes
------------------------------ ------------- -------------------- ------------------------- ----------------
flwdev 8192.0 MB data only Apr3 2009 10:14AM 4228456
flwdevlog 2.0 MB log only Apr3 2009 10:14AM not applicable
flwdevlog 1022.0 MB log only May 11 20099:43AM not applicable
flwdevlog 1024.0 MB log only May 11 2009 10:35AM not applicable
flwdev 2048.0 MB data only May 14 2009 11:03AM 1251380
flwdev1 8192.0 MB data only May 14 2009 11:50AM 5053478
flwdevlog2 2048.0 MB log only May 14 20093:35PM not applicable
flwdev2 9012.0 MB data only Jul 24 20091:08PM 5217792
flwdev3 9000.0 MB data only Jul 24 20091:12PM 5121426
flwdev1 2048.0 MB data only Oct 26 2009 10:21AM 1397656
flwdev2 1024.0 MB data only Oct 26 2009 10:22AM 884604
flwdev3 1024.0 MB data only Oct 26 2009 10:23AM 819660
flwdev4 5116.0 MB data only Oct 26 2009 11:12AM 2799674
flwdev4 3072.0 MB data only Oct 26 20093:14PM 764134
flwdev4 2048.0 MB data only Nov2 20099:15AM 141150
flwdev5 2048.0 MB data only Nov2 20091:49PM 1647576
flwdev5 2048.0 MB data only Nov3 2009 11:40AM 1726502
flwdevlog 2.0 MB log only Nov4 20093:03AM not applicable
flwdevlog 256.0 MB log only Nov4 20093:03AM not applicable
flwdev5 2048.0 MB data only Nov6 20091:35PM 1697956
flwdev5 2048.0 MB data only Nov6 20091:37PM 1149812
flwdev5 2048.0 MB data only Nov9 20097:55AM 1512250
flwdev2 204.0 MB data only Nov9 20098:44AM 107698
flwdev3 216.0 MB data only Nov9 20098:44AM 126330
flwdev4 4.0 MB data only Nov9 20098:44AM 720
flwdev6 2048.0 MB data only Nov 11 20097:41AM 1283524
flwdev6 2048.0 MB data only Nov 12 20097:39AM 1052944
flwdev6 2048.0 MB data only Nov 12 2009 10:05AM 869400
flwdev6 2048.0 MB data only Nov 13 20097:48AM 1512580
flwdev6 2048.0 MB data only Nov 13 20093:33PM 274338
flwdev7 2048.0 MB data only Nov 14 20093:17PM 142716
flwdev7 2048.0 MB data only Nov 16 20099:29AM 58158
flwdev7 1024.0 MB data only Nov 16 2009 12:52PM 807242
flwdev7 1024.0 MB data only Nov 16 20091:21PM 453608
flwdev7 2048.0 MB data only Nov 17 20097:44AM 1057754
flwdev7 2048.0 MB data only Nov 17 20093:27PM 1473184
flwdevlog 2.0 MB log only Nov 18 20098:15PM not applicable
flwdevlog 2.0 MB log only Nov 18 20098:15PM not applicable
flwdevlog 256.0 MB log only Nov 18 20098:15PM not applicable
flwdevlog 256.0 MB log only Nov 18 20098:18PM not applicable
flwdevlog 256.0 MB log only Nov 18 20098:21PM not applicable
flwdev8 10240.0 MB data only Nov 23 20094:13PM 5870002
flwdevlog 2.0 MB log only Dec 14 20093:44AM not applicable
flwdevlog 256.0 MB log only Dec 14 20093:44AM not applicable
flwdevlog 256.0 MB log only Dec 14 20093:46AM not applicable
flwdevlog 42.5 MB log only Dec 14 20093:47AM not applicable
flwdev9 10240.0 MB data only Dec 14 20093:15PM 6911218
flwdev10 5120.0 MB data only Dec 14 20093:31PM 3141970
flwdev10 5012.0 MB data only Dec 14 20093:50PM 4425536
flwdev10 108.0 MB data only Dec 14 20094:12PM 106750
flwdev11 2046.0 MB data only Dec 16 20098:55AM 1565384
flwdev11 2050.0 MB data only Dec 16 20099:05AM 1745654
flwdev11 4096.0 MB data only Dec 16 2009 10:36AM 3051840
flwdev11 2048.0 MB data only Dec 16 2009 11:29AM 1862568
flwdev12 4096.0 MB data only Dec 16 20093:58PM 2823284
flwdev12 4096.0 MB data only Dec 16 20094:53PM 2821792
flwdev12 2048.0 MB data only Dec 16 20095:38PM 1410752
flwdev13 4096.0 MB data only Dec 21 20098:50AM 3900738
flwdev13 4096.0 MB data only Dec 23 20099:26AM 3425396
flwdevlog2 1024.0 MB log only Dec 23 20099:31AM not applicable
flwdev13 2048.0 MB data only Dec 24 20098:51AM 1250526
flwdevlog2 1024.0 MB log only Dec 24 20098:52AM not applicable
flwdev14 4096.0 MB data only Dec 24 20099:04AM 3534618
flwdev14 4096.0 MB data only Dec 29 20098:59AM 3006546
flwdev14 2048.0 MB data only Feb 13 20106:57PM 1512176
flwdev15 2048.0 MB data only Feb 20 20109:19AM 931058
flwdev15 8192.0 MB data only Feb 21 20109:08AM 5933830
flwdev16 4086.0 MB data only Feb 25 20109:01AM 2269212
flwdev16 6154.0 MB data only Mar1 20109:27AM 4494878
flwdev17 5086.0 MB data only Mar3 2010 12:56PM 3546376
flwdev18 4096.0 MB data only Mar5 20105:04PM 2992952
flwdev17 5154.0 MB data only Mar8 20109:14AM 3374314
flwdev18 4096.0 MB data only Mar 10 20108:43AM 3408870
flwdev18 2048.0 MB data only Mar 12 20108:49AM 1949666
flwdev19 4096.0 MB data only Mar 15 20109:59AM 3765814
flwdev19 4096.0 MB data only Mar 17 20109:48AM 3731318
flwdev19 2048.0 MB data only Mar 22 20109:41AM 1829432
flwdev20 4096.0 MB data only Mar 22 20104:28PM 3499064
flwdev20 6144.0 MB data only Mar 25 20109:44AM 4275214
flwdev21 4096.0 MB data only Mar 29 2010 10:01AM 3183798
flwdev21 6144.0 MB data only Mar 31 20109:59AM 5542760
flwdb22 10240.0 MB data only Apr6 20101:45PM 8967380
flwdev23 4096.0 MB data only Apr 12 2010 11:07AM 3553972
flwdev23 6144.0 MB data only Apr 16 20109:57AM 5860888
flwdevlog3 100.0 MB log only Apr 16 20104:22PM not applicable
flwdev24 10240.0 MB data only Apr 21 2010 11:05AM 9524650
flwdevlog3 1024.0 MB log only Apr 27 2010 12:54PM not applicable
flwdevlog3 100.0 MB log only Apr 27 20101:45PM not applicable
flwdevlog3 100.0 MB log only Apr 27 20102:08PM not applicable
flwdevlog3 100.0 MB log only Apr 27 20102:10PM not applicable
flwdevlog3 100.0 MB log only Apr 27 20102:12PM not applicable
flwdevlog3 100.0 MB log only Apr 27 20102:13PM not applicable
flwdevlog3 100.0 MB log only Apr 27 20102:16PM not applicable
flwdevlog3 200.0 MB log only Apr 28 2010 10:21AM not applicable
flwdevlog3 100.0 MB log only Apr 28 2010 10:24AM not applicable
flwdevlog3 24.0 MB log only Apr 28 2010 10:28AM not applicable
flwdevlog4 2048.0 MB log only Apr 28 2010 12:23PM not applicable
flwdev25 10240.0 MB data only Apr 29 20106:39PM 8351490
flwdevlog4 100.0 MB log only Apr 30 2010 10:13AM not applicable
flwdevlog4 100.0 MB log only Apr 30 2010 11:03AM not applicable
flwdevlog4 100.0 MB log only Apr 30 2010 11:16AM not applicable
flwdevlog4 1024.0 MB log only Apr 30 2010 11:33AM not applicable
flwdevlog4 100.0 MB log only May4 2010 10:12AM not applicable
flwdevlog4 1648.0 MB log only May4 2010 11:02AM not applicable
flwdev25 10240.0 MB data only May 13 20109:42AM 9419576
flwdev26 20480.0 MB data only May 24 2010 10:54AM 15580718
flwdevlog6 10240.0 MB log only Jun3 20102:47PM not applicable
flwdevlog5 1024.0 MB log only Jun3 20105:02PM not applicable
flwdevlog7 1024.0 MB log only Jun3 20105:19PM not applicable
flwdevlog8 1024.0 MB log only Jun3 20105:59PM not applicable
flwdevlog9 1024.0 MB log only Jun3 20106:02PM not applicable
flwdevlog 2.0 MB log only Aug 20 2011 12:14PM not applicable
flwdevlog10 1024.0 MB log only Aug 23 20114:32PM not applicable
加了很多日志设备不起作用,可以看到trunc log on chkpt 是开着的,另外我估计不是导入数据量的问题,因为随便一个小的事物提交都会报错,错误还是日志空间不足,数据库貌似就不知道它还有很多日志空间,它就是不明白。。怎么让他明白呢 dump tran flwdb with truncate_only之后呢? dump tran flwdb with truncate_only之后呢?
Eisen 发表于 2011-08-30 09:31 http://bbs.chinaunix.net/images/common/back.gif
也是一样的满日志,现在能想到的方法就是重启了,但是不知道重启了能不能解决。哥们还有什么好方法吗 什么版本ase?
页:
[1]
2