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 12»»

Shrink DB and Log Expand / Collapse
Author
Message
Posted Monday, September 22, 2008 12:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:09 PM
Points: 2, Visits: 238
Comments posted to this topic are about the item Shrink DB and Log
Post #573265
Posted Tuesday, September 23, 2008 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 18, 2010 9:07 AM
Points: 1, Visits: 15
Can I shrink more then one at a time??
Post #574375
Posted Thursday, September 25, 2008 1:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
Hello,

Looks like this script will only shrink the database log file and not the database (.mdf file). Can I replace the database_log with the database file to shrink that too?

Thanks,

Rudy

PS. Man it shrinks very fast!!



Post #576369
Posted Sunday, November 16, 2008 10:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 6:18 AM
Points: 3, Visits: 9
hi all
i have SQL 2005 express and the database size is mor than 4G i tray to user the comand give me this
error
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@pmdb$primavera".

i write the command by this way

SET @pmdb$primavera = 'pmdb$primavera';

the name of the database is pmdb$primavera
plz advice me coa the work is down
Post #603354
Posted Wednesday, January 7, 2009 1:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:05 PM
Points: 386, Visits: 318
Do u have any script to shrink log files for Mirrored db its huge around 500GB without breaking mirroring .
Post #631831
Posted Thursday, July 30, 2009 4:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 30, 2009 4:46 AM
Points: 1, Visits: 1
hello All,

I run the following sintax
====================================
/*
Shrink a named transaction log file belonging to a database

Originally found at;

http://support.microsoft.com/support/kb/articles/q256/6/50.asp

Changes:
28.04.2004
Modified the inner loop so it tested the dx time so long overruns did not happen
Modified the inner loop so it had a fixed minimum quantity so there was no skip in skip out

29.03.2006
Modified the inner loop so it had a dynamic minimum quantity to allow faster shrinkage

24.05.2006
Modified the USE statement so it uses brackets around the dbname
Modified the @TruncLog variable so it uses brackets around the dbname

31.06.2006
Modified the code to use PRINT instead of SELECT in several cases
Modified the code to use @MaxCount instead of two unclear rules
Modified the code to use @Factor instead of several hard-coded values
Commented the use of @Factor
Moved the configuration and @Counter init code to before the start of the first loop to avoid repetition
Modified the code to display the process runtime in seconds rather than minutes

*/

SET NOCOUNT ON

DECLARE @LogicalFileName SYSNAME,
@MaxMinutes INT,
@NewSize INT,
@Factor FLOAT

/*
The process has several control parameters, most of the time you only need to worry about the first four
as these are the big controls whereas the fifth is simply a fine tuning control which rarely needs to
come into play.
*/

--This is the name of the database for which the log will be shrunk.
USE [cera]

--Use sp_helpfile to identify the logical file name that you want to shrink.
SET @LogicalFileName = 'cera_Log';
--Limit on time allowed to wrap log in minutes
SET @MaxMinutes = 5;
--Ideal size of logfile in MB
SET @NewSize =100;

/*
Factor determining maximum number of pages to pad out based on the original number of pages in use
(single page = 8K). Values in the range 1.0 - 0.8 seems to work well for many databases.

Increasing the number will increase the maximum number of pages allowed to be padded, which should
force larger amounts of data to be dropped before the process finishes. Often speeds up shrinking
very large databases which are going through the process before the timer runs out.

Decreasing the number will decrease the maximum number of pages allowed to be padded, which should
force less work to be done. Often aids with forcing smaller databases to shrink to minimum size
when larger values were actually expanding them.

*/
SET @Factor = 1.0;

/*
All code after this point is driven by these parameters and will not require editing unless you need to
fix a bug in the padding/shrinking process itself.
*/

-- Setup / initialize
DECLARE @OriginalSize INT,
@StringData VARCHAR(500)

SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName;

SELECT @StringData = 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName;

PRINT @StringData;
PRINT ''

