asking for SQL query

i am developing a account statement so when the user click on the link to view the February link it will show the transaction from Jan - Feb ,
how to format it to a query
i have the sample fo the following table:

ID trans_desc trans_date
-------------
1 testing 1/9/2003 11:33:00 AM
2 testing1 2/9/2003 11:33:00 AM
3 testing2 1/9/2004 11:33:00 AM

how to i extract the transaction description month from the month Jan-Feb . how to i do for the SQL queryIf there are 50 people on this forum, you'll get 50 different ways to do this. Here is one:

declare @.SelectedDate datetime
set @.SelectedDate = '2003-02-15'

select *
from YourTable
where trans_date >= convert(char(4), @.SelectedDate, 120)+'-01-01'
and trans_date < dateadd(month, 1, convert(char(7), @.SelectedDate, 120)+'-01')

AS2000. How to organize pocessing?

In AS 2000 I made update processing off all dimension and full processing of the last partition in every cube. I was made in a transaction. Duering processing the old version of data was available for the MDX querying.

If I do the same in AS 2000, the database seem to be locked.

What I make wrong?

Hiere is the xmla batch what I send to the AS2005.

<Batch Transaction="true" ProcessAffectedObjects="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<DimensionID>Abteilung</DimensionID>

</Object>

<Type>ProcessUpdate</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<DimensionID>Adm</DimensionID>

</Object>

<Type>ProcessUpdate</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<DimensionID>Artikel</DimensionID>

</Object>

<Type>ProcessUpdate</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>Verkauf</MeasureGroupID>

<PartitionID>Verkauf_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>VerkaufAuftrag</MeasureGroupID>

<PartitionID>VerkaufAuftrag_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>VerkaufRechnung</MeasureGroupID>

<PartitionID>VerkaufRechnung_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<Object>

<DatabaseID>MW</DatabaseID>

<CubeID>Verkauf</CubeID>

<MeasureGroupID>VerkaufArtikel</MeasureGroupID>

<PartitionID>VerkaufArtikel_2006</PartitionID>

</Object>

<Type>ProcessFull</Type>

</Process>

</Parallel>

</Batch>

Do you mean "If I do the same in AS 2005, the database seem to be locked" ?

If this is related to Analysis Services 2005 behvior, this is strange. I think you should have access to product support. Please report this problem.

Thanks.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Arithmetic overflow error converting expression to data type datetime

I have a sql server 2000 database...using vb.net 2005... I have a form which allow user to find outstanding transaction by month selected in dropdownlist... If it matches the month it will populate out in datagrid, this date is store as dd/MM/yyyy... below is the following code:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf ddl_payment.SelectedItem.Text ="Outstanding"Then ddl_months.Visible =True datagrid1.Visible =False If ddl_months.Visible =True Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(mm,dateOfPurchase)='" + ddl_months.SelectedValue + "'AND balance > 0") bindData() datagrid1.Visible = False datagrid.Visible = True End If ElseIf ddl_payment.SelectedItem.Text = "Fully Paid" Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE balance = 0") bindData1() datagrid.Visible =False datagrid1.Visible =True ddl_months.Visible =False Else datagrid.Visible =False datagrid1.Visible =False ddl_months.Visible =False End If End Sub

Below is the error:

Arithmetic overflow error converting expression to data type datetime.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.

Source Error:

Line 76: cmd.CommandText = sqlStatementLine 77: cmd.Connection = connLine 78: cmd.ExecuteNonQuery()Line 79: Line 80: reader = cmd.ExecuteReader

Source File:c:\inetpub\wwwroot\TAKA\App_Code\config.vb Line:78

Stack Trace:

[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 TAKA.config.dataReader2(String sqlStatement) in c:\inetpub\wwwroot\TAKA\App_Code\config.vb:78 TAKA.PaymentStatus.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\TAKA\PaymentStatus.aspx.vb:65 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061

I have another table which is staff table and it datefield is store in MM/dd/yyyy format

DATEPART returns an integer so wrappingddl_months.SelectedValue in quotes is probably pointless.

Seeing as it's a sql exception I would say it's extremely likely that the field "dateOfPurchase" is of the wrong type. Perhaps ur storing it as a string or an int...?

Also sql doesn't store dates in a locale specific format, the fact that u've said "I have another table whihc is staff table and it datefield is store in MM/dd/yyyy format" suggests to me ur storing ur dates as strings which will not work.

|||

Furthermore executing unparameterised queries against sql is slap-worthy offence. Use parameters... or get hacked.

|||

I waited so long for it approved zzz...ya...worldspawn thx for reply... 4get to said that my date is in nvarchar format... erm so any idea how to get the month in the database in this format? Or do I need change it to smalldatetime format? I need smth like For example my database has this date(23/10/2007, 21/09/2007,22/10/2007)

How can i retrieve by month, If ddl_months i selected '10' den that 2 records with month 10 will shown in datagrid...

Thank in adv

|||

If u want to keep it as a string u'll need to store it in a local generic format such as yyyy-mm-dd. You should then be able to CAST/CONVERT to smalldatetime and then run DATEPART on the converted value.

But i would highly recommend storing it as smalldatetime; unless you have some need to store it as text...?

If you just change it to smalldatetime your code will start working (except remove the quotes from ur sql string. 10 not '10')

That's interesting about the approval. I used to get that but stopped seeing it. I assumed they'd just made everything unmoderated but it's probably just for user's that havent reached 'participant' status.

|||

Would it be safe if i jus change my database dateOfPurchase to smalldatetime format? Cos i received prompt when trying to save... It say data may be lost when trying to convert nvarchar to smalldatetime... below is a few line example of my code... I select a date from calendar and display it in a textbox then save all the data enter in database... will there be any serious problem if i change?

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

todayD.Text = Calendar1.TodaysDate().ToString("dd/MM/yyyy")

End Sub

PrivateSub Calendar1_SelectionChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Calendar1.SelectionChanged

'display selected date from calendar

tb_dop.Text = Calendar1.SelectedDate().ToString("dd/MM/yyyy")

EndSub

PublicSub AddTransaction1(ByVal dateOfPurchaseAsString,ByVal totalAmountAsString,ByVal paymentReceivedAsString,ByVal bAsString,ByVal receiptInvoiceAsString,ByVal remarksAsString,ByVal csngInvoiceAsString,ByVal transDetailsAsString,ByVal coNameAsString)

'declare the INSERT method for INSERT transaction

Dim strSQLAsString ="INSERT INTO custTransaction (dateOfPurchase, totalAmount, paymentReceived, balance, receiptInvoice, remarks, csngInvoice, transDetails, coName) VALUES ('" + dateOfPurchase +"'," + totalAmount +"," + paymentReceived +"," + b +",'" + receiptInvoice +"','" + remarks +"', '" + csngInvoice +"', '" + transDetails +"', '" + coName +"')"

con1.Open()

Dim cmdAsNew SqlCommand(strSQL, con1)

cmd.ExecuteNonQuery()

con1.Close()

EndSub

PrivateSub btn_add2_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btn_add2.Click

dateOfPurchase = tb_dop.Text

If tb_csngInvoice.Text ="None"Then

'check for duplication

If tb_receiptInvoice.Text = config.dataReader2("SELECT receiptInvoice FROM custTransaction WHERE receiptInvoice = '" + tb_receiptInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

ElseIf tb_receiptInvoice.Text ="None"Then

'check for duplication

If tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text +"'")Then

emsg.Text ="The InvoiceNo. already exist OR cannot be None"

'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then

' emsg.Text = " The csngInvoiceNo. already exist"

Else

emsg.Text =""

AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)

'redirect to add transaction successful page

Response.Redirect("AddTransactionSuccessfully.aspx")

EndIf

EndIf

EndSub

Thank you for ur time

|||

Is this production database? Honestly i have no idea what would happen exactly but i think it's safe to say ur existing date fields will become incorrect and it may encounter and error trying to convert them and not even work... make sure sql server's date format is dmy and it should work without any problems, if it's still set to mdy u'll have big problems.

Also if you want to display a date in the form dd/MM/yyyy use the inbuilt ToShortDateString method which will convert the date to that format (as long as ur server is configured to use that dateformat by default).

Please read a tutorial on how to property construct a sql command using parameters and not string concatenation. String concatentation leaves u open to sql injection attacks... plus it's just crappy.

|||

Hi, worldspawn can i know how u convert the sql server to display date in dd/MM/yyyy... although i have set the my own server to UK time but it still display as MM/dd/yyyy... Thanks

|||

I'm not a sql guru but I believe u type:

SET DATEFORMAT dmy

So just connect to the sql server with query analyzer or whatever and execute that. It's 6pm, i'm off home, best of luck :)

|||

Hi,

I think the here type of the field as being smalldatetime or datetime is not the issue.

Smalldatetime can handle dates between 1/1/1900 and 6/6/2079 with the accuracy of 1 minute

Datetime can handle dates between 1/1/1753 and 31/12/9999 with the accuracy of 3.33 miliseconds.

So you do not need to change the type of the field. Do it if you have billions of rows to decrease the database size because datetime consumes 8 bytes and smalldatetime consumes 4 bytes. But as mentioned above the accuracy should not be important for that filed.

I think shifting from datetime to smalldatetime will increase the performance.

If you want to know if some data would be truncated. 

select dateOfPurchase from custTransaction where dateOfPurchase < cast('01/01/1900 00:00:00' as datetime) and dateOfPurchase > cast('06/06/2079 00:00:00' as datetime)

run this if you have any result than dont convert to smalldatetime.

 About the main problem, I am not sure you should analyze the script with profiler but maybe this helps;

First be sure  ddl_months.SelectedValue is integer

then try the code as 

 config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(m,dateOfPurchase) = cast('"+ ddl_months.SelectedValue + "', as integer)AND balance > 0")

Hope this helps.

|||

Hi yvzman,

A nice infomation to note... Thank for replying... But i think is use CONVERT instead of CAST... dunno y CAST is not longer supported by my script

Are NT users stored in SQL Server 2000/2005 transaction logs?

I am trying to implement auditing the cheapest way possible. I know SQL
Server users get logged but how about NT users?
--
MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
"Helping people grow and develop their full potential as God has plan for
them"Hi
Logins are for SQL Server access
Users are for database access
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
"bass_player" <bassplayer@.discussions.microsoft.com> wrote in message
news:1719566F-D101-441F-95F6-B9D11AAD347B@.microsoft.com...
>I am trying to implement auditing the cheapest way possible. I know SQL
> Server users get logged but how about NT users?
> --
> MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
> "Helping people grow and develop their full potential as God has plan for
> them"|||They (nt or sql logins) do not get logged in the transaction log. Do check
out the following article for details:
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx
-oj
"bass_player" <bassplayer@.discussions.microsoft.com> wrote in message
news:1719566F-D101-441F-95F6-B9D11AAD347B@.microsoft.com...
>I am trying to implement auditing the cheapest way possible. I know SQL
> Server users get logged but how about NT users?
> --
> MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
> "Helping people grow and develop their full potential as God has plan for
> them"

Are NT users stored in SQL Server 2000/2005 transaction logs?

I am trying to implement auditing the cheapest way possible. I know SQL
Server users get logged but how about NT users?
--
MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
"Helping people grow and develop their full potential as God has plan for
them"Hi
Logins are for SQL Server access
Users are for database access
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"bass_player" <bassplayer@.discussions.microsoft.com> wrote in message
news:1719566F-D101-441F-95F6-B9D11AAD347B@.microsoft.com...
>I am trying to implement auditing the cheapest way possible. I know SQL
> Server users get logged but how about NT users?
> --
> MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
> "Helping people grow and develop their full potential as God has plan for
> them"|||They (nt or sql logins) do not get logged in the transaction log. Do check
out the following article for details:
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
-oj
"bass_player" <bassplayer@.discussions.microsoft.com> wrote in message
news:1719566F-D101-441F-95F6-B9D11AAD347B@.microsoft.com...
>I am trying to implement auditing the cheapest way possible. I know SQL
> Server users get logged but how about NT users?
> --
> MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
> "Helping people grow and develop their full potential as God has plan for
> them"

Are multiple transaction logs used concurrently or sequentially?

It'd be nice to have redundant transaction logs, maybe even one on a
mapped drive, but I read in one post here that SQL Server considers
all transaction logs to be one big log file. Is this true?
Is there any way to have redundant transaction logs without mirroring>
Hi,
No, SQL Server allows to create multiple Transaction logs for a database.
Eg: from books online:-
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
Thanks
Hari
SQL Server MVP
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>
|||On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>No, SQL Server allows to create multiple Transaction logs for a database.
>
I understand that you can have multiple transaction logs, but are they
concurrent, that is, interchangeable copies of one another, or are
they used one after another as continuation files?
|||Hi,
They are interchangable.Check out the section "Transaction Log Physical
Architecture" in the SQL
Server Books Online.
Thanks
Hari
"fumanchu" <leondobr@.verizon.net> wrote in message
news:ii1un0h1o0tnk7s3ndmb66oc8v3nv7jb2j@.4ax.com...
> On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
> I understand that you can have multiple transaction logs, but are they
> concurrent, that is, interchangeable copies of one another, or are
> they used one after another as continuation files?
|||The logs are all in use, you would use multiple logs across multiple RAID
sets to gain better performance, say you had 3 log files, data would be
striped across all 3.
I sort of see what you might want to do what you ask, you could achieve this
by doing log shipping, but, the interval between log dumps would be your
potentially non-recovery window.
You could also look at clustering.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||The way the SQL Logs work is sequential... Even if you have 5 logs, SQL is
only using one at a time, writing serially to the end of the file... So
having multiple files does not improve performance.
Mirroring is probably the best way to get redundancy on the logs, and it is
a best practice that I suspect most people follow.
There is some file mirroring software out there ( I can't remember the
company ) that keeps files in sync real-time, and they claim this works with
SQL as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>
|||Hi Wayne,
If the database has multiple logs they are written to (striped) at the same
time - so, if you put them on the same logical disk they will grow together.
For example, if you create a database (see below) with 2 log files and write
a big table onto it, both log files will grow together, in my own test here
they both ended up 39MB.
The biggest problem here is that its striping (essentially RAID 0) so no
fault tolerance, if you put the logs on seperate drives and one goes then
you lose the log.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
GO
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||Tony,
I think the question is about performance, and I have a feeling that SQL Server will not write
concurrently to these log files. AFAIK, SQL Server will grow one file (with z number of VLF's) fill
that, then grow the other file and fill that etc. I don't know HW enough to determine whether we
still could gain anything by having multiple log files, but assuming a simple install with one log
file per physical disk, I would assume that only one disk would be hit at a time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:ezYBooCvEHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi Wayne,
> If the database has multiple logs they are written to (striped) at the same time - so, if you put
> them on the same logical disk they will grow together.
> For example, if you create a database (see below) with 2 log files and write a big table onto it,
> both log files will grow together, in my own test here they both ended up 39MB.
> The biggest problem here is that its striping (essentially RAID 0) so no fault tolerance, if you
> put the logs on seperate drives and one goes then you lose the log.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> GO
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
|||Hi Tibor,
Nope - try the example, SQL Server stripes across the 2 log files! It does
the same for multiple data files in a file group as well.
Create a database using the create db below and do something like this (from
a big table in another db)...
select *
into mytest
from yourotherdb..bigtable
where 1 = 0
go
insert mytest
select *
from yourotherdb..bigtable
Observe that the two log files end up the same size, in fact, watch them
grow - they grow at the same rate.
When multiple log (or data files) for a single db are placed on different
logical disks, the proportion of data striped to each file is dependant on
how much free space there is on the drives where they reside.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||Hi Tony
I think we're not talking about growing the physical files, but how existing
log files are filled. Your script shows that once the log files have reached
their initially created size, then new space is allocated for each of them
in a striped fashion.
But, if you change your script to initially create your log files with some
reasonable size, and then use dbcc loginfo to examine them as the insert is
taking place, you will see that one of them gets filled completely before
the other one starts to be filled.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23YSfShEvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi Tibor,
> Nope - try the example, SQL Server stripes across the 2 log files! It does
> the same for multiple data files in a file group as well.
> Create a database using the create db below and do something like this
> (from a big table in another db)...
> select *
> into mytest
> from yourotherdb..bigtable
> where 1 = 0
> go
> insert mytest
> select *
> from yourotherdb..bigtable
> Observe that the two log files end up the same size, in fact, watch them
> grow - they grow at the same rate.
> When multiple log (or data files) for a single db are placed on different
> logical disks, the proportion of data striped to each file is dependant on
> how much free space there is on the drives where they reside.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>

Are multiple transaction logs used concurrently or sequentially?

It'd be nice to have redundant transaction logs, maybe even one on a
mapped drive, but I read in one post here that SQL Server considers
all transaction logs to be one big log file. Is this true?
Is there any way to have redundant transaction logs without mirroring>Hi,
No, SQL Server allows to create multiple Transaction logs for a database.
Eg: from books online:-
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
Thanks
Hari
SQL Server MVP
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>|||On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hi,
>No, SQL Server allows to create multiple Transaction logs for a database.
>
I understand that you can have multiple transaction logs, but are they
concurrent, that is, interchangeable copies of one another, or are
they used one after another as continuation files?|||Hi,
They are interchangable.Check out the section "Transaction Log Physical
Architecture" in the SQL
Server Books Online.
Thanks
Hari
"fumanchu" <leondobr@.verizon.net> wrote in message
news:ii1un0h1o0tnk7s3ndmb66oc8v3nv7jb2j@.4ax.com...
> On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>>Hi,
>>No, SQL Server allows to create multiple Transaction logs for a database.
> I understand that you can have multiple transaction logs, but are they
> concurrent, that is, interchangeable copies of one another, or are
> they used one after another as continuation files?|||The logs are all in use, you would use multiple logs across multiple RAID
sets to gain better performance, say you had 3 log files, data would be
striped across all 3.
I sort of see what you might want to do what you ask, you could achieve this
by doing log shipping, but, the interval between log dumps would be your
potentially non-recovery window.
You could also look at clustering.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||The way the SQL Logs work is sequential... Even if you have 5 logs, SQL is
only using one at a time, writing serially to the end of the file... So
having multiple files does not improve performance.
Mirroring is probably the best way to get redundancy on the logs, and it is
a best practice that I suspect most people follow.
There is some file mirroring software out there ( I can't remember the
company ) that keeps files in sync real-time, and they claim this works with
SQL as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>|||Hi Wayne,
If the database has multiple logs they are written to (striped) at the same
time - so, if you put them on the same logical disk they will grow together.
For example, if you create a database (see below) with 2 log files and write
a big table onto it, both log files will grow together, in my own test here
they both ended up 39MB.
The biggest problem here is that its striping (essentially RAID 0) so no
fault tolerance, if you put the logs on seperate drives and one goes then
you lose the log.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
GO
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Tony,
I think the question is about performance, and I have a feeling that SQL Server will not write
concurrently to these log files. AFAIK, SQL Server will grow one file (with z number of VLF's) fill
that, then grow the other file and fill that etc. I don't know HW enough to determine whether we
still could gain anything by having multiple log files, but assuming a simple install with one log
file per physical disk, I would assume that only one disk would be hit at a time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:ezYBooCvEHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi Wayne,
> If the database has multiple logs they are written to (striped) at the same time - so, if you put
> them on the same logical disk they will grow together.
> For example, if you create a database (see below) with 2 log files and write a big table onto it,
> both log files will grow together, in my own test here they both ended up 39MB.
> The biggest problem here is that its striping (essentially RAID 0) so no fault tolerance, if you
> put the logs on seperate drives and one goes then you lose the log.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> GO
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Hi Tibor,
Nope - try the example, SQL Server stripes across the 2 log files! It does
the same for multiple data files in a file group as well.
Create a database using the create db below and do something like this (from
a big table in another db)...
select *
into mytest
from yourotherdb..bigtable
where 1 = 0
go
insert mytest
select *
from yourotherdb..bigtable
Observe that the two log files end up the same size, in fact, watch them
grow - they grow at the same rate.
When multiple log (or data files) for a single db are placed on different
logical disks, the proportion of data striped to each file is dependant on
how much free space there is on the drives where they reside.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Hi Tony
I think we're not talking about growing the physical files, but how existing
log files are filled. Your script shows that once the log files have reached
their initially created size, then new space is allocated for each of them
in a striped fashion.
But, if you change your script to initially create your log files with some
reasonable size, and then use dbcc loginfo to examine them as the insert is
taking place, you will see that one of them gets filled completely before
the other one starts to be filled.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23YSfShEvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi Tibor,
> Nope - try the example, SQL Server stripes across the 2 log files! It does
> the same for multiple data files in a file group as well.
> Create a database using the create db below and do something like this
> (from a big table in another db)...
> select *
> into mytest
> from yourotherdb..bigtable
> where 1 = 0
> go
> insert mytest
> select *
> from yourotherdb..bigtable
> Observe that the two log files end up the same size, in fact, watch them
> grow - they grow at the same rate.
> When multiple log (or data files) for a single db are placed on different
> logical disks, the proportion of data striped to each file is dependant on
> how much free space there is on the drives where they reside.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Sorry Kalen, didn't read the entire post.
I see that the first file is written to then it works from top to bottom on
the second file filling that first and then the first file until filled.
The allocation of space is done to both, which makes sense.
Sorry Wayne/Tibor !
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Are multiple transaction logs used concurrently or sequentially?

It'd be nice to have redundant transaction logs, maybe even one on a
mapped drive, but I read in one post here that SQL Server considers
all transaction logs to be one big log file. Is this true?
Is there any way to have redundant transaction logs without mirroring>Hi,
No, SQL Server allows to create multiple Transaction logs for a database.
Eg: from books online:-
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql
server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
Thanks
Hari
SQL Server MVP
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.
4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>|||On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>No, SQL Server allows to create multiple Transaction logs for a database.
>
I understand that you can have multiple transaction logs, but are they
concurrent, that is, interchangeable copies of one another, or are
they used one after another as continuation files?|||Hi,
They are interchangable.Check out the section "Transaction Log Physical
Architecture" in the SQL
Server Books Online.
Thanks
Hari
"fumanchu" <leondobr@.verizon.net> wrote in message
news:ii1un0h1o0tnk7s3ndmb66oc8v3nv7jb2j@.
4ax.com...
> On Tue, 26 Oct 2004 21:03:37 -0500, "Hari Prasad"
> <hari_prasad_k@.hotmail.com> wrote:
>
> I understand that you can have multiple transaction logs, but are they
> concurrent, that is, interchangeable copies of one another, or are
> they used one after another as continuation files?|||The logs are all in use, you would use multiple logs across multiple RAID
sets to gain better performance, say you had 3 log files, data would be
striped across all 3.
I sort of see what you might want to do what you ask, you could achieve this
by doing log shipping, but, the interval between log dumps would be your
potentially non-recovery window.
You could also look at clustering.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||The way the SQL Logs work is sequential... Even if you have 5 logs, SQL is
only using one at a time, writing serially to the end of the file... So
having multiple files does not improve performance.
Mirroring is probably the best way to get redundancy on the logs, and it is
a best practice that I suspect most people follow.
There is some file mirroring software out there ( I can't remember the
company ) that keeps files in sync real-time, and they claim this works with
SQL as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"fumanchu" <leondobr@.verizon.net> wrote in message
news:rjvtn0tjdv3afv74bv33t3k9ueng4fg7ml@.
4ax.com...
> It'd be nice to have redundant transaction logs, maybe even one on a
> mapped drive, but I read in one post here that SQL Server considers
> all transaction logs to be one big log file. Is this true?
> Is there any way to have redundant transaction logs without mirroring>|||Hi Wayne,
If the database has multiple logs they are written to (striped) at the same
time - so, if you put them on the same logical disk they will grow together.
For example, if you create a database (see below) with 2 log files and write
a big table onto it, both log files will grow together, in my own test here
they both ended up 39MB.
The biggest problem here is that its striping (essentially RAID 0) so no
fault tolerance, if you put the logs on seperate drives and one goes then
you lose the log.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
GO
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Tony,
I think the question is about performance, and I have a feeling that SQL Ser
ver will not write
concurrently to these log files. AFAIK, SQL Server will grow one file (with
z number of VLF's) fill
that, then grow the other file and fill that etc. I don't know HW enough to
determine whether we
still could gain anything by having multiple log files, but assuming a simpl
e install with one log
file per physical disk, I would assume that only one disk would be hit at a
time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:ezYBooCvEHA.568@.TK2MSFTNGP09.phx.gbl...
> Hi Wayne,
> If the database has multiple logs they are written to (striped) at the sam
e time - so, if you put
> them on the same logical disk they will grow together.
> For example, if you create a database (see below) with 2 log files and wri
te a big table onto it,
> both log files will grow together, in my own test here they both ended up
39MB.
> The biggest problem here is that its striping (essentially RAID 0) so no f
ault tolerance, if you
> put the logs on seperate drives and one goes then you lose the log.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> GO
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>|||Hi Tibor,
Nope - try the example, SQL Server stripes across the 2 log files! It does
the same for multiple data files in a file group as well.
Create a database using the create db below and do something like this (from
a big table in another db)...
select *
into mytest
from yourotherdb..bigtable
where 1 = 0
go
insert mytest
select *
from yourotherdb..bigtable
Observe that the two log files end up the same size, in fact, watch them
grow - they grow at the same rate.
When multiple log (or data files) for a single db are placed on different
logical disks, the proportion of data striped to each file is dependant on
how much free space there is on the drives where they reside.
CREATE DATABASE testlog
ON PRIMARY
( NAME = testdb1,
FILENAME = N'e:\testlog1.mdf',
SIZE = 10MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = testlog1,
FILENAME = N'h:\testlog1.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%),
( NAME = testlog2,
FILENAME = N'h:\testlog2.ldf',
SIZE = 512KB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||Hi Tony
I think we're not talking about growing the physical files, but how existing
log files are filled. Your script shows that once the log files have reached
their initially created size, then new space is allocated for each of them
in a striped fashion.
But, if you change your script to initially create your log files with some
reasonable size, and then use dbcc loginfo to examine them as the insert is
taking place, you will see that one of them gets filled completely before
the other one starts to be filled.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> wrote in message
news:%23YSfShEvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hi Tibor,
> Nope - try the example, SQL Server stripes across the 2 log files! It does
> the same for multiple data files in a file group as well.
> Create a database using the create db below and do something like this
> (from a big table in another db)...
> select *
> into mytest
> from yourotherdb..bigtable
> where 1 = 0
> go
> insert mytest
> select *
> from yourotherdb..bigtable
> Observe that the two log files end up the same size, in fact, watch them
> grow - they grow at the same rate.
> When multiple log (or data files) for a single db are placed on different
> logical disks, the proportion of data striped to each file is dependant on
> how much free space there is on the drives where they reside.
> CREATE DATABASE testlog
> ON PRIMARY
> ( NAME = testdb1,
> FILENAME = N'e:\testlog1.mdf',
> SIZE = 10MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> LOG ON
> ( NAME = testlog1,
> FILENAME = N'h:\testlog1.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%),
> ( NAME = testlog2,
> FILENAME = N'h:\testlog2.ldf',
> SIZE = 512KB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%)
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>