数据库事务日志清除操作指南_图文_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
数据库事务日志清除操作指南
上传于||暂无简介
阅读已结束，如果下载本文需要使用1下载券
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩6页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢博客访问： 783494
博文数量： 192
博客积分： 10080
博客等级： 上将
技术积分： 2680
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： WINDOWS
DUMP TRANSACTION
WITH NO_LOG
2.------------XXM,M,,
Trunc Log on ChkptLogSQLServerLogLog
dump transaction”Log
dump transaction with truncate_only
dump transaction with no_log
dump transaction with trancate_only”SYBASEdump transaction with no_log”SQLServer
??dump transaction database_name to dumpdevice”
exec sp_attach_single_file_db '', '.mdf'
DUMP TRANSACTION
WITH NO_LOG&
BACKUP LOG
WITH NO_LOG
& ----XXM,M,,
& ----XXM,M,,
DBCC SHRINKDATABASE()
--,1,:select * from sysfiles
DBCC SHRINKFILE(1)
4.(sql 7.0,)
& --------
& --------
E X E C sp_detach_db @dbname = 'pubs'
E X E C sp_attach_single_file_db @dbname = 'pubs',
&& @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
----------""
E X E C sp_dboption '', 'autoshrink', 'TRUE'
alter database
modify file(name=,maxsize=20)
updatedeletewherewhere
truncate log on checkpoint1
1trunc. log on chkpt.1
&&& backup log dbName to disk='fileName'
2with norecovery
&&& restore database dbName from disk='fileName' with norecovery
&&& restore log dbName from disk='fileName'
&&& with stopat='date_time'
SQL SERVER
DUMP TRANSACTION
WITH NO_LOG
2.------------XXM,M,,
SQL SERVER
alter database
set recovery simple
Truncate log on checkpoint(SQL7.0SQL 2000)CHECKPOINT 70% True Auto shrink25%25% Truncate log on checkpoint True
truncate tableselect into
??此法生成新的LOG520K
??或用代码：
??下面的示例分离 77169database 77169database
EXEC sp_detach_db @dbname = '77169database'
EXEC sp_attach_single_file_db @dbname = '77169database',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\77169database.mdf??
??DUMP TRANSACTION
WITH NO_LOG /// DUMP TRANSACTION ulionmis WITH NO_LOG
??企业管理器------------XXM,M,,
??企业管理器－＞服务器－＞数据库－＞属性－＞事务日志－＞将文件增长限制为2M
??自动收缩日志,
??ALTER DATABASE
??SET AUTO_SHRINK ON
??故障还原模型改为简单,
??USE MASTER
??ALTER DATABASE
SET RECOVERY SIMPLE
??-------------------------------------------------------------------------------
????截断事务日志：
????BACKUP LOG { database_name | @database_name_var }
?? { NO_LOG | TRUNCATE_ONLY } ]
??请按步骤进行,,
??否则可能损坏你的数据库.
??DUMP TRANSACTION
WITH NO_LOG
??BACKUP LOG
WITH NO_LOG
??企业管理器--------
??----XXM,M,,
??----XXM,M,,
??也可以用SQL
??DBCC SHRINKDATABASE()
??--,1,:select * from sysfiles
??DBCC SHRINKFILE(1)
??4.(sql 7.0,)
??企业管理器--------
??企业管理器--------
??此法将生成新的LOG500K
??或用代码：
??下面的示例分离 77169database 77169database
??EXEC sp_detach_db @dbname = '77169database'
??EXEC sp_attach_single_file_db @dbname = '77169database',
?? @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\77169database.mdf'
??企业管理器----------""
??EXEC sp_dboption '', 'autoshrink', 'TRUE'
??企业管理器--------
??alter database
modify file(name=,maxsize=20)
??-------------------------------------------------------------------------------
??压缩日志及数据库文件大小
??因为要对数据库进行分离处理
??所以存储过程不能创建在被压缩的数据库中
exec p_compdb 'test'
??use master --,master
??if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_compdb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
??drop procedure [dbo].[p_compdb]
??create proc p_compdb
??@dbname sysname, --
??@bkdatabase bit=1, --,,
??@bkfname nvarchar(260)='' --,,,:+
??exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')
??exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')
??exec('DBCC SHRINKDATABASE(['+@dbname+'])')
??exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TRUE''')
??if @bkdatabase=1
??if isnull(@bkfname,'')=''
??set @bkfname=@dbname+'_'+convert(varchar,getdate(),112)
??+replace(convert(varchar,getdate(),108),':','')
??select ='SQL ,:'+@bkfname
??exec('backup database ['+@dbname+'] to disk='''+@bkfname+'''')
?create table #t(fname nvarchar(260),type int)
?exec('insert into #t select filename,type=status&0x40 from ['+@dbname+']..sysfiles')
?exec('sp_detach_db '''+@dbname+'''')
??declare @fname nvarchar(260),@s varchar(8000)
??declare tb cursor local for select fname from #t where type=64
??fetch next from tb into @fname
??while @@fetch_status=0
??set @s='del "'+rtrim(@fname)+'"'
??exec master..xp_cmdshell @s,no_output
??fetch next from tb into @fname
??close tb
??deallocate tb
??set @s=''
??declare tb cursor local for select fname from #t where type=0
??fetch next from tb into @fname
??while @@fetch_status=0
??set @s=@s+','''+rtrim(@fname)+''''
??fetch next from tb into @fname
??close tb
??deallocate tb
??exec('sp_attach_single_file_db '''+@dbname+''''+@s)
DUMP TRANSACTION
WITH NO_LOG
2.------------XXM,M,,
SQL SERVER
alter database
set recovery simple
Truncate log on checkpoint(SQL7.0SQL 2000)CHECKPOINT 70% True Auto shrink25%25% Truncate log on checkpoint True
&200492411:23
600M, 33M, 2.8G!!!
SHIRNK DATABASE TRUNCATE LOG FILE, SQL SERVERBUG
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE Marias --
SELECT@LogicalFileName = Marias_log,--
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100-- (M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),@OriginalSize) +
8K pages or
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + MB
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLogVARCHAR(255)
SELECT@StartTime = GETDATE(),
@TruncLog = BACKUP LOG
+ db_name() +
WITH TRUNCATE_ONLY
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans valueS (Fill Log)
DELETE DummyTrans
SELECT @Counter = @Counter + 1
EXEC (@TruncLog)
SELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +
CONVERT(VARCHAR(30),size) +
8K pages or
CONVERT(VARCHAR(30),(size*8/1024)) + MB
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF&
删除MS SQL Server 2000日志
1.打开MS SQL Server 2000企业管理器
2.打开数据库，选中需要删除日期的数据库，点击“察看”，选择“任务板”：
3.鼠标右键点中需要删除日期的数据库，选择右键菜单“所有任务”-“备份数据库”：
4.选择“事务日志”：
5.点击“添加”，选择“文件名”：
6.选择备份目录，输入文件名，点击“确定”：
7.点击“确定”：
8.选择“重写现有媒体”，点击“确定”：
9.开始备份：
10.备份完成：
事务日志删除了一部分，如果还不够小，则再重复一次。
11.重新鼠标右键点中需要删除日志的数据库，选择右键菜单“所有任务”-“收缩数据库”：
12.点击“文件”：
13.选择日志“数据库文件”，在“收缩操作”中选择“从文件结尾截断可用空间”，点击“确定”：
14.日志收缩完毕：
SQL Server
1BACKUP LOG database WITH NO_LOG
&&&&& BACKUP LOG database WITH NO_LOG
2sql server
&&&&&&&&&&&&&& (2)
&&&&&&&&&&&&&& (3)mdf
&&&&& Auto shrink”
1)Enterprise Manager
Enterprise Manager All TasksShrink DatabaseFiles”Change” Change” OK”
2)Transact-SQL
DBCC SHRINKDATABASE DBCC SHRINKFILE DBCC SHRINKDATABASE DBCC SHRINKFILE
(1)DBCC SHRINKDATABASE
DBCC SHRINKDATABASE
DBCC SHRINKDATABASE (database_name [, target_percent][, {NOTRUNCATE | TRUNCATEONLY}] )
target_percent
NOTRUECATE
TRUNCATEONLY
SQL Server target_percent
mytest 20%
dbcc shrinkdatabase (mytest, 20)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(2)DBCC SHRINKFILE
DBCC SHRINKFILE
DBCC SHRINKFILE ( {file_name | file_id }{ [, target_size] |[, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}] } )
Identification number IDID
FILE_IDSp_helpdb
target_size
MB SQL Server
ALTER DATABASE
NOTRUNCATE TRUNCATEONLY DBCC SHRINKDATABASE
: mydb mydb_data2 1MB
use mydb dbcc shrinkfile (mydb_data2, 1)
& EXEC&& sp_attach_single_file_db&& @dbname&& =&& 'TyBusiness',&&&&
&&&&&&& @physname&& =&& 'E:\Help\TyBusiness.MDF'&&
& && 'TyBusiness'CREATE&& DATABASE&& &&
& && 'd:\Program&& Files\Microsoft&& SQL&& Server\MSSQL\TyBusiness_log.ldf'&& &&
& 2.sql&& server,&&
& 3.sql&& server,:&&
&&&&&&& sp_configure&& 'allow',1&&
&&&&&&& reconfigure&& with&& overirde&&
&&&&&&& update&& sysdatabases&& set&& status=32768&& where&& name='yourdata'&&
&&&&&&& dbcc&& rebuild_log('yourdata','your&& data&& path\newdata_log.ldf')&&
&&&&& update&& sysdatabases&& set&& status=0&& where&& name='yourdata'&&
&&&&& restore&& sysdatabases&& yourdata&& with&& recovery&&
&&&&& sp_configure&& 'allow',0&&
&&&&& reconfigure&& with&& override&&
& 6.sql&& server&&
& use&& yourdatabasename&&
& backup&& log&& yourdatabasename&& with&& TRUNCATE_ONLY&&&&
& DBCC&& SHRINKFILE&& (,&& )&&
& Query&&&& Analyzer:&&&&&&
& 1.:&&&&&&
& EXEC&&&& sp_detach_db&&&& 'dbname',&&&& 'true';&&&&&&&& --&&&&&&
& SQL&&&& Serverdatadbname_log.ldf&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
& 2.&&&&&&
& EXEC&&&& sp_attach_single_file_db&&&&&&
& 'dbname','d:\mssql7\data\dbname.mdf';&&&&&&
& --,500K.&&
use& yourdatabasename&
backup& log& yourdatabasename& with& TRUNCATE_ONLY&&&
DBCC& SHRINKFILE& (,& )&
3MuOnline_log.LDFS
4->muonline
backup log MuOnline with NO_LOG
backup log MuOnline with TRUNCATE_ONLY
DBCC SHRINKDATABASE(MuOnline)
exec sp_dboption MuOnline,autoshrink,on
Logautoshrinkoff
backup log MuOnlie with NO_LOG
backup log MuOnlie with TRUNCATE_ONLY
DBCC SHRINKDATABASE(MuOnlie)
BACKUP LOG MuOnline WITH NO_LOG
DBCC SHRINKDATABASE(MuOnline,10)
MuOnline MuOnline
sqlmuonlineranking
joinserverm14Gjoinserverlogjoinserverwrite log errorjoinserver700k
backup log MuOnline with NO_LOG
backup log MuOnline with TRUNCATE_ONLY
DBCC SHRINKDATABASE(MuOnline)
3MuOnline_log.LDFS
4->muonline
backup log MuOnline with NO_LOG
backup log MuOnline with TRUNCATE_ONLY
DBCC SHRINKDATABASE(MuOnline)
D:\Muserver\db .log
D:\Muserver\db .mdf
DUMP TRANSACTION
WITH NO_LOG
2.------------XXM,M,,
SQL SERVER
alter database
set recovery simple
Truncate log on checkpoint(SQL7.0SQL 2000)CHECKPOINT 70% True Auto shrink25%25% Truncate log on checkpoint True
--------------------------------------------------------------------------------
backup log MuOnline with NO_LOG
backup log MuOnline with TRUNCATE_ONLY
DBCC SHRINKDATABASE(MuOnline)
exec sp_dboption 'MuOnline','autoshrink','on'
Logautoshrinkoff
3MuOnline_log.LDFS
4-&muonline
教你如何清除SQL日志.rar
阅读(5176) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(1250)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_',
blogTitle:'领导者常常被一些事务性工作所纠缠',
blogAbstract:'领导者常常被一些事务性工作所纠缠，比如制作文件、查询资料、审阅公文等。这些事务性工作挤占了他的大部分时间和精力，势必使其无暇顾及本部门、本企业的管理和协调，导致企业陷入低效、混乱的局面。这种管理是一种低级的管理。 其实，是一个复杂的日常任务，领导者必须避免。
许多人之所以抓着不放，完全是其权力欲在作怪。他们特别强调所拥有的正式权威，往往会深陷其中而不能自拔。他们认为：这种权威不仅是某种名义上的象征，更多时候，自己必须履行许多职责。因此，他们是在一条毯子，所有 根本不去考虑组织的职能划分。下面这个案例中的主人公便是这样一个典型： 一个成功的老板究竟是那种长时间地工作、忘记去做午餐、连续不断的电话、最后一分钟预订车船机票的人，还是那些不慌不忙的人——
他能在有效的办公时间里做完应该做的工作。在这一点上，甚至你，我觉得这个复杂的问题呢？
你还用去思考究竟谁更符合时代的要求吗？',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:4,
publishTime:3,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}}