multiple instances of sqlservr.exe process

I have a Windows Server 2003 with SQL server 2000
installed. Whenever I view the Task Manager/Processes I
see three entires for the sqlservr.exe process. I am
thinking this is not normal and must be causing certain
performance issues I am facing. Whenever the server has
been up for a few hours the server starts getting slower.
If I reboot the server everything stays fine for a few
hours but then it starts slowing down. I have 2GB of RAM
on the server, about 20 Gigs free space on the C: drive
and about 120 Gigs free space on the data drive. All the
database files are stored on the data drive. Any help or
pointers in the right direction would be greatly
appreciated.
LakshmanHow many services whose name starts "MSSQL" do you see when you look in
Services ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Lakshman" <hlokku@.hotmail.com> wrote in message
news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
> I have a Windows Server 2003 with SQL server 2000
> installed. Whenever I view the Task Manager/Processes I
> see three entires for the sqlservr.exe process. I am
> thinking this is not normal and must be causing certain
> performance issues I am facing. Whenever the server has
> been up for a few hours the server starts getting slower.
> If I reboot the server everything stays fine for a few
> hours but then it starts slowing down. I have 2GB of RAM
> on the server, about 20 Gigs free space on the C: drive
> and about 120 Gigs free space on the data drive. All the
> database files are stored on the data drive. Any help or
> pointers in the right direction would be greatly
> appreciated.
> Lakshman|||Hello Lakshman,
Please monitor the following.
Memory\Available Bytes (Threshold<4MB)
Memory\Pages/sec (Threshold>20)
PageFile\ % Usage (Threshold>70%)
Physical Disk\Avg. Disk Queue Length ( Threshold: > 1.5 x # of spindles )
Physical Disk\% Disk Time (Threshold: >90% )
SQLServer:Memory Manager/Total Server Memory (per instance)
Also make sure your patch level is up to date on the SQL server and the OS.
Did you install any extended stored procs?
"Lakshman" <hlokku@.hotmail.com> wrote in message
news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
> I have a Windows Server 2003 with SQL server 2000
> installed. Whenever I view the Task Manager/Processes I
> see three entires for the sqlservr.exe process. I am
> thinking this is not normal and must be causing certain
> performance issues I am facing. Whenever the server has
> been up for a few hours the server starts getting slower.
> If I reboot the server everything stays fine for a few
> hours but then it starts slowing down. I have 2GB of RAM
> on the server, about 20 Gigs free space on the C: drive
> and about 120 Gigs free space on the data drive. All the
> database files are stored on the data drive. Any help or
> pointers in the right direction would be greatly
> appreciated.
> Lakshman|||I do not see any processes with "MSSQL" but there are 2
processes that say "sqlmangr.exe"
Lakshman.
>--Original Message--
>How many services whose name starts "MSSQL" do you see
when you look in
>Services ?
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Lakshman" <hlokku@.hotmail.com> wrote in message
>news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
>> I have a Windows Server 2003 with SQL server 2000
>> installed. Whenever I view the Task Manager/Processes I
>> see three entires for the sqlservr.exe process. I am
>> thinking this is not normal and must be causing certain
>> performance issues I am facing. Whenever the server has
>> been up for a few hours the server starts getting
slower.
>> If I reboot the server everything stays fine for a few
>> hours but then it starts slowing down. I have 2GB of RAM
>> on the server, about 20 Gigs free space on the C: drive
>> and about 120 Gigs free space on the data drive. All the
>> database files are stored on the data drive. Any help or
>> pointers in the right direction would be greatly
>> appreciated.
>> Lakshman
>
>.
>|||He was referring to Services:
Start -> Settings -> Control Panel -> Administrative Tools -> Services
<anonymous@.discussions.microsoft.com> wrote in message
news:294a01c3fca6$83b713d0$a501280a@.phx.gbl...
> I do not see any processes with "MSSQL" but there are 2
> processes that say "sqlmangr.exe"
> Lakshman.
> >--Original Message--
> >How many services whose name starts "MSSQL" do you see
> when you look in
> >Services ?
> >
> >--
> >HTH
> >
> >Jasper Smith (SQL Server MVP)
> >
> >I support PASS - the definitive, global
> >community for SQL Server professionals -
> >http://www.sqlpass.org
> >
> >
> >"Lakshman" <hlokku@.hotmail.com> wrote in message
> >news:2a3801c3fc98$efbaef60$a301280a@.phx.gbl...
> >> I have a Windows Server 2003 with SQL server 2000
> >> installed. Whenever I view the Task Manager/Processes I
> >> see three entires for the sqlservr.exe process. I am
> >> thinking this is not normal and must be causing certain
> >> performance issues I am facing. Whenever the server has
> >> been up for a few hours the server starts getting
> slower.
> >> If I reboot the server everything stays fine for a few
> >> hours but then it starts slowing down. I have 2GB of RAM
> >> on the server, about 20 Gigs free space on the C: drive
> >> and about 120 Gigs free space on the data drive. All the
> >> database files are stored on the data drive. Any help or
> >> pointers in the right direction would be greatly
> >> appreciated.
> >> Lakshman
> >
> >
> >.
> >|||If you have multiple sqlservr.exe process showing up then you have multiple
instances of SQL Server running on this server. There are a multitude of
applications that install a copy of MSDE 2000 and use it as a data store.
If you did not purposely install the instances of SQL Server then more than
like other applications did. BackUp Exec uses MSDE, so if you have it
installed on this server that would explain one of the instances.
As Adam said, check:
Start -> Settings -> Control Panel -> Administrative Tools -> Services
You should see multiple services that start with MSSQL.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Multiple Instance of DTS

I have a DTS package I created that is dadabase driven to process source data from multiple sources.

Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?

JavaWaba

javawaba wrote:

I have a DTS package I created that is dadabase driven to process source data from multiple sources.

Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?

JavaWaba

This isn't a DTS forum. I recommend you direct your question to the DTS newsgroup microsoft.public.sqlserver.dts

Multiple inserts

Hi Everyone -

I am trying to perform multiple inserts into a database -

At the moment, I have each insert on its own process -

But i would like to consolodate them into one statement...

Right now it is setup like this...

insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);

is there a way to streamline this operation?

thanks
tonyHi Tony,

Sure. You can concatenate them together into a single round trip to the db, something like this:

insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37); and so on.

You can also read them from a temp table, for example, using a SELECT INTO, if you're running the code in a sproc.

How are you running the insert statements?

Don|||Hi Don -

that is exactly what i tried as well -

joining them all into a single insert with semi-colons to seperate
the statements -

and agin - this has caused a failure -
although the failure happeded a LOT faster... :-)

I'm beginning to think the web server itself is causing the
failure on the desktop program.

I'm using the SQLXML URL as the means to post the data -

but i'm leaning twords the updates are posting too fast for the server
to handle them...

Next week i'm going to go ahead and remove 2.0 of the SQLXML
and replace it with the SQLXML 3.0 and see if that solves the problem.

BTW -

I've also created a stored proc to handle the insert -
but the same thing is happening - using these methods

single line insert - one at a time
multiple line inserts - all at once, one long concatenated line -
single line insert using the stores proc

I've also put a thread.sleep(1000) between each
of the inserts, and although it runs MUCH slower -
it does get past the point of failure for the single line inserts.

I've also noticed the program just crashed out to the desktop -
even with a VERY large amount of try catches -

it just blows right out of the application.

thanks for responding.

take care
tony|||Hi Tony,

Yeah, SQLXML 2.0 (actually called something different: Web Release for SQL or something like that) had some issues. Make sure you install the SP1 of SQLXML 3.0. That version fixes a lot of stuff, and I'd say that's our prime suspect right now.

If it doesn't solve the problem, we can dig further. You hadn't mentioned you were using SQLXML, so there may be something about how you're using it that's causing a problem. Still kinda weird, though.

Keep us posted!
Don

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors yo
u
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:

> I have another question. I took a Full Backup of the database before addin
g
> the secondary filegroup(single filegroup database) , the backup file size
is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. Th
e
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is har
d
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you the
n
have the choice of having different indexes on the load table to the archive
d
data; you would also be able to build the indexes on the archived data at th
e
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:

> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties
->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older da
ta
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This ma
y
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
>
>

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors you
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:
> I have another question. I took a Full Backup of the database before adding
> the secondary filegroup(single filegroup database) , the backup file size is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. The
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> > Hi Everyone,
> >
> > I added a secondary filegroup to a production database and moved some
> > tables to it from the Primary Filegroup.In the process I changed the
> > database recovery model to Simple and back to Full,backed up the
> > Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> > database recovery model is Full at the moment. I have noticed 2 oddities:
> >
> > 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> > MB a day,the database is being used as usual)
> >
> > 2. sp_spaceused and Enterprise Manager Console report strange values for
> > database available space:
> >
> > db_name size unallocated_space
> > DB_LIVE 55812.06 MB -4257.63 MB
> >
> > reserved data index_size
> > unused
> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >
> >
> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> > or is there somethig else I have to do?
> >
> > Best Regards.
> >
> >
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>> I have another question. I took a Full Backup of the database before
>> adding
>> the secondary filegroup(single filegroup database) , the backup file size
>> is
>> about 55,4 GB.
>> Today I backed up the Primary Filegroup and the newly added Filegroup1.
>> The
>> backup file size for the Primary filegroup is about 45 GB and backup file
>> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
>> GB.
>> As far as I know no data has been deleted. Is there any explanation
>> regarding the cause of the total size reduction ?
>> Regards.
>> Sezgin Rafet
>>
>> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
>> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>> > Hi Everyone,
>> >
>> > I added a secondary filegroup to a production database and moved some
>> > tables to it from the Primary Filegroup.In the process I changed the
>> > database recovery model to Simple and back to Full,backed up the
>> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
>> > The
>> > database recovery model is Full at the moment. I have noticed 2
>> > oddities:
>> >
>> > 1. The Transaction Log appears to grow much slower than before(only 2 -
>> > 5
>> > MB a day,the database is being used as usual)
>> >
>> > 2. sp_spaceused and Enterprise Manager Console report strange values
>> > for
>> > database available space:
>> >
>> > db_name size unallocated_space
>> > DB_LIVE 55812.06 MB -4257.63 MB
>> >
>> > reserved data index_size
>> > unused
>> > 61507528 KB 61203424 KB 291632 KB 12472 KB
>> >
>> >
>> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
>> > or is there somethig else I have to do?
>> >
>> > Best Regards.
>> >
>> >
>>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is hard
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you then
have the choice of having different indexes on the load table to the archived
data; you would also be able to build the indexes on the archived data at the
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:
> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties ->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older data
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This may
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> > Hi
> >
> > You don't say how you moved the tables, I assumed that you re-created the
> > clustered index in the new filegroup, in which case it would have been
> > re-built. If you had not defragged the index for a while then this may be
> > where the space has been found. You may want to look at what fill factors
> > you
> > are using or whether the clustered index is suitable.
> >
> > John
> >
> > "Sezgin Rafet" wrote:
> >
> >> I have another question. I took a Full Backup of the database before
> >> adding
> >> the secondary filegroup(single filegroup database) , the backup file size
> >> is
> >> about 55,4 GB.
> >> Today I backed up the Primary Filegroup and the newly added Filegroup1.
> >> The
> >> backup file size for the Primary filegroup is about 45 GB and backup file
> >> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
> >> GB.
> >> As far as I know no data has been deleted. Is there any explanation
> >> regarding the cause of the total size reduction ?
> >>
> >> Regards.
> >>
> >> Sezgin Rafet
> >>
> >>
> >>
> >> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> >> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> >> > Hi Everyone,
> >> >
> >> > I added a secondary filegroup to a production database and moved some
> >> > tables to it from the Primary Filegroup.In the process I changed the
> >> > database recovery model to Simple and back to Full,backed up the
> >> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
> >> > The
> >> > database recovery model is Full at the moment. I have noticed 2
> >> > oddities:
> >> >
> >> > 1. The Transaction Log appears to grow much slower than before(only 2 -
> >> > 5
> >> > MB a day,the database is being used as usual)
> >> >
> >> > 2. sp_spaceused and Enterprise Manager Console report strange values
> >> > for
> >> > database available space:
> >> >
> >> > db_name size unallocated_space
> >> > DB_LIVE 55812.06 MB -4257.63 MB
> >> >
> >> > reserved data index_size
> >> > unused
> >> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >> >
> >> >
> >> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> >> > or is there somethig else I have to do?
> >> >
> >> > Best Regards.
> >> >
> >> >
> >>
> >>
> >>
>
>