--Drop the temporary table if it already exists
IF ( OBJECT_ID('[dbo].[DummyTrans]') IS NOT NULL )
DROP TABLE [DummyTrans]

CREATE TABLE [DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL );

-- Wrap log and truncate it.
DECLARE @Counter INT,
@MaxCount INT,
@StartTime DATETIME,
@TruncLog VARCHAR(500)

-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY';
EXEC (@TruncLog)

-- Configure limiter
IF @OriginalSize / @Factor > 50000
SET @MaxCount = 50000
ELSE
SET @MaxCount = @OriginalSize * @Factor

-- Attempt to shrink down the log file
PRINT 'Minimum Quantity : '+CAST( @MaxCount AS VARCHAR(10) )
PRINT 'Maximum Time : '+CAST( @MaxMinutes AS VARCHAR(10) )+' minutes ('+CAST( @MaxMinutes*60 AS VARCHAR(10) )+' seconds)'
PRINT ''

SET @Counter = 0;
SET @StartTime = GETDATE();

--loop the padding code to reduce the log while
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Outer loop.

--pad out the logfile a page at a time while
-- number of pages padded does not exceed our maximum page padding limit
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@Counter < @MaxCount) AND
(@MaxMinutes*60 > DATEDIFF(ss, @StartTime, GETDATE())) AND
(@OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)) AND
((@OriginalSize * 8 / 1024) > @NewSize)
)
BEGIN --Inner loop

INSERT INTO DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE FROM DummyTrans
SELECT @Counter = @Counter + 1

--Every 1,000 cycles tell the user what is going on
IF ROUND( @Counter , -3 ) = @Counter
BEGIN
PRINT 'Padded '+LTRIM( CAST( @Counter*8 AS VARCHAR(10) ) )+'K @ '+LTRIM( CAST( DATEDIFF( ss, @StartTime, GETDATE() ) AS VARCHAR(10) ) )+' seconds';
END
END

--See if a trunc of the log shrinks it.
EXEC( @TruncLog )

END
PRINT ''

SELECT @StringData = 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName;

PRINT @StringData
PRINT ''

DROP TABLE DummyTrans;
PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF


====================================
but i got following error

Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.

I am using sql server 2008
Post #762262
Posted Thursday, July 30, 2009 5:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 1:09 PM
Points: 2, Visits: 238
I wouldn't think that would be a problem, although I haven't tried it myself. as long as you back up the database before you try this it shouldn't be a problem and of course not in operating hours, it's got to be OOH or if you have a change control make sure the database isn't being used.
Post #762310
Posted Thursday, October 1, 2009 10:03 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 27, Visits: 475
Use this, to find all DBs both mdf and ldf, it generates shrink commands for you.
http://usa.redirectme.net/repriser/sqlserverpub.html


Jason
http://dbace.us
Post #796459
Posted Friday, October 2, 2009 3:12 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 27, Visits: 475
If I understand Microsoft correctly, when mirrored, the transaction log got written to the mirror directly from memory (setting asynch or synch), so whatever happens to primary will happen to mirror. Shrink is resource intense operation (imagine), so I don't know in reality what it will do depending on your environment. If you are on a 70Kb pipe with huge amount to shrink, it may not do well.

Jason
http://dbace.us
Post #797254
Posted Monday, May 6, 2013 6:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:48 AM
Points: 2,040, Visits: 1,667
I hate to be negative, but it's extremely unfortunate that this script truncates the log, breaking the log backup chain, and that isn't explained anywhere in the script or the comments. Even if it was explained, this resets all your disaster recovery options afterwards to starting with the full backup it advises you to take at the end. Previous backups cannot be used to recover past the log truncation.

Be extremely wary about using this script in production. There's a reason we (as I was on the SQL team at the time) removed the TRUNCATE_ONLY/NO_LOG options to BACKUP LOG in SQL Server 2008.

[Edit: there's now a comment added at the bottom of the intro about understanding the implications - it wasn't there earlier today when I wrote this comment.]

Thanks


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1449683
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse