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 ««1234»»»

Transaction Log Backup Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Looks like we're cooking with gas now. I used the Maintenance Plan to schedule Log backups against the test DB every hour and looks like the backup files are being generated nicely although very small files. What do you think? Ready to move to production.


Post #1419706
Posted Wednesday, February 13, 2013 12:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 7,081, Visits: 12,573
That looks familiar

If you're happy I say send it to prod!


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1419710
Posted Thursday, February 14, 2013 12:52 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:29 AM
Points: 70, Visits: 61
Dont forget to go through a couple restore/recovery tests :)
Post #1419848
Posted Thursday, February 14, 2013 6:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Went live with the Trans log backup this morning. As you can see the first file is huge. I guess that because it's never been done before. Hopefully this will stop the log file from growing out of control. Now what I would like to do is reclaim some of that disk space (after a few more full DB backups).

Post #1420034
Posted Thursday, February 14, 2013 7:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 7,081, Visits: 12,573
That makes perfect sense. You can now use DBCC SHRINKFILE (once) to reclaim log space. The command can be blocked from shrinking the file for various reasons so post back if you have trouble.

Pick a sensible size for the log to shrink it to and adjust your autogrow settings too. Speaking of that, what are the autogrow settings? If it was a small increment, like 10MB, you may have tens of thousands of Virtual Log Files (VLFs). To get rid of those you may actually want to shrink your log to practically nothing and then regrow it manually to that sensible size to get rid of VLFs.

How many rows are returned when running this:

DBCC LOGINFO



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420038
Posted Thursday, February 14, 2013 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Autogrow settings 10% restricted to 2097152MB. DBCC LOGINFO returns 752 rows.
Post #1420075
Posted Thursday, February 14, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 7,081, Visits: 12,573
I wouldn't worry about 752 but I would definitely change from 10% to something in MB. I use 128MB by default. Then shrink your file once to a sensible size and monitor for new growth.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420097
Posted Thursday, February 14, 2013 9:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 9:10 AM
Points: 16, Visits: 21
Great info thanks. What would a sensible size be, 50 gig? I did notice a 'Shrink File' in the Maintenance Plan, is this what I use for the one time shrink? What happens if I try to shrink the file past what the size actually is? Sorry for all the questions, but, we're almost home.....
Post #1420169
Posted Thursday, February 14, 2013 10:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 7,081, Visits: 12,573
midnight251 (2/14/2013)
Great info thanks. What would a sensible size be, 50 gig?

It's mostly a guess. The only way to know for sure would be to capture the output of this query before you take a log backup for a while, maybe for a day or a week or even a month depending on your regular business cycle, and see how much space the log file actually has to accommodate before it is backed up and used space is freed.

USE YourDatabase;
SELECT name AS LogicalName,
CAST(size / 128. AS DECIMAL(34, 2)) AS Size,
CAST(FILEPROPERTY(name, 'SPACEUSED') / 128. AS DECIMAL(34, 2)) AS SpaceUsed
FROM sys.database_files
-- tran log file
WHERE type = 1;

I did notice a 'Shrink File' in the Maintenance Plan, is this what I use for the one time shrink?

No, just issue the T-SQL directly. I am not sure I even like that Microsoft put that Task into Maintenance Plans. It sends a bad signal.

What happens if I try to shrink the file past what the size actually is?

It will simply fail to shrink the file lower than what it needs. No real consequences.

edit: add USE to code sample


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1420179
Posted Thursday, February 14, 2013 10:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,860, Visits: 7,130
midnight251 (2/14/2013)
Great info thanks. What would a sensible size be, 50 gig? I did notice a 'Shrink File' in the Maintenance Plan, is this what I use for the one time shrink? What happens if I try to shrink the file past what the size actually is? Sorry for all the questions, but, we're almost home.....
A good rule to follow for sizing your transaction log file would be to find out what your largest clustered index size is for that particular instance, and size your file to that size (preferably a little more). That way when your indexing strategy kicks off, you hopefully won't have to grow your log file while your larger indexes are being rebuilt.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1420194
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse