Filegrowth by percent or by size. (And how much).

Yesterday we encountered a problem.
Setting a small testmachine (slow disks) filegrowth at 10 % and
a databasesize of 1.2 Gbyte.
Problem : A simple insert took over 1 minute.
Causing a timeout.
Took some time, but the growing of the database took some time.
Because of the timeout, the extend was not added after the action,
so the next action causes the same problem.
Our production machines are quite a bit faster, so the problem has
not been noticed (yet) on a production machine.
Our development machines are faster as wel, but a glitch once
probably wouldn't be noticed as significant.
So what timing is to be expected by extending the database ?
(Is 64 Mbyte a good size for extending)
Any thoughts about this ?
ben brugmanHi Ben,
In our production environment, we give a good initial size
to the data and log files and set the increment value by
200 MB and not by size. We have found that this is the
best way to handle databases that grow by 100 MB in a week
and more than 2 GB in size... In this way, we reduce
frequent growth of dbs.
regards,
bharath
mcdba
>--Original Message--
>Yesterday we encountered a problem.
>Setting a small testmachine (slow disks) filegrowth at 10
% and
>a databasesize of 1.2 Gbyte.
>Problem : A simple insert took over 1 minute.
>Causing a timeout.
>Took some time, but the growing of the database took some
time.
>Because of the timeout, the extend was not added after
the action,
>so the next action causes the same problem.
>Our production machines are quite a bit faster, so the
problem has
>not been noticed (yet) on a production machine.
>Our development machines are faster as wel, but a glitch
once
>probably wouldn't be noticed as significant.
>So what timing is to be expected by extending the
database ?
>(Is 64 Mbyte a good size for extending)
>Any thoughts about this ?
>ben brugman
>
>.
>|||Can you give an indication of the 'size' of your machine
and the time it takes to extend by 200 MB ?
ben
"bharath" <barathsing@.hotmail.com> wrote in message
news:a20401c3b7d3$6dc70d90$a601280a@.phx.gbl...
> Hi Ben,
> In our production environment, we give a good initial size
> to the data and log files and set the increment value by
> 200 MB and not by size. We have found that this is the
> best way to handle databases that grow by 100 MB in a week
> and more than 2 GB in size... In this way, we reduce
> frequent growth of dbs.
> regards,
> bharath
> mcdba
>
> >--Original Message--
> >Yesterday we encountered a problem.
> >Setting a small testmachine (slow disks) filegrowth at 10
> % and
> >a databasesize of 1.2 Gbyte.
> >
> >Problem : A simple insert took over 1 minute.
> >Causing a timeout.
> >
> >Took some time, but the growing of the database took some
> time.
> >Because of the timeout, the extend was not added after
> the action,
> >so the next action causes the same problem.
> >
> >Our production machines are quite a bit faster, so the
> problem has
> >not been noticed (yet) on a production machine.
> >Our development machines are faster as wel, but a glitch
> once
> >probably wouldn't be noticed as significant.
> >
> >So what timing is to be expected by extending the
> database ?
> >(Is 64 Mbyte a good size for extending)
> >
> >Any thoughts about this ?
> >
> >ben brugman
> >
> >
> >.
> >

Filegroups and indexes

Hi,
I have two physical disks that each have and NDF file(two different
filegroups: FG1 on Disk1, FG2 on disk2).
Also I have two tables that my queries always join them.
Which decision will lead to better performance:
1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes on
disk2(FG2)
2) Placing Table1 and its indexes on FG1 and placing Table2 and its indexes
on FG2.
Many thanks in advance.
Leila
The only way to know is to test it in your exact situation. It could be
that neither will give you any better performance.
Andrew J. Kelly SQL MVP
"Leila" <lelas@.hotpop.com> wrote in message
news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have two physical disks that each have and NDF file(two different
> filegroups: FG1 on Disk1, FG2 on disk2).
> Also I have two tables that my queries always join them.
> Which decision will lead to better performance:
> 1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes
on
> disk2(FG2)
> 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
indexes
> on FG2.
> Many thanks in advance.
> Leila
>
|||Andrew,
What conditions should be met so that it helps to increase performance?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> The only way to know is to test it in your exact situation. It could be
> that neither will give you any better performance.
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
indexes
> on
> indexes
>
|||That's a big subject but when it comes to disks your best bet is to see if
you have disk queue issues first. You can do this by monitoring the Avg &
current disk queues in Perfmon. Here are some links that may help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:uuRgqnApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Andrew,
> What conditions should be met so that it helps to increase performance?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...
> indexes
>

Filegroups and indexes

Hi,
I have two physical disks that each have and NDF file(two different
filegroups: FG1 on Disk1, FG2 on disk2).
Also I have two tables that my queries always join them.
Which decision will lead to better performance:
1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes on
disk2(FG2)
2) Placing Table1 and its indexes on FG1 and placing Table2 and its indexes
on FG2.
Many thanks in advance.
LeilaThe only way to know is to test it in your exact situation. It could be
that neither will give you any better performance.
--
Andrew J. Kelly SQL MVP
"Leila" <lelas@.hotpop.com> wrote in message
news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have two physical disks that each have and NDF file(two different
> filegroups: FG1 on Disk1, FG2 on disk2).
> Also I have two tables that my queries always join them.
> Which decision will lead to better performance:
> 1) Placing Table1 and Table2 on first disk(FG1) and placing their indexes
on
> disk2(FG2)
> 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
indexes
> on FG2.
> Many thanks in advance.
> Leila
>|||Andrew,
What conditions should be met so that it helps to increase performance?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...
> The only way to know is to test it in your exact situation. It could be
> that neither will give you any better performance.
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have two physical disks that each have and NDF file(two different
> > filegroups: FG1 on Disk1, FG2 on disk2).
> > Also I have two tables that my queries always join them.
> > Which decision will lead to better performance:
> > 1) Placing Table1 and Table2 on first disk(FG1) and placing their
indexes
> on
> > disk2(FG2)
> > 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
> indexes
> > on FG2.
> >
> > Many thanks in advance.
> > Leila
> >
> >
>|||That's a big subject but when it comes to disks your best bet is to see if
you have disk queue issues first. You can do this by monitoring the Avg &
current disk queues in Perfmon. Here are some links that may help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:uuRgqnApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Andrew,
> What conditions should be met so that it helps to increase performance?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#A0VAPApEHA.2948@.TK2MSFTNGP11.phx.gbl...
> > The only way to know is to test it in your exact situation. It could be
> > that neither will give you any better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Leila" <lelas@.hotpop.com> wrote in message
> > news:%239Irja%23oEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > > Hi,
> > > I have two physical disks that each have and NDF file(two different
> > > filegroups: FG1 on Disk1, FG2 on disk2).
> > > Also I have two tables that my queries always join them.
> > > Which decision will lead to better performance:
> > > 1) Placing Table1 and Table2 on first disk(FG1) and placing their
> indexes
> > on
> > > disk2(FG2)
> > > 2) Placing Table1 and its indexes on FG1 and placing Table2 and its
> > indexes
> > > on FG2.
> > >
> > > Many thanks in advance.
> > > Leila
> > >
> > >
> >
> >
>

Filegroups

Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second filegroup than the "default" one and put here one of the 2 most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table that cause most problems and the Raid 1 i would put the other table. Any opinions? Any suggestions?
Thanxs
Cl=E1udiaYou should always keep the log on a different drive than the data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia,
I'd recommend keeping all your data files off of the log files drive.
Putting data files on the same drive as your log files will greatly slow
down access to the log files.
--
Denny Cherry
DBA
GameSpy Industries
"Cláudia" <Alphaseusa@.hotmail.com> wrote in message
news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
Question:
I have 3 hard disks: C, D and E
C contains OS information (Raid 1).
I was thinking of doing this ( in order to optimise
performace of my SQL Server):
P.S Its an OLAP server
D: (Raid 1) put all the log files here + create a second
filegroup than the "default" one and put here one of the 2
most heavy queried tables
E: put all the data files in one filegroup (Raid 5)
I was thinking that in the Raid 5 i would put the table
that cause most problems and the Raid 1 i would put the
other table. Any opinions? Any suggestions?
Thanxs
Cláudia|||Claudia
The guys are right about log files, for OLTP, but as you said your's is OLAP which can be different. If you are not updating the database or you are doing it at a time when no-users are on the system and there is a large time window, the impact of the transaction logs being on the same drives as the data is reduced to a level where if you can increase performance, during the time users are on the system by spreading the data more, then don't worry about the log location.
You say you have 3 hard drives, I assume that is at the logical level, bit hard to have a one drive raid 5. The main thing that worries me about your proposed use is you say one drive is raid 1 and one is raid 5 that you propose to put the data on. It is not a good idea to mix the raid levels your data files are on.
If you resolve your raid levels, I see no harm in trying to spread the load.
Regards
John
>--Original Message--
>Claudia,
>I'd recommend keeping all your data files off of the log files drive.
>Putting data files on the same drive as your log files will greatly slow
>down access to the log files.
>-- >Denny Cherry
>DBA
>GameSpy Industries
>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>Question:
>I have 3 hard disks: C, D and E
>C contains OS information (Raid 1).
>I was thinking of doing this ( in order to optimise
>performace of my SQL Server):
>P.S Its an OLAP server
>D: (Raid 1) put all the log files here + create a second
>filegroup than the "default" one and put here one of the 2
>most heavy queried tables
>E: put all the data files in one filegroup (Raid 5)
>I was thinking that in the Raid 5 i would put the table
>that cause most problems and the Raid 1 i would put the
>other table. Any opinions? Any suggestions?
>Thanxs
>Cl=E1udia
>
>.
>|||Claudia
Would be easier if you had 8, but lets see what we can do. I don't know the size of your disks or your database, so this might not work.
I am assuming you are a 'classic' olap data warehousing system and not some kind of hybrid.
Leave the operating system as it is, two disks RAID 1. Configure 4 disks as RAID 0 + 1 and put the databases on there. Despite saying you can put the transaction logs on the same disk, put it on the remaining disk with no RAID. (If you had an extra disk, I would have said 6 disk RAID 0+1 and put the lot on it.
Before anyone says, what no RAID for the logs, I was assuming 'classic' olap where the logs are not used, usually the database is set to simple recovery (or trauncate log on checkpoint for 6.5 or 7). For what I see as 'classic' olap it usually quicker and easier to rebuild the database from source than have a backup/DR plan, so no backups and no logs.
Not enough info to know if this would be suitable, what do you think?
Regards
John
>--Original Message--
>I have 7 hard disks:
>2 -- raid 1
>2 -- raid 1
>3 -- raid 5
>any ideas on how to do it best to optimize performance?
>>--Original Message--
>>Claudia
>>The guys are right about log files, for OLTP, but as you >>said your's is OLAP which can be different. If you are >not >>updating the database or you are doing it at a time when >>no-users are on the system and there is a large time >>window, the impact of the transaction logs being on the >>same drives as the data is reduced to a level where if >you >>can increase performance, during the time users are on >the >>system by spreading the data more, then don't worry about >>the log location.
>>You say you have 3 hard drives, I assume that is at the >>logical level, bit hard to have a one drive raid 5. The >>main thing that worries me about your proposed use is you >>say one drive is raid 1 and one is raid 5 that you >propose >>to put the data on. It is not a good idea to mix the raid >>levels your data files are on.
>>If you resolve your raid levels, I see no harm in trying >>to spread the load.
>>Regards
>>John
>>
>>--Original Message--
>>Claudia,
>>I'd recommend keeping all your data files off of the log >>files drive.
>>Putting data files on the same drive as your log files >>will greatly slow
>>down access to the log files.
>>-- >>Denny Cherry
>>DBA
>>GameSpy Industries
>>"Cl=E1udia" <Alphaseusa@.hotmail.com> wrote in message
>>news:4e6101c356ba$cc02b0a0$a001280a@.phx.gbl...
>>Question:
>>I have 3 hard disks: C, D and E
>>C contains OS information (Raid 1).
>>I was thinking of doing this ( in order to optimise
>>performace of my SQL Server):
>>P.S Its an OLAP server
>>D: (Raid 1) put all the log files here + create a second
>>filegroup than the "default" one and put here one of the >2
>>most heavy queried tables
>>E: put all the data files in one filegroup (Raid 5)
>>I was thinking that in the Raid 5 i would put the table
>>that cause most problems and the Raid 1 i would put the
>>other table. Any opinions? Any suggestions?
>>Thanxs
>>Cl=E1udia
>>
>>.
>>.
>.
>

File placement on server with 2 instances of SQL

I have inherited a stand alone server with 2 instances of SQL installed.
There are 6 physical disks in the server which have been made into 3 mirrors.
The first mirror has the OS on it. Currently, the 2nd disk has all the
database and transaction log files from both instances of SQL.
I plan to make use of the 3rd disk. My question is: is it better to move the
database and transaction log files from the second instance to the new disk
so that all the files for the first instance are on disk 2 and all the files
for the 2nd instance are on disk 3 OR is it better to keep all the database
files from both instances on disk 2 and move all the log files for both
instances to disk 3?
I'm sure I have read somehwere that in this situation, the disks should be
separated by instance rather than seperating by file type.Seen from the databases' perspective, same or different instances doesn't really make a difference.
As usual, only testing with a realistic workload can give you a definitive answer. My guess, though,
is to have the tlog files separated.
Unless you have some pretty unusual workload. Like only reads during day and modifications during
night. And it doesn't matter that modifications take a bit longer. Then you might consider having db
1 (all db files) on one raid and db 2 (all files) on another. That would mean that the read
operations during day would not compete between db1 and db2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>I have inherited a stand alone server with 2 instances of SQL installed.
> There are 6 physical disks in the server which have been made into 3 mirrors.
> The first mirror has the OS on it. Currently, the 2nd disk has all the
> database and transaction log files from both instances of SQL.
> I plan to make use of the 3rd disk. My question is: is it better to move the
> database and transaction log files from the second instance to the new disk
> so that all the files for the first instance are on disk 2 and all the files
> for the 2nd instance are on disk 3 OR is it better to keep all the database
> files from both instances on disk 2 and move all the log files for both
> instances to disk 3?
> I'm sure I have read somehwere that in this situation, the disks should be
> separated by instance rather than seperating by file type.
>|||+1 to both of Tibor's sentiments.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>|||I also second Tibor's response. Especially the part about the only
difinitive answer will come from testing.
And don't forget there is a third option. That's to put the data from DB1
and the log from DB2 on Disk2 and the log from DB1 and the data from DB2 on
Disk3. That might help in a situation where you have primarily reads on
both databases during the day and updates during the night and almost all
the updates are done to one database.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>

file group question

Hi
I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
They are 4 different disks and different disk controllers.
I do not have a backup of the database. Now I have a situation where I lost
one of the disk containing the index filegroup. I am running in FULL MODE.
I do not care if I loose index file group as it will only slow down the
database.
Is there a way to start the database without the index filegroup?
Can I take it offline or drop it?
Thanks
Mangesh
Mangesh Deshpande wrote:
> Hi
> I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
> They are 4 different disks and different disk controllers.
> I do not have a backup of the database. Now I have a situation where
> I lost one of the disk containing the index filegroup. I am running
> in FULL MODE.
> I do not care if I loose index file group as it will only slow down
> the database.
> Is there a way to start the database without the index filegroup?
> Can I take it offline or drop it?
> Thanks
> Mangesh
Do you think there were any clustered indexes in the database. If so,
where did you put those tables? On one of the table filegroups or one of
the index file groups? The table goes with the clustered index, so if
you built a clustered index on a table and put it on the index drive you
lost, you lost the table as well.
Many here are going to wonder:
Where is your redundancy on your drive subsystem?
Why no backups?
It's pretty risky leaving both out of the equation as you now know.
Going forward I might suggest using two mirrored sets. One for data and
one for temp db and log files.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David. This is just a hypothetical case. For production we are using
RAIDa 5.
I am keeping clustered index on the my table file group and it is not broken.
My non clustered indexes are on separate file group and it is broken.
So I want to have a mechanism by which even if the index file group disk
crashes my system should be up. In oracle you can take index file system
offline and allow the database to work fine.
I wanted to check if we have a mechanism in SQL Server 2000.
Thanks always
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> Do you think there were any clustered indexes in the database. If so,
> where did you put those tables? On one of the table filegroups or one of
> the index file groups? The table goes with the clustered index, so if
> you built a clustered index on a table and put it on the index drive you
> lost, you lost the table as well.
> Many here are going to wonder:
> Where is your redundancy on your drive subsystem?
> Why no backups?
> It's pretty risky leaving both out of the equation as you now know.
> Going forward I might suggest using two mirrored sets. One for data and
> one for temp db and log files.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Mangesh Deshpande wrote:
> Thanks David. This is just a hypothetical case. For production we are
> using RAIDa 5.
> I am keeping clustered index on the my table file group and it is not
> broken. My non clustered indexes are on separate file group and it is
> broken.
> So I want to have a mechanism by which even if the index file group
> disk crashes my system should be up. In oracle you can take index
> file system offline and allow the database to work fine.
> I wanted to check if we have a mechanism in SQL Server 2000.
> Thanks always
> Mangesh
But in production you are using RAID 5 which won't apply to your
hypothetical. I personally have no idea whether SQL Server can recover
from a lost drive with a lost filegroup. I assume there is a way.
Whether that's with the help of MS PSS or some technique I don;t know
about I'm not sure. My only question here is why is this hypothetical
even worth considering if your production environment does not resemble
what you describe?
David Gugick
Imceda Software
www.imceda.com
|||Thanks David for sharing the knowledge. I was thinking of adding the
filegroups on one of our other production NON CRITICAL database which has no
RAID.
But I was checking to see if I can achieve any benefit and JUST Curious as
it is a standard practice in Oracle to Create indexes on separate Tablespaces.
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> But in production you are using RAID 5 which won't apply to your
> hypothetical. I personally have no idea whether SQL Server can recover
> from a lost drive with a lost filegroup. I assume there is a way.
> Whether that's with the help of MS PSS or some technique I don;t know
> about I'm not sure. My only question here is why is this hypothetical
> even worth considering if your production environment does not resemble
> what you describe?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

file group question

Hi
I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
They are 4 different disks and different disk controllers.
I do not have a backup of the database. Now I have a situation where I lost
one of the disk containing the index filegroup. I am running in FULL MODE.
I do not care if I loose index file group as it will only slow down the
database.
Is there a way to start the database without the index filegroup?
Can I take it offline or drop it'
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
> They are 4 different disks and different disk controllers.
> I do not have a backup of the database. Now I have a situation where
> I lost one of the disk containing the index filegroup. I am running
> in FULL MODE.
> I do not care if I loose index file group as it will only slow down
> the database.
> Is there a way to start the database without the index filegroup?
> Can I take it offline or drop it'
> Thanks
> Mangesh
Do you think there were any clustered indexes in the database. If so,
where did you put those tables? On one of the table filegroups or one of
the index file groups? The table goes with the clustered index, so if
you built a clustered index on a table and put it on the index drive you
lost, you lost the table as well.
Many here are going to wonder:
Where is your redundancy on your drive subsystem?
Why no backups?
It's pretty risky leaving both out of the equation as you now know.
Going forward I might suggest using two mirrored sets. One for data and
one for temp db and log files.
David Gugick
Imceda Software
www.imceda.com|||Thanks David. This is just a hypothetical case. For production we are using
RAIDa 5.
I am keeping clustered index on the my table file group and it is not broken
.
My non clustered indexes are on separate file group and it is broken.
So I want to have a mechanism by which even if the index file group disk
crashes my system should be up. In oracle you can take index file system
offline and allow the database to work fine.
I wanted to check if we have a mechanism in SQL Server 2000.
Thanks always
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> Do you think there were any clustered indexes in the database. If so,
> where did you put those tables? On one of the table filegroups or one of
> the index file groups? The table goes with the clustered index, so if
> you built a clustered index on a table and put it on the index drive you
> lost, you lost the table as well.
> Many here are going to wonder:
> Where is your redundancy on your drive subsystem?
> Why no backups?
> It's pretty risky leaving both out of the equation as you now know.
> Going forward I might suggest using two mirrored sets. One for data and
> one for temp db and log files.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks David. This is just a hypothetical case. For production we are
> using RAIDa 5.
> I am keeping clustered index on the my table file group and it is not
> broken. My non clustered indexes are on separate file group and it is
> broken.
> So I want to have a mechanism by which even if the index file group
> disk crashes my system should be up. In oracle you can take index
> file system offline and allow the database to work fine.
> I wanted to check if we have a mechanism in SQL Server 2000.
> Thanks always
> Mangesh
But in production you are using RAID 5 which won't apply to your
hypothetical. I personally have no idea whether SQL Server can recover
from a lost drive with a lost filegroup. I assume there is a way.
Whether that's with the help of MS PSS or some technique I don;t know
about I'm not sure. My only question here is why is this hypothetical
even worth considering if your production environment does not resemble
what you describe?
David Gugick
Imceda Software
www.imceda.com|||Thanks David for sharing the knowledge. I was thinking of adding the
filegroups on one of our other production NON CRITICAL database which has no
RAID.
But I was checking to see if I can achieve any benefit and JUST Curious as
it is a standard practice in Oracle to Create indexes on separate Tablespace
s.
Mangesh
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> But in production you are using RAID 5 which won't apply to your
> hypothetical. I personally have no idea whether SQL Server can recover
> from a lost drive with a lost filegroup. I assume there is a way.
> Whether that's with the help of MS PSS or some technique I don;t know
> about I'm not sure. My only question here is why is this hypothetical
> even worth considering if your production environment does not resemble
> what you describe?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

file group question

Hi
I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
They are 4 different disks and different disk controllers.
I do not have a backup of the database. Now I have a situation where I lost
one of the disk containing the index filegroup. I am running in FULL MODE.
I do not care if I loose index file group as it will only slow down the
database.
Is there a way to start the database without the index filegroup?
Can I take it offline or drop it'
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
> They are 4 different disks and different disk controllers.
> I do not have a backup of the database. Now I have a situation where
> I lost one of the disk containing the index filegroup. I am running
> in FULL MODE.
> I do not care if I loose index file group as it will only slow down
> the database.
> Is there a way to start the database without the index filegroup?
> Can I take it offline or drop it'
> Thanks
> Mangesh
Do you think there were any clustered indexes in the database. If so,
where did you put those tables? On one of the table filegroups or one of
the index file groups? The table goes with the clustered index, so if
you built a clustered index on a table and put it on the index drive you
lost, you lost the table as well.
Many here are going to wonder:
Where is your redundancy on your drive subsystem?
Why no backups?
It's pretty risky leaving both out of the equation as you now know.
Going forward I might suggest using two mirrored sets. One for data and
one for temp db and log files.
--
David Gugick
Imceda Software
www.imceda.com|||Thanks David. This is just a hypothetical case. For production we are using
RAIDa 5.
I am keeping clustered index on the my table file group and it is not broken.
My non clustered indexes are on separate file group and it is broken.
So I want to have a mechanism by which even if the index file group disk
crashes my system should be up. In oracle you can take index file system
offline and allow the database to work fine.
I wanted to check if we have a mechanism in SQL Server 2000.
Thanks always
Mangesh
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have 4 filegroups in sql server 2000. 2 indexes and 2 tables.
> > They are 4 different disks and different disk controllers.
> >
> > I do not have a backup of the database. Now I have a situation where
> > I lost one of the disk containing the index filegroup. I am running
> > in FULL MODE.
> >
> > I do not care if I loose index file group as it will only slow down
> > the database.
> > Is there a way to start the database without the index filegroup?
> > Can I take it offline or drop it'
> >
> > Thanks
> > Mangesh
> Do you think there were any clustered indexes in the database. If so,
> where did you put those tables? On one of the table filegroups or one of
> the index file groups? The table goes with the clustered index, so if
> you built a clustered index on a table and put it on the index drive you
> lost, you lost the table as well.
> Many here are going to wonder:
> Where is your redundancy on your drive subsystem?
> Why no backups?
> It's pretty risky leaving both out of the equation as you now know.
> Going forward I might suggest using two mirrored sets. One for data and
> one for temp db and log files.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Mangesh Deshpande wrote:
> Thanks David. This is just a hypothetical case. For production we are
> using RAIDa 5.
> I am keeping clustered index on the my table file group and it is not
> broken. My non clustered indexes are on separate file group and it is
> broken.
> So I want to have a mechanism by which even if the index file group
> disk crashes my system should be up. In oracle you can take index
> file system offline and allow the database to work fine.
> I wanted to check if we have a mechanism in SQL Server 2000.
> Thanks always
> Mangesh
But in production you are using RAID 5 which won't apply to your
hypothetical. I personally have no idea whether SQL Server can recover
from a lost drive with a lost filegroup. I assume there is a way.
Whether that's with the help of MS PSS or some technique I don;t know
about I'm not sure. My only question here is why is this hypothetical
even worth considering if your production environment does not resemble
what you describe?
--
David Gugick
Imceda Software
www.imceda.com|||Thanks David for sharing the knowledge. I was thinking of adding the
filegroups on one of our other production NON CRITICAL database which has no
RAID.
But I was checking to see if I can achieve any benefit and JUST Curious as
it is a standard practice in Oracle to Create indexes on separate Tablespaces.
Mangesh
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Thanks David. This is just a hypothetical case. For production we are
> > using RAIDa 5.
> > I am keeping clustered index on the my table file group and it is not
> > broken. My non clustered indexes are on separate file group and it is
> > broken.
> >
> > So I want to have a mechanism by which even if the index file group
> > disk crashes my system should be up. In oracle you can take index
> > file system offline and allow the database to work fine.
> > I wanted to check if we have a mechanism in SQL Server 2000.
> >
> > Thanks always
> > Mangesh
> But in production you are using RAID 5 which won't apply to your
> hypothetical. I personally have no idea whether SQL Server can recover
> from a lost drive with a lost filegroup. I assume there is a way.
> Whether that's with the help of MS PSS or some technique I don;t know
> about I'm not sure. My only question here is why is this hypothetical
> even worth considering if your production environment does not resemble
> what you describe?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>