Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID = (SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scripts/article.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID => (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||Like most people you need proper database change control
then these issues would disappear. It seems that people
are all to quick to think that their processes are OK
until the inevitable happens and they are in the _hit!
Smart people use DB Ghost.
regards,
Mark Baekdal
www.dbghost.com
Living and breathing database change management for SQL
Server
>--Original Message--
>MS SQL Server 2000 does not allow columns with Default
value constraints to
>be dropped. We are looking for an easy way to drop such
columns. The problem
>occurs because the DEFAULT clause actually creates a
constraint on the
>table. This name of this constraint is chosen by SQL
Server and will differ
>from database to database.
>The seemingly obvious solution:
>exec sp_unbindefault 'table.column'
>does not work but gives us the following error:
>Server: Msg 15049, Level 11, State 1, Procedure
sp_unbindefault, Line 98
>Cannot unbind from 'table.column'. Use ALTER TABLE DROP
CONSTRAINT
>OK, ALTER TABLE DROP CONSTRAINT would certainly work,
but we don't know the
>name of the contraint! Makes it kind of hard for us to
script this to work
>for multiple databases.
>The only working solution we have seems very cumbersome.
(I guess we could
>convert it to a stored procedure.)
>DECLARE @.STR VARCHAR(100)
>SET @.STR = (
>SELECT NAME
>FROM SYSOBJECTS SO
>JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
>WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
>AND SO.XTYPE = 'D' AND SC.COLID =>(SELECT COLID FROM SYSCOLUMNS
>WHERE ID = OBJECT_ID('<table>')
>AND NAME = '<column>')
>)
>SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR
EXEC (@.STR)
>The other solution would be to stop using the DEFAULT
clause on the column
>definition, but rather to create a named constraint. But
completely
>eschewing the DEFAULT clause will drive the complexity
of our database
>definition scripts way up. We use DEFAULT values rather
heavily and I don't
>want to have to specify an ALTER TABLE ADD CONSTRAINT
for every column with
>a default value! I don't like this option.
>There's got to be a better way (I hope). I can't believe
it has to be this
>difficult to drop a column simply because it has a
default value specified
>for it. Is there an easy way to deal with this?
>Thanks for your help!
>Joe Geretz
>
>.
>sql

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe Geretz
Check this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scrip...le.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>
|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

Easy way to drop a column with Default Value defined?

MS SQL Server 2000 does not allow columns with Default value constraints to
be dropped. We are looking for an easy way to drop such columns. The problem
occurs because the DEFAULT clause actually creates a constraint on the
table. This name of this constraint is chosen by SQL Server and will differ
from database to database.
The seemingly obvious solution:
exec sp_unbindefault 'table.column'
does not work but gives us the following error:
Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know the
name of the contraint! Makes it kind of hard for us to script this to work
for multiple databases.
The only working solution we have seems very cumbersome. (I guess we could
convert it to a stored procedure.)
DECLARE @.STR VARCHAR(100)
SET @.STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('<table>')
AND NAME = '<column>')
)
SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
The other solution would be to stop using the DEFAULT clause on the column
definition, but rather to create a named constraint. But completely
eschewing the DEFAULT clause will drive the complexity of our database
definition scripts way up. We use DEFAULT values rather heavily and I don't
want to have to specify an ALTER TABLE ADD CONSTRAINT for every column with
a default value! I don't like this option.
There's got to be a better way (I hope). I can't believe it has to be this
difficult to drop a column simply because it has a default value specified
for it. Is there an easy way to deal with this?
Thanks for your help!
Joe GeretzCheck this out... It's a Stored Proc that will take care of this for you...
http://www.databasejournal.com/scri...cle.php/1498701
--TJTODD
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:#aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>|||> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up.
You can specify the default constraint name, even when included in a CREATE
TABLE statement. You don't need a separate ALTER TABLE to name the
constraint:
CREATE TABLE MyTable
(
Col1 int NOT NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0,
Col2 int NOT NULL
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23aEbto4bEHA.1144@.TK2MSFTNGP11.phx.gbl...
> MS SQL Server 2000 does not allow columns with Default value constraints
to
> be dropped. We are looking for an easy way to drop such columns. The
problem
> occurs because the DEFAULT clause actually creates a constraint on the
> table. This name of this constraint is chosen by SQL Server and will
differ
> from database to database.
> The seemingly obvious solution:
> exec sp_unbindefault 'table.column'
> does not work but gives us the following error:
> Server: Msg 15049, Level 11, State 1, Procedure sp_unbindefault, Line 98
> Cannot unbind from 'table.column'. Use ALTER TABLE DROP CONSTRAINT
> OK, ALTER TABLE DROP CONSTRAINT would certainly work, but we don't know
the
> name of the contraint! Makes it kind of hard for us to script this to work
> for multiple databases.
> The only working solution we have seems very cumbersome. (I guess we could
> convert it to a stored procedure.)
> DECLARE @.STR VARCHAR(100)
> SET @.STR = (
> SELECT NAME
> FROM SYSOBJECTS SO
> JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
> WHERE OBJECT_NAME(SO.PARENT_OBJ) = '<table>'
> AND SO.XTYPE = 'D' AND SC.COLID =
> (SELECT COLID FROM SYSCOLUMNS
> WHERE ID = OBJECT_ID('<table>')
> AND NAME = '<column>')
> )
> SET @.STR = 'ALTER TABLE <table> DROP CONSTRAINT ' + @.STR EXEC (@.STR)
> The other solution would be to stop using the DEFAULT clause on the column
> definition, but rather to create a named constraint. But completely
> eschewing the DEFAULT clause will drive the complexity of our database
> definition scripts way up. We use DEFAULT values rather heavily and I
don't
> want to have to specify an ALTER TABLE ADD CONSTRAINT for every column
with
> a default value! I don't like this option.
> There's got to be a better way (I hope). I can't believe it has to be this
> difficult to drop a column simply because it has a default value specified
> for it. Is there an easy way to deal with this?
> Thanks for your help!
> Joe Geretz
>

dynamically changing default parameter?

I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.

Can this be done?

here is how i'm setting my "from date" (datetime) param to be the first of the current month
=DateSerial(Year(Now()), Month(Now()),1)

for today, just use
=Today()

this is for a whole date, you can just pick the year part

Dynamic SQL Cache

I am running SQL Server 2005 SP2 Standard Edition on Windows 2003 Server
Standard Edition (4 gb RAM, No AWE Enabled, default Max/Min Memory
settings). From PerfMon traces the overall server memory appears to be just
fine all the time. The SQL Server appears to run out of available SQL
memory resources during certain ETL jobs and log the following errors:
--Error: 701, Severity: 17, State: 123
--There is insufficient system memory to run this query.
I see significant spikes in the SQLServer:Memory Manager:SQL Cache Memory
counter during times when I experience "Out Of Memory" errors on my SQL
Server. The value is normally 0 until milliseconds before I get the 701
Errors. According to BOL this Counter represents the "Total amount of
dynamic memory the server is using for the dynamic SQL cache."
What exactly is the dynamic SQL cache? What should I look for in tracking
down this memory resource hog?Hi Ulysses,
I understand that you encountered the insufficient system memory error
during your ETL jobs execution.
If I have misunderstood, please let me know.
Dynamic SQL cache means that the dynamic memory that your SQL Server
instance caches. By design SQL Server will try to occupy memory as much as
possible to ensure its performance. SQL Server frees memory when it has
more than the min server memory setting, and Windows indicates that there
is a shortage of free memory. However in memory intensive usage situations,
this happens uncertainly.
You may refer to:
Dynamic Memory Management
http://msdn2.microsoft.com/en-us/library/ms178145.aspx
ETL jobs for extracting large volume of data may consume large memory for
SQL Server. Since AWE was not enabled, your SQL Server can only use 2GB
memory at most. I recommend that you use /3GB switch or enable AWE option
in your SQL Server 2005.
You may refer to the following articles:
Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750/en-us
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Hi
>SQL Server frees memory when it has
> more than the min server memory setting,
BOL says
The database engine will not free any of the acquired memory until it
reaches the amount specified in min server memory
""Charles Wang [MSFT]"" <changliw@.online.microsoft.com> wrote in message
news:azN3LJrpIHA.1788@.TK2MSFTNGHUB02.phx.gbl...
> Hi Ulysses,
> I understand that you encountered the insufficient system memory error
> during your ETL jobs execution.
> If I have misunderstood, please let me know.
> Dynamic SQL cache means that the dynamic memory that your SQL Server
> instance caches. By design SQL Server will try to occupy memory as much as
> possible to ensure its performance. SQL Server frees memory when it has
> more than the min server memory setting, and Windows indicates that there
> is a shortage of free memory. However in memory intensive usage
> situations,
> this happens uncertainly.
> You may refer to:
> Dynamic Memory Management
> http://msdn2.microsoft.com/en-us/library/ms178145.aspx
> ETL jobs for extracting large volume of data may consume large memory for
> SQL Server. Since AWE was not enabled, your SQL Server can only use 2GB
> memory at most. I recommend that you use /3GB switch or enable AWE option
> in your SQL Server 2005.
> You may refer to the following articles:
> Enabling AWE Memory for SQL Server
> http://msdn2.microsoft.com/en-us/library/ms190673.aspx
> How to configure SQL Server to use more than 2 GB of physical memory
> http://support.microsoft.com/kb/274750/en-us
> Please feel free to let me know if you have any other questions or
> concerns. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ===========================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for
> non-urgent issues where an initial response from the community
> or a Microsoft Support Engineer within 1 business day is acceptable.
> Please note that each follow up response may take approximately
> 2 business days as the support professional working with you may
> need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ============================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================>
>|||Sorry for delaying this response since I took a sick leave these two days.
Hi Uri,
Could you please point out which paragraph that BOL describes this
information?
From the article "Dynamic Memory Management" in BOL, you can find the
following description:
" A SQL Server instance continues to acquire physical memory until it
either reaches its max server memory allocation target or Windows indicates
there is no longer an excess of free memory; it frees memory when it has
more than the min server memory setting, and Windows indicates that there
is a shortage of free memory."
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Thanks. I did enable the /3GB switch and enabled AWE in SQL Server 2005 and
then set the min / max memory using sp_configure and this allowed for the
ETL process to run without interupting the other processes (i.e., no more
Out-of-Memory errors). I do not know if it was simply enabling the "AWE
/3GB" or manually setting the "min/max" memory that fixed the problem.
Perhaps it was the combination of both changes. I have moved on to more
pressing issues now (i.e., Linked Server Connection Error: 18456)|||Hi Ulysses,
Thanks for your response for letting me know this issue has been fixed.
Just clarify one thing that actually you can just enable one of the two
options:/3GB and AWE. In other words, if you specify /3GB switch, you do
not need to enable AWE; otherwise, you can enable AWE together with /PAE
switch. You may refer to:
Large memory support is available in Windows Server 2003 and in Windows 2000
http://support.microsoft.com/kb/283037/en-us
Regarding the linked server connection error, I will continue working with
you for further research under your other post "Linked Server Connection
Error: 18459".
Please feel free to let me know if you have any other questions or concerns.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Hi Charles
I hope now you are doing well.
I was mention BOL (SS2000) Min server memory option then Effects of min and
max server memory
""Charles Wang [MSFT]"" <changliw@.online.microsoft.com> wrote in message
news:3FUxTgpqIHA.1784@.TK2MSFTNGHUB02.phx.gbl...
> Sorry for delaying this response since I took a sick leave these two days.
> Hi Uri,
> Could you please point out which paragraph that BOL describes this
> information?
> From the article "Dynamic Memory Management" in BOL, you can find the
> following description:
> " A SQL Server instance continues to acquire physical memory until it
> either reaches its max server memory allocation target or Windows
> indicates
> there is no longer an excess of free memory; it frees memory when it has
> more than the min server memory setting, and Windows indicates that there
> is a shortage of free memory."
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> =========================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================>