Lock Question - Should I delete them all??

I have SQL Server 2000 and ASP.NET applications running. It was brought to my attention that some of my applications hitting one of my databases was Timing Out. After a little bit of troubleshooting, I found out it was due to some Locks that existed on my database. I have since then 'Killed Process' and everything seems to be working.

What I did was go to Enterprise Manager, picked the Server --> Management --> Current Activity --> Locks/Object and then found 2 IDs that needed to be killed.

Since then there aren't any hang-ups, but I have noticed under Locks/Objects there are many Process IDs in there. Is it good to Kill all of them? How dangerous is this? All of these processes have the following attributes:

Lock Type = DB
Mode = S
Status = GRANT
Ownder = Sess

I have read BOL and other literature, but can't seem to get the answer I am looking for. And who the heck is Sess?

Your assistance will be greatly appreciated.Don't be worried about this lock, this lock is necessary and you should not kill this process. It is a required System process. The mode is 'S' which menas Shared this is not a strong lock and will not cause deadlocks, so don't worry.

Location of SQL

I have an SQL server with a number of databses including :
Sharepoint and SMS. I would like to move these databases to the D
partition and they are currently in the C:\ partition.
How can this be done ?
Easiest is to us sp_detach_db and sp_attach_db (documented in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?
|||options
1. Detach / Attach
2. Backup / Restore with MOVE
3. add files on d drive to file group
DBBC SHRINKFILE with EMPTRYFILE
drop old files
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?
|||Hi thanks for your response. How Do I move the databses using EM ? Also
How do I attach , detack with EM ?
"jaylou" wrote:
[vbcol=seagreen]
> The easiest way would be to detach the databases, Move the MDF and LDF files
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
|||Hi , I found out how to detach but i am having the problem with one
datbase. A databse that is named tembdb which is 5GB in size
"jaylou" wrote:
[vbcol=seagreen]
> The easiest way would be to detach the databases, Move the MDF and LDF files
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
|||The easiest way would be to detach the databases, Move the MDF and LDF files
to the new location and re-attach the databases.
You can do this all in Enterprise Manager. You can also set the default
location to your new location of these databases thru EM Properties of the
Server.
HTH,
Joe
"scuba19" wrote:

> I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?
|||tempdb is a system database that is used to proccess queryies...refer to
Books Online
to move this database you can simply use ALTER DATABASE...
if you restart your sql server ,this db will shrink back to its origional
size...determined by the settings of the model db
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...[vbcol=seagreen]
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:
|||http://support.microsoft.com/default...22120121120120
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uFfhhZNAGHA.356@.TK2MSFTNGP12.phx.gbl...
> tempdb is a system database that is used to proccess queryies...refer to
> Books Online
> to move this database you can simply use ALTER DATABASE...
> if you restart your sql server ,this db will shrink back to its origional
> size...determined by the settings of the model db
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...
>
|||I am not able to do this to a databse named tempdb which is 5 GB in size ?
"David J. Cartwright" wrote:

> options
> 1. Detach / Attach
> 2. Backup / Restore with MOVE
> 3. add files on d drive to file group
> DBBC SHRINKFILE with EMPTRYFILE
> drop old files
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>
>
|||What problem are you having with TempDB? Did you move Master and model as
well?
I never tried to do this with system DBs only my own created DBs.
"scuba19" wrote:
[vbcol=seagreen]
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:

Location of SQL

I have an SQL server with a number of databses including :
Sharepoint and SMS. I would like to move these databases to the D
partition and they are currently in the C:\ partition.
How can this be done ?Easiest is to us sp_detach_db and sp_attach_db (documented in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||options
1. Detach / Attach
2. Backup / Restore with MOVE
3. add files on d drive to file group
DBBC SHRINKFILE with EMPTRYFILE
drop old files
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||Hi thanks for your response. How Do I move the databses using EM ? Also
How do I attach , detack with EM ?
"jaylou" wrote:
[vbcol=seagreen]
> The easiest way would be to detach the databases, Move the MDF and LDF fil
es
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
>|||Hi , I found out how to detach but i am having the problem with one
datbase. A databse that is named tembdb which is 5GB in size
"jaylou" wrote:
[vbcol=seagreen]
> The easiest way would be to detach the databases, Move the MDF and LDF fil
es
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
>|||The easiest way would be to detach the databases, Move the MDF and LDF files
to the new location and re-attach the databases.
You can do this all in Enterprise Manager. You can also set the default
location to your new location of these databases thru EM Properties of the
Server.
HTH,
Joe
"scuba19" wrote:

> I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||tempdb is a system database that is used to proccess queryies...refer to
Books Online
to move this database you can simply use ALTER DATABASE...
if you restart your sql server ,this db will shrink back to its origional
size...determined by the settings of the model db
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...[vbcol=seagreen]
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:
>|||21120120" target="_blank">http://support.microsoft.com/defaul...br />
21120120
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uFfhhZNAGHA.356@.TK2MSFTNGP12.phx.gbl...
> tempdb is a system database that is used to proccess queryies...refer to
> Books Online
> to move this database you can simply use ALTER DATABASE...
> if you restart your sql server ,this db will shrink back to its origional
> size...determined by the settings of the model db
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...
>|||I am not able to do this to a databse named tempdb which is 5 GB in size ?
"David J. Cartwright" wrote:

> options
> 1. Detach / Attach
> 2. Backup / Restore with MOVE
> 3. add files on d drive to file group
> DBBC SHRINKFILE with EMPTRYFILE
> drop old files
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>
>|||What problem are you having with TempDB? Did you move Master and model as
well?
I never tried to do this with system DBs only my own created DBs.
"scuba19" wrote:
[vbcol=seagreen]
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:
>

Location of SQL

I have an SQL server with a number of databses including :
Sharepoint and SMS. I would like to move these databases to the D
partition and they are currently in the C:\ partition.
How can this be done ?Easiest is to us sp_detach_db and sp_attach_db (documented in Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||options
1. Detach / Attach
2. Backup / Restore with MOVE
3. add files on d drive to file group
DBBC SHRINKFILE with EMPTRYFILE
drop old files
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
>I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||Hi thanks for your response. How Do I move the databses using EM ? Also
How do I attach , detack with EM ?
"jaylou" wrote:
> The easiest way would be to detach the databases, Move the MDF and LDF files
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
> > I have an SQL server with a number of databses including :
> >
> > Sharepoint and SMS. I would like to move these databases to the D
> > partition and they are currently in the C:\ partition.
> >
> > How can this be done ?|||Hi , I found out how to detach but i am having the problem with one
datbase. A databse that is named tembdb which is 5GB in size
"jaylou" wrote:
> The easiest way would be to detach the databases, Move the MDF and LDF files
> to the new location and re-attach the databases.
> You can do this all in Enterprise Manager. You can also set the default
> location to your new location of these databases thru EM Properties of the
> Server.
> HTH,
> Joe
>
> "scuba19" wrote:
> > I have an SQL server with a number of databses including :
> >
> > Sharepoint and SMS. I would like to move these databases to the D
> > partition and they are currently in the C:\ partition.
> >
> > How can this be done ?|||The easiest way would be to detach the databases, Move the MDF and LDF files
to the new location and re-attach the databases.
You can do this all in Enterprise Manager. You can also set the default
location to your new location of these databases thru EM Properties of the
Server.
HTH,
Joe
"scuba19" wrote:
> I have an SQL server with a number of databses including :
> Sharepoint and SMS. I would like to move these databases to the D
> partition and they are currently in the C:\ partition.
> How can this be done ?|||tempdb is a system database that is used to proccess queryies...refer to
Books Online
to move this database you can simply use ALTER DATABASE...
if you restart your sql server ,this db will shrink back to its origional
size...determined by the settings of the model db
"scuba19" <scuba19@.discussions.microsoft.com> wrote in message
news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:
>> The easiest way would be to detach the databases, Move the MDF and LDF
>> files
>> to the new location and re-attach the databases.
>> You can do this all in Enterprise Manager. You can also set the default
>> location to your new location of these databases thru EM Properties of
>> the
>> Server.
>> HTH,
>> Joe
>>
>> "scuba19" wrote:
>> > I have an SQL server with a number of databses including :
>> >
>> > Sharepoint and SMS. I would like to move these databases to the D
>> > partition and they are currently in the C:\ partition.
>> >
>> > How can this be done ?|||http://support.microsoft.com/default.aspx?scid=kb;en-us;224071#XSLTH3207121122120121120120
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uFfhhZNAGHA.356@.TK2MSFTNGP12.phx.gbl...
> tempdb is a system database that is used to proccess queryies...refer to
> Books Online
> to move this database you can simply use ALTER DATABASE...
> if you restart your sql server ,this db will shrink back to its origional
> size...determined by the settings of the model db
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D3D4B487-6C03-41DE-AE62-BE5944C4D504@.microsoft.com...
>> Hi , I found out how to detach but i am having the problem with one
>> datbase. A databse that is named tembdb which is 5GB in size
>> "jaylou" wrote:
>> The easiest way would be to detach the databases, Move the MDF and LDF
>> files
>> to the new location and re-attach the databases.
>> You can do this all in Enterprise Manager. You can also set the default
>> location to your new location of these databases thru EM Properties of
>> the
>> Server.
>> HTH,
>> Joe
>>
>> "scuba19" wrote:
>> > I have an SQL server with a number of databses including :
>> >
>> > Sharepoint and SMS. I would like to move these databases to the D
>> > partition and they are currently in the C:\ partition.
>> >
>> > How can this be done ?
>|||I am not able to do this to a databse named tempdb which is 5 GB in size ?
"David J. Cartwright" wrote:
> options
> 1. Detach / Attach
> 2. Backup / Restore with MOVE
> 3. add files on d drive to file group
> DBBC SHRINKFILE with EMPTRYFILE
> drop old files
> "scuba19" <scuba19@.discussions.microsoft.com> wrote in message
> news:D954B376-1C4F-449E-B25D-0DF5CDA0FA59@.microsoft.com...
> >I have an SQL server with a number of databses including :
> >
> > Sharepoint and SMS. I would like to move these databases to the D
> > partition and they are currently in the C:\ partition.
> >
> > How can this be done ?
>
>|||What problem are you having with TempDB? Did you move Master and model as
well?
I never tried to do this with system DBs only my own created DBs.
"scuba19" wrote:
> Hi , I found out how to detach but i am having the problem with one
> datbase. A databse that is named tembdb which is 5GB in size
> "jaylou" wrote:
> > The easiest way would be to detach the databases, Move the MDF and LDF files
> > to the new location and re-attach the databases.
> > You can do this all in Enterprise Manager. You can also set the default
> > location to your new location of these databases thru EM Properties of the
> > Server.
> >
> > HTH,
> > Joe
> >
> >
> > "scuba19" wrote:
> >
> > > I have an SQL server with a number of databses including :
> > >
> > > Sharepoint and SMS. I would like to move these databases to the D
> > > partition and they are currently in the C:\ partition.
> > >
> > > How can this be done ?

Location of Data Files

I have installed SQL Server 2000 with Master / Model /
MSDB databases in C Drive.
Since the size of C Drive is not sufficient, is it
possible for me to restore a database backup from another
SQL Server to D:\MSSQL\Data ?
ThanksThis article may help you
http://support.microsoft.com/?kbid=224071
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Roger Lee" <rogerlee@.nospam.com> wrote in message
news:06b101c36553$fd1b6db0$a101280a@.phx.gbl...
> I have installed SQL Server 2000 with Master / Model /
> MSDB databases in C Drive.
> Since the size of C Drive is not sufficient, is it
> possible for me to restore a database backup from another
> SQL Server to D:\MSSQL\Data ?
> Thankssql

Located databases no longer used.

I’m looking to locate databases that have not been written to or accessed for
a specified period of time; we are looking to locate databases that are not
used anymore and clean house. Is there a system table or stored procedure to
access this information? Thank you in advance for you assistance.
Pauly C
No there is not. Your best bet is to run a continuous trace filtered on the
particular db(s) and see if anything accesses them.
Andrew J. Kelly SQL MVP
"Pauly C" <paulysc@.optonline.net> wrote in message
news:04BD4AED-F586-4686-ADBB-0AD86A4EEF90@.microsoft.com...
> I'm looking to locate databases that have not been written to or accessed
> for
> a specified period of time; we are looking to locate databases that are
> not
> used anymore and clean house. Is there a system table or stored procedure
> to
> access this information? Thank you in advance for you assistance.
> Pauly C
>
|||Bummer, Thanks Andy. Tony B say's hello.
Pauly C
"Andrew J. Kelly" wrote:

> No there is not. Your best bet is to run a continuous trace filtered on the
> particular db(s) and see if anything accesses them.
> --
> Andrew J. Kelly SQL MVP
>
> "Pauly C" <paulysc@.optonline.net> wrote in message
> news:04BD4AED-F586-4686-ADBB-0AD86A4EEF90@.microsoft.com...
>
>
|||Didn't even realize that was you<g>. Unfortunately the answer is still
no<g>... Everyone wants that feature though.
Andrew J. Kelly SQL MVP
"Pauly C" <paulysc@.optonline.net> wrote in message
news:A5BDC793-6A72-4F0B-9891-025DC99D927C@.microsoft.com...[vbcol=seagreen]
> Bummer, Thanks Andy. Tony B say's hello.
> Pauly C
> "Andrew J. Kelly" wrote:

Localization in SQL Server 2000

Hi.

I have distributed databases in different language versions, I am creating a stored procedure, and this problably in future is going to be migrated to other SQL Server database. the query that I have inside the stored procedure is this:

Insert Into Prueba Values(14,'31/07/1999') ---> format date of this query is in Hispanic version (this works fine)

But, If this query is migrated to other SQL Server and it's version would be in English, that query wouldn't work, the principal reason is the format date.

Solution that I have on mind is creating a stored procedure that receives three parameters the month,day and year. I want to identify the localization of that SQL Server database and use "IF" conditions and inside of these concatenate month,day and year obviously depending of the date format identified through "IF" conditions.

If somebody has an idea to solve this or somebody knows how to identify the locatization in an SQL Server database I would be thankful.

Thank you again and best regards.

Christian

Hello my friend,

You do not need to do this. Instead, change the way string dates are interpreted in your stored procedure by using the SET DATEFORMAT command in the procedure before parsing the date. Run the following to see what I mean: -

DECLARE @.datevar varchar(50)
SET @.datevar = '2007/08/04'

SET DATEFORMAT ymd
SELECT MONTH(@.datevar)

SET DATEFORMAT ydm
SELECT MONTH(@.datevar)

Kind regards

Scotty

|||

Thank you so much...

I know it's going to work fine.

Thank you so much again !!

Local server name

Hi,
I am trying to connect to my (local) server to get a list of databases on that server. It doesn't work when I pass (local) as the server name, but it works if I hardcode in the actual name. Does anyone know how to get the actual server name?
TIA,
RoyceTry "." or "127.0.0.1" without the quotes.

Tim S