tempdb.dbo.##lockinfo60
in [Server Configuration]
|
From: DG on 1 Jul 2008 10:04 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
From: Linchi Shea on 1 Jul 2008 12:15 > 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 "DG" wrote: > 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 > > >
From: DG on 1 Jul 2008 13:30 I tried to rename them in File Explorer. It would not let me because it says the file is in use by another program. "Linchi Shea" <LinchiShea(a)discussions.microsoft.com> wrote in message news:A0FB0E6D-2649-4F4D-8F0D-1A213E28A7EE(a)microsoft.com... >> 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 > > "DG" wrote: > >> 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 >> >> >>
From: Tibor Karaszi on 1 Jul 2008 13:37 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-prevent-this-from-happening.html -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "DG" <nothere(a)somewhere.com> wrote in message news:OuBr6A62IHA.3544(a)TK2MSFTNGP06.phx.gbl... >I tried to rename them in File Explorer. It would not let me because it says the file is in use by >another program. > > > > "Linchi Shea" <LinchiShea(a)discussions.microsoft.com> wrote in message > news:A0FB0E6D-2649-4F4D-8F0D-1A213E28A7EE(a)microsoft.com... >>> 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 >> >> "DG" wrote: >> >>> 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 >>> >>> >>> > >
From: DG on 1 Jul 2008 13:53
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 "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:uT$gjE62IHA.4928(a)TK2MSFTNGP04.phx.gbl... > 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-prevent-this-from-happening.html > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "DG" <nothere(a)somewhere.com> wrote in message > news:OuBr6A62IHA.3544(a)TK2MSFTNGP06.phx.gbl... >>I tried to rename them in File Explorer. It would not let me because it >>says the file is in use by another program. >> >> >> >> "Linchi Shea" <LinchiShea(a)discussions.microsoft.com> wrote in message >> news:A0FB0E6D-2649-4F4D-8F0D-1A213E28A7EE(a)microsoft.com... >>>> 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 >>> >>> "DG" wrote: >>> >>>> 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 >>>> >>>> >>>> >> >> > |