Format Alignment: filegroup

Showing posts with label filegroup. Show all posts

Friday, March 23, 2012

filestats

Hi ,
In 2k there is dbcc command dbcc showfilestats to see used & free extents in
filegroup , what is equivalent in Yukon or shall we continue using the same
.
Thanks
ARRcheck out sys.dm.db.index_physical_stats in BooksOnLine.
Andrew J. Kelly SQL MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> Hi ,
> In 2k there is dbcc command dbcc showfilestats to see used & free extents
> in
> filegroup , what is equivalent in Yukon or shall we continue using the
> same
> .
> Thanks
> ARR
>|||sys.dm_db_index_physical_stats does not report per file does it? Only per
object.
select fileproperty('filename', 'SpaceUsed') reports the amount of space
allocated but not free.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> check out sys.dm.db.index_physical_stats in BooksOnLine.
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free extents
>> in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>|||Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent of
dbcc showfilestats column UsedExtents * 8
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>|||And the equivalent of TotalExtents can be found in: select size from
sys.database_files or select size from sys.master_files
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
> of dbcc showfilestats column UsedExtents * 8
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>> sys.dm_db_index_physical_stats does not report per file does it? Only per
>> object.
>> select fileproperty('filename', 'SpaceUsed') reports the amount of space
>> allocated but not free.
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>>
>|||Sorry I was thinking showcontig<g>.
Andrew J. Kelly SQL MVP
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> sys.dm_db_index_physical_stats does not report per file does it? Only per
> object.
> select fileproperty('filename', 'SpaceUsed') reports the amount of space
> allocated but not free.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>|||Last one to wrap this up, this would be the ultimate Yukon way of retrieving
the data today you get from showfilestats (which will remains an
undocumented DBCC command in Yukon as well).
select 'FileId' = file_id,
'FileGroup' = data_space_id,
'TotalExtents' = size/8,
'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
'Name' = name,
'FileName' = physical_name
from sys.database_files
where type = 0
and state = 0
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> And the equivalent of TotalExtents can be found in: select size from
> sys.database_files or select size from sys.master_files
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
>> Sorry, the select fileproperty('filename', 'SpaceUsed') is the equivalent
>> of dbcc showfilestats column UsedExtents * 8
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
>> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>> sys.dm_db_index_physical_stats does not report per file does it? Only
>> per object.
>> select fileproperty('filename', 'SpaceUsed') reports the amount of space
>> allocated but not free.
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2005 All rights reserved.
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
>> check out sys.dm.db.index_physical_stats in BooksOnLine.
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Aju" <ajuonline@.yahoo.com> wrote in message
>> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
>> Hi ,
>> In 2k there is dbcc command dbcc showfilestats to see used & free
>> extents in
>> filegroup , what is equivalent in Yukon or shall we continue using the
>> same
>> .
>> Thanks
>> ARR
>>
>>
>>
>>
>|||Thanks a lot .
This helped me alot .
Thanks
ARR
"Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
news:#Jr$nhvAFHA.4044@.TK2MSFTNGP14.phx.gbl...
> Last one to wrap this up, this would be the ultimate Yukon way of
retrieving
> the data today you get from showfilestats (which will remains an
> undocumented DBCC command in Yukon as well).
> select 'FileId' = file_id,
> 'FileGroup' = data_space_id,
> 'TotalExtents' = size/8,
> 'UsedExtents' = fileproperty (name, 'SpaceUsed')/8,
> 'Name' = name,
> 'FileName' = physical_name
> from sys.database_files
> where type = 0
> and state = 0
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> news:es9H7AvAFHA.2192@.TK2MSFTNGP14.phx.gbl...
> > And the equivalent of TotalExtents can be found in: select size from
> > sys.database_files or select size from sys.master_files
> >
> > GertD@.SQLDev.Net
> >
> > Please reply only to the newsgroups.
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > You assume all risk for your use.
> > Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >
> > "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> > news:eYFWV8uAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> >> Sorry, the select fileproperty('filename', 'SpaceUsed') is the
equivalent
> >> of dbcc showfilestats column UsedExtents * 8
> >>
> >> GertD@.SQLDev.Net
> >>
> >> Please reply only to the newsgroups.
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> You assume all risk for your use.
> >> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >>
> >> "Gert E.R. Drapers" <GertD@.SQLDev.Net> wrote in message
> >> news:%234i4n6uAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> >> sys.dm_db_index_physical_stats does not report per file does it? Only
> >> per object.
> >> select fileproperty('filename', 'SpaceUsed') reports the amount of
space
> >> allocated but not free.
> >>
> >> GertD@.SQLDev.Net
> >>
> >> Please reply only to the newsgroups.
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> You assume all risk for your use.
> >> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:%23YlelvuAFHA.3504@.TK2MSFTNGP12.phx.gbl...
> >> check out sys.dm.db.index_physical_stats in BooksOnLine.
> >>
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Aju" <ajuonline@.yahoo.com> wrote in message
> >> news:eZ28iWtAFHA.1392@.tk2msftngp13.phx.gbl...
> >> Hi ,
> >> In 2k there is dbcc command dbcc showfilestats to see used & free
> >> extents in
> >> filegroup , what is equivalent in Yukon or shall we continue using
the
> >> same
> >> .
> >>
> >> Thanks
> >> ARR
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>

Wednesday, March 21, 2012

files, filegroups

Can someone tell me if we can move files to different filegroups in SQL2K5? If we can, how do you do it?

Here is an excerpt from BOL.

REMOVE FILEGROUP filegroup_name
Removes a filegroup from the database. The filegroup cannot be removed unless it is empty. Remove all files from the filegroup first by either ***moving the files to another filegroup*** or, if the files are empty, by removing the files.

Is that a typo or what?

Thanks.I am hoping Microsoft folks will reply to this. Please let me know if the BOL has an error or you can actually move files among filegroups.
|||

This is a doc error. We can not move files between filegroups.

I've reported the problem to our doc team.

Thanks for reporting it here.

sql

Files & FileGroups

Hey guys I have a VLDB and I would like to point or move several tables to another filegroup.

1). Add a filegroup to the database
ALTER DATABASE dbname
ADD FILEGROUP filename_table
go

2). Add a file to the file group
ALTER DATABASE dbname
ADD FILE
....
TO FILEGROUP filename_table
go

If a table is already part of the primary filegroup:
Can I change it to another filegroup?
When changed to the another filegroup will it move the whole table to the other filegroup or just start to write data to the newly created filegroup?In order to move the table to another filegroup, you need to first move the data, then move any indexes that you also want on that filegroup or another filegroup. To move the data, you need to create or alter the clustered index. To move the indicies, you need to drop and recreate the indicies.

-PatP|||thanks Pat for the heads up I will work towards that...

Filegroups, CPU, & RAID

Not sure if this is the right forum...
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>sql

Filegroups, CPU, & RAID

Not sure if this is the right forum...
I have a table that has its own filegroup on a dedicated RAID 10 array.
Somewhere in Books Online I read that you should create a file in the
filegroup for each CPU in the server. My understanding is that in SQL 2000,
this triggered the server to spawn multiple threads to handle the I/O. My
questions are:
1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
2 - If not, being on a RAID 10 array, does it help in any way to have
multiple files in a group?
Thanks,
-- Brian"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
>
It doesn't matter. You don't want multiple threads for IO hitting a single
volume. One thread is quite enough to saturate your IO channel. A second
thread issuing IO requests over the channel isn't going to improve
performance.

> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
>
Multiple files per filegroup and RAID striping both accomplish the same
thing, and there is no need to use both. Both are mechanisms to spread IO
operations targeting a single table or index over a number of spindles.
David|||The "filegroup/file per CPU" rule went out with SQL 7.0. SQL 2000 will spin
up more IO threads as necessary until the controller is saturated. Multiple
files may make it easier to manage moving files due to later growth but will
give zero performance increase if the files are on the same underlying RAID
container. I personally prefer to keep files sizes doen to 100GB or smaller
on all but the largest systems.
There are some "File/CPU" considerations for tempdb for both SQL 2000 and
SQL 2005, but those apply on only the highest volume systems.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Brian Shelden" <BrianShelden@.discussions.microsoft.com> wrote in message
news:EEB24CCA-E759-45EE-8514-5BF3C996B5BC@.microsoft.com...
> Not sure if this is the right forum...
> I have a table that has its own filegroup on a dedicated RAID 10 array.
> Somewhere in Books Online I read that you should create a file in the
> filegroup for each CPU in the server. My understanding is that in SQL
> 2000,
> this triggered the server to spawn multiple threads to handle the I/O. My
> questions are:
> 1 - Does SQL 2005 need multiple files to spawn multiple threads for I/O?
> 2 - If not, being on a RAID 10 array, does it help in any way to have
> multiple files in a group?
> Thanks,
> -- Brian
>

filegroups table

Hi!!!
I would like to know what filegroup is in the table or what tables are in the filegroup, using SELECT.
Excume my english.
Thank you.May check DBCC CHECKFILEGROUP which returns part of information required.|||It is found in sysindexes.
The data pages will be id = 0 if no clustered index or 1 if there is a clustered index.

groupid will give the filegroup id.

You can find the filegroup corresponding to the groupid in sysfiles.

Filegroups question

Hello everyone -

Currently the setup is a single filegroup sql machine
with each company as a new database in a single system.

Would breaking the companies into thier own filegroups
be benefitial?

or perhaps creating a instance of SQL for each company be a better approach?

thoughts / ideas would be appreciated

thanks
tonyWould breaking the companies into thier own filegroups
be benefitial?

Only if those filegroups reside on different actual drives, you might gain the performance benefit of different companies not impacting eachother performance.

or perhaps creating a instance of SQL for each company be a better approach?

Only if you have given the customer sysadmin or someother sql server wide access which I would sincerely discourage. That way the differrent customers could not access eachothers stuff.|||Thanks for the reply

I appreciate it!

take care
tony

Monday, March 19, 2012

Filegroups and RAID 1+0

Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#
http://msdn.microsoft.com/library/en...es_02_2ak3.asp
David Portas
SQL Server MVP
|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegr oups.com...
> http://msdn.microsoft.com/library/en...es_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>

Filegroups and RAID 1+0

Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/e...des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/e...des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>

Filegroups and RAID 1+0

Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
--
David Portas
SQL Server MVP
--|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegroups.com...
> http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>

Filegroups and Joins

Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
LeilaThat's correct. You cannot direct a table or index to a particular file -
only to a filegroup. What the author probably meant to say was to place the
tables into separate filegroups on different drives.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
Leila|||Leila
CREATE DATABASE test
GO
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I read this tip in a article:
> "For very large joins, consider placing the tables to be joined in
> separate physical files in the same filegroup"
> How can I force SQL Server to create a table in filegroup on a particular
> file? As far as I know, I can tell SQL Server to create table on desired
> filegroup, but if that filegroup contains more than one file, is it
possible
> to put table on a particular one?
> Thanks in advance.
> Leila
>|||Thanks Uri,
But you have placed two tables on two different filegroups. The author
mentioned that the tables can be placed in two different files, on the SAME
filegroup!
Leila
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Leila
> CREATE DATABASE test
> GO
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
>
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > I read this tip in a article:
> > "For very large joins, consider placing the tables to be joined in
> > separate physical files in the same filegroup"
> >
> > How can I force SQL Server to create a table in filegroup on a
particular
> > file? As far as I know, I can tell SQL Server to create table on desired
> > filegroup, but if that filegroup contains more than one file, is it
> possible
> > to put table on a particular one?
> >
> > Thanks in advance.
> > Leila
> >
> >
>|||The author was wrong. You cannot place an object on a particular file, only
a particular filegroup.
The space for the object is then allocated more or less evenly across all
files in that filegroup.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> Thanks Uri,
> But you have placed two tables on two different filegroups. The author
> mentioned that the tables can be placed in two different files, on the
> SAME
> filegroup!
> Leila
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> Leila
>> CREATE DATABASE test
>> GO
>> ALTER DATABASE test
>> ADD FILEGROUP ww_Group
>> GO
>> ALTER DATABASE test
>> ADD FILE
>> ( NAME = ww,
>> FILENAME = 'D:\wwdat1.ndf',
>> SIZE = 5MB,
>> MAXSIZE = 100MB,
>> FILEGROWTH = 5MB)
>> TO FILEGROUP ww_Group
>> create table test..test(id int identity) on [primary]
>> create table test..test_GR(id int identity) on ww_Group
>>
>>
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> > I read this tip in a article:
>> > "For very large joins, consider placing the tables to be joined in
>> > separate physical files in the same filegroup"
>> >
>> > How can I force SQL Server to create a table in filegroup on a
> particular
>> > file? As far as I know, I can tell SQL Server to create table on
>> > desired
>> > filegroup, but if that filegroup contains more than one file, is it
>> possible
>> > to put table on a particular one?
>> >
>> > Thanks in advance.
>> > Leila
>> >
>> >
>>
>|||Thanks Kalen,
What about log files? If I have more than one log file for my DB, are the
logged information written in multiple files or the log files are filled one
by one?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> The author was wrong. You cannot place an object on a particular file,
only
> a particular filegroup.
> The space for the object is then allocated more or less evenly across all
> files in that filegroup.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > Thanks Uri,
> > But you have placed two tables on two different filegroups. The author
> > mentioned that the tables can be placed in two different files, on the
> > SAME
> > filegroup!
> >
> > Leila
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> Leila
> >> CREATE DATABASE test
> >> GO
> >> ALTER DATABASE test
> >> ADD FILEGROUP ww_Group
> >> GO
> >> ALTER DATABASE test
> >> ADD FILE
> >> ( NAME = ww,
> >> FILENAME = 'D:\wwdat1.ndf',
> >> SIZE = 5MB,
> >> MAXSIZE = 100MB,
> >> FILEGROWTH = 5MB)
> >> TO FILEGROUP ww_Group
> >>
> >> create table test..test(id int identity) on [primary]
> >> create table test..test_GR(id int identity) on ww_Group
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> > Hi,
> >> > I read this tip in a article:
> >> > "For very large joins, consider placing the tables to be joined in
> >> > separate physical files in the same filegroup"
> >> >
> >> > How can I force SQL Server to create a table in filegroup on a
> > particular
> >> > file? As far as I know, I can tell SQL Server to create table on
> >> > desired
> >> > filegroup, but if that filegroup contains more than one file, is it
> >> possible
> >> > to put table on a particular one?
> >> >
> >> > Thanks in advance.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||One by one. SQL Server "rattles through the log files like a snake", where the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Love the analogy, Tibor! ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23vZOx3tEHA.1356@.TK2MSFTNGP11.phx.gbl...
One by one. SQL Server "rattles through the log files like a snake", where
the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message
news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled
one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across
all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

filegroups / reindex

Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk Jan
Does it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel
|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:

> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>
|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel
|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:

> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>
|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel
|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:

> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>

filegroups / reindex

Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:

> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:

> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:

> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>

filegroups / reindex

Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:
> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:
> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:
> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>

Filegroup with SQL Server virtual server

What the best way to move a SQL Server 2000 virtual server with Austin_Sales
as the virtual server name and REPT as the name instance set but as a
non-filegroup. Create a new SQL Server 2000 virtual server with a named
instance get the data from Austin_Sales\REPT database into the new filegroup
database?
Thanks,
Restore will give you (essentially) a binary image of what is inside the
database. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before
the backup or after the
restore...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster with
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database.
Can I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,
That depends on what goal you are after with file groups. What are you
trying to accomplish?
Sincerely,
Anthony Thomas
"Joe K." wrote:

> What the best way to move a SQL Server 2000 virtual server with Austin_Sales
> as the virtual server name and REPT as the name instance set but as a
> non-filegroup. Create a new SQL Server 2000 virtual server with a named
> instance get the data from Austin_Sales\REPT database into the new filegroup
> database?
> Thanks,
>
> Restore will give you (essentially) a binary image of what is inside the
> database. I.e., you cannot
> change filegroup layout through backup/restore. You'd have to do that before
> the backup or after the
> restore...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
> news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster with
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database.
> Can I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,
>

Filegroup with SQL Server virtual server

What the best way to move a SQL Server 2000 virtual server with Austin_Sales
as the virtual server name and REPT as the name instance set but as a
non-filegroup. Create a new SQL Server 2000 virtual server with a named
instance get the data from Austin_Sales\REPT database into the new filegroup
database?
Thanks,
Restore will give you (essentially) a binary image of what is inside the
database. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before
the backup or after the
restore...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster with
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database.
Can I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,That depends on what goal you are after with file groups. What are you
trying to accomplish?
Sincerely,
Anthony Thomas
"Joe K." wrote:
> What the best way to move a SQL Server 2000 virtual server with Austin_Sales
> as the virtual server name and REPT as the name instance set but as a
> non-filegroup. Create a new SQL Server 2000 virtual server with a named
> instance get the data from Austin_Sales\REPT database into the new filegroup
> database?
> Thanks,
>
> Restore will give you (essentially) a binary image of what is inside the
> database. I.e., you cannot
> change filegroup layout through backup/restore. You'd have to do that before
> the backup or after the
> restore...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
> news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster with
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database.
> Can I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,
>

Filegroup with SQL Server virtual server

What the best way to move a SQL Server 2000 virtual server with Austin_Sales
as the virtual server name and REPT as the name instance set but as a
non-filegroup. Create a new SQL Server 2000 virtual server with a named
instance get the data from Austin_Sales\REPT database into the new filegroup
database?
Thanks,
Restore will give you (essentially) a binary image of what is inside the
database. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before
the backup or after the
restore...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster wit
h
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database.
Can I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,That depends on what goal you are after with file groups. What are you
trying to accomplish?
Sincerely,
Anthony Thomas
"Joe K." wrote:

> What the best way to move a SQL Server 2000 virtual server with Austin_Sal
es
> as the virtual server name and REPT as the name instance set but as a
> non-filegroup. Create a new SQL Server 2000 virtual server with a named
> instance get the data from Austin_Sales\REPT database into the new filegro
up
> database?
> Thanks,
>
> Restore will give you (essentially) a binary image of what is inside the
> database. I.e., you cannot
> change filegroup layout through backup/restore. You'd have to do that befo
re
> the backup or after the
> restore...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
> news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster w
ith
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database.
> Can I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,
>

Filegroup with no file, not possible to remove it!

