multiprocessor environment

Folks,
We're running Microsoft SQL-2000 server
in a multiprocessor environment. (quad processor box)
Can we force different jobs that are
scheduled concurrently to use a particular
processor.
What we would like to do is run 4 separate
jobs, scheduled for the same time, and force
each job to use a different processor on a
quad processor database server host.
Is it possible to do this at all with SQL-2000.

Thanks in advanceI think you can only configure which processors will be used by SQL Server. The server itself will assign every thread that runs to a processor based on the priority.sql

Multiplication Aggregate

Howdy folks. I have a question about how to go about creating a function that will return the multiplication aggregate over a row. Essentially I need this to function in the exact same was as the sum() function, but with multiplication instead. I've been struggling trying to create a UDF to do this for me, but I'm not having much luck. I would be amazed if this is not something somebody else has already done out of necessity. Any ideas?Maybe you can show what formula you want to apply?|||Ok, how about:

USE Northwind
GO

CREATE FUNCTION udf_MULT (@.x float)
Returns float
AS
BEGIN
DECLARE @.y float
SELECT @.y = @.x * 2
RETURN @.y
END

GO

SELECT Freight, dbo.udf_MULT(Freight) FROM Orders
GO|||Brett, i don't think your udf_MULT is what Teddy wants

sample rows:

name value
foo 2
bar 4
qux 6
fap 8

select sum(value) from ... gives 20

select mult(value) from ..., assuming there were such an aggregate function as "mult()", would return 384

maybe a variation of that routine written up in that sqlteam article using coalesce to produce a comma-delimited list?

rudy|||You mean like:

USE Northwind

DECLARE @.x money
SELECT @.x = ISNULL(@.x,1) + Freight FROM Orders
SELECT @.x

I added them because of overflow...but you could use * instead...|||mmm, i love overflow

perhaps that's why "mult()" was never invented -- too easy to blow up the query real good

:)|||Add for the +, the ISNULL value should be 0, not 1...1 For Multiplication

hmmmmmmmmm...my cup runneth over with a flaming homer...or was that a flaming moe...

Hey...seeing it in type...

Noooow I get it...

Great episode...

Got to pick me up some cough medice on the way home...|||Thanks for the reply guys!!

I've been a bit busy with the holiday so I haven't had much time to make my rounds here. So far I've learned that this essentially is not possible with anything other then ints without a udf. Currently I'm searching for a udf that will do this for me, as I am not terribly familiar with coding them myself. Perhaps it's time to up the learning curve eh?|||a MULT function for varchar?

Hmmmm, now you've got me TOTALLY lost...

What exactly are you trying to do?|||Originally posted by Brett Kaiser
a MULT function for varchar?

Hmmmm, now you've got me TOTALLY lost...

What exactly are you trying to do?

Not for varchar, float. The closest I've found so far is sort of a bizarre workaround that doesn't seem to work properly over large recordsets:

EXP(SUM(LOG(value)))|||Originally posted by Brett Kaiser
What exactly are you trying to do?

Do you mean like what r937 said?

You can't (to my knowledge) create a user defined scalar function...

But you can employ the other method I posted

DECLARE @.x bigint
SELECT @.x = @.x + floatColumn FROM Table
SELECT @.x

Is that what you're trying to do? Multiply all the numbers in 1 column?|||Originally posted by Brett Kaiser
Do you mean like what r937 said?

You can't (to my knowledge) create a user defined scalar function...

But you can employ the other method I posted

DECLARE @.x bigint
SELECT @.x = @.x + floatColumn FROM Table
SELECT @.x

Is that what you're trying to do? Multiply all the numbers in 1 column?

That appears to be returning null. Does the select @.x = iterate through the entire recordset? I'm a bit new with variable usage in the queries themselves. Generally I would do this sort of thing at the application level with my front-end.|||Give this a read...

http://sqlteam.com/item.asp?ItemID=2368|||That appears to be returning nullhence the need for the COALESCE

and yes, you did mention this earlier, brett, but it's worth repeating -- 0 for addition, 1 for multiplication

:D|||Originally posted by r937
hence the need for the COALESCE

and yes, you did mention this earlier, brett, but it's worth repeating -- 0 for addition, 1 for multiplication

:D

Ok, I'm think I'm getting on the right track, but I am still not quite getting what I need. Currently I am using:

DECLARE @.x FLOAT

SELECT @.x = COALESCE(@.x, 1) * myField
FROM myTable

SELECT @.x

That is now returning 0.0 Oddly enough, if I use + instead of *, I return the same as SUM() + 1. So it does appear to function exactly as expected when applied to addition (the +1 is the result of using 0 as opposed to 1 for the null return), I'm still a bit stumped as to why it returns zero for multiplication. What am I overlooking?|||overlooking? probably precision and/or scale (i can never remember which is which)

try

SELECT @.x = COALESCE(@.x, 1.000000000) * myField|||Originally posted by r937
overlooking? probably precision and/or scale (i can never remember which is which)

try

SELECT @.x = COALESCE(@.x, 1.000000000) * myField

The issue was an overflow error. I ran the same syntax over a much smaller dataset and produced the desired result.

Thanks a bunch guys!!

This is at least enough to get me over the "how the hell..." hump that everyone hits once in a while.

Much obliged.|||And just for another twist...what if 1 of your rows has a 0

Come on...

puuuuleeeeeeeeeeeeze

Tell us this is more than an academic exercise...because I can't think of a practical application at all...|||Originally posted by Brett Kaiser
And just for another twist...what if 1 of your rows has a 0

Come on...

puuuuleeeeeeeeeeeeze

Tell us this is more than an academic exercise...because I can't think of a practical application at all...

The practical application is to a series of modifiers to an insurance quote. If one of them is zero, then the quote is free. I doubt that will happen.

:)|||So you have a very specific predicate...the quote...how many modifiers per quote on average?

...and if it can happen it will happen...just a matter of time...

CREATE TABLE myTable99 (Col1 int NOT NULL CONSTRAINT myTable99_chk1 CHECK (Col1 <> 0))|||Originally posted by Brett Kaiser
So you have a very specific predicate...the quote...how many modifiers per quote on average?

...and if it can happen it will happen...just a matter of time...

CREATE TABLE myTable99 (Col1 int NOT NULL CONSTRAINT myTable99_chk1 CHECK (Col1 <> 0))


approx 7-8 modifiers per quote. The mods tend to be in the .75 - 1.5 range. And it will never be 0. Not unless the chairman of the board owes someone a serious favor. In which case, that's really not my problem to begin with.

The issue is the varying number of modfiers for each quote. Some will only have one, some will have 8. That's why this mult function comes in handy. This is fairly easy to do client-side, but for some reason is murderous to do in sql until this morning.|||So you would check to see if the result of this multiplier function is zero, and then act accordingly?

There are other methods that might work. For instance:

select min(Abs(YourColumn))

...would return zero if any of the values is zero.

blindman|||...and you can include this directly in a calculation:

Cost * cast(cast(min(Abs(YourColumn)) as bit) as int)

...would result in a cost of $0 if any of the multipliers is zero.

blindman|||Originally posted by blindman
...and you can include this directly in a calculation:

Cost * cast(cast(min(Abs(YourColumn)) as bit) as int)

...would result in a cost of $0 if any of the multipliers is zero.

blindman

That's really not an issue. This is well and solved.

Anywho, the current method would return 0 as-is.|||Originally posted by Teddy
Not for varchar, float. The closest I've found so far is sort of a bizarre workaround that doesn't seem to work properly over large recordsets:

EXP(SUM(LOG(value)))

I do like the workaround solution. Could you tell me what do you mean by "doesn't seem to work properly over large recordsets"? Is it the overflow problem? I tested it on a table of 4000 records, the results is as expected.|||Originally posted by shianmiin
I do like the workaround solution. Could you tell me what do you mean by "doesn't seem to work properly over large recordsets"? Is it the overflow problem?

edit: read that wrong.

I'm not sure what the issue is. It returns nothing at all when I use a large set. Very odd.|||Originally posted by Teddy
edit: read that wrong.

I'm not sure what the issue is. It returns nothing at all when I use a large set. Very odd.

for actual cases, i.e.

approx 7-8 modifiers per quote. The mods tend to be in the .75 - 1.5 range. And it will never be 0. Not unless the chairman of the board owes someone a serious favor.

I think this would be a best solution to me. :)

select
case
when min(isnull(f1, 0))=0 then 0
else exp(sum(log(isnull(case when f1=0 then 1 else f1 end,1))))
end
from t1

The query posted handles zero and null value correctly.
And I don't think overflow would be a problem.
(There shouldn't be negative values in the table, if it is, you need to add some code to handle it properly)|||The issue is:

USE Northwind
GO

DECLARE @.x BIGINT
SELECT @.x = 1
--Wont Blow Up
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 1
SELECT @.@.ERROR AS ErrorCode
--Will Blow Up
SELECT @.x
SELECT @.x = 1
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 2
SELECT @.@.ERROR AS ErrorCode
SELECT @.x

--The error should read:
-- Server: Msg 8115, Level 16, State 2, Line 11
-- Arithmetic overflow error converting expression to data type bigint.

In any event, error checking is a must...for all code...|||Originally posted by Brett Kaiser
The issue is:

USE Northwind
GO

DECLARE @.x BIGINT
SELECT @.x = 1
--Wont Blow Up
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 1
SELECT @.@.ERROR AS ErrorCode
--Will Blow Up
SELECT @.x
SELECT @.x = 1
SELECT @.x = @.x * ISNULL(EmployeeId,1)
FROM Orders
WHERE EmployeeId = 2
SELECT @.@.ERROR AS ErrorCode
SELECT @.x

--The error should read:
-- Server: Msg 8115, Level 16, State 2, Line 11
-- Arithmetic overflow error converting expression to data type bigint.

In any event, error checking is a must...for all code...

Since the query returns float, the range would be - 1.79E + 308 through 1.79E + 308, that's what I meant that overflow shouldn't be a problem for actual situration. To prevent errors of overflow, the query may check the value before applying EXP(), that would be something like

case
when sum(log(...)) > 307 then ... -- overflow
when sum(log(...)) < -307 then ... -- underflow
else exp(sum(log(...)))
end|||Ahhh proactive error handling...very nice...

just watch...the [banging head]overhead[/banging head] for some statements...|||Originally posted by Brett Kaiser
Ahhh proactive error handling...very nice...

just watch...the [banging head]overhead[/banging head] for some statements...

I agree that the overhead should be considered. Normally procedural statements have more control over what needs to be done while combination of functions tends to make code look neat. Which choice to go would depends on actual siturations. :)

Multiple Value TextBox

Hi Folks,

I'm trying to assign multiple values to a textbox and I'm receiving an error. The error says, "The value expression for the textbox AcctName contains an error." The first value is account number and the second value is account name. An example follows:

1234 - SPC Travel Agency

My expression for the textbox contains the following:

=Fields!AcctNum.Value + ' - ' + Fields!AcctName.Value

Please help.

Hello,

Try this:

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Hope this helps.

Jarret

|||

Hello:

Jarret thanks for responding however, I figured it out by doing the following:

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

Best regards

|||

Yes, that does the same as what I posted, except without the cast for string. Since you didn't include the field type on AcctNum, I went ahead and included the cStr(), just in case it was a numeric field.

Jarret

|||

Jarret wrote:

Yes, that does the same as what I posted, except without the cast for string. Since you didn't include the field type on AcctNum, I went ahead and included the cStr(), just in case it was a numeric field.

Jarret

He used & for string concat, while you used + for integer concat/sum though

I do that all the time in SSRS, using + (in T-SQL) instead of & (in VB)

|||

Hello Jerry,

That is true, but the '+' and '&' behave the same when working with strings. Since I casted the field as string, they work the same. The other problem with the expression in his first post was that he was trying to use a single quote (') instead of a double quote (") on the literal string (the dash between the AcctNum and AcctName).

The & does an implicit convert to string on the operators. So, where I did the cast using cStr(), the & handled that itself.

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Jarret

|||

Jarret wrote:

Hello Jerry,

That is true, but the '+' and '&' behave the same when working with strings. Since I casted the field as string, they work the same. The other problem with the expression in his first post was that he was trying to use a single quote (') instead of a double quote (") on the literal string (the dash between the AcctNum and AcctName).

The & does an implicit convert to string on the operators. So, where I did the cast using cStr(), the & handled that itself.

=Fields!AcctNum.Value & " - " & Fields!AcctName.Value

=cStr(Fields!AcctNum.Value) + " - " + Fields!AcctName.Value

Jarret

Thanks Jarret

That's nice to know that it knows how to interpret the + concat as well

Multiple Transaction Logs

Hey Folks,
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJay
DeeJay,
Have you looked in Books Online? I think that there's a pretty good section about these things. Not
that complicated, though:

> For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
you cut the tail of the snake. If you have several files, and you are at then end of the current
file (and it is full), the head of the snake moves to the beginning of the next file.

> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out over several physical
files). At restore, though, you need to have as many files as when you performed the backup.

> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.

> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay
|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain it
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:

> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good section about these things. Not
> that complicated, though:
>
> One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at then end of the current
> file (and it is full), the head of the snake moves to the beginning of the next file.
>
> You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread out over several physical
> files). At restore, though, you need to have as many files as when you performed the backup.
>
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
>
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
>
>

Multiple Transaction Logs

Hey Folks,
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJayDeeJay,
Have you looked in Books Online? I think that there's a pretty good section
about these things. Not
that complicated, though:

> For
> example, if I create two transaction logs for a database, how are they use
d?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). E
ach time you backup log,
you cut the tail of the snake. If you have several files, and you are at the
n end of the current
file (and it is full), the head of the snake moves to the beginning of the n
ext file.

> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the
database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out
over several physical
files). At restore, though, you need to have as many files as when you perfo
rmed the backup.

> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER
DATABASE to remove it.

> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). T
he log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inacti
ve (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they use
d?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain i
t
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:

> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good sectio
n about these things. Not
> that complicated, though:
>
> One at a time. Think about the log as a snake rattling though the file(s).
Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at t
hen end of the current
> file (and it is full), the head of the snake moves to the beginning of the
next file.
>
> You don't backup the log file. You perform a transaction log backup for th
e database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread o
ut over several physical
> files). At restore, though, you need to have as many files as when you per
formed the backup.
>
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALT
ER DATABASE to remove it.
>
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files).
The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inac
tive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
>
>

Multiple Transaction Logs

Hey Folks,
I was looking for some documents/articles/links to get a better
understanding of Multiple Transaction Logs for SQL Server 2000/7. For
example, if I create two transaction logs for a database, how are they used?
Round-Robin method until each one is full? Or use one at a time until the
second log file is needed?
I also wanted to learn about backing up and restore multiple transaction
logs. How to delete the second log file? Is one log active and the other
in-active, etc.
Hope to get some good reading material.
Thanks,
DeeJayDeeJay,
Have you looked in Books Online? I think that there's a pretty good section about these things. Not
that complicated, though:
> For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
you cut the tail of the snake. If you have several files, and you are at then end of the current
file (and it is full), the head of the snake moves to the beginning of the next file.
> I also wanted to learn about backing up and restore multiple transaction
> logs.
You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
the transaction log is one logical construct (which happens to be spread out over several physical
files). At restore, though, you need to have as many files as when you performed the backup.
> How to delete the second log file?
DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
> Is one log active and the other
> in-active, etc.
Not the files per se.
One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
Use DBCC LOGINFO for this. I mention a little bit about this in
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> Hey Folks,
> I was looking for some documents/articles/links to get a better
> understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> example, if I create two transaction logs for a database, how are they used?
> Round-Robin method until each one is full? Or use one at a time until the
> second log file is needed?
> I also wanted to learn about backing up and restore multiple transaction
> logs. How to delete the second log file? Is one log active and the other
> in-active, etc.
> Hope to get some good reading material.
> Thanks,
> DeeJay|||Tibor,
Thanks for the reply.
I was already familiar with these concepts (the snake analogy is a good
one). I am also familiar with the VLFs.
I was not sure about deleting the EMPTY log files. Thanks for that.
Thanks for the undocumented DBCC LOGINFO command!
I am getting cross-trained in DB2 and DB2 has a slightly different way of
handling its transaction logs (hence the terminology active/inactive). The
DB2 DBA had asked about it and I wanted to make sure I was able to explain it
to him properly.
Thanks,
DeeJay
"Tibor Karaszi" wrote:
> DeeJay,
> Have you looked in Books Online? I think that there's a pretty good section about these things. Not
> that complicated, though:
> > For
> > example, if I create two transaction logs for a database, how are they used?
> > Round-Robin method until each one is full? Or use one at a time until the
> > second log file is needed?
> One at a time. Think about the log as a snake rattling though the file(s). Each time you backup log,
> you cut the tail of the snake. If you have several files, and you are at then end of the current
> file (and it is full), the head of the snake moves to the beginning of the next file.
>
> > I also wanted to learn about backing up and restore multiple transaction
> > logs.
> You don't backup the log file. You perform a transaction log backup for the database. To SQL Server,
> the transaction log is one logical construct (which happens to be spread out over several physical
> files). At restore, though, you need to have as many files as when you performed the backup.
>
> > How to delete the second log file?
> DBCC SHRINKFILE with the EMPTYFILE option. Make sure it is empty. Then ALTER DATABASE to remove it.
> > Is one log active and the other
> > in-active, etc.
> Not the files per se.
> One thing you want to look into is the concept of VLF (Virtual Log Files). The log file is divided
> up in a number of VLF, and a VLF can be active (log records on it) or inactive (empty, not used).
> Use DBCC LOGINFO for this. I mention a little bit about this in
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DeeJay Puar" <DeeJayPuar@.discussions.microsoft.com> wrote in message
> news:8F52396F-1FBA-468C-B5C4-11BF4BDDB720@.microsoft.com...
> > Hey Folks,
> >
> > I was looking for some documents/articles/links to get a better
> > understanding of Multiple Transaction Logs for SQL Server 2000/7. For
> > example, if I create two transaction logs for a database, how are they used?
> > Round-Robin method until each one is full? Or use one at a time until the
> > second log file is needed?
> >
> > I also wanted to learn about backing up and restore multiple transaction
> > logs. How to delete the second log file? Is one log active and the other
> > in-active, etc.
> >
> > Hope to get some good reading material.
> >
> > Thanks,
> >
> > DeeJay
>
>