Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC SHRINKFILE print out issues Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 12:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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

Post #1436650
Posted Thursday, March 28, 2013 12:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 4,390, Visits: 9,536
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1436678
Posted Thursday, March 28, 2013 12:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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!



Thank you!!,

Angelindiego

Post #1436681
Posted Thursday, March 28, 2013 1:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 23,397, Visits: 32,237
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?



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)
Post #1436682
Posted Thursday, March 28, 2013 1:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 23,397, Visits: 32,237
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?



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)
Post #1436683
Posted Thursday, March 28, 2013 1:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 2008, MVP
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

Post #1436698
Posted Thursday, March 28, 2013 1:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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

Post #1436715
Posted Friday, March 29, 2013 2:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1436840
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse