How do I make truncate log script as dynamic SQL in SQL Server?

Question

I am using the below script to truncate the log files of a database. But I have to execute the below script results in separate window due to this I am not able to schedule the jobs .

SET NOCOUNT ON 

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

Expected O/P is script has to run instead of copy pasting the above query results

An alternative to dynamic SQL is to use SQL Server's Maintenance Plans. Each plan is an SSIS package executed by the SQL Agent, on a fixed scheduled. Here is a link to the MS Docs. — David Rushton
– David Rushton, Commented Aug 5, 2016 at 16:09
If you can't use maintenance plans...Are you looking for something as simple as sp_executesql? You would use this in conjunction with a cursor. — SMM
– SMM, Commented Aug 5, 2016 at 17:59

Attie Wagner · Accepted Answer · 2020-02-18 09:31:27Z

2

You can achieve this with Dynamic SQL, without using a cursor:

SET NOCOUNT ON 

DECLARE @SQL VARCHAR(MAX) = ''

SELECT @SQL = @SQL +
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY);' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

PRINT   (@SQL)
EXEC    (@SQL)

Does the same thing...

answered Feb 18, 2020 at 9:31

Attie Wagner

1,36016 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ranjana Ghimire · Accepted Answer · 2016-08-08 04:44:44Z

1

SET NOCOUNT ON

DECLARE @LOG TABLE (
  QUERY varchar(max)
)
DECLARE @QUERY varchar(max)

INSERT INTO @LOG
  SELECT
    'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
  FROM sys.master_files mf
  JOIN sys.databases d
    ON mf.database_id = d.database_id
  WHERE d.database_id > 4
  AND mf.type_desc = 'LOG'


DECLARE C CURSOR FOR
SELECT
  QUERY
FROM @LOG
OPEN C
FETCH NEXT FROM C INTO @QUERY
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @QUERY
  EXEC (@QUERY)
  FETCH NEXT FROM C INTO @QUERY
END
CLOSE C
DEALLOCATE C

answered Aug 8, 2016 at 4:44

Ranjana Ghimire

1,8451 gold badge14 silver badges22 bronze badges

3 Comments

SNR Over a year ago

Hi , I am not able vote but Ranjana Ghimire script worked for me. Thanks Ranjana.

Ranjana Ghimire Over a year ago

You are welcome. no problem about voting. I am glad that i could help you.

Hadi Over a year ago

please mark this question as answered (without voting) to help people when they search no this topic

Collectives™ on Stack Overflow

How do I make truncate log script as dynamic SQL in SQL Server?

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related