Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC SHRINKFILE print out issues


DBCC SHRINKFILE print out issues

Author
Message
Angelindiego
Angelindiego
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 441
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)


Thank you!!,

Angelindiego

Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4461 Visits: 9829
I 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...

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Angelindiego
Angelindiego
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 441
ok...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 <> 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! :-P


Thank you!!,

Angelindiego

Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
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)


Big question, why? What is the business case for this?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24171 Visits: 37936
As 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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47191 Visits: 44359
Before you go and harm your databases with this, please take a read through this - Managing Transaction Logs

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.


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


Angelindiego
Angelindiego
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 441
Thank 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!!


Thank you!!,

Angelindiego

durai nagarajan
durai nagarajan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1107 Visits: 2771
if 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.

Regards
Durai Nagarajan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search