Format Alignment: group

Showing posts with label group. Show all posts

Thursday, March 29, 2012

Filter does not work on a group?

I must be missing something. I have a table with two groups. One groups on
the user who created the record the other on the date the record was created.
I sort on the username group by name and the createdate group with a
category type.
My dataset pulls information for the current year however in this table I
want to only display data for the last week. If I apply the filter to the
table I receive expected results however if I apply the filter to the group
nothing comes back.
For either group if I put "= #2/22/2005#" for the expression "<=" as the
operator and "= Fields!CreateDate" as the Value I return 0 records despite
having some. I know my syntax is correct becaus this works at the table
level.
Can anyone tell me what I am missing?And you have date values in there more recent than 2/22/2005?
2/22/2005 <= Fields!CreateDate
I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
the value.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
>I must be missing something. I have a table with two groups. One groups
>on
> the user who created the record the other on the date the record was
> created.
> I sort on the username group by name and the createdate group with a
> category type.
> My dataset pulls information for the current year however in this table I
> want to only display data for the last week. If I apply the filter to the
> table I receive expected results however if I apply the filter to the
> group
> nothing comes back.
> For either group if I put "= #2/22/2005#" for the expression "<=" as the
> operator and "= Fields!CreateDate" as the Value I return 0 records despite
> having some. I know my syntax is correct becaus this works at the table
> level.
> Can anyone tell me what I am missing?|||Same result. I know there are values past 2/22/2005 because I see them
without the filter applied. The filter works at the table level just not the
group level. This is strange.
"Jeff A. Stucker" wrote:
> And you have date values in there more recent than 2/22/2005?
> 2/22/2005 <= Fields!CreateDate
> I would try "= Fields!CreateDate" in the expression and "= #2/22/2005#" in
> the value.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ben Holcombe" <BenHolcombe@.discussions.microsoft.com> wrote in message
> news:41E911DB-58AE-4F74-9A6B-B6144C454DCA@.microsoft.com...
> >I must be missing something. I have a table with two groups. One groups
> >on
> > the user who created the record the other on the date the record was
> > created.
> > I sort on the username group by name and the createdate group with a
> > category type.
> >
> > My dataset pulls information for the current year however in this table I
> > want to only display data for the last week. If I apply the filter to the
> > table I receive expected results however if I apply the filter to the
> > group
> > nothing comes back.
> >
> > For either group if I put "= #2/22/2005#" for the expression "<=" as the
> > operator and "= Fields!CreateDate" as the Value I return 0 records despite
> > having some. I know my syntax is correct becaus this works at the table
> > level.
> >
> > Can anyone tell me what I am missing?
>
>

Monday, March 26, 2012

Fill up missing months.

Hi,
I've got this resultset:
year month group value
2005 5 a 10
2005 6 a 20
2005 1 b 15
2005 3 b 16
2005 9 c 15
and now I need to fill it up with "missing" months -- like this:
year month group value
2005 1 a 0
2005 2 a 0
2005 3 a 0
2005 4 a 0
2005 5 a 10
2005 6 a 20
2005 7 a 0
2005 8 a 0
2005 9 a 0
2005 10 a 0
2005 11 a 0
2005 12 a 0
2005 1 b 15
2005 2 b 0
2005 3 b 16
2005 4 b 0
2005 5 b 0
2005 6 b 0
2005 7 b 0
2005 8 b 0
2005 9 b 0
2005 10 b 0
2005 11 b 0
2005 12 b 0
2005 1 c 0
2005 2 c 0
2005 3 c 0
2005 4 c 0
2005 5 c 0
2005 6 c 0
2005 7 c 0
2005 8 c 0
2005 9 c 15
2005 10 c 0
2005 11 c 0
2005 12 c 0
Anything I can think of is some cursor or loop-oriented approach with a
little help of "Dates" table. Maybe someone can come up with more
ellegant, set oriented solution.
Thanks in advance
MaciekHi Maciek !
Use a calendar table:
http://www.aspfaq.com/show.asp?id=2519
HTH, Jens Suessmeyer.|||Create a Calendar table and populate it with all the dates you will
ever need (a calendar I'm using right now has 100 years worth). Once
you've done that you can just outer join it in your query.
You'll may still use a loop to populate the calendar but you only need
to do that once and do it at install time rather than runtime.
David Portas
SQL Server MVP
--|||DECLARE @.StartYear SMALLINT , @.EndYear SMALLINT
SELECT @.StartYear = 2004 , @.EndYear = 2005
SELECT
Years.N AS [Year]
, Months.N AS [Month]
FROM
( SELECT * FROM tblNumbers WHERE N BETWEEN @.StartYear AND @.EndYear ) Years
CROSS JOIN
( SELECT * FROM tblNumbers WHERE N BETWEEN 1 AND 12 ) Months
ORDER BY
[Year]
, [Month]
Then right outer join the table to your resultset.
HTH
"maciek" <maciek@.kolobrzeg.com.pl> wrote in message
news:1129805888.703877.78890@.g47g2000cwa.googlegroups.com...
> Hi,
> I've got this resultset:
> year month group value
> 2005 5 a 10
> 2005 6 a 20
> 2005 1 b 15
> 2005 3 b 16
> 2005 9 c 15
> and now I need to fill it up with "missing" months -- like this:
> year month group value
> 2005 1 a 0
> 2005 2 a 0
> 2005 3 a 0
> 2005 4 a 0
> 2005 5 a 10
> 2005 6 a 20
> 2005 7 a 0
> 2005 8 a 0
> 2005 9 a 0
> 2005 10 a 0
> 2005 11 a 0
> 2005 12 a 0
> 2005 1 b 15
> 2005 2 b 0
> 2005 3 b 16
> 2005 4 b 0
> 2005 5 b 0
> 2005 6 b 0
> 2005 7 b 0
> 2005 8 b 0
> 2005 9 b 0
> 2005 10 b 0
> 2005 11 b 0
> 2005 12 b 0
> 2005 1 c 0
> 2005 2 c 0
> 2005 3 c 0
> 2005 4 c 0
> 2005 5 c 0
> 2005 6 c 0
> 2005 7 c 0
> 2005 8 c 0
> 2005 9 c 15
> 2005 10 c 0
> 2005 11 c 0
> 2005 12 c 0
> Anything I can think of is some cursor or loop-oriented approach with a
> little help of "Dates" table. Maybe someone can come up with more
> ellegant, set oriented solution.
> Thanks in advance
> Maciek
>|||Additionally,
You'd want to cross join the Year/Month results with a distinct list of the
(a,b,c) column - so you'd get a full list of Years/Months/ColumnX :)
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435774d3$0$140$7b0f0fd3@.mistral.news.newnet.co.uk...
> DECLARE @.StartYear SMALLINT , @.EndYear SMALLINT
> SELECT @.StartYear = 2004 , @.EndYear = 2005
> SELECT
> Years.N AS [Year]
> , Months.N AS [Month]
> FROM
> ( SELECT * FROM tblNumbers WHERE N BETWEEN @.StartYear AND @.EndYear )
Years
> CROSS JOIN
> ( SELECT * FROM tblNumbers WHERE N BETWEEN 1 AND 12 ) Months
> ORDER BY
> [Year]
> , [Month]
> Then right outer join the table to your resultset.
> HTH
> "maciek" <maciek@.kolobrzeg.com.pl> wrote in message
> news:1129805888.703877.78890@.g47g2000cwa.googlegroups.com...
>|||Thanks Jens and David,
Sorry, I put it into wrong words when I wrote "(...) with a little help
of 'Dates' table." I thougt of the Calendar table you mentioned. I have
one in my db but the problem is I do not know how to write this outer
join :-( I mean if it was just one group ("a" for example) i would just
do a simple outer join on 'year' and 'month' columns, but since there
are a few groups I do not know how to make it work. How to repeat this
'twelve months' for each group. I think I'm not set oriented kind of
mind...|||Now I've got the clue!
Thanks!

Fill stops /timeouts

Dear Group,

I am tring to use a command that calls the server to fill an adapter, it
never seems to get to the adapter, command and the server either times out
or does not respond. The timeout is set at 10 hours. I am using Visual
Studio to acces MS SQL - Server.

I think I have all the rights and permissions set correctly. Also, I have
used this command to fill other adapters and tables.

Does anyone have a suggestion.

Jeff MagouirkJeff Magouirk (magouirkj@.njc.org) writes:
> I am tring to use a command that calls the server to fill an adapter, it
> never seems to get to the adapter, command and the server either times out
> or does not respond. The timeout is set at 10 hours. I am using Visual
> Studio to acces MS SQL - Server.
> I think I have all the rights and permissions set correctly. Also, I have
> used this command to fill other adapters and tables.
> Does anyone have a suggestion.

I am afraid that the amount of detail in your post is too small for a
good answer to be possible.

One trivial reason could be blocking. You can run sp_who to see if you
have any blocking. If there is a non-zero value in th Blk column, then
that process is blocking the spid for this row.

If the query really takes ten hours to run - then you have some tuning
to do!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Fill footer dynamically by group

Hello,
I would like to modify dynamically my footer.
I have a list displayed on several pages and grouping my data on IDCategory. I would like to put on the footer the name of the category on each page of the list.
When I use a textbox (with the name of category) on the body, with a link ReportItems!textbox1.Value on the footer, the value is only displayed on the first page of my report.
Could you tell me how to display it on each page ?
The second footer problem is that I want to hide this footer on the first page of my list. In reporting services, you can hide the footer on the first page, but can you hide the footer of each first page of a list ?
Thanks for all.Report item references from the page header/footer return Nothing if that
report item doesn't appear on the page.
Instead of putting a single textbox in the body (which would only appear on
the first page of the report), put the textbox in your innermost list. That
way it will be certain to appear on every page (mark it as Hidden so that it
is on the page but not visisble to the user).
For the second problem, you need some way of detecting the start of your
inner list. For this, you could add a second (hidden) textbox inside the
outer list. Then the expression in your footer could be something like
this:
=iif(ReportItems!textbox2.Value is Nothing, ReportItems!textbox1.Value,
Nothing)
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Drix" <Drix@.discussions.microsoft.com> wrote in message
news:65779D3C-E124-45D7-AD0A-4D20627F3664@.microsoft.com...
> Hello,
> I would like to modify dynamically my footer.
> I have a list displayed on several pages and grouping my data on
IDCategory. I would like to put on the footer the name of the category on
each page of the list.
> When I use a textbox (with the name of category) on the body, with a link
ReportItems!textbox1.Value on the footer, the value is only displayed on the
first page of my report.
> Could you tell me how to display it on each page ?
> The second footer problem is that I want to hide this footer on the first
page of my list. In reporting services, you can hide the footer on the first
page, but can you hide the footer of each first page of a list ?
> Thanks for all.|||Thanks for your help.
For your first answer, what do you mean with "innermost" ? I've tried to put the textbox at the bottom of the list, but the problem persists : the footer only appears on the last page.
For your second answer, it's not possible to put on the footer more than one reference to the body and I couldn't test other solutions if the first problem isn't resolved !
Thanks for your help for "innermost"...
"Chris Hays [MSFT]" wrote:
> Report item references from the page header/footer return Nothing if that
> report item doesn't appear on the page.
> Instead of putting a single textbox in the body (which would only appear on
> the first page of the report), put the textbox in your innermost list. That
> way it will be certain to appear on every page (mark it as Hidden so that it
> is on the page but not visisble to the user).
> For the second problem, you need some way of detecting the start of your
> inner list. For this, you could add a second (hidden) textbox inside the
> outer list. Then the expression in your footer could be something like
> this:
> =iif(ReportItems!textbox2.Value is Nothing, ReportItems!textbox1.Value,
> Nothing)
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "Drix" <Drix@.discussions.microsoft.com> wrote in message
> news:65779D3C-E124-45D7-AD0A-4D20627F3664@.microsoft.com...
> > Hello,
> >
> > I would like to modify dynamically my footer.
> > I have a list displayed on several pages and grouping my data on
> IDCategory. I would like to put on the footer the name of the category on
> each page of the list.
> >
> > When I use a textbox (with the name of category) on the body, with a link
> ReportItems!textbox1.Value on the footer, the value is only displayed on the
> first page of my report.
> > Could you tell me how to display it on each page ?
> >
> > The second footer problem is that I want to hide this footer on the first
> page of my list. In reporting services, you can hide the footer on the first
> page, but can you hide the footer of each first page of a list ?
> >
> > Thanks for all.
>
>|||Based on your description, you have two lists (the outer one containing the
inner one).
The outer list groups on IDCategory. The second textbox should be inside
this list.
The inner list doesn't group (it shows detail data). The first textbox
should be inside this list.
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Drix" <Drix@.discussions.microsoft.com> wrote in message
news:E931E967-9FC5-4EA9-9D2A-26554B26AEC4@.microsoft.com...
> Thanks for your help.
> For your first answer, what do you mean with "innermost" ? I've tried to
put the textbox at the bottom of the list, but the problem persists : the
footer only appears on the last page.
> For your second answer, it's not possible to put on the footer more than
one reference to the body and I couldn't test other solutions if the first
problem isn't resolved !
> Thanks for your help for "innermost"...
>
> "Chris Hays [MSFT]" wrote:
> > Report item references from the page header/footer return Nothing if
that
> > report item doesn't appear on the page.
> >
> > Instead of putting a single textbox in the body (which would only appear
on
> > the first page of the report), put the textbox in your innermost list.
That
> > way it will be certain to appear on every page (mark it as Hidden so
that it
> > is on the page but not visisble to the user).
> >
> > For the second problem, you need some way of detecting the start of your
> > inner list. For this, you could add a second (hidden) textbox inside
the
> > outer list. Then the expression in your footer could be something like
> > this:
> >
> > =iif(ReportItems!textbox2.Value is Nothing, ReportItems!textbox1.Value,
> > Nothing)
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Drix" <Drix@.discussions.microsoft.com> wrote in message
> > news:65779D3C-E124-45D7-AD0A-4D20627F3664@.microsoft.com...
> > > Hello,
> > >
> > > I would like to modify dynamically my footer.
> > > I have a list displayed on several pages and grouping my data on
> > IDCategory. I would like to put on the footer the name of the category
on
> > each page of the list.
> > >
> > > When I use a textbox (with the name of category) on the body, with a
link
> > ReportItems!textbox1.Value on the footer, the value is only displayed on
the
> > first page of my report.
> > > Could you tell me how to display it on each page ?
> > >
> > > The second footer problem is that I want to hide this footer on the
first
> > page of my list. In reporting services, you can hide the footer on the
first
> > page, but can you hide the footer of each first page of a list ?
> > >
> > > Thanks for all.
> >
> >
> >sql

Monday, March 12, 2012

Filegroup restore Issue

I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?

with smiles
santhoshDoes any one face this issue yet? Any resolution.|||

Hi Santosh,

You need to perform recovery using transaction log backups taken after the backup you used to restore the filegroup and then finally recover the last log backup taken using No_truncate.

I was wondering if you did a backup log with no_truncate before restoring the filegroup.

regards

Jag

|||Hi Jag,
oh I didnt try with no_truncate thing. what If I dont have a transaction log and I didnt get a chance to take the tail log backup. Wont the filegroup restore work? Thanks for your reply Jag.|||

Hi Santosh,

You need to do a tail-log back up and recover the database using the all the log backups (including tail backup in the end.) taken after database backup, that used for restoring the filegroup.

This is because you are performing partial restore and for this to you need to perform complete recovery.

if the complete recovery is not performed the LSNs of restored filegroup are different from rest of the database.

regards

Jag

Filegroup restore Issue

with smiles
santhoshDoes any one face this issue yet? Any resolution.|||

Hi Santosh,

You need to perform recovery using transaction log backups taken after the backup you used to restore the filegroup and then finally recover the last log backup taken using No_truncate.

I was wondering if you did a backup log with no_truncate before restoring the filegroup.

regards

Jag

Hi Santosh,

You need to do a tail-log back up and recover the database using the all the log backups (including tail backup in the end.) taken after database backup, that used for restoring the filegroup.

This is because you are performing partial restore and for this to you need to perform complete recovery.

if the complete recovery is not performed the LSNs of restored filegroup are different from rest of the database.

regards

Jag

Filegroup recommendation

Are there any general recommendations concerning filegroups? My personal point of view is to place large tables in their own filegroups and group smaller, more static, tables in a single filegroup. Is it also good practice to group small and large index in two separate filegroups or should each large index have their own filegroup? Are there any useful links out there concerning filegroups and configuration?http://www.sql-server-performance.com/filegroups.asp

Friday, March 9, 2012

Filegroup & tables

Is there a way to see which table belong to which file of a file group? I
have a filegroup of 10 files and they files are not evenly distributed. One
hold 16G while the other in 3G range. Thanks!Tables only can be assigned to a specific filegroup. When you right click
the table name in Enterprise Manager and script it, it will tell you what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the first
file, then move to the next, move to the next after that second one was
filled and so on. That is why you see the uneven file distribution. You'll
have to dig around in SQL books online, but it should be in there somewhere.
I learned this studying to recertify on SQL 2000.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file group? I
> have a filegroup of 10 files and they files are not evenly distributed.
One
> hold 16G while the other in 3G range. Thanks!
>|||This is a multi-part message in MIME format.
--=_NextPart_000_003A_01C37DE2.477867E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Andy,
I'm not sure this is true. Everthing should be distributed equally =across the data files stored on diferrent disk drives.
BOL:
"Filegroups use a proportional fill strategy across all the files within =each filegroup. As data is written to the filegroup, Microsoft=AE SQL =ServerT 2000 writes an amount proportional to the free space in the file =to each file within the filegroup, rather than writing all the data to =the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has 200 MB =free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved."
Thanks,
Eddie
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:%23mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
> Tables only can be assigned to a specific filegroup. When you right =click
> the table name in Enterprise Manager and script it, it will tell you =what
> file group it is on
> > eg
> > CREATE TABLE ....
> ( ...
> )
> ON PRIMARY
> > The above example is the primary group.
> > What SQL does is when you add objects to a filegroup is fill up the =first
> file, then move to the next, move to the next after that second one =was
> filled and so on. That is why you see the uneven file distribution. =You'll
> have to dig around in SQL books online, but it should be in there =somewhere.
> I learned this studying to recertify on SQL 2000.
> > -- > ***********************************
> Andy S.
> andy_mcdba@.yahoo.com
> > ***********************************
> "Kevin" <kevin@.noemail.com> wrote in message
> news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> > Is there a way to see which table belong to which file of a file =group? I
> > have a filegroup of 10 files and they files are not evenly =distributed.
> One
> > hold 16G while the other in 3G range. Thanks!
> >
> >
> >
--=_NextPart_000_003A_01C37DE2.477867E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Andy,
I'm not sure this is true. Everthing =should be distributed equally across the data files stored on diferrent disk drives.
BOL:
"Filegroups use a proportional fill =strategy across all the files within each filegroup. As data is written to the =filegroup, Microsoft=AE SQL Server=99 2000 writes an amount proportional to the =free space in the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has 200 MB free, one =extent is allocated from file f1, two extents from file f2, and so on. This way =both files become full at about the same time, and simple striping is achieved."
Thanks,
Eddie
"Andy Svendsen" =wrote in message news:%23mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...> =Tables only can be assigned to a specific filegroup. When you right click> =the table name in Enterprise Manager and script it, it will tell you =what> file group it is on> > eg> > CREATE TABLE ....> ( ...> )> ON PRIMARY> > The =above example is the primary group.> > What SQL does is when you =add objects to a filegroup is fill up the first> file, then move to =the next, move to the next after that second one was> filled and so =on. That is why you see the uneven file distribution. You'll> have =to dig around in SQL books online, but it should be in there somewhere.> =I learned this studying to recertify on SQL 2000.> > -- => ***********************************> Andy S.> andy_mcdba@.yahoo.com> > ***********************************> "Kevin" =wrote in message> news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...> > Is there a way to see which table belong to =which file of a file group? I> > have a filegroup of 10 files and =they files are not evenly distributed.> One> > hold 16G =while the other in 3G range. Thanks!> >> >> =>

--=_NextPart_000_003A_01C37DE2.477867E0--|||I am not aware of any way you can easily discover WHICH file in a filegroup
contains the datapages for a table... You COULD look at the IAM page in each
file for the table and do some stuff, but it would be manual...
--
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's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <kevin@.noemail.com> wrote in message
news:#YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file group? I
> have a filegroup of 10 files and they files are not evenly distributed.
One
> hold 16G while the other in 3G range. Thanks!
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0047_01C37DE7.0F78ACB0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. =You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_0047_01C37DE7.0F78ACB0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

And if it is so, how come that I get =significantly higher usage on one of my disks (10 times), if I have only one =filegroup, which is spread across 7 hard disks (each of them has one or two data files)? =It seems to me that practically, database engine uses diferrent =strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across all =the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space in the =file to each file within the filegroup, rather than writing all the data to =the first file until full, and then writing to the next file. For example, if =file f1 has 100 megabytes (MB) free and file f2 has 200 MB =free, one extent is allocated from file f1, two extents from file =f2, and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to accommodate =more data (provided that the database is set to grow automatically). For =example, a filegroup comprises three files, all set to automatically grow. When =space in all files in the filegroup is exhausted, only the first file is =expanded. When the first file is full, and no more data can be written to the =filegroup, the second file is expanded. When the second file is full, and no more =data can be written to the filegroup, the third file is expanded. If the third =file becomes full, and no more data can be written to the filegroup, the =first file is expanded again, and so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE ....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup is =fill up the firstfile, then move to the next, move to the next after that =second one wasfilled and so on. That is why you see the uneven file = distribution. You'llhave to dig around in SQL books online, =but it should be in there somewhere.I learned this studying to recertify =on SQL 2000.-- ***********************************Andy =S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file =group? I> have a filegroup of 10 files and they files are not evenly distributed.One> hold 16G while the other in 3G =range. Thanks!>>

--=_NextPart_000_0047_01C37DE7.0F78ACB0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0316_01C37DE9.2183E5D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
In the 2nd paragraph of the BOL extract below, it mentions what happens =with file growth. Basically, as long as you stay ahead of the file =growth yourself, it will do proportional fill. However, once everything =fills, you start expanding and filling in a round-robin fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. =You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_0316_01C37DE9.2183E5D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as you =stay ahead of the file growth yourself, it will do proportional fill. =However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get =significantly higher usage on one of my disks (10 times), if I have only one =filegroup, which is spread across 7 hard disks (each of them has one or two data files)? =It seems to me that practically, database engine uses diferrent =strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across all =the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space in the =file to each file within the filegroup, rather than writing all the data to =the first file until full, and then writing to the next file. For example, if =file f1 has 100 megabytes (MB) free and file f2 has 200 MB =free, one extent is allocated from file f1, two extents from file =f2, and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to accommodate =more data (provided that the database is set to grow automatically). For =example, a filegroup comprises three files, all set to automatically grow. When =space in all files in the filegroup is exhausted, only the first file is =expanded. When the first file is full, and no more data can be written to the =filegroup, the second file is expanded. When the second file is full, and no more =data can be written to the filegroup, the third file is expanded. If the third =file becomes full, and no more data can be written to the filegroup, the =first file is expanded again, and so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE ....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup is =fill up the firstfile, then move to the next, move to the next after that =second one wasfilled and so on. That is why you see the uneven file = distribution. You'llhave to dig around in SQL books online, =but it should be in there somewhere.I learned this studying to recertify =on SQL 2000.-- ***********************************Andy =S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file =group? I> have a filegroup of 10 files and they files are not evenly distributed.One> hold 16G while the other in 3G =range. Thanks!>>

--=_NextPart_000_0316_01C37DE9.2183E5D0--|||What the book said and reality do not go together. I read BOL and couple
books and they said the way to do horizontal partition of a database is to
create filegroups accross multiple physical disks.
I have the database of 120G and it was there since SQL 6.5 & 7.0 (currently
in SQL2K) and it is growing; therefore, the filegroups are unevenly
distributed. I create a new database from scratch with new filegroups to
gain better performance. Once it is done, I load the data from source
database to new database sequentially. Technically (based on books & BOL),
the data should be load in round-robin method, but they didn't. Couple
tables are in the 20G (data + indexes) ranges and they need horizontal
partition. If I can figure out which tables cause the unevenly
distribution, I can reload them again.
Looking IAM could help but it could take a while to get the numbers.
If there is a better to do horizontal partition, please show me how to do
it. Because BOL & couple books I got are too general in partitioning.
Thanks!
Kevin
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:e9tbCRgfDHA.1200@.TK2MSFTNGP09.phx.gbl...
> I am not aware of any way you can easily discover WHICH file in a
filegroup
> contains the datapages for a table... You COULD look at the IAM page in
each
> file for the table and do some stuff, but it would be manual...
> --
> 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's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:#YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> > Is there a way to see which table belong to which file of a file group?
I
> > have a filegroup of 10 files and they files are not evenly distributed.
> One
> > hold 16G while the other in 3G range. Thanks!
> >
> >
>|||The way you get out of balance is...
1. you add new files to the filegroup when tables with records already
exist.. SQL does NOT automatically rebalance.
2. Even tho , as others have quoted, SQL will use proportional fill to place
equivalent amounts of data across all files... users can still delete data
from a table, leaving the remaining records mostly on one filegroup...
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's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <kevin@.noemail.com> wrote in message
news:#YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file group? I
> have a filegroup of 10 files and they files are not evenly distributed.
One
> hold 16G while the other in 3G range. Thanks!
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0018_01C37DF8.031CB400
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
We don't have automatic growth option turned on on any of the data =files. We monitor and expand the files manually, according to the free =space in the database. So far, we had several data file expansions, but =only when database population was around 80%. Neather data file was =completely populated, so according to BOL, as I understand, database =engine shouldn't have started using round-robin... Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FUTuqgfDHA.944@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL extract below, it mentions what =happens with file growth. Basically, as long as you stay ahead of the =file growth yourself, it will do proportional fill. However, once =everything fills, you start expanding and filling in a round-robin =fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. = You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_0018_01C37DF8.031CB400
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

We don't have automatic growth option =turned on on any of the data files. We monitor and expand the files manually, =according to the free space in the database. So far, we had several data file expansions, but only when database population was around =80%. Neather data file was completely populated, so according to BOL, as I =understand, database engine shouldn't have started using round-robin... =Eddie
"Tom Moreau" = wrote in message news:%23FUTuqgfDHA.9=44@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as =you stay ahead of the file growth yourself, it will do proportional =fill. However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one of my disks (10 times), if I have =only one filegroup, which is spread across 7 hard disks (each of them has one =or two data files)? It seems to me that practically, database engine uses =diferrent strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across =all the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space =in the file to each file within the filegroup, rather than writing all the data =to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has =200 MB free, one extent is allocated from file f1, two extents from =file f2, and so on. This way both files become full at about the =same time, and simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to = accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all =set to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, and no =more data can be written to the filegroup, the second file is expanded. =When the second file is full, and no more data can be written to the =filegroup, the third file is expanded. If the third file becomes full, and no more =data can be written to the filegroup, the first file is expanded again, and =so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE =....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup =is fill up the firstfile, then move to the next, move to the next after =that second one wasfilled and so on. That is why you see the =uneven file distribution. You'llhave to dig around in SQL books =online, but it should be in there somewhere.I learned this studying to =recertify on SQL 2000.-- =***********************************Andy S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file group? I> have a filegroup of 10 files and they files =are not evenly distributed.One> hold 16G while the other in 3G range. Thanks!>>

--=_NextPart_000_0018_01C37DF8.031CB400--|||This is a multi-part message in MIME format.
--=_NextPart_000_047F_01C37DFB.D0ED9400
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I'll see what I can get from MS.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:ekW7MmhfDHA.3464@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option turned on on any of the data =files. We monitor and expand the files manually, according to the free =space in the database. So far, we had several data file expansions, but =only when database population was around 80%. Neather data file was =completely populated, so according to BOL, as I understand, database =engine shouldn't have started using round-robin... Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FUTuqgfDHA.944@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL extract below, it mentions what =happens with file growth. Basically, as long as you stay ahead of the =file growth yourself, it will do proportional fill. However, once =everything fills, you start expanding and filling in a round-robin =fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. = You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_047F_01C37DFB.D0ED9400
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I'll see what I can get from =MS.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eddie" =wrote in message news:ekW7MmhfDHA.3464=@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option =turned on on any of the data files. We monitor and expand the files manually, =according to the free space in the database. So far, we had several data file expansions, but only when database population was around =80%. Neather data file was completely populated, so according to BOL, as I =understand, database engine shouldn't have started using round-robin... =Eddie
"Tom Moreau" = wrote in message news:%23FUTuqgfDHA.9=44@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as =you stay ahead of the file growth yourself, it will do proportional =fill. However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one of my disks (10 times), if I have =only one filegroup, which is spread across 7 hard disks (each of them has one =or two data files)? It seems to me that practically, database engine uses =diferrent strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across =all the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space =in the file to each file within the filegroup, rather than writing all the data =to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has =200 MB free, one extent is allocated from file f1, two extents from =file f2, and so on. This way both files become full at about the =same time, and simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to = accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all =set to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, and no =more data can be written to the filegroup, the second file is expanded. =When the second file is full, and no more data can be written to the =filegroup, the third file is expanded. If the third file becomes full, and no more =data can be written to the filegroup, the first file is expanded again, and =so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE =....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup =is fill up the firstfile, then move to the next, move to the next after =that second one wasfilled and so on. That is why you see the =uneven file distribution. You'llhave to dig around in SQL books =online, but it should be in there somewhere.I learned this studying to =recertify on SQL 2000.-- =***********************************Andy S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file group? I> have a filegroup of 10 files and they files =are not evenly distributed.One> hold 16G while the other in 3G range. Thanks!>>

--=_NextPart_000_047F_01C37DFB.D0ED9400--|||This is a multi-part message in MIME format.
--=_NextPart_000_002C_01C37E13.7C7581E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Could you please post the version of SQL Server you are using?
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Eddie" <espasojevic@.rogers.com> wrote in message =news:ekW7MmhfDHA.3464@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option turned on on any of the data =files. We monitor and expand the files manually, according to the free =space in the database. So far, we had several data file expansions, but =only when database population was around 80%. Neather data file was =completely populated, so according to BOL, as I understand, database =engine shouldn't have started using round-robin... Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FUTuqgfDHA.944@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL extract below, it mentions what =happens with file growth. Basically, as long as you stay ahead of the =file growth yourself, it will do proportional fill. However, once =everything fills, you start expanding and filling in a round-robin =fashion.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eddie" <espasojevic@.rogers.com> wrote in message =news:#EoHZigfDHA.3528@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one =of my disks (10 times), if I have only one filegroup, which is spread =across 7 hard disks (each of them has one or two data files)? It seems =to me that practically, database engine uses diferrent strategy...
Eddie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eammxQgfDHA.128@.tk2msftngp13.phx.gbl...
Not quite. It uses a proportional fill strategy. From the BOL:
"Filegroups use a proportional fill strategy across all the files =within each filegroup. As data is written to the filegroup, Microsoft=AE =SQL Server=99 2000 writes an amount proportional to the free space in =the file to each file within the filegroup, rather than writing all the =data to the first file until full, and then writing to the next file. =For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 =MB free, one extent is allocated from file f1, two extents from file f2, =and so on. This way both files become full at about the same time, and =simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server =automatically expands one file at a time in a round-robin fashion to =accommodate more data (provided that the database is set to grow =automatically). For example, a filegroup comprises three files, all set =to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, =and no more data can be written to the filegroup, the second file is =expanded. When the second file is full, and no more data can be written =to the filegroup, the third file is expanded. If the third file becomes =full, and no more data can be written to the filegroup, the first file =is expanded again, and so on."
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andy Svendsen" <andy_mcdba@.yahoo.com> wrote in message =news:#mvLcGgfDHA.2236@.TK2MSFTNGP12.phx.gbl...
Tables only can be assigned to a specific filegroup. When you right =click
the table name in Enterprise Manager and script it, it will tell you =what
file group it is on
eg
CREATE TABLE ....
( ...
)
ON PRIMARY
The above example is the primary group.
What SQL does is when you add objects to a filegroup is fill up the =first
file, then move to the next, move to the next after that second one =was
filled and so on. That is why you see the uneven file distribution. = You'll
have to dig around in SQL books online, but it should be in there =somewhere.
I learned this studying to recertify on SQL 2000.
-- ***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin" <kevin@.noemail.com> wrote in message
news:%23YdaQAgfDHA.576@.tk2msftngp13.phx.gbl...
> Is there a way to see which table belong to which file of a file =group? I
> have a filegroup of 10 files and they files are not evenly =distributed.
One
> hold 16G while the other in 3G range. Thanks!
>
>
--=_NextPart_000_002C_01C37E13.7C7581E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Could you please post the version of =SQL Server you are using?
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Eddie" =wrote in message news:ekW7MmhfDHA.3464=@.TK2MSFTNGP11.phx.gbl...
We don't have automatic growth option =turned on on any of the data files. We monitor and expand the files manually, =according to the free space in the database. So far, we had several data file expansions, but only when database population was around =80%. Neather data file was completely populated, so according to BOL, as I =understand, database engine shouldn't have started using round-robin... =Eddie
"Tom Moreau" = wrote in message news:%23FUTuqgfDHA.9=44@.TK2MSFTNGP11.phx.gbl...
In the 2nd paragraph of the BOL =extract below, it mentions what happens with file growth. Basically, as long as =you stay ahead of the file growth yourself, it will do proportional =fill. However, once everything fills, you start expanding and filling in a round-robin fashion.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Eddie" =wrote in message news:#EoHZigfDHA.3528=@.tk2msftngp13.phx.gbl...
And if it is so, how come that I get significantly higher usage on one of my disks (10 times), if I have =only one filegroup, which is spread across 7 hard disks (each of them has one =or two data files)? It seems to me that practically, database engine uses =diferrent strategy...
Eddie
"Tom Moreau" = wrote in message news:eammxQgfDHA.128@.t=k2msftngp13.phx.gbl...
Not quite. It uses a =proportional fill strategy. From the BOL:

"Filegroups use a proportional fill strategy across =all the files within each filegroup. As data is written to the filegroup, =Microsoft=AE SQL Server=99 2000 writes an amount proportional to the free space =in the file to each file within the filegroup, rather than writing all the data =to the first file until full, and then writing to the next file. For =example, if file f1 has 100 megabytes (MB) free and file f2 has =200 MB free, one extent is allocated from file f1, two extents from =file f2, and so on. This way both files become full at about the =same time, and simple striping is achieved. As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to = accommodate more data (provided that the database is set to grow automatically). For example, a filegroup comprises three files, all =set to automatically grow. When space in all files in the filegroup is =exhausted, only the first file is expanded. When the first file is full, and no =more data can be written to the filegroup, the second file is expanded. =When the second file is full, and no more data can be written to the =filegroup, the third file is expanded. If the third file becomes full, and no more =data can be written to the filegroup, the first file is expanded again, and =so on."
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Andy Svendsen" =wrote in message news:#mvLcGgfDHA.2236=@.TK2MSFTNGP12.phx.gbl...Tables only can be assigned to a specific filegroup. When you right clickthe table name in Enterprise Manager and script it, it will =tell you whatfile group it is onegCREATE TABLE =....( ...)ON PRIMARYThe above example is the primary group.What SQL does is when you add objects to a filegroup =is fill up the firstfile, then move to the next, move to the next after =that second one wasfilled and so on. That is why you see the =uneven file distribution. You'llhave to dig around in SQL books =online, but it should be in there somewhere.I learned this studying to =recertify on SQL 2000.-- =***********************************Andy S.andy_mcdba@.yahoo.com****=*******************************"Kevin" =wrote in messagenews:%23YdaQAgfDHA.5=76@.tk2msftngp13.phx.gbl...> Is there a way to see which table belong to which file of a file group? I> have a filegroup of 10 files and they files =are not evenly distributed.One> hold 16G while the other in 3G range. Thanks!>>

--=_NextPart_000_002C_01C37E13.7C7581E0--

Filegroup

I am looking for a Microsoft article to explain how to convert from primary
file group to multi file group. I have a SQL Server 2000 database with
numerous tables, stored procedures, and views. Additional articles would be
helpful to explain the benefit of using file groups and how they function.
Thank You,
Hi
Visually everything that needs to be said is on BOL under the heading
"filegroups".
What else do you need to know?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:16CF3E05-AB29-4B41-90F8-8CF2FDE48D80@.microsoft.com...
> I am looking for a Microsoft article to explain how to convert from
primary
> file group to multi file group. I have a SQL Server 2000 database with
> numerous tables, stored procedures, and views. Additional articles would
be
> helpful to explain the benefit of using file groups and how they function.
> Thank You,
>
>

Filegroup

I am looking for a Microsoft article to explain how to convert from primary
file group to multi file group. I have a SQL Server 2000 database with
numerous tables, stored procedures, and views. Additional articles would be
helpful to explain the benefit of using file groups and how they function.
Thank You,Hi
Visually everything that needs to be said is on BOL under the heading
"filegroups".
What else do you need to know?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:16CF3E05-AB29-4B41-90F8-8CF2FDE48D80@.microsoft.com...
> I am looking for a Microsoft article to explain how to convert from
primary
> file group to multi file group. I have a SQL Server 2000 database with
> numerous tables, stored procedures, and views. Additional articles would
be
> helpful to explain the benefit of using file groups and how they function.
> Thank You,
>
>

Sunday, February 26, 2012

File Share Delivery Error

I have given subscription access to a domain group in reporting services.
Well when the users create a file share subscription and the subscription
launches they are getting the message "Failure writing file .pdf : Error
impersonating user." Thanks,
--
Andrew
MCSA,MCDBAThe error is stating that RS can not log on as the user that you configured
the subscription to use. It has nothing to do with the share permissions.
You should make sure that user can log onto the machine that RS is running
on.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:6E473576-5F7A-430D-AB70-A0EB8A4C7C60@.microsoft.com...
>I have given subscription access to a domain group in reporting services.
> Well when the users create a file share subscription and the subscription
> launches they are getting the message "Failure writing file .pdf : Error
> impersonating user." Thanks,
> --
> Andrew
> MCSA,MCDBA|||Daniel,
Thanks for the advice I discovered the only group granted log on locally
rights was the administrators group. Thanks again,
--
Andrew
MCSA,MCDBA
"Daniel Reib [MSFT]" wrote:
> The error is stating that RS can not log on as the user that you configured
> the subscription to use. It has nothing to do with the share permissions.
> You should make sure that user can log onto the machine that RS is running
> on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:6E473576-5F7A-430D-AB70-A0EB8A4C7C60@.microsoft.com...
> >I have given subscription access to a domain group in reporting services.
> > Well when the users create a file share subscription and the subscription
> > launches they are getting the message "Failure writing file .pdf : Error
> > impersonating user." Thanks,
> >
> > --
> > Andrew
> > MCSA,MCDBA
>
>

Sunday, February 19, 2012

File Groups: moving a large table

We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?

The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.

Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?

Like I said a rough estimate is fine.

Many thanks,

Scott

Well, 75 GB of data to move, 1 GB dedicated NIC/SAN. Unknowns include: amount and complexity of indexing, other demands on the server and SAN...

With little other activity, my WAG* is that you will be involved for at least 2-3 hours -and it could go up quite a bit if there are a lot of indexes and other demands on the SAN pipe.

Best widow of opportunity -NOT 9-5, Mon-Fri!

Yes, a 'normal' FULL backup will backup all files for the database.

(With my two points, I would like to buy a vowel please...)

* Wild A$$ed Guess

|||Thanks Arnie!
|||Should be noted that the process will halt if the log file grows larger that the disk it lives on can handle.

pingcarlo@.gmail.com
pingchuck@.hotmail.com
pingccat@.yahoo.com

File Groups

Hi everyone,
While creating our database in only one disc(C or D), suppose that we create more than one file group in order to group our data files. However, in this situation; I wonder that whether it brings any benefit or advantage to us.

Also, I wonder that why we always have to put our data file into separate file group if we use separate discs for data files. Is not it allowed to use only one file group even if we use separate dics ?

Would you explain these to me ?

Thanks

If youre on one disk, there's not much difference. If you have 2 disks, putting log on separate disk is the most common and efficient solution in most cases.
For a typical database application, the best configuration of hardware and an average one are not likely to differ dramaticaly in terms of performance. Performance tuning is almost 90% done at SQL and application layers.

But putting log on separate disk always helps ;)

|||

Thanks for your reply but it seems to me a little bit confusing.
Would you mind giving some examples about the efficiency and high level performance of File Groups ?

Thanks

|||

Database transaction typically write some data to the disk, and some data to the log. This means constant log file access for writing. Meanwhile, when there are no failures, this writes can be performed sequentially in large chunks. This dramatically decreases disk access time, because significant part of time that disks need to perform an operation(read or write) consumes "seeking" disk head to the desired position, because this is mechanical operation. Sequential writes avoid seeks.
This is the main thing I could think of given no details. In a rare case if your application is very read-intensive (OLAP), then you might consider partitioning data files among disks to fasten reads.

In a mixed (OLTP) application, consider doing the following, depending on how many disks you have:

A separate disk for all log files, maybe with redundancy(mirroring).
Then, consider separating secondary (non-clustered) indexes from data files.
Then consider separating data files.

|||

Hi,
First of all, thanks for your all replies and thanks for spnding your time for my questions.
I think that I finally kept the idea about the efficiency and high level performance which I want to demonstrate.
For instance, suppose that we recall some records from two different table in one databases and we do this by using inner join. And if we put this two table into different file groups which are located in different discs, then it is really more efficient to call the column of these tables than other situation, is not it ?

Briefly, we do this application in order to share the whole power to the to the discs so that at one time, we can do many things faster than before.

Am I right or wrong ?

Thanks

|||

merit,
Your scheme of splitting data among disks works perfectly with you example provided, that is, when overhelming part of transactions are read-only. Otherwise, if you have read-write (OLTP) application and only 2 disks, just put the data in one disk and log to the other.

|||

Hi,

Again thanks for your reply.

While I was reading a tutorial abou this topic, I heard that The CREATE DATABASE statement is a multithreaded process that creates one process thread for each logical device.

What does this mean ?

Would you please explain it to me ?

|||

Hi,

Another good and significant example about the efficiency of using separate file group in different discs is SELECT INTO statement. Because, while performing this , you both read data from a table and then write this data to another table. Thus, if we try to use different discs for both reading data and writing data, we will have increased the performance and speed of the program.

Menawhile, the another reason for this is that while performing some operations, every disc are used by the system at the same time, is not it ?

Thanks,

Mert

|||

I guess not.

The main point is that, as I mentioned earlier, when you write into some table, you simultaneously write into log too. In this case, it's much better to put data files to one disks and put log to separate disk. Never forget that log is intensively used during write transactions. In your example, you completely do not consider log writes.

|||

Hi,
You suggest me to put log to different discs which I also appreciate since it is obvious that this brings performance benefit to the dics.
But at the following link , it supports what I said about SELECT INTO.
Reads from one discs and write to another table which is located in different file group in different disc, so do you really think that this does not increase the performance ?
Suppose that you performs both operations from the same discs. So while doing this, it is likely that your disc drive spends more power to do it and more time to do it.
So I would like to learn the main reason why you think that only, putting log files into different disc can bring high level performance.

http://www.dell.com/content/topics/global.aspx/power/en/ps2q03se_rosetta?c=us&cs=555&l=en&s=biz

Thanks,
Mert

|||

That's all correct.
In the link you provided those guys featured the following disk configurations:

<Quote>Storage consisted of two EMC^? CLARiiON^? FC4700 storage arrays and two EMC ^? 8530 storage arrays. Each of the EMC CLARiiON FC4700 storage arrays had a 4 GB write-read cache, while each EMC Symmetrix 8530 storage array had a 16 GB write-read cache. Each of the four storage arrays included 96 disks capable of 73 GB and 10,000 rotations per minute (RPM), for a total of 28 TB of raw disk space (12 TB after RAID-1+0 configuration).<Quote>

It is obvious that they had put log into other drive too, see it here:

<Quote>The Rosetta Genomics application database files totaled 10.35 TB for data files and 135 GB for the log file. Administrators defined both a primary, or default, file group and a user-defined file group. The default file group had one data file containing database system objects. Located on the default drive C, the default file group shared drive space with SQL Server binaries and the operating system. The user-defined file group was configured with 40 fixed-size 131 GB data files on CLARiiON arrays and 40 fixed-size 135 GB data files on Symmetrix arrays, for a total of 80 data files?one for each LUN designated for database file storage. Mount points were configured at 132 GB on CLARiiON arrays and 136 GB on Symmetrix arrays.<Quote>

Indeed, if you have SUCH storage capacitites, you can put everything on different drives. In my earler post I wrote that if you have ENOUGH disks you can split data files too. Anyhow, given TWO disks and a WRITE-INTENSIVE application, it is almost always the best practice to separate log from data files.
Once again, if you have such opportunity, indeed, split data files too! I was all about priorities, and the wonderful article you provided says nothing about putting log into one disk and distributing data.
I'd reccomend you the wonderful book of D. Shasha and P. Bonnet, "Database Tuning" for very thorough discussion of the topic.
http://www.amazon.com/gp/product/1558607536/sr=8-1/qid=1151403308/ref=pd_bbs_1/103-2557871-6722269?ie=UTF8

P.S.
Applications with extremely large data storage requirements very often mean that the application is OLAP one, that is, a read-intensive application, when everything I said should be reversed. In those applications, log file is no more a hot spot.

P.P.S. merit, I am just too curious not to ask, is your disk configuraton something (even remotely) close to the configuration in the article?

|||

Thanks for your interest to this thread,

Andranik Khachatryan

For your question, I ask everything since I am also curious about them, not for application on my computers or any problem that I encounter.

Meanwhile, I am studying to the MCAD exam that's why I ask lots of questions recently.

Best wishes,

Mert

|||

Hi,
There something make me still confused about this topic.
For example, when we call some columns from a table with Select query statement, do the SQL server do this by first finding the file group that this table located in and then finding the corresopnding table ?

Thanks

File group space error

Hi,

Received the following error during index creation of the tables. The
data & log files are set to 'unrestricted growth' and enough space
available on the disk. Any reasons?

___________
Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocate
new page for database 'Ultimareports'. There are no more pages available
in filegroup PRIMARY. Space can be created by dropping objects, adding
additional files, or allowing file growth
___________

Thanks
John Jayaseelan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!John Jayaseelan <john.jayaseelan@.caravan-club.co.uk> wrote in message news:<3fc34206$0$195$75868355@.news.frii.net>...
> Hi,
> Received the following error during index creation of the tables. The
> data & log files are set to 'unrestricted growth' and enough space
> available on the disk. Any reasons?
> ___________
> Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocate
> new page for database 'Ultimareports'. There are no more pages available
> in filegroup PRIMARY. Space can be created by dropping objects, adding
> additional files, or allowing file growth
> ___________
> Thanks
> John Jayaseelan
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It's not clear what the issue is without some more information. Do
sp_helpfile and sp_helpfilegroup return what you expect to see? How
big is the database, what is the autogrow increment set to, and what
is the filesystem? Are there any possible external factors, like disk
quotas?

Simon

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
>

Thursday, March 29, 2012

Filter does not work on a group?

Monday, March 26, 2012

Fill up missing months.

Fill stops /timeouts

Fill footer dynamically by group

Monday, March 12, 2012

Filegroup restore Issue

Filegroup restore Issue

Filegroup recommendation

Friday, March 9, 2012

Filegroup & tables

Filegroup

Filegroup

Sunday, February 26, 2012

File Share Delivery Error

Sunday, February 19, 2012

File Groups: moving a large table

File Groups

File group space error

file group question

file group question

Format Alignment

Blog Archive

About Me