sql local data: log

Showing posts with label log. Show all posts

Wednesday, March 28, 2012

Lock Resource

I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?

Monday, March 26, 2012

Lock Manager does a dead lock search?

0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec

0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jl
Sounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec

0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec

0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl
|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Lock Manager does a dead lock search?

I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
--
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
--
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
> >I see the following in the SQL (error) log and am curious as to why it
> >shows
> > up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> > 2000
> > SP4. This server uses the Intel EMT processor. We have two other
> > similarly
> > configured servers and I don't see these mesages on those servers (same
> > databases, etc.).
> >
> >
> > Starting deadlock search 34450
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> > ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> > SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> > was not found.
> >
> > --
> > jl
>
>

Lock Manager does a dead lock search?

0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec

0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec

0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec

0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:

> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBC
C
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>

Friday, March 23, 2012

lock database and shrink database log

I need a script or method to lock users out of a database,
shrink it/truncated it and then let them back in....You can try:
ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This will set the database to dbo_useonly and roll back all current
transactions.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:966501c3eb21$82ec2cb0$a101280a@.phx.gbl...
> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....|||Hi,
use the below steps in script
1. ALTER DATABASE PUBS SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
2. If it full recovery model Execute , Backup Log command (To backup
transaction log)
3. Execute DBCC SHRINKFILE on Transaction log file
4. ALTER DATABASE PUBS SET MULTI_USER
Thanks
Hari
MCDBA
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:966501c3eb21$82ec2cb0$a101280a@.phx.gbl...
> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....

lock database and shrink database log

quote:

> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....

|||Hi,
use the below steps in script
1. ALTER DATABASE PUBS SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
2. If it full recovery model Execute , Backup Log command (To backup
transaction log)
3. Execute DBCC SHRINKFILE on Transaction log file
4. ALTER DATABASE PUBS SET MULTI_USER
Thanks
Hari
MCDBA
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:966501c3eb21$82ec2cb0$a101280a@.phx.gbl...

quote:

> I need a script or method to lock users out of a database,
> shrink it/truncated it and then let them back in....

Wednesday, March 21, 2012

Location of transaction log on another machine

Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
Steve
Basically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default...b;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.c om...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com

Location of transaction log on another machine

Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performa
nce hit for
modifications. For the full story, check out
http://support.microsoft.com/defaul...kb;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com

Location of transaction log on another machine

Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com

Location of the Audit Log

This should be an easy one: when I turn on the Audit Log, what directory and
what is the log called? Can I send the log to a drive other than the drive
I have my data files on?SQL Server auditing is sent to the NT Event logs.
Is this what you are asking?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I need the name of the file SQL Server creates. I am looking in that config
folder and ther is at least 14 NT files in this thing!|||Errorlogs are sent to the drive specified by this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\Parameters
SQLArg1
Hope this helps.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||got it! Thanks for all your help!

Location of MDF / LDF / Backup / Log

For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your adviceStephen wrote:
> For a finance application, we use a Windows 2003 Server
> with C and D are local HD (They are mirrored as RAID 1).
> On the other hand, it is connected to a SAN with drive
> letter J.
> We find that the consultant has installed the SQLS Server
> 2000 DB MDF and LDF in the D drive. The finance
> application is installed on the J drive.
> We will backup both C,D and J drive daily. They haven't
> setup the database maintenance plan yet.
> For both performance purpose, we would like to get your
> advie where is the best place to put the backup files and
> log files ? Is it D or J drive ? Should we suggest them
> to put the transaction log file from D to J as well ?
> Thank you for your advice
If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored set (so that makes four local hard drives)? You don't have to
back up the drives themselves for SQL Server backup and recovery. What
you need to back up are the full, differential, and t-log backups you
make throughout the day. Generally, you want to put the backup files on
different spindles than the drive on which the data originates to
maximize bandwidth. So if the data is on the SAN, you can back up to one
of the local arrays.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> Stephen wrote:
>> For a finance application, we use a Windows 2003 Server
>> with C and D are local HD (They are mirrored as RAID 1).
>> On the other hand, it is connected to a SAN with drive
>> letter J.
>> We find that the consultant has installed the SQLS Server
>> 2000 DB MDF and LDF in the D drive. The finance
>> application is installed on the J drive.
>> We will backup both C,D and J drive daily. They haven't
>> setup the database maintenance plan yet.
>> For both performance purpose, we would like to get your
>> advie where is the best place to put the backup files and
>> log files ? Is it D or J drive ? Should we suggest them
>> to put the transaction log file from D to J as well ?
>> Thank you for your advice
> If you've paid for a SAN, it seems silly not use it for the data and
> t-logs. Are you saying that C is a mirrored set and drive D is a mirrored
> set (so that makes four local hard drives)? You don't have to back up the
> drives themselves for SQL Server backup and recovery. What you need to
> back up are the full, differential, and t-log backups you make throughout
> the day. Generally, you want to put the backup files on different spindles
> than the drive on which the data originates to maximize bandwidth. So if
> the data is on the SAN, you can back up to one of the local arrays.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Dear David,
> Thank you for your advice. Yes, both C and D are mirrored set.
> Someone suggest to put the transaction log file in the RAID 1 for recovery
> and data in RAID 5. Is it OK from your point of view ?
> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> Thanks
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> > Stephen wrote:
> >> For a finance application, we use a Windows 2003 Server
> >> with C and D are local HD (They are mirrored as RAID 1).
> >> On the other hand, it is connected to a SAN with drive
> >> letter J.
> >>
> >> We find that the consultant has installed the SQLS Server
> >> 2000 DB MDF and LDF in the D drive. The finance
> >> application is installed on the J drive.
> >>
> >> We will backup both C,D and J drive daily. They haven't
> >> setup the database maintenance plan yet.
> >>
> >> For both performance purpose, we would like to get your
> >> advie where is the best place to put the backup files and
> >> log files ? Is it D or J drive ? Should we suggest them
> >> to put the transaction log file from D to J as well ?
> >>
> >> Thank you for your advice
> >
> > If you've paid for a SAN, it seems silly not use it for the data and
> > t-logs. Are you saying that C is a mirrored set and drive D is a
mirrored
> > set (so that makes four local hard drives)? You don't have to back up
the
> > drives themselves for SQL Server backup and recovery. What you need to
> > back up are the full, differential, and t-log backups you make
throughout
> > the day. Generally, you want to put the backup files on different
spindles
> > than the drive on which the data originates to maximize bandwidth. So if
> > the data is on the SAN, you can back up to one of the local arrays.
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
>|||Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> Stephen
> Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
> buy RAID 10&1) put the log on
> RAID1 device and data on RAID-5
> (this option is appropriate if the writes activites are moderate)
>
> Note: The appropriate decision depends on your perfomance charctiristics
> and fault tolerance needs.
>
>
> "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>> Dear David,
>> Thank you for your advice. Yes, both C and D are mirrored set.
>> Someone suggest to put the transaction log file in the RAID 1 for
>> recovery
>> and data in RAID 5. Is it OK from your point of view ?
>> Where should I put the backup file ? RAID 1 or RAID 5 array ?
>> Thanks
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>> > Stephen wrote:
>> >> For a finance application, we use a Windows 2003 Server
>> >> with C and D are local HD (They are mirrored as RAID 1).
>> >> On the other hand, it is connected to a SAN with drive
>> >> letter J.
>> >>
>> >> We find that the consultant has installed the SQLS Server
>> >> 2000 DB MDF and LDF in the D drive. The finance
>> >> application is installed on the J drive.
>> >>
>> >> We will backup both C,D and J drive daily. They haven't
>> >> setup the database maintenance plan yet.
>> >>
>> >> For both performance purpose, we would like to get your
>> >> advie where is the best place to put the backup files and
>> >> log files ? Is it D or J drive ? Should we suggest them
>> >> to put the transaction log file from D to J as well ?
>> >>
>> >> Thank you for your advice
>> >
>> > If you've paid for a SAN, it seems silly not use it for the data and
>> > t-logs. Are you saying that C is a mirrored set and drive D is a
> mirrored
>> > set (so that makes four local hard drives)? You don't have to back up
> the
>> > drives themselves for SQL Server backup and recovery. What you need to
>> > back up are the full, differential, and t-log backups you make
> throughout
>> > the day. Generally, you want to put the backup files on different
> spindles
>> > than the drive on which the data originates to maximize bandwidth. So
>> > if
>> > the data is on the SAN, you can back up to one of the local arrays.
>> >
>> > --
>> > David Gugick
>> > Quest Software
>> > www.imceda.com
>> > www.quest.com
>>
>|||Stepfen
I'd recommend you buy "Inside SQL Server 2000" written by Kalen Delaney (See
topic about RAID solutions)
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:e2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would like
to
> know the underlying reason to put the log on RAID 1 and data on RAID 5.
> On the other hand, should we put the Database and Transaction log backup
on
> RAID 5 array ?
> Thank you again.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> > Stephen
> > Most of SQL Server DBA/Programmers ( yep if the budget does not permit
to
> > buy RAID 10&1) put the log on
> > RAID1 device and data on RAID-5
> > (this option is appropriate if the writes activites are moderate)
> >
> >
> >
> > Note: The appropriate decision depends on your perfomance
charctiristics
> > and fault tolerance needs.
> >
> >
> >
> >
> > "Stephen" <anonymous@.discussions.microsoft.com> wrote in message
> > news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
> >> Dear David,
> >>
> >> Thank you for your advice. Yes, both C and D are mirrored set.
> >>
> >> Someone suggest to put the transaction log file in the RAID 1 for
> >> recovery
> >> and data in RAID 5. Is it OK from your point of view ?
> >>
> >> Where should I put the backup file ? RAID 1 or RAID 5 array ?
> >>
> >> Thanks
> >>
> >> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> >> news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
> >> > Stephen wrote:
> >> >> For a finance application, we use a Windows 2003 Server
> >> >> with C and D are local HD (They are mirrored as RAID 1).
> >> >> On the other hand, it is connected to a SAN with drive
> >> >> letter J.
> >> >>
> >> >> We find that the consultant has installed the SQLS Server
> >> >> 2000 DB MDF and LDF in the D drive. The finance
> >> >> application is installed on the J drive.
> >> >>
> >> >> We will backup both C,D and J drive daily. They haven't
> >> >> setup the database maintenance plan yet.
> >> >>
> >> >> For both performance purpose, we would like to get your
> >> >> advie where is the best place to put the backup files and
> >> >> log files ? Is it D or J drive ? Should we suggest them
> >> >> to put the transaction log file from D to J as well ?
> >> >>
> >> >> Thank you for your advice
> >> >
> >> > If you've paid for a SAN, it seems silly not use it for the data and
> >> > t-logs. Are you saying that C is a mirrored set and drive D is a
> > mirrored
> >> > set (so that makes four local hard drives)? You don't have to back up
> > the
> >> > drives themselves for SQL Server backup and recovery. What you need
to
> >> > back up are the full, differential, and t-log backups you make
> > throughout
> >> > the day. Generally, you want to put the backup files on different
> > spindles
> >> > than the drive on which the data originates to maximize bandwidth. So
> >> > if
> >> > the data is on the SAN, you can back up to one of the local arrays.
> >> >
> >> > --
> >> > David Gugick
> >> > Quest Software
> >> > www.imceda.com
> >> > www.quest.com
> >>
> >>
> >
> >
>|||Stephen wrote:
> Dear Uri,
> Thank you for your advice. However, I am new to RAID array. I would
> like to know the underlying reason to put the log on RAID 1 and data
> on RAID 5.
> On the other hand, should we put the Database and Transaction log
> backup on RAID 5 array ?
>
I didn't think RAID 5 was one of your installed options given you only
had 4 local drives...
In any case, for small and medium sized databases with a low percentage
of writes, RAID 5 is an inexpensive solution. RAID 5 writes data very
slowly, so it's only appropriate for data files. Transaction logs and
tempdb should reside on a mirrored set (which you have). It's best for
performance to keep data and t-logs on different drives, which are also
separate from the OS. In your environment, you have a SAN (J), and 2
local mirrored sets (C and D).
I would recommend you use C for the OS and applications like SQL Server.
Use D for the t-logs and tempdb, and use the SAN for the data.
Everything needs to backed up in some fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--030209030201000601050508
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
This website has a good intro to RAID levels and the basic pros & cons
(even if they are basically trying to sell you their products):
http://www.acnc.com/raid.html
(for those that can't afford Kalen's book _Inside SQL Server_, which in
an excellent book that I thoroughly recommend)
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Stephen wrote:
>Dear Uri,
>Thank you for your advice. However, I am new to RAID array. I would like to
>know the underlying reason to put the log on RAID 1 and data on RAID 5.
>On the other hand, should we put the Database and Transaction log backup on
>RAID 5 array ?
>Thank you again.
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>
>>Stephen
>>Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
>>buy RAID 10&1) put the log on
>>RAID1 device and data on RAID-5
>>(this option is appropriate if the writes activites are moderate)
>>
>>Note: The appropriate decision depends on your perfomance charctiristics
>>and fault tolerance needs.
>>
>>
>>"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
>>news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl...
>>
>>Dear David,
>>Thank you for your advice. Yes, both C and D are mirrored set.
>>Someone suggest to put the transaction log file in the RAID 1 for
>>recovery
>>and data in RAID 5. Is it OK from your point of view ?
>>Where should I put the backup file ? RAID 1 or RAID 5 array ?
>>Thanks
>>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>>news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl...
>>
>>Stephen wrote:
>>
>>For a finance application, we use a Windows 2003 Server
>>with C and D are local HD (They are mirrored as RAID 1).
>>On the other hand, it is connected to a SAN with drive
>>letter J.
>>We find that the consultant has installed the SQLS Server
>>2000 DB MDF and LDF in the D drive. The finance
>>application is installed on the J drive.
>>We will backup both C,D and J drive daily. They haven't
>>setup the database maintenance plan yet.
>>For both performance purpose, we would like to get your
>>advie where is the best place to put the backup files and
>>log files ? Is it D or J drive ? Should we suggest them
>>to put the transaction log file from D to J as well ?
>>Thank you for your advice
>>
>>If you've paid for a SAN, it seems silly not use it for the data and
>>t-logs. Are you saying that C is a mirrored set and drive D is a
>>
>>mirrored
>>
>>set (so that makes four local hard drives)? You don't have to back up
>>
>>the
>>
>>drives themselves for SQL Server backup and recovery. What you need to
>>back up are the full, differential, and t-log backups you make
>>
>>throughout
>>
>>the day. Generally, you want to put the backup files on different
>>
>>spindles
>>
>>than the drive on which the data originates to maximize bandwidth. So
>>if
>>the data is on the SAN, you can back up to one of the local arrays.
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>>
>>
>
>
--030209030201000601050508
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>This website has a good intro to RAID levels and the basic pros
& cons (even if they are basically trying to sell you their
products): 
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.acnc.com/raid.html</a> ">http://www.acnc.com/raid.html">http://www.acnc.com/raid.html</a> 
 
(for those that can't afford Kalen's book Inside SQL Server,
which in an excellent book that I thoroughly recommend) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Stephen wrote:
<blockquote cite="mide2dG$6kdFHA.3040@.TK2MSFTNGP14.phx.gbl" type="cite">
<pre wrap="">Dear Uri,
Thank you for your advice. However, I am new to RAID array. I would like to
know the underlying reason to put the log on RAID 1 and data on RAID 5.
On the other hand, should we put the Database and Transaction log backup on
RAID 5 array ?
Thank you again.
"Uri Dimant" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:urid@.iscar.co.il"><urid@.iscar.co.il></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:uQhEZ3kdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen
Most of SQL Server DBA/Programmers ( yep if the budget does not permit to
buy RAID 10&1) put the log on
RAID1 device and data on RAID-5
(this option is appropriate if the writes activites are moderate)
Note: The appropriate decision depends on your perfomance charctiristics
and fault tolerance needs.
"Stephen" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:anonymous@.discussions.microsoft.com"><anonymous@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl">news:%23zfTGykdFHA.1136@.TK2MSFTNGP12.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Dear David,
Thank you for your advice. Yes, both C and D are mirrored set.
Someone suggest to put the transaction log file in the RAID 1 for
recovery
and data in RAID 5. Is it OK from your point of view ?
Where should I put the backup file ? RAID 1 or RAID 5 array ?
Thanks
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl">news:uqkbyWidFHA.892@.tk2msftngp13.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Stephen wrote:
</pre>
<blockquote type="cite">
<pre wrap="">For a finance application, we use a Windows 2003 Server
with C and D are local HD (They are mirrored as RAID 1).
On the other hand, it is connected to a SAN with drive
letter J.
We find that the consultant has installed the SQLS Server
2000 DB MDF and LDF in the D drive. The finance
application is installed on the J drive.
We will backup both C,D and J drive daily. They haven't
setup the database maintenance plan yet.
For both performance purpose, we would like to get your
advie where is the best place to put the backup files and
log files ? Is it D or J drive ? Should we suggest them
to put the transaction log file from D to J as well ?
Thank you for your advice
</pre>
</blockquote>
<pre wrap="">If you've paid for a SAN, it seems silly not use it for the data and
t-logs. Are you saying that C is a mirrored set and drive D is a
</pre>
</blockquote>
</blockquote>
<pre wrap="">mirrored
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">set (so that makes four local hard drives)? You don't have to back up
</pre>
</blockquote>
</blockquote>
<pre wrap="">the
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">drives themselves for SQL Server backup and recovery. What you need to
back up are the full, differential, and t-log backups you make
</pre>
</blockquote>
</blockquote>
<pre wrap="">throughout
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">the day. Generally, you want to put the backup files on different
</pre>
</blockquote>
</blockquote>
<pre wrap="">spindles
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">than the drive on which the data originates to maximize bandwidth. So
if
the data is on the SAN, you can back up to one of the local arrays.
--
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--030209030201000601050508--

Location of MDF / LDF / Backup / Log

Location of error log?

Hopefully this will be a simple question.
Where is the error log located? I am looking in
program files\microsoft sql server\mssql\reporting services\LogFile
at a file called ReportServer__09_27_2005_11_38_23.log inside that file is a
message:
e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Where can I fine the error log it refers to? Everything has been installed
to default locations.
ThanksYou are looking at the error log. The log contains the text that is
displayed to users so it is a little misleading when seeing it in the log
itself. Can you include the entire call stack?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
> Hopefully this will be a simple question.
> Where is the error log located? I am looking in
> program files\microsoft sql server\mssql\reporting services\LogFile
> at a file called ReportServer__09_27_2005_11_38_23.log inside that file is
> a
> message:
> e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more
> details., ;
> Where can I fine the error log it refers to? Everything has been
> installed
> to default locations.
> Thanks|||Thanks for that. I am getting different errors in the log at different times
from the same problem. The problem in detailed in my post "Login Prompt
after ~3min timeout".
I am reguarly getting out memory errors, but I have looked at the machine
and it is only using ~1/3 of available memory and CPU so I think that is a
red herring.
The report runs fine if the data allows it to complete before 2.5 - 3mins -
this seems to be 2.5mins on the test server I am using and 3mins on the live
environment.
The following messages are the only consistant ones I am seeing in the log:
w3wp!runningjobs!1568!28/09/2005-09:46:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!1568!28/09/2005-09:46:45:: w WARN:
RunningJobContext.Cancel; failed
w3wp!runningjobs!1568!28/09/2005-09:47:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:48:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:49:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:50:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!fe4!28/09/2005-09:51:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:52:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
Any thoughts, pointers or suggestions will be gratefully received.
"Daniel Reib [MSFT]" wrote:
> You are looking at the error log. The log contains the text that is
> displayed to users so it is a little misleading when seeing it in the log
> itself. Can you include the entire call stack?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
> news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
> > Hopefully this will be a simple question.
> >
> > Where is the error log located? I am looking in
> >
> > program files\microsoft sql server\mssql\reporting services\LogFile
> >
> > at a file called ReportServer__09_27_2005_11_38_23.log inside that file is
> > a
> > message:
> >
> > e ERROR: Throwing
> > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> > An internal error occurred on the report server. See the error log for
> > more
> > details., ;
> >
> > Where can I fine the error log it refers to? Everything has been
> > installed
> > to default locations.
> >
> > Thanks
>
>|||The orphaned requests means that the client is disconnected from the server.
In this case RS will stop processing the job.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
news:E2F9A42E-C173-4B35-8BA3-749239454E90@.microsoft.com...
> Thanks for that. I am getting different errors in the log at different
> times
> from the same problem. The problem in detailed in my post "Login Prompt
> after ~3min timeout".
> I am reguarly getting out memory errors, but I have looked at the machine
> and it is only using ~1/3 of available memory and CPU so I think that is a
> red herring.
> The report runs fine if the data allows it to complete before 2.5 -
> 3mins -
> this seems to be 2.5mins on the test server I am using and 3mins on the
> live
> environment.
> The following messages are the only consistant ones I am seeing in the
> log:
> w3wp!runningjobs!1568!28/09/2005-09:46:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!1568!28/09/2005-09:46:45:: w WARN:
> RunningJobContext.Cancel; failed
> w3wp!runningjobs!1568!28/09/2005-09:47:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:48:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:49:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:50:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!fe4!28/09/2005-09:51:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:52:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> Any thoughts, pointers or suggestions will be gratefully received.
> "Daniel Reib [MSFT]" wrote:
>> You are looking at the error log. The log contains the text that is
>> displayed to users so it is a little misleading when seeing it in the log
>> itself. Can you include the entire call stack?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
>> news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
>> > Hopefully this will be a simple question.
>> >
>> > Where is the error log located? I am looking in
>> >
>> > program files\microsoft sql server\mssql\reporting services\LogFile
>> >
>> > at a file called ReportServer__09_27_2005_11_38_23.log inside that file
>> > is
>> > a
>> > message:
>> >
>> > e ERROR: Throwing
>> > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
>> > An internal error occurred on the report server. See the error log for
>> > more
>> > details., ;
>> >
>> > Where can I fine the error log it refers to? Everything has been
>> > installed
>> > to default locations.
>> >
>> > Thanks
>>

Location of database and log files

"SeaBob" <SeaBob@.discussions.microsoft.com> wrote in message
news:B5C2383C-D301-4096-8C74-9247CBEE6F75@.microsoft.com...
> We are beginning a new implemenation of SQL2000 and considering the
locations
> of the database and log files. Am I correct that the Log files must reside
on
> the same server as SQL? Is performance effected at all if the database
file
> resides on another server?
|||Data and log files can and should be split across multiple volumes or
devices on a server (either direct-attached or SAN storage) but those
devices must be attached to the server that is running SQL.
In certain very restricted circumstances and on certain hardware only NAS
storage is supported but this is definitely not recommended other than in
exceptional cases.
Installation of databases on a remote server is not supported, nor would it
be a desriable or sensible thing to do from the point of view of
architecture, performance, resilience or data-integrity.
David Portas
SQL Server MVP

Location of database and log files

We are beginning a new implemenation of SQL2000 and considering the locations
of the database and log files. Am I correct that the Log files must reside on
the same server as SQL? Is performance effected at all if the database file
resides on another server?Well - think what would happen if you lost the other server or even if you
lost network connection.
It's not a good idea. :(
"SeaBob" <SeaBob@.discussions.microsoft.com> wrote in message
news:B5C2383C-D301-4096-8C74-9247CBEE6F75@.microsoft.com...
> We are beginning a new implemenation of SQL2000 and considering the
locations
> of the database and log files. Am I correct that the Log files must reside
on
> the same server as SQL? Is performance effected at all if the database
file
> resides on another server?|||Data and log files can and should be split across multiple volumes or
devices on a server (either direct-attached or SAN storage) but those
devices must be attached to the server that is running SQL.
In certain very restricted circumstances and on certain hardware only NAS
storage is supported but this is definitely not recommended other than in
exceptional cases.
Installation of databases on a remote server is not supported, nor would it
be a desriable or sensible thing to do from the point of view of
architecture, performance, resilience or data-integrity.
--
David Portas
SQL Server MVP
--

Location of .MDF and .LDF files

Does anyone know where a good article pertaining to where you should locate your Data and Log files (in SQL Server 2005)?

I read an article several years ago stating that Log files should be on a seperate RAID 1 and Data on a seperate RAID 5.

Anyway, any help is appreciated.

Look at http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=62

Monday, March 19, 2012

Locating S

I recently uploaded my site to the internet. But now, when I try to to log in, I receive the following error message:

An error has occurred while establishing a connection tothe server. When connecting to SQL Server 2005, this failure may becaused by the fact that under the default settings SQL Server does notallow remote connections. (provider: SQL Network Interfaces, error: 26- Error Locating Server/Instance Specified)

How do I fix this?

I assumed you mean you get this error when you uploaded your site to your hosting service.

This error usually means that your ASP.NET application is trying to connect to a SQL Express database. In general, very few webhosting provider support SQL Express because it is not intended for production use. Does your host offer SQL 2005? If so, migrate your data to the SQL 2k5 database and update your application's connection string in the web.config (or in the code itself) to point to the SQL 2k5 database.

Hope this helps.

|||You're right. Thanks for the help. Fixed it now.

Localsystem account access deny

Hi All,

I have a SQL server, as I use a domain account to log on to SQL server and Sql server agent, all maintanence plans work good, since I changed from a domain into Localsystem account to log on to SQL server, and Sql server agent, all maintanence plans didn't work any more, then I tried only keep Localsystem account at SQl server , using a domain log on to Sql server agent, but it's still failed to maintanence plans. The error in job history is"Executed as user: candyl. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.". And the message at Sql server log is :"BackupDiskFile::CreateMedia: Backup device 'D:\Database Backups\Noon Backup\ESMDEV_db_200406141548.BAK' failed to create. Operating system error = 5(Access is denied.)". It looks like permission problem, but for Localsystem account which should has full permission, right? I tried may ways and searched from knowledge base , still couldn't find the related solution.
Anyone can give me some advices?

Thanks.Help please!!!!!|||Does the user candyl have NT permission to create files in the destination directory?|||If users have been set up permission at server box, they can create the directiory.|||1. Does the user 'candyl' has the admin rights on the SQL Server?
2. Try this: 1) log on to your server under the 'candyl' login abd try to create any (dummy) file under the 'D:\Database Backups\Noon Backup\' directory. Was it successful?

Friday, March 9, 2012

localhost giving problem

WIN XP
Windows 2000
we have a standard build script as follows to build a database locally.
osql -d Master -i script name -o script log -E -S localhost
The script fails with the following error message
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
If -S option is removed, the script runs fine.
I Googled on this problem and did the following.
In the client library I disabled Named Piped protocol and enabled only
TCP/IP protocols.
Stopped and restarted SQL Server.
I also added an alias localhost with network libary set to TCP/IP. Unchecked
Dynically check port
number and set the port to 1433.
Still the problem persists.
BTW this is a new build machine and Sql server is installed for the first
time on that machine.
TIA
Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
Or, why can't you just leave the -S parameter off?
http://www.aspfaq.com/
(Reverse address to reply.)
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.t eranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>
|||I'm not following why you need to specify the name of the server...
according to BOL:
The -S parameter, needs:
[-S server_name[\instance_name]]
Further:
"If you do not specify a server, the name of the workstation is used."
I'd recommend leaving the -S off.
Steve
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109697584.61f025d955437a9ee32b16bfa728bcfd@.t eranews...
> WIN XP
> Windows 2000
> we have a standard build script as follows to build a database locally.
> osql -d Master -i script name -o script log -E -S localhost
> The script fails with the following error message
> [TCP/IP Sockets] SQL Server does not exist or access denied
> [TCP/IP Sockets]ConnectionOpen (Connect())
> If -S option is removed, the script runs fine.
> I Googled on this problem and did the following.
> In the client library I disabled Named Piped protocol and enabled only
> TCP/IP protocols.
> Stopped and restarted SQL Server.
> I also added an alias localhost with network libary set to TCP/IP.
Unchecked
> Dynically check port
> number and set the port to 1433.
> Still the problem persists.
> BTW this is a new build machine and Sql server is installed for the first
> time on that machine.
> TIA
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Did you try -S 127.0.0.1 or -S (local) or -S <the actual machine name>?
> Or, why can't you just leave the -S parameter off?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
I will have to do a big justification on why -S should be removed. I just
joined this project.
These scripts were running fine on older machines. The problem is with this
new machine only.
changing the script (removing -S) will be my last option.
thanks all.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109698545.da57de61dec89a895ce5ac06b889c11e@.t eranews...
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23GiDLPoHFHA.1528@.TK2MSFTNGP09.phx.gbl...
> I will have to do a big justification on why -S should be removed. I just
> joined this project.
> These scripts were running fine on older machines. The problem is with
> this new machine only.
> changing the script (removing -S) will be my last option.
I have another machine where this script works. However that machine is Win
2000 [ version 5.00.2195].
The one which is giving problem is WIn XP [ version 5.1.2600 ].
Is this a useful information?
|||> I will have to do a big justification on why -S should be removed.
Other than "it breaks, and is not necessary anyway"?
|||> I have another machine where this script works. However that machine is
Win
> 2000 [ version 5.00.2195].
> The one which is giving problem is WIn XP [ version 5.1.2600 ].
> Is this a useful information?
That depends. Did you try any of my other suggestions?
1. .
2. (local)
3. 127.0.0.1
4. <the actual IP address>
5. <the actual workstation name>
6. "do a big justification" on leaving off the unnecessary parameter
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etYDHeoHFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Win
> That depends. Did you try any of my other suggestions?
> 1. .
> 2. (local)
success

> 3. 127.0.0.1
(failed)

> 4. <the actual IP address>
> 5. <the actual workstation name>
> 6. "do a big justification" on leaving off the unnecessary parameter
did not try.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote[vbcol=seagreen]
> success
sorry. this one too works.?
so we have following two commands working
osql -d Master -i file.sql -o file.out -E -S (local)
osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
but this one fails
osql -d Master -i file.sql -o file.out -E -S localhost
[TCP/IP Sockets] SQL Server does not exist or access denied
[TCP/IP Sockets]ConnectionOpen (Connect())
I double checked C:\WINDOWS\SYSTEM32\DRIVERS\etc\hosts file and saw an entry
for 127.0.0.1 localhost.
so why only localhost is failing.
|||"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:1109707082.5fa894f609537b1ba2e3d37d057d3898@.t eranews...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote
> sorry. this one too works.?
>
> so we have following two commands working
> osql -d Master -i file.sql -o file.out -E -S (local)
> osql -d Master -i file.sql -o file.out -E -S 127.0.0.1
> but this one fails
> osql -d Master -i file.sql -o file.out -E -S localhost
even IP address works
osql -d Master -i file.sql -o file.out -E -S 170.x.x.x
runs OK.

Wednesday, March 28, 2012

Lock Resource

Monday, March 26, 2012

Lock Manager does a dead lock search?

Lock Manager does a dead lock search?

Lock Manager does a dead lock search?

Friday, March 23, 2012

lock database and shrink database log

lock database and shrink database log

Wednesday, March 21, 2012

Location of transaction log on another machine

Location of transaction log on another machine

Location of transaction log on another machine

Location of the Audit Log

Location of MDF / LDF / Backup / Log

Location of MDF / LDF / Backup / Log

Location of MDF / LDF / Backup / Log

Location of error log?

Location of database and log files

Location of database and log files

Location of .MDF and .LDF files

Monday, March 19, 2012

Locating S

Localsystem account access deny

Friday, March 9, 2012

localhost giving problem

sql local data

Blog Archive

About Me