Showing posts with label manually. Show all posts
Showing posts with label manually. Show all posts

Monday, March 19, 2012

Log File Full

My production database is very big in size and due to contious transactions, my log file becomes full frequently. I manually shrink log file when it becomes large. But i believe shrinking many times is not a good practise in terms of performance. So what should i do? Can anyone suggest a better approach.

How much frequency are you taking backup of transaction log ?

For example, if you are taking transaction log backup every 30 minutes, change it to every 15 minutes.

Thanks

Naras.

|||

Yes, you rightly said that shrinking is not a good practice. You should control the growth of Transaction log and if space is not there in the current drive, you should shift TL to a better place. You can control the growth by taking backup of transaction log more frequently. When you shrink the transaciton log the backup chain breaks. so the first thing you do should be full backup. If you dont want point in time restore you can change the recovery model to simple which will restirct the gwoth of TL to a certain limit.

How to stop the transaction log of a SQL Server database from growing unexpectedly

http://support.microsoft.com/kb/873235.

Madhu

|||I take trn log backup every 2 hours. As i want point in time restore, i need to take tl back up atleast 2 hr.|||But if i don't shrink it grows beyond 2 GB in week. I need a poin in time restore so i had to keep full recovery model.|||

what is the frequency of TL backup of this database.

Madhu

|||Every 2 hr TL Backup, daily Diff backup and weekly Full backup.|||

You can still achieve a point in time restore, even if you take transaction log backup every 30/60/90/120 minutes.

Thanks

Naras.

|||

schedule TL backup more frequently... say every 15 min... this is the better approach

Madhu

|||

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=263

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Addition to Madhu's references.

Friday, February 24, 2012

Log backup chain

Hi all, BOL states
Although the transaction log may be truncated manually, it is strongly
recommended that you do not do this, as it breaks the log backup chain. Until
a full database backup is created, the database is not protected from media
failure. Use manual log truncation only in very special circumstances, and
create a full database backup as soon as practical.
Case:
A daily full backup of a db occurs at 7 p.m. and tran log backup every
15min, and a job shrinks the log file (DBCC SHRINKFILE) daily at 6 a.m. What
should happen if I try to restore the database to a point of failure after 6
a.m. (say 11.45 a.m.)? Can I still continue restoring my tran log backups up
11:45 or everthing from 6 a.m onwards is lost?
I thought that truncating the log file manually would break the log backup
chain because inactive part of the log will be released. However, when I
tried to do so all seemed to be fine (no error messages). Surely, I
misunderstood something. Can anyone explain what "it breaks the log backup
chain" actually means?Hi,
If u studied the BOL then u should come to know that as u will do the
back up of the database transaction log automatically truncates and
flushes out the unused space.
When we execute any DML command sql writes it in transaction log and
issues a checkpoint and when we do backup the sql server clears all old
transaction records except the active portion of the transaction log.
So it will not harm u .u can do the point in time recovery.
U are doing transaction log backup every 15 min so suppose u had backup
at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
PM and finishes in few seconds.
So u have the backup till 7 PM and when u will run the truncate command
it will clear the logfile before 7 pm and u have the tlog backup after
7 pm.
So, now u have tlog back every 15 mins.In case u have a crash then u
have to apply the tlog after the restore of last backup back of the
database and then restore all the tlog after 7 PM.
Aslo study virtual logfile and truncate logfile from BOL
If u use the command with Tuncate_only it will shrink the logfile on
disk
No it will not break the chain because as u will do the backup of
database database issue a checkpoint and stores a entry in it self so
when u apply the tlog it will take the LSN after the backup.
Hope it helps u.
from
Killer.|||Thanks doller for your time. But what if dbcc shrinkfile was executed between
tlob backups. e.g
Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
I understand what after tran log backup, inactive part of the log gets
removed and we don't need to run the shrinkfile command. But I am looking at
the server that has this configs (?) . I have tried to restore the data,
wanting to see if it will cause any problems and it did not.
"doller" wrote:
> Hi,
> If u studied the BOL then u should come to know that as u will do the
> back up of the database transaction log automatically truncates and
> flushes out the unused space.
> When we execute any DML command sql writes it in transaction log and
> issues a checkpoint and when we do backup the sql server clears all old
> transaction records except the active portion of the transaction log.
> So it will not harm u .u can do the point in time recovery.
> U are doing transaction log backup every 15 min so suppose u had backup
> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> PM and finishes in few seconds.
> So u have the backup till 7 PM and when u will run the truncate command
> it will clear the logfile before 7 pm and u have the tlog backup after
> 7 pm.
> So, now u have tlog back every 15 mins.In case u have a crash then u
> have to apply the tlog after the restore of last backup back of the
> database and then restore all the tlog after 7 PM.
> Aslo study virtual logfile and truncate logfile from BOL
> If u use the command with Tuncate_only it will shrink the logfile on
> disk
> No it will not break the chain because as u will do the backup of
> database database issue a checkpoint and stores a entry in it self so
> when u apply the tlog it will take the LSN after the backup.
>
> Hope it helps u.
> from
> Killer.
>|||Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> Thanks doller for your time. But what if dbcc shrinkfile was executed between
> tlob backups. e.g
> Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> I understand what after tran log backup, inactive part of the log gets
> removed and we don't need to run the shrinkfile command. But I am looking at
> the server that has this configs (?) . I have tried to restore the data,
> wanting to see if it will cause any problems and it did not.
>
> "doller" wrote:
>> Hi,
>> If u studied the BOL then u should come to know that as u will do the
>> back up of the database transaction log automatically truncates and
>> flushes out the unused space.
>> When we execute any DML command sql writes it in transaction log and
>> issues a checkpoint and when we do backup the sql server clears all old
>> transaction records except the active portion of the transaction log.
>> So it will not harm u .u can do the point in time recovery.
>> U are doing transaction log backup every 15 min so suppose u had backup
>> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> PM and finishes in few seconds.
>> So u have the backup till 7 PM and when u will run the truncate command
>> it will clear the logfile before 7 pm and u have the tlog backup after
>> 7 pm.
>> So, now u have tlog back every 15 mins.In case u have a crash then u
>> have to apply the tlog after the restore of last backup back of the
>> database and then restore all the tlog after 7 PM.
>> Aslo study virtual logfile and truncate logfile from BOL
>> If u use the command with Tuncate_only it will shrink the logfile on
>> disk
>> No it will not break the chain because as u will do the backup of
>> database database issue a checkpoint and stores a entry in it self so
>> when u apply the tlog it will take the LSN after the backup.
>>
>> Hope it helps u.
>> from
>> Killer.
>>|||Thanx Tibor, I have visited your site many times before but did see this
article. I follow now. That's why somitimes when you do
"dbcc shrinkfile" and the size of the tlog is not reduced we need to run
"backup log xxx with truncate_only" and than dbcc shrinkfile.
Thanx again.
"Tibor Karaszi" wrote:
> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
> > tlob backups. e.g
> >
> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> >
> > I understand what after tran log backup, inactive part of the log gets
> > removed and we don't need to run the shrinkfile command. But I am looking at
> > the server that has this configs (?) . I have tried to restore the data,
> > wanting to see if it will cause any problems and it did not.
> >
> >
> > "doller" wrote:
> >
> >> Hi,
> >> If u studied the BOL then u should come to know that as u will do the
> >> back up of the database transaction log automatically truncates and
> >> flushes out the unused space.
> >> When we execute any DML command sql writes it in transaction log and
> >> issues a checkpoint and when we do backup the sql server clears all old
> >> transaction records except the active portion of the transaction log.
> >> So it will not harm u .u can do the point in time recovery.
> >> U are doing transaction log backup every 15 min so suppose u had backup
> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> >> PM and finishes in few seconds.
> >>
> >> So u have the backup till 7 PM and when u will run the truncate command
> >> it will clear the logfile before 7 pm and u have the tlog backup after
> >> 7 pm.
> >> So, now u have tlog back every 15 mins.In case u have a crash then u
> >> have to apply the tlog after the restore of last backup back of the
> >> database and then restore all the tlog after 7 PM.
> >>
> >> Aslo study virtual logfile and truncate logfile from BOL
> >>
> >> If u use the command with Tuncate_only it will shrink the logfile on
> >> disk
> >> No it will not break the chain because as u will do the backup of
> >> database database issue a checkpoint and stores a entry in it self so
> >> when u apply the tlog it will take the LSN after the backup.
> >>
> >>
> >>
> >> Hope it helps u.
> >> from
> >> Killer.
> >>
> >>
>|||> That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
Exactly. It's all in understanding how the virtual log files work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:362F54BB-0F65-404D-BE62-F35BCCB9249A@.microsoft.com...
> Thanx Tibor, I have visited your site many times before but did see this
> article. I follow now. That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
> Thanx again.
> "Tibor Karaszi" wrote:
>> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any
>> of
>> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit
>> on
>> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
>> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
>> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
>> > tlob backups. e.g
>> >
>> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
>> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
>> >
>> > I understand what after tran log backup, inactive part of the log gets
>> > removed and we don't need to run the shrinkfile command. But I am looking at
>> > the server that has this configs (?) . I have tried to restore the data,
>> > wanting to see if it will cause any problems and it did not.
>> >
>> >
>> > "doller" wrote:
>> >
>> >> Hi,
>> >> If u studied the BOL then u should come to know that as u will do the
>> >> back up of the database transaction log automatically truncates and
>> >> flushes out the unused space.
>> >> When we execute any DML command sql writes it in transaction log and
>> >> issues a checkpoint and when we do backup the sql server clears all old
>> >> transaction records except the active portion of the transaction log.
>> >> So it will not harm u .u can do the point in time recovery.
>> >> U are doing transaction log backup every 15 min so suppose u had backup
>> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> >> PM and finishes in few seconds.
>> >>
>> >> So u have the backup till 7 PM and when u will run the truncate command
>> >> it will clear the logfile before 7 pm and u have the tlog backup after
>> >> 7 pm.
>> >> So, now u have tlog back every 15 mins.In case u have a crash then u
>> >> have to apply the tlog after the restore of last backup back of the
>> >> database and then restore all the tlog after 7 PM.
>> >>
>> >> Aslo study virtual logfile and truncate logfile from BOL
>> >>
>> >> If u use the command with Tuncate_only it will shrink the logfile on
>> >> disk
>> >> No it will not break the chain because as u will do the backup of
>> >> database database issue a checkpoint and stores a entry in it self so
>> >> when u apply the tlog it will take the LSN after the backup.
>> >>
>> >>
>> >>
>> >> Hope it helps u.
>> >> from
>> >> Killer.
>> >>
>> >>
>>