Hi All,
I have a problem with one of my filegroup. Every files have been removed,
but when I try to remove the filegroup, I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
When I run the following query, to see which filegroup have no
allocation_unit:
SELECT * FROM sys.filegroups f
WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
i.data_space_id = f.data_space_id)
The Filegroup named 'TEXT' is returned.
Does anyone have an idea to know why I cannot remove this filegroup?
Regards,
JeremyAs a sanity check, verify that there are no remaining files in the
filegroup:
EXEC sp_helpfilegroup 'TEXT'
or
SELECT *
FROM sys.data_spaces AS ds
JOIN sys.database_files AS df ON
df.data_space_id = ds.data_space_id
WHERE ds.NAME = 'TEXT'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <Jeremy Calles@.discussions.microsoft.com> wrote in message
news:C24E794B-0241-495A-8E87-3F57F9BB4E67@.microsoft.com...
> Hi All,
> I have a problem with one of my filegroup. Every files have been removed,
> but when I try to remove the filegroup, I have the following error:
> Msg 5042, Level 16, State 8, Line 1
> The filegroup 'TEXT' cannot be removed because it is not empty.
> When I run the following query, to see which filegroup have no
> allocation_unit:
> SELECT * FROM sys.filegroups f
> WHERE NOT EXISTS (SELECT * FROM sys.allocation_units i WHERE
> i.data_space_id = f.data_space_id)
> The Filegroup named 'TEXT' is returned.
> Does anyone have an idea to know why I cannot remove this filegroup?
> Regards,
> Jeremy|||Hi Dan,
Thanks for your message, but I have already checked that no files are
assigned to this filegroup. And of course, there are no files.
So this is why it seems really strange to me.
I don't know what could it be.
Regards,
Jeremy|||Jeremy
DBCC SHRINKFILE command has an EMPTYFILE parameter.Specify a file that is
on filegroup and run it
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Uri,
As I have said in my first post: There is no files in this filegroups.
I have removed all files using DBCC SHRINKFILE
When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
This is why I have posted in this filegroup. Cause a filegroup without any
files attached cannot be removed.
Regards,
Jeremy|||Sorry, did not read it properly, how did you try to remove it ? by EM or
ALTER DATABASE... in QA?
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:D418E95E-EE3A-4049-BFDB-8432D31000F7@.microsoft.com...
> Hi Uri,
> As I have said in my first post: There is no files in this filegroups.
> I have removed all files using DBCC SHRINKFILE
> When I execute "EXEC sp_helpfilegroup 'TEXT'"I have no result.
> This is why I have posted in this filegroup. Cause a filegroup without any
> files attached cannot be removed.
> Regards,
> Jeremy
>
>|||You're welcome, Uri.
I'm doing ALTER DATABASE XXX REMOVE FILEGROUP [TEXT] which has worked fo
r
many filegroups before, and I have the following error:
Msg 5042, Level 16, State 8, Line 1
The filegroup 'TEXT' cannot be removed because it is not empty.
Regards,
Jeremy|||I haven't seen this problem before. Can you post the exact steps you
performed the remove the files?
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:A3D24B42-5CB4-4E27-AD85-15DCB1BDA701@.microsoft.com...
> Hi Dan,
> Thanks for your message, but I have already checked that no files are
> assigned to this filegroup. And of course, there are no files.
> So this is why it seems really strange to me.
> I don't know what could it be.
> Regards,
> Jeremy|||Hi Dan,
I have done the following:
- First extract the name of files attached to [TEXT] filegroup:
SELECT name FROM sys.database_files WHERE data_space_id =
FILEGROUP_ID('TEXT')
-For each file, I have done:
DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
-When all files have been remove, I do the following on filegroup:
ALTER DATABASE XXX REMOVE FILEGROUP [Text]
I have done the following many times, and it used to works on all
filegroups, but not on this one.
Regards,
Jeremy|||How did you move existing objects from filegroup? I just want to be sure
I'm not missing something.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Calles" <JeremyCalles@.discussions.microsoft.com> wrote in message
news:5E8B5C25-C8B6-4EEC-8A03-8CB2042439CD@.microsoft.com...
> Hi Dan,
> I have done the following:
> - First extract the name of files attached to [TEXT] filegroup:
> SELECT name FROM sys.database_files WHERE data_space_id =
> FILEGROUP_ID('TEXT')
> -For each file, I have done:
> DBCC SHRINKFILE(XXXXXXX_01,EMPTYFILE)
> ALTER DATABASE XXX REMOVE FILE XXXXXXX_01
> -When all files have been remove, I do the following on filegroup:
> ALTER DATABASE XXX REMOVE FILEGROUP [Text]
> I have done the following many times, and it used to works on all
> filegroups, but not on this one.
> Regards,
> Jeremy

Friday, March 23, 2012

filestats

Wednesday, March 21, 2012

files, filegroups

Files & FileGroups

Filegroups, CPU, & RAID

Filegroups, CPU, & RAID

filegroups table

Filegroups question

Monday, March 19, 2012

Filegroups and RAID 1+0

Filegroups and RAID 1+0

Filegroups and RAID 1+0

Filegroups and Joins

Filegroups and Joins

Filegroups and Joins

filegroups / reindex

filegroups / reindex

filegroups / reindex

Filegroup with SQL Server virtual server

Filegroup with SQL Server virtual server

Filegroup with SQL Server virtual server

Filegroup with no file, not possible to remove it!

Format Alignment

Blog Archive

About Me