July 25, 2011 at 1:12 am
We have log files for all the databases that we create.
When there is more No.of transaction means the log file get filled.
We need to clear the log file and then we need to perform the transaction.
Can't we dynamically increase the size or can't we clear the log file size
or can't we create other log file as the current log file gets filled.
kindly help me..
because I have faced many problems while deleting many records because of the log file size.
July 25, 2011 at 3:38 am
christhurajsacraj (7/25/2011)
We have log files for all the databases that we create.When there is more No.of transaction means the log file get filled.
We need to clear the log file and then we need to perform the transaction.
Can't we dynamically increase the size or can't we clear the log file size
Log files are grown dynamically and uses as much spaces as is required. Look up BOl for create database
or can't we create other log file as the current log file gets filled.
Log files are sequential and there is no gain having mutiple log files
kindly help me..
because I have faced many problems while deleting many records because of the log file size.
Use Bulk logged recovery model when performing Bulk operations to reduce the impact it has not tran log size.
Log file automatically shrink after a backup and release unwanted spaces back , If you still want to free space lookup "shrink file: in BOL , however keep in mind that there will be fragmentation.
July 25, 2011 at 4:01 am
It seems that you need to learn more about log management, recovery models and log backups. After you’ll understand those topics, you’ll be able to configure and manage the logs correctly. You can start by reading the fallowing articles:
http://msdn.microsoft.com/en-us/library/ms189275.aspx%5B/url%5D
http://www.mssqltips.com/tip.asp?tip=1497%5B/url%5D
http://www.simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups/%5B/url%5D
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2011 at 4:17 am
Jayanth_Kurup (7/25/2011)
Log file automatically shrink after a backup and release unwanted spaces back
No it does not. A log backup just marks 0 or more VLFs within the log file as reusable. It does not change the size of the log file in any way. The only thing that reduces the size of a file (data or log) is a shrink (database, file or autoshrink)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 4:22 am
christhurajsacraj (7/25/2011)
Can't we dynamically increase the size or can't we clear the log file sizeor can't we create other log file as the current log file gets filled.
First you need to identify why the log is full and fix that. This should help:
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
because I have faced many problems while deleting many records because of the log file size.
Large deletes can be a pain. The usual way is to delete in smaller chunks and do either a log backup or checkpoint between them in order to mark the log as reusable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 7:29 am
Hi
Here is what I use to reduce my logs but as Gale advised it would be good to find out why those are getting filled.
USE DatabaseName
DBCC SHRINKFILE('Database_log', 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE('Database_log', 1)
Repalce the DatabaseName with your DatabaseName and Database_log with your log file name.
Good luck
Imran
July 25, 2011 at 7:40 am
Imran Nadeem (7/25/2011)
DBCC SHRINKFILE('Database_log', 1)BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE('Database_log', 1)
Don't do that. Very, very, very bad log mismanagement. That's going to cause the log to grow again, it's going to cause log fragmentation (unless by some chance your log growth increments are sensible) and it's going to break the log chain and prevent point in time restores.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 8:33 am
GilaMonster (7/25/2011)
Imran Nadeem (7/25/2011)
Don't do that. Very, very, very bad log mismanagement. That's going to cause the log to grow again, it's going to cause log fragmentation (unless by some chance your log growth increments are sensible) and it's going to break the log chain and prevent point in time restores.
Hi Gale the link you have provided is not matching up with your statment.
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Read the statements under index reuse.
"The act of marking one or more VLFs as inactive is generally called log truncation. While this is a commonly used term, it’s a not a very accurate term. Truncate, according to the Oxford English dictionary means “shorten (something) by cutting off the top or the end”, however log truncation doesn’t shorten anything; it just makes the space available for reuse.
In addition, people often seem to think that truncate means to discard log records and break the log backup chain (probably from BACKUP LOG ... WITH TRUNCATE ONLY). It does not mean that."
Regards
Imran
July 25, 2011 at 9:50 am
Imran Nadeem (7/25/2011)
GilaMonster (7/25/2011)
Imran Nadeem (7/25/2011)
Don't do that. Very, very, very bad log mismanagement. That's going to cause the log to grow again, it's going to cause log fragmentation (unless by some chance your log growth increments are sensible) and it's going to break the log chain and prevent point in time restores.
Hi Gale the link you have provided is not matching up with your statment.
http://www.sqlservercentral.com/articles/Transaction+Log/72488/%5B/quote%5D
Err, do please note that I wrote the article you're saying doesn't match with my statement.
"The act of marking one or more VLFs as inactive is generally called log truncation. While this is a commonly used term, it’s a not a very accurate term. Truncate, according to the Oxford English dictionary means “shorten (something) by cutting off the top or the end”, however log truncation doesn’t shorten anything; it just makes the space available for reuse.
In addition, people often seem to think that truncate means to discard log records and break the log backup chain (probably from BACKUP LOG ... WITH TRUNCATE ONLY). It does not mean that."
And what in that contradicts what I said to you?
Your advice was to shrink the log, truncate the log without backing it up (BACKUP LOG DatabaseName WITH TRUNCATE_ONLY), then shrink it again.
The shrinks will just mean that the log grows again, probably fragmenting in the process. The BACKUP LOG WITH TRUNCATE_ONLY will break the log chain leaving you unable to do point in time restores until a full or diff backup is taken. No, the BACKUP LOG doesn't shrink the log, but the two DBCC ShrinkFiles in there sure will.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 10:20 am
Hi Gail
I know its written by you and that 's why I was looking for clarification.
You are advising here;
1. The BACKUP LOG WITH TRUNCATE_ONLY will break the log chain leaving you unable to do point in time restores until a full or diff backup is taken.
In the article you are saying ;
2.In addition, people often seem to think that truncate means to discard log records and break the log backup chain (probably from BACKUP LOG ... WITH TRUNCATE ONLY). It does not mean that.
Will be good if you can make it clear. Sorry for being thick it might be me not getting it.
Your point is valid that if you don't find the culprit and fix the problem; logs will grow back up again.I am not disagreeing with that at all.
I am still on learning curve ; not an expert at all.
Thanks much
Imran
July 25, 2011 at 10:31 am
Imran Nadeem (7/25/2011)
You are advising here;1. The BACKUP LOG WITH TRUNCATE_ONLY will break the log chain leaving you unable to do point in time restores until a full or diff backup is taken.
Yes, it does. Easily verifiable.
In the article you are saying ;
2.In addition, people often seem to think that truncate means to discard log records and break the log backup chain (probably from BACKUP LOG ... WITH TRUNCATE ONLY). It does not mean that.
Truncation is the act of marking zero or more VLFs as inactive. It occurs every time that a log backup occurs (full/bulk-logged recovery). Truncation doesn't break the log chain.
However, if you mark VLFs as inactive without backing them up, the log chain is broken because there are now missing log records. A log chain is an unbroken chain of all log records starting with a full or diff backup
BACKUP LOG WITH TRUNCATE_ONLY, as the option makes clear, ONLY truncates the log, it doesn't back it up. Hence now there are log records missing from the log chain. Hence that option breaks the log chain. Once you've run that you can no longer take log backups (they fail with an error saying there's no current database backup). Hence you can non longer do point in time recovery and you're opening yourself to data loss.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2011 at 10:51 am
GilaMonster (7/25/2011)[hr
Err, do please note that I wrote the article you're saying doesn't match with my statement.
Lol, now that's just too funny :w00t:!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply