March 28, 2013 at 12:23 pm
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)
March 28, 2013 at 12:54 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 28, 2013 at 12:57 pm
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! 😛
March 28, 2013 at 1:06 pm
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?
March 28, 2013 at 1:09 pm
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?
March 28, 2013 at 1:37 pm
Before you go and harm your databases with this, please take a read through this - 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.
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
March 28, 2013 at 1:56 pm
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!!
March 29, 2013 at 2:24 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply