How to force space allocation to a SS2000 DB?

I have to force a space allocation to a DB file in order to stop a Diagnostic Manager alert which states that the database is over 80% full. Now, I don't think this alert is rational, nor do I think it is useful in any way but that's the way it goes. My only option is to allocate more space to the DB so that the space used will fall below 80% so the alert will stop being issued.

So, can you tell me how to force a space allocation on an existing db file?

Thanks,

Michael

Hi Michael,

You have to define the initial size for your file as big as you need. You can do it with the MS SQL Server Manager Studio, Databases --> mydatabase --> right clic --> properties --> files

You can do it like this code sample

Code Snippet

USE [master]

GO

ALTER DATABASE [mydatabase] MODIFY FILE ( NAME = N'mydatabase_file_name', SIZE = 10240KB )

GO

Hope it helps.

Laurent

How to FORCE failed validation?

hi all-
is there any way to, as the subject so subtly states, force valdation to
fail? mind you, i don't want a false positive (failed validation message
with no data discrepancies) - i want to somehow prevent records from getting
replicated around, if possible. i'm trying to test ways of solving failed
validation issues, and need as many test cases as possible.
i'm dealing with anonymous merge subscriptions if that makes a difference...
thanks in advance,
-ben
Ben,
you could temporarily modify the merge trigger so your data change is not
logged or do a bulk insert without the FIRE_TRIGGERS option.
HTH,
Paul Ibison

How to find 1st Maximum & 2nd maximum No. in a table?

Hi

I have table like this

State Version

CA 19

CA 8

CA 15

CA 6

MA 12

MA 7

MA 10

MA 5

Like this I have 50 states with different Version No.s (Version No.s are unique)

I want to display output like this(in SQL Server 2000)

State 1^st Maximum 2^nd Maximum

CA 19 15

MA 12 10

Please advice

Thanks

Here you go..

Code Snippet

Create Table #data (

[State] varchar(3) ,

[Version] int

);

Insert Into #data Values('CA','19');

Insert Into #data Values('CA','8');

Insert Into #data Values('CA','15');

Insert Into #data Values('CA','6');

Insert Into #data Values('MA','12');

Insert Into #data Values('MA','7');

Insert Into #data Values('MA','10');

Insert Into #data Values('MA','5');

Code Snippet

--Only on SQL Server 2005

;With CTE

as

(

Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data

)

Select

[State]

,Max(Case When RowId=1 Then [Version] End) [1st Max]

,Max(Case When RowId=2 Then [Version] End) [2nd Max]

From

CTE

Group By

[State]

Code Snippet

--or

--Only on SQL Server 2005

;With CTE

as

(

Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data

)

Select

[State]

,[1] as [1st Max]

,[2] as [2nd Max]

From

CTE

Pivot

(

Max([Version])

For RowId in ([1],[2])

) as PVT

Code Snippet

--Any SQL Server version

Select

[State]

,Max(Case When RowId=1 Then [Version] End) [1st Max]

,Max(Case When RowId=2 Then [Version] End) [2nd Max]

From

(

Select

[State]

,[Version]

,(Select Count(*) From #data Sub Where Sub.[Version] >= Main.[Version]

And Sub.[State] = Main.[State]) RowId

From

#data Main

) as data

Group By

[State]

|||

wow!!!!!!!

Thanks a lot, Sekar

I got my answer from your nice queries.

Once again Thanks

With Regards

VJ

|||

Try:

Code Snippet

createtable dbo.t1 (

[State] char(2)notnull,

Version intnotnull

)

go

insertinto dbo.t1 values('CA', 19)

insertinto dbo.t1 values('CA', 8)

insertinto dbo.t1 values('CA', 15)

insertinto dbo.t1 values('CA', 6)

insertinto dbo.t1 values('MA', 12)

insertinto dbo.t1 values('MA', 7)

insertinto dbo.t1 values('MA', 10)

insertinto dbo.t1 values('MA', 5)

go

select

[State],

max(version)as [1st Maximum],

min(version)as [2nd Maximum]

from

(

select

[state], version

from

dbo.t1 as a

where

version in(

selecttop 2 b.version

from dbo.t1 as b

where b.[state] = a.[state]

orderby b.version desc

)

)as t

groupby

[state]

orderby

[state]

go

-- 2005 - 1

;with cte

as

(

select

[state], version,

row_number()over(partitionby [state] orderby version desc)as rn

from

dbo.t1

)

select

[state],

max(version)as [1st Maximum],

min(version)as [2nd Maximum]

from

cte

where

rn <= 2

groupby

[state]

orderby

[state]

go

-- 2005 - 2

select

a.[state],

max(c.version)as [1st Maximum],

min(c.version)as [2nd Maximum]

from

(

selectdistinct

[state]

from

dbo.t1

)as a

crossapply

(

selecttop 2

b.version

from

dbo.t1 as b

where

b.[state] = a.[state]

orderby

b.version desc

)as c

groupby

a.[state]

orderby

a.[state]

go

droptable dbo.t1

go

AMB