Cant shrink log file

Question

Post reply

Cant shrink log file

cute_lhen05

SSC Eights!

Points: 989
More actions
February 25, 2010 at 8:27 pm

#219449

Good day everyone,
i noticed that the log file is consuming too much space; From 8174 MB, it already consumed 6172 MB; i tried shrinking the database and it shows that its successful; however once i refresh the page it still giving me the same numbers;
how you could help me;
thanks in advance,

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Are you in full recovery mode?

If so, then you need to be backing up the log file on a regular basis in addition to your full backups.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cute_lhen05 SSC Eights! Points: 989 More actions · Answer 2

Its already in full recovery and the transaction log backup is already included in the database maintenanceplan;

thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

cute_lhen05 (2/25/2010)
Its already in full recovery and the transaction log backup is already included in the database maintenanceplan;
thanks

What is the frequency of your Tlog backup?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cute_lhen05 SSC Eights! Points: 989 More actions · Answer 4

the back up runs everyday;

is there anyway to minimize the size?

thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

If I am to understand you correctly, it appears that you only backup your transaction log once a day as part of your backup maintenance plan which also performs the full backup.

You must setup a separate plan for your tlogs. To keep growth to a minimum you should backup the t-logs at least hourly. You're transaction log does not appear to have a "lot" of activity and thus you could do with hourly. As soon as the t-log is backed up, your log will reclaim space from the transactions that were a part of the backup.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cute_lhen05 SSC Eights! Points: 989 More actions · Answer 6

we use the database often thats why we dont schedule it hourly;

is there another option? thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

cute_lhen05 (2/25/2010)
we use the database often thats why we dont schedule it hourly;
is there another option? thanks

A common practice is to backup the transaction log every 15 minutes. Understand that the transaction log is not a full database backup.

As an example, I backup transaction logs every 15 minutes. Those backups are 1.5GB in size which would be 6GB after an hour. You have 6Gb after an entire day. I don't think you will have a problem in backing up your log every hour.

Again, your transaction log backups should be a different maintenance plan than your full backup.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 8

To add to what Jason stated...

Not that I recommend it, but if you're only going to backup the log once a day, then there's almost no sense in having the database in the FULL recovery mode because you'll only be able to recover to the last day's backup anyway. It would be better to backup once an hour, at least. If the data is actually valuable (it always is...), then I'd recommend doing the T-Log backups every 15 minutes as Jason suggests.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

If we are backing up the TLog.

Will it reclaim the space ?

cant we use DBCC shrinkfile option to regain the space ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Bhuvnesh (2/26/2010)
If we are backing up the TLog.
Will it reclaim the space ?
cant we use DBCC shrinkfile option to regain the space ?

Reclaim the space within the file. Backing up the tlog does not shrink the file.

You could use shrinkfile to regain the space from disk, however you need to be careful about doing that as a regular practice. If the logfile is growing to 8GB and then you shrink it everyday to 1Gb just to see it grow again, you are helping to create more io and log fragmentation than necessary. It is best to create a log file size based on usage patterns and need. Doing this avoids shrinks and growths.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events