On the subject of backup plans ...

Given the situation of a daily full backup and shrink of a database, does
anyone know of any 'gotchas' that are likely to bite one in the backside
when the recovery model for the databse is 'Full' as opposed to 'Simple'.
For example:
use master;
backup database <databasename> to disk='<filename>' with description='Full
backup of <databasename>',init,skip;
dbcc shrinkdatabase(<databasename>)When you set your database's recovery model to FULL then your passive
virtual log files will be held until you backup the database.
FULL recovery model is recommended for production environments because then
you could restore your database to the point of failure. Also you'd be able
to backup Transaction Logs.
If you set it's rec. model to SIMPLE then passive vlfs will be truncated at
every checkpoint. This recovery model is recommended for test environments
and if you use this recovery model then you'd not be able to transaction log
files. So you'd be able to restore your database only using your FULL and
DIFFERENTIAL backups.
Not using Transaction Log File backups is unacceptable for most of the
production environments.
Also, shrinking a database all the time is not a good idea for every
situation. This could be a performance problem as your database would need
to expand when it is being used. Expending and shrinking is a resource
consuming process.
Overview of the Recovery Models:
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Stephany Young" <noone@.localhost> wrote in message
news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
> Given the situation of a daily full backup and shrink of a database, does
> anyone know of any 'gotchas' that are likely to bite one in the backside
> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
> For example:
> use master;
> backup database <databasename> to disk='<filename>' with
> description='Full backup of <databasename>',init,skip;
> dbcc shrinkdatabase(<databasename>)
>|||Yes, yes ... I know all that!
So you are not aware of any 'gotchas' for the situation I described then?
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
> When you set your database's recovery model to FULL then your passive
> virtual log files will be held until you backup the database.
> FULL recovery model is recommended for production environments because
> then you could restore your database to the point of failure. Also you'd
> be able to backup Transaction Logs.
> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
> at every checkpoint. This recovery model is recommended for test
> environments and if you use this recovery model then you'd not be able to
> transaction log files. So you'd be able to restore your database only
> using your FULL and DIFFERENTIAL backups.
> Not using Transaction Log File backups is unacceptable for most of the
> production environments.
> Also, shrinking a database all the time is not a good idea for every
> situation. This could be a performance problem as your database would need
> to expand when it is being used. Expending and shrinking is a resource
> consuming process.
> Overview of the Recovery Models:
> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
> --
> Ekrem Önsoy
> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>
> "Stephany Young" <noone@.localhost> wrote in message
> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database, does
>> anyone know of any 'gotchas' that are likely to bite one in the backside
>> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||Not interested in your subject anymore.
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Stephany Young" <noone@.localhost> wrote in message
news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Yes, yes ... I know all that!
> So you are not aware of any 'gotchas' for the situation I described then?
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able to
>> transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed to
>> 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||> So you are not aware of any 'gotchas' for the situation I described then?
Shrinking files every night is bad because of the performance hit that
occurs when files grow back to the needed size during the day. In the FULL
recovery model, the logs should be sized to accommodate expected activity
between log backups. The goal is to avoid file growth during normal
activity while keep file sizes manageable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephany Young" <noone@.localhost> wrote in message
news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Yes, yes ... I know all that!
> So you are not aware of any 'gotchas' for the situation I described then?
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able to
>> transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed to
>> 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>|||Thanks for that Dan.
Maybe I phrased my original question badly.
I think the question should have been:
Is anyone aware of any behavioural differences between executing a 'dbcc
shrinkdatabase(<databasename>)' on a Full Recovery Model database compared
to executing the same statement on the same database if it were using Simple
Recovery Model?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:7AF6D479-8816-43AC-98A4-67F284D3E7D4@.microsoft.com...
>> So you are not aware of any 'gotchas' for the situation I described then?
> Shrinking files every night is bad because of the performance hit that
> occurs when files grow back to the needed size during the day. In the
> FULL recovery model, the logs should be sized to accommodate expected
> activity between log backups. The goal is to avoid file growth during
> normal activity while keep file sizes manageable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stephany Young" <noone@.localhost> wrote in message
> news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Yes, yes ... I know all that!
>> So you are not aware of any 'gotchas' for the situation I described then?
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive
>> virtual log files will be held until you backup the database.
>> FULL recovery model is recommended for production environments because
>> then you could restore your database to the point of failure. Also you'd
>> be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated
>> at every checkpoint. This recovery model is recommended for test
>> environments and if you use this recovery model then you'd not be able
>> to transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the
>> production environments.
>> Also, shrinking a database all the time is not a good idea for every
>> situation. This could be a performance problem as your database would
>> need to expand when it is being used. Expending and shrinking is a
>> resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database,
>> does anyone know of any 'gotchas' that are likely to bite one in the
>> backside when the recovery model for the databse is 'Full' as opposed
>> to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with
>> description='Full backup of <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>>
>|||Ya, I got "gotcha" for ya.
When you shrink a DB (Simple, BulkLogged, or Full), watch the effect on the
filesystem in terms of drive fragmentation (outside of SQL Server).
Use the Windows defrag tool to analyze the partition your .MDF/.NDF files
are on and when it is done, look at the report for the .MDF/.NDF you shrunk.
It is likely the file will be fragmented on the disk, hindering overall
performance.
Since you specified 'shrinkdatabase', I'm assuming you're after .mdf/.ndf
files. The recovery model is dealing with the transaction log .ldf file. So
I guess the 'gotcha' is that you're operating on different areas.
I've never used 'dbcc shrinkdatabase', only 'dbcc shrinkfile' for out of
control log files (which relate more directly to the Recovery Model), so I
don't know the details of their differences.
Jay
"Stephany Young" <noone@.localhost> wrote in message
news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
> Given the situation of a daily full backup and shrink of a database, does
> anyone know of any 'gotchas' that are likely to bite one in the backside
> when the recovery model for the databse is 'Full' as opposed to 'Simple'.
> For example:
> use master;
> backup database <databasename> to disk='<filename>' with
> description='Full backup of <databasename>',init,skip;
> dbcc shrinkdatabase(<databasename>)
>|||> Is anyone aware of any behavioural differences between executing a 'dbcc
> shrinkdatabase(<databasename>)' on a Full Recovery Model database compared to executing the same
> statement on the same database if it were using Simple Recovery Model?
For data files, the pages moved are logged to the transaction log. The shrink is not one
transaction, it is a lot of smaller transaction. This means that the log can be truncated (emptied)
during the shrink if you are in simple recovery.
And, just in case you didn't read: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stephany Young" <noone@.localhost> wrote in message news:%23aN8wbwFIHA.6068@.TK2MSFTNGP02.phx.gbl...
> Thanks for that Dan.
> Maybe I phrased my original question badly.
> I think the question should have been:
> Is anyone aware of any behavioural differences between executing a 'dbcc
> shrinkdatabase(<databasename>)' on a Full Recovery Model database compared to executing the same
> statement on the same database if it were using Simple Recovery Model?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7AF6D479-8816-43AC-98A4-67F284D3E7D4@.microsoft.com...
>> So you are not aware of any 'gotchas' for the situation I described then?
>> Shrinking files every night is bad because of the performance hit that occurs when files grow
>> back to the needed size during the day. In the FULL recovery model, the logs should be sized to
>> accommodate expected activity between log backups. The goal is to avoid file growth during
>> normal activity while keep file sizes manageable.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:ukG6%23EvFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Yes, yes ... I know all that!
>> So you are not aware of any 'gotchas' for the situation I described then?
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:541DF4B5-1A51-4955-AFEC-421251F6CE2F@.microsoft.com...
>> When you set your database's recovery model to FULL then your passive virtual log files will be
>> held until you backup the database.
>> FULL recovery model is recommended for production environments because then you could restore
>> your database to the point of failure. Also you'd be able to backup Transaction Logs.
>> If you set it's rec. model to SIMPLE then passive vlfs will be truncated at every checkpoint.
>> This recovery model is recommended for test environments and if you use this recovery model
>> then you'd not be able to transaction log files. So you'd be able to restore your database only
>> using your FULL and DIFFERENTIAL backups.
>> Not using Transaction Log File backups is unacceptable for most of the production environments.
>> Also, shrinking a database all the time is not a good idea for every situation. This could be a
>> performance problem as your database would need to expand when it is being used. Expending and
>> shrinking is a resource consuming process.
>> Overview of the Recovery Models:
>> http://msdn2.microsoft.com/en-us/library/ms189275.aspx
>> --
>> Ekrem Önsoy
>> http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
>> MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
>>
>> "Stephany Young" <noone@.localhost> wrote in message
>> news:O9NXxxtFIHA.3672@.TK2MSFTNGP02.phx.gbl...
>> Given the situation of a daily full backup and shrink of a database, does anyone know of any
>> 'gotchas' that are likely to bite one in the backside when the recovery model for the databse
>> is 'Full' as opposed to 'Simple'.
>> For example:
>> use master;
>> backup database <databasename> to disk='<filename>' with description='Full backup of
>> <databasename>',init,skip;
>> dbcc shrinkdatabase(<databasename>)
>>
>>
>

OLTP SQL Memory problem

My OLTP SQL server usually serves ~2700 connections. Twice in the past we've
had memory problem when there is more traffic. In that situation, the number
of connections goes up to 4000. The connections are legitimate and it's been
proved that there are not leaking connections.
I see flood of errors such as the following in the error log. The server has
4GB of memory and /3GB switch is on. SQL server is configured to use the max
memory.
Where do I being to troubleshoot this problem?
Thanks
---
2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
Maximum=245613 Available=245613
2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410 Free=85
Procedures=843
Inram=0 Dirty=23719 Kept=0
I/O=0, Latched=6, Other=303289
2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
Target=338352 Hashed=327015
InternalReservation=516 ExternalReservation=0 Min Free=172
2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
TotalPages=843 InUsePages=389
2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
Reserved=21680
OS Committed=21667
OS In Use=21665
Query Plan=1080 Optimizer=0
General=2578
Utilities=7 Connection=29191
2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
Locks=59
SQLCache=121 Replication=2
LockBytes=2 ServerGlobal=45
Xact=74Kam,
4GB for a system serving 4000 users seems a little low. I don't know how
large your DB is or how much other parts of the memory are being used but
each connection takes up 12 KB + (3 * Network Packet Size). Then you have
all the other objects and the OS that need memory as well. You don't give
any clues as to what kind of memory issues you are having so it's hard to
say if your just low on ram or there is something else going on. I would
suggest you give MS PSS a call and have them work with you directly and they
can suggest some traces and diags to help pinpoint the issue. Without any
other info I would have to say you are probably just short on ram.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
http://www.mssqlserver.com/faq/general-pss.asp MS PSS
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faacbff$1@.news.microsoft.com...
> My OLTP SQL server usually serves ~2700 connections. Twice in the past
we've
> had memory problem when there is more traffic. In that situation, the
number
> of connections goes up to 4000. The connections are legitimate and it's
been
> proved that there are not leaking connections.
>
> I see flood of errors such as the following in the error log. The server
has
> 4GB of memory and /3GB switch is on. SQL server is configured to use the
max
> memory.
>
> Where do I being to troubleshoot this problem?
>
> Thanks
>
>
> ---
> 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0 Waiting=0
> Maximum=245613 Available=245613
> 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
Free=85
> Procedures=843
> Inram=0 Dirty=23719 Kept=0
> I/O=0, Latched=6, Other=303289
> 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> Target=338352 Hashed=327015
> InternalReservation=516 ExternalReservation=0 Min Free=172
> 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> TotalPages=843 InUsePages=389
> 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252 OS
> Reserved=21680
> OS Committed=21667
> OS In Use=21665
> Query Plan=1080 Optimizer=0
> General=2578
> Utilities=7 Connection=29191
> 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> Locks=59
> SQLCache=121 Replication=2
> LockBytes=2 ServerGlobal=45
> Xact=74
>|||Andrew,
Some more information based on my observation
- Memory usuage is at 95%+ range
- CPU usage however seems normal ~ 45%
- There is no other application except SQL server
- SQL does accept any more connection when this issue occurs
- When I tried to log into the profiler, I got a message saying Memory is
Low
- There was a rolling blocking senario on the server too
4000 * [12,288 + (3 * 4,096)] = 98,304,000
Andrew, ~93 MB of memory doesn't look that much to me considering SQL
controls 3GB on that server.
Thanks,
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> Kam,
> 4GB for a system serving 4000 users seems a little low. I don't know how
> large your DB is or how much other parts of the memory are being used but
> each connection takes up 12 KB + (3 * Network Packet Size). Then you have
> all the other objects and the OS that need memory as well. You don't give
> any clues as to what kind of memory issues you are having so it's hard to
> say if your just low on ram or there is something else going on. I would
> suggest you give MS PSS a call and have them work with you directly and
they
> can suggest some traces and diags to help pinpoint the issue. Without any
> other info I would have to say you are probably just short on ram.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kam" <krajabi@.hotmail.com> wrote in message
> news:3faacbff$1@.news.microsoft.com...
> > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> we've
> > had memory problem when there is more traffic. In that situation, the
> number
> > of connections goes up to 4000. The connections are legitimate and it's
> been
> > proved that there are not leaking connections.
> >
> >
> >
> > I see flood of errors such as the following in the error log. The server
> has
> > 4GB of memory and /3GB switch is on. SQL server is configured to use the
> max
> > memory.
> >
> >
> >
> > Where do I being to troubleshoot this problem?
> >
> >
> >
> > Thanks
> >
> >
> >
> >
> >
> > ---
> >
> > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
Waiting=0
> > Maximum=245613 Available=245613
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> Free=85
> > Procedures=843
> >
> > Inram=0 Dirty=23719 Kept=0
> >
> > I/O=0, Latched=6, Other=303289
> >
> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > Target=338352 Hashed=327015
> >
> > InternalReservation=516 ExternalReservation=0 Min Free=172
> >
> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > TotalPages=843 InUsePages=389
> >
> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
OS
> > Reserved=21680
> >
> > OS Committed=21667
> >
> > OS In Use=21665
> >
> > Query Plan=1080 Optimizer=0
> >
> > General=2578
> >
> > Utilities=7 Connection=29191
> >
> > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > Locks=59
> >
> > SQLCache=121 Replication=2
> >
> > LockBytes=2 ServerGlobal=45
> >
> > Xact=74
> >
> >
>|||It may not look like much by itself but it does have to share that with
everything else that takes up memory. If you had blocking issues then you
could also have a ton of locks being held which also use up memory. It may
be the locks that are starting the trouble so you might want to find out why
the blocking happened. But it still sounds like you are right at the edge
of using all the memory effectively for the load and it is likely to happen
again and again.
--
Andrew J. Kelly
SQL Server MVP
"Kam" <krajabi@.hotmail.com> wrote in message
news:3faaeda8$1@.news.microsoft.com...
> Andrew,
> Some more information based on my observation
> - Memory usuage is at 95%+ range
> - CPU usage however seems normal ~ 45%
> - There is no other application except SQL server
> - SQL does accept any more connection when this issue occurs
> - When I tried to log into the profiler, I got a message saying Memory is
> Low
> - There was a rolling blocking senario on the server too
> 4000 * [12,288 + (3 * 4,096)] = 98,304,000
> Andrew, ~93 MB of memory doesn't look that much to me considering SQL
> controls 3GB on that server.
> Thanks,
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
> > Kam,
> >
> > 4GB for a system serving 4000 users seems a little low. I don't know
how
> > large your DB is or how much other parts of the memory are being used
but
> > each connection takes up 12 KB + (3 * Network Packet Size). Then you
have
> > all the other objects and the OS that need memory as well. You don't
give
> > any clues as to what kind of memory issues you are having so it's hard
to
> > say if your just low on ram or there is something else going on. I
would
> > suggest you give MS PSS a call and have them work with you directly and
> they
> > can suggest some traces and diags to help pinpoint the issue. Without
any
> > other info I would have to say you are probably just short on ram.
> >
> > http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL
Support
> > http://www.mssqlserver.com/faq/general-pss.asp MS PSS
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kam" <krajabi@.hotmail.com> wrote in message
> > news:3faacbff$1@.news.microsoft.com...
> > > My OLTP SQL server usually serves ~2700 connections. Twice in the past
> > we've
> > > had memory problem when there is more traffic. In that situation, the
> > number
> > > of connections goes up to 4000. The connections are legitimate and
it's
> > been
> > > proved that there are not leaking connections.
> > >
> > >
> > >
> > > I see flood of errors such as the following in the error log. The
server
> > has
> > > 4GB of memory and /3GB switch is on. SQL server is configured to use
the
> > max
> > > memory.
> > >
> > >
> > >
> > > Where do I being to troubleshoot this problem?
> > >
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> > >
> >
> ---
> > >
> > > 2003-11-04 13:59:44.12 spid3100 Query Memory Manager: Grants=0
> Waiting=0
> > > Maximum=245613 Available=245613
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Distribution: Stolen=10410
> > Free=85
> > > Procedures=843
> > >
> > > Inram=0 Dirty=23719 Kept=0
> > >
> > > I/O=0, Latched=6, Other=303289
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Buffer Counts: Commited=338352
> > > Target=338352 Hashed=327015
> > >
> > > InternalReservation=516 ExternalReservation=0 Min Free=172
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Procedure Cache: TotalProcs=283
> > > TotalPages=843 InUsePages=389
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory Manager: Stolen=11252
> OS
> > > Reserved=21680
> > >
> > > OS Committed=21667
> > >
> > > OS In Use=21665
> > >
> > > Query Plan=1080 Optimizer=0
> > >
> > > General=2578
> > >
> > > Utilities=7 Connection=29191
> > >
> > > 2003-11-04 13:59:44.14 spid3167 Global Memory Objects: Resource=2303
> > > Locks=59
> > >
> > > SQLCache=121 Replication=2
> > >
> > > LockBytes=2 ServerGlobal=45
> > >
> > > Xact=74
> > >
> > >
> >
> >
>|||under perfmon, process object, sqlservr instance, what is
the Virtual Bytes, Working Set,
also get the Virtual Memory size from task manager for the
SqlServr.exe process.
under perfmon SQLServer Buffer Manager, what is the total
pages?
One common problem when SQL Server use 2 or 3GB of memory
is that too much memory and virtual address space gets
allocated to buffers, leaving inadequate address space for
other data structures.
While SQL Server will release buffers to free up memory
for other applications, it does not appear to free up
buffers to make more address space available for other SQL
Server internal requests
By default, SQL reserves 256M of address space for other
than buffers, that's why you frequently see 1.75GB
physical memory used for standard and 2.75GB for /3GB mode,
almost all of the address space is used for data buffers,
and most of the reserved 256M is unused.
in your case, it could be that you need more than 275M but
SQL has already allocated 2.75GB for buffers, and there no
more address space for other uses
provide the above info, but you can also try the -gxxx
startup parameter with say -g384 leaving 384MB for other
stuff
>--Original Message--
>Andrew,
>Some more information based on my observation
>- Memory usuage is at 95%+ range
>- CPU usage however seems normal ~ 45%
>- There is no other application except SQL server
>- SQL does accept any more connection when this issue
occurs
>- When I tried to log into the profiler, I got a message
saying Memory is
>Low
>- There was a rolling blocking senario on the server too
>4000 * [12,288 + (3 * 4,096)] = 98,304,000
>Andrew, ~93 MB of memory doesn't look that much to me
considering SQL
>controls 3GB on that server.
>Thanks,
>
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in
message
>news:uNFR#LMpDHA.1656@.tk2msftngp13.phx.gbl...
>> Kam,
>> 4GB for a system serving 4000 users seems a little
low. I don't know how
>> large your DB is or how much other parts of the memory
are being used but
>> each connection takes up 12 KB + (3 * Network Packet
Size). Then you have
>> all the other objects and the OS that need memory as
well. You don't give
>> any clues as to what kind of memory issues you are
having so it's hard to
>> say if your just low on ram or there is something else
going on. I would
>> suggest you give MS PSS a call and have them work with
you directly and
>they
>> can suggest some traces and diags to help pinpoint the
issue. Without any
>> other info I would have to say you are probably just
short on ram.
>> http://support.microsoft.com/default.aspx?scid=fh;EN-
US;sql SQL Support
>> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
>> --
>> Andrew J. Kelly
>> SQL Server MVP
>>
>> "Kam" <krajabi@.hotmail.com> wrote in message
>> news:3faacbff$1@.news.microsoft.com...
>> > My OLTP SQL server usually serves ~2700 connections.
Twice in the past
>> we've
>> > had memory problem when there is more traffic. In
that situation, the
>> number
>> > of connections goes up to 4000. The connections are
legitimate and it's
>> been
>> > proved that there are not leaking connections.
>> >
>> >
>> >
>> > I see flood of errors such as the following in the
error log. The server
>> has
>> > 4GB of memory and /3GB switch is on. SQL server is
configured to use the
>> max
>> > memory.
>> >
>> >
>> >
>> > Where do I being to troubleshoot this problem?
>> >
>> >
>> >
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> > ---
--
>> >
>> > 2003-11-04 13:59:44.12 spid3100 Query Memory
Manager: Grants=0
>Waiting=0
>> > Maximum=245613 Available=245613
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer
Distribution: Stolen=10410
>> Free=85
>> > Procedures=843
>> >
>> > Inram=0 Dirty=23719 Kept=0
>> >
>> > I/O=0, Latched=6, Other=303289
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Buffer Counts:
Commited=338352
>> > Target=338352 Hashed=327015
>> >
>> > InternalReservation=516
ExternalReservation=0 Min Free=172
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Procedure Cache:
TotalProcs=283
>> > TotalPages=843 InUsePages=389
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Dynamic Memory
Manager: Stolen=11252
>OS
>> > Reserved=21680
>> >
>> > OS Committed=21667
>> >
>> > OS In Use=21665
>> >
>> > Query Plan=1080 Optimizer=0
>> >
>> > General=2578
>> >
>> > Utilities=7 Connection=29191
>> >
>> > 2003-11-04 13:59:44.14 spid3167 Global Memory
Objects: Resource=2303
>> > Locks=59
>> >
>> > SQLCache=121 Replication=2
>> >
>> > LockBytes=2 ServerGlobal=45
>> >
>> > Xact=74
>> >
>> >
>>
>
>.
>

OLE DB provider 'MSDAORA' reported an error.(SQL Server 7399)

Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
MarcusWe recently came across a similar situation. You could try setting your
environment before the execution:
SET IMPLICT_TRANSACTIONS OFF
SET XACT_ABORT ON
In our case, the vendor had coded a BAD trigger against the destination
table. It was bad in the sense that it was coded row based instead of set
based; so, it returned an error whenever the number of records attempted to
insert were more than one, but the Distributed Transaction Coordinator
returned the error message you received.
Sincerely,
Anthony Thomas
"Marcus" <Marcus@.discussions.microsoft.com> wrote in message
news:D9CF72F5-BE5B-41A5-923B-36BBE0F93A52@.microsoft.com...
Hi all
here's the situation:
The Error:
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error. (SQL Server 7399)OLE DB error trace [OLE/DB
Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x80004005
The error araises when quering the remote oracle database. The error does
NOT happen every time, but sometimes (20 times a day with 10 queries am
minute)
The Databases:
MSSQL2000 (SP3a) with link to Oracle9i Enterprise Edition 9.2.0.4.0 64bit
Production.
The call using open query syntax in a function looks like (on MSSQL):
'insert into SAP_ORGANISATION_Temp select ''' + @.pDataID + ''', * from
openquery (SAP_VM, ''select * from ARSREMEDY.ZORGANISATION_FULL ' + @.sqlq +
''')'
where: SAP_ORGANISATION_Temp is a local table, SAP_VM is the linked server,
ARSREMEDY is the user, ZORGANISATION_FULL is a view on the oracle side with
some join, @.sqlq is some condition
Additional info:
oracle client is: 8.1.7.0.0
Windows Registry says:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"
SQLNET ComponentVersion: 2.60.6526.2 (at least that's what the regestry
says)
msdaora.dll File Version: 2.71.9030.0
Hope this is any good to someone.
Any help appreciated
Marcus

OLE DB DataSource w\ stored procedure not populating column metadata

I'm having some issues getting OLE DB Data Sources to work w\ stored procs in SSIS. Here's the situation.

I have an OLE DB Data Source set up to call a stored proc w\ no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.

I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.

Any help would be much appreciated....

I believe that there are known problems around using SPs in OLE DB Source components. Its something to do with requiring that the query that returns the data is the first statement in the proc. This enables SSIS to understand the metadata of the returned result set.

I know this doesn't help you in your case though. I do not know if there is a workaround or not having never faced this problem before myself. I hope what I've said above goes some way to helping though.

-Jamie

|||I think OLE DB Command will execute the Store proc|||Try converting the sp to a multi-statement table-valued function, giving Data Access Mode for your OLE DB source as SQL Statement and specifying the statement SELECT * FROM your_table_valued_function(); the wizard will gussy the SQL up