Truncate Log Script

Question

Truncate Log Script

OnlyOneRJ

Hall of Fame

Points: 3894
More actions
April 29, 2013 at 5:12 am

#292758

Hi Buddies,
I got a Script for Truncate Logs...
Please SUGGEST how to make this runnable
***************************************************************
DECLARE c CURSOR FOR SELECT database_id, name, recovery_model_desc FROM sys.databases
DECLARE @dbname VARCHAR(1024);
DECLARE @rmod VARCHAR(1024);
DECLARE @id INT;
DECLARE @lfile VARCHAR(1024);
OPEN c;
FETCH NEXT FROM c INTO @id, @dbname, @rmod;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @rmod = ''FULL''
BEGIN
SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
PRINT @lfile
EXEC(''alter database ['' + @dbname + ''] set recovery simple '')
EXEC(''USE [''+@dbname+'']; DBCC SHRINKFILE([''+@lfile+''], 1)'')
EXEC(''ALTER DATABASE ['' + @dbname + ''] SET RECOVERY FULL '')
END ELSE
IF @rmod = ''SIMPLE''
BEGIN
SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
PRINT @lfile
EXEC(''USE [''+@dbname+'']; DBCC SHRINKFILE([''+@lfile+''], 1)'')
END
FETCH NEXT FROM c INTO @id, @dbname,@rmod;
END;
CLOSE c
DEALLOCATE c
GO
***************************************************************
EXECUTING THIS ERROR COMES---:w00t:
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'FULL'.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'alter'.
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near ''.
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'USE'.
Msg 911, Level 16, State 1, Line 19
Could not locate entry in sysdatabases for database '''+@dbname+'''. No entry found with that name. Make sure that the name is entered correctly.
************************************
Every Dog has a Tail !!!!! :-D

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You're doubling up your quotes. If you avoid doing that, it should solve your problem. If you're working in Management Studio, everything enclosed in quotes should be coloured red.

John

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 2

Gosh !!! thanks it worked.. will inform the user to use the updated script for his purpose 🙂

************************************
Every Dog has a Tail !!!!! :-D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I suggest the following for that script:

Ctrl-A, Del.

Then read over this for an idea on how to manage your transaction logs properly.

Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass