 |
 | 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 2008tempdb.dbo.##lockinfo60
I am running SQL2000. I received a notice that my drive W: was almost full. I did not know drive W: was even being used. It has a folder called SqlData which has tempdb.mdf and templog.ldf in it. Therefore we have W:\SqlData\tempdb.mdf at 1.2 GB and W:\SqlData\templog.ldf at 6.7 GB. I went to rename them (before I delete) and the system said there was a lock. I found in EM under Current Activity - Locks / Process ID - spid 60 which had locks on the files and under Locks / Object I found tempdb.dbo.##lockinfo60. 1st question what is creating these files and locks. How do I find what is writing to drive W: and why? 2nd question what do I do? Kill the process, re-boot, do I make the drive bigger? any help would be greatly appreciated. DG > I went to rename them (before I delete) and the system said there was a > lock. I found in EM under Current Activity - Locks / Process ID - spid 60 How did you do your renaming> Did you try to rename the file in the OS? Linchi > I am running SQL2000. I received a notice that my drive W: was almost full. > I did not know drive W: was even being used. It has a folder called SqlData [quoted text clipped - 17 lines] > > DG I tried to rename them in File Explorer. It would not let me because it says the file is in use by another program. >> I went to rename them (before I delete) and the system said there was a >> lock. I found in EM under Current Activity - Locks / Process ID - spid [quoted text clipped - 31 lines] >> >> DG You are confusing locks on the files (which the database engine has), in this case the files that constitutes your tempdb database) with locks inside a database (acquired by the database engine as queries are processed. Apparently somebody thought it was a good idea to have the tempdb files in the W drive and also apparently you have lots of activity in tempdb causing the files to grow to that size. This later is your concern so I suggest you start with http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-preve nt-this-from-happening.html  SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi >I tried to rename them in File Explorer. It would not let me because it says the file is in use by >another program. [quoted text clipped - 27 lines] >>> >>> DG Thanks Tibor, That is helpful. Where is SQL pointing to W:? I would like to change that to a different drive for the future. DG > You are confusing locks on the files (which the database engine has), in > this case the files that constitutes your tempdb database) with locks [quoted text clipped - 43 lines] >>>> >>>> DG I found it. How do I remove the lock? Do I go to spid 60 and kill the process? DG > Thanks Tibor, That is helpful. > [quoted text clipped - 52 lines] >>>>> >>>>> DG I wouldn't be surprised if this lock is taken by *you* when looking at locks. I.e., ignore it. SQL Server do not shrink database files by itself because of resource usage, fragmentation etc. Consider re-starting SQL Server if you need to get the files down in size. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi >I found it. > [quoted text clipped - 46 lines] >>>>>> >>>>>> DG This is (almost certainly) your tempdb. Instructions for moving tempdb can be found at http://support.microsoft.com/kb/224071/en-us Read the Prerequistes section (especially the part about making sure you have current backups of all your databases including the master database (except, of course, you do not need a backup of tempdb)). Run the following code use tempdb go exec sp_helpfile go Make sure that the physical file names that are listed in the filename column are your two files on the W drive. If they are, then scroll down to the "Moving the tempdb database" section in the above webpage. Follow those instructions. Tom > Thanks Tibor, That is helpful. > [quoted text clipped - 52 lines] >>>>> >>>>> DG |
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.