如何使用cursor ..来构造日志文件? [英] how to trucate logfile using cursor..?

问题描述

Am正在执行以下查询，但出现错误..

Am executing below query but getting error ..

DECLARE @DataBase VARCHAR(128)
DECLARE @SqlScript VARCHAR(MAX) 
DECLARE DataBaseList CURSOR FOR 
SELECT name FROM SYS.sysdatabases

WHERE NAME NOT IN ('master','tempdb','model','msdb')
-- step 2. insert all the database name and corresponding log files' names into the temp table
OPEN DataBaseList FETCH
NEXT FROM DataBaseList INTO @DataBase
WHILE @@FETCH_STATUS <> -1 
BEGIN

SET @SqlScript = 'USE [' + @DataBase + '] 
INSERT INTO #TransactionLogFiles(DatabaseName, LogFileName) 
SELECT '''+ @DataBase + ''', Name FROM sysfiles WHERE FileID=2'
--SELECT @SqlScript
EXEC(@SqlScript) 
FETCH NEXT FROM DataBaseList INTO @DataBase END

DEALLOCATE DataBaseList

-- step 3. go through the each row and execute the shrinkfile script against each database log file on the server
DECLARE TransactionLogList CURSOR FOR 
SELECT DatabaseName, LogFileName FROM #TransactionLogFiles 
DECLARE @LogFile VARCHAR(128) 

OPEN TransactionLogList FETCH
NEXT FROM TransactionLogList INTO @DataBase, @LogFile
WHILE @@FETCH_STATUS <> -1 
BEGIN 
SELECT @SqlScript = 'USE [' + @DataBase + '] '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY SIMPLE WITH NO_WAIT '
+ 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY FULL WITH NO_WAIT'
EXEC(@SqlScript) 

FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END
DEALLOCATE TransactionLogList
--SELECT * FROM #TransactionLogFiles

-- step 4. clean up
--DROP TABLE #TransactionLogFiles

错误如下所示

error is like in below

Msg 208, Level 16, State 0, Line 2
Invalid object name '#TransactionLogFiles'.

推荐答案

哈希(``#'')表示临时表.创建后它可能不会持久.

尝试以下方法:

The hash (''#'') denotes a temporary table. It may not persist after it''s creation.

Try this instead:

SET @SqlScript = 'USE [' + @DataBase + '] 
SELECT 
    '''+ @DataBase + ''' as DatabaseName, 
    Name as LogFileName
INTO #TransactionLogFiles(DatabaseName, LogFileName) 
 FROM sysfiles WHERE FileID=2'

如果该表尚不存在，则此语法将创建该表.

如果要标识列，则必须首先创建表.

在过程中创建和删除临时表始终是一个好习惯.这样，您将知道何时存在，何时不存在.

希望对您有所帮助^ _ ^
安迪

This syntax will create the table if it does not already exist.

If you want an identity column then you will have to create the table first.

It is always good practice to create and delete temporary tables from within the procedure. That way you will know when it exists and when it does not.

Hope that helps ^_^
Andy

这篇关于如何使用cursor ..来构造日志文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！