August 10, 2009 at 1:49 am
Hi,
I have a database in production sqlserver 2005 with sp2 which is on simple recovery model. It is regularlly observed that the .ldf file which is on drive occupies full drive space. There is nothing on this drive expect .ldf file of the database.
The database is in simple recovery model. When i get this issue I run sp_who2 and I have always observed that in BlkBy column there is a entry by -2. I have no idea which is this spid and how it is coming. I cannot kill this as well.
I do the following steps to solve this problem.
1.I manaully issue checkpoint on this database.
2. I check logspace(DBCC SQLPERF ( LOGSPACE ) ) for the database and observed that logspace used % for database will be 97.19094 % or more.
3. I have checked open transactions and always found something not every time the same query but after confirming from the user I used to kill the spid.
4. I take log backup with truncate_only
5. I do shrinkfile .ldf file but it does not do it.
I have to put the db in single user mode and then shrink it.
Can somebody please help me in troubleshooting this problem.
Thanks a ton in advance
Hassan
August 10, 2009 at 2:40 am
To fix the spid -2 problems (which is an orphaned distributed transaction) run this
select request_owner_guid from sys.dm_tran_locks
where request_session_id = -2
Pass the guid the the query returns to the KILL command and those orphaned locks should go away.
If that doesn't fix the log problem, please check the following and post here.
The value of log_reuse_wait_descr in sys.databases
The output of DBCC OPENTRAN
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
August 10, 2009 at 4:03 am
Thanks a lot Sir for the reply.
After searching lot in web, I have come know to that this is a orphaned connection to the sqlserver database.
Just want to know how can I troubleshoot and fix this issue. If you can provide me any basic idea will be a great help.
Regards
Hassan
August 10, 2009 at 4:08 am
I just told you how to troubleshoot and fix orphaned connections. The first part of my post above that talks about sys.sm_tran_locks.
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
August 10, 2009 at 7:31 am
To resolve orphaned users search for sp_change_users_login in BOL.
Thanks
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply