Angelindiego (3/28/2013)

---

I want to run a script that builds out a message to execute shrinking log files. The issue that I am seeing is that when it is printed out in the message tab, it doesn't finish....it cuts off the last db print out (see below). The record count is correct (say there is 49 dbs in sys.databases, it counts correctly, but cuts off the print statement). Also, I ran it on another server and the record count should have been 53 in that instance and it printed out 3 total print statements.......WHAT THE HECK????? Not even consistant!

Can anyone see what I am missing here??????

Here is the script that prints (or executes) the command:

declare @SQL nvarchar(max)

declare @option bit

Set @option = 0 --(0 = print, 1 = execute @sql)

If @option = 1

BEGIN

SELECT @SQL = coalesce(@SQL + char(13) + char(10),'') + N'

Use ' + QUOTENAME(d.[name]) + ';' + CHAR(13) + '

ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY SIMPLE;

DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1);

ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY FULL;'

FROM sys.databases d

INNER JOIN sys.master_files mf ON [d].[database_id] = [mf].[database_id]

WHERE

d.[database_id] > 4 --no sys dbs

AND d.recovery_model = 1

AND d.is_read_only = 0

AND mf.[type] = 1 --log files

ORDER BY d.name

execute (@SQL)

END

ELSE

BEGIN

SELECT @SQL = coalesce(@SQL + char(13) + char(10),'') + N'

Use ' + QUOTENAME(d.[name]) + ';' + CHAR(13) + '

ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY SIMPLE;

DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1);

ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY FULL;'

FROM sys.databases d

INNER JOIN sys.master_files mf ON [d].[database_id] = [mf].[database_id]

WHERE

d.[database_id] > 4 --no sys dbs

AND d.recovery_model = 1

AND d.is_read_only = 0

AND mf.[type] = 1 --log files

ORDER BY d.name

print @SQL

END

here is an example of the cut off print statement:

......

Use [DB_10];

ALTER DATABASE [DB_10] SET RECOVERY SIMPLE;

DBCC SHRINKFILE ('DB_10_log', 1);

ALTER DATABASE [DB_10] SET RECOVERY FULL;

Use [DB_11];

ALTER DATABASE [DB_11] SET RECOVERY SIMPLE;

DBCC SHRINKF

(49 row(s) affected)