multiple fields in SELECT WHERE IN

Hi All

I'm in the process of trying to write a query (in access 2000) that copys data between tables, then updates related tables.

The problem:

One of the tables is linked on 2 fields, and I need to do a where not in query, so I can't use a join.

Is it possible to do something along the lines of:

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

Any help much appreciated

Mark MiddlemistYES YOU CAN

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

this works fine, but check that field1 and ref1 should have same datatype etc.,|||This won't be upgradable to sql server though.
You could

select * from table1
where not exists
(select * from table2 where table1.field1 = table2.ref1 and table1.field2 = table2.ref2)

or

select table1.*
from table1 left outer join table2
on table1.field1 = table2.ref1 and table1.field2 = table2.ref2
where table1.field1 is null

these will still bring back results if the fields allow null whereas the in clause may well not.
In all cases the results may not be correct for nullable fields.

Multiple Database or Full Backup

I can't see a way of backing up all databases at once, or
the ability to choose multiple databases to back up. I am
in the process of moving a bunch of databases from one
laptop to another and I would rather not do it one at a
time.
Ideas?
Thanks in advance!!can you take SQL off line, copy database files to new server and attach
databases?
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
> I can't see a way of backing up all databases at once, or
> the ability to choose multiple databases to back up. I am
> in the process of moving a bunch of databases from one
> laptop to another and I would rather not do it one at a
> time.
> Ideas?
> Thanks in advance!!|||I can! In fact I am doing just that as I write this. I
had hoped there would be a way of doing a full backup and
a full restore bring over security intact, etc., but I
hearing there isn't. Seems rather manual this way!
Thanks!!

>--Original Message--
>can you take SQL off line, copy database files to new
server and attach
>databases?
>"Top Cat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:10b9501c412ad$961d95e0$a601280a@.phx
.gbl...
or
am
>
>.
>|||Hi,
Using a automated script you can
1. Backup all the user databases remotely to Laptop
2. After the backup restore it in Laptop ( You might need to start all the
databases in sinle_user using Alter database statement)
I have a script whckh will Backup remotely, please add the restore portion
in the script
Prerequisites
1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am|||I had to restore from a bunch of backup files in a directory a while ago, so
I write a stored proc which loops
the files and do the restore for each file. You can have a similar loop to p
erform the backup as well. But you
really need some TSQL programming skill to write the loop to perform the bac
kups, and you probably want to
verify my code which does the restore:
http://www.karaszi.com/sqlserver/ut...all_in_file.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Top Cat" <anonymous@.discussions.microsoft.com> wrote in message
news:efbd01c412b1$8bc40fd0$a001280a@.phx.gbl...
> I can! In fact I am doing just that as I write this. I
> had hoped there would be a way of doing a full backup and
> a full restore bring over security intact, etc., but I
> hearing there isn't. Seems rather manual this way!
> Thanks!!
>
> server and attach
> message
> or
> am

Multiple Data Regions on Same Page

I have a batch accounting process that moves a payment (check)
from the original invoices where it was applied to a new set of
invoices.
I am trying to build a report that will list, for a given batch, the
original and new invoices.
Approach 1:
I tried using a single query and 2 report tables with a single group
in each. The table grouping correctly lists each invoice once but because
the query is running against 2 sets of invoices it is returning multiple
rows per invoice so the grouping total is overstated.
Is there a way to show in the group footer, just the total of the displayed
items in the report table and not of all values returned in the query?
Approach 2:
I also tried using 2 Datasets for the original/new invoices. This
allows me to have each query return the correct number of invoices.
But I cannot find a way to reference both data sets on the same page
either in separate report tables or within the same table.
I know I can probably do this with a subreport but I would like to
avoid deploying a second RDL.
Can this be done?
ThanksHi Mike,
I'm not sure if I've fully understood your question, but just in the case.
All aggregate functions have an scope associated with them. If none is
specified, the default is used.
So, if you drop the Amount field, i.e., in the TABLE footer, RS is assuming
you want to sum all table items.
Thus
Sum(Fields!Amount) is equivalent to Sum(Fields!Amount, "table1")
If you do the same in the GROUP footer
Sum(Fields!Amount) is equivalent to Sum(Fields!Amount, "table1_group1")
In consequence, what I've understood you're trying to do is the default
behavior.
I do not understand your statement about "reference both datasets in the
same page" because this is quite straight, too.
Could you elaborate this a bit more, please?
BTW, are you using Filters on the table data regions?
Best regards,
Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning
"Mike Harbinger" <MikeH@.Cybervillage.net> escribió en el mensaje
news:uzYTwof$FHA.3864@.TK2MSFTNGP12.phx.gbl...
>I have a batch accounting process that moves a payment (check)
> from the original invoices where it was applied to a new set of
> invoices.
> I am trying to build a report that will list, for a given batch, the
> original and new invoices.
> Approach 1:
> I tried using a single query and 2 report tables with a single group
> in each. The table grouping correctly lists each invoice once but because
> the query is running against 2 sets of invoices it is returning multiple
> rows per invoice so the grouping total is overstated.
> Is there a way to show in the group footer, just the total of the
> displayed
> items in the report table and not of all values returned in the query?
> Approach 2:
> I also tried using 2 Datasets for the original/new invoices. This
> allows me to have each query return the correct number of invoices.
> But I cannot find a way to reference both data sets on the same page
> either in separate report tables or within the same table.
> I know I can probably do this with a subreport but I would like to
> avoid deploying a second RDL.
> Can this be done?
> Thanks
>|||Hi Jordi
Let me try to make this clearer-
I have 3 tables that store the results of a transaction to
move payments from one set of invoices to another. Assume
the tables Batch, Trans, Invoice and a single transaction
with data as follows:
Batch- ID TransCount TotalAmt
1 4 30.00
Trans- ID BatchId InvoiceNbr CashAmt IsSource
1 1 100 10.00 1
2 1 101 20.00 1
3 1 201 10.00 0
4 1 215 20.00 0
****
The report query result looks like this:
From InvNbr From Amt ToInvNbr ToAmt
100 10.00 201 10.00
100 10.00 215 20.00
101 20.00 201 10.00
101 20.00 215 20.00
I have tried but I don't think there is a way to
eliminate the redundancy from the query (?)
****
The report would look like example below, with each batch on
a separate page. You would see the header area then a table
showing the 'from' invoices and one showing the 'To' invoices
----
Batch Nbr: 1
Trans count: 4
Moved from Invoice-
InvNbr Amt
100 10.00
101 20.00
Total: 30.00
Moved to Invoice-
InvNbr Amt
200 10.00
215 20.00
Total: 30.00
----
I can make the report tables correctly show a single
instance of the from/to invoices, but the problem is
the table totals show 60.00 instead of 30.00
1) I tried adding this to the footer of table1:
=sum(Fields!FromAmt.Value,"table1_group1")
but get this error:
"The value expression for the textbox textbox21' has a scope
parameter that is not valid for an aggregate function. The scope
parameter must be set to a string constant that is equal to either
the name of a containing group, the name of a containing data region,
or the name of a data set."
2) I also tried using 2 datasets, one for the 'FromInvoices'
and the other for the 'ToInvoices'
-I tried just using the report tables and adding each to the related
set; but I could not control the page breaking so that there would be
one page per batch
-I tried using the 'List' object but could only reference 1 data
set. Using the List, I also tried referencing the specific data set in
the group:
=sum(Fields!FromAmt.Value,"dsToInvoice")
but got out of scope errors
I know I am missing something obvious. Many thanks for your help!|||Hi Mike,
Your message is long so let me ask a question before I get hands on:
- I understand from the query results that you are self joining the trans
table by BatchID and filtering by IsSource. Are amounts always equal between
Source and Target transactions, so you can include this factor on the join
expression too?.
- Have you tried using the table name ONLY in the scope of the Sum?
- When you say the table totals, do you mean that each of both tables show
60 instead of 30? If positive, how do you "filter" at each table?
- Have you tried using a Rectangle to force the page break instead of a
List?
Best regards,
Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning
"Mike Harbinger" <MikeH@.Cybervillage.net> escribió en el mensaje
news:%23ulxrGr$FHA.504@.TK2MSFTNGP12.phx.gbl...
> Hi Jordi
> Let me try to make this clearer-
> I have 3 tables that store the results of a transaction to
> move payments from one set of invoices to another. Assume
> the tables Batch, Trans, Invoice and a single transaction
> with data as follows:
> Batch- ID TransCount TotalAmt
> 1 4 30.00
> Trans- ID BatchId InvoiceNbr CashAmt IsSource
> 1 1 100 10.00 1
> 2 1 101 20.00 1
> 3 1 201 10.00 0
> 4 1 215 20.00 0
> ****
> The report query result looks like this:
> From InvNbr From Amt ToInvNbr ToAmt
> 100 10.00 201 10.00
> 100 10.00 215 20.00
> 101 20.00 201 10.00
> 101 20.00 215 20.00
> I have tried but I don't think there is a way to
> eliminate the redundancy from the query (?)
> ****
> The report would look like example below, with each batch on
> a separate page. You would see the header area then a table
> showing the 'from' invoices and one showing the 'To' invoices
> ----
> Batch Nbr: 1
> Trans count: 4
> Moved from Invoice-
> InvNbr Amt
> 100 10.00
> 101 20.00
> Total: 30.00
> Moved to Invoice-
> InvNbr Amt
> 200 10.00
> 215 20.00
> Total: 30.00
> ----
> I can make the report tables correctly show a single
> instance of the from/to invoices, but the problem is
> the table totals show 60.00 instead of 30.00
> 1) I tried adding this to the footer of table1:
> =sum(Fields!FromAmt.Value,"table1_group1")
> but get this error:
> "The value expression for the textbox textbox21' has a scope
> parameter that is not valid for an aggregate function. The scope
> parameter must be set to a string constant that is equal to either
> the name of a containing group, the name of a containing data region,
> or the name of a data set."
> 2) I also tried using 2 datasets, one for the 'FromInvoices'
> and the other for the 'ToInvoices'
> -I tried just using the report tables and adding each to the related
> set; but I could not control the page breaking so that there would be
> one page per batch
> -I tried using the 'List' object but could only reference 1 data
> set. Using the List, I also tried referencing the specific data set in
> the group:
> =sum(Fields!FromAmt.Value,"dsToInvoice")
> but got out of scope errors
> I know I am missing something obvious. Many thanks for your help!
>|||Hi Jordi
> - I understand from the query results that you are self joining the trans
> table by BatchID and filtering by IsSource. Are amounts always equal
> between Source and Target transactions, so you can include this factor on
> the join expression too?
While the total 'From invocies' will always equal the totlal 'To invoices' ,
the
individual amounts may vary because there could be one 'From' invoice
and n 'To' invocies.
> - Have you tried using the table name ONLY in the scope of the Sum?
Like this? =Sum(Fields!FromAmt.Value,"tbFromInv")
Yes and it stills returns the DataSet total
> - When you say the table totals, do you mean that each of both tables show
> 60 instead of 30?
Yes
>If positive, how do you "filter" at each table?
I am not filtering and was not sure if/how I could do that. How could I
filter
in the 'From' group to omit the 'To' items without omitting the 'From'
items?
> - Have you tried using a Rectangle to force the page break instead of a
> List?
I did try this, but it lists all the batches at once in each of the tables,
then
does a page-break; insteadof listing one batch in both tables, page-break,
next
batch etc
Thanks again
Chris