Determining Security: client

Showing posts with label client. Show all posts

Thursday, March 29, 2012

determine the SP level of a client install?

Hello,
How can I tell what service pack has been installed on a local (non-server) client installation of SQL Server 2000?
I'd appreciate any feedback
Thanks,
John
Have you looked at Help | About within Query Analyzer?
--=20
Keith
"John P" <John P@.discussions.microsoft.com> wrote in message =
news:6A79F645-CBF8-4123-8FC8-CC0A8952A7F6@.microsoft.com...
> Hello,
>=20
> How can I tell what service pack has been installed on a local =
(non-server) client installation of SQL Server 2000?
>=20
> I'd appreciate any feedback
>=20
> Thanks,
>=20
> John
|||ahh...why didn't I think of that?! I tried that in Enterprise Manager but noticed it was MMC not SQL so I gave up!
Thanks Keith!
"Keith Kratochvil" wrote:

> Have you looked at Help | About within Query Analyzer?
> --
> Keith
>
> "John P" <John P@.discussions.microsoft.com> wrote in message news:6A79F645-CBF8-4123-8FC8-CC0A8952A7F6@.microsoft.com...
>
|||Yeah, Help | About within EM gives information about MMC, not about the =
specific snap in.
--=20
Keith
"John P" <JohnP@.discussions.microsoft.com> wrote in message =
news:62578D1D-7A60-440B-99A4-5B8B774A6E73@.microsoft.com...
> ahh...why didn't I think of that?! I tried that in Enterprise Manager =
but noticed it was MMC not SQL so I gave up![vbcol=seagreen]
>=20
> Thanks Keith!
>=20
> "Keith Kratochvil" wrote:
>=20
news:6A79F645-CBF8-4123-8FC8-CC0A8952A7F6@.microsoft.com...[vbcol=seagreen]
(non-server) client installation of SQL Server 2000?[vbcol=seagreen]

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
Hi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>
|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica
|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>
|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:
> Hi,
> I have a client with a database that is running over 1G in size. Looking at
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at
>> the tables, I can't find any that would seem to be larger than a 1-2 meg
>> with total size should be around 30meg. Is there any way to find out
>> table
>> sizes?
>> Thanks,
>> Mica
>>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a client with a database that is running over 1G in size. Looking
>> at the tables, I can't find any that would seem to be larger than a 1-2
>> meg with total size should be around 30meg. Is there any way to find out
>> table sizes?
>> Thanks,
>> Mica
>sql

Determine table size

Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
MicaHi Mica Cooper,
To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size
Mario Aoun
"Mica Cooper" wrote:

> Hi,
> I have a client with a database that is running over 1G in size. Looking a
t
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
> Thanks,
> Mica
>
>|||Use sp_spaceused or see: http://vyaskn.tripod.com/sp_show_biggest_tables.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?
Thanks,
Mica|||Database size is a fuzzy thing. I don't know your level of SQL experience,
so forgive me if I start off with some basics.
What do you mean, when you say "over 1GB in size"? Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties. On the first tab ("General"), there's a "Size", and then a
"Space Available". Is this the 1GB you're talking about? What does the
"Space Available" have beside it?
If that is the size that you're talking about, that includes the log file as
well as the actual data file. Click on the next tab "Data Files", and see
how much space is allocated. Then go to the "Transaction Log" tab, and see
the same thing there. If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.
The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically. To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database". I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file. You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.
To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer. It will give you the
number of rows, reserved size, data size, index size, and unused size.
I hope this helped! I'm not a SQL expert, and I don't play one on TV. But
post back with your results and any new questions, if you need more
guidance.
Clint
"Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
> Thanks,
> Mica
>|||Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica
"Mario Aoun" <MarioAoun@.discussions.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E-59D00D5137C2@.microsoft.com...[vbcol=seagreen]
> Hi Mica Cooper,
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
> Mario Aoun
> "Mica Cooper" wrote:
>|||Clint,
I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.
So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.
Thanks Guys,
Mica
"Clint" <nobody@.nowhere.non> wrote in message
news:O6TXngrkFHA.1044@.tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing. I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
> What do you mean, when you say "over 1GB in size"? Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties. On the first tab ("General"), there's a "Size", and then a
> "Space Available". Is this the 1GB you're talking about? What does the
> "Space Available" have beside it?
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file. Click on the next tab "Data Files", and
> see how much space is allocated. Then go to the "Transaction Log" tab,
> and see the same thing there. If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database". I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file. You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer. It will give you
> the number of rows, reserved size, data size, index size, and unused size.
> I hope this helped! I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
> Clint
> "Mica Cooper" <Mica.Cooper@.removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@.TK2MSFTNGP10.phx.gbl...
>

Tuesday, March 27, 2012

Determine Licenses currently in use.

How do I determine total client coneections that are currently being used by
the SQL Server?
We are developing an application that connects to a DB Server and downloads
the information particlular to that workstation, and then never hits the SQL
server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point
via our application.
The question is; Do we need/require a CAL for each workstation? If not then
why? If so, if the server is only registering one connection to the server
at any given time, why have all the CAL's?
Brian CookSounds like you would be better off with per processor licensing (unless you
have a large number of CPUs, or are running enterprise edition)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook|||Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?
>|||http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook|||Cost wise, it would be cheaper to purchase licenses for each client. This be
ing an Enterprise application we are designing, and the SQL Servers are inte
nded to be Quad processor computers, it would not be cost effective to run t
he per processor version. A
lthough the unlimited amount of licenses is attractive, I do not see the jus
tification/need for the expense. (god I sound like an accountant! Ack! Ack!
plaaaa!).
Thanks,
Brian
-- Kevin3NF wrote: --
http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
point via our application.
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?|||Hari, thanks for the script, however it does not tell me how many licenses a
re currently in use by the users/computer connections. This tells me how man
y licenses are INSTALLED to the SQL server. I need to know/validate how many
are being used at any give
n time.
Thanks,
Brian
-- Hari Prasad wrote: --
Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> Kevin Hill
> President
> 3NF Consulting
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?|||I spoke with Microsoft this morning, and there is no way built into SQL to d
etermine how many current client access licenses, so the answer is that I mu
st have a CAL for each workstation.
Individual CAL's are cheaper than the Per Processor model in this instance.
Thanks for the suggestions guys.
Brian
-- Brian Cook wrote: --
How do I determine total client coneections that are currently being used by
the SQL Server?
We are developing an application that connects to a DB Server and downloads
the information particlular to that workstation, and then never hits the SQL
server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point
via our application.
The question is; Do we need/require a CAL for each workstation? If not then
why? If so, if the server is only registering one connection to the server
at any given time, why have all the CAL's?
Brian Cook

Determine Licenses currently in use.

How do I determine total client coneections that are currently being used by the SQL Server?
We are developing an application that connects to a DB Server and downloads the information particlular to that workstation, and then never hits the SQL server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point via our application.
The question is; Do we need/require a CAL for each workstation? If not then why? If so, if the server is only registering one connection to the server at any given time, why have all the CAL's?
Brian Cook
Sounds like you would be better off with per processor licensing (unless you
have a large number of CPUs, or are running enterprise edition)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook
|||Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?
>
|||http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
> We are developing an application that connects to a DB Server and
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
> We will have about 130 workstations that will hit the database at one
point via our application.
> The question is; Do we need/require a CAL for each workstation? If not
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
> Brian Cook
|||Cost wise, it would be cheaper to purchase licenses for each client. This being an Enterprise application we are designing, and the SQL Servers are intended to be Quad processor computers, it would not be cost effective to run the per processor version. A
lthough the unlimited amount of licenses is attractive, I do not see the justification/need for the expense. (god I sound like an accountant! Ack! Ack! plaaaa!).
Thanks,
Brian
-- Kevin3NF wrote: --
http://www.microsoft.com/sql/howtobuy/default.asp
There are device CALs and user CALs. I don't know the retail price of an
individual CAL, but if it is even as low as $50, you have already passed
what the cost would be for a one CPU processor license.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Brian Cook" <anonymous@.discussions.microsoft.com> wrote in message
news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
> How do I determine total client coneections that are currently being used
by the SQL Server?
downloads the information particlular to that workstation, and then never
hits the SQL server again until the next user logs on.
point via our application.
then why? If so, if the server is only registering one connection to the
server at any given time, why have all the CAL's?
|||Hari, thanks for the script, however it does not tell me how many licenses are currently in use by the users/computer connections. This tells me how many licenses are INSTALLED to the SQL server. I need to know/validate how many are being used at any give
n time.
Thanks,
Brian
-- Hari Prasad wrote: --
Hi,
How do I determine total client coneections that are currently being used
by the SQL Server?
Execute the below script in Query analyzer to identify the mode of license
used and the count.
select serverproperty('LicenseType')
go
select serverproperty('NumLicenses'
The question is; Do we need/require a CAL for each workstation? If not then
why?
I feel that you need to get seperate licenses for each user connectiong even
though they wint access concurrently
Thanks
Hari
MCDBA
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:u9jCUoLHEHA.2744@.TK2MSFTNGP10.phx.gbl...
> Sounds like you would be better off with per processor licensing (unless
you
> have a large number of CPUs, or are running enterprise edition)
> Kevin Hill
> President
> 3NF Consulting
> news:8EE3CFCC-EBBF-4095-A99A-67B3B85AC309@.microsoft.com...
used
> by the SQL Server?
> downloads the information particlular to that workstation, and then never
> hits the SQL server again until the next user logs on.
> point via our application.
> then why? If so, if the server is only registering one connection to the
> server at any given time, why have all the CAL's?
|||I spoke with Microsoft this morning, and there is no way built into SQL to determine how many current client access licenses, so the answer is that I must have a CAL for each workstation.
Individual CAL's are cheaper than the Per Processor model in this instance.
Thanks for the suggestions guys.
Brian
-- Brian Cook wrote: --
How do I determine total client coneections that are currently being used by the SQL Server?
We are developing an application that connects to a DB Server and downloads the information particlular to that workstation, and then never hits the SQL server again until the next user logs on.
We will have about 130 workstations that will hit the database at one point via our application.
The question is; Do we need/require a CAL for each workstation? If not then why? If so, if the server is only registering one connection to the server at any given time, why have all the CAL's?
Brian Cook

Sunday, March 11, 2012

Detaching and Re-Attaching Loses Stored Procedures

Hi, I've just detached using QA and sp_detach_db a database that I have been
working on for a client and taken it to the clients site and using QA and
sp_attach_db attached it to their SQL 2000 Server.
The problem is that a) there are none of my stored procedures for that db on
their server, b) when re-attaching it back to my SQL Server there are none
of my stored procedures and worst still c) who forgot to backit up first!!
I thought all user stored procedures were stored within the mdf files so why
have they not gone with the db, tables, etc. The user sps did NOT start with
sp_ by the way.
Any help would be much appreciated.
Thanks
David Smith
MCP MCSE MCDBA <-- (ha, ha - I'm supposed to know!)Look at that issues:
http://groups.google.de/groups?q=at...c.rr.com&rnum=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David Smith" <david.smith@.lonsdale.co.uk> schrieb im Newsbeitrag
news:uGfYA8bSFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hi, I've just detached using QA and sp_detach_db a database that I have
> been working on for a client and taken it to the clients site and using QA
> and sp_attach_db attached it to their SQL 2000 Server.
> The problem is that a) there are none of my stored procedures for that db
> on their server, b) when re-attaching it back to my SQL Server there are
> none of my stored procedures and worst still c) who forgot to backit up
> first!!
> I thought all user stored procedures were stored within the mdf files so
> why have they not gone with the db, tables, etc. The user sps did NOT
> start with sp_ by the way.
> Any help would be much appreciated.
> Thanks
> David Smith
> MCP MCSE MCDBA <-- (ha, ha - I'm supposed to know!)
>

Detaching a database in SQL2005 is changing the security of the files

For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc96f58526f.htm
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7=bc=AD96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegroups.com...
>
> > For some reason when I detach a database from my SQL 2005 server using
> > my client machine, the security of the database files changes so that
> > I am the only user with security to that file. It even removes all of
> > the Administrators that were there. If I attach the database back,
> > the security then changes back to the users that were originally there
> > and my username is gone. Any ideas as to why this is happening or if
> > it should work this way... Hide quoted text -
> - Show quoted text -|||I am also having a problem with the detach. After detaching a database I get
a message that I cannot copy the database because it is in use. How can it be
inuse if it is detached. I have used detach and attach to copy databases for
years and this is the first time I have had this problem. It is also the
first time I am using SQL 2005. Is there anything in 2005 that doesn't get
cleaned up on the detach that could be doing this. Any info would be great.
CookCountySQLUser
"tharris@.krispykreme.com" wrote:
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>|||How about using some tool to say which process is using the file? I believe that sysinternals (now
MS) has such tools).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CookCountySQLUser" <CookCountySQLUser@.discussions.microsoft.com> wrote in message
news:69A376CB-D973-4EF8-9778-32D4D0F8B152@.microsoft.com...
>I am also having a problem with the detach. After detaching a database I get
> a message that I cannot copy the database because it is in use. How can it be
> inuse if it is detached. I have used detach and attach to copy databases for
> years and this is the first time I have had this problem. It is also the
> first time I am using SQL 2005. Is there anything in 2005 that doesn't get
> cleaned up on the detach that could be doing this. Any info would be great.
> CookCountySQLUser
> "tharris@.krispykreme.com" wrote:
>> For some reason when I detach a database from my SQL 2005 server using
>> my client machine, the security of the database files changes so that
>> I am the only user with security to that file. It even removes all of
>> the Administrators that were there. If I attach the database back,
>> the security then changes back to the users that were originally there
>> and my username is gone. Any ideas as to why this is happening or if
>> it should work this way?
>>

Detaching a database in SQL2005 is changing the security of the files

For some reason when I detach a database from my SQL 2005 server using
my client machine, the security of the database files changes so that
I am the only user with security to that file. It even removes all of
the Administrators that were there. If I attach the database back,
the security then changes back to the users that were originally there
and my username is gone. Any ideas as to why this is happening or if
it should work this way?
Yes, this is exactly what is supposed to happen.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bc96f58526f.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<tharris@.krispykreme.com> wrote in message
news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||Thanks for the reply. It makes sense that that attach cleans up the
file security to only the necessary users, but it doesn't make sense
to remove the administrators when detaching the database.
On Mar 1, 3:49 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes, this is exactly what is supposed to happen.
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/849b9f34-baac-48f2-b953-7bcX96f58526f.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <thar...@.krispykreme.com> wrote in message
> news:1172780956.981600.79660@.v33g2000cwv.googlegro ups.com...
>
>
> - Show quoted text -
|||I am also having a problem with the detach. After detaching a database I get
a message that I cannot copy the database because it is in use. How can it be
inuse if it is detached. I have used detach and attach to copy databases for
years and this is the first time I have had this problem. It is also the
first time I am using SQL 2005. Is there anything in 2005 that doesn't get
cleaned up on the detach that could be doing this. Any info would be great.
CookCountySQLUser
"tharris@.krispykreme.com" wrote:

> For some reason when I detach a database from my SQL 2005 server using
> my client machine, the security of the database files changes so that
> I am the only user with security to that file. It even removes all of
> the Administrators that were there. If I attach the database back,
> the security then changes back to the users that were originally there
> and my username is gone. Any ideas as to why this is happening or if
> it should work this way?
>
|||How about using some tool to say which process is using the file? I believe that sysinternals (now
MS) has such tools).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"CookCountySQLUser" <CookCountySQLUser@.discussions.microsoft.com> wrote in message
news:69A376CB-D973-4EF8-9778-32D4D0F8B152@.microsoft.com...[vbcol=seagreen]
>I am also having a problem with the detach. After detaching a database I get
> a message that I cannot copy the database because it is in use. How can it be
> inuse if it is detached. I have used detach and attach to copy databases for
> years and this is the first time I have had this problem. It is also the
> first time I am using SQL 2005. Is there anything in 2005 that doesn't get
> cleaned up on the detach that could be doing this. Any info would be great.
> CookCountySQLUser
> "tharris@.krispykreme.com" wrote:

Detaching a database in SQL2005 is changing the security of the files

Sunday, February 19, 2012

design question

Howdy all. I have developers wanting to design a table that will hold not
only client info, but what type of program they are eligible for. I have
narrowed it down to 2 options, but was really looking for opinions. The
current design is like this:
Client Table:
ClientId
LName
FName
(Lots of other info that I will look at later)
IsCash(bit)
IsFoodStamps(bit)
IsMedical(bit)
But I'd like to split it out a bit so the various programs will be in
another table(s):
Option 1:
CLIENT TABLE:
ClientId
LName
FName
PROGRAMS TABLE:
ProgramId
ProgramName
CLIENT PROGRAM LOOKUP TABLE:
ClientId
ProgramId
Option 2:
CLIENT TABLE:
ClientId
LName
FName
CLIENT PROGRAM TABLE:
ClientId
Program (with a CHECK constraint so that only certain values can be entered)
Other ideas are welcomed as well.
TIA, ChrisR> Howdy all. I have developers wanting to design a table that will hold not
> only client info, but what type of program they are eligible for. I have
> narrowed it down to 2 options, but was really looking for opinions. The
> current design is like this:
> Client Table:
> ClientId
> LName
> FName
> (Lots of other info that I will look at later)
> IsCash(bit)
> IsFoodStamps(bit)
> IsMedical(bit)
I'm leaning toward your option 1, or this, with 1 Program = many Clients:
Program table:
ProgramID
Description
Client Table:
ClientID
LName
FName
ProgramID
I think your decision should be based on whether or not a client can belong
to one program at a time, or more than one at a time. So if I can belong to
both Cash and FoodStamps programs, this design won't work and you will want
to go with your Option 1.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||To answer your question "yes, a client can and will belong to more than one
program". But why wouldn't Option 2 work?
CLIENT TABLE:
ClientId
LName
FName
CLIENT PROGRAM TABLE:
ClientId
Program
Insert into Client(1234,'Blow','Joe')
Insert into ClientProgram(1234, 'FoodStamps')
Insert into ClientProgram(1234, 'Medical')
I know it's probably not the proper way to do things, but would reduce the
amount of joins. Plus if I had a CHECK constraint on the column "program",
only the right info could be entered.
Thoughts?
"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:OrAyv0gSGHA.4608@.tk2msftngp13.phx.gbl...
not
> I'm leaning toward your option 1, or this, with 1 Program = many Clients:
> Program table:
> ProgramID
> Description
> Client Table:
> ClientID
> LName
> FName
> ProgramID
> I think your decision should be based on whether or not a client can
belong
> to one program at a time, or more than one at a time. So if I can belong
to
> both Cash and FoodStamps programs, this design won't work and you will
want
> to go with your Option 1.
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||ChrisR wrote:
> To answer your question "yes, a client can and will belong to more than on
e
> program". But why wouldn't Option 2 work?
> CLIENT TABLE:
> ClientId
> LName
> FName
> CLIENT PROGRAM TABLE:
> ClientId
> Program
>
> Insert into Client(1234,'Blow','Joe')
> Insert into ClientProgram(1234, 'FoodStamps')
> Insert into ClientProgram(1234, 'Medical')
> I know it's probably not the proper way to do things, but would reduce the
> amount of joins. Plus if I had a CHECK constraint on the column "program",
> only the right info could be entered.
> Thoughts?
>
Two things to consider. Under option 2 the users won't be able to add
new Programs - that would require a schema change to alter the CHECK
constraint. Also, if you want to change the program description for any
reason then you'll have to change it on every row, not just once.
Perhaps option 2 will also require more storage - it appears so from
your sample data.
In general I'd say that Option 1 is better for "descriptive" text
(because business users are more likely to want changes to
descriptions), whereas Option 2 is better for sets of codes because
they tend to change less frequently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:eMm9AAhSGHA.4740@.TK2MSFTNGP14.phx.gbl...
> To answer your question "yes, a client can and will belong to more than
> one
> program". But why wouldn't Option 2 work?
It would work.
Option one (clients, programs, and a third table to match the two) is the
standard method for representing a many to many relationship.
Option two would work if the only information about the program that you
care about is the program name. If you plan on entering any other details
about the program, then this isn't good enough.|||I hadn't thought of either of the points you guys vrought up. Thanks a lot
and have a great w

end.
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:eMm9AAhSGHA.4740@.TK2MSFTNGP14.phx.gbl...
> To answer your question "yes, a client can and will belong to more than
one
> program". But why wouldn't Option 2 work?
> CLIENT TABLE:
> ClientId
> LName
> FName
> CLIENT PROGRAM TABLE:
> ClientId
> Program
>
> Insert into Client(1234,'Blow','Joe')
> Insert into ClientProgram(1234, 'FoodStamps')
> Insert into ClientProgram(1234, 'Medical')
> I know it's probably not the proper way to do things, but would reduce the
> amount of joins. Plus if I had a CHECK constraint on the column "program",
> only the right info could be entered.
> Thoughts?
>
> "Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
> news:OrAyv0gSGHA.4608@.tk2msftngp13.phx.gbl...
> not
have
The
Clients:
> belong
belong
> to
> want
>

design question

Hi,
Please see the three table below, I'm designing the db schema so that it
maintains each Client has exactly 1 default service.
table ServiceType
(
ServiceTypeId,
...
...
)
table Client
(
ClientId,
...
...
)
table ClientAvailableService
(
ClientId foreign key
ServiceTypeId foreign key
IsDefaultService bit
...
...
)
The first possible way is to create IsDefaultService column in the
ClientAvailableService and use trigger to ensure that there is exactly one
row for each ClientId has the bit turns on, but I'm not sure if that i can
handle scenario when my application user wants to switch default service as
it would either need to turn the existing off first (which would immediately
violate the trigger checking) then turn on the new one OR turn on the new
one (which violates the trigger checking) before turning off the existing
one. Overall this strategy, if it works, would still be messy and
inefficient as the trigger needs to go through many rows in the table to
maintain it.
The second way is to do add a ranking column in decimal type or int type,
the lowest number would mean that it's the default, this would definitely
work but retrieving the default one would require reading and sorting many
rows.
The third way is to add another table
table ClientDefaultAvailableService
(
ClientId
ServiceTypeId
primarykey( ClientId, ServiceTypeId )
)
This would require joining to find out which one is default even when I need
to retrieve the entire list of ClientAvailableService for a particular
client.
Would someone out there have or know a better way to implement this or just
have some comment about this issue? I assume experienced db designers must
have run into this issue before. Thank you very much.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Please see the three table below, I'm designing the db schema so that it
> maintains each Client has exactly 1 default service.
> table ServiceType
> (
> ServiceTypeId,
> ...
> ...
> )
> table Client
> (
> ClientId,
> ...
> ...
> )
> table ClientAvailableService
> (
> ClientId foreign key
> ServiceTypeId foreign key
> IsDefaultService bit
> ...
> ...
> )
>
....
>
> Would someone out there have or know a better way to implement this or
> just
> have some comment about this issue? I assume experienced db designers
> must
> have run into this issue before. Thank you very much.
>
>
The correct way to model this is to add a DefaultService column to Client.
There's really two distinct relationships beteen client and ServiceType.
Each client has one to many ServiceTypes, and Each client has at most one
Default ServiceType.
If you model both relationships then there's no need for triggers. You do
have to do some things in a certian order. For instance you have to add a
Client, then Add services then choose one of those services as the Default
service. But those operational constraints make good sense in the model.
create table ServiceType
(
ServiceTypeID int primary key
)
create table Client
(
ClientID int primary key,
DefaultService int null references ServiceType
)
create table ClientServiceType
(
ClientID int references Client on delete cascade,
ServiceType int references ServiceType,
constraint pk_ClientServiceType
primary key(ClientID,ServiceType)
)
--and this guarantees that the client's DefaultService
--is in that client's list of ServiceTypes.
alter table client
add constraint fk_DefaultServise
foreign key (ClientID,DefaultService) references ClientServiceType
insert into ServiceType values (1)
insert into ServiceType values (2)
insert into ServiceType values (3)
insert into Client(ClientID) values (1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,2)
update client set DefaultService = 2 where ClientID =1
David
|||David, thank you so much for your help!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O29kge8kFHA.3436@.tk2msftngp13.phx.gbl...
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> ...
> The correct way to model this is to add a DefaultService column to Client.
> There's really two distinct relationships beteen client and ServiceType.
> Each client has one to many ServiceTypes, and Each client has at most one
> Default ServiceType.
> If you model both relationships then there's no need for triggers. You do
> have to do some things in a certian order. For instance you have to add a
> Client, then Add services then choose one of those services as the Default
> service. But those operational constraints make good sense in the model.
>
> create table ServiceType
> (
> ServiceTypeID int primary key
> )
> create table Client
> (
> ClientID int primary key,
> DefaultService int null references ServiceType
> )
> create table ClientServiceType
> (
> ClientID int references Client on delete cascade,
> ServiceType int references ServiceType,
> constraint pk_ClientServiceType
> primary key(ClientID,ServiceType)
> )
> --and this guarantees that the client's DefaultService
> --is in that client's list of ServiceTypes.
> alter table client
> add constraint fk_DefaultServise
> foreign key (ClientID,DefaultService) references ClientServiceType
>
> insert into ServiceType values (1)
> insert into ServiceType values (2)
> insert into ServiceType values (3)
> insert into Client(ClientID) values (1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,2)
> update client set DefaultService = 2 where ClientID =1
> David
>
|||On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
(snip)
>The second way is to do add a ranking column in decimal type or int type,
>the lowest number would mean that it's the default, this would definitely
>work but retrieving the default one would require reading and sorting many
>rows.
Hi Zeng,
Though David's suggestion is very good, a ranking column is a very good
idea as well. Finding the default would actually be fast if there is an
index (and there will be one, since the combination of ClientID / Rank
should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
index is automatically added.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:espie1djg0dtjsf5c5chp8u3bca6frl3oa@.4ax.com... [vbcol=seagreen]
> On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
> (snip)
many
> Hi Zeng,
> Though David's suggestion is very good, a ranking column is a very good
> idea as well. Finding the default would actually be fast if there is an
> index (and there will be one, since the combination of ClientID / Rank
> should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
> index is automatically added.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Design Question

Here is my scenario:
I'm creating a thin client viewer/editor for a client that has a rebate
program.
There are several types of rebate programs, and incentives are different for
each. There are approximately 160 predicating companies and each "reports"
it's sales amount or other qualifying criteria each quarter.
Now, all this is pretty vanilla until, except that the rebate percentage for
each company can change in midstream of a fiscal year.
The old records (pre percentage) change must be kept for viewing only, but
post change must be editable and updateable.
What is the best way to accomplish this without having to nest a lot of
tables?
Thanks in advance for you input..
~ChrisI'd track the old and new percentage rates in a table and based on your
query date (Run from date) you select the proper percentage rate from
that table to use for the calculation.
Ex.
PercentageValidDate Percentage
1/1/2000 6.5
1/1/2003 6.85
6/1/2003 6.93
SELECT TOP 11 Percentage where PercentageValidDATE < [paramater run date]
order by PercentageValidDATE DESC
Smitty wrote:
> Here is my scenario:
> I'm creating a thin client viewer/editor for a client that has a rebate
> program.
> There are several types of rebate programs, and incentives are different for
> each. There are approximately 160 predicating companies and each "reports"
> it's sales amount or other qualifying criteria each quarter.
> Now, all this is pretty vanilla until, except that the rebate percentage for
> each company can change in midstream of a fiscal year.
> The old records (pre percentage) change must be kept for viewing only, but
> post change must be editable and updateable.
> What is the best way to accomplish this without having to nest a lot of
> tables?
> Thanks in advance for you input..
> ~Chris
>

Friday, February 17, 2012

design question

Hi,
Please see the three table below, I'm designing the db schema so that it
maintains each Client has exactly 1 default service.
table ServiceType
(
ServiceTypeId,
...
...
)
table Client
(
ClientId,
...
...
)
table ClientAvailableService
(
ClientId foreign key
ServiceTypeId foreign key
IsDefaultService bit
...
...
)
The first possible way is to create IsDefaultService column in the
ClientAvailableService and use trigger to ensure that there is exactly one
row for each ClientId has the bit turns on, but I'm not sure if that i can
handle scenario when my application user wants to switch default service as
it would either need to turn the existing off first (which would immediately
violate the trigger checking) then turn on the new one OR turn on the new
one (which violates the trigger checking) before turning off the existing
one. Overall this strategy, if it works, would still be messy and
inefficient as the trigger needs to go through many rows in the table to
maintain it.
The second way is to do add a ranking column in decimal type or int type,
the lowest number would mean that it's the default, this would definitely
work but retrieving the default one would require reading and sorting many
rows.
The third way is to add another table
table ClientDefaultAvailableService
(
ClientId
ServiceTypeId
primarykey( ClientId, ServiceTypeId )
)
This would require joining to find out which one is default even when I need
to retrieve the entire list of ClientAvailableService for a particular
client.
Would someone out there have or know a better way to implement this or just
have some comment about this issue? I assume experienced db designers must
have run into this issue before. Thank you very much."Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Please see the three table below, I'm designing the db schema so that it
> maintains each Client has exactly 1 default service.
> table ServiceType
> (
> ServiceTypeId,
> ...
> ...
> )
> table Client
> (
> ClientId,
> ...
> ...
> )
> table ClientAvailableService
> (
> ClientId foreign key
> ServiceTypeId foreign key
> IsDefaultService bit
> ...
> ...
> )
>
...
>
> Would someone out there have or know a better way to implement this or
> just
> have some comment about this issue? I assume experienced db designers
> must
> have run into this issue before. Thank you very much.
>
>
The correct way to model this is to add a DefaultService column to Client.
There's really two distinct relationships beteen client and ServiceType.
Each client has one to many ServiceTypes, and Each client has at most one
Default ServiceType.
If you model both relationships then there's no need for triggers. You do
have to do some things in a certian order. For instance you have to add a
Client, then Add services then choose one of those services as the Default
service. But those operational constraints make good sense in the model.
create table ServiceType
(
ServiceTypeID int primary key
)
create table Client
(
ClientID int primary key,
DefaultService int null references ServiceType
)
create table ClientServiceType
(
ClientID int references Client on delete cascade,
ServiceType int references ServiceType,
constraint pk_ClientServiceType
primary key(ClientID,ServiceType)
)
--and this guarantees that the client's DefaultService
--is in that client's list of ServiceTypes.
alter table client
add constraint fk_DefaultServise
foreign key (ClientID,DefaultService) references ClientServiceType
insert into ServiceType values (1)
insert into ServiceType values (2)
insert into ServiceType values (3)
insert into Client(ClientID) values (1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,2)
update client set DefaultService = 2 where ClientID =1
David|||On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
(snip)
>The second way is to do add a ranking column in decimal type or int type,
>the lowest number would mean that it's the default, this would definitely
>work but retrieving the default one would require reading and sorting many
>rows.
Hi Zeng,
Though David's suggestion is very good, a ranking column is a very good
idea as well. Finding the default would actually be fast if there is an
index (and there will be one, since the combination of ClientID / Rank
should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
index is automatically added.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||David, thank you so much for your help!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O29kge8kFHA.3436@.tk2msftngp13.phx.gbl...
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > Please see the three table below, I'm designing the db schema so that it
> > maintains each Client has exactly 1 default service.
> >
> > table ServiceType
> > (
> > ServiceTypeId,
> > ...
> > ...
> > )
> >
> > table Client
> > (
> > ClientId,
> > ...
> > ...
> > )
> >
> > table ClientAvailableService
> > (
> > ClientId foreign key
> > ServiceTypeId foreign key
> > IsDefaultService bit
> > ...
> > ...
> >
> > )
> >
> ...
> >
> >
> > Would someone out there have or know a better way to implement this or
> > just
> > have some comment about this issue? I assume experienced db designers
> > must
> > have run into this issue before. Thank you very much.
> >
> >
> >
> The correct way to model this is to add a DefaultService column to Client.
> There's really two distinct relationships beteen client and ServiceType.
> Each client has one to many ServiceTypes, and Each client has at most one
> Default ServiceType.
> If you model both relationships then there's no need for triggers. You do
> have to do some things in a certian order. For instance you have to add a
> Client, then Add services then choose one of those services as the Default
> service. But those operational constraints make good sense in the model.
>
> create table ServiceType
> (
> ServiceTypeID int primary key
> )
> create table Client
> (
> ClientID int primary key,
> DefaultService int null references ServiceType
> )
> create table ClientServiceType
> (
> ClientID int references Client on delete cascade,
> ServiceType int references ServiceType,
> constraint pk_ClientServiceType
> primary key(ClientID,ServiceType)
> )
> --and this guarantees that the client's DefaultService
> --is in that client's list of ServiceTypes.
> alter table client
> add constraint fk_DefaultServise
> foreign key (ClientID,DefaultService) references ClientServiceType
>
> insert into ServiceType values (1)
> insert into ServiceType values (2)
> insert into ServiceType values (3)
> insert into Client(ClientID) values (1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,2)
> update client set DefaultService = 2 where ClientID =1
> David
>|||Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:espie1djg0dtjsf5c5chp8u3bca6frl3oa@.4ax.com...
> On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
> (snip)
> >The second way is to do add a ranking column in decimal type or int type,
> >the lowest number would mean that it's the default, this would definitely
> >work but retrieving the default one would require reading and sorting
many
> >rows.
> Hi Zeng,
> Though David's suggestion is very good, a ranking column is a very good
> idea as well. Finding the default would actually be fast if there is an
> index (and there will be one, since the combination of ClientID / Rank
> should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
> index is automatically added.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

design question

Hi,
Please see the three table below, I'm designing the db schema so that it
maintains each Client has exactly 1 default service.
table ServiceType
(
ServiceTypeId,
..
..
)
table Client
(
ClientId,
...
...
)
table ClientAvailableService
(
ClientId foreign key
ServiceTypeId foreign key
IsDefaultService bit
..
..
)
The first possible way is to create IsDefaultService column in the
ClientAvailableService and use trigger to ensure that there is exactly one
row for each ClientId has the bit turns on, but I'm not sure if that i can
handle scenario when my application user wants to switch default service as
it would either need to turn the existing off first (which would immediately
violate the trigger checking) then turn on the new one OR turn on the new
one (which violates the trigger checking) before turning off the existing
one. Overall this strategy, if it works, would still be messy and
inefficient as the trigger needs to go through many rows in the table to
maintain it.
The second way is to do add a ranking column in decimal type or int type,
the lowest number would mean that it's the default, this would definitely
work but retrieving the default one would require reading and sorting many
rows.
The third way is to add another table
table ClientDefaultAvailableService
(
ClientId
ServiceTypeId
primarykey( ClientId, ServiceTypeId )
)
This would require joining to find out which one is default even when I need
to retrieve the entire list of ClientAvailableService for a particular
client.
Would someone out there have or know a better way to implement this or just
have some comment about this issue? I assume experienced db designers must
have run into this issue before. Thank you very much."Zeng" <Zeng5000@.hotmail.com> wrote in message
news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Please see the three table below, I'm designing the db schema so that it
> maintains each Client has exactly 1 default service.
> table ServiceType
> (
> ServiceTypeId,
> ...
> ...
> )
> table Client
> (
> ClientId,
> ...
> ...
> )
> table ClientAvailableService
> (
> ClientId foreign key
> ServiceTypeId foreign key
> IsDefaultService bit
> ...
> ...
> )
>
...
>
> Would someone out there have or know a better way to implement this or
> just
> have some comment about this issue? I assume experienced db designers
> must
> have run into this issue before. Thank you very much.
>
>
The correct way to model this is to add a DefaultService column to Client.
There's really two distinct relationships beteen client and ServiceType.
Each client has one to many ServiceTypes, and Each client has at most one
Default ServiceType.
If you model both relationships then there's no need for triggers. You do
have to do some things in a certian order. For instance you have to add a
Client, then Add services then choose one of those services as the Default
service. But those operational constraints make good sense in the model.
create table ServiceType
(
ServiceTypeID int primary key
)
create table Client
(
ClientID int primary key,
DefaultService int null references ServiceType
)
create table ClientServiceType
(
ClientID int references Client on delete cascade,
ServiceType int references ServiceType,
constraint pk_ClientServiceType
primary key(ClientID,ServiceType)
)
--and this guarantees that the client's DefaultService
--is in that client's list of ServiceTypes.
alter table client
add constraint fk_DefaultServise
foreign key (ClientID,DefaultService) references ClientServiceType
insert into ServiceType values (1)
insert into ServiceType values (2)
insert into ServiceType values (3)
insert into Client(ClientID) values (1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,1)
insert into ClientServiceType(ClientID,ServiceType)
values(1,2)
update client set DefaultService = 2 where ClientID =1
David|||David, thank you so much for your help!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O29kge8kFHA.3436@.tk2msftngp13.phx.gbl...
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:uuFBoM8kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> ...
> The correct way to model this is to add a DefaultService column to Client.
> There's really two distinct relationships beteen client and ServiceType.
> Each client has one to many ServiceTypes, and Each client has at most one
> Default ServiceType.
> If you model both relationships then there's no need for triggers. You do
> have to do some things in a certian order. For instance you have to add a
> Client, then Add services then choose one of those services as the Default
> service. But those operational constraints make good sense in the model.
>
> create table ServiceType
> (
> ServiceTypeID int primary key
> )
> create table Client
> (
> ClientID int primary key,
> DefaultService int null references ServiceType
> )
> create table ClientServiceType
> (
> ClientID int references Client on delete cascade,
> ServiceType int references ServiceType,
> constraint pk_ClientServiceType
> primary key(ClientID,ServiceType)
> )
> --and this guarantees that the client's DefaultService
> --is in that client's list of ServiceTypes.
> alter table client
> add constraint fk_DefaultServise
> foreign key (ClientID,DefaultService) references ClientServiceType
>
> insert into ServiceType values (1)
> insert into ServiceType values (2)
> insert into ServiceType values (3)
> insert into Client(ClientID) values (1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,1)
> insert into ClientServiceType(ClientID,ServiceType)
> values(1,2)
> update client set DefaultService = 2 where ClientID =1
> David
>|||On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
(snip)
>The second way is to do add a ranking column in decimal type or int type,
>the lowest number would mean that it's the default, this would definitely
>work but retrieving the default one would require reading and sorting many
>rows.
Hi Zeng,
Though David's suggestion is very good, a ranking column is a very good
idea as well. Finding the default would actually be fast if there is an
index (and there will be one, since the combination of ClientID / Rank
should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
index is automatically added.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:espie1djg0dtjsf5c5chp8u3bca6frl3oa@.
4ax.com...
> On Thu, 28 Jul 2005 15:19:59 -0700, Zeng wrote:
> (snip)
many[vbcol=seagreen]
> Hi Zeng,
> Though David's suggestion is very good, a ranking column is a very good
> idea as well. Finding the default would actually be fast if there is an
> index (and there will be one, since the combination of ClientID / Rank
> should be declared as either PRIMARY KEY or UNIQUE, and in both cases an
> index is automatically added.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Tuesday, February 14, 2012

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
Dan van Ginhoven
Sweden
Is this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> Dan van Ginhoven
> Sweden
>

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
---
Dan van Ginhoven
SwedenIs this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> ---
> Dan van Ginhoven
> Sweden
>

Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
---
Dan van Ginhoven
SwedenIs this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
--
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> ---
> Dan van Ginhoven
> Sweden
>

Thursday, March 29, 2012

determine the SP level of a client install?

Determine table size

Determine table size

Determine table size

Tuesday, March 27, 2012

Determine Licenses currently in use.

Determine Licenses currently in use.

Sunday, March 11, 2012

Detaching and Re-Attaching Loses Stored Procedures

Detaching a database in SQL2005 is changing the security of the files

Detaching a database in SQL2005 is changing the security of the files

Detaching a database in SQL2005 is changing the security of the files

Sunday, February 19, 2012

design question

design question

Design Question

Friday, February 17, 2012

design question

design question

Tuesday, February 14, 2012

Design flaw - Too slow lock escalation

Design flaw - Too slow lock escalation

Design flaw - Too slow lock escalation

Determining Security

Blog Archive

About Me