Hi friends,
i want to know something abt sql server installation and database properties as under.
1) how much disk space reqd by multiuser sqlserver 2000 db to install ?
2) is it possible to install sql 2K on Flash Drive ( gen connect at USB) ?
3) When v create a database user Enterprise Manager , which files created by database ?
4) If i Know datafiles name of a database e.g. pubs , and if v copied on cd and paste on other system which has sql 2K install , does those database works ?
5) is it possible to create database on flash drive ?
thanx in advance.Come on...are these trick questions?|||I suppose it could be done, but you would have to be very sure that you properly detach the database before someone goes and unplugs the USB drive. I think you would set yourself up for a heck of a lot more user errors than SQL Server errors this way.
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Thursday, March 29, 2012
Data Files and one FileGroup
Hi,
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
RosieRosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>|||inline
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.
> Thanks,
> Rosie
>
>|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 file per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw dependent.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism. One thing to look out for is for best performance you
> want the files to file at the same rate. Autogrow and adding additional files often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>> Hi,
>> Is it OK if we have one FileGroup but 3 data files in different disk drives?
>> We have a database of size 150G, two data fils are in the same disk drive
>> and the third data file is in a different disk driver. All are in the same
>> filegroup.
>> Shouldn't we create another file group for the third data file?
>> Thanks,
>> Rosie
>>
>
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
RosieRosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>|||inline
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.
> Thanks,
> Rosie
>
>|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 file per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw dependent.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism. One thing to look out for is for best performance you
> want the files to file at the same rate. Autogrow and adding additional files often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>> Hi,
>> Is it OK if we have one FileGroup but 3 data files in different disk drives?
>> We have a database of size 150G, two data fils are in the same disk drive
>> and the third data file is in a different disk driver. All are in the same
>> filegroup.
>> Shouldn't we create another file group for the third data file?
>> Thanks,
>> Rosie
>>
>
Data Files and one FileGroup
Hi,
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
Rosie
Rosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>
|||inline
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.
> Thanks,
> Rosie
>
>
|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 file per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw dependent.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism. One thing to look out for is for best performance you
> want the files to file at the same rate. Autogrow and adding additional files often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
Rosie
Rosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>
|||inline
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.
> Thanks,
> Rosie
>
>
|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 file per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw dependent.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recommends one file per
> physical processor to help parrallelism. One thing to look out for is for best performance you
> want the files to file at the same rate. Autogrow and adding additional files often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>
Data Files and one FileGroup
Hi,
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
RosieRosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>|||inline
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.
> Thanks,
> Rosie
>
>|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file pe
r
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing
I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 fil
e per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw depende
nt.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...seagreen">
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recomm
ends one file per
> physical processor to help parrallelism. One thing to look out for is for
best performance you
> want the files to file at the same rate. Autogrow and adding additional f
iles often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>
Is it OK if we have one FileGroup but 3 data files in different disk drives?
We have a database of size 150G, two data fils are in the same disk drive
and the third data file is in a different disk driver. All are in the same
filegroup.
Shouldn't we create another file group for the third data file?
Thanks,
RosieRosie,
You're probably fine. Multiple files in a filegroup is common. MS
recommends one file per physical processor to help parrallelism. One thing
to look out for is for best performance you want the files to file at the
same rate. Autogrow and adding additional files often leads to hot spots on
the IO subsystem.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
> Shouldn't we create another file group for the third data file?
> Thanks,
> Rosie
>
>|||inline
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosie" <Rosie@.discussions.microsoft.com> wrote in message
news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
> Hi,
> Is it OK if we have one FileGroup but 3 data files in different disk
> drives?
sunila> Yes. This will help you distribute the IO load across multiple
spindles.
> We have a database of size 150G, two data fils are in the same disk drive
> and the third data file is in a different disk driver. All are in the same
> filegroup.
Sunila> I will be curious to know why created two files belonging to same
file group on the same disk?
> Shouldn't we create another file group for the third data file?
Sunila> SQL Server allows creating file group that contains files from one
or more physical disks.
> Thanks,
> Rosie
>
>|||> You're probably fine. Multiple files in a filegroup is common. MS recommends one file pe
r
> physical processor to help parrallelism.
Interesting... Do you have a link to that statement? So far, the only thing
I've seen is that you
might want to consider the number of physical disks. Somewhere between 1 fil
e per disk to 1 file per
8 disks. MS haven't given out a firm recommendation as it is very hw depende
nt.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Danny" <someone@.nowhere.com> wrote in message news:CIPef.19373$%t4.15694@.trnddc07...seagreen">
> Rosie,
> You're probably fine. Multiple files in a filegroup is common. MS recomm
ends one file per
> physical processor to help parrallelism. One thing to look out for is for
best performance you
> want the files to file at the same rate. Autogrow and adding additional f
iles often leads to hot
> spots on the IO subsystem.
>
> "Rosie" <Rosie@.discussions.microsoft.com> wrote in message
> news:D194C8A5-5B94-4CBA-A994-4E78EA69E6B9@.microsoft.com...
>
Wednesday, March 7, 2012
Data and log sits on different disk drive.
Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.
Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.
Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Data and log sits on different disk drive.
Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Data and log sits on different disk drive.
Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different d
isk drive? Is there any benefit of doing it in term of performance? How abou
t backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Can I create a database with the .mdf (data) and .ldf (log) on 2 different d
isk drive? Is there any benefit of doing it in term of performance? How abou
t backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
Saturday, February 25, 2012
Data and log files on separate disks
The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
Data and log files on separate disks
The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> > The production database of one of our clients is set up with both the data
> > and log files on the same disk. We suggested that they should separate
> > them
> > out to eliminate disk contention and to eliminate I/O problems. The DBA at
> > the client site came back saying that he does not believe this is
> > necessary.
> > Are we wrong in suggesting that the data and log files should be on
> > separate
> > disks?
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
> > The client database is about 20 GB.
> > Thanks in advance.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
>> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
>> > The production database of one of our clients is set up with both the
>> > data
>> > and log files on the same disk. We suggested that they should separate
>> > them
>> > out to eliminate disk contention and to eliminate I/O problems. The DBA
>> > at
>> > the client site came back saying that he does not believe this is
>> > necessary.
>> > Are we wrong in suggesting that the data and log files should be on
>> > separate
>> > disks?
>> No, you're not.
>> However, whether it actually helps their performance is a good question.
>> If it's small enough and low volume enough, it won't help there.
>> If say the machine can only support 2 disks in a RAID 1 config, this
>> solution is "ok" given the machine.
>> If the machine can support more RAIDs (say 4 disks) then they're probably
>> better off separating them just because then if two disks fail, they're
>> less
>> likely to actually suffer data loss.
>>
>> > The client database is about 20 GB.
>> > Thanks in advance.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> > Thanks for the reply. As I mentioned the database is about 20GB and there is
> > plenty of activity during office hours and during other scheduled task runs.
> > Do you still believe separating the data and log files will improve some of
> > the bottlenecks.
> > Thanks
> >
> >
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> > The production database of one of our clients is set up with both the data
> > and log files on the same disk. We suggested that they should separate
> > them
> > out to eliminate disk contention and to eliminate I/O problems. The DBA at
> > the client site came back saying that he does not believe this is
> > necessary.
> > Are we wrong in suggesting that the data and log files should be on
> > separate
> > disks?
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're less
> likely to actually suffer data loss.
>
> > The client database is about 20 GB.
> > Thanks in advance.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
>> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
>> > The production database of one of our clients is set up with both the
>> > data
>> > and log files on the same disk. We suggested that they should separate
>> > them
>> > out to eliminate disk contention and to eliminate I/O problems. The DBA
>> > at
>> > the client site came back saying that he does not believe this is
>> > necessary.
>> > Are we wrong in suggesting that the data and log files should be on
>> > separate
>> > disks?
>> No, you're not.
>> However, whether it actually helps their performance is a good question.
>> If it's small enough and low volume enough, it won't help there.
>> If say the machine can only support 2 disks in a RAID 1 config, this
>> solution is "ok" given the machine.
>> If the machine can support more RAIDs (say 4 disks) then they're probably
>> better off separating them just because then if two disks fail, they're
>> less
>> likely to actually suffer data loss.
>>
>> > The client database is about 20 GB.
>> > Thanks in advance.
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there is
> plenty of activity during office hours and during other scheduled task runs.
> Do you still believe separating the data and log files will improve some of
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> > Thanks for the reply. As I mentioned the database is about 20GB and there is
> > plenty of activity during office hours and during other scheduled task runs.
> > Do you still believe separating the data and log files will improve some of
> > the bottlenecks.
> > Thanks
> >
> >
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
Data and log files on separate disks
The production database of one of our clients is set up with both the data
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're le
ss
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there
is
> plenty of activity during office hours and during other scheduled task run
s.
> Do you still believe separating the data and log files will improve some o
f
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
and log files on the same disk. We suggested that they should separate them
out to eliminate disk contention and to eliminate I/O problems. The DBA at
the client site came back saying that he does not believe this is necessary.
Are we wrong in suggesting that the data and log files should be on separate
disks?
The client database is about 20 GB.
Thanks in advance."Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> The production database of one of our clients is set up with both the data
> and log files on the same disk. We suggested that they should separate
> them
> out to eliminate disk contention and to eliminate I/O problems. The DBA at
> the client site came back saying that he does not believe this is
> necessary.
> Are we wrong in suggesting that the data and log files should be on
> separate
> disks?
No, you're not.
However, whether it actually helps their performance is a good question.
If it's small enough and low volume enough, it won't help there.
If say the machine can only support 2 disks in a RAID 1 config, this
solution is "ok" given the machine.
If the machine can support more RAIDs (say 4 disks) then they're probably
better off separating them just because then if two disks fail, they're less
likely to actually suffer data loss.
> The client database is about 20 GB.
> Thanks in advance.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the reply. As I mentioned the database is about 20GB and there is
plenty of activity during office hours and during other scheduled task runs.
Do you still believe separating the data and log files will improve some of
the bottlenecks.
Thanks
"Greg D. Moore (Strider)" wrote:
> "Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
> news:80F4426D-0326-40D5-9EF4-B42853066EE6@.microsoft.com...
> No, you're not.
> However, whether it actually helps their performance is a good question.
> If it's small enough and low volume enough, it won't help there.
> If say the machine can only support 2 disks in a RAID 1 config, this
> solution is "ok" given the machine.
> If the machine can support more RAIDs (say 4 disks) then they're probably
> better off separating them just because then if two disks fail, they're le
ss
> likely to actually suffer data loss.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:9ABCC48E-C789-482F-8F42-D02AB663E307@.microsoft.com...
> Thanks for the reply. As I mentioned the database is about 20GB and there
> is
> plenty of activity during office hours and during other scheduled task
> runs.
> Do you still believe separating the data and log files will improve some
> of
> the bottlenecks.
Is it experiencing disk I/O bottlenecks?
If so, it would probably help. But again. 20GB and 'plenty of activity'
doesn't really tell enough to say for sure.
[vbcol=seagreen]
> Thanks
>
> "Greg D. Moore (Strider)" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Frank1213 wrote:
> Thanks for the reply. As I mentioned the database is about 20GB and there
is
> plenty of activity during office hours and during other scheduled task run
s.
> Do you still believe separating the data and log files will improve some o
f
> the bottlenecks.
> Thanks
>
Hi,
Maybe you should ask is the disk is in their SAN. It's true that in most
cases it's good practice to have your database and logfiles on seperate
disk - both for performance reasons but also for disaster recovery
reasons. There are a few cases where this doesn't really make any
difference though. If they e.g. are using a HP EVA SAN there isn't any
performance reason to have the files on different disks because in the
end these disk will share the same physical spindles. The EVA SAN simply
puts all the disks in one big diskgroup and then on top of that you can
create your virtual disks. This means that if you create 2 virtual disks
and present to a server, the server will see it as 2 disks. In the end
it's not any different than just creating 1 virtual disk and put
everything on that single virtual disk - it's still the same physical
spindles it puts the data on.
If this is the case with your customer that could be the reason for why
the DBA answers like he do.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Thanks to both of you for your answers.
""Steen Schlüter Persson (DK)"" wrote:
> Frank1213 wrote:
> Hi,
> Maybe you should ask is the disk is in their SAN. It's true that in most
> cases it's good practice to have your database and logfiles on seperate
> disk - both for performance reasons but also for disaster recovery
> reasons. There are a few cases where this doesn't really make any
> difference though. If they e.g. are using a HP EVA SAN there isn't any
> performance reason to have the files on different disks because in the
> end these disk will share the same physical spindles. The EVA SAN simply
> puts all the disks in one big diskgroup and then on top of that you can
> create your virtual disks. This means that if you create 2 virtual disks
> and present to a server, the server will see it as 2 disks. In the end
> it's not any different than just creating 1 virtual disk and put
> everything on that single virtual disk - it's still the same physical
> spindles it puts the data on.
> If this is the case with your customer that could be the reason for why
> the DBA answers like he do.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
Friday, February 24, 2012
Damaged databases
Hi
I recently had a hard disk failure on which was running Win2003 SQL2000 SP4.
This server is a sharepoint backend server, I repaired the disk to the point that it could be mirrored across to a new and then set about looking at the databases. There was a schedule in place to backup all 4 DBs daily, the backup process at no time after verifying the backup had any errors. All but one of the DBs reports this error when I attempt to run DBCC or similar query I/O error (bad page ID) detected during read at offset etc. (error 823) I have restored back to the earliest DBs I have and I still get the same error.
Any ideas on how I can recover as much as possible if not all of the DBs? What is the best way to restore from a transaction log? I have found a few articles on it and they all take a slightly different approach for differnet situations however I'm not entirely sure which applies to me.
All help will be very much appreciated.
Rob
Sounds like your backups are corrupt too - I suspect that the disk hardware was going bad for a while and all the backups have backed up corrupted data. Does the backup process backup the databases to the same disk hardware? How often do you run DBCC CHECKDB on your databases to verify their integrity?
You can't recover from just a transaction log - you need to restore a full backup first. What's the DBCC error you get (post the output of "DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS")? Your only option may be to extract what data you can into a new database.
Thanks
Damaged databases
Hi
I recently had a hard disk failure on which was running Win2003 SQL2000 SP4.
This server is a sharepoint backend server, I repaired the disk to the point that it could be mirrored across to a new and then set about looking at the databases. There was a schedule in place to backup all 4 DBs daily, the backup process at no time after verifying the backup had any errors. All but one of the DBs reports this error when I attempt to run DBCC or similar query I/O error (bad page ID) detected during read at offset etc. (error 823) I have restored back to the earliest DBs I have and I still get the same error.
Any ideas on how I can recover as much as possible if not all of the DBs? What is the best way to restore from a transaction log? I have found a few articles on it and they all take a slightly different approach for differnet situations however I'm not entirely sure which applies to me.
All help will be very much appreciated.
Rob
Sounds like your backups are corrupt too - I suspect that the disk hardware was going bad for a while and all the backups have backed up corrupted data. Does the backup process backup the databases to the same disk hardware? How often do you run DBCC CHECKDB on your databases to verify their integrity?
You can't recover from just a transaction log - you need to restore a full backup first. What's the DBCC error you get (post the output of "DBCC CHECKDB (yourdb) WITH ALL_ERRORMSGS, NO_INFOMSGS")? Your only option may be to extract what data you can into a new database.
Thanks
Sunday, February 19, 2012
Daily backup problem
My SQL 2000 server's daily (full) backup started failing due to a lack of
disk space. This daily backup has been running for quite a while so im
surprised. The file system was full but it seems the old backup are not
being deleted even though the maintenance plan is set to ""Remove files older
than 1 day". Any ideas why this would happen? Solutions?
PaulOld fulls, or old t-log backups? If t-log make sure every db in the plan is
set to full recovery model. If you have databases in Simple recovery, make
a separate plan for them without the t-log backup step
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
> My SQL 2000 server's daily (full) backup started failing due to a lack of
> disk space. This daily backup has been running for quite a while so im
> surprised. The file system was full but it seems the old backup are not
> being deleted even though the maintenance plan is set to ""Remove files
> older
> than 1 day". Any ideas why this would happen? Solutions?
>
> Paul
>|||Old fulls.
I only do full backups.
"Kevin3NF" wrote:
> Old fulls, or old t-log backups? If t-log make sure every db in the plan is
> set to full recovery model. If you have databases in Simple recovery, make
> a separate plan for them without the t-log backup step
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >
> >
> > My SQL 2000 server's daily (full) backup started failing due to a lack of
> > disk space. This daily backup has been running for quite a while so im
> > surprised. The file system was full but it seems the old backup are not
> > being deleted even though the maintenance plan is set to ""Remove files
> > older
> > than 1 day". Any ideas why this would happen? Solutions?
> >
> >
> >
> > Paul
> >
>
>|||Paul,
The delete happens after the backup is taken. If you do not have enough
space for the backup it will not delete the old backup. Could this be your
problem?
Chris
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||Check to see if the account running the SQL Server Agent has permissions to
delete those fulls. Look for the maintenance plan logs in the \LOG
directory (same location as the ERRORLOG file(s) )
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||No there is enough space for 3 days of backups
"Chris Wood" wrote:
> Paul,
> The delete happens after the backup is taken. If you do not have enough
> space for the backup it will not delete the old backup. Could this be your
> problem?
> Chris
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account has full admin permission so that not a problem. Like I said this
has not been a problem for months. I don't see a log specifacally for
maintenance logs.
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account have the permissions to delete these file. No problem there. I
don't see a maintenance plan specific log file.
Paul
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||Add a "text file" option in the Reporting tab of the Database maintenance
GUI (SQL 2000)
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:8670769E-BF6B-4C1F-BE67-84C830DBF144@.microsoft.com...
> The account have the permissions to delete these file. No problem there. I
> don't see a maintenance plan specific log file.
> Paul
> "Kevin3NF" wrote:
>> Check to see if the account running the SQL Server Agent has permissions
>> to
>> delete those fulls. Look for the maintenance plan logs in the \LOG
>> directory (same location as the ERRORLOG file(s) )
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>> > Old fulls.
>> > I only do full backups.
>> >
>> > "Kevin3NF" wrote:
>> >
>> >> Old fulls, or old t-log backups? If t-log make sure every db in the
>> >> plan
>> >> is
>> >> set to full recovery model. If you have databases in Simple recovery,
>> >> make
>> >> a separate plan for them without the t-log backup step
>> >>
>> >> --
>> >>
>> >> Kevin3NF
>> >> SQL Server dude
>> >>
>> >> You want fries with that?
>> >> http://kevin3nf.blogspot.com/
>> >>
>> >> I only check the newsgroups during work hours, M-F.
>> >> Hit my blog and the contact links if necessary...I may be available.
>> >>
>> >>
>> >>
>> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >> >
>> >> >
>> >> > My SQL 2000 server's daily (full) backup started failing due to a
>> >> > lack
>> >> > of
>> >> > disk space. This daily backup has been running for quite a while so
>> >> > im
>> >> > surprised. The file system was full but it seems the old backup are
>> >> > not
>> >> > being deleted even though the maintenance plan is set to ""Remove
>> >> > files
>> >> > older
>> >> > than 1 day". Any ideas why this would happen? Solutions?
>> >> >
>> >> >
>> >> >
>> >> > Paul
>> >> >
>> >>
>> >>
>> >>
>>
disk space. This daily backup has been running for quite a while so im
surprised. The file system was full but it seems the old backup are not
being deleted even though the maintenance plan is set to ""Remove files older
than 1 day". Any ideas why this would happen? Solutions?
PaulOld fulls, or old t-log backups? If t-log make sure every db in the plan is
set to full recovery model. If you have databases in Simple recovery, make
a separate plan for them without the t-log backup step
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>
> My SQL 2000 server's daily (full) backup started failing due to a lack of
> disk space. This daily backup has been running for quite a while so im
> surprised. The file system was full but it seems the old backup are not
> being deleted even though the maintenance plan is set to ""Remove files
> older
> than 1 day". Any ideas why this would happen? Solutions?
>
> Paul
>|||Old fulls.
I only do full backups.
"Kevin3NF" wrote:
> Old fulls, or old t-log backups? If t-log make sure every db in the plan is
> set to full recovery model. If you have databases in Simple recovery, make
> a separate plan for them without the t-log backup step
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >
> >
> > My SQL 2000 server's daily (full) backup started failing due to a lack of
> > disk space. This daily backup has been running for quite a while so im
> > surprised. The file system was full but it seems the old backup are not
> > being deleted even though the maintenance plan is set to ""Remove files
> > older
> > than 1 day". Any ideas why this would happen? Solutions?
> >
> >
> >
> > Paul
> >
>
>|||Paul,
The delete happens after the backup is taken. If you do not have enough
space for the backup it will not delete the old backup. Could this be your
problem?
Chris
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||Check to see if the account running the SQL Server Agent has permissions to
delete those fulls. Look for the maintenance plan logs in the \LOG
directory (same location as the ERRORLOG file(s) )
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> Old fulls.
> I only do full backups.
> "Kevin3NF" wrote:
>> Old fulls, or old t-log backups? If t-log make sure every db in the plan
>> is
>> set to full recovery model. If you have databases in Simple recovery,
>> make
>> a separate plan for them without the t-log backup step
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >
>> >
>> > My SQL 2000 server's daily (full) backup started failing due to a lack
>> > of
>> > disk space. This daily backup has been running for quite a while so im
>> > surprised. The file system was full but it seems the old backup are
>> > not
>> > being deleted even though the maintenance plan is set to ""Remove files
>> > older
>> > than 1 day". Any ideas why this would happen? Solutions?
>> >
>> >
>> >
>> > Paul
>> >
>>|||No there is enough space for 3 days of backups
"Chris Wood" wrote:
> Paul,
> The delete happens after the backup is taken. If you do not have enough
> space for the backup it will not delete the old backup. Could this be your
> problem?
> Chris
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account has full admin permission so that not a problem. Like I said this
has not been a problem for months. I don't see a log specifacally for
maintenance logs.
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||The account have the permissions to delete these file. No problem there. I
don't see a maintenance plan specific log file.
Paul
"Kevin3NF" wrote:
> Check to see if the account running the SQL Server Agent has permissions to
> delete those fulls. Look for the maintenance plan logs in the \LOG
> directory (same location as the ERRORLOG file(s) )
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
> > Old fulls.
> > I only do full backups.
> >
> > "Kevin3NF" wrote:
> >
> >> Old fulls, or old t-log backups? If t-log make sure every db in the plan
> >> is
> >> set to full recovery model. If you have databases in Simple recovery,
> >> make
> >> a separate plan for them without the t-log backup step
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
> >> >
> >> >
> >> > My SQL 2000 server's daily (full) backup started failing due to a lack
> >> > of
> >> > disk space. This daily backup has been running for quite a while so im
> >> > surprised. The file system was full but it seems the old backup are
> >> > not
> >> > being deleted even though the maintenance plan is set to ""Remove files
> >> > older
> >> > than 1 day". Any ideas why this would happen? Solutions?
> >> >
> >> >
> >> >
> >> > Paul
> >> >
> >>
> >>
> >>
>
>|||Add a "text file" option in the Reporting tab of the Database maintenance
GUI (SQL 2000)
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
news:8670769E-BF6B-4C1F-BE67-84C830DBF144@.microsoft.com...
> The account have the permissions to delete these file. No problem there. I
> don't see a maintenance plan specific log file.
> Paul
> "Kevin3NF" wrote:
>> Check to see if the account running the SQL Server Agent has permissions
>> to
>> delete those fulls. Look for the maintenance plan logs in the \LOG
>> directory (same location as the ERRORLOG file(s) )
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> news:21B3A1CB-02A2-45B2-94EF-62A90E7ADACD@.microsoft.com...
>> > Old fulls.
>> > I only do full backups.
>> >
>> > "Kevin3NF" wrote:
>> >
>> >> Old fulls, or old t-log backups? If t-log make sure every db in the
>> >> plan
>> >> is
>> >> set to full recovery model. If you have databases in Simple recovery,
>> >> make
>> >> a separate plan for them without the t-log backup step
>> >>
>> >> --
>> >>
>> >> Kevin3NF
>> >> SQL Server dude
>> >>
>> >> You want fries with that?
>> >> http://kevin3nf.blogspot.com/
>> >>
>> >> I only check the newsgroups during work hours, M-F.
>> >> Hit my blog and the contact links if necessary...I may be available.
>> >>
>> >>
>> >>
>> >> "Paul_A" <PaulA@.discussions.microsoft.com> wrote in message
>> >> news:21E389B9-FADE-45A6-9DA6-22B27965EE32@.microsoft.com...
>> >> >
>> >> >
>> >> > My SQL 2000 server's daily (full) backup started failing due to a
>> >> > lack
>> >> > of
>> >> > disk space. This daily backup has been running for quite a while so
>> >> > im
>> >> > surprised. The file system was full but it seems the old backup are
>> >> > not
>> >> > being deleted even though the maintenance plan is set to ""Remove
>> >> > files
>> >> > older
>> >> > than 1 day". Any ideas why this would happen? Solutions?
>> >> >
>> >> >
>> >> >
>> >> > Paul
>> >> >
>> >>
>> >>
>> >>
>>
Friday, February 17, 2012
CXPACKET, NETWORKIO, PAGELATCH_EX on server
I have a server with 300Gb+ of disk space on a SAN, with a database 55Gb for data and 15Gb for tranx log. SQL Server 2000 w/SP3 is installed. Whenever I run any query (simple, complex, short, long, using indexes or not) the server current activity shows many instances of NETWORKIO, PAGELATCH_EX, CXPACKET and other wait types on several processes. In Profiler, 500,000+ events were recorded within a 60 second timeframe. Someone else is responsible for "infrastructure" so before I go with recommendations to fix my issues, I would like as much accurate info as possible about the cause. This is supposed to be a relatively low-activity server. My understanding is serious disk subsystem issues would cause all these wait types together. But why would this happen when running a "select x, y, z from table" where the table contains < 250000 records and x,y,z make up a covering index. Additionally, I ran a relatively simple query, two tables, inner join, with properly defined indexes and went home to let it run. I returned and it had been running for 18 hours - I finally ran out of patience and stopped it. Any ideas?And you just went home? 2 possibilities:
1. You've been blocked
2. Too many rows to return and the bottleneck is network and your workstation memory|||yes, i went home. :)
the query that ran for 18 hours was an update, and would have affected fewer than 300k records, which is not an issue when running on my local instance of sql.
there are no deadlocks for any of my processes; just the wait types.|||Can you post the query and the table(s) DDL?|||I've also had this kind of issue on a multiprocessor system. In my case, the update/select just took a *very* long time (but it did finish), while sysprocesses was throwing cxpackets and all that. Sometimes I could rewrite the query, in others I just had to wait.
Next to that: I found that sometimes it helps a lot to recreate the indices of a table. I found this out when it took 15 minutes to return on a simple query. Considering the query, I thought it took way too long. Showplan indicated a full table scan, it didn't use an index while I had a perfect index for that query. Hinting didn't help. So, I reindexed. After that, the query started using the index and voila... just a sec.
1. You've been blocked
2. Too many rows to return and the bottleneck is network and your workstation memory|||yes, i went home. :)
the query that ran for 18 hours was an update, and would have affected fewer than 300k records, which is not an issue when running on my local instance of sql.
there are no deadlocks for any of my processes; just the wait types.|||Can you post the query and the table(s) DDL?|||I've also had this kind of issue on a multiprocessor system. In my case, the update/select just took a *very* long time (but it did finish), while sysprocesses was throwing cxpackets and all that. Sometimes I could rewrite the query, in others I just had to wait.
Next to that: I found that sometimes it helps a lot to recreate the indices of a table. I found this out when it took 15 minutes to return on a simple query. Considering the query, I thought it took way too long. Showplan indicated a full table scan, it didn't use an index while I had a perfect index for that query. Hinting didn't help. So, I reindexed. After that, the query started using the index and voila... just a sec.
Subscribe to:
Posts (Atom)