Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gsgill76
Posting Yak Master
137 Posts |
 Posted - 2006-11-02 : 03:07:45
|
| Hi experts,I have a problem with Transactional Log file, using SQL Server 2005I haveMDF file size: 10 MB (app)LDF file size: 2.14 GB (app,I know it’s due to uncommented transaction, part of bad Front end programming, which I can’t change)When I issue, to try to reduce the size of .ldf fileDBCC SHRINKFILE (PMS_log,100)It reduces the size to 928MB(fine), also display the output as Cannot shrink log file 2 (PMS_log) because all logical log files are in use.(1 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.My questions,i)How can I reduce the size of this log to optimum / best, I mean as small as possible?ii)How can I, if I don’t want this log file, as I don’t need recovery?iii)How can I reduce the size, more? I mean other tips.Kind Regards,Thanks.Gurpreet S. Gill |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-11-02 : 04:26:50
|
If you aren't too concerned about recovery - then why not look at changing the recovery modle to simple - as I'm sure it is currently set to Full at the moment.Duane.  |
 |
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-11-02 : 05:00:44
|
| ditchyou are right, Recovery Model is 'full'.Now i had changed it to 'simple'. Let see what impact does it make.i still want the solution of my same questions.Kind Regards,Thanks.Gurpreet S. Gill |
|
|
|
madhuotp
Yak Posting Veteran
78 Posts |
Posted - 2006-11-02 : 06:19:26
|
| Hi Gill,You should truncate the log before shrink. It also observed that in DBCC SHRINKFILE (PMS_log,100) , you are specifying the log file to be 100 MB. it semes that the minimum size has to be more than 928MB. u just give it 1000 and try.Madhu |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
gsgill76
Posting Yak Master
137 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-11-02 : 08:02:04
|
| Nope - it's a work not in progress at the moment.It contains stuff about the log structure and viewing it - it got overtaken by v2005 and other things.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
gsgill76
Posting Yak Master
137 Posts |
Posted - 2006-11-03 : 01:32:38
|
| Thanks NR |
|
|
|
|
|
|
|
|
Transactional Log file Size Problem