May 12, 2009 at 8:17 am
Having a problem with something that i don’t usually find a problem. I have been handed over the administration of a third party database. The MDF file is approx 4 GB but the log file is 40GB and taking up the whole log file drive. No problem I thought, I’ll shrink the log file.
But despite shrinking the log several times and taking backups of both the transaction log and full log file, then shrinking the logs again no space has been reclaimed. Can anyone help ?
May 12, 2009 at 8:21 am
Do you have configured database mirroring or replication on this database???
May 12, 2009 at 8:33 am
What's the value of log_reuse_wait_descr in sys.databases for this database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2009 at 8:38 am
No database mirroring or replication.
Values from the table are:
4ACTIVE_TRANSACTION
May 12, 2009 at 8:46 am
In this case... the only cause I can see is that you have a very long active transaction running...
May 12, 2009 at 10:59 am
What does DBCC OPENTRAN return?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2009 at 2:24 am
DBCC OPENTRANS Results:
Transaction information for database 'HCMDEV90'.
Oldest active transaction:
SPID (server process ID): 66
UID (user ID) : -1
Name : user_transaction
LSN : (92453:183829:1)
Start time : May 7 2009 6:16:28:477AM
SID : 0x2a11ed0d58c8354080d59bef94b01d66
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Transaction information for database 'HCMDEV90'.
May 13, 2009 at 3:45 am
Ok
Someone started a transaction on the 7th May and hasn't committed or rolled it back. You can either kill session 66, or find out who's running it (using sys.dm_exec_sessions) and get them to stop whatever they're doing.
After that transaction's been committed or rolled back you'll br able to back the log up and shrink it to a reasonable size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply