Debug Truncate Log Script 1

[DrewConn]

I have the following script I use to shrink my trans log for various DB's. No problem before until I try it on a DB that has an embedded space in the DB name.

set NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
USE [CMSI DATA] -- This is the name of the database for which the log will be shrunk.
SELECT @LogicalFileName = 'CMSI DATA_LOG', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 500 -- in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
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)


-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
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) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
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
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF


I get this error on run:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATA'.

I think the error is from the SELECT @LogicalFileName = 'CMSI DATA_LOG' statement but can't figure out a fix. Tried 'CMSI' + &quot; &quot; + 'DATA_LOG' but got same error.

Any idea's?

 

[crystalized]

Now I have never done anything like what you are doing but could you try putting [] around the file name? I know that is what I do with other things like table names with embedded spaces. Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[DrewConn]

Tried that also. Get in invalid collum name error.

Thanks anyway.

 

[SQLBill]

according to BOL, names with spaces have to be enclosed with either brackets ([]) or double quotes (&quot; &quot. If the brackets didn't work...try the double quotes.

&quot;CMSI DATA_LOG&quot;

-SQLBill

 

[DrewConn]

Tried that. Same result as with the single quotes.

Thanks anyway.

 

[SQLBill]

I think I found the problem....I believe you need to change the select statement.

SELECT @LogicalFileName = 'CMSI DATA_LOG',

should be (I think)

SET @LogicalFileName = 'CMSI DATA_LOG',

-SQLBill

 

[DrewConn]

Thanks for the effort but still no go. Changed to set and added select on following line. (Error otherwise at comma.)

Get same error as before.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATA'.

Thanks again.

 

[SQLBill]

Try:

SET @MaxMinutes = 10
SET @NewSize = 500

SELECT @LogicalFileName = 'CMSI DATA_LOG', @MaxMinutes, @NewSize

I think the problem is that it is trying to retrieve information for @MaxMinutes and @NewSize, but there's no information to retrieve for them. So, you need to SET them and then retrieve the information.

-SQLBill

 

[DrewConn]

Sorry, no luck. Get A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations error.

This script works perfectly for my other DB's. The only difference is the space in the DB name.

Thanks.

 

[ccuster]

I think I found your problem. The problem resides with both the dbcc shrinkfile and the exec backup log lines.

I created a stored procedure that works and I changed the name with fileid, and used [] for the exec backup log line.

See if this works for you

use something like
exec sp_shrink_log 'CMSI DATA_LOG'

------------------- Begin Procedure-------------------
CREATE PROCEDURE SP_SHRINK_LOG

@LogicalFileName sysname = null,
@MaxMinutes int = 10,
@NewSize int = 500

AS

SET NOCOUNT ON

DECLARE @OriginalSize int, @FileId int

SELECT @FileID=fileid
from sysfiles
where name = @LogicalFileName

SELECT @OriginalSize = size
FROM sysfiles
WHERE fileid = @FileId

SELECT 'Original Size of ' + db_name() +' LOG is '+
CONVERT(Varchar(30),(@OriginalSize*8/1024))+'MB'
FROM sysfiles
where fileid = @FileId

CREATE TABLE DummyTrans
(DummyColumn char(8000) not null)

DECLARE @Counter int,
@StartTime DateTime,
@TruncLog VarChar(255)

SELECT @StartTime = GetDate(), @TruncLog='BACKUP LOG ['+db_name()+'] WITH TRUNCATE_ONLY'

DBCC SHRINKFILE(@FileId, @NewSize)
EXEC (@TruncLog)

WHILE @MaxMinutes > DATEDIFF(MI,@StartTime, Getdate())
AND @OriginalSize=(SELECT size From sysfiles Where fileid = @FileId)
AND @OriginalSize*8/1024 > @NewSize
BEGIN
SET @COUNTER=0
While ((@Counter<@OriginalSize/16) and (@Counter<50000))
BEGIN
Insert DummyTrans Values('Fill Log')
Delete DummyTrans
Set @Counter=@Counter+1
END

EXEC(@TruncLog)

END

SELECT 'Final Size of '+ db_name() + ' Log is '+
CONVERT(VARCHAR(30),SIZE*8/1024) +' MB'
FROM SYSFILES
WHERE fileid = @FileId

Drop Table DummyTrans

 

[DrewConn]

That did it. Should of thought to use fileid. Great approach.

Just saved me 13 Gig on that DB alone. Probably get another 20+ on my other DB's.

Thanks!!! :->>

 

[tlbroadbent]

A couple of minor changes should also fix the problem.

Add brackets around the database name.

@TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY'

Use the fileid rather than file name in the DBCC SHRINKFILE statement.

DECLARE @OriginalSize int, @fileid int
SELECT @OriginalSize = size, -- in 8K pages
@fileid = fileid
FROM sysfiles
WHERE name = @LogicalFileName
.
.
.

DBCC SHRINKFILE (@FileID, @NewSize)


We do not allow spaces in table of column names because of the potential problems.

An alternative fix would be to simply rename the logical file. Remove the space or replace the space with an underscore.

ALTER DATABASE [CMSI DATA]
MODIFY FILE
(NAME='CMSI DATA_LOG',
NEWNAME='CMSI_DATA_LOG') Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.