additional data files not filing

We have a quad sql server that runs OLTP transactions at the rate of
100's per second (read & Write).

We used to have all the tables on 1 file but started to notice high contention on this file. We added 3 more files to match the processor number. The problem is that the 3 additional files are not filling with data. Does anyone know why this happens or can reccommend a fix?
--
willVerify that the newly added files are in the appropriate file group (probably PRIMARY) and are of the appropriate file type (ie, they were created as data files, not log files). Also, your new file(s) may not get data written to them if the old file still has space available.

Your logic for reducing contention by adding files to match the number of processors does not make sense. Contention is caused by physical IO going to the same physical disk and getting backed up because the pipe to the physical disk is too small. You would add files to a database filegroup to ease contention by creating the files on separate physical disks. It has no relation the number of CPUs.

Regards,

hmscott|||Yes they are all the same file type (data files).

Microsoft claims that you should have a seperate file per processor for high contention OLTP sql servers. Parallelism is the goal here. Sql server can only access 1 file per cpu at any one time. If it was a dual processor machine It would have less impact considering the OS & tempdb usually have something going on in parallel.

Considering we have a quad sql server = 4 files per file group.

As an FYI we are running a raid 10 with 14 disks.|||Hmm, not doubting you, but could you send a link with the MS info? I had not seen that before.

As for your issue, what's the state of the original data file? Full, nearly full or not even close? Also, can you verify that the new files you added were updateable (ie, not read-only)? Beyond that, I would try testing some scenarios out on a development server somewhere.

Regards,

hmscott

Adding Weekend data to Monday

We have a data extract prcess that runs daily. In SQL Script, there is a
variable that equals to 1 (meaning 1 day worth of data). We run this monday
through friday at 9:00 PM. Now the client wants, if there is any processing
on the weekend (Saturday/Sunday) it should be added to the monday's data.
This is an automated process and we need to keep it like that. The question
is, how can I modify the script so that it picks up the daily data Tuesday
through friday and 3 days of data (Saturday, Sunday and Monday) on mondays ?
Thanks for any help.Do you want a sum or the rows on their own, only presenting those on the
weekdays ?
You have to specify your expected results a bit.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> We have a data extract prcess that runs daily. In SQL Script, there is a
> variable that equals to 1 (meaning 1 day worth of data). We run this
> monday
> through friday at 9:00 PM. Now the client wants, if there is any
> processing
> on the weekend (Saturday/Sunday) it should be added to the monday's data.
> This is an automated process and we need to keep it like that. The
> question
> is, how can I modify the script so that it picks up the daily data Tuesday
> through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> ?
> Thanks for any help.|||Data is selected according to what is in 'datetime' columns and not as sum.
"Jens Sü�meyer" wrote:
> Do you want a sum or the rows on their own, only presenting those on the
> weekdays ?
> You have to specify your expected results a bit.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> > We have a data extract prcess that runs daily. In SQL Script, there is a
> > variable that equals to 1 (meaning 1 day worth of data). We run this
> > monday
> > through friday at 9:00 PM. Now the client wants, if there is any
> > processing
> > on the weekend (Saturday/Sunday) it should be added to the monday's data.
> >
> > This is an automated process and we need to keep it like that. The
> > question
> > is, how can I modify the script so that it picks up the daily data Tuesday
> > through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> > ?
> >
> > Thanks for any help.
>
>|||Sure that you got your results in mind you want to have, we don´t.
Do you want to get something like
Tu-1,we-1,th-1.fr-1,m-4
...if there is data for every day.
Or do you want to build a sum of some value for thse days ?
The best things would be to provide us with some DDL and sample data.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:28A41391-7E40-4550-88D0-EC1A32AB35CF@.microsoft.com...
> Data is selected according to what is in 'datetime' columns and not as
> sum.
>
> "Jens Süßmeyer" wrote:
>> Do you want a sum or the rows on their own, only presenting those on the
>> weekdays ?
>> You have to specify your expected results a bit.
>> --
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
>> > We have a data extract prcess that runs daily. In SQL Script, there is
>> > a
>> > variable that equals to 1 (meaning 1 day worth of data). We run this
>> > monday
>> > through friday at 9:00 PM. Now the client wants, if there is any
>> > processing
>> > on the weekend (Saturday/Sunday) it should be added to the monday's
>> > data.
>> >
>> > This is an automated process and we need to keep it like that. The
>> > question
>> > is, how can I modify the script so that it picks up the daily data
>> > Tuesday
>> > through friday and 3 days of data (Saturday, Sunday and Monday) on
>> > mondays
>> > ?
>> >
>> > Thanks for any help.
>>

Adding Weekend data to Monday

We have a data extract prcess that runs daily. In SQL Script, there is a
variable that equals to 1 (meaning 1 day worth of data). We run this monday
through friday at 9:00 PM. Now the client wants, if there is any processing
on the weekend (Saturday/Sunday) it should be added to the monday's data.
This is an automated process and we need to keep it like that. The question
is, how can I modify the script so that it picks up the daily data Tuesday
through friday and 3 days of data (Saturday, Sunday and Monday) on mondays ?
Thanks for any help.
Do you want a sum or the rows on their own, only presenting those on the
weekdays ?
You have to specify your expected results a bit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> We have a data extract prcess that runs daily. In SQL Script, there is a
> variable that equals to 1 (meaning 1 day worth of data). We run this
> monday
> through friday at 9:00 PM. Now the client wants, if there is any
> processing
> on the weekend (Saturday/Sunday) it should be added to the monday's data.
> This is an automated process and we need to keep it like that. The
> question
> is, how can I modify the script so that it picks up the daily data Tuesday
> through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> ?
> Thanks for any help.
|||Data is selected according to what is in 'datetime' columns and not as sum.
"Jens Sü?meyer" wrote:

> Do you want a sum or the rows on their own, only presenting those on the
> weekdays ?
> You have to specify your expected results a bit.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
>
>
|||Sure that you got your results in mind you want to have, we dont.
Do you want to get something like
Tu-1,we-1,th-1.fr-1,m-4
...if there is data for every day.
Or do you want to build a sum of some value for thse days ?
The best things would be to provide us with some DDL and sample data.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:28A41391-7E40-4550-88D0-EC1A32AB35CF@.microsoft.com...[vbcol=seagreen]
> Data is selected according to what is in 'datetime' columns and not as
> sum.
>
> "Jens Smeyer" wrote:

Adding Weekend data to Monday

We have a data extract prcess that runs daily. In SQL Script, there is a
variable that equals to 1 (meaning 1 day worth of data). We run this monday
through friday at 9:00 PM. Now the client wants, if there is any processing
on the weekend (Saturday/Sunday) it should be added to the monday's data.
This is an automated process and we need to keep it like that. The question
is, how can I modify the script so that it picks up the daily data Tuesday
through friday and 3 days of data (Saturday, Sunday and Monday) on mondays ?
Thanks for any help.Do you want a sum or the rows on their own, only presenting those on the
weekdays ?
You have to specify your expected results a bit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> We have a data extract prcess that runs daily. In SQL Script, there is a
> variable that equals to 1 (meaning 1 day worth of data). We run this
> monday
> through friday at 9:00 PM. Now the client wants, if there is any
> processing
> on the weekend (Saturday/Sunday) it should be added to the monday's data.
> This is an automated process and we need to keep it like that. The
> question
> is, how can I modify the script so that it picks up the daily data Tuesday
> through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> ?
> Thanks for any help.|||Data is selected according to what is in 'datetime' columns and not as sum.
"Jens Sü?meyer" wrote:

> Do you want a sum or the rows on their own, only presenting those on the
> weekdays ?
> You have to specify your expected results a bit.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
>
>|||Sure that you got your results in mind you want to have, we dont.
Do you want to get something like
Tu-1,we-1,th-1.fr-1,m-4
...if there is data for every day.
Or do you want to build a sum of some value for thse days ?
The best things would be to provide us with some DDL and sample data.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:28A41391-7E40-4550-88D0-EC1A32AB35CF@.microsoft.com...[vbcol=seagreen]
> Data is selected according to what is in 'datetime' columns and not as
> sum.
>
> "Jens Smeyer" wrote:
>

Adding parameters during .updating event handler

Not receiving any errors. The update runs perfectly on all fields, except for the added parameter during the sub. I need to change the hidden field's value after parsing out several fields in the form and generating a logfile entry of sorts. This needs to happen after all the form fields are updated, but before the update is executed.

aspx: (relevant parts only...)
<asp:SqlDataSourceID="TicketDetails"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
UpdateCommand="UPDATE Tickets SET TicketSuspense = @.TicketSuspense, TicketPriority = @.TicketPriority, TicketLastUpdated = CURRENT_TIMESTAMP, TicketStatus = 'Assigned', TicketTechnicianNotes = @.TicketTechnicianNotes WHERE (TicketID = @.TicketID)">
<UpdateParameters>
<asp:ParameterName="TicketPriority"Type="String"/>
<asp:ParameterName="TicketSuspense"Type="DateTime"/>
<asp:ParameterName="TicketID"Type="Int32"/>
</UpdateParameters>
</asp:SqlDataSource>

codebehind: (once again, the relevant parts only)
ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value
TicketDetails.UpdateParameters.Add(New Parameter("TicketTechnicianNotes", TypeCode.String, TicketTechnicianNotesHiddenField.Value))
EndSub

Thanks,

- Brad

What about this:

ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value

e.Command.Parameters("@.TicketTechnicianNotes").Value = TicketTechnicianNotesHiddenField.Value

EndSub

|||

I think that was the only variation I hadn't yet tried. It worked.

I have to ask... what caused the "Add" not to work?

Thanks,

- Brad

|||

"TheSqlDataSource control will also automatically create parameters based on values passed by a data-bound control... "

You can find more information from this link:

http://msdn2.microsoft.com/en-us/library/z72eefad.aspx

Adding ALL to a query.

Ok I have a parameter on a report.
It list all the Agencys we have.
The person who runs the report selects all the agencies they wont on the
report.
THE PROBLEM:::
There are over 400 agencies!!!!!!!
This is what I wont to do.
I wont to add "ALL*" to the query after it creates the agencies list.
I wont to be able to select "ALL*" and that will make the query select all
the agencies in the list. then run the report.
Is this posible'
Scott Burke
SQL2005
Microsoft Visual Studio 2005Add a value called 'All' to your parameter list. If your parameter is based
on a query then do this:
select 'All' as label, 'All' as value UNION select somefield as label,
someotherfield as value
Then in your query do this:
select * from sometable where (yourfield = @.Param or @.Param = 'All)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:21AA81FF-08F0-4540-BD25-4C0A112957EB@.microsoft.com...
> Ok I have a parameter on a report.
> It list all the Agencys we have.
> The person who runs the report selects all the agencies they wont on the
> report.
> THE PROBLEM:::
> There are over 400 agencies!!!!!!!
> This is what I wont to do.
> I wont to add "ALL*" to the query after it creates the agencies list.
> I wont to be able to select "ALL*" and that will make the query select all
> the agencies in the list. then run the report.
> Is this posible'
> Scott Burke
> SQL2005
> Microsoft Visual Studio 2005|||If the drop-down list on the report is a multi-valued parameter then there
should already be a "Select All" option on your drop down...
"Scott Burke" wrote:
> Ok I have a parameter on a report.
> It list all the Agencys we have.
> The person who runs the report selects all the agencies they wont on the
> report.
> THE PROBLEM:::
> There are over 400 agencies!!!!!!!
> This is what I wont to do.
> I wont to add "ALL*" to the query after it creates the agencies list.
> I wont to be able to select "ALL*" and that will make the query select all
> the agencies in the list. then run the report.
> Is this posible'
> Scott Burke
> SQL2005
> Microsoft Visual Studio 2005