 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008templog.ldf has grown too large
Hello, I am running a couple of accounting applications on SQL server 2000 and have a problem with the templog.ldf file growing to 36GB in size (I'm almost out of disk space). What will be the best way to resolve this issue? Thanks, Eddie It's possible that you have an open transaction that is writing to tempdb. What happens when you run: use tempdb go DBCC OPENTRAN  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Hello, I am running a couple of accounting applications on SQL server 2000 and have a problem with the templog.ldf file growing to 36GB in size (I'm almost out of disk space). What will be the best way to resolve this issue? Thanks, Eddie I get this: Transaction information for database 'tempdb'. Oldest active transaction: SPID (server process ID) : 54 UID (user ID) : -1 Name : implicit_transaction LSN : (2338:4519:546) Start time : Apr 23 2008 7:26:12:187PM DBCC execution completed. If DBCC printed error messages, contact your system administrator. > It's possible that you have an open transaction that is writing to tempdb. > What happens when you run: [quoted text clipped - 10 lines] > > Eddie As I suspected, you have an open transaction. In this case, it is SPID 54 - and it has been running since April 23! You may want to kill this process. You should try and find out what it is supposed to be doing before killing it. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau I get this: Transaction information for database 'tempdb'. Oldest active transaction: SPID (server process ID) : 54 UID (user ID) : -1 Name : implicit_transaction LSN : (2338:4519:546) Start time : Apr 23 2008 7:26:12:187PM DBCC execution completed. If DBCC printed error messages, contact your system administrator. "Tom Moreau" wrote: > It's possible that you have an open transaction that is writing to tempdb. > What happens when you run: [quoted text clipped - 11 lines] > > Eddie Ok, now do DBCC INPUTBUFFER(54); What does that say? This thing has been running for 3 months + ? On 7/28/08 11:37 AM, in article 7187C4DE-893C-40CC-B316-3E480CFB6886@microsoft.com, "Eddie" > I get this: > [quoted text clipped - 23 lines] >> >> Eddie The following was returned. RPC Event 0 #jtds000002;1 Just so you know I'm a newbie when it comes to SQL > Ok, now do > [quoted text clipped - 32 lines] > >> > >> Eddie It's a little cryptic. It suggests that a temporary stored proc was created and then a remote procedure call was run against it. Unless that SPID is killed, your transaction log will continue to grow. Do you know who started the process? What happens when you run: sp_who 54 SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau The following was returned. RPC Event 0 #jtds000002;1 Just so you know I'm a newbie when it comes to SQL "Aaron Bertrand [SQL Server MVP]" wrote: > Ok, now do > [quoted text clipped - 39 lines] > >> > >> Eddie I get the following: 54 0 sleeping CMCO\hpsim CMCO1 0 vmm_db_v2 AWAITING COMMAND This is accociated with the HP systems insight manager. How can I kill this and recover the space back on my hard drive? > It's a little cryptic. It suggests that a temporary stored proc was created > and then a remote procedure call was run against it. [quoted text clipped - 55 lines] > > >> > > >> Eddie KILL 54; GO BACKUP LOG tempdb WITH TRUNCATE_ONLY; GO USE tempdb; GO DBCC SHRINKFILE(templog, 1); GO Instead of all this though, you could simply restart SQL Server; tempdb will be re-created from scratch. However, this is kind of like taking NyQuil for pneumonia; it is not going to fix the problem, only eliminate a symptom temporarily... whatever that program did to start bloating the size of tempdb, it is going to do it again. You need to find out why it is doing that in the first place. Have you talked to the vendor about it? On 7/28/08 12:45 PM, in article FDCE0324-C2A1-43D9-A44B-2DB9BA0703C6@microsoft.com, "Eddie" > I get the following: > 54 0 sleeping CMCO\hpsim CMCO1 [quoted text clipped - 63 lines] >>>>> >>>>> Eddie To add to Aaron's comments, I'd run DBCC OPENTRAN on a regular basis and if you see a SPID with a long-running transaction, you may have to kill it again. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau KILL 54; GO BACKUP LOG tempdb WITH TRUNCATE_ONLY; GO USE tempdb; GO DBCC SHRINKFILE(templog, 1); GO Instead of all this though, you could simply restart SQL Server; tempdb will be re-created from scratch. However, this is kind of like taking NyQuil for pneumonia; it is not going to fix the problem, only eliminate a symptom temporarily... whatever that program did to start bloating the size of tempdb, it is going to do it again. You need to find out why it is doing that in the first place. Have you talked to the vendor about it? On 7/28/08 12:45 PM, in article FDCE0324-C2A1-43D9-A44B-2DB9BA0703C6@microsoft.com, "Eddie" <Eddie@discussions.microsoft.com> wrote: > I get the following: > 54 0 sleeping CMCO\hpsim CMCO1 [quoted text clipped - 56 lines] >>>>> go >>>>> DBCC OPENTRAN I might have to reinstall the application that is causing this but for now I just want to kill this process and regain some disk space. Thanks to both of you for all your help. > KILL 54; > GO [quoted text clipped - 84 lines] > >>>>> > >>>>> Eddie Talk to the vendor and see if there is a service pack or hotfix or later version. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau I might have to reinstall the application that is causing this but for now I just want to kill this process and regain some disk space. Thanks to both of you for all your help. "Aaron Bertrand [SQL Server MVP]" wrote: > KILL 54; > GO [quoted text clipped - 81 lines] > >>>>> go > >>>>> DBCC OPENTRAN |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.