Sorry if this is a repost I don't think my first one went through.
I have optimization setup in my database maintenance plan. It fails
everytime. It creates a LDF file which stops when it hits 2.5gb. This
causes the error "Log File is Full". At that point noone can connect to the
DB until I run a copy and delte query to move the info elsewhere.
Two questions:
1) Can I change the size the LDF file is allowed to grow too and where, or
can I tell it not to create the LDFfile?
2) Cani choose where the LDF file is located, I need it written to another
drive where I have more room for it to grow.
Thanks in aedvance.UPDATE: Ok so when I look at the properties for one of the databases and
look at the Transaction Log tab, I see where the LDF record is coming from.
I also see where it is restricted to 2500 mb.
I would like to change the maximum file size to Unrestricted file growth.
But I need to move where the log file is saved to a different drive. When I
click to change the location I get the message "A transaction log file's
physical filename cannot be changed once the transaction log file has been
created."
How do I change where the physical location of the log file is?
Thanks,
"Kevin Porter" wrote:
> Sorry if this is a repost I don't think my first one went through.
> I have optimization setup in my database maintenance plan. It fails
> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> causes the error "Log File is Full". At that point noone can connect to the
> DB until I run a copy and delte query to move the info elsewhere.
> Two questions:
> 1) Can I change the size the LDF file is allowed to grow too and where, or
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to another
> drive where I have more room for it to grow.
> Thanks in aedvance.
>|||Kevin,
This KB has instructions for various SQL versons and databases, so it should
have your answer.
http://support.microsoft.com/kb/224071/en-us
RLF
"Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
> UPDATE: Ok so when I look at the properties for one of the databases and
> look at the Transaction Log tab, I see where the LDF record is coming
> from.
> I also see where it is restricted to 2500 mb.
> I would like to change the maximum file size to Unrestricted file growth.
> But I need to move where the log file is saved to a different drive. When
> I
> click to change the location I get the message "A transaction log file's
> physical filename cannot be changed once the transaction log file has been
> created."
> How do I change where the physical location of the log file is?
> Thanks,
> "Kevin Porter" wrote:
>> Sorry if this is a repost I don't think my first one went through.
>> I have optimization setup in my database maintenance plan. It fails
>> everytime. It creates a LDF file which stops when it hits 2.5gb. This
>> causes the error "Log File is Full". At that point noone can connect to
>> the
>> DB until I run a copy and delte query to move the info elsewhere.
>> Two questions:
>> 1) Can I change the size the LDF file is allowed to grow too and where,
>> or
>> can I tell it not to create the LDFfile?
>> 2) Cani choose where the LDF file is located, I need it written to
>> another
>> drive where I have more room for it to grow.
>> Thanks in aedvance.
>>|||Thank you Russell. That looks to be exactly what I need. I figured it would
involve detaching the database, but walkthroughs are nice when you're just
learning like me.
Thanks again for the quick response.
"Russell Fields" wrote:
> Kevin,
> This KB has instructions for various SQL versons and databases, so it should
> have your answer.
> http://support.microsoft.com/kb/224071/en-us
> RLF
> "Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
> news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
> > UPDATE: Ok so when I look at the properties for one of the databases and
> > look at the Transaction Log tab, I see where the LDF record is coming
> > from.
> > I also see where it is restricted to 2500 mb.
> >
> > I would like to change the maximum file size to Unrestricted file growth.
> > But I need to move where the log file is saved to a different drive. When
> > I
> > click to change the location I get the message "A transaction log file's
> > physical filename cannot be changed once the transaction log file has been
> > created."
> >
> > How do I change where the physical location of the log file is?
> >
> > Thanks,
> >
> > "Kevin Porter" wrote:
> >
> >> Sorry if this is a repost I don't think my first one went through.
> >>
> >> I have optimization setup in my database maintenance plan. It fails
> >> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> >> causes the error "Log File is Full". At that point noone can connect to
> >> the
> >> DB until I run a copy and delte query to move the info elsewhere.
> >>
> >> Two questions:
> >>
> >> 1) Can I change the size the LDF file is allowed to grow too and where,
> >> or
> >> can I tell it not to create the LDFfile?
> >>
> >> 2) Cani choose where the LDF file is located, I need it written to
> >> another
> >> drive where I have more room for it to grow.
> >>
> >> Thanks in aedvance.
> >>
> >>
>
>|||On Wed, 18 Jul 2007 09:40:02 -0700, Kevin Porter wrote:
>Sorry if this is a repost I don't think my first one went through.
>I have optimization setup in my database maintenance plan. It fails
>everytime. It creates a LDF file which stops when it hits 2.5gb. This
>causes the error "Log File is Full". At that point noone can connect to the
>DB until I run a copy and delte query to move the info elsewhere.
>Two questions:
>1) Can I change the size the LDF file is allowed to grow too and where, or
>can I tell it not to create the LDFfile?
>2) Cani choose where the LDF file is located, I need it written to another
>drive where I have more room for it to grow.
>Thanks in aedvance.
>
Hi Kevin,
Your questions are already answered, but you might still need to adress
the reason of your ever-growing log file.
Do you know that a database with recovery model full (the default) or
bulk-logged will continue to grow the log file until you take a bacup of
the log file? If you need full (or bulk-logged) recovery, then you
should schedule regular log backups. If you don't need the ability to
restore beyond the last full database backup, you should change the
recovery model to simple.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Wednesday, March 21, 2012
Optimization failure.
Sorry if this is a repost I don't think my first one went through.
I have optimization setup in my database maintenance plan. It fails
everytime. It creates a LDF file which stops when it hits 2.5gb. This
causes the error "Log File is Full". At that point noone can connect to the
DB until I run a copy and delte query to move the info elsewhere.
Two questions:
1) Can I change the size the LDF file is allowed to grow too and where, or
can I tell it not to create the LDFfile?
2) Cani choose where the LDF file is located, I need it written to another
drive where I have more room for it to grow.
Thanks in aedvance.
UPDATE: Ok so when I look at the properties for one of the databases and
look at the Transaction Log tab, I see where the LDF record is coming from.
I also see where it is restricted to 2500 mb.
I would like to change the maximum file size to Unrestricted file growth.
But I need to move where the log file is saved to a different drive. When I
click to change the location I get the message "A transaction log file's
physical filename cannot be changed once the transaction log file has been
created."
How do I change where the physical location of the log file is?
Thanks,
"Kevin Porter" wrote:
> Sorry if this is a repost I don't think my first one went through.
> I have optimization setup in my database maintenance plan. It fails
> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> causes the error "Log File is Full". At that point noone can connect to the
> DB until I run a copy and delte query to move the info elsewhere.
> Two questions:
> 1) Can I change the size the LDF file is allowed to grow too and where, or
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to another
> drive where I have more room for it to grow.
> Thanks in aedvance.
>
|||Kevin,
This KB has instructions for various SQL versons and databases, so it should
have your answer.
http://support.microsoft.com/kb/224071/en-us
RLF
"Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...[vbcol=seagreen]
> UPDATE: Ok so when I look at the properties for one of the databases and
> look at the Transaction Log tab, I see where the LDF record is coming
> from.
> I also see where it is restricted to 2500 mb.
> I would like to change the maximum file size to Unrestricted file growth.
> But I need to move where the log file is saved to a different drive. When
> I
> click to change the location I get the message "A transaction log file's
> physical filename cannot be changed once the transaction log file has been
> created."
> How do I change where the physical location of the log file is?
> Thanks,
> "Kevin Porter" wrote:
|||Thank you Russell. That looks to be exactly what I need. I figured it would
involve detaching the database, but walkthroughs are nice when you're just
learning like me.
Thanks again for the quick response.
"Russell Fields" wrote:
> Kevin,
> This KB has instructions for various SQL versons and databases, so it should
> have your answer.
> http://support.microsoft.com/kb/224071/en-us
> RLF
> "Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
> news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
>
>
|||On Wed, 18 Jul 2007 09:40:02 -0700, Kevin Porter wrote:
>Sorry if this is a repost I don't think my first one went through.
>I have optimization setup in my database maintenance plan. It fails
>everytime. It creates a LDF file which stops when it hits 2.5gb. This
>causes the error "Log File is Full". At that point noone can connect to the
>DB until I run a copy and delte query to move the info elsewhere.
>Two questions:
>1) Can I change the size the LDF file is allowed to grow too and where, or
>can I tell it not to create the LDFfile?
>2) Cani choose where the LDF file is located, I need it written to another
>drive where I have more room for it to grow.
>Thanks in aedvance.
>
Hi Kevin,
Your questions are already answered, but you might still need to adress
the reason of your ever-growing log file.
Do you know that a database with recovery model full (the default) or
bulk-logged will continue to grow the log file until you take a bacup of
the log file? If you need full (or bulk-logged) recovery, then you
should schedule regular log backups. If you don't need the ability to
restore beyond the last full database backup, you should change the
recovery model to simple.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
I have optimization setup in my database maintenance plan. It fails
everytime. It creates a LDF file which stops when it hits 2.5gb. This
causes the error "Log File is Full". At that point noone can connect to the
DB until I run a copy and delte query to move the info elsewhere.
Two questions:
1) Can I change the size the LDF file is allowed to grow too and where, or
can I tell it not to create the LDFfile?
2) Cani choose where the LDF file is located, I need it written to another
drive where I have more room for it to grow.
Thanks in aedvance.
UPDATE: Ok so when I look at the properties for one of the databases and
look at the Transaction Log tab, I see where the LDF record is coming from.
I also see where it is restricted to 2500 mb.
I would like to change the maximum file size to Unrestricted file growth.
But I need to move where the log file is saved to a different drive. When I
click to change the location I get the message "A transaction log file's
physical filename cannot be changed once the transaction log file has been
created."
How do I change where the physical location of the log file is?
Thanks,
"Kevin Porter" wrote:
> Sorry if this is a repost I don't think my first one went through.
> I have optimization setup in my database maintenance plan. It fails
> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> causes the error "Log File is Full". At that point noone can connect to the
> DB until I run a copy and delte query to move the info elsewhere.
> Two questions:
> 1) Can I change the size the LDF file is allowed to grow too and where, or
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to another
> drive where I have more room for it to grow.
> Thanks in aedvance.
>
|||Kevin,
This KB has instructions for various SQL versons and databases, so it should
have your answer.
http://support.microsoft.com/kb/224071/en-us
RLF
"Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...[vbcol=seagreen]
> UPDATE: Ok so when I look at the properties for one of the databases and
> look at the Transaction Log tab, I see where the LDF record is coming
> from.
> I also see where it is restricted to 2500 mb.
> I would like to change the maximum file size to Unrestricted file growth.
> But I need to move where the log file is saved to a different drive. When
> I
> click to change the location I get the message "A transaction log file's
> physical filename cannot be changed once the transaction log file has been
> created."
> How do I change where the physical location of the log file is?
> Thanks,
> "Kevin Porter" wrote:
|||Thank you Russell. That looks to be exactly what I need. I figured it would
involve detaching the database, but walkthroughs are nice when you're just
learning like me.
Thanks again for the quick response.
"Russell Fields" wrote:
> Kevin,
> This KB has instructions for various SQL versons and databases, so it should
> have your answer.
> http://support.microsoft.com/kb/224071/en-us
> RLF
> "Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
> news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
>
>
|||On Wed, 18 Jul 2007 09:40:02 -0700, Kevin Porter wrote:
>Sorry if this is a repost I don't think my first one went through.
>I have optimization setup in my database maintenance plan. It fails
>everytime. It creates a LDF file which stops when it hits 2.5gb. This
>causes the error "Log File is Full". At that point noone can connect to the
>DB until I run a copy and delte query to move the info elsewhere.
>Two questions:
>1) Can I change the size the LDF file is allowed to grow too and where, or
>can I tell it not to create the LDFfile?
>2) Cani choose where the LDF file is located, I need it written to another
>drive where I have more room for it to grow.
>Thanks in aedvance.
>
Hi Kevin,
Your questions are already answered, but you might still need to adress
the reason of your ever-growing log file.
Do you know that a database with recovery model full (the default) or
bulk-logged will continue to grow the log file until you take a bacup of
the log file? If you need full (or bulk-logged) recovery, then you
should schedule regular log backups. If you don't need the ability to
restore beyond the last full database backup, you should change the
recovery model to simple.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tuesday, March 20, 2012
Optimization failure.
Sorry if this is a repost I don't think my first one went through.
I have optimization setup in my database maintenance plan. It fails
everytime. It creates a LDF file which stops when it hits 2.5gb. This
causes the error "Log File is Full". At that point noone can connect to the
DB until I run a copy and delte query to move the info elsewhere.
Two questions:
1) Can I change the size the LDF file is allowed to grow too and where, or
can I tell it not to create the LDFfile?
2) Cani choose where the LDF file is located, I need it written to another
drive where I have more room for it to grow.
Thanks in aedvance.UPDATE: Ok so when I look at the properties for one of the databases and
look at the Transaction Log tab, I see where the LDF record is coming from.
I also see where it is restricted to 2500 mb.
I would like to change the maximum file size to Unrestricted file growth.
But I need to move where the log file is saved to a different drive. When I
click to change the location I get the message "A transaction log file's
physical filename cannot be changed once the transaction log file has been
created."
How do I change where the physical location of the log file is?
Thanks,
"Kevin Porter" wrote:
> Sorry if this is a repost I don't think my first one went through.
> I have optimization setup in my database maintenance plan. It fails
> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> causes the error "Log File is Full". At that point noone can connect to t
he
> DB until I run a copy and delte query to move the info elsewhere.
> Two questions:
> 1) Can I change the size the LDF file is allowed to grow too and where, o
r
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to anothe
r
> drive where I have more room for it to grow.
> Thanks in aedvance.
>|||Kevin,
This KB has instructions for various SQL versons and databases, so it should
have your answer.
http://support.microsoft.com/kb/224071/en-us
RLF
"Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...[vbcol=seagreen]
> UPDATE: Ok so when I look at the properties for one of the databases and
> look at the Transaction Log tab, I see where the LDF record is coming
> from.
> I also see where it is restricted to 2500 mb.
> I would like to change the maximum file size to Unrestricted file growth.
> But I need to move where the log file is saved to a different drive. When
> I
> click to change the location I get the message "A transaction log file's
> physical filename cannot be changed once the transaction log file has been
> created."
> How do I change where the physical location of the log file is?
> Thanks,
> "Kevin Porter" wrote:
>|||Thank you Russell. That looks to be exactly what I need. I figured it woul
d
involve detaching the database, but walkthroughs are nice when you're just
learning like me.
Thanks again for the quick response.
"Russell Fields" wrote:
> Kevin,
> This KB has instructions for various SQL versons and databases, so it shou
ld
> have your answer.
> http://support.microsoft.com/kb/224071/en-us
> RLF
> "Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
> news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
>
>|||On Wed, 18 Jul 2007 09:40:02 -0700, Kevin Porter wrote:
>Sorry if this is a repost I don't think my first one went through.
>I have optimization setup in my database maintenance plan. It fails
>everytime. It creates a LDF file which stops when it hits 2.5gb. This
>causes the error "Log File is Full". At that point noone can connect to th
e
>DB until I run a copy and delte query to move the info elsewhere.
>Two questions:
>1) Can I change the size the LDF file is allowed to grow too and where, or
>can I tell it not to create the LDFfile?
>2) Cani choose where the LDF file is located, I need it written to another
>drive where I have more room for it to grow.
>Thanks in aedvance.
>
Hi Kevin,
Your questions are already answered, but you might still need to adress
the reason of your ever-growing log file.
Do you know that a database with recovery model full (the default) or
bulk-logged will continue to grow the log file until you take a bacup of
the log file? If you need full (or bulk-logged) recovery, then you
should schedule regular log backups. If you don't need the ability to
restore beyond the last full database backup, you should change the
recovery model to simple.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
I have optimization setup in my database maintenance plan. It fails
everytime. It creates a LDF file which stops when it hits 2.5gb. This
causes the error "Log File is Full". At that point noone can connect to the
DB until I run a copy and delte query to move the info elsewhere.
Two questions:
1) Can I change the size the LDF file is allowed to grow too and where, or
can I tell it not to create the LDFfile?
2) Cani choose where the LDF file is located, I need it written to another
drive where I have more room for it to grow.
Thanks in aedvance.UPDATE: Ok so when I look at the properties for one of the databases and
look at the Transaction Log tab, I see where the LDF record is coming from.
I also see where it is restricted to 2500 mb.
I would like to change the maximum file size to Unrestricted file growth.
But I need to move where the log file is saved to a different drive. When I
click to change the location I get the message "A transaction log file's
physical filename cannot be changed once the transaction log file has been
created."
How do I change where the physical location of the log file is?
Thanks,
"Kevin Porter" wrote:
> Sorry if this is a repost I don't think my first one went through.
> I have optimization setup in my database maintenance plan. It fails
> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> causes the error "Log File is Full". At that point noone can connect to t
he
> DB until I run a copy and delte query to move the info elsewhere.
> Two questions:
> 1) Can I change the size the LDF file is allowed to grow too and where, o
r
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to anothe
r
> drive where I have more room for it to grow.
> Thanks in aedvance.
>|||Kevin,
This KB has instructions for various SQL versons and databases, so it should
have your answer.
http://support.microsoft.com/kb/224071/en-us
RLF
"Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...[vbcol=seagreen]
> UPDATE: Ok so when I look at the properties for one of the databases and
> look at the Transaction Log tab, I see where the LDF record is coming
> from.
> I also see where it is restricted to 2500 mb.
> I would like to change the maximum file size to Unrestricted file growth.
> But I need to move where the log file is saved to a different drive. When
> I
> click to change the location I get the message "A transaction log file's
> physical filename cannot be changed once the transaction log file has been
> created."
> How do I change where the physical location of the log file is?
> Thanks,
> "Kevin Porter" wrote:
>|||Thank you Russell. That looks to be exactly what I need. I figured it woul
d
involve detaching the database, but walkthroughs are nice when you're just
learning like me.
Thanks again for the quick response.
"Russell Fields" wrote:
> Kevin,
> This KB has instructions for various SQL versons and databases, so it shou
ld
> have your answer.
> http://support.microsoft.com/kb/224071/en-us
> RLF
> "Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
> news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
>
>|||On Wed, 18 Jul 2007 09:40:02 -0700, Kevin Porter wrote:
>Sorry if this is a repost I don't think my first one went through.
>I have optimization setup in my database maintenance plan. It fails
>everytime. It creates a LDF file which stops when it hits 2.5gb. This
>causes the error "Log File is Full". At that point noone can connect to th
e
>DB until I run a copy and delte query to move the info elsewhere.
>Two questions:
>1) Can I change the size the LDF file is allowed to grow too and where, or
>can I tell it not to create the LDFfile?
>2) Cani choose where the LDF file is located, I need it written to another
>drive where I have more room for it to grow.
>Thanks in aedvance.
>
Hi Kevin,
Your questions are already answered, but you might still need to adress
the reason of your ever-growing log file.
Do you know that a database with recovery model full (the default) or
bulk-logged will continue to grow the log file until you take a bacup of
the log file? If you need full (or bulk-logged) recovery, then you
should schedule regular log backups. If you don't need the ability to
restore beyond the last full database backup, you should change the
recovery model to simple.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
optimization
I cannot make the database optimize properly. I have a
maintenance plan setup specifically to do just that, but
it is never able to run and our database is becoming
extremely slow. Any ideas?What errors are being reported by sqlmaint - should be in one of the txt =filesin the sql server logs directory. Youcan find the filename buy =looking at the details of the scheduled job that runs the optimisatons =job.
If the optimisations job i snever running it will man indexes etc may be =less than optimal but your phrase "extremely slow" makes me suspect =something else is wrong as well.
Mike John
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message =news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> I cannot make the database optimize properly. I have a > maintenance plan setup specifically to do just that, but > it is never able to run and our database is becoming > extremely slow. Any ideas?|||As far as I can tell, there are no errors, it is just not
doing it. In the History file, it tells me that it is
checking the db, but it is not able to rebuild indexes,
which my boss thinks is the reason the db is so very slow.
It backs up the db without any problem, but it cannot
back up the transaction log...
As you can probably tell, I am fairly new to SQL server...
Anything I can do to fix it?
>--Original Message--
>What errors are being reported by sqlmaint - should be
in one of the txt filesin the sql server logs directory.
Youcan find the filename buy looking at the details of
the scheduled job that runs the optimisatons job.
>If the optimisations job i snever running it will man
indexes etc may be less than optimal but your
phrase "extremely slow" makes me suspect something else
is wrong as well.
>Mike John
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> I cannot make the database optimize properly. I have
a
>> maintenance plan setup specifically to do just that,
but
>> it is never able to run and our database is becoming
>> extremely slow. Any ideas?
>.
>|||> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
How do you know it isn't rebuilding indexes if there are no error messages?
We need the error message from the rtp file.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
That is probably not related. My guess is that the database is in simple
recovery mode. read more in Books Online about backup to understand recovery
mode and ability to do log backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
> which my boss thinks is the reason the db is so very slow.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
> As you can probably tell, I am fairly new to SQL server...
> Anything I can do to fix it?
> >--Original Message--
> >What errors are being reported by sqlmaint - should be
> in one of the txt filesin the sql server logs directory.
> Youcan find the filename buy looking at the details of
> the scheduled job that runs the optimisatons job.
> >
> >If the optimisations job i snever running it will man
> indexes etc may be less than optimal but your
> phrase "extremely slow" makes me suspect something else
> is wrong as well.
> >
> >Mike John
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> I cannot make the database optimize properly. I have
> a
> >> maintenance plan setup specifically to do just that,
> but
> >> it is never able to run and our database is becoming
> >> extremely slow. Any ideas?
> >.
> >|||Kristen
Have you tried using dbcc showcontig, this will show you
if your tables are fragmented. (See BOL for details). If
they are bably fragmented you will know the main plan is
not working.
Another thought is that in your maint plan have you chosen
the option 'attempt to repair minor problems' if you have
remove it. This regularly causes problems with maint
plans. Any open connections and your plan will not run
properly with this option.
Hope this helps
John|||The reason I know it is not rebuilding indexes is because
the history tells me so. There is always a red X next to
rebuild indexes indicating it was not done. I looked
through every error log at the times it reported that
rebuilding indexes was not done and it said nothing about
rebuilding indexes. That is why I say there is no error
message. If there is a different place I should be
looking please tell me because I do not know what an rtp
file is.
>--Original Message--
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>How do you know it isn't rebuilding indexes if there are
no error messages?
>We need the error message from the rtp file.
>
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>That is probably not related. My guess is that the
database is in simple
>recovery mode. read more in Books Online about backup to
understand recovery
>mode and ability to do log backup.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>> which my boss thinks is the reason the db is so very
slow.
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>> As you can probably tell, I am fairly new to SQL
server...
>> Anything I can do to fix it?
>> >--Original Message--
>> >What errors are being reported by sqlmaint - should be
>> in one of the txt filesin the sql server logs
directory.
>> Youcan find the filename buy looking at the details of
>> the scheduled job that runs the optimisatons job.
>> >
>> >If the optimisations job i snever running it will man
>> indexes etc may be less than optimal but your
>> phrase "extremely slow" makes me suspect something else
>> is wrong as well.
>> >
>> >Mike John
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> >> I cannot make the database optimize properly. I
have
>> a
>> >> maintenance plan setup specifically to do just that,
>> but
>> >> it is never able to run and our database is becoming
>> >> extremely slow. Any ideas?
>> >.
>> >
>
>.
>|||Hi,
Thanks for the suggestion about the showcontig. I will
try that later. I checked the 'attempt to repair minor
problems' in all three of our maint plans and none of
them have this option checked.
Thanks,
Kristen
>--Original Message--
>Kristen
>Have you tried using dbcc showcontig, this will show you
>if your tables are fragmented. (See BOL for details). If
>they are bably fragmented you will know the main plan is
>not working.
>Another thought is that in your maint plan have you
chosen
>the option 'attempt to repair minor problems' if you
have
>remove it. This regularly causes problems with maint
>plans. Any open connections and your plan will not run
>properly with this option.
>Hope this helps
>John
>.
>|||The main wizard can create a report file. Check the config of the maint
plan, make sure that you have specified a report file and check that report
file for error messages.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> The reason I know it is not rebuilding indexes is because
> the history tells me so. There is always a red X next to
> rebuild indexes indicating it was not done. I looked
> through every error log at the times it reported that
> rebuilding indexes was not done and it said nothing about
> rebuilding indexes. That is why I say there is no error
> message. If there is a different place I should be
> looking please tell me because I do not know what an rtp
> file is.
>
> >--Original Message--
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >
> >How do you know it isn't rebuilding indexes if there are
> no error messages?
> >We need the error message from the rtp file.
> >
> >
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >
> >That is probably not related. My guess is that the
> database is in simple
> >recovery mode. read more in Books Online about backup to
> understand recovery
> >mode and ability to do log backup.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >> which my boss thinks is the reason the db is so very
> slow.
> >>
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >>
> >> As you can probably tell, I am fairly new to SQL
> server...
> >>
> >> Anything I can do to fix it?
> >>
> >> >--Original Message--
> >> >What errors are being reported by sqlmaint - should be
> >> in one of the txt filesin the sql server logs
> directory.
> >> Youcan find the filename buy looking at the details of
> >> the scheduled job that runs the optimisatons job.
> >> >
> >> >If the optimisations job i snever running it will man
> >> indexes etc may be less than optimal but your
> >> phrase "extremely slow" makes me suspect something else
> >> is wrong as well.
> >> >
> >> >Mike John
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> >> I cannot make the database optimize properly. I
> have
> >> a
> >> >> maintenance plan setup specifically to do just that,
> >> but
> >> >> it is never able to run and our database is becoming
> >> >> extremely slow. Any ideas?
> >> >.
> >> >
> >
> >
> >.
> >|||Thank you!
Here it is:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What does it mean?
>--Original Message--
>The main wizard can create a report file. Check the
config of the maint
>plan, make sure that you have specified a report file
and check that report
>file for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
>> The reason I know it is not rebuilding indexes is
because
>> the history tells me so. There is always a red X next
to
>> rebuild indexes indicating it was not done. I looked
>> through every error log at the times it reported that
>> rebuilding indexes was not done and it said nothing
about
>> rebuilding indexes. That is why I say there is no
error
>> message. If there is a different place I should be
>> looking please tell me because I do not know what an
rtp
>> file is.
>>
>> >--Original Message--
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >
>> >How do you know it isn't rebuilding indexes if there
are
>> no error messages?
>> >We need the error message from the rtp file.
>> >
>> >
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >
>> >That is probably not related. My guess is that the
>> database is in simple
>> >recovery mode. read more in Books Online about backup
to
>> understand recovery
>> >mode and ability to do log backup.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >> which my boss thinks is the reason the db is so very
>> slow.
>> >>
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >>
>> >> As you can probably tell, I am fairly new to SQL
>> server...
>> >>
>> >> Anything I can do to fix it?
>> >>
>> >> >--Original Message--
>> >> >What errors are being reported by sqlmaint -
should be
>> >> in one of the txt filesin the sql server logs
>> directory.
>> >> Youcan find the filename buy looking at the details
of
>> >> the scheduled job that runs the optimisatons job.
>> >> >
>> >> >If the optimisations job i snever running it will
man
>> >> indexes etc may be less than optimal but your
>> >> phrase "extremely slow" makes me suspect something
else
>> >> is wrong as well.
>> >> >
>> >> >Mike John
>> >> >
>> >> >
>> >> >"Kristen" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message news:025e01c3a7d4$d3af1d00
$a501280a@.phx.gbl...
>> >> >> I cannot make the database optimize properly. I
>> have
>> >> a
>> >> >> maintenance plan setup specifically to do just
that,
>> >> but
>> >> >> it is never able to run and our database is
becoming
>> >> >> extremely slow. Any ideas?
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||It means that you either has a view with index on or a computed column with
an index on. And for these to be manipulated (index rebuilt in this case),
the session which executes that manipulation command (maintenance wizard
executing DBCC DBREINDEX in this case) need to have the SET commands
mentioned in the error message turned on. Unfortunately, the maint wizard
doesn't do that. Your options it to remove the reorg from the maint wizard
and do it yourself from an Agent TSQL jobstep (having the correct SET
options), or remove those indexes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:047f01c3a894$427adc00$a301280a@.phx.gbl...
> Thank you!
> Here it is:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> failed because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What does it mean?
> >--Original Message--
> >The main wizard can create a report file. Check the
> config of the maint
> >plan, make sure that you have specified a report file
> and check that report
> >file for error messages.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> >> The reason I know it is not rebuilding indexes is
> because
> >> the history tells me so. There is always a red X next
> to
> >> rebuild indexes indicating it was not done. I looked
> >> through every error log at the times it reported that
> >> rebuilding indexes was not done and it said nothing
> about
> >> rebuilding indexes. That is why I say there is no
> error
> >> message. If there is a different place I should be
> >> looking please tell me because I do not know what an
> rtp
> >> file is.
> >>
> >>
> >> >--Original Message--
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >
> >> >How do you know it isn't rebuilding indexes if there
> are
> >> no error messages?
> >> >We need the error message from the rtp file.
> >> >
> >> >
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >
> >> >That is probably not related. My guess is that the
> >> database is in simple
> >> >recovery mode. read more in Books Online about backup
> to
> >> understand recovery
> >> >mode and ability to do log backup.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >> which my boss thinks is the reason the db is so very
> >> slow.
> >> >>
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >>
> >> >> As you can probably tell, I am fairly new to SQL
> >> server...
> >> >>
> >> >> Anything I can do to fix it?
> >> >>
> >> >> >--Original Message--
> >> >> >What errors are being reported by sqlmaint -
> should be
> >> >> in one of the txt filesin the sql server logs
> >> directory.
> >> >> Youcan find the filename buy looking at the details
> of
> >> >> the scheduled job that runs the optimisatons job.
> >> >> >
> >> >> >If the optimisations job i snever running it will
> man
> >> >> indexes etc may be less than optimal but your
> >> >> phrase "extremely slow" makes me suspect something
> else
> >> >> is wrong as well.
> >> >> >
> >> >> >Mike John
> >> >> >
> >> >> >
> >> >> >"Kristen" <anonymous@.discussions.microsoft.com>
> wrote
> >> in
> >> >> message news:025e01c3a7d4$d3af1d00
> $a501280a@.phx.gbl...
> >> >> >> I cannot make the database optimize properly. I
> >> have
> >> >> a
> >> >> >> maintenance plan setup specifically to do just
> that,
> >> >> but
> >> >> >> it is never able to run and our database is
> becoming
> >> >> >> extremely slow. Any ideas?
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
maintenance plan setup specifically to do just that, but
it is never able to run and our database is becoming
extremely slow. Any ideas?What errors are being reported by sqlmaint - should be in one of the txt =filesin the sql server logs directory. Youcan find the filename buy =looking at the details of the scheduled job that runs the optimisatons =job.
If the optimisations job i snever running it will man indexes etc may be =less than optimal but your phrase "extremely slow" makes me suspect =something else is wrong as well.
Mike John
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message =news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> I cannot make the database optimize properly. I have a > maintenance plan setup specifically to do just that, but > it is never able to run and our database is becoming > extremely slow. Any ideas?|||As far as I can tell, there are no errors, it is just not
doing it. In the History file, it tells me that it is
checking the db, but it is not able to rebuild indexes,
which my boss thinks is the reason the db is so very slow.
It backs up the db without any problem, but it cannot
back up the transaction log...
As you can probably tell, I am fairly new to SQL server...
Anything I can do to fix it?
>--Original Message--
>What errors are being reported by sqlmaint - should be
in one of the txt filesin the sql server logs directory.
Youcan find the filename buy looking at the details of
the scheduled job that runs the optimisatons job.
>If the optimisations job i snever running it will man
indexes etc may be less than optimal but your
phrase "extremely slow" makes me suspect something else
is wrong as well.
>Mike John
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> I cannot make the database optimize properly. I have
a
>> maintenance plan setup specifically to do just that,
but
>> it is never able to run and our database is becoming
>> extremely slow. Any ideas?
>.
>|||> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
How do you know it isn't rebuilding indexes if there are no error messages?
We need the error message from the rtp file.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
That is probably not related. My guess is that the database is in simple
recovery mode. read more in Books Online about backup to understand recovery
mode and ability to do log backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> As far as I can tell, there are no errors, it is just not
> doing it. In the History file, it tells me that it is
> checking the db, but it is not able to rebuild indexes,
> which my boss thinks is the reason the db is so very slow.
> It backs up the db without any problem, but it cannot
> back up the transaction log...
> As you can probably tell, I am fairly new to SQL server...
> Anything I can do to fix it?
> >--Original Message--
> >What errors are being reported by sqlmaint - should be
> in one of the txt filesin the sql server logs directory.
> Youcan find the filename buy looking at the details of
> the scheduled job that runs the optimisatons job.
> >
> >If the optimisations job i snever running it will man
> indexes etc may be less than optimal but your
> phrase "extremely slow" makes me suspect something else
> is wrong as well.
> >
> >Mike John
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> I cannot make the database optimize properly. I have
> a
> >> maintenance plan setup specifically to do just that,
> but
> >> it is never able to run and our database is becoming
> >> extremely slow. Any ideas?
> >.
> >|||Kristen
Have you tried using dbcc showcontig, this will show you
if your tables are fragmented. (See BOL for details). If
they are bably fragmented you will know the main plan is
not working.
Another thought is that in your maint plan have you chosen
the option 'attempt to repair minor problems' if you have
remove it. This regularly causes problems with maint
plans. Any open connections and your plan will not run
properly with this option.
Hope this helps
John|||The reason I know it is not rebuilding indexes is because
the history tells me so. There is always a red X next to
rebuild indexes indicating it was not done. I looked
through every error log at the times it reported that
rebuilding indexes was not done and it said nothing about
rebuilding indexes. That is why I say there is no error
message. If there is a different place I should be
looking please tell me because I do not know what an rtp
file is.
>--Original Message--
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>How do you know it isn't rebuilding indexes if there are
no error messages?
>We need the error message from the rtp file.
>
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>That is probably not related. My guess is that the
database is in simple
>recovery mode. read more in Books Online about backup to
understand recovery
>mode and ability to do log backup.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> As far as I can tell, there are no errors, it is just
not
>> doing it. In the History file, it tells me that it is
>> checking the db, but it is not able to rebuild indexes,
>> which my boss thinks is the reason the db is so very
slow.
>> It backs up the db without any problem, but it cannot
>> back up the transaction log...
>> As you can probably tell, I am fairly new to SQL
server...
>> Anything I can do to fix it?
>> >--Original Message--
>> >What errors are being reported by sqlmaint - should be
>> in one of the txt filesin the sql server logs
directory.
>> Youcan find the filename buy looking at the details of
>> the scheduled job that runs the optimisatons job.
>> >
>> >If the optimisations job i snever running it will man
>> indexes etc may be less than optimal but your
>> phrase "extremely slow" makes me suspect something else
>> is wrong as well.
>> >
>> >Mike John
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
>> >> I cannot make the database optimize properly. I
have
>> a
>> >> maintenance plan setup specifically to do just that,
>> but
>> >> it is never able to run and our database is becoming
>> >> extremely slow. Any ideas?
>> >.
>> >
>
>.
>|||Hi,
Thanks for the suggestion about the showcontig. I will
try that later. I checked the 'attempt to repair minor
problems' in all three of our maint plans and none of
them have this option checked.
Thanks,
Kristen
>--Original Message--
>Kristen
>Have you tried using dbcc showcontig, this will show you
>if your tables are fragmented. (See BOL for details). If
>they are bably fragmented you will know the main plan is
>not working.
>Another thought is that in your maint plan have you
chosen
>the option 'attempt to repair minor problems' if you
have
>remove it. This regularly causes problems with maint
>plans. Any open connections and your plan will not run
>properly with this option.
>Hope this helps
>John
>.
>|||The main wizard can create a report file. Check the config of the maint
plan, make sure that you have specified a report file and check that report
file for error messages.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> The reason I know it is not rebuilding indexes is because
> the history tells me so. There is always a red X next to
> rebuild indexes indicating it was not done. I looked
> through every error log at the times it reported that
> rebuilding indexes was not done and it said nothing about
> rebuilding indexes. That is why I say there is no error
> message. If there is a different place I should be
> looking please tell me because I do not know what an rtp
> file is.
>
> >--Original Message--
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >
> >How do you know it isn't rebuilding indexes if there are
> no error messages?
> >We need the error message from the rtp file.
> >
> >
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >
> >That is probably not related. My guess is that the
> database is in simple
> >recovery mode. read more in Books Online about backup to
> understand recovery
> >mode and ability to do log backup.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> As far as I can tell, there are no errors, it is just
> not
> >> doing it. In the History file, it tells me that it is
> >> checking the db, but it is not able to rebuild indexes,
> >> which my boss thinks is the reason the db is so very
> slow.
> >>
> >> It backs up the db without any problem, but it cannot
> >> back up the transaction log...
> >>
> >> As you can probably tell, I am fairly new to SQL
> server...
> >>
> >> Anything I can do to fix it?
> >>
> >> >--Original Message--
> >> >What errors are being reported by sqlmaint - should be
> >> in one of the txt filesin the sql server logs
> directory.
> >> Youcan find the filename buy looking at the details of
> >> the scheduled job that runs the optimisatons job.
> >> >
> >> >If the optimisations job i snever running it will man
> >> indexes etc may be less than optimal but your
> >> phrase "extremely slow" makes me suspect something else
> >> is wrong as well.
> >> >
> >> >Mike John
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message news:025e01c3a7d4$d3af1d00$a501280a@.phx.gbl...
> >> >> I cannot make the database optimize properly. I
> have
> >> a
> >> >> maintenance plan setup specifically to do just that,
> >> but
> >> >> it is never able to run and our database is becoming
> >> >> extremely slow. Any ideas?
> >> >.
> >> >
> >
> >
> >.
> >|||Thank you!
Here it is:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What does it mean?
>--Original Message--
>The main wizard can create a report file. Check the
config of the maint
>plan, make sure that you have specified a report file
and check that report
>file for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Kristen" <anonymous@.discussions.microsoft.com> wrote in
message
>news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
>> The reason I know it is not rebuilding indexes is
because
>> the history tells me so. There is always a red X next
to
>> rebuild indexes indicating it was not done. I looked
>> through every error log at the times it reported that
>> rebuilding indexes was not done and it said nothing
about
>> rebuilding indexes. That is why I say there is no
error
>> message. If there is a different place I should be
>> looking please tell me because I do not know what an
rtp
>> file is.
>>
>> >--Original Message--
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >
>> >How do you know it isn't rebuilding indexes if there
are
>> no error messages?
>> >We need the error message from the rtp file.
>> >
>> >
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >
>> >That is probably not related. My guess is that the
>> database is in simple
>> >recovery mode. read more in Books Online about backup
to
>> understand recovery
>> >mode and ability to do log backup.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
>> >> As far as I can tell, there are no errors, it is
just
>> not
>> >> doing it. In the History file, it tells me that it
is
>> >> checking the db, but it is not able to rebuild
indexes,
>> >> which my boss thinks is the reason the db is so very
>> slow.
>> >>
>> >> It backs up the db without any problem, but it
cannot
>> >> back up the transaction log...
>> >>
>> >> As you can probably tell, I am fairly new to SQL
>> server...
>> >>
>> >> Anything I can do to fix it?
>> >>
>> >> >--Original Message--
>> >> >What errors are being reported by sqlmaint -
should be
>> >> in one of the txt filesin the sql server logs
>> directory.
>> >> Youcan find the filename buy looking at the details
of
>> >> the scheduled job that runs the optimisatons job.
>> >> >
>> >> >If the optimisations job i snever running it will
man
>> >> indexes etc may be less than optimal but your
>> >> phrase "extremely slow" makes me suspect something
else
>> >> is wrong as well.
>> >> >
>> >> >Mike John
>> >> >
>> >> >
>> >> >"Kristen" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> message news:025e01c3a7d4$d3af1d00
$a501280a@.phx.gbl...
>> >> >> I cannot make the database optimize properly. I
>> have
>> >> a
>> >> >> maintenance plan setup specifically to do just
that,
>> >> but
>> >> >> it is never able to run and our database is
becoming
>> >> >> extremely slow. Any ideas?
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||It means that you either has a view with index on or a computed column with
an index on. And for these to be manipulated (index rebuilt in this case),
the session which executes that manipulation command (maintenance wizard
executing DBCC DBREINDEX in this case) need to have the SET commands
mentioned in the error message turned on. Unfortunately, the maint wizard
doesn't do that. Your options it to remove the reorg from the maint wizard
and do it yourself from an Agent TSQL jobstep (having the correct SET
options), or remove those indexes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kristen" <anonymous@.discussions.microsoft.com> wrote in message
news:047f01c3a894$427adc00$a301280a@.phx.gbl...
> Thank you!
> Here it is:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
> failed because the following SET options have incorrect
> settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What does it mean?
> >--Original Message--
> >The main wizard can create a report file. Check the
> config of the maint
> >plan, make sure that you have specified a report file
> and check that report
> >file for error messages.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Kristen" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:024701c3a87a$cdfe2fd0$a301280a@.phx.gbl...
> >> The reason I know it is not rebuilding indexes is
> because
> >> the history tells me so. There is always a red X next
> to
> >> rebuild indexes indicating it was not done. I looked
> >> through every error log at the times it reported that
> >> rebuilding indexes was not done and it said nothing
> about
> >> rebuilding indexes. That is why I say there is no
> error
> >> message. If there is a different place I should be
> >> looking please tell me because I do not know what an
> rtp
> >> file is.
> >>
> >>
> >> >--Original Message--
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >
> >> >How do you know it isn't rebuilding indexes if there
> are
> >> no error messages?
> >> >We need the error message from the rtp file.
> >> >
> >> >
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >
> >> >That is probably not related. My guess is that the
> >> database is in simple
> >> >recovery mode. read more in Books Online about backup
> to
> >> understand recovery
> >> >mode and ability to do log backup.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Kristen" <anonymous@.discussions.microsoft.com> wrote
> in
> >> message
> >> >news:0b3d01c3a7df$dc7d7160$a001280a@.phx.gbl...
> >> >> As far as I can tell, there are no errors, it is
> just
> >> not
> >> >> doing it. In the History file, it tells me that it
> is
> >> >> checking the db, but it is not able to rebuild
> indexes,
> >> >> which my boss thinks is the reason the db is so very
> >> slow.
> >> >>
> >> >> It backs up the db without any problem, but it
> cannot
> >> >> back up the transaction log...
> >> >>
> >> >> As you can probably tell, I am fairly new to SQL
> >> server...
> >> >>
> >> >> Anything I can do to fix it?
> >> >>
> >> >> >--Original Message--
> >> >> >What errors are being reported by sqlmaint -
> should be
> >> >> in one of the txt filesin the sql server logs
> >> directory.
> >> >> Youcan find the filename buy looking at the details
> of
> >> >> the scheduled job that runs the optimisatons job.
> >> >> >
> >> >> >If the optimisations job i snever running it will
> man
> >> >> indexes etc may be less than optimal but your
> >> >> phrase "extremely slow" makes me suspect something
> else
> >> >> is wrong as well.
> >> >> >
> >> >> >Mike John
> >> >> >
> >> >> >
> >> >> >"Kristen" <anonymous@.discussions.microsoft.com>
> wrote
> >> in
> >> >> message news:025e01c3a7d4$d3af1d00
> $a501280a@.phx.gbl...
> >> >> >> I cannot make the database optimize properly. I
> >> have
> >> >> a
> >> >> >> maintenance plan setup specifically to do just
> that,
> >> >> but
> >> >> >> it is never able to run and our database is
> becoming
> >> >> >> extremely slow. Any ideas?
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Monday, March 12, 2012
Optimal Placement of Data/Log Files
What is the best way to setup a server for hosting SQL Sever 2005? If I could get input of type of RAID to use and where to place the files that would be very appreciated.
Thanks,
Todd Sparks
Hey Todd. Well, ideally we'd always recommend RAID 10 if possible for both data and log file LUNs for performance and availability reasons. If that's not possible due to cost, I'd recommend a RAID 1 configuration for your log file and a RAID 5 for data files...ideally, if cost is not prohibitive again RAID 1 for data files would probaby be better than 5.
For a more in depth look at the internals of SQL Server IO see the following paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
In addition, check out the following paper on disk subsystem performance:
http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx
HTH
Optimal Drive Performance
I am getting ready to setup our first SQL server in house. I have been
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
can.
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
can.
Memory - as suggested, memory is relatively inexpensive these days and datab
ase servers are generally pretty memory and disk intensive. It really depend
s on the usage profile and DB size but 4GB is not really *that* expensive.
Disk config: Disk config is very important as it can make a major difference
to the performance of the database.
Here are some general recommendations but again this depends on the app and
how it will be used.
RAID 5: Good fault tolerance, good read performance, BAD write performance (
parity has to be written every time the disk is written to). Use this if the
DB is read intensive with few writes. RAID 5 offers good fault tolerance at
a relatively low cost.
RAID 1: Good fault tolerance, and good performance for sequential writes (su
ch as transaction logs). Expensive because you only have effective use of ha
lf the disk.
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent per
formance, but NO fault tolerance.
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms
interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example
if you have 8 disks, it would stripe data across 4 mirrors. This offers exc
ellent performance, excellent fault tolerance but a not-so-excellent bank ba
lance!
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, yo
u would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirro
red. This also offers good performance and fault tolerance (as RAID 1+0) but
this will be degraded in t
he event of disk failures (much more than RAID 1+0).
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always cho
ose RAID 1+0.
An example of a high-end disk spec would be:
OS: 2 x 18.2GB RAID1
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+0
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of
2, the total number constrained by the storage device)
This may or may not be an overkill depending on the actual app.
In terms of controllers, separate controllers sounds like a good idea - agai
n its down to the cost/benefit of doing this.
I hope this helps as a very general guideline.
Regards,
Rob
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
quote:|||Andrew J. Kelly wrote:
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus
quote:
> Memory is too cheap these days to not have enough. How large do you expec
t
> your DB to get and of that how much of the data would be read or written t
o
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
quote:
> Andrew J. Kelly wrote:
expect[QUOTE]
to[QUOTE]
you[QUOTE]
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
quote:|||Well it's imposable to tell if any of those configurations will ultimately
> Thanks,
> John Jakus
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
quote:
> Andrew J. Kelly wrote:
expect[QUOTE]
to[QUOTE]
you[QUOTE]
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
quote:|||To try and cover your issues separately:
> Thanks,
> John Jakus
Memory - as suggested, memory is relatively inexpensive these days and datab
ase servers are generally pretty memory and disk intensive. It really depend
s on the usage profile and DB size but 4GB is not really *that* expensive.
Disk config: Disk config is very important as it can make a major difference
to the performance of the database.
Here are some general recommendations but again this depends on the app and
how it will be used.
RAID 5: Good fault tolerance, good read performance, BAD write performance (
parity has to be written every time the disk is written to). Use this if the
DB is read intensive with few writes. RAID 5 offers good fault tolerance at
a relatively low cost.
RAID 1: Good fault tolerance, and good performance for sequential writes (su
ch as transaction logs). Expensive because you only have effective use of ha
lf the disk.
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent per
formance, but NO fault tolerance.
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms
interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example
if you have 8 disks, it would stripe data across 4 mirrors. This offers exc
ellent performance, excellent fault tolerance but a not-so-excellent bank ba
lance!
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, yo
u would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirro
red. This also offers good performance and fault tolerance (as RAID 1+0) but
this will be degraded in t
he event of disk failures (much more than RAID 1+0).
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always cho
ose RAID 1+0.
An example of a high-end disk spec would be:
OS: 2 x 18.2GB RAID1
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+0
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of
2, the total number constrained by the storage device)
This may or may not be an overkill depending on the actual app.
In terms of controllers, separate controllers sounds like a good idea - agai
n its down to the cost/benefit of doing this.
I hope this helps as a very general guideline.
Regards,
Rob
Optimal Drive Performance
I am getting ready to setup our first SQL server in house. I have been
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus|||Andrew J. Kelly wrote:
> Memory is too cheap these days to not have enough. How large do you expect
> your DB to get and of that how much of the data would be read or written to
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||Well it's imposable to tell if any of those configurations will ultimately
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||To try and cover your issues separately
Memory - as suggested, memory is relatively inexpensive these days and database servers are generally pretty memory and disk intensive. It really depends on the usage profile and DB size but 4GB is not really *that* expensive
Disk config: Disk config is very important as it can make a major difference to the performance of the database
Here are some general recommendations but again this depends on the app and how it will be used
RAID 5: Good fault tolerance, good read performance, BAD write performance (parity has to be written every time the disk is written to). Use this if the DB is read intensive with few writes. RAID 5 offers good fault tolerance at a relatively low cost
RAID 1: Good fault tolerance, and good performance for sequential writes (such as transaction logs). Expensive because you only have effective use of half the disk
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent performance, but NO fault tolerance
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example if you have 8 disks, it would stripe data across 4 mirrors. This offers excellent performance, excellent fault tolerance but a not-so-excellent bank balance
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, you would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirrored. This also offers good performance and fault tolerance (as RAID 1+0) but this will be degraded in the event of disk failures (much more than RAID 1+0)
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always choose RAID 1+0
An example of a high-end disk spec would be
OS: 2 x 18.2GB RAID
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of 2, the total number constrained by the storage device
This may or may not be an overkill depending on the actual app
In terms of controllers, separate controllers sounds like a good idea - again its down to the cost/benefit of doing this
I hope this helps as a very general guideline
Regards
Ro
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus|||Andrew J. Kelly wrote:
> Memory is too cheap these days to not have enough. How large do you expect
> your DB to get and of that how much of the data would be read or written to
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||Well it's imposable to tell if any of those configurations will ultimately
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||To try and cover your issues separately
Memory - as suggested, memory is relatively inexpensive these days and database servers are generally pretty memory and disk intensive. It really depends on the usage profile and DB size but 4GB is not really *that* expensive
Disk config: Disk config is very important as it can make a major difference to the performance of the database
Here are some general recommendations but again this depends on the app and how it will be used
RAID 5: Good fault tolerance, good read performance, BAD write performance (parity has to be written every time the disk is written to). Use this if the DB is read intensive with few writes. RAID 5 offers good fault tolerance at a relatively low cost
RAID 1: Good fault tolerance, and good performance for sequential writes (such as transaction logs). Expensive because you only have effective use of half the disk
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent performance, but NO fault tolerance
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example if you have 8 disks, it would stripe data across 4 mirrors. This offers excellent performance, excellent fault tolerance but a not-so-excellent bank balance
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, you would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirrored. This also offers good performance and fault tolerance (as RAID 1+0) but this will be degraded in the event of disk failures (much more than RAID 1+0)
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always choose RAID 1+0
An example of a high-end disk spec would be
OS: 2 x 18.2GB RAID
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of 2, the total number constrained by the storage device
This may or may not be an overkill depending on the actual app
In terms of controllers, separate controllers sounds like a good idea - again its down to the cost/benefit of doing this
I hope this helps as a very general guideline
Regards
Ro
Friday, March 9, 2012
Opinions for best setup
We have a web server which needs instant results from our SQL Server.
We also have a lot of reports that are run at various times of the day
that hit against a few multi-million record tables and it bogs the SQL
server down. This in turn slows the response of the web server.
Can I setup two SQL Servers with replication or clustering or something
and have the web server hit one SQL server and the reports hit the
other server? Would the reports still bog down the web requests in
this setup (if it's possible)?
Other suggestions are welcome.
Thanks,
CYou can do this a number of ways depending on how up to date the reports
need to be. If you can live with data for the reports that is not real time
you can use log shipping or snapshot replication from the main SQL Server to
another server for the reporting. If it needs to be near real time then you
can use transactional replication. You can find more details on each in
BooksOnLine.
Andrew J. Kelly SQL MVP
"The Cornjerker" <addoty@.gmail.com> wrote in message
news:1131376846.542603.182480@.g44g2000cwa.googlegroups.com...
> We have a web server which needs instant results from our SQL Server.
> We also have a lot of reports that are run at various times of the day
> that hit against a few multi-million record tables and it bogs the SQL
> server down. This in turn slows the response of the web server.
>
> Can I setup two SQL Servers with replication or clustering or something
> and have the web server hit one SQL server and the reports hit the
> other server? Would the reports still bog down the web requests in
> this setup (if it's possible)?
>
> Other suggestions are welcome.
>
> Thanks,
> C
>
We also have a lot of reports that are run at various times of the day
that hit against a few multi-million record tables and it bogs the SQL
server down. This in turn slows the response of the web server.
Can I setup two SQL Servers with replication or clustering or something
and have the web server hit one SQL server and the reports hit the
other server? Would the reports still bog down the web requests in
this setup (if it's possible)?
Other suggestions are welcome.
Thanks,
CYou can do this a number of ways depending on how up to date the reports
need to be. If you can live with data for the reports that is not real time
you can use log shipping or snapshot replication from the main SQL Server to
another server for the reporting. If it needs to be near real time then you
can use transactional replication. You can find more details on each in
BooksOnLine.
Andrew J. Kelly SQL MVP
"The Cornjerker" <addoty@.gmail.com> wrote in message
news:1131376846.542603.182480@.g44g2000cwa.googlegroups.com...
> We have a web server which needs instant results from our SQL Server.
> We also have a lot of reports that are run at various times of the day
> that hit against a few multi-million record tables and it bogs the SQL
> server down. This in turn slows the response of the web server.
>
> Can I setup two SQL Servers with replication or clustering or something
> and have the web server hit one SQL server and the reports hit the
> other server? Would the reports still bog down the web requests in
> this setup (if it's possible)?
>
> Other suggestions are welcome.
>
> Thanks,
> C
>
Wednesday, March 7, 2012
Operationg System Command (CmdExec) job failing
I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainName\SQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine.
The job just copies files from one directory to another, here's the code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
When run as a job, with the owner of the job being DomainName\SQLAdmin the job fails with the following error message: Executed as user: DomainName\SQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
Anyone have an idea whats going on?It sounds like there could be multiple reasons for the access denied issue, but an odd one which I just found out about follows:
I was just trying to restore a db and I was receiving an Access denied error. I ended up finding out that some of the parent folders were in 'read only' mode. After I disabled it, everything worked fine.
Good luck,
Hope this helped|||what account is listed as the job owner?|||The account that owns the job, DomainName\SQLAdmin, is also the account that belongs to the Administrator group on the server and Stops and Starts the MSSQLSERVER/SQLSERVERAGENT services. As far as I can tell this account has all the permissions possible.
Also checked permissions on all parent folders none are marked read-only.|||I found a work around but it doesn't explain why my original job failed. What I did was create a batch file with the following code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
I then stored it on the C: drive and called the batch from within the job and this worked. So anyone have an idea what's going on? I'm sure it's a permissions thing but I can't explain it.
The job just copies files from one directory to another, here's the code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
When run as a job, with the owner of the job being DomainName\SQLAdmin the job fails with the following error message: Executed as user: DomainName\SQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
Anyone have an idea whats going on?It sounds like there could be multiple reasons for the access denied issue, but an odd one which I just found out about follows:
I was just trying to restore a db and I was receiving an Access denied error. I ended up finding out that some of the parent folders were in 'read only' mode. After I disabled it, everything worked fine.
Good luck,
Hope this helped|||what account is listed as the job owner?|||The account that owns the job, DomainName\SQLAdmin, is also the account that belongs to the Administrator group on the server and Stops and Starts the MSSQLSERVER/SQLSERVERAGENT services. As far as I can tell this account has all the permissions possible.
Also checked permissions on all parent folders none are marked read-only.|||I found a work around but it doesn't explain why my original job failed. What I did was create a batch file with the following code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
I then stored it on the C: drive and called the batch from within the job and this worked. So anyone have an idea what's going on? I'm sure it's a permissions thing but I can't explain it.
Subscribe to:
Posts (Atom)