Archiving very slow, help needed please

Hey guys,
The vendor gave us an archiving tool for our huge tables. We gave them a
production copy of our tables. They tested their tool against their own
server and told us that it takes 2 seconds to insert each record. I know
this is already bad. But today, I tested it against our own test server.
They also gave us instruction on what to do first before running the
archiving tool. Well anyways, after running the archiving tool, it took 20
seconds to insert 1 record. That's totally bad!
I would like to know if you will be able to help me guys identify the
problem by just looking at this links.
http://restricted.dyndns.org/executionplan.txt
http://restricted.dyndns.org/execplan1.gif
http://restricted.dyndns.org/execplan2.gif
http://restricted.dyndns.org/execplan3.gif
The INSERT statement that you will see there consumed 10 seconds of CPU and
30 seconds of Duration.
Are there any other statements that I can execute against my captured
profiler table that can us troubleshoot?
Any help will be greatly appreciated.
Thanks.
V1rt>> told us that it takes 2 seconds to insert each record
well....
Go back to the vendor (I assume you haven't paid them yet) and tell them
this isn't acceptable.
How many records do you have to deal with - even at 2 secs per record?
"Neil" wrote:
> Hey guys,
> The vendor gave us an archiving tool for our huge tables. We gave them a
> production copy of our tables. They tested their tool against their own
> server and told us that it takes 2 seconds to insert each record. I know
> this is already bad. But today, I tested it against our own test server.
> They also gave us instruction on what to do first before running the
> archiving tool. Well anyways, after running the archiving tool, it took 20
> seconds to insert 1 record. That's totally bad!
> I would like to know if you will be able to help me guys identify the
> problem by just looking at this links.
> http://restricted.dyndns.org/executionplan.txt
> http://restricted.dyndns.org/execplan1.gif
> http://restricted.dyndns.org/execplan2.gif
> http://restricted.dyndns.org/execplan3.gif
> The INSERT statement that you will see there consumed 10 seconds of CPU and
> 30 seconds of Duration.
> Are there any other statements that I can execute against my captured
> profiler table that can us troubleshoot?
> Any help will be greatly appreciated.
> Thanks.
> V1rt
>
>|||It looks like you are filtering the source table, Enclosure. Nothing wrong
with that. However, then you are joining this to the destination table.
Why?
An archive table usually doesn't contain the data in it yet. Are you
joining to make sure that you don't attempt to archive data that's already
been copied?
If so, there are better ways to write this. If you are INSERTing into a
table, not an update, there is rarely a need to join the destination to the
source. This will only slow you down because an INSERT will usually involve
updating the joined columns.
Try something more like this:
BEGIN TRANSACTION
INSERT INTO TableDestination
(Column1, Column2, ..., ColumnN)
SELECT Column1, Column2, ..., ColumnN
FROM TableSource
WHERE TableSource.ColumnX = ExpressionX
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM s
FROM TableDestination AS d
INNER JOIN TableSource AS s
ON d.Key1 = s.Key1
AND d.Key2 = s.Key2
AND...
AND d.KeyN = s.KeyN
IF @.@.ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Sincerely,
Anthony Thomas
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
> >> told us that it takes 2 seconds to insert each record
> well....
> Go back to the vendor (I assume you haven't paid them yet) and tell them
> this isn't acceptable.
> How many records do you have to deal with - even at 2 secs per record?
>
> "Neil" wrote:
> > Hey guys,
> >
> > The vendor gave us an archiving tool for our huge tables. We gave them a
> > production copy of our tables. They tested their tool against their own
> > server and told us that it takes 2 seconds to insert each record. I know
> > this is already bad. But today, I tested it against our own test server.
> > They also gave us instruction on what to do first before running the
> > archiving tool. Well anyways, after running the archiving tool, it took
20
> > seconds to insert 1 record. That's totally bad!
> >
> > I would like to know if you will be able to help me guys identify the
> > problem by just looking at this links.
> >
> > http://restricted.dyndns.org/executionplan.txt
> > http://restricted.dyndns.org/execplan1.gif
> > http://restricted.dyndns.org/execplan2.gif
> > http://restricted.dyndns.org/execplan3.gif
> >
> > The INSERT statement that you will see there consumed 10 seconds of CPU
and
> > 30 seconds of Duration.
> >
> > Are there any other statements that I can execute against my captured
> > profiler table that can us troubleshoot?
> >
> > Any help will be greatly appreciated.
> >
> > Thanks.
> >
> > V1rt
> >
> >
> >|||Hi Anthony,
Thanks for the awesome reply. Am I correct that the destination table that
is being joint is the tablefieldaudit? That's what I saw in the INSERT
statement.
Below is what I captured using Profiler. I saw tons of this running for 20+
seconds each. :(
INSERT INTO
TABLEFIELDAUDIT(TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,KEYVALUE,USERID,SUBKEY1,
SUBKEY2)
SELECT TABLENAME,FIELDNAME,FIELDVALUE,CHANGEDATE,E.RECORDID AS
KEYVALUE,USERID,SUBKEY1, SUBKEY2
FROM TABLEFIELDAUDIT
INNER JOIN ENCLOSURE E
ON TABLEFIELDAUDIT.SUBKEY1=E.BARCODE AND
TABLEFIELDAUDIT.SUBKEY2=E.ENCLOSURENUMBER
WHERE TABLENAME='ENCLOSURE' AND SUBKEY1='00010690'
Thanks again,
Neil
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OLmPhvtyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> It looks like you are filtering the source table, Enclosure. Nothing
> wrong
> with that. However, then you are joining this to the destination table.
> Why?
> An archive table usually doesn't contain the data in it yet. Are you
> joining to make sure that you don't attempt to archive data that's already
> been copied?
> If so, there are better ways to write this. If you are INSERTing into a
> table, not an update, there is rarely a need to join the destination to
> the
> source. This will only slow you down because an INSERT will usually
> involve
> updating the joined columns.
> Try something more like this:
> BEGIN TRANSACTION
> INSERT INTO TableDestination
> (Column1, Column2, ..., ColumnN)
> SELECT Column1, Column2, ..., ColumnN
> FROM TableSource
> WHERE TableSource.ColumnX = ExpressionX
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM s
> FROM TableDestination AS d
> INNER JOIN TableSource AS s
> ON d.Key1 = s.Key1
> AND d.Key2 = s.Key2
> AND...
> AND d.KeyN = s.KeyN
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> Sincerely,
>
> Anthony Thomas
>
> --
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:B085D060-8557-4C9E-B632-0C320BE1CE98@.microsoft.com...
>> >> told us that it takes 2 seconds to insert each record
>> well....
>> Go back to the vendor (I assume you haven't paid them yet) and tell them
>> this isn't acceptable.
>> How many records do you have to deal with - even at 2 secs per record?
>>
>> "Neil" wrote:
>> > Hey guys,
>> >
>> > The vendor gave us an archiving tool for our huge tables. We gave them
>> > a
>> > production copy of our tables. They tested their tool against their own
>> > server and told us that it takes 2 seconds to insert each record. I
>> > know
>> > this is already bad. But today, I tested it against our own test
>> > server.
>> > They also gave us instruction on what to do first before running the
>> > archiving tool. Well anyways, after running the archiving tool, it took
> 20
>> > seconds to insert 1 record. That's totally bad!
>> >
>> > I would like to know if you will be able to help me guys identify the
>> > problem by just looking at this links.
>> >
>> > http://restricted.dyndns.org/executionplan.txt
>> > http://restricted.dyndns.org/execplan1.gif
>> > http://restricted.dyndns.org/execplan2.gif
>> > http://restricted.dyndns.org/execplan3.gif
>> >
>> > The INSERT statement that you will see there consumed 10 seconds of CPU
> and
>> > 30 seconds of Duration.
>> >
>> > Are there any other statements that I can execute against my captured
>> > profiler table that can us troubleshoot?
>> >
>> > Any help will be greatly appreciated.
>> >
>> > Thanks.
>> >
>> > V1rt
>> >
>> >
>> >
>

Archiving is Not reducing DB Size

I created few jobs that would archive the production DB and delete the archived data...
but it looks like the DB size is not reducing!!! Some times it looks like the size has increased!!

I think this is because of the log file size has increaded by the DELETE operations...But what can I do for this?

Please Help!!Action\All Tasks\Shrink Database in EM
or
DBCC SHRINKDATABASE in QA

Archiving database for reporting

HI,
I have a database on sql server 2005 ent edition with about 300 000
new records every day. I must keep data in production database about
two months, but there are a lot of reporting activities which are
interrupting normal functioning of production server and there is also
requirement to query historical data(about 1 year) with reports. I
must add new data to the archive database every day. Additionally two
months old data in the production database can change (delete, update)
and that also must be reflected in archive database. So basically I
must insert new data, update changed date and delete deleted data from
production database into archive database but also keep about 1 year
of data in archive database. Then I can separately optimize archive
database for reporting. What is the best way to satisfy all that
requirements. I am thinking about SSIS.SSIS will work. You could also consider a small trigger on the main table
that will put the key values into a holding table when
inserts/updates/deletes occur so you can very quickly make the necessary
changes to the historical records when <2mth old data is modified.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||Hi
SSIS can be a good choice.
Also
>also keep about 1 year
> of data in archive database.
DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> I must keep data in production database about
> two months, b
Create a job and schedule it on day period
I must keep data in production database about
INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
dt<=DATEADD(month,-2,GETDATE())
>Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database.
Here, I'd suggest you to create a trigger or (take a look at OUTPUT clause)
and flag or whatever modified data
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||On Feb 7, 9:03=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> SSIS can be a good choice.
> Also
> >also keep about 1 year
> > of data in archive database.
> DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> > I must keep data in production database about
> > two months, b
> =A0 =A0 Create a job and schedule it on day period
> =A0I must keep data in production database about
> INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AN=D
> dt<=3DDATEADD(month,-2,GETDATE())
> >Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database.
> Here, I'd suggest =A0you to create a trigger or (take a look at OUTPUT cla=use)
> and flag or whatever modified data
> "OgnjenT" <Ognj...@.gmail.com> wrote in message
> news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
>
> > HI,
> > I have a database on sql server 2005 ent edition with about 300 000
> > new records every day. I must keep data in production database about
> > two months, but there are a lot of reporting activities which are
> > interrupting normal functioning of production server and there is also
> > requirement to query historical data(about 1 year) with reports. I
> > must add new data to the archive database every day. Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database. So basically I
> > must insert new data, update changed date and delete deleted data from
> > production database into archive database but also keep about 1 year
> > of data in archive database. Then I can separately optimize archive
> > database for reporting. What is the best way to satisfy all that
> > requirements. I am thinking about SSIS.- Hide quoted text -
> - Show quoted text -
Problem with triger is when I clean data older then two month it will
delete data in archive database too. Also I must then put the triger
on the other tables because of the referential integrity in the
archive database.
>INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AND=
>dt<=3DDATEADD(month,-2,GETDATE())
I can't do that because I must have to synchronize production and
archive database every day because every night I mast have a dozen of
reports.
I decided to have archive database because of different way of queries
for normal processing and for reporting and I don't wont reporting
have so mutch influence on normaln work. So it is not only archiving
data but creating separate database for reporting so I can put some
more indexes, indexed views and in the same time my insert and updates
will still be fast in the production database. Also production server
doesn't have to hold data older than two months so it is better to
clean it so my queries woold be faster.
Is it maybe ok to delete data older than two months from archive
database every night and insert that data from production database. It
seems to me that it will be faster than check every row in production
database and then update archive database if row is updated, delete if
deleted. The esiest of course is to insert new rows.
Maybe before that bulk delete and insert it wood be smart to drop all
indexes, indexed views and constraint and recreate it after. But I
must do all that for about 15 minutes.

Archiving Data

I was exploring ways of archiving old data in our production environment
whilst maintaining it's availability... ...the current data takes 90% of the
queries, but grows very large and puts pressure on the disks... ...I wanted
to explore using a "sliding window" on a partitioned view across a federated
database and have two initial questions I can't find an answer to...
1. Does the data move between partitions when partitions are located on
different federated servers?
2. Assuming a good horizontal partition is chosen, is an automated sliding
window practical in a production environment?
Thanks in advance BenUKPersonally, I'd revise your strategy to use Partioned Tables in SQL2K5.
Awesome feature.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:843C5511-A7D1-4323-83A9-7D7B17BA7639@.microsoft.com...
> I was exploring ways of archiving old data in our production environment
> whilst maintaining it's availability... ...the current data takes 90% of
the
> queries, but grows very large and puts pressure on the disks... ...I
wanted
> to explore using a "sliding window" on a partitioned view across a
federated
> database and have two initial questions I can't find an answer to...
> 1. Does the data move between partitions when partitions are located on
> different federated servers?
> 2. Assuming a good horizontal partition is chosen, is an automated sliding
> window practical in a production environment?
> Thanks in advance BenUK

Applying SP4

Hi,
I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
This Test Server will be made the new Production Server. I have to apply SP4
to the Test server. Do I -
First apply SP4 to the test server and then Backup system and user databases
on Production Server and restore them on the Test Server or
Do a Backup of system and user databases on Production Server, restore them
on test server and then apply SP4 on the test server?
Will there be a issue after restore on the test server as it has SP3 and the
Production Server has SP2?
Is Backup and restore a better way here in comparison to detach and attach
database?
ThanksThat all depends, what is your goal?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply
> SP4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user
> databases
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore
> them
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and
> the
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
>
>|||Thanks for the response. My goal is to promote the Test Server to a Full
Production Server running SQL Server 2000 (SP4)
"Aaron Bertrand [SQL Server MVP]" wrote:
> That all depends, what is your goal?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
> > Hi,
> >
> > I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> > and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> >
> > This Test Server will be made the new Production Server. I have to apply
> > SP4
> > to the Test server. Do I -
> >
> > First apply SP4 to the test server and then Backup system and user
> > databases
> > on Production Server and restore them on the Test Server or
> > Do a Backup of system and user databases on Production Server, restore
> > them
> > on test server and then apply SP4 on the test server?
> > Will there be a issue after restore on the test server as it has SP3 and
> > the
> > Production Server has SP2?
> >
> > Is Backup and restore a better way here in comparison to detach and attach
> > database?
> >
> > Thanks
> >
> >
> >
> >
>
>|||Hi
"sharman" wrote:
> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply SP4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user databases
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore them
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and the
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
Although I don't think you will have a any major problems with the migration
from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the test
server and test you application, then backup the databases, upgrade the
production server to the same level before backing up the database again and
migrating them.
John|||> Thanks for the response. My goal is to promote the Test Server to a Full
> Production Server running SQL Server 2000 (SP4)
With the exact same databases on it?
I would upgrade production to SP4, take the backups, then apply SP4 to test,
and then restore the backups.
Remember to get the 2040 hotfix (and maybe even the 2187 cumulative hotfix)
if you are using AWE.|||This would help me understand Service Packs. If I apply SP4 to Test server
and then migrate databases from Production Server (running SP2) (both system
and user databases) to the Test Server,would it undo the changes done by SP4
on Test Server?
If I migrate system and user databases from Production to Test Server and
then apply SP4 to Test Server and test it. After successful testing I just
migrate the user databases to the test server, would it undo the changes done
by SP4 on Test Server?
"John Bell" wrote:
> Hi
> "sharman" wrote:
> > Hi,
> >
> > I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> > and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> >
> > This Test Server will be made the new Production Server. I have to apply SP4
> > to the Test server. Do I -
> >
> > First apply SP4 to the test server and then Backup system and user databases
> > on Production Server and restore them on the Test Server or
> > Do a Backup of system and user databases on Production Server, restore them
> > on test server and then apply SP4 on the test server?
> > Will there be a issue after restore on the test server as it has SP3 and the
> > Production Server has SP2?
> >
> > Is Backup and restore a better way here in comparison to detach and attach
> > database?
> >
> > Thanks
> Although I don't think you will have a any major problems with the migration
> from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the test
> server and test you application, then backup the databases, upgrade the
> production server to the same level before backing up the database again and
> migrating them.
> John|||Hi
"sharman" wrote:
> This would help me understand Service Packs. If I apply SP4 to Test server
> and then migrate databases from Production Server (running SP2) (both system
> and user databases) to the Test Server,would it undo the changes done by SP4
> on Test Server?
> If I migrate system and user databases from Production to Test Server and
> then apply SP4 to Test Server and test it. After successful testing I just
> migrate the user databases to the test server, would it undo the changes done
> by SP4 on Test Server?
>
The exact changes to a database made by a release or hotfix is not usually
made available to people outside MS, although I guess you could raise an
incident to ask. Even if the database files are compatible, if you tested on
one version the behaviour of your application will not necessarily be the
same when run on a different version, therefore invalidating your tests. It
is therefore best to upgrade in such a way as everything is in step.
HTH
John

Applying SP4

Hi,
I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
This Test Server will be made the new Production Server. I have to apply SP4
to the Test server. Do I -
First apply SP4 to the test server and then Backup system and user databases
on Production Server and restore them on the Test Server or
Do a Backup of system and user databases on Production Server, restore them
on test server and then apply SP4 on the test server?
Will there be a issue after restore on the test server as it has SP3 and the
Production Server has SP2?
Is Backup and restore a better way here in comparison to detach and attach
database?
Thanks
That all depends, what is your goal?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply
> SP4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user
> databases
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore
> them
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and
> the
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
>
>
|||Thanks for the response. My goal is to promote the Test Server to a Full
Production Server running SQL Server 2000 (SP4)
"Aaron Bertrand [SQL Server MVP]" wrote:

> That all depends, what is your goal?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
>
>
|||Hi
"sharman" wrote:

> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply SP4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user databases
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore them
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and the
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
Although I don't think you will have a any major problems with the migration
from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the test
server and test you application, then backup the databases, upgrade the
production server to the same level before backing up the database again and
migrating them.
John
|||> Thanks for the response. My goal is to promote the Test Server to a Full
> Production Server running SQL Server 2000 (SP4)
With the exact same databases on it?
I would upgrade production to SP4, take the backups, then apply SP4 to test,
and then restore the backups.
Remember to get the 2040 hotfix (and maybe even the 2187 cumulative hotfix)
if you are using AWE.
|||This would help me understand Service Packs. If I apply SP4 to Test server
and then migrate databases from Production Server (running SP2) (both system
and user databases) to the Test Server,would it undo the changes done by SP4
on Test Server?
If I migrate system and user databases from Production to Test Server and
then apply SP4 to Test Server and test it. After successful testing I just
migrate the user databases to the test server, would it undo the changes done
by SP4 on Test Server?
"John Bell" wrote:

> Hi
> "sharman" wrote:
>
> Although I don't think you will have a any major problems with the migration
> from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the test
> server and test you application, then backup the databases, upgrade the
> production server to the same level before backing up the database again and
> migrating them.
> John
|||Hi
"sharman" wrote:

> This would help me understand Service Packs. If I apply SP4 to Test server
> and then migrate databases from Production Server (running SP2) (both system
> and user databases) to the Test Server,would it undo the changes done by SP4
> on Test Server?
> If I migrate system and user databases from Production to Test Server and
> then apply SP4 to Test Server and test it. After successful testing I just
> migrate the user databases to the test server, would it undo the changes done
> by SP4 on Test Server?
>
The exact changes to a database made by a release or hotfix is not usually
made available to people outside MS, although I guess you could raise an
incident to ask. Even if the database files are compatible, if you tested on
one version the behaviour of your application will not necessarily be the
same when run on a different version, therefore invalidating your tests. It
is therefore best to upgrade in such a way as everything is in step.
HTH
John

Applying SP4

Hi,
I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
This Test Server will be made the new Production Server. I have to apply SP4
to the Test server. Do I -
First apply SP4 to the test server and then Backup system and user databases
on Production Server and restore them on the Test Server or
Do a Backup of system and user databases on Production Server, restore them
on test server and then apply SP4 on the test server?
Will there be a issue after restore on the test server as it has SP3 and the
Production Server has SP2?
Is Backup and restore a better way here in comparison to detach and attach
database?
ThanksThat all depends, what is your goal?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply
> SP4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user
> databases
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore
> them
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and
> the
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
>
>|||Thanks for the response. My goal is to promote the Test Server to a Full
Production Server running SQL Server 2000 (SP4)
"Aaron Bertrand [SQL Server MVP]" wrote:

> That all depends, what is your goal?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:11FCCE2A-19D0-4B26-8B71-107FB518A1AA@.microsoft.com...
>
>|||Hi
"sharman" wrote:

> Hi,
> I have a Production Server - Windows 2000 Server, SQL Server 2000 (SP2)
> and a Test server - Windows 2003 Server, SQL Server 2000 (SP3).
> This Test Server will be made the new Production Server. I have to apply S
P4
> to the Test server. Do I -
> First apply SP4 to the test server and then Backup system and user databas
es
> on Production Server and restore them on the Test Server or
> Do a Backup of system and user databases on Production Server, restore the
m
> on test server and then apply SP4 on the test server?
> Will there be a issue after restore on the test server as it has SP3 and t
he
> Production Server has SP2?
> Is Backup and restore a better way here in comparison to detach and attach
> database?
> Thanks
Although I don't think you will have a any major problems with the migration
from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the test
server and test you application, then backup the databases, upgrade the
production server to the same level before backing up the database again and
migrating them.
John|||> Thanks for the response. My goal is to promote the Test Server to a Full
> Production Server running SQL Server 2000 (SP4)
With the exact same databases on it?
I would upgrade production to SP4, take the backups, then apply SP4 to test,
and then restore the backups.
Remember to get the 2040 hotfix (and maybe even the 2187 cumulative hotfix)
if you are using AWE.|||This would help me understand Service Packs. If I apply SP4 to Test server
and then migrate databases from Production Server (running SP2) (both system
and user databases) to the Test Server,would it undo the changes done by SP4
on Test Server?
If I migrate system and user databases from Production to Test Server and
then apply SP4 to Test Server and test it. After successful testing I just
migrate the user databases to the test server, would it undo the changes don
e
by SP4 on Test Server?
"John Bell" wrote:

> Hi
> "sharman" wrote:
>
> Although I don't think you will have a any major problems with the migrati
on
> from SP2 to SP4 (plus hotfixes) the safest way would be to upgrade the tes
t
> server and test you application, then backup the databases, upgrade the
> production server to the same level before backing up the database again a
nd
> migrating them.
> John|||Hi
"sharman" wrote:

> This would help me understand Service Packs. If I apply SP4 to Test server
> and then migrate databases from Production Server (running SP2) (both syst
em
> and user databases) to the Test Server,would it undo the changes done by S
P4
> on Test Server?
> If I migrate system and user databases from Production to Test Server and
> then apply SP4 to Test Server and test it. After successful testing I just
> migrate the user databases to the test server, would it undo the changes d
one
> by SP4 on Test Server?
>
The exact changes to a database made by a release or hotfix is not usually
made available to people outside MS, although I guess you could raise an
incident to ask. Even if the database files are compatible, if you tested o
n
one version the behaviour of your application will not necessarily be the
same when run on a different version, therefore invalidating your tests. It
is therefore best to upgrade in such a way as everything is in step.
HTH
John

Applying SP1 on Sql Server Express production environment

How do you apply Service Pack 1 to Sql Server Express 2005?Hi,

this can be directly read from the setup instructions:

http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Applying service packs

Hi,
I have a production server running SQL 2000 SP2 on Windows 2000 server. I
have to migrate all the databases on this production server to a new server
running SQL Server 2000 SP4 on Windows 2003 Server.
Are there any known issues? How should I proceed ? Apply SP4 on the current
Production Server first and then migrate? Any insight will be greatly
appreciated. Thanks.
I would recommend applying SP4 to the current server before the migration.
This way all your system databases will match between systems.
If you are going to copy the system databases as well (which I would
recommend if you can keep all the directory paths the same) you will need to
do a sp_droplogin and sp_addlogin to change the server's name to the new
server name.
There should be no issues with this migration.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"sharman" wrote:

> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.
|||Hello,
Best option:-
1. Install SP4 to current production system
2. Copy the databases to new server.
3. You can also copy the system databases. This will ahelp you not to create
any jobs, DTS, Logins...
Thanks
Hari
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F892EF13-069B-4E8A-9DB7-0EF942C90450@.microsoft.com...
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new
> server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the
> current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.

Applying service packs

Hi,
I have a production server running SQL 2000 SP2 on Windows 2000 server. I
have to migrate all the databases on this production server to a new server
running SQL Server 2000 SP4 on Windows 2003 Server.
Are there any known issues? How should I proceed ? Apply SP4 on the current
Production Server first and then migrate? Any insight will be greatly
appreciated. Thanks.I would recommend applying SP4 to the current server before the migration.
This way all your system databases will match between systems.
If you are going to copy the system databases as well (which I would
recommend if you can keep all the directory paths the same) you will need to
do a sp_droplogin and sp_addlogin to change the server's name to the new
server name.
There should be no issues with this migration.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"sharman" wrote:

> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new serve
r
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the curren
t
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.|||Hello,
Best option:-
1. Install SP4 to current production system
2. Copy the databases to new server.
3. You can also copy the system databases. This will ahelp you not to create
any jobs, DTS, Logins...
Thanks
Hari
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F892EF13-069B-4E8A-9DB7-0EF942C90450@.microsoft.com...
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new
> server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the
> current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.

Applying service packs

Hi,
I have a production server running SQL 2000 SP2 on Windows 2000 server. I
have to migrate all the databases on this production server to a new server
running SQL Server 2000 SP4 on Windows 2003 Server.
Are there any known issues? How should I proceed ? Apply SP4 on the current
Production Server first and then migrate? Any insight will be greatly
appreciated. Thanks.I would recommend applying SP4 to the current server before the migration.
This way all your system databases will match between systems.
If you are going to copy the system databases as well (which I would
recommend if you can keep all the directory paths the same) you will need to
do a sp_droplogin and sp_addlogin to change the server's name to the new
server name.
There should be no issues with this migration.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"sharman" wrote:
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.|||Hello,
Best option:-
1. Install SP4 to current production system
2. Copy the databases to new server.
3. You can also copy the system databases. This will ahelp you not to create
any jobs, DTS, Logins...
Thanks
Hari
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F892EF13-069B-4E8A-9DB7-0EF942C90450@.microsoft.com...
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new
> server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the
> current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.

appling DTS custom transformation to replication problem (Urgent)

Hi all,
In my production environment, there is a source database that replicates
data to another server (using transactional replication).
Because of some upgrade needs, I need to change the subscriber database to
store Unicode. As a result, I want to customize the replication such that our
Big5 data in source database can be converted to UNICODE automatically during
replication.
Firstly, I want to know whether applying DTS custom transformation in
replication can solve my problem. Is there any better methods?
In addition, how can I configure DTS custom transforamtion.?
I have tried using Define Transformation of Published Data Wizard but error
occurs at the step of adding task of creating the DTS package. The error is
"SQL Server Enterprise Manager cannot complete this operation".
I also try to use DTS designer to create the package. But I cannot select
the package created bt DTS designer when I create a transformable
subscription.
Please help me
Thanks
"kazom" wrote:

> Hi all,
> In my production environment, there is a source database that replicates
> data to another server (using transactional replication).
> Because of some upgrade needs, I need to change the subscriber database to
> store Unicode. As a result, I want to customize the replication such that our
> Big5 data in source database can be converted to UNICODE automatically during
> replication.
> Firstly, I want to know whether applying DTS custom transformation in
> replication can solve my problem. Is there any better methods?
> In addition, how can I configure DTS custom transforamtion.?
> I have tried using Define Transformation of Published Data Wizard but error
> occurs at the step of adding task of creating the DTS package. The error is
> "SQL Server Enterprise Manager cannot complete this operation".
> I also try to use DTS designer to create the package. But I cannot select
> the package created bt DTS designer when I create a transformable
> subscription.
> Please help me
> Thanks
>
Hi all,
Let me make the question be more specific.
I just follow this document to configure the DTS custom transformation
replication.
But error occurs after step 11 of the part Define a DTS Package for the
Transformation
The error is:
SQL Server Enterprise Manager could not complete this operation
Both the publisher and subscripter is at the same server but different
database
The server is SQL Server 2000 service pack 4
Please help me. I really need your professional help
This is very urgent
Thanks

Application Databases storing location

We have SQL Database in Cluster volume F:
Cluster volume is mounted in the primary server A1.
We have production and test databases of the applications
stored in the Cluster Storage F:.
Is it possible to store all the test databases of the
applications in the Clocal harddisk) of the primary
server?
If there is a solution pls advice.
You can copy data to anywhere you would like. To use the data with
clustering, you have to have the ability for that data to be reached by any
clustered node, even after a failure. If you keep anything local, and that
machine
fails, other nodes will not be able to access it.
Cheers,
Rod
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:743201c430db$f3018270$a401280a@.phx.gbl...
> We have SQL Database in Cluster volume F:
> Cluster volume is mounted in the primary server A1.
> We have production and test databases of the applications
> stored in the Cluster Storage F:.
> Is it possible to store all the test databases of the
> applications in the Clocal harddisk) of the primary
> server?
> If there is a solution pls advice.
>
|||A clustered SQL server must store data on a drive that is in the same
resource group. The SQL server must be dependant on the physical disk(s)
that clustered data is stored on.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:743201c430db$f3018270$a401280a@.phx.gbl...
> We have SQL Database in Cluster volume F:
> Cluster volume is mounted in the primary server A1.
> We have production and test databases of the applications
> stored in the Cluster Storage F:.
> Is it possible to store all the test databases of the
> applications in the Clocal harddisk) of the primary
> server?
> If there is a solution pls advice.
>
|||Can the Clocal harddisk) of the primary server be
included as part of the same resource group as the
clustered SQL server?

>--Original Message--
>A clustered SQL server must store data on a drive that is
in the same
>resource group. The SQL server must be dependant on the
physical disk(s)
>that clustered data is stored on.
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Anonymous" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:743201c430db$f3018270$a401280a@.phx.gbl...
applications
>
>.
>
|||Fine. We do not want the test data in the cluster mode and
need not be available in case of failure.
Is it possible to have SQL server in Cluster and
applications data in the local?

>--Original Message--
>You can copy data to anywhere you would like. To use the
data with
>clustering, you have to have the ability for that data to
be reached by any
>clustered node, even after a failure. If you keep
anything local, and that
>machine
>fails, other nodes will not be able to access it.
>Cheers,
>Rod
>"Anonymous" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:743201c430db$f3018270$a401280a@.phx.gbl...
applications
>
>.
>
|||No. C: is not a shared cluster resource.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:886301c4326d$56ac1a00$a301280a@.phx.gbl...[vbcol=seagreen]
> Can the Clocal harddisk) of the primary server be
> included as part of the same resource group as the
> clustered SQL server?
> in the same
> physical disk(s)
> in message
> applications
|||No, sorry you can't do that. All SQL data needs to be on the shared storage.
Cheers,
Rod
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:886b01c4326e$406dde30$a301280a@.phx.gbl...[vbcol=seagreen]
> Fine. We do not want the test data in the cluster mode and
> need not be available in case of failure.
> Is it possible to have SQL server in Cluster and
> applications data in the local?
> data with
> be reached by any
> anything local, and that
> in message
> applications

Application architecture

Hi all,
I am developing a production optimization application. The application is a
distributed application with several physical machines doing things like
measurements, calculations and control and they all communicate through
TCP/IP using .Net remoting.
A requirement for the application is that it should be able to process 100
items / minute.
I am interested in using SQL server to store data about my produced items
and also about the system itself. For instance storing performance counters
from the different machines.
The logging to the database is not a critical feature of the system and it's
important that the writing to the database does not harm the performance of
other more critical functions in the system.
To handle the communication with the database I have a few altenative
architectures that I would like you to comment on (or please feel free to
suggest any other solutions)
1. Use a single SQL server that all machines on the network communicate
directly to. Should I use asynchrounous write to limit the impact on the
writing system?
2. Use separate instances on each machine (SQL Express) and then copy or
move the data when the system is idle.
3. Use a middle layer that resides on a separate machine that handles all
the communication to the SQL server. Essentially dump all messages on this
resource and write them from here when there is an opportunity. All problems
only affect this machine.
I would Like to use alternative 1 but I'm concerned about the availiabilty.
I have had problems before with another application that writes to an SQL
server over an office network where the SQL server does not respond (or
responds slowly) and cause performance degrading in my application.
Please give me some advice on this matter
Best regards
Henrik
SQL Server will cache any new data or data changes and handle the disk
writing on its own time.
If you are inserting new data, then the operation will be very fast.
(Sometimes, when updating existing data, slowness can be encountered because
other users may be locking and/or blocking the data. I have production
systems that are capable of accepting new data inserts at a rate of
thousands per second -so your requirement of hundreds per minute will be no
issue for SQL Server.
The main issue affecting the speed you desire is that the location of the
Transaction Log file (*.ldf) should be on a dedicated drive or array. The
log file writes all changes, serialized, and any other activity on the drive
will move the drive heads, and then they have to return to the last location
to write the next piece of data. If there are no other activities on the
drive, then the drive heads are 'always' in the right location, and the
speed is optimal.
Choice #1, and do not bother trying async writes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Henrik" <henrik_@.community.nospam> wrote in message
news:7188099B-03F9-4A01-9C9D-5BD41CD1234E@.microsoft.com...
> Hi all,
> I am developing a production optimization application. The application is
> a
> distributed application with several physical machines doing things like
> measurements, calculations and control and they all communicate through
> TCP/IP using .Net remoting.
> A requirement for the application is that it should be able to process 100
> items / minute.
> I am interested in using SQL server to store data about my produced items
> and also about the system itself. For instance storing performance
> counters
> from the different machines.
> The logging to the database is not a critical feature of the system and
> it's
> important that the writing to the database does not harm the performance
> of
> other more critical functions in the system.
> To handle the communication with the database I have a few altenative
> architectures that I would like you to comment on (or please feel free to
> suggest any other solutions)
> 1. Use a single SQL server that all machines on the network communicate
> directly to. Should I use asynchrounous write to limit the impact on the
> writing system?
> 2. Use separate instances on each machine (SQL Express) and then copy or
> move the data when the system is idle.
> 3. Use a middle layer that resides on a separate machine that handles all
> the communication to the SQL server. Essentially dump all messages on this
> resource and write them from here when there is an opportunity. All
> problems
> only affect this machine.
> I would Like to use alternative 1 but I'm concerned about the
> availiabilty.
> I have had problems before with another application that writes to an SQL
> server over an office network where the SQL server does not respond (or
> responds slowly) and cause performance degrading in my application.
> Please give me some advice on this matter
> Best regards
> Henrik
>
>
|||Hello Henrik,
I'd also vote for alternative 1 since it is most simple and efficient. As
Arnie said, SQL itself cache data writing. If there is no blocking issues
on the server, its preformance shall be very good. You may want to adjust
your application/Stored procedures/queries carefully to avoid/alleviate
blocking issues so that SQL Server can keep quick response time, especailly
when multiple clients are inserting/updating same tables at same time to
SQL Server.
Also, performance tuning of SQL Server itself is important for better
response time.
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
<http://support.microsoft.com/?id=224453>
271509 INF: How to Monitor SQL Server 2000 Blocking
<http://support.microsoft.com/?id=271509>
Q224587 INF: Troubleshooting Application Performance with SQL Server
<http://support.microsoft.com/support/kb/articles/q224/5/87.asp>
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Application architecture

Hi all,
I am developing a production optimization application. The application is a
distributed application with several physical machines doing things like
measurements, calculations and control and they all communicate through
TCP/IP using .Net remoting.
A requirement for the application is that it should be able to process 100
items / minute.
I am interested in using SQL server to store data about my produced items
and also about the system itself. For instance storing performance counters
from the different machines.
The logging to the database is not a critical feature of the system and it's
important that the writing to the database does not harm the performance of
other more critical functions in the system.
To handle the communication with the database I have a few altenative
architectures that I would like you to comment on (or please feel free to
suggest any other solutions)
1. Use a single SQL server that all machines on the network communicate
directly to. Should I use asynchrounous write to limit the impact on the
writing system?
2. Use separate instances on each machine (SQL Express) and then copy or
move the data when the system is idle.
3. Use a middle layer that resides on a separate machine that handles all
the communication to the SQL server. Essentially dump all messages on this
resource and write them from here when there is an opportunity. All problems
only affect this machine.
I would Like to use alternative 1 but I'm concerned about the availiabilty.
I have had problems before with another application that writes to an SQL
server over an office network where the SQL server does not respond (or
responds slowly) and cause performance degrading in my application.
Please give me some advice on this matter
Best regards
HenrikSQL Server will cache any new data or data changes and handle the disk
writing on its own time.
If you are inserting new data, then the operation will be very fast.
(Sometimes, when updating existing data, slowness can be encountered because
other users may be locking and/or blocking the data. I have production
systems that are capable of accepting new data inserts at a rate of
thousands per second -so your requirement of hundreds per minute will be no
issue for SQL Server.
The main issue affecting the speed you desire is that the location of the
Transaction Log file (*.ldf) should be on a dedicated drive or array. The
log file writes all changes, serialized, and any other activity on the drive
will move the drive heads, and then they have to return to the last location
to write the next piece of data. If there are no other activities on the
drive, then the drive heads are 'always' in the right location, and the
speed is optimal.
Choice #1, and do not bother trying async writes.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Henrik" <henrik_@.community.nospam> wrote in message
news:7188099B-03F9-4A01-9C9D-5BD41CD1234E@.microsoft.com...
> Hi all,
> I am developing a production optimization application. The application is
> a
> distributed application with several physical machines doing things like
> measurements, calculations and control and they all communicate through
> TCP/IP using .Net remoting.
> A requirement for the application is that it should be able to process 100
> items / minute.
> I am interested in using SQL server to store data about my produced items
> and also about the system itself. For instance storing performance
> counters
> from the different machines.
> The logging to the database is not a critical feature of the system and
> it's
> important that the writing to the database does not harm the performance
> of
> other more critical functions in the system.
> To handle the communication with the database I have a few altenative
> architectures that I would like you to comment on (or please feel free to
> suggest any other solutions)
> 1. Use a single SQL server that all machines on the network communicate
> directly to. Should I use asynchrounous write to limit the impact on the
> writing system?
> 2. Use separate instances on each machine (SQL Express) and then copy or
> move the data when the system is idle.
> 3. Use a middle layer that resides on a separate machine that handles all
> the communication to the SQL server. Essentially dump all messages on this
> resource and write them from here when there is an opportunity. All
> problems
> only affect this machine.
> I would Like to use alternative 1 but I'm concerned about the
> availiabilty.
> I have had problems before with another application that writes to an SQL
> server over an office network where the SQL server does not respond (or
> responds slowly) and cause performance degrading in my application.
> Please give me some advice on this matter
> Best regards
> Henrik
>
>|||Hello Henrik,
I'd also vote for alternative 1 since it is most simple and efficient. As
Arnie said, SQL itself cache data writing. If there is no blocking issues
on the server, its preformance shall be very good. You may want to adjust
your application/Stored procedures/queries carefully to avoid/alleviate
blocking issues so that SQL Server can keep quick response time, especailly
when multiple clients are inserting/updating same tables at same time to
SQL Server.
Also, performance tuning of SQL Server itself is important for better
response time.
224453 INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems
<http://support.microsoft.com/?id=224453>
271509 INF: How to Monitor SQL Server 2000 Blocking
<http://support.microsoft.com/?id=271509>
Q224587 INF: Troubleshooting Application Performance with SQL Server
<http://support.microsoft.com/suppor...s/q224/5/87.asp>
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pr...fprb.mspx#EYBAG
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

AppDomain unloads due to memory pressure.

We deployed a new SQL Server 2005 production server 6 weeks ago. This
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.
On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/78717b6a0452847a/ad371baa9fa12a4d?hl=en&lnk=st&q=AppDomain+is+marke d+for+unload+due+to+memory+pressure.#ad371baa9fa12 a4d[/url]
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.
|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> [...]
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.microsoft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.com/group/microsoft.public.sqlserver.server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad

AppDomain unloads due to memory pressure.

We deployed a new SQL Server 2005 production server 6 weeks ago. This
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.d
bo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[
;runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
http://groups.google.com/group/micr...d371baa9fa12a4d
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
>
> [...]
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.micro
soft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.co
m/gr...server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad

AppDomain unloads due to memory pressure.

We deployed a new SQL Server 2005 production server 6 weeks ago. This
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/78717b6a0452847a/ad371baa9fa12a4d?hl=en&lnk=st&q=AppDomain+is+marked+for+unload+due+to+memory+pressure.#ad371baa9fa12a4d
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> > We deployed a new SQL Server 2005 production server 6 weeks ago. This
> > server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> > servermemoryand /3GB switch.
> [...]
> > Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.microsoft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.com/group/microsoft.public.sqlserver.server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad