I have a storage subsystem which allows five independant RAID 10 devices. I am creating five filegroups each with 2 data files distributed across the devices in a smart round robin fashion.
I have assigned tables which are commonly joined between the filegroups. For example, Table A is in Filegroup 1 while Table B is in Filegroup 2. The most common and high volume query is the join between these two tables.
My question is this: I understand SQL Server is theaded by data file, not filegroup. But for filegroups which have 2 or more data files, do they both grow evenly? Or once one data file is full, SQL Server then writes all new data to the new data file in the same filegroup.
Hopefully this is not too confusing.
Thanks.
BryanSQL Server will write data to the members of a filegroup evenly, so if you create 2 100MB files in a filegroup, then insert 150MB of data into this filegroup, you will have 75 MB in each. Figuring out what you have after you delete 80 MB of data is virtually impossible, however. Hope this helps.
Showing posts with label across. Show all posts
Showing posts with label across. Show all posts
Friday, March 9, 2012
Multiple Data & Log Drives
I have a 20g database, the data and log files are spread
across multiple drives. What is the best way to get the
data back to 1 drive and the log back to 1 drive ?Use
Dbcc shrinkfile(Friendlyfilename, null, emptyfile) on a file
then
Alter database prod drop file friendlyfilename
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
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:05a401c3ce0d$9aafb910$a601280a@.phx.gbl...
> I have a 20g database, the data and log files are spread
> across multiple drives. What is the best way to get the
> data back to 1 drive and the log back to 1 drive ?|||Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
For the log files, shrink the second log file with DBCC SHRINKFILE and use
the EMPTYFILE option. Next, alter the database to drop the now-empty file.
As for the data files, you did not say if they are in the same filegroup.
If so, proceed as above. If not, you will have to drop the indexes in the
second file and re-create them in the primary file. Then, proceed as above.
HTH
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
For the log files, shrink the second =log file with DBCC SHRINKFILE and use the EMPTYFILE option. Next, alter the =database to drop the now-empty file.
As for the data files, you did not say =if they are in the same filegroup. If so, proceed as above. If not, you =will have to drop the indexes in the second file and re-create them in the =primary file. Then, proceed as above.
HTH
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:060001c3ce13$e0=c5dd70$a601280a@.phx.gbl...Let me be more specific:My database consists of 2 data files and 2 =log files:e:\data1.mdff:\data2.mdfg:\log1.ldff:\log2.=ldfHow can I get the data back to 1 file and 1 drive ? How can I get =the log bacl to 1 file and 1 drice.>--Original =Message-->I have a 20g database, the data and log files are spread >across =multiple drives. What is the best way to get the >data back to 1 drive and =the log back to 1 drive ?>.>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0--|||Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
OK, moving files is different. What you can do is detach and reattach the
database:
1. Detach the database, using sp_detach_db
2. Move the files to a different drive
3. Reattach the database, using sp_attach_db
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c3ce17$192e4be0$a601280a@.phx.gbl...
Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK, moving files is different. =What you can do is detach and reattach the database:
1. Detach the =database, using sp_detach_db
2. Move the files to =a different drive
3. Reattach the =database, using sp_attach_db
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:062b01c3ce17$19=2e4be0$a601280a@.phx.gbl...Yes they are in the primary file group. My problem is that I need to =move the primary data and log files. Those are the drives that are running =out of space. >--Original Message-->For the log =files, shrink the second log file with DBCC SHRINKFILE and use>the =EMPTYFILE option. Next, alter the database to drop the now-empty file.>>As for the data files, you did not say if they are =in the same filegroup.>If so, proceed as above. If not, you =will have to drop the indexes in the>second file and re-create them in =the primary file. Then, proceed as above.>>HTH>>-->Tom>>--=------>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Kell=y" wrote in message>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...>=Let me be more specific:>>My database consists of 2 data files =and 2 log files:>>e:\data1.mdf>f:\data2.mdf>>g:\=log1.ldf>f:\log2.ldf>>How can I get the data back to 1 file and 1 drive ?>>How can I =get the log bacl to 1 file and 1 drice.>>--Original Message-->I have a 20g database, the data and log files =are spread>across multiple drives. What is the best way to get the>data back to 1 drive and the log back to 1 drive ?>.>>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0--
across multiple drives. What is the best way to get the
data back to 1 drive and the log back to 1 drive ?Use
Dbcc shrinkfile(Friendlyfilename, null, emptyfile) on a file
then
Alter database prod drop file friendlyfilename
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
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:05a401c3ce0d$9aafb910$a601280a@.phx.gbl...
> I have a 20g database, the data and log files are spread
> across multiple drives. What is the best way to get the
> data back to 1 drive and the log back to 1 drive ?|||Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
For the log files, shrink the second log file with DBCC SHRINKFILE and use
the EMPTYFILE option. Next, alter the database to drop the now-empty file.
As for the data files, you did not say if they are in the same filegroup.
If so, proceed as above. If not, you will have to drop the indexes in the
second file and re-create them in the primary file. Then, proceed as above.
HTH
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
For the log files, shrink the second =log file with DBCC SHRINKFILE and use the EMPTYFILE option. Next, alter the =database to drop the now-empty file.
As for the data files, you did not say =if they are in the same filegroup. If so, proceed as above. If not, you =will have to drop the indexes in the second file and re-create them in the =primary file. Then, proceed as above.
HTH
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:060001c3ce13$e0=c5dd70$a601280a@.phx.gbl...Let me be more specific:My database consists of 2 data files and 2 =log files:e:\data1.mdff:\data2.mdfg:\log1.ldff:\log2.=ldfHow can I get the data back to 1 file and 1 drive ? How can I get =the log bacl to 1 file and 1 drice.>--Original =Message-->I have a 20g database, the data and log files are spread >across =multiple drives. What is the best way to get the >data back to 1 drive and =the log back to 1 drive ?>.>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0--|||Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
OK, moving files is different. What you can do is detach and reattach the
database:
1. Detach the database, using sp_detach_db
2. Move the files to a different drive
3. Reattach the database, using sp_attach_db
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c3ce17$192e4be0$a601280a@.phx.gbl...
Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK, moving files is different. =What you can do is detach and reattach the database:
1. Detach the =database, using sp_detach_db
2. Move the files to =a different drive
3. Reattach the =database, using sp_attach_db
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:062b01c3ce17$19=2e4be0$a601280a@.phx.gbl...Yes they are in the primary file group. My problem is that I need to =move the primary data and log files. Those are the drives that are running =out of space. >--Original Message-->For the log =files, shrink the second log file with DBCC SHRINKFILE and use>the =EMPTYFILE option. Next, alter the database to drop the now-empty file.>>As for the data files, you did not say if they are =in the same filegroup.>If so, proceed as above. If not, you =will have to drop the indexes in the>second file and re-create them in =the primary file. Then, proceed as above.>>HTH>>-->Tom>>--=------>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Kell=y" wrote in message>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...>=Let me be more specific:>>My database consists of 2 data files =and 2 log files:>>e:\data1.mdf>f:\data2.mdf>>g:\=log1.ldf>f:\log2.ldf>>How can I get the data back to 1 file and 1 drive ?>>How can I =get the log bacl to 1 file and 1 drice.>>--Original Message-->I have a 20g database, the data and log files =are spread>across multiple drives. What is the best way to get the>data back to 1 drive and the log back to 1 drive ?>.>>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0--
Wednesday, March 7, 2012
MULTIPLE COLUMNS using and GROUP BY
I hope there's someone that is able to help me. I'm new to SQL Server 2005 and have come across a pretty tough problem.
I need to return City Names from the City Table ordered by province and postal code. However, SELECT statement returns multiple occurrences of a City_Name from the Cities table. I only want the City Name to appear once though.
Here's the SQL statement:
"SELECT City_Name, Province_ID, City_PostCode
FROM Cities
WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode
AND City_Name != '" + exclude
ORDER BY Province_ID ASC, City_PostCode ASC ";
I've tried to use distinct on the City Name but that didn't work. It seems the distinct keyword has to use all the column names to return a distinct record. An alternative would be to GROUP the records by City_Name however you have to use all the columns that were selected leaving me with the same distinct keyword problem.
Can anyone help me?
hi,
Well, if you have different zip codes to the same City, you should have duplications and no matter what you use; distinct or group by.
eg:
City_name Province_ID City_PostCode
Los Angeles CA 90150
Los Angeles CA 90151
In this case, you should not have one record, based on your select statement. Maybe you should appear a zip code range, instead of individual zips, then you should have distinct result set.
Also, if just change the second zip to the first one, and using distinct you will have one record. you do not need to specify column level the distinct, it applied on all enumerated column in the select statement.
I hope it helps.
Kind Regards,
Janos
|||If you want to show the multiple postal codes as single row then you can use the following query.. One row per city with out data loose..
Code Snippet
Create Table #cities (
[City_name] Varchar(100) ,
[Province_ID] Varchar(100) ,
[City_PostCode] Varchar(100)
);
Insert Into #cities Values('Los Angeles','CA','90150');
Insert Into #cities Values('Los Angeles','CA','90151');
Insert Into #cities Values('Austin','TX','73301');
Select
Distinct
Main.City_name
, Main.Province_ID
, Substring((Select ',' + City_PostCode as [text()]
From #cities Sub
Where Sub.City_name = Main.City_name
And Sub.Province_ID = Main.Province_ID For XML Path('')),2,8000) as [City_PostCodes]
From
#cities as Main
|||Thanks Jano, I updated the statement to reflect the following:
"SELECT DISTINCT City_Name, Province_ID FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' )
ORDER BY Province_ID ASC ";
The only thing is I can't sort the selections according to there postal codes. however the intial problem of the distinct fields is resolved.
Thanks again.
Regards,
Simon
|||
You can sort by postal_code (using OVER clause)..
"SELECT DISTINCT City_Name, Province_ID,Max([City_PostCode]) Over (Partition By City_name,Province_ID) as [City_PostCode] FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' ) ORDER BY [City_PostCode] ASC ";
Subscribe to:
Posts (Atom)