We have a database that adds over 100,000 records per day. This goes back to 2002 and I only need historical data for 6 months. Presently we can only can delete 1000 row at a time. Is there a faster way of deleting. We seem to continuely run out of disk space. Urgent!!!!!!!!!!!If you just need a small set of data from a much larger set, you are better off insert those data you need into a new table, drop the old table, and rename the new table to the old table's name.|||After deciding to pull data out of the large table going back 6 months only. I created a statement that pulls each field out and will insert into a temp table. I get this error message.
'Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'INCOMING_TEMP' when IDENTITY_INSERT is set to OFF.'
I'm not sure what this means. Is it that I cannot insert into a ID field?|||Check 'SET IDENTITY_INSERT' in the BOL.|||? Check the bol???|||Books... On... Line... B.O.L.|||Due to the forum being down. I did some reserch on the error message and found a solution. I went into the management properties of the new temp_table and made it able to except inserts. The update is running as we speak. It's been running about 20 minutes and that's about right. The date range hold about 1,500,000 record per month. And that's why I'm clearing it out and holding only 6 months of backup historical data. Day 2 of new project.|||What your tranny logs and tempdb....
Are you doing this in batches with transactions?
A permanent table I think would have been better
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Monday, March 26, 2012
Historical Data Problem
We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!
Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000
>
>.
>
|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!
Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000
>
>.
>
|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>
Historical Data Problem
We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
--
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>> We have a database that adds over 100,000 records per
>> day. This goes back to 2002 and I only need historical
>> data for 6 months. Presently we can only can delete
1000
>> row at a time. Is there a faster way of deleting. We
>> seem to continuely run out of disk space.
>> Urgent!!!!!!!!!!!
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
--
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> > We have a database that adds over 100,000 records per
> > day. This goes back to 2002 and I only need historical
> > data for 6 months. Presently we can only can delete 1000
> > row at a time. Is there a faster way of deleting. We
> > seem to continuely run out of disk space.
> > Urgent!!!!!!!!!!!
>
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
--
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>> We have a database that adds over 100,000 records per
>> day. This goes back to 2002 and I only need historical
>> data for 6 months. Presently we can only can delete
1000
>> row at a time. Is there a faster way of deleting. We
>> seem to continuely run out of disk space.
>> Urgent!!!!!!!!!!!
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
--
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> > We have a database that adds over 100,000 records per
> > day. This goes back to 2002 and I only need historical
> > data for 6 months. Presently we can only can delete 1000
> > row at a time. Is there a faster way of deleting. We
> > seem to continuely run out of disk space.
> > Urgent!!!!!!!!!!!
>
Historical Data Problem
We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000[vbcol=seagreen]
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>sql
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000[vbcol=seagreen]
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>sql
Friday, March 23, 2012
High security Needs, Cheep Clients, Need Help
I have an existing server hosting several sites with associated back end
databases.
I have a new client who needs secure hosting solutions, including a secure
database, without the expense of a new server. I was thinking of the
following architecture. I know some ( most ) of this is not SQL related,
but bear with me please.
I am hosting web services on the server to provide the interface with the
external world. End-Use clients will have applications with certificates to
access these web services. I have two instances of SQL Server 2005 running,
MYSERVER\PUBLIC & MYSERVER\PRIVATE.
MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
info relevant to the new service which need not be secure.
MYSERVER\PRIVATE will contain all the new, to be secured, data.
MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
available only as 'localhost' from within the server, i.e. not be public to
the outside world. My web services should be able to access this data, and
the web services will only be accessible to these signed applications. Of
course, any relevant info would be encrypted.
Is this sufficient ?
Any input would be appreciated
Thanks
PhilYou have to make sure that you are using different service accounts for each
instance otherwise someone gaining elevated rights within one instance will
be able to get to the other instance. Additionally, you should not allow
any SQL logins to either instance. The web server should be configured to
connect to the instances using Windows credentials which forces anyone who
can not authenticate in Windows to not have access to either database
instance. The IIS server needs to be locked down to ensure that you don't
have any security holes. The service accounts for each instance should be
regular Windows accounts and NOT a member of local administrators or even
worse domain/enterprise administrators.
After accomplishing that, you pretty much have the front door closed. Now
you have to worry about all of the back end stuff. How are you going to
handle backups of the databases such that the data in your secure instance
remains secure even through the backup files? How are you going to control
administrator and developer access within your company to the secured
instance? How are you going to audit the operations within the secure
instance such that your customer on this secure instance can't create a
situation where they actually damaged data or caused the security to be
compromised, but are pointing the finger at you?
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
> Any input would be appreciated
> Thanks
> Phil
>|||"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
>
Can't tell if it's sufficient, but it should work.
Also don't even need to enable TCP/IP for the PRIVATE instance. Programs
running on the same box can use the shared memory provider, which only works
locally. Also make sure to put the private web apps in their own
application pool (or web site), and use a seperate set of Windows accounts
to run the secure web apps, connect to SQL and as the SQL Service account.
Segegate the data and use NTFS permissions to restrict read/write to the
database files, logs and backups.
David
databases.
I have a new client who needs secure hosting solutions, including a secure
database, without the expense of a new server. I was thinking of the
following architecture. I know some ( most ) of this is not SQL related,
but bear with me please.
I am hosting web services on the server to provide the interface with the
external world. End-Use clients will have applications with certificates to
access these web services. I have two instances of SQL Server 2005 running,
MYSERVER\PUBLIC & MYSERVER\PRIVATE.
MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
info relevant to the new service which need not be secure.
MYSERVER\PRIVATE will contain all the new, to be secured, data.
MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
available only as 'localhost' from within the server, i.e. not be public to
the outside world. My web services should be able to access this data, and
the web services will only be accessible to these signed applications. Of
course, any relevant info would be encrypted.
Is this sufficient ?
Any input would be appreciated
Thanks
PhilYou have to make sure that you are using different service accounts for each
instance otherwise someone gaining elevated rights within one instance will
be able to get to the other instance. Additionally, you should not allow
any SQL logins to either instance. The web server should be configured to
connect to the instances using Windows credentials which forces anyone who
can not authenticate in Windows to not have access to either database
instance. The IIS server needs to be locked down to ensure that you don't
have any security holes. The service accounts for each instance should be
regular Windows accounts and NOT a member of local administrators or even
worse domain/enterprise administrators.
After accomplishing that, you pretty much have the front door closed. Now
you have to worry about all of the back end stuff. How are you going to
handle backups of the databases such that the data in your secure instance
remains secure even through the backup files? How are you going to control
administrator and developer access within your company to the secured
instance? How are you going to audit the operations within the secure
instance such that your customer on this secure instance can't create a
situation where they actually damaged data or caused the security to be
compromised, but are pointing the finger at you?
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
> Any input would be appreciated
> Thanks
> Phil
>|||"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
>
Can't tell if it's sufficient, but it should work.
Also don't even need to enable TCP/IP for the PRIVATE instance. Programs
running on the same box can use the shared memory provider, which only works
locally. Also make sure to put the private web apps in their own
application pool (or web site), and use a seperate set of Windows accounts
to run the secure web apps, connect to SQL and as the SQL Service account.
Segegate the data and use NTFS permissions to restrict read/write to the
database files, logs and backups.
David
Wednesday, March 21, 2012
high memory usage
Hi, i have an sql server machine reported high memory usage (2.5 GB), and
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.
Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>
|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
sql
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.
Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>
|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
sql
high memory usage
Hi, i have an sql server machine reported high memory usage (2.5 GB), and
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.
Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>
|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.
Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
http://msdn.microsoft.com/library/de...onfig_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>
|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> http://msdn.microsoft.com/library/de...onfig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>
|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?[vbcol=seagreen]
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/de...onfig_9zfy.asp[vbcol=seagreen]
and
>
high memory usage
Hi, i have an sql server machine reported high memory usage (2.5 GB), and
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
g_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d... />
g_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/d...config_9zfy.asp
[vbcol=seagreen]
and[vbcol=seagreen]
>|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/d...config_9zfy.asp
[vbcol=seagreen]
and[vbcol=seagreen]
>
cause some workstations failed to save data back to sql server (use ADO
connection and client record set, connection failed).
I think reboot will fix the problem, but I want to know what cause problem,
how to check?
Please advice.Well, you can:
1. Buy more RAM. That's not really that much RAM.
2. Set a maximum memory size for SQL Server so it doesn't use too much RAM.
Here's an article that should help you:
g_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d... />
g_9zfy.asp
"js" <js@.someone.com> wrote in message
news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> Hi, i have an sql server machine reported high memory usage (2.5 GB), and
> cause some workstations failed to save data back to sql server (use ADO
> connection and client record set, connection failed).
> I think reboot will fix the problem, but I want to know what cause
problem,
> how to check?
> Please advice.
>|||is it an application can only use 4 GB memory?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||Can I see the dropped connection in sql log?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||What's will happen when memory reach max server memory(2 GB) in SQL server?
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Well, you can:
> 1. Buy more RAM. That's not really that much RAM.
> 2. Set a maximum memory size for SQL Server so it doesn't use too much
> RAM.
> Here's an article that should help you:
> fig_9zfy.asp" target="_blank">http://msdn.microsoft.com/library/d...>
fig_9zfy.asp
>
> "js" <js@.someone.com> wrote in message
> news:OwlAQcyUFHA.3076@.TK2MSFTNGP12.phx.gbl...
> problem,
>|||If you really have a problem with the SQL Server, then you should see some
kind of errors in either the event viewer logs or the SQL Server log. Is
this a dedicated SQL Server? How much total RAM do you have in the machine?
What else is running on it? What version do you have of Windows and SQL
Server?
"js" <js@.someone.com> wrote in message
news:O73EI6yUFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Can I see the dropped connection in sql log?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/d...config_9zfy.asp
[vbcol=seagreen]
and[vbcol=seagreen]
>|||If the server has enough RAM, then nothing will happen. If the SQL Server
actually needs more RAM, then you will see paging activity, a decrease in
performance, more recompiles of code, etc. The only way to fix it though if
you are on SQL Server standard edition and this is a dedicated SQL Server is
to either 1. tune to the server using profiler, looking at execution plans,
etc; or 2. Buy Enterprise Edition and more RAM.
You might want to check out http://www.sql-server-performance.com for
information on how to tune SQL Server, better manage memory, etc.
"js" <js@.someone.com> wrote in message
news:uP9NkQ0UFHA.3184@.TK2MSFTNGP15.phx.gbl...
> What's will happen when memory reach max server memory(2 GB) in SQL
server?
> "Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
> news:uqZZJlyUFHA.3044@.TK2MSFTNGP10.phx.gbl...
http://msdn.microsoft.com/library/d...config_9zfy.asp
[vbcol=seagreen]
and[vbcol=seagreen]
>
Friday, March 9, 2012
Hierarchy Equivalent of a Crossjoin
I want to pull back a cellset with two levels of a hierarchy on the same dimension. Using AdventureWorks as an example, let's say I want "Account Level 01" and "Account Level 02" both on the rows dimesion.
When I send MDX like this:
SELECT
{[Account].[Accounts].[Account Level 01].Members * [Account].[Accounts].[Account Level 02].Members}
DIMENSION PROPERTIES MEMBER_TYPE ON AXIS(0) FROM [Adventure Works]
I get this error message:
The Accounts hierarchy is used more than once in the Crossjoin function.
What MDX would I need in this situation? I've tried spying on the MDX generated by BIDs, but it uses so many temporary SETs that it's nearly impossible to follow.
Thanks for any help,
Terry
Terry,
If my understanding of what you are trying to do is correct, one possible solution would be to use the "Generate()" function:
Generate([Account].[Accounts].[Account Level 01].Members,
{[Account].[Accounts].CurrentMember,[Account].[Accounts].CurrentMember.Children})
HTH,
Steve
Subscribe to:
Posts (Atom)