Max Filegroup Size set to 20 how do I change it?

Hello,

I have been trying to figure out how to temporarily change the max
filegroup size of a SQL 2000 database I "inherited" when our DBA quit.

I just need some breathing room until I can figure out how to properly
archive and remove some filegroups.

Here is the scenario, my filegroup is set to 20. Each filegroup is set
to grow to 4096.
I currently have all 20 filegroups in use and filegroup number 20 is
more than 1/2 full.

I have considered making the max size for each filegroup larger but
believe this is treating the symptom not fixing the problem.

I would RTFM if I knew how to ask the question in 5 words or less.
I have plenty of room to grow in terms of disk space.
In Query Analyzer I suspect I have to use an ALTER DATABASE
In Gui mode I suspect I have to right click on the database properties,
filegoups,,,,

In the past I have setup my databases with 1 filegroup and auto
configured to grow as needed so this is new territory for me so please
feel free to dumb it down for a noob as I need baby steps.Filegroups contain files. Files have settable parameters like max size not
filegroups. Unless something is configured strangely 20 files in a
filegroup usually indicates an attempt to spread out IO operations across
lots of disks (probably SAN based). MS also recommends having at least one
file per processor on multiprocessor servers for performance reasons. Yes.
You are correct you have two choices.

Use the Alter database command or EM and access the properties of the
database.

Ps. Try to keep the file sizes within a Filegroup the same size so that the
proportional fill algorithm can distribute the extents evenly.

<mchang@.allosource.org> wrote in message
news:1118379203.356973.4140@.o13g2000cwo.googlegrou ps.com...
> Hello,
> I have been trying to figure out how to temporarily change the max
> filegroup size of a SQL 2000 database I "inherited" when our DBA quit.
> I just need some breathing room until I can figure out how to properly
> archive and remove some filegroups.
> Here is the scenario, my filegroup is set to 20. Each filegroup is set
> to grow to 4096.
> I currently have all 20 filegroups in use and filegroup number 20 is
> more than 1/2 full.
> I have considered making the max size for each filegroup larger but
> believe this is treating the symptom not fixing the problem.
> I would RTFM if I knew how to ask the question in 5 words or less.
> I have plenty of room to grow in terms of disk space.
> In Query Analyzer I suspect I have to use an ALTER DATABASE
> In Gui mode I suspect I have to right click on the database properties,
> filegoups,,,,
> In the past I have setup my databases with 1 filegroup and auto
> configured to grow as needed so this is new territory for me so please
> feel free to dumb it down for a noob as I need baby steps.|||Filegroups contain files. Files have settable parameters like max size not
filegroups. Unless something is configured strangely 20 files in a
filegroup usually indicates an attempt to spread out IO operations across
lots of disks (probably SAN based). MS also recommends having at least one
file per processor on multiprocessor servers for performance reasons. Yes.
You are correct you have two choices.

Use the Alter database command or EM and access the properties of the
database.

Ps. Try to keep the file sizes within a Filegroup the same size so that the
proportional fill algorithm can distribute the extents evenly.

<mchang@.allosource.org> wrote in message
news:1118379203.356973.4140@.o13g2000cwo.googlegrou ps.com...
> Hello,
> I have been trying to figure out how to temporarily change the max
> filegroup size of a SQL 2000 database I "inherited" when our DBA quit.
> I just need some breathing room until I can figure out how to properly
> archive and remove some filegroups.
> Here is the scenario, my filegroup is set to 20. Each filegroup is set
> to grow to 4096.
> I currently have all 20 filegroups in use and filegroup number 20 is
> more than 1/2 full.
> I have considered making the max size for each filegroup larger but
> believe this is treating the symptom not fixing the problem.
> I would RTFM if I knew how to ask the question in 5 words or less.
> I have plenty of room to grow in terms of disk space.
> In Query Analyzer I suspect I have to use an ALTER DATABASE
> In Gui mode I suspect I have to right click on the database properties,
> filegoups,,,,
> In the past I have setup my databases with 1 filegroup and auto
> configured to grow as needed so this is new territory for me so please
> feel free to dumb it down for a noob as I need baby steps.

MAX dont know what type is returning

Hi guys,

I'm doing some function in the SQLExpress using the max func and I cant figure out if its returning null or empty string. It always return null even it's returning index.

Please help.

Here's is the sample data of the table that I'm manipuilating

code name

code name

code_1 name_1

code_2 name_2

tues_2 name_4

code_4 name_3

>>> What am trying to get is the maximum index of the code

Below is my function

declare @.codeIndex varchar(20)

select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2), len'code')) ,0)),0) from table where [code] like 'code%'

print @.codeIndex

if @.codeIndex is null

begin

print 'Code index is null

end

else

begin

print 'we got an index!'

end

>>> the output of this is always null even it's returning an index

Can you post a full repro? You code didn't compile (not correct number of parameters etc). Either against the pubs database or also post CREATE TABLE and some INSERT statements. I changed the code so it at least compiled (but I doubt it has correct logic), and there doesn't seem to anything wrong with the MAX function per se.: declare @.codeIndex varchar(20) select @.codeIndex = isnull(max(isnull(substring([au_lname],len('code')+2, len('code')) ,0)),0) from authors print @.codeIndex if @.codeIndex is null begin print 'Code index is null' end else begin print 'we got an index!' end -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:b4844e9a-27a2-49d0-8a11-482daa3423db@.discussions.microsoft.com...
> Hi guys, >
> I'm doing some function in the SQLExpress using the max func and I
> cant figure out if its returning null or empty string. It always return
> null even it's returning index. >
> Please help. >
> Here's is the sample data of the table that I'm manipuilating >
> code name >
> code name >
> code_1 name_1 >
> code_2 name_2 >
> tues_2 name_4 >
> code_4 name_3 > > > >>>> What am trying to get is the maximum index of the code >
> Below is my function >
> declare @.codeIndex varchar(20) >
> select @.codeIndex = isnull(max(isnull(substring([code],len('code')+2),
> len'code')) ,0)),0) from table where [code] like 'code%' > > >
> print @.codeIndex >
> if @.codeIndex is null >
> begin >
> print 'Code index is null >
> end >
> else >
> begin >
> print 'we got an index!' >
> end > >>>> the output of this is always null even it's returning an index >
>|||

Hi NNTP,

Thanks! I already figured it out; maybe, I'm just exhausted last friday.

Max Date for multiple columns

Hi all,
Okay... this should be a pretty question, but, can't seem to figure out
how to do it. In this database I'm working with, they have created 8 column
s
(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
I need to get the max date (Date1, Date2, Date3 or Date4) and the
information from the appropriate column (So, if Date2 is the max date, then
return the result set containing Version2 and Date2 data)
Any idea about how to approach this problem?
Any help is greatly apprectiated.
DougSure...
CREATE TABLE [dbo].[TEST] (
[ACCOUNT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEM1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE1] [datetime] NULL ,
[SYSTEM2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE2] [datetime] NULL ,
[SYSTEM3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE3] [datetime] NULL ,
[SYSTEM4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE4] [datetime] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Doug" wrote:

> Hi all,
> Okay... this should be a pretty question, but, can't seem to figure ou
t
> how to do it. In this database I'm working with, they have created 8 colu
mns
> (Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
> information from the appropriate column (So, if Date2 is the max date, the
n
> return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
> Any help is greatly apprectiated.
> Doug|||I see several solutions
A. Self joins
B. Big if statement
or
C. Temp table
Create a temp table that has the record number, Version, and Date. Each
row represents a single Version / Date pair. Each row in your original
table would then become 4 rows in the temp table.
Then you could do
select recordid, max(datecol)
from tempTable
group by recordid|||>> Any idea about how to approach this problem?
The very fact that a simple query as this requires a complex solution itself
suggest that the table design could be improved. Rather than using column
names to represent data, consider something along the lines of:
CREATE TABLE tbl (
key_col ...
version ..
date_col DATETIME ) ;
This will allow you to add more versions without having to alter the schema.
Moreover the design is more flexible & allows for better constraint
enforcement as well.
If you are somehow forced to stick with the existing schema consider using a
view/ derived table to logically abstract the data like:
SELECT key_col,
CASE n WHEN 1 THEN version1
WHEN 2 THEN version2
WHEN 3 THEN version3
WHEN 4 THEN version4
END AS "version",
CASE n WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS "version_date"
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) T ( n );
Now, it is just a matter of using aggregate function MAX() on the
version_date column to get the required value.
Anith|||On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:

>Hi all,
> Okay... this should be a pretty question, but, can't seem to figure out
>how to do it. In this database I'm working with, they have created 8 colum
ns
>(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
>information from the appropriate column (So, if Date2 is the max date, then
>return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
Hi Doug,
Normalise your design. You should have a seperate table with Date and
Version as columns, plus a foreign key to the table where these 8
columns now are. Then, it's quite easy.
Assuming the normalised table looks like this
CREATE TABLE YourTable
(CustomerID int NOT NULL,
TheDate datetime NOT NULL,
Version varchar(20) NOT NULL,
PRIMARY KEY (CustomerID, TheDate),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
The query is like this
SELECT a.CustomerID, a.TheDate, a.Version
FROM YourTable AS a
INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
FROM YourTable
GROUP BY CustomerID) AS b
ON b.Customer = a.Customer
AND b.MaxDate = a.TheDate
Hugo Kornelis, SQL Server MVP|||Hi all,
Thanks for all of the help! Unfortunately, the design can't be
normalised. We are using the Goldmine application (commercial product) and
they designed the tables to work this way. This design has caused me a
number of headaches.
Overall, I went with a stored procedure to get the data into a
temporary table that was more normalized and then got my information using
standard techniques.
Dougie
"Hugo Kornelis" wrote:

> On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>
> Hi Doug,
> Normalise your design. You should have a seperate table with Date and
> Version as columns, plus a foreign key to the table where these 8
> columns now are. Then, it's quite easy.
> Assuming the normalised table looks like this
> CREATE TABLE YourTable
> (CustomerID int NOT NULL,
> TheDate datetime NOT NULL,
> Version varchar(20) NOT NULL,
> PRIMARY KEY (CustomerID, TheDate),
> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
> )
> The query is like this
> SELECT a.CustomerID, a.TheDate, a.Version
> FROM YourTable AS a
> INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
> FROM YourTable
> GROUP BY CustomerID) AS b
> ON b.Customer = a.Customer
> AND b.MaxDate = a.TheDate
> --
> Hugo Kornelis, SQL Server MVP
>

Matrix with Percentages

Is there any way to create a matrix table like this? I can't figure out how
to do percentages.
Rank Number %
Professor 48 49
Associate Professor 25 26
Assistant Professor 24 25
Total 97 100
Thanks!
Danhi Dan
I have a table in my report and have the following as an expression.
=FormatPercent(Fields!SLAResponseCompliant.Value/Fields!ClosedTickets.Value)
not sure how it will go with a matrix table but if you use something
similar? or it may at least give you something to work with.
Jewel
"Dan" wrote:
> Is there any way to create a matrix table like this? I can't figure out how
> to do percentages.
> Rank Number %
> Professor 48 49
> Associate Professor 25 26
> Assistant Professor 24 25
> Total 97 100
> Thanks!
> Dan
>sql

Matrix Rendering Problem?

Hello,
I have a matrix rendering problem that drives me crazy. Can't figure out
what it is for a couple of days. The problem is illlustrated below - note
that starting from "Min Value" all matrix rows are shifted down. "Max Time"
row shows minimum values, "Max Value" row shows time of maximum values, and
maximum values are displayed in the extra row that does not exist at all. The
strange thing is that this only happens when report is rendered as HTML via
url access. It works just fine in the Report Designer (both Preview and Debug
Local), and it renders correctly if exported to a different format such as
PDF or IMAGE. If anyone knows how to fix this, please advise. Thanks in
advance.
M T
W
Depth Vel Quant Depth Vel Quant Depth
Vel Quant
Total 0.255 0.239
0.232
Avg 2.85 1.88 0.255 2.79 1.83 0.239 2.74
1.82 0.232
Min Time 5:30 4:30 4:30 3:00 5:15 5:15 5:15 2:45
2:45
Min Value
Max Time 1.66 0.50 0.033 1.66 0.51 0.032 1.63 0.49
0.033
Max Value 17:00 20:15 20:15 9:45 9:45 9:45 8:30 7:30
9:15
3.77 2.53 0.449 3.77 2.23 0.410 3.65
2.32 0.384
--
Regards,
Alex Korygin.Sorry for the formatting. Hopefully this one will show better.
M T W
Depth Vel Quant Depth Vel Quant Depth Vel Quant
Total 0.255 0.239 0.232
Avg 2.85 1.88 0.255 2.79 1.83 0.239 2.74 1.82 0.232
Min Time 5:30 4:30 4:30 3:00 5:15 5:15 5:15 2:45 2:45
Min Value
Max Time 1.66 0.50 0.033 1.66 0.51 0.032 1.63 0.49 0.033
Max Value 17:00 20:15 20:15 9:45 9:45 9:45 8:30 7:30 9:15
3.77 2.53 0.449 3.77 2.23 0.410 3.65 2.32 0.384
"Alex Korygin" wrote:
> Hello,
> I have a matrix rendering problem that drives me crazy. Can't figure out
> what it is for a couple of days. The problem is illlustrated below - note
> that starting from "Min Value" all matrix rows are shifted down. "Max Time"
> row shows minimum values, "Max Value" row shows time of maximum values, and
> maximum values are displayed in the extra row that does not exist at all. The
> strange thing is that this only happens when report is rendered as HTML via
> url access. It works just fine in the Report Designer (both Preview and Debug
> Local), and it renders correctly if exported to a different format such as
> PDF or IMAGE. If anyone knows how to fix this, please advise. Thanks in
> advance.
> M T
> W
> Depth Vel Quant Depth Vel Quant Depth
> Vel Quant
> Total 0.255 0.239
> 0.232
> Avg 2.85 1.88 0.255 2.79 1.83 0.239 2.74
> 1.82 0.232
> Min Time 5:30 4:30 4:30 3:00 5:15 5:15 5:15 2:45
> 2:45
> Min Value
> Max Time 1.66 0.50 0.033 1.66 0.51 0.032 1.63 0.49
> 0.033
> Max Value 17:00 20:15 20:15 9:45 9:45 9:45 8:30 7:30
> 9:15
> 3.77 2.53 0.449 3.77 2.23 0.410 3.65
> 2.32 0.384
>
> --
> Regards,
> Alex Korygin.

Matrix grouping/visibility

I have tried several combinations, but I can't quite figure out the solution to this one. I have a matrix set up that is giving me the results I want, but I need to do a conditional visibility and still have what I need.

I have a column that is filled with operation numbers and a row that is filled with item numbers. The detail is for work centers and it shows where each of them fall in the grid. I need to say to show all work centers that apply where the workcenter parameter matches. I was able to filter and say only show me this workcenter, but I want to show just those, but then if this workcenter would show up, show me the rest.

BJ

bj

By right click on the detail column and selecting properties. You have access to the visiblility tab, on the tab, you have expression. You can use the expression to control what is display and what is hide.

Hammer.