SQLServerCentral.com / Administering / SQL Server 2005 / DBCC SHRINKFILE print out issues / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 15:49:13 GMT20RE: DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspxif you dont want the log keep the DB in Simple recovery.why are you switching it to simple and full.is this simple but if you dont have full backup you may lose all the data in case of failure.please go through the comments posted by others and gails article is nice.Fri, 29 Mar 2013 02:24:53 GMTdurai nagarajanRE: DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspxThank you everyone for all the good advice. I would not do this on our dev or production servers as a rule. What I am doing is sending backups clear across the country and restoring on another box for testing. SIZE IS KILLING US. Only and only for this reason, am I doing this. It will be done on the test box only........so......with you all knowing I am not doing this as a habit, can you still offer me some advice to make it work?????Really, I do thank you for everything you shared!!Thu, 28 Mar 2013 13:56:39 GMTAngelindiegoRE: DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspxBefore you go and harm your databases with this, please take a read through this - [url=http://www.sqlservercentral.com/articles/64582/]Managing Transaction Logs[/url]I'm going to guess you have no log backups and hence the log grows large. If so, the fix isn't a temporary switch to simple recovery and a shrink, it's scheduling log backups.Thu, 28 Mar 2013 13:37:18 GMTGilaMonsterRE: DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspxAs Jeff indicated in his post, this is a bad idea. Changing from FULL recovery model to SIMPLE recovery model breaks your log chain. Changing back requires a full or differential backup before further log backups can be taken.Constanly shrinking the transaction log can result in fragmented log files.May I suggest reading the last article I reference below in my signature block regarding Managing Transaction Logs?Thu, 28 Mar 2013 13:09:48 GMTLynn PettisRE: DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspx[quote][b]Angelindiego (3/28/2013)[/b][hr]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 bitSet @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] &gt; 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)ENDELSEBEGINSELECT @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] &gt; 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 @SQLENDhere 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)[/quote]Big question, why? What is the business case for this?Thu, 28 Mar 2013 13:06:32 GMTLynn PettisRE: DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspxok...so I did more research and the issue seems to be the length of printing out @SQL being 8000/4000....whodathunkit.next...the count was off due to some of the dbs being recovery model &lt;&gt; 1.....whodathunkit.SO....maybe it is printing out as best it can. Is there an easy fix for this printing issue????????? Besides multiple print statements...which will break the script with a CR/LF break??????Who's with me here....HELP!!and thank you! :-PThu, 28 Mar 2013 12:57:18 GMTAngelindiegoRE: DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspxI am not going to review what you are doing wrong - because the whole idea is wrong.You do not want to setup a process to shrink log files on a regular basis, and therefore there is no reason to build a script to generate the statements.And finally, your method of shrinking the log files breaks the log chain and prevents further transaction log backups from occurring on those databases. It arbitrarily shrinks every log file to 1MB - which is going to force auto growth to kick in for every log file as the log needs to grow back to its normal operating size.I am just guessing here - but you probably also have the default auto growth settings which will either be 10% or 1MB, both of which are not ideal for any database.I would recommend that you not do this at all...Thu, 28 Mar 2013 12:54:34 GMTJeffrey Williams 3188DBCC SHRINKFILE print out issueshttp://www.sqlservercentral.com/Forums/Topic1436650-146-1.aspxI 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 bitSet @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] &gt; 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)ENDELSEBEGINSELECT @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] &gt; 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 @SQLENDhere 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)Thu, 28 Mar 2013 12:23:39 GMTAngelindiego