clearing transaction log
-
dear friends,
is it okay to clear transaction log in mssql server ?
if it's ok, how to clear transaction log ?
if not, what are the consequences if transaction log is cleared up ?
thanks in advance
-
>is it okay to clear transaction log in mssql server ?
Yes... unless the server crashed and you need to roll some data forward.
>if it's ok, how to clear transaction log ?
You could either do a regular backup or a backup of the log with TRUNCATE_ONLY. See Books OnLine (Backup, Described). Any uncommitted transactions will NOT be affected.
>if not, what are the consequences if transaction log is cleared up ?
If your server crashed and you need to recover uncommited transactions to a point in time (roll forward), you won't be able to. This is normally not the case, however.
You can find more info on how to control the content of the transaction log file in RECOVERY MODES in Books OnLine. Your instincts will guide you after that.
--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) -
thank you mr. jeff moden for your explanation.
i will try your suggestion.
-
The truncate_only option does remove the inactive part of the transaction log, however, if you do this on a production database you should immediately backup the database.
Peforming regular log backups will also remove the inactive part of the transaction log, but you will preserve the ability to perform point-in-time recoveries and recover in the event of a corrupted database, lost server or any other disaster. without suffering any data loss.
When you perform a Truncate_only you will only have the option of recovering to the last database backup, any updates to the database in between will be lost. This could be an option on development databases, but a lot of thought should be given before implementing this on a production database.
Eric
-
What do you mean by: If your server crashed and you need to recover uncommited transactions to a point in time (roll forward), you won't be able to. This is normally not the case, however
Uncommitted transactions?
If they are uncommitted they will roll back.
If they are committed but not written to disk (need for rolling forward the transaction [i.e reapplying the transaction]) before the server crashes, the transaction is still in the ACTIVE part of the transaction log. And you cannot truncate the active part of a transaction log.
//Hans
-
Yeah, I didn't say that quite right... BOL has a better way of saying what I so poorly said...
Each time an instance of SQL Server starts, it recovers each database, rolling back transactions that did not commit and rolling forward transactions that did commit but whose changes were not yet written to disk when an instance of SQL Server stopped.
--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) -
dear friends,
how if i shrink the transaction log ?
i already read the bol and i use manual shrinking instead of auto shrink because auto shrink not recommended by the bol.
according to your experience, is it ok to tick the auto shrink option in database properties ?
because every time i do manual shrinking, i can conserve space, but the size will grow bigger again and i have to check all the time.
if i choose auto shrink, on what condition sql server will do this auto shrink option ?
i ask this because it is related with the backup, because i set both database and transaction log backup and the backup size grows big very fast.
another question, is it ok to backup only the database and transaction log not included in db maintenance scenario ?
i really need your suggestion, thanks very much
arwan
-
Hi,
SHRINK is a very resource intensive task and should (if ever) be done during off peak hours.
I would not recommend auto shrink your databases. In fact if you need DB shrinking, you probably need more discs in your drive arrays instead
The AUTO_SHRINK periodically checks for unused space and shrinks your DB (more detailed then this I cannot be because I do not use AUTO SHRINK and never had the interest/time of getting deeper understanding of it.
What do you mean by:
another question, is it ok to backup only the database and transaction log not included in db maintenance scenario ?
//Hanslindgren
-
You will need to monitor the size of your database and transaction log. It should be an unusual situation when these are required to autogrow. You will want to schedule the growth of the database to occur at off peak hours and allocate larger areas to the database file. This limits the fragmentation that can occur when you allocate smaller areas (like when an autogrow is performed).
By monitoring the size and the frequency of performing transaction log backups you should be able to limit the times that this is required to autogrow.
Autogrow will get you through emergencies, but the database/log file size should be managed to the point where it is not necessary.
Eric
-
dear friends, thanks for your suggestions
if auto shrink not recommended, it means i should check the file growth every time right ( database and log ) ?
to mr Hanslindgren and the others, i mean : is it ok to backup only the database without the transaction log ?
i ask all these questions because i want to conserve space.
the database and transaction log use a large amount of space, and those files influence the space used in backup tape.
is there any more suggestion ? i need it, because right now the space on the hdd almost empty and the backup tape starts to reject to data copied to it because of lack of space.
thanks to you all
arwan
-
>>is it ok to backup only the database without the transaction log ?<<
The answer is ... it depends ... When you backup the database you are ensuring that you will be able to recover to that point in time. If you do not back up the transaction log then you are risking data loss, should a failure of some sort occur, until the next backup is peformed. If this is acceptable then set recovery to simple (no transaction log) and move on.
In most production environments this is not acceptable so then the transaction log comes in, this keeps track of the changes that have occured to the database so should a failure occur you can have point in time recovery. Now if you never did any backups of the transaction log it would grow quite large so it is necessary for space (and safety) to perform regular transaction log backups. The timing is up to you depending on your risk tolerance.
If you are having problems because performing regular db backups and log backups are causing you to run out of space then you need to take a look at the length of time that you keep the backup files and then to make sure you have enough disk space to support that.
>> i ask this because it is related with the backup, because i set both database and transaction log backup and the backup size grows big very fast.<<
I am not sure what you mean by this.
- Do you use the maintenance plan wizard to set up your db and log backups
- How long do you keep the db and log backup files
- Do you use backup sets
- If you peform the backups with your own scripts do you use the INIT option
Eric
-
Eric Mueller has some valid points there and the only thing I would add would be that it might be time to check for some third party backup solution (like LiteSpeed or RedGates' SQL Backup) that both does massive compression. It is not unusual to see that a full backup takes less then 10% of the used size in your databases (with that I am not counting free space in data files as used space).
Though these kinds of solutions cost a little money it could be money very well spent!
And on a side-note. I would suggest buying more disc drives for your array(s)
//Hanslindgren
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply