March 22, 2004 at 7:42 am
Hi
I have a transactional log that size is 7286.12mb and space used is 548.42mb. When i shrink log to 1000mb i get something like this:
Current size: 7286.12mb and space used: 623.53mb.
I think that current size should be 1000mb.
Why current size still 7286.12mb.
thanks
alex
March 22, 2004 at 7:44 am
Are you using EM? With wich option.
Try DBCC SHRINKFILE instead.
March 22, 2004 at 8:00 am
yes, i used EM, but the same think was with DBCC SHRINKFILE
alex
March 23, 2004 at 12:43 am
Take a look a Microsoft KB article:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Patrick SIMONS, MCP
March 23, 2004 at 5:06 am
Are you using 2000 or 7?
If 7 there is an issue with the Virtual Logs not breaking down that was never fixed. However the script here
http://www.sqlservercentral.com/scripts/contributions/26.asp
can set the condition so that each will finally clear. However I do suggest making a backup beforehand just in case I can't remember if it clears the transactions as well (think it does which is why I do a backup myself).
March 23, 2004 at 3:46 pm
First you can truncate the unused portion of the log by issuing:
backup log [databasename]
with TRUNCATE_ONLY
go
Then shrink the log file: get the fileid of the .ldf that you are trying to shrink by doing sp_helpdb [dbname]
dbcc shrinkfile(2, 500)
Note, if you have hourly transaction log dumps they will fail after the backup log with truncate_only option. Since this is a non-logged operation. If you do have hourly t-log try backing up your log to disk and then shrinking it.
Hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply