代码如下,一开始很正常,但是后面就不起作用了。
if object_id('tempdb..#LogTab') is NOT NULL
begin
truncate table #LogTab
end
else
begin
CREATE TABLE #LogTab(DatabaseName varchar(80),LogSizeMB float,LogSpaceUsed float,Status int)
end
GO
--EXEC sp_configure 'Ole Automation Procedures',1
--RECONFIGURE
-- 获取数据
DECLARE @err int,@src varchar(255),@desc varchar(255)
DECLARE @obj int,@str varchar(1000) SET @str='Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=master;Data Source=125.64.95.34\SMSBDB,1433;User ID=sa;Password=abcdefgsss'
-- 创建对象
EXEC @err=sp_oacreate 'adodb.recordset',@obj OUT
IF @err<>0 GOTO lberr
-- 获取需要的数据
EXEC @err=sp_oamethod @obj,'open',null,'dbcc sqlperf(logspace)',@str
IF @err<>0 GOTO lberr INSERT #LogTab EXEC @err=sp_oamethod @obj,'getrows'
IF @err<>0 GOTO lberr
EXEC @err=sp_oadestroy @obj
RETURN
lberr:
EXEC sp_oageterrorinfo 0,@src OUT,@desc OUT
SELECT CAST(@err as varbinary(4)) as 错误号,
@src as 错误源,@desc as 错误描述
--EXEC sp_configure 'Ole Automation Procedures',0
GO
---------------------------------------------------------------------------
if object_id('tempdb..#dbtb') is NOT NULL
begin
drop table #dbtb
endGO
select b.name as databasename,a.name as logfilename into #dbtb from (
select database_id,name from sys.master_files
where type_desc='Log')a
inner join sys.databases b
on a.database_id=b.database_id
where b.name in
(
SELECT DatabaseName FROM #LogTab
where logspaceused>80
) and b.name not in
(
'master','model','msdb','tempdb'
)
GO
--------------------------------------------------------------------------
DECLARE @err int
declare @dbname varchar(80)
declare @logfilename varchar(100)
declare @nsql varchar(7000)
declare @sql varchar(200)
DECLARE tnames_cursor CURSOR
FOR
SELECT * FROM #dbtb
OPEN tnames_cursor
FETCH next from tnames_cursor into @dbname,@logfilename
while @@fetch_status=0
begin
BEGIN TRY
begin tran
set @nsql='use '+@dbname+' alter database '+@dbname+' set recovery simple BACKUP LOG ' + @dbname + ' WITH TRUNCATE_ONLY dbcc shrinkfile('''+@logfilename+''',1)'
exec (@nsql)
set @sql='alter database '+@dbname+' set recovery full'
exec (@sql)
commit
END TRY
BEGIN CATCH
rollback tran
END CATCH
FETCH next from tnames_cursor into @dbname,@logfilename
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
drop table #LogTab,#dbtb
if object_id('tempdb..#LogTab') is NOT NULL
begin
truncate table #LogTab
end
else
begin
CREATE TABLE #LogTab(DatabaseName varchar(80),LogSizeMB float,LogSpaceUsed float,Status int)
end
GO
--EXEC sp_configure 'Ole Automation Procedures',1
--RECONFIGURE
-- 获取数据
DECLARE @err int,@src varchar(255),@desc varchar(255)
DECLARE @obj int,@str varchar(1000) SET @str='Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=master;Data Source=125.64.95.34\SMSBDB,1433;User ID=sa;Password=abcdefgsss'
-- 创建对象
EXEC @err=sp_oacreate 'adodb.recordset',@obj OUT
IF @err<>0 GOTO lberr
-- 获取需要的数据
EXEC @err=sp_oamethod @obj,'open',null,'dbcc sqlperf(logspace)',@str
IF @err<>0 GOTO lberr INSERT #LogTab EXEC @err=sp_oamethod @obj,'getrows'
IF @err<>0 GOTO lberr
EXEC @err=sp_oadestroy @obj
RETURN
lberr:
EXEC sp_oageterrorinfo 0,@src OUT,@desc OUT
SELECT CAST(@err as varbinary(4)) as 错误号,
@src as 错误源,@desc as 错误描述
--EXEC sp_configure 'Ole Automation Procedures',0
GO
---------------------------------------------------------------------------
if object_id('tempdb..#dbtb') is NOT NULL
begin
drop table #dbtb
endGO
select b.name as databasename,a.name as logfilename into #dbtb from (
select database_id,name from sys.master_files
where type_desc='Log')a
inner join sys.databases b
on a.database_id=b.database_id
where b.name in
(
SELECT DatabaseName FROM #LogTab
where logspaceused>80
) and b.name not in
(
'master','model','msdb','tempdb'
)
GO
--------------------------------------------------------------------------
DECLARE @err int
declare @dbname varchar(80)
declare @logfilename varchar(100)
declare @nsql varchar(7000)
declare @sql varchar(200)
DECLARE tnames_cursor CURSOR
FOR
SELECT * FROM #dbtb
OPEN tnames_cursor
FETCH next from tnames_cursor into @dbname,@logfilename
while @@fetch_status=0
begin
BEGIN TRY
begin tran
set @nsql='use '+@dbname+' alter database '+@dbname+' set recovery simple BACKUP LOG ' + @dbname + ' WITH TRUNCATE_ONLY dbcc shrinkfile('''+@logfilename+''',1)'
exec (@nsql)
set @sql='alter database '+@dbname+' set recovery full'
exec (@sql)
commit
END TRY
BEGIN CATCH
rollback tran
END CATCH
FETCH next from tnames_cursor into @dbname,@logfilename
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
drop table #LogTab,#dbtb
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货