February 25, 2010 at 8:27 pm
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,
February 25, 2010 at 10:03 pm
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
February 25, 2010 at 10:16 pm
Its already in full recovery and the transaction log backup is already included in the database maintenanceplan;
thanks
February 25, 2010 at 10:24 pm
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
February 25, 2010 at 10:53 pm
the back up runs everyday;
is there anyway to minimize the size?
thanks
February 25, 2010 at 10:58 pm
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
February 25, 2010 at 11:42 pm
we use the database often thats why we dont schedule it hourly;
is there another option? thanks
February 26, 2010 at 12:38 am
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
February 26, 2010 at 12:51 am
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
Change is inevitable... Change for the better is not.
February 26, 2010 at 5:21 am
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;-)
February 26, 2010 at 9:14 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply