| Author |
Topic |
|
SQLboom
Yak Posting Veteran
63 Posts |
 Posted - 2003-12-15 : 06:25:31
|
| My Transaction Log Space shows ...Log Size (MB) Log Space Used (%)-------------------------------------1082.8047 10.06857I beleive that this implies that i should be able to truncate 90% of the Log Size space. I took the back of the database and performed a shrink on the database. But it still shows the same result as above. If someone can tell the reason and solution for this. Retrieving over 900MB of space for the system would be a great bonus for my system. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-15 : 07:17:59
|
| A full backup does not release log entries.Either set the database to simple recovery mode or backup the log.If this doesn't work you can (backup the database first) detach the database (sp_detach_db), delete the ldf file the attach it again (sp_attach_single_file_db) - this will create a minimal size log file.==========================================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. |
 |
|
|
SQLboom
Yak Posting Veteran
63 Posts |
Posted - 2003-12-15 : 09:19:36
|
| does this mean that once the log file goes to certain limit, there's no way to get it back to original empty (minimum) size. ...be it taking full backup + backup for transaction log + shrinking only way out is detaching and then attaching databases. . . |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-12-15 : 11:05:22
|
| No - if you backup the log then shrink it it should shrink to the original size.The detach/attach is only needed if there are problems with that e.g. open transactions, used transaction at end of file, ...==========================================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. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-12-15 : 16:29:22
|
| Please, If you are going to attempt the second method (Which I wouldn't recommend) just rename the log file, don't delete it.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-15 : 17:42:08
|
Hehehe the weary voice of experience from the PSS trenches  Damian |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-12-16 : 01:14:27
|
quote:
Originally posted by chadmat
Please, If you are going to attempt the second method (Which I wouldn't recommend) just rename the log file, don't delete it.
Well, there is no going back to the old logfile even if you did rename it. Unless you performed an offline backup. My question is why bother shrinking this file at all. Who cares about a mere 900MB. Plus, the logfile grew to 1GB in size for a reason, it will most likely do that again in the future. You are just going to increase the external fragmentation of the logfile, especially if this system is running low on space already and the available freespace is heavily fragmented.-ec |
|
|
|
SQLboom
Yak Posting Veteran
63 Posts |
Posted - 2003-12-16 : 02:50:23
|
| it' boiling down to the point that my log is having some open transactions or say some active transactions at the end although i have had no tasks running for sometime now. .. . .how can i detect them or even remove without detection knowing well that the option to delete (rename) the log still remains in hand.. . . |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-12-16 : 03:01:44
|
| >>it' boiling down to the point that my log is having some open transactions or say some active transactions at the end although i have had no tasks running for sometime nowWhich seems to be the problem. The active portion of the log is always at the end, and it will not move to be beginning (say after a DBCC SHRINKFILE command), until there are newer entries. The simple way is to pad the transaction log with dummy entries, like doing 1000 inserts into a temp table. Most importantly, keep backing up the transaction log regularly.I agree with what eyechart says, dont shrink the file unless you have a database that you won't be using a lot anymore. If your transaction log needs to grow later, it'll hold up running queries while it is being grown.OwaisWe make a living out of what we get, but we make a life out of what we give. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-12-16 : 12:48:48
|
quote:
Originally posted by mohdowaisThe active portion of the log is always at the end, and it will not move to be beginning (say after a DBCC SHRINKFILE command), until there are newer entries. The simple way is to pad the transaction log with dummy entries, like doing 1000 inserts into a temp table....
That was the case with 7.0. You often had to do dummy transactions to move the active portion to the front. However, I have never had to do it in 2000, it is supposed to do it for you. quote:
Hehehe the weary voice of experience from the PSS trenches
Exactly. I don't understand why people would delete a file like that unless the DB is reattached, and working properly. But to just trust that attach single file is going to work, and delete your log is risky. It will probaby work 9 out of 10 times, but eventually it won't.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
|
Shrinking Transaction Log