db and t-log size and growth--help!

Question

Post reply

db and t-log size and growth--help!

nmargol

SSC Enthusiast

Points: 134
More actions
July 1, 2005 at 2:07 pm

#186446

i've just started tracking my db and log file sizes and noticed (in taskpad view, in the allocated space section) that the msdb dbs on both servers (1 production, 1 development) have no space left, and the production model db has no space either.

several other dbs on our production server have disproportionately allocated t-log spaces (for example, one db has 15495 MB used, 11083MB free but its t-log has 414MB used and 36829MB free).

questions: if there is a big chunk of unused space in the space allocated t-log, should i shrink the file? would that bring the allocated size down or the actual space being used? is that a normal proportion or are our db t-log allocated space sizes (or maximum size) simply set too high? and what to do about growing the msdb or model db? (i'm new to the company and trying to get my dba bearings at the same time)

also, how does that space actually get allocated? is it initially assigned and then fluctuates with db and log growth? i want to be sure i'm looking at the right figures for tracking and planning db sizes, as well as keeping an eye on when to shrink or grow dbs.

thanks in advance for helping out a beginner!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

The size of your T-Log is based on your backup Recovery Model.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp

Transaction logs grow alot specifically when in Full Recovery mode when there is alot of activity, and no Log backups.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp

You do want to Shrink the log to a more manageable size especially for backup and restore purposes. Set up incremental backups or Transaction log backups throughout the day, and it will keep the log at a more manageable size

nmargol SSC Enthusiast Points: 134 More actions · Answer 2

Thanks for pointing me in the right direction. I already have transaction log backups running hourly during the work day; is there some setting aside from auto-shrink (which I'd prefer not to turn on) that I need to include to ensure that the transaction log shrinks appropriately?

Also, is there any reason (aside from just not knowing) why anyone would create the dbs and t-logs with such disproportional allocated space? I've been hesitant to go in and start change sizes because I thought there must be rhyme or reason to the original set-up, but now I'm wondering if they were just not well designed.

Thanks for your help!

Martin Mojzisek SSCrazy Points: 2187 More actions · Answer 3

Actually, you shouldn't shrink transaction logs at all. Shrinking has considerable overhead, which can be avoided and can lead to fragmentation of database files on file system. The best way is to take some time to determine sufficient size of log(s) and perform regular tr.log backups, thus the log files shouldn't grow out of control.

Disproportional log sizes can be the remnants of full recovery model and not performing log backups as Ray pointed out or of some large operations in db.

You can use dbcc sqlperf(logspace) command or Performance Monitor (counters under SQL Serveratabases) to track usage of transaction log(s) and make your decisions regarding ideal size.

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 4

If the used space is NEVER getting anywhere close to the total size of the log files anymore, then you might want to consider using ALTER DATABASE to alter the file size. The option is listed in Books Online. It's pretty straightforward. If you are getting close to the maximum size, you need to leave it alone, so you don't get automatic file growths or run out of room.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.