please help me out

my mdf and ldf are in 1 drive only. i am facing query timeout problem for
this server which is mainly used for employees
if i separate ldf into another drive will there will be any perf benefit
can i add another log file
how to clean up my log fileraghu veer wrote:
> my mdf and ldf are in 1 drive only. i am facing query timeout problem
> for this server which is mainly used for employees
> if i separate ldf into another drive will there will be any perf
> benefit can i add another log file
> how to clean up my log file
No way to know for sure given the limited amount of information.
Assuming you do not have SQL tuning problems or CPU-related issues (not
enough CPU or other services/applications using too much), then a drive
change should be in order. It's always a good idea to separate data and
log files.
A query timeout occurs when you set a maximum limit for query execution
in your code. Have you set a maximum timeout value? Make sure you roll
back the transaction when the query times out.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||BOL has this
SET QUERY_GOVERNOR_COST_LIMIT
Overrides the currently configured value for the current connection.
Syntax
SET QUERY_GOVERNOR_COST_LIMIT value
Arguments
value
Is a numeric or integer value indicating if all queries are allowed to run
(value of 0) or if no queries are allowed to run with an estimated cost
greater than the specified nonzero value. If a numeric value is specified,
Microsoft? SQL Server? truncates it to an integer.
Remarks
Using SET QUERY_GOVERNOR_COST_LIMIT applies to the current connection only
and lasts the duration of the current connection. Use the query governor cos
t
limit option of sp_configure to change the server-wide query governor cost
limit value. For more information about configuring this option, see
sp_configure and Setting Configuration Options.
The setting of SET QUERY_GOVERNOR_COST_LIMIT is set at execute or run time
and not at parse time.
Permissions
SET QUERY_GOVERNOR_COST_LIMIT permissions default to members of the symin
fixed server role.
Regards
R.D
--Knowledge gets doubled when shared
"David Gugick" wrote:

> raghu veer wrote:
> No way to know for sure given the limited amount of information.
> Assuming you do not have SQL tuning problems or CPU-related issues (not
> enough CPU or other services/applications using too much), then a drive
> change should be in order. It's always a good idea to separate data and
> log files.
> A query timeout occurs when you set a maximum limit for query execution
> in your code. Have you set a maximum timeout value? Make sure you roll
> back the transaction when the query times out.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||R.D wrote:
> BOL has this
> SET QUERY_GOVERNOR_COST_LIMIT
> Overrides the currently configured value for the current connection.
> <SNIP>
Are you saying you are setting a governor limit? If so, I wouldn't think
resource overutilization on the server would cause queries to not run
using this setting. Could you clarify?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Please help i can't connect....

Hi
I'm loged on Workstation as a user EUR\zzzz1 and i use different user
EUR\abc123 to map a drive on remote server SERVER2, SERVER2 is a domain
controler . It works.
On SERVER2 i have SQL Server 2000 with mixed mode authentication.
I have a user abc123 added in Active directory on SERVER2:
SERVER2\abc123. The same user is added in Sql Server with rights etc.
When i lounch Query Analyzer on my Workstation and select Sql
Authentication to use EUR\abc123 user i got message "[Microsoft][ODBC
SQL Server Driver][SQL Server]Login failed for user 'EUR\abc123'"
Why i have no access to sql server ?
regards
m
Hi
You must not use SQL Authentication, as it is a domain account, you need to
select NT Authentication when connecting.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"marta" <grupy_d@.go2.pl> wrote in message
news:1127985222.430902.144700@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm loged on Workstation as a user EUR\zzzz1 and i use different user
> EUR\abc123 to map a drive on remote server SERVER2, SERVER2 is a domain
> controler . It works.
> On SERVER2 i have SQL Server 2000 with mixed mode authentication.
> I have a user abc123 added in Active directory on SERVER2:
> SERVER2\abc123. The same user is added in Sql Server with rights etc.
> When i lounch Query Analyzer on my Workstation and select Sql
> Authentication to use EUR\abc123 user i got message "[Microsoft][ODBC
> SQL Server Driver][SQL Server]Login failed for user 'EUR\abc123'"
> Why i have no access to sql server ?
>
> regards
> m
>
|||Ok now i use windows NT Authentication and i can log in but this way
i'm connected to SERVER2 with user EUR\zzzz1 not abc123... I need to
connect using user abc123. How to do that ?
M.
|||Hi Marta
If EUR\abc123 has been added to the server as a login, you will need SQL
authentication to connect as that login. Are you sure you're using the
right password?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"marta" <grupy_d@.go2.pl> wrote in message
news:1127989262.660929.186550@.o13g2000cwo.googlegr oups.com...
> Ok now i use windows NT Authentication and i can log in but this way
> i'm connected to SERVER2 with user EUR\zzzz1 not abc123... I need to
> connect using user abc123. How to do that ?
> M.
>
|||Of course i can map a drive using that pass.
The problem is : how to connect to sql server on remote server with
windows authentication, when i am logged on local workstation with
different user.
M
|||You could probably use "Run As" (right-click the shortcut/executable,
and choose "Run as..." from the menu) to run Query Analyzer under the
context of another user, and therefore be able to pass that security
ticket to the SQL Server when you use Windows Authentication. Runas.exe
can also be called from the command line w/ switches. I believe this
functionality is only available from the right-click menu on Windows
2003 and XP - 2000 contains the command line version.
Good luck,
Tony Sebion
"marta" <grupy_d@.go2.pl> wrote in message
news:1128002474.807122.247970@.g14g2000cwa.googlegr oups.com:

> Of course i can map a drive using that pass.
> The problem is : how to connect to sql server on remote server with
> windows authentication, when i am logged on local workstation with
> different user.
> M
|||you can always connect to the ipc share of remote user with the login/pw of
the user you want
NET USE \\REMOTEPUTER\IPC$ /USER:MYUSERNAME "MYPASSWORD"
now event hough your are logged on as user LOCALUSER...any connection to
remote puter will be as MYUSERNAME
get it ?
"marta" <grupy_d@.go2.pl> wrote in message
news:1128002474.807122.247970@.g14g2000cwa.googlegr oups.com...
> Of course i can map a drive using that pass.
> The problem is : how to connect to sql server on remote server with
> windows authentication, when i am logged on local workstation with
> different user.
> M
>
sql

Please help i can't connect....

Hi
I'm loged on Workstation as a user EUR\zzzz1 and i use different user
EUR\abc123 to map a drive on remote server SERVER2, SERVER2 is a domain
controler . It works.
On SERVER2 i have SQL Server 2000 with mixed mode authentication.
I have a user abc123 added in Active directory on SERVER2:
SERVER2\abc123. The same user is added in Sql Server with rights etc.
When i lounch Query Analyzer on my Workstation and select Sql
Authentication to use EUR\abc123 user i got message "[Microsoft][ODB
C
SQL Server Driver][SQL Server]Login failed for user 'EUR\abc123'"
Why i have no access to sql server ?
regards
mHi
You must not use SQL Authentication, as it is a domain account, you need to
select NT Authentication when connecting.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"marta" <grupy_d@.go2.pl> wrote in message
news:1127985222.430902.144700@.o13g2000cwo.googlegroups.com...
> Hi
> I'm loged on Workstation as a user EUR\zzzz1 and i use different user
> EUR\abc123 to map a drive on remote server SERVER2, SERVER2 is a domain
> controler . It works.
> On SERVER2 i have SQL Server 2000 with mixed mode authentication.
> I have a user abc123 added in Active directory on SERVER2:
> SERVER2\abc123. The same user is added in Sql Server with rights etc.
> When i lounch Query Analyzer on my Workstation and select Sql
> Authentication to use EUR\abc123 user i got message "[Microsoft][O
DBC
> SQL Server Driver][SQL Server]Login failed for user 'EUR\abc123'"
> Why i have no access to sql server ?
>
> regards
> m
>|||Ok now i use windows NT Authentication and i can log in but this way
i'm connected to SERVER2 with user EUR\zzzz1 not abc123... I need to
connect using user abc123. How to do that ?
M.|||Hi Marta
If EUR\abc123 has been added to the server as a login, you will need SQL
authentication to connect as that login. Are you sure you're using the
right password?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"marta" <grupy_d@.go2.pl> wrote in message
news:1127989262.660929.186550@.o13g2000cwo.googlegroups.com...
> Ok now i use windows NT Authentication and i can log in but this way
> i'm connected to SERVER2 with user EUR\zzzz1 not abc123... I need to
> connect using user abc123. How to do that ?
> M.
>|||Of course i can map a drive using that pass.
The problem is : how to connect to sql server on remote server with
windows authentication, when i am logged on local workstation with
different user.
M|||You could probably use "Run As" (right-click the shortcut/executable,
and choose "Run as..." from the menu) to run Query Analyzer under the
context of another user, and therefore be able to pass that security
ticket to the SQL Server when you use Windows Authentication. Runas.exe
can also be called from the command line w/ switches. I believe this
functionality is only available from the right-click menu on Windows
2003 and XP - 2000 contains the command line version.
Good luck,
Tony Sebion
"marta" <grupy_d@.go2.pl> wrote in message
news:1128002474.807122.247970@.g14g2000cwa.googlegroups.com:

> Of course i can map a drive using that pass.
> The problem is : how to connect to sql server on remote server with
> windows authentication, when i am logged on local workstation with
> different user.
> M|||you can always connect to the ipc share of remote user with the login/pw of
the user you want
NET USE \\REMOTEPUTER\IPC$ /USER:MYUSERNAME "MYPASSWORD"
now event hough your are logged on as user LOCALUSER...any connection to
remote puter will be as MYUSERNAME
get it ?
"marta" <grupy_d@.go2.pl> wrote in message
news:1128002474.807122.247970@.g14g2000cwa.googlegroups.com...
> Of course i can map a drive using that pass.
> The problem is : how to connect to sql server on remote server with
> windows authentication, when i am logged on local workstation with
> different user.
> M
>

PLEASE help - moving transaction log from one disk to another

SQL Server 7 SP3a.
In an effort to reclaim a large amount of space on disk, I have moved the
logfile from one drive to another using the steps outlined in this article:
http://www.microsoft.com/sql/techinfo/tips/administration/movingtransactionlogs.asp
when I attempted to reattach the database to the new log file on the new
drive, errors were thrown and SQL Server reported that the database was
corrupt.
Further research indicated that I had to hack the sysfiles1 table to change
the filename path to reflect the new drive location. I did this and rebooted
the server. This now created a suspect database.
How can I get this to work properly?
ThanksHi
I think there may be some problems with access rights /
permissions accessing new drive and files.
Detach and Attach usually works fine.
The indications of suspect status points the problem
accessing file / drive.
Thanks
Suri
>--Original Message--
>SQL Server 7 SP3a.
>In an effort to reclaim a large amount of space on disk,
I have moved the
>logfile from one drive to another using the steps
outlined in this article:
>http://www.microsoft.com/sql/techinfo/tips/administration/
movingtransactionlogs.asp
>when I attempted to reattach the database to the new log
file on the new
>drive, errors were thrown and SQL Server reported that
the database was
>corrupt.
>Further research indicated that I had to hack the
sysfiles1 table to change
>the filename path to reflect the new drive location. I
did this and rebooted
>the server. This now created a suspect database.
>How can I get this to work properly?
>Thanks
>
>.
>

Please Help

I have installed SQL Server 2000 Personal Edition on my machine, which has C: and D: drive. D: is the place where all the installation goes. After the installation when I try to open the Books online I'm getting the following error: Cannot open the file: D:\Program Files\Microsoft SQL Server\80\Tools\Books\SQL80.col.

Please help.Did you try to unistall and reinstall it yet?

Did you explorer to the location and see if the files are there?|||Did you try to see if SQL Query Analyzer and Enterprise Manager work? Did you see an small icon of "Running - \\ServerName - MSSQL server" at the right corner of bottom?|||Install the following and it will do the trick:
http://www.microsoft.com/downloads/details.aspx?FamilyID=6e1c7f59-aba6-4824-90df-43a5be073cd9&DisplayLang=en

:) :cool: ;)

placing system table filegroup and Log files on same drive

Is it better to create a seperate filegroup for all user tables ? And if so
should all the system tables that belong to the Primary filegroup reside on
the same drive with the log file . I am using SQL 2000. Which system tables
get hit the most daily for any inserts,updates and deletes that may be
occuring on the server
ThanksDepends on the disk configuration.
I believe it is generally recommended to separate the log file from any data
files, i.e., separate physical disks... however, if you have a multiple
channel controller, with high thoroughput on each channel, you may be able
to get by w/ having the log and the data on the same logical disk.
I believe that all system tables must reside in PRIMARY, I'd have to verify.
If that is true, it doesn't matter what system tables get inserts, updates,
deletes. However, you are taking away some fault tolerance if you put the
system tables and the log files on the same physical disk.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Is it better to create a seperate filegroup for all user tables ? And if
so
> should all the system tables that belong to the Primary filegroup reside
on
> the same drive with the log file . I am using SQL 2000. Which system
tables
> get hit the most daily for any inserts,updates and deletes that may be
> occuring on the server
> Thanks
>|||You also generally want log files on RAID 1 or 10 as they are continuously
sequentially writing.
Thanks,
Greg H
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eoDKm2EXDHA.2212@.TK2MSFTNGP09.phx.gbl...
> Depends on the disk configuration.
> I believe it is generally recommended to separate the log file from any
data
> files, i.e., separate physical disks... however, if you have a multiple
> channel controller, with high thoroughput on each channel, you may be able
> to get by w/ having the log and the data on the same logical disk.
> I believe that all system tables must reside in PRIMARY, I'd have to
verify.
> If that is true, it doesn't matter what system tables get inserts,
updates,
> deletes. However, you are taking away some fault tolerance if you put the
> system tables and the log files on the same physical disk.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Is it better to create a seperate filegroup for all user tables ? And if
> so
> > should all the system tables that belong to the Primary filegroup reside
> on
> > the same drive with the log file . I am using SQL 2000. Which system
> tables
> > get hit the most daily for any inserts,updates and deletes that may be
> > occuring on the server
> >
> > Thanks
> >
> >
>|||Hassan
If you are using SQL Server 7, it is a good idea to put
your system files in a seperate filegroup. If you get a
corrupted user table, if your system tables are in a
seperate filegroup you can still perform a transaction log
backup prior to invoking DR (If you have DR of course). If
they are in the same filegroup you may not be able to.
This is no longer an issue in 2000.
Hope this helps
John

Placing Log on another drive

We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
Jack
Your two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack
|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack
|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon
|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>
|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?

Placing Log on another drive

We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =(within your SQL Server program group).
-- Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> > We have a database that uses the same drive for logs and > data. After a new disk has been installed what is the > easiest way (and reliable) way to move the logs to the new > disk.
> > I'm thinking of dumping and restoring the database using > the log on clause of restore. Any other ideas ?
> > TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. -- Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
> > Detach\Reattach requires downtime
> > > > Greg Jackson
> PDX, Oregon
> >|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. >-- >Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?

Placing Log on another drive

We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcas
t.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?

Placement of database (newbie question)

I want to create a new database and place it in a drive
and directory other than C:\Program files\microsoft SQL
Server\MSSQL\Data. When I attempt this I get Error 5123:
CREATE FILE encountered operating system error5(Access
denied).....
Must I create the new database in the C: as above? If not
how do I get around the above. I can create files and
folders in that drive from any other program.
The reason for creating in a different drive is space
issues.Anon,
What account rights is the SQL Server service running with? Are you running
as a local admin? Or a specific account?
What this is telling you is that that account does not have rights to your
destination. You should be able to grant rights to the directory to your
SQL Server account and all will be fine.
The following article on MSDN has a lot of server setup information:
Microsoft SQL Server 2000 Scalability Project-Server Consolidation
http://tinyurl.com/5asz7
Russell Fields
<anonymous@.discussions.microsoft.com> wrote in message
news:506001c4911c$c657a140$a501280a@.phx.gbl...
> I want to create a new database and place it in a drive
> and directory other than C:\Program files\microsoft SQL
> Server\MSSQL\Data. When I attempt this I get Error 5123:
> CREATE FILE encountered operating system error5(Access
> denied).....
> Must I create the new database in the C: as above? If not
> how do I get around the above. I can create files and
> folders in that drive from any other program.
> The reason for creating in a different drive is space
> issues.