| Author |
Topic |
|
dr223
Constraint Violating Yak Guru
444 Posts |
 Posted - 2011-03-07 : 10:03:20
|
| Hi, I have a database in production and has been in use for 4 years. I noticed today that its trasanction log file is 1.54 GB while the data file is 194 MB. This caused an alarm to me as the transaction log file is nearly 8 times the size of the data file. Please could someone advice me on;a) Reason for the large log fileb) What should I do?Thanks in advance Regards |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-03-07 : 13:25:07
|
| What is the recovery model of the database ? |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-03-07 : 16:05:30
|
| If the recovery model of the database is set to full - how often are you backing up the transaction log? This is not the same as backing up the database. You need another job that runs frequently (usually every 15 minutes) to backup the transaction log.Jeff |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2011-03-08 : 05:12:40
|
| Yes, the recover model is Full and there is no back up of the transaction log file mechanism implemented.. I never created this database, I have been given the task to investigate why the transaction log file is huge and secondly to put down procedures to control its growth...Note, there is scheduled backup of the entire database done everyday at 2200 hrs.Any help please |
|
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-03-08 : 06:17:34
|
| Have a look at:http://support.microsoft.com/kb/907511 |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-08 : 06:20:05
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2011-03-08 : 09:56:50
|
| Ok, thanks for all your advices.. Tried this; Database - Tasks - Shrink - Files - File typ - Log Currently allocated space: 1584.88 MB Available free space: 1563.74 MB (98%)Shrink actionChecked - Release unused space the log file was reduced to 7 MB. So I manually reduced the transaction log file to 7 MB.. Good!My question is that will this have any IMPACT to the entire database inclusive the primary data file.. Are there any drawbacks?Was I doing the right thing? any suggestions will indeed be highly appreciated..many thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-03-08 : 10:11:31
|
If you don't need the tranlog to recover your database to a point in time when a crash has happended then change the recovery model to simple - that's all.Otherwise you have to do logbackups for example every 15 minutes...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2011-03-08 : 10:22:13
|
| thanks webfred - I want to still maintain FULL backups, and I dont want to manually reduce the transaction log file, how can I set a transaction log backup - need the procedure I should follow to set the transaction log file -- any handy article .. Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2011-03-08 : 10:59:01
|
| Ok, thanks Procedure I have done in #7 will cause any performance issues to the application?.. When the log file is shrunk to 7 MB?Secondly, I have set a regular backup of the database at 2000 hrs everyday, (saving the mdf and log files) if the database crash can I restore to the most recent database? Thanks |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-08 : 13:33:46
|
| Sure you can restore, but is losing up to 24 hours of data acceptable to the users?--Gail ShawSQL Server MVP |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2011-03-10 : 06:02:03
|
| yeah thats fine....as they are mostly storing in house applications which do not have data directly from our customers.What about shrinking the transaction log file to 7MB releasing the avaialable free space (refer to (#7) and performance issues to be encountered?Thanks |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-10 : 07:01:04
|
| Are they absolutely certain that losing up to 24 hours of data in a crash is acceptable?If so, switch the database to simple recovery model and shrink the log to a reasonable size, maybe 40MB or so.Otherwise, set up log backups and then shrink the log to around the same size See: [url]http://www.sqlservercentral.com/articles/64582/[/url] (if you haven't read it already)--Gail ShawSQL Server MVP |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2011-03-10 : 08:11:02
|
| When I manually try to shrink the log file, it shows that I have Available free space (98%), if I ok it and shrink 98% - my question is will this have any performance issues.. thank you |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-10 : 13:00:53
|
| Yes, because the next thing that will happen is that the log will grow. Shrink it to a reasonable size, not to the smallest it can be.--Gail ShawSQL Server MVP |
|
|
|
dr223
Constraint Violating Yak Guru
444 Posts |
Posted - 2011-03-11 : 05:20:16
|
| How to shrink it to a reasonable size, please .. The procedure I am using is - Tasks - Shrink - Select File type: Log- Shrick action: Reorganize pages before releasing unused space; Shrink file to: 60 MB- OKIs this the right way of doing it? Many thanks |
|
|
|
|
Transaction Log file