i want to prevent some referential integrity scripts to be deployed at the
subscriber *before* the snapshot is applied , is there a way to interfere
with initial snapshot programmatically?
thanks in advance
There are a couple of ways. You can use the Pre- and Post snapshot commands
to run extra scripts eitehr before or after (or both). You can stop the
distribution task and hand-edit the snapshot after it is created. Note that
the second method will have to be redone on every new snapshot.
As for Referential Integrity, they are not automatically applied since you
may choose to replicate child tables without the parent tables. You can add
them on, but it is not recommended since they tend to interfere with
snapshot application.
Geoff N. Hiten
Microsoft SQL Server MVP
"uykusuz" <yb> wrote in message
news:ewKbjm9gFHA.3568@.TK2MSFTNGP10.phx.gbl...
>i want to prevent some referential integrity scripts to be deployed at the
> subscriber *before* the snapshot is applied , is there a way to
> interfere
> with initial snapshot programmatically?
>
> thanks in advance
>
Showing posts with label deployed. Show all posts
Showing posts with label deployed. Show all posts
Monday, March 19, 2012
Thursday, February 16, 2012
Append @ExecutionTime to the Report File Name
I am trying to append the date to each snapshot of the report that is
deployed on a file share (e.g. RPT001 5-25-2005.xls).
I tried RPT001 @.ExecutionTime in the File Name of the subscription
definition but this deploys the report as: RPT001 @.ExecutionTime.xls
Anybody any ideas?
Thanks,
FlorinHi,
We would also like to do this - did you resolve?
Thanks,
--
Matt
"Florin" wrote:
> I am trying to append the date to each snapshot of the report that is
> deployed on a file share (e.g. RPT001 5-25-2005.xls).
> I tried RPT001 @.ExecutionTime in the File Name of the subscription
> definition but this deploys the report as: RPT001 @.ExecutionTime.xls
> Anybody any ideas?
> Thanks,
> Florin|||I have not figured how to do that.
"Matt" wrote:
> Hi,
> We would also like to do this - did you resolve?
> Thanks,
> --
> Matt
>
> "Florin" wrote:
> > I am trying to append the date to each snapshot of the report that is
> > deployed on a file share (e.g. RPT001 5-25-2005.xls).
> >
> > I tried RPT001 @.ExecutionTime in the File Name of the subscription
> > definition but this deploys the report as: RPT001 @.ExecutionTime.xls
> >
> > Anybody any ideas?
> >
> > Thanks,
> > Florin
deployed on a file share (e.g. RPT001 5-25-2005.xls).
I tried RPT001 @.ExecutionTime in the File Name of the subscription
definition but this deploys the report as: RPT001 @.ExecutionTime.xls
Anybody any ideas?
Thanks,
FlorinHi,
We would also like to do this - did you resolve?
Thanks,
--
Matt
"Florin" wrote:
> I am trying to append the date to each snapshot of the report that is
> deployed on a file share (e.g. RPT001 5-25-2005.xls).
> I tried RPT001 @.ExecutionTime in the File Name of the subscription
> definition but this deploys the report as: RPT001 @.ExecutionTime.xls
> Anybody any ideas?
> Thanks,
> Florin|||I have not figured how to do that.
"Matt" wrote:
> Hi,
> We would also like to do this - did you resolve?
> Thanks,
> --
> Matt
>
> "Florin" wrote:
> > I am trying to append the date to each snapshot of the report that is
> > deployed on a file share (e.g. RPT001 5-25-2005.xls).
> >
> > I tried RPT001 @.ExecutionTime in the File Name of the subscription
> > definition but this deploys the report as: RPT001 @.ExecutionTime.xls
> >
> > Anybody any ideas?
> >
> > Thanks,
> > Florin
AppDomain unloads due to memory pressure.
We deployed a new SQL Server 2005 production server 6 weeks ago. This
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.
On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/78717b6a0452847a/ad371baa9fa12a4d?hl=en&lnk=st&q=AppDomain+is+marke d+for+unload+due+to+memory+pressure.#ad371baa9fa12 a4d[/url]
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.
|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> [...]
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.microsoft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.com/group/microsoft.public.sqlserver.server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.
On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/78717b6a0452847a/ad371baa9fa12a4d?hl=en&lnk=st&q=AppDomain+is+marke d+for+unload+due+to+memory+pressure.#ad371baa9fa12 a4d[/url]
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.
|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> [...]
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.microsoft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.com/group/microsoft.public.sqlserver.server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad
Monday, February 13, 2012
AppDomain unloads due to memory pressure.
We deployed a new SQL Server 2005 production server 6 weeks ago. This
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.d
bo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[
;runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
http://groups.google.com/group/micr...d371baa9fa12a4d
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
>
> [...]
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.micro
soft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.co
m/gr...server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.d
bo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[
;runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
http://groups.google.com/group/micr...d371baa9fa12a4d
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
>
> [...]
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.micro
soft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.co
m/gr...server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad
AppDomain unloads due to memory pressure.
We deployed a new SQL Server 2005 production server 6 weeks ago. This
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/78717b6a0452847a/ad371baa9fa12a4d?hl=en&lnk=st&q=AppDomain+is+marked+for+unload+due+to+memory+pressure.#ad371baa9fa12a4d
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> > We deployed a new SQL Server 2005 production server 6 weeks ago. This
> > server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> > servermemoryand /3GB switch.
> [...]
> > Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.microsoft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.com/group/microsoft.public.sqlserver.server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad
server has 8 GB memory and is configured with AWE enabled, 6656 MB max
server memory and /3GB switch.
Twice for the last 2 weeks we had a situation when CPU time went up
and all users reported a significant system slowdown.
Every 30 seconds these messages were logged in the error log:
AppDomain 29 (master.dbo[runtime].28) is marked for unload due to
memory pressure.
AppDomain 29 (master.dbo[runtime].28) unloaded.
AppDomain 30 (master.dbo[runtime].29) created.
sys.dm_exec_cached_plans view had only few records, so I assume the
procedure cache was flushed with every AppDomain unload.
This is the memory snapshot at the time:
Memory Manager KB
-- --
VM Reserved 2802932
VM Committed 141204
AWE Allocated 6821176
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2798772
VM Committed 137196
AWE Allocated 6821176
MultiPage Allocator 33384
SinglePage Allocator 64904
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2612332
VM Committed 69032
AWE Allocated 6821176
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1280
MultiPage Allocator 1488
Buffer Distribution Buffers
-- --
Stolen 4568
Free 40186
Cached 3545
Database (clean) 699081
Database (dirty) 104587
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 803669
Stolen Potential 293455
External Reservation 0
Min Free 1872
Visible 317440
Available Paging File 578065
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 7
TotalPages 92
InUsePages 42
This is the memory snapshot during the regular load:
Memory Manager KB
-- --
VM Reserved 2795508
VM Committed 130708
AWE Allocated 6815904
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
-- --
VM Reserved 2791348
VM Committed 126700
AWE Allocated 6815904
MultiPage Allocator 21296
SinglePage Allocator 1506648
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
--- --
VM Reserved 2620524
VM Committed 69032
AWE Allocated 6815904
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 2040
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 135936
VM Committed 23900
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 880
MultiPage Allocator 1232
Buffer Distribution Buffers
-- --
Stolen 8073
Free 11148
Cached 180258
Database (clean) 627780
Database (dirty) 24708
I/O 0
Latched 1
(7 row(s) affected)
Buffer Counts Buffers
-- --
Committed 851968
Target 851968
Hashed 652489
Stolen Potential 114209
External Reservation 0
Min Free 1008
Visible 318464
Available Paging File 581447
(8 row(s) affected)
Procedure Cache Value
-- --
TotalProcs 29506
TotalPages 176205
InUsePages 2795
Both times the server reboot has fixed the problem.
I'm just wondering if this is an indication of CLR related memory
leaking or the problem is with SQL Server 2005 memory allocation/
management that forces AppDomain unloads?
Do I still need to reboot the server in order to fix it or I can just
run DBCC FREESYSTEMCACHE?
Is there a way to prevent this from happening?
Thanks.On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> We deployed a new SQL Server 2005 production server 6 weeks ago. This
> server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> servermemoryand /3GB switch.
[...]
> Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
I am having something similar happen.
I found this, which doesn't directly apply to me (we're on SP2, so it
should be fixed), but we are getting those messages as well.
http://support.microsoft.com/kb/917271
However, at least one other person is having this happen:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/78717b6a0452847a/ad371baa9fa12a4d?hl=en&lnk=st&q=AppDomain+is+marked+for+unload+due+to+memory+pressure.#ad371baa9fa12a4d
It says user defined datatypes and CLR, and mine has a specific
database name, so that's where I'm looking first. Will post more as I
find it.|||On Dec 4, 3:03 pm, bour...@.gmail.com wrote:
> On Nov 14, 9:39 am, vlad <vesmu...@.yahoo.com> wrote:
> > We deployed a new SQL Server 2005 production server 6 weeks ago. This
> > server has 8 GBmemoryand is configured with AWE enabled, 6656 MB max
> > servermemoryand /3GB switch.
> [...]
> > Every 30 seconds these messages were logged in the error log:AppDomain29 (master.dbo[runtime].28) ismarkedforunloadduetomemorypressure.AppDomain29 (master.dbo[runtime].28) unloaded.AppDomain30 (master.dbo[runtime].29) created.
> I am having something similar happen.
> I found this, which doesn't directly apply to me (we're on SP2, so it
> should be fixed), but we are getting those messages as well.http://support.microsoft.com/kb/917271
> However, at least one other person is having this happen:http://groups.google.com/group/microsoft.public.sqlserver.server/brow...
> It says user defined datatypes and CLR, and mine has a specific
> database name, so that's where I'm looking first. Will post more as I
> find it.
We are also running SP2. The message is related to master since the
CLR assembly is created in master. It happens once in 3 days now
during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only
reboot.
Opened a ticket with Microsoft but they have no idea what's happening.
Asked me to run PSSDiag and send a result to them.
These are the performance counters related to memory when the issue
occurs:
Memory: Available Bytes: 602,260,000
Memory: Pages/sec: 0
Process: Working Set Total: 778,264,576
Process: Working Set SQL Server: 262,144,000
SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851
SQL Server: Buffer Manager: Total Pages: 851,968
SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744
MEMORYCLERK_SQLCLR (Total) KB
--- --
VM Reserved 133760
VM Committed 19572
There is no any other processes running on the server at the time.
Don't see any memory pressure.
This is really frustrating since it was running on a less powerful
hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no
issues.
I really need help with this one.
Thanks,
Vlad
Subscribe to:
Posts (Atom)