March 4, 2008 at 4:38 am
Hello,
I run dbcc sqlperf(logspace) and my database as the log as you can see:
Database NameLog Size(MB)Log Space Used (%)Status
MyDatabase 20832,55 90,91605 0
After i run the following commands:
backup log MyDatabase with truncate_only
go
dbcc shrinkfile (MyDatabase_LOG,5000) -- @FILENAME ,@MB
go
but the log file still appears with like this:
Database NameLog Size(MB)Log Space Used (%)Status
MyDatabase 19176,05 99,38473 0
Can you tell me what should i do to shrink the transaction log without impact and can you explain me what should i must always do to shrink logs.
Thanks and regards,
JMSM 😉
March 4, 2008 at 4:49 am
The first question, what recovery mode are you running in? I sort of hope that you are in SIMPLE. (if not, then I assume that you are trying to reduce the log file because it has grown too large, and you are not expecting to grow to this size again (note, when you truncate the log, like you do, you break the log chain) :))
Are you using replication? If not, you most likely have a transaction that is still running. You are using most of the log space in your transaction log, so most of it is active, therefore it cannot be shrunk. The reasons for the active part of the transaction log being active is on http://msdn2.microsoft.com/en-us/library/ms345414.aspx
Regards,
Andras
March 4, 2008 at 5:08 am
The database is in Simple Recovery Model.
How can i put in 'pause' any transactions that are running to shrink the log without impact with database performance? Is this possible?
I've done this operation a lot of times but now i don't know what to do.
I execute the dbcc opentran('MyDatabase') and in the output appears one user transaction that started in the following date: Start time : Nov 13 2007 5:54:41:733PM, then i execute dbcc inputbuffer(200), and in the result appears 'set implicit_transactions on' :blink:
Transaction information for database 'MyDatabase'.
Oldest active transaction:
SPID (server process ID): 200
UID (user ID) : -1
Name : user_transaction
LSN : (488:31647:1)
Start time : Nov 13 2007 5:54:41:733PM
SID : 0x010500000000000515000000896f7898e87b12646f6c242494010300
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I need to know exacly what should i do to shrink the log file with the minimum of impact in database performance.
Once more, thanks for your help.
Regards,
JMSM 😉
March 4, 2008 at 5:21 am
Check who it is who is responsible for this transaction
sp_who2 200
If you think that this transaction was a mistake, then kill the 200 process (this will roll back that transaction), and you should be able to shrink the log file.
Regards,
Andras
March 4, 2008 at 8:56 am
Truncating the log file and then shrinking it should work. Also, any file cannot be shrinked smaller than its initially specified size (during creation). If the initially specified file size is too big, then you may have to alter the database, to reduce its size.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply