We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
Windows 2003.
We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Max
Server Memory set to 6144.
After a recent application upgrade, we were running into memory pressure due
to limited space in VAS. Our buffer pool remained in good shape. We increased
VAS from 256 to 512 and we are no longer having any issues.
During this troubleshooting period, we began toying with the idea of moving
from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
process of being set up. With the same 8GB RAM on the proposed 64 bit server,
is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary?
What is the default size of VAS in 64 bit?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200705/1
/3gb and AWE is no longer required with x64
max memory option still here.
Windows x64 is required.
and sure, x64 version is really helpful for the memory management.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200705/1
>
|||> What is the default size of VAS in 64 bit?
The user mode addressable space is 8TB.
Linchi
"cbrichards via droptable.com" wrote:
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure due
> to limited space in VAS. Our buffer pool remained in good shape. We increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200705/1
>
|||If you were running into VAS issues you must be running apps other than SQL
Server on the same server. If this is the case you should always leave
enough free memory for those other apps. The problem with 32 bit and AWE is
that it is not dynamic so when the other app or the OS needs more than the
2GB left you are forced to page. With x64 bit it can be dynamic and there is
no need for AWE or /3GB. But that still won't get around the issue that you
probably need more memory for the other apps.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200705/1
>
|||You are correct Andrew, the VAS issues came into play when we deployed CLR.
To clarify my understanding then, with 64 bit, when I have 8GB RAM, and Max
Server Memory = 6144:
1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic, does
it then borrow from the 6GB in the buffer pool?
2. Regarding the VAS issues, in 32 bit the resolution was to increase the
default MemToLeave from 256 to 512 using startup parameter -g. Is this
startup parameter still needed in 64 bit, or in other words, is 256 still the
default in 64 bit?
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you were running into VAS issues you must be running apps other than SQL
>Server on the same server. If this is the case you should always leave
>enough free memory for those other apps. The problem with 32 bit and AWE is
>that it is not dynamic so when the other app or the OS needs more than the
>2GB left you are forced to page. With x64 bit it can be dynamic and there is
>no need for AWE or /3GB. But that still won't get around the issue that you
>probably need more memory for the other apps.
>[quoted text clipped - 19 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200705/1
|||OK you have VAS confused with MemToLeave. The - g option is for MemToLeave
and not VAS. MemToLeave is for allocations that require more than 8K of
contiguous memory. I can see where the CLR would require more MemToLeave
than the default.
See if this helps:
http://blogs.msdn.com/khen1234/archive/2005/10/12/480292.aspx
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71e09d8645e08@.uwe...
> You are correct Andrew, the VAS issues came into play when we deployed
> CLR.
> To clarify my understanding then, with 64 bit, when I have 8GB RAM, and
> Max
> Server Memory = 6144:
> 1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic,
> does
> it then borrow from the 6GB in the buffer pool?
> 2. Regarding the VAS issues, in 32 bit the resolution was to increase the
> default MemToLeave from 256 to 512 using startup parameter -g. Is this
> startup parameter still needed in 64 bit, or in other words, is 256 still
> the
> default in 64 bit?
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200705/1
>
sql
Showing posts with label lock. Show all posts
Showing posts with label lock. Show all posts
Sunday, March 25, 2012
32 bit vs. 64 bit options
We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
Windows 2003.
We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Max
Server Memory set to 6144.
After a recent application upgrade, we were running into memory pressure due
to limited space in VAS. Our buffer pool remained in good shape. We increased
VAS from 256 to 512 and we are no longer having any issues.
During this troubleshooting period, we began toying with the idea of moving
from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
process of being set up. With the same 8GB RAM on the proposed 64 bit server,
is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary?
What is the default size of VAS in 64 bit?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1/3gb and AWE is no longer required with x64
max memory option still here.
Windows x64 is required.
and sure, x64 version is really helpful for the memory management.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>|||> What is the default size of VAS in 64 bit?
The user mode addressable space is 8TB.
Linchi
"cbrichards via SQLMonster.com" wrote:
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure due
> to limited space in VAS. Our buffer pool remained in good shape. We increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>|||If you were running into VAS issues you must be running apps other than SQL
Server on the same server. If this is the case you should always leave
enough free memory for those other apps. The problem with 32 bit and AWE is
that it is not dynamic so when the other app or the OS needs more than the
2GB left you are forced to page. With x64 bit it can be dynamic and there is
no need for AWE or /3GB. But that still won't get around the issue that you
probably need more memory for the other apps.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>|||You are correct Andrew, the VAS issues came into play when we deployed CLR.
To clarify my understanding then, with 64 bit, when I have 8GB RAM, and Max
Server Memory = 6144:
1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic, does
it then borrow from the 6GB in the buffer pool?
2. Regarding the VAS issues, in 32 bit the resolution was to increase the
default MemToLeave from 256 to 512 using startup parameter -g. Is this
startup parameter still needed in 64 bit, or in other words, is 256 still the
default in 64 bit?
Andrew J. Kelly wrote:
>If you were running into VAS issues you must be running apps other than SQL
>Server on the same server. If this is the case you should always leave
>enough free memory for those other apps. The problem with 32 bit and AWE is
>that it is not dynamic so when the other app or the OS needs more than the
>2GB left you are forced to page. With x64 bit it can be dynamic and there is
>no need for AWE or /3GB. But that still won't get around the issue that you
>probably need more memory for the other apps.
>> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
>> Windows 2003.
>[quoted text clipped - 19 lines]
>> necessary?
>> What is the default size of VAS in 64 bit?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1|||OK you have VAS confused with MemToLeave. The - g option is for MemToLeave
and not VAS. MemToLeave is for allocations that require more than 8K of
contiguous memory. I can see where the CLR would require more MemToLeave
than the default.
See if this helps:
http://blogs.msdn.com/khen1234/archive/2005/10/12/480292.aspx
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:71e09d8645e08@.uwe...
> You are correct Andrew, the VAS issues came into play when we deployed
> CLR.
> To clarify my understanding then, with 64 bit, when I have 8GB RAM, and
> Max
> Server Memory = 6144:
> 1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic,
> does
> it then borrow from the 6GB in the buffer pool?
> 2. Regarding the VAS issues, in 32 bit the resolution was to increase the
> default MemToLeave from 256 to 512 using startup parameter -g. Is this
> startup parameter still needed in 64 bit, or in other words, is 256 still
> the
> default in 64 bit?
> Andrew J. Kelly wrote:
>>If you were running into VAS issues you must be running apps other than
>>SQL
>>Server on the same server. If this is the case you should always leave
>>enough free memory for those other apps. The problem with 32 bit and AWE
>>is
>>that it is not dynamic so when the other app or the OS needs more than the
>>2GB left you are forced to page. With x64 bit it can be dynamic and there
>>is
>>no need for AWE or /3GB. But that still won't get around the issue that
>>you
>>probably need more memory for the other apps.
>> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2,
>> on
>> Windows 2003.
>>[quoted text clipped - 19 lines]
>> necessary?
>> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>
Windows 2003.
We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Max
Server Memory set to 6144.
After a recent application upgrade, we were running into memory pressure due
to limited space in VAS. Our buffer pool remained in good shape. We increased
VAS from 256 to 512 and we are no longer having any issues.
During this troubleshooting period, we began toying with the idea of moving
from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
process of being set up. With the same 8GB RAM on the proposed 64 bit server,
is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary?
What is the default size of VAS in 64 bit?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1/3gb and AWE is no longer required with x64
max memory option still here.
Windows x64 is required.
and sure, x64 version is really helpful for the memory management.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>|||> What is the default size of VAS in 64 bit?
The user mode addressable space is 8TB.
Linchi
"cbrichards via SQLMonster.com" wrote:
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure due
> to limited space in VAS. Our buffer pool remained in good shape. We increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>|||If you were running into VAS issues you must be running apps other than SQL
Server on the same server. If this is the case you should always leave
enough free memory for those other apps. The problem with 32 bit and AWE is
that it is not dynamic so when the other app or the OS needs more than the
2GB left you are forced to page. With x64 bit it can be dynamic and there is
no need for AWE or /3GB. But that still won't get around the issue that you
probably need more memory for the other apps.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>|||You are correct Andrew, the VAS issues came into play when we deployed CLR.
To clarify my understanding then, with 64 bit, when I have 8GB RAM, and Max
Server Memory = 6144:
1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic, does
it then borrow from the 6GB in the buffer pool?
2. Regarding the VAS issues, in 32 bit the resolution was to increase the
default MemToLeave from 256 to 512 using startup parameter -g. Is this
startup parameter still needed in 64 bit, or in other words, is 256 still the
default in 64 bit?
Andrew J. Kelly wrote:
>If you were running into VAS issues you must be running apps other than SQL
>Server on the same server. If this is the case you should always leave
>enough free memory for those other apps. The problem with 32 bit and AWE is
>that it is not dynamic so when the other app or the OS needs more than the
>2GB left you are forced to page. With x64 bit it can be dynamic and there is
>no need for AWE or /3GB. But that still won't get around the issue that you
>probably need more memory for the other apps.
>> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
>> Windows 2003.
>[quoted text clipped - 19 lines]
>> necessary?
>> What is the default size of VAS in 64 bit?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1|||OK you have VAS confused with MemToLeave. The - g option is for MemToLeave
and not VAS. MemToLeave is for allocations that require more than 8K of
contiguous memory. I can see where the CLR would require more MemToLeave
than the default.
See if this helps:
http://blogs.msdn.com/khen1234/archive/2005/10/12/480292.aspx
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:71e09d8645e08@.uwe...
> You are correct Andrew, the VAS issues came into play when we deployed
> CLR.
> To clarify my understanding then, with 64 bit, when I have 8GB RAM, and
> Max
> Server Memory = 6144:
> 1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic,
> does
> it then borrow from the 6GB in the buffer pool?
> 2. Regarding the VAS issues, in 32 bit the resolution was to increase the
> default MemToLeave from 256 to 512 using startup parameter -g. Is this
> startup parameter still needed in 64 bit, or in other words, is 256 still
> the
> default in 64 bit?
> Andrew J. Kelly wrote:
>>If you were running into VAS issues you must be running apps other than
>>SQL
>>Server on the same server. If this is the case you should always leave
>>enough free memory for those other apps. The problem with 32 bit and AWE
>>is
>>that it is not dynamic so when the other app or the OS needs more than the
>>2GB left you are forced to page. With x64 bit it can be dynamic and there
>>is
>>no need for AWE or /3GB. But that still won't get around the issue that
>>you
>>probably need more memory for the other apps.
>> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2,
>> on
>> Windows 2003.
>>[quoted text clipped - 19 lines]
>> necessary?
>> What is the default size of VAS in 64 bit?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200705/1
>
32 bit vs. 64 bit options
We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
Windows 2003.
We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Ma
x
Server Memory set to 6144.
After a recent application upgrade, we were running into memory pressure due
to limited space in VAS. Our buffer pool remained in good shape. We increase
d
VAS from 256 to 512 and we are no longer having any issues.
During this troubleshooting period, we began toying with the idea of moving
from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
process of being set up. With the same 8GB RAM on the proposed 64 bit server
,
is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary
?
What is the default size of VAS in 64 bit?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200705/1/3gb and AWE is no longer required with x64
max memory option still here.
Windows x64 is required.
and sure, x64 version is really helpful for the memory management.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>|||> What is the default size of VAS in 64 bit?
The user mode addressable space is 8TB.
Linchi
"cbrichards via droptable.com" wrote:
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure d
ue
> to limited space in VAS. Our buffer pool remained in good shape. We increa
sed
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of movin
g
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit serv
er,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE, M
ax
> Server Memory) remain the same in 64 bit? Is the /3GB switch still necessa
ry?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>|||If you were running into VAS issues you must be running apps other than SQL
Server on the same server. If this is the case you should always leave
enough free memory for those other apps. The problem with 32 bit and AWE is
that it is not dynamic so when the other app or the OS needs more than the
2GB left you are forced to page. With x64 bit it can be dynamic and there is
no need for AWE or /3GB. But that still won't get around the issue that you
probably need more memory for the other apps.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>|||You are correct Andrew, the VAS issues came into play when we deployed CLR.
To clarify my understanding then, with 64 bit, when I have 8GB RAM, and Max
Server Memory = 6144:
1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic, doe
s
it then borrow from the 6GB in the buffer pool?
2. Regarding the VAS issues, in 32 bit the resolution was to increase the
default MemToLeave from 256 to 512 using startup parameter -g. Is this
startup parameter still needed in 64 bit, or in other words, is 256 still th
e
default in 64 bit?
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you were running into VAS issues you must be running apps other than SQL
>Server on the same server. If this is the case you should always leave
>enough free memory for those other apps. The problem with 32 bit and AWE i
s
>that it is not dynamic so when the other app or the OS needs more than the
>2GB left you are forced to page. With x64 bit it can be dynamic and there i
s
>no need for AWE or /3GB. But that still won't get around the issue that yo
u
>probably need more memory for the other apps.
>
>[quoted text clipped - 19 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200705/1|||OK you have VAS confused with MemToLeave. The - g option is for MemToLeave
and not VAS. MemToLeave is for allocations that require more than 8K of
contiguous memory. I can see where the CLR would require more MemToLeave
than the default.
See if this helps:
http://blogs.msdn.com/khen1234/arch.../12/480292.aspx
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71e09d8645e08@.uwe...
> You are correct Andrew, the VAS issues came into play when we deployed
> CLR.
> To clarify my understanding then, with 64 bit, when I have 8GB RAM, and
> Max
> Server Memory = 6144:
> 1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic,
> does
> it then borrow from the 6GB in the buffer pool?
> 2. Regarding the VAS issues, in 32 bit the resolution was to increase the
> default MemToLeave from 256 to 512 using startup parameter -g. Is this
> startup parameter still needed in 64 bit, or in other words, is 256 still
> the
> default in 64 bit?
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>
Windows 2003.
We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and Ma
x
Server Memory set to 6144.
After a recent application upgrade, we were running into memory pressure due
to limited space in VAS. Our buffer pool remained in good shape. We increase
d
VAS from 256 to 512 and we are no longer having any issues.
During this troubleshooting period, we began toying with the idea of moving
from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
process of being set up. With the same 8GB RAM on the proposed 64 bit server
,
is it desirable to have the sp_Configure options in 32 bit (such as AWE, Max
Server Memory) remain the same in 64 bit? Is the /3GB switch still necessary
?
What is the default size of VAS in 64 bit?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200705/1/3gb and AWE is no longer required with x64
max memory option still here.
Windows x64 is required.
and sure, x64 version is really helpful for the memory management.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>|||> What is the default size of VAS in 64 bit?
The user mode addressable space is 8TB.
Linchi
"cbrichards via droptable.com" wrote:
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure d
ue
> to limited space in VAS. Our buffer pool remained in good shape. We increa
sed
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of movin
g
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit serv
er,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE, M
ax
> Server Memory) remain the same in 64 bit? Is the /3GB switch still necessa
ry?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>|||If you were running into VAS issues you must be running apps other than SQL
Server on the same server. If this is the case you should always leave
enough free memory for those other apps. The problem with 32 bit and AWE is
that it is not dynamic so when the other app or the OS needs more than the
2GB left you are forced to page. With x64 bit it can be dynamic and there is
no need for AWE or /3GB. But that still won't get around the issue that you
probably need more memory for the other apps.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71d7704e288d2@.uwe...
> We are currently running 32 bit SQL Server 2005, Standard Edition, SP2, on
> Windows 2003.
> We have 8GB RAM, with AWE enabled, Lock Pages in Memory, /3GB switch, and
> Max
> Server Memory set to 6144.
> After a recent application upgrade, we were running into memory pressure
> due
> to limited space in VAS. Our buffer pool remained in good shape. We
> increased
> VAS from 256 to 512 and we are no longer having any issues.
> During this troubleshooting period, we began toying with the idea of
> moving
> from SQL Server 32 bit to 64 bit. We have a 64 bit test environment in the
> process of being set up. With the same 8GB RAM on the proposed 64 bit
> server,
> is it desirable to have the sp_Configure options in 32 bit (such as AWE,
> Max
> Server Memory) remain the same in 64 bit? Is the /3GB switch still
> necessary?
> What is the default size of VAS in 64 bit?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>|||You are correct Andrew, the VAS issues came into play when we deployed CLR.
To clarify my understanding then, with 64 bit, when I have 8GB RAM, and Max
Server Memory = 6144:
1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic, doe
s
it then borrow from the 6GB in the buffer pool?
2. Regarding the VAS issues, in 32 bit the resolution was to increase the
default MemToLeave from 256 to 512 using startup parameter -g. Is this
startup parameter still needed in 64 bit, or in other words, is 256 still th
e
default in 64 bit?
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you were running into VAS issues you must be running apps other than SQL
>Server on the same server. If this is the case you should always leave
>enough free memory for those other apps. The problem with 32 bit and AWE i
s
>that it is not dynamic so when the other app or the OS needs more than the
>2GB left you are forced to page. With x64 bit it can be dynamic and there i
s
>no need for AWE or /3GB. But that still won't get around the issue that yo
u
>probably need more memory for the other apps.
>
>[quoted text clipped - 19 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200705/1|||OK you have VAS confused with MemToLeave. The - g option is for MemToLeave
and not VAS. MemToLeave is for allocations that require more than 8K of
contiguous memory. I can see where the CLR would require more MemToLeave
than the default.
See if this helps:
http://blogs.msdn.com/khen1234/arch.../12/480292.aspx
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:71e09d8645e08@.uwe...
> You are correct Andrew, the VAS issues came into play when we deployed
> CLR.
> To clarify my understanding then, with 64 bit, when I have 8GB RAM, and
> Max
> Server Memory = 6144:
> 1. If CLR or OS needs more than the 2GB left, then if 64 bit is dynamic,
> does
> it then borrow from the 6GB in the buffer pool?
> 2. Regarding the VAS issues, in 32 bit the resolution was to increase the
> default MemToLeave from 256 to 512 using startup parameter -g. Is this
> startup parameter still needed in 64 bit, or in other words, is 256 still
> the
> default in 64 bit?
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200705/1
>
Friday, February 24, 2012
2005 Shared Nothing
I though 2005 was going to have a feature where you could face multiple
instances of a DB towards your customers with all the lock and transaction
bits accomplished in the background? The most common question I get about
Clustering is whether it is HA or really is a scalibility solution as well.
Avokia makes a piece that effectively accomplishes this.Are you referring to database snapshots?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
well.
> Avokia makes a piece that effectively accomplishes this.|||Hi
Clustering is HA and no Windows Clustering implantation is intended for
scale out.
SQL server 2005 has never has a scale out feature in it's feature list.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
> well.
> Avokia makes a piece that effectively accomplishes this.|||Database mirroring seems to be like what I thought I heard 2005 to have.
This would be more like having a separate server instance to improve
performance.
"Adam Machanic" wrote:
> Are you referring to database snapshots?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> > I though 2005 was going to have a feature where you could face multiple
> > instances of a DB towards your customers with all the lock and transaction
> > bits accomplished in the background? The most common question I get about
> > Clustering is whether it is HA or really is a scalibility solution as
> well.
> > Avokia makes a piece that effectively accomplishes this.
>
>|||Database mirroring, with a snapshot running against the mirror gives you a
real-time read only copy of the data.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
>> Are you referring to database snapshots?
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>> > I though 2005 was going to have a feature where you could face multiple
>> > instances of a DB towards your customers with all the lock and
>> > transaction
>> > bits accomplished in the background? The most common question I get
>> > about
>> > Clustering is whether it is HA or really is a scalibility solution as
>> well.
>> > Avokia makes a piece that effectively accomplishes this.
>>|||As Mike mentioned, database mirroring really doesn't accomplish that.
Sounds like a merge replication scenario to me...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
> > Are you referring to database snapshots?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote
in
> > message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> > > I though 2005 was going to have a feature where you could face
multiple
> > > instances of a DB towards your customers with all the lock and
transaction
> > > bits accomplished in the background? The most common question I get
about
> > > Clustering is whether it is HA or really is a scalibility solution as
> > well.
> > > Avokia makes a piece that effectively accomplishes this.
> >
> >
> >
instances of a DB towards your customers with all the lock and transaction
bits accomplished in the background? The most common question I get about
Clustering is whether it is HA or really is a scalibility solution as well.
Avokia makes a piece that effectively accomplishes this.Are you referring to database snapshots?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
well.
> Avokia makes a piece that effectively accomplishes this.|||Hi
Clustering is HA and no Windows Clustering implantation is intended for
scale out.
SQL server 2005 has never has a scale out feature in it's feature list.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
> well.
> Avokia makes a piece that effectively accomplishes this.|||Database mirroring seems to be like what I thought I heard 2005 to have.
This would be more like having a separate server instance to improve
performance.
"Adam Machanic" wrote:
> Are you referring to database snapshots?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> > I though 2005 was going to have a feature where you could face multiple
> > instances of a DB towards your customers with all the lock and transaction
> > bits accomplished in the background? The most common question I get about
> > Clustering is whether it is HA or really is a scalibility solution as
> well.
> > Avokia makes a piece that effectively accomplishes this.
>
>|||Database mirroring, with a snapshot running against the mirror gives you a
real-time read only copy of the data.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
>> Are you referring to database snapshots?
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
>> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>> > I though 2005 was going to have a feature where you could face multiple
>> > instances of a DB towards your customers with all the lock and
>> > transaction
>> > bits accomplished in the background? The most common question I get
>> > about
>> > Clustering is whether it is HA or really is a scalibility solution as
>> well.
>> > Avokia makes a piece that effectively accomplishes this.
>>|||As Mike mentioned, database mirroring really doesn't accomplish that.
Sounds like a merge replication scenario to me...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
> > Are you referring to database snapshots?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote
in
> > message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> > > I though 2005 was going to have a feature where you could face
multiple
> > > instances of a DB towards your customers with all the lock and
transaction
> > > bits accomplished in the background? The most common question I get
about
> > > Clustering is whether it is HA or really is a scalibility solution as
> > well.
> > > Avokia makes a piece that effectively accomplishes this.
> >
> >
> >
2005 Shared Nothing
I though 2005 was going to have a feature where you could face multiple
instances of a DB towards your customers with all the lock and transaction
bits accomplished in the background? The most common question I get about
Clustering is whether it is HA or really is a scalibility solution as well.
Avokia makes a piece that effectively accomplishes this.
Are you referring to database snapshots?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
well.
> Avokia makes a piece that effectively accomplishes this.
|||Hi
Clustering is HA and no Windows Clustering implantation is intended for
scale out.
SQL server 2005 has never has a scale out feature in it's feature list.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
> well.
> Avokia makes a piece that effectively accomplishes this.
|||Database mirroring seems to be like what I thought I heard 2005 to have.
This would be more like having a separate server instance to improve
performance.
"Adam Machanic" wrote:
> Are you referring to database snapshots?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> well.
>
>
|||Database mirroring, with a snapshot running against the mirror gives you a
real-time read only copy of the data.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
|||As Mike mentioned, database mirroring really doesn't accomplish that.
Sounds like a merge replication scenario to me...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
in[vbcol=seagreen]
multiple[vbcol=seagreen]
transaction[vbcol=seagreen]
about[vbcol=seagreen]
instances of a DB towards your customers with all the lock and transaction
bits accomplished in the background? The most common question I get about
Clustering is whether it is HA or really is a scalibility solution as well.
Avokia makes a piece that effectively accomplishes this.
Are you referring to database snapshots?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
well.
> Avokia makes a piece that effectively accomplishes this.
|||Hi
Clustering is HA and no Windows Clustering implantation is intended for
scale out.
SQL server 2005 has never has a scale out feature in it's feature list.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
> well.
> Avokia makes a piece that effectively accomplishes this.
|||Database mirroring seems to be like what I thought I heard 2005 to have.
This would be more like having a separate server instance to improve
performance.
"Adam Machanic" wrote:
> Are you referring to database snapshots?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> well.
>
>
|||Database mirroring, with a snapshot running against the mirror gives you a
real-time read only copy of the data.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
|||As Mike mentioned, database mirroring really doesn't accomplish that.
Sounds like a merge replication scenario to me...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
in[vbcol=seagreen]
multiple[vbcol=seagreen]
transaction[vbcol=seagreen]
about[vbcol=seagreen]
2005 Shared Nothing
I though 2005 was going to have a feature where you could face multiple
instances of a DB towards your customers with all the lock and transaction
bits accomplished in the background? The most common question I get about
Clustering is whether it is HA or really is a scalibility solution as well.
Avokia makes a piece that effectively accomplishes this.Are you referring to database snapshots?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
well.
> Avokia makes a piece that effectively accomplishes this.|||Hi
Clustering is HA and no Windows Clustering implantation is intended for
scale out.
SQL server 2005 has never has a scale out feature in it's feature list.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
> well.
> Avokia makes a piece that effectively accomplishes this.|||Database mirroring seems to be like what I thought I heard 2005 to have.
This would be more like having a separate server instance to improve
performance.
"Adam Machanic" wrote:
> Are you referring to database snapshots?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> well.
>
>|||Database mirroring, with a snapshot running against the mirror gives you a
real-time read only copy of the data.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
>|||As Mike mentioned, database mirroring really doesn't accomplish that.
Sounds like a merge replication scenario to me...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
>
in[vbcol=seagreen]
multiple[vbcol=seagreen]
transaction[vbcol=seagreen]
about[vbcol=seagreen]
instances of a DB towards your customers with all the lock and transaction
bits accomplished in the background? The most common question I get about
Clustering is whether it is HA or really is a scalibility solution as well.
Avokia makes a piece that effectively accomplishes this.Are you referring to database snapshots?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
well.
> Avokia makes a piece that effectively accomplishes this.|||Hi
Clustering is HA and no Windows Clustering implantation is intended for
scale out.
SQL server 2005 has never has a scale out feature in it's feature list.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
>I though 2005 was going to have a feature where you could face multiple
> instances of a DB towards your customers with all the lock and transaction
> bits accomplished in the background? The most common question I get about
> Clustering is whether it is HA or really is a scalibility solution as
> well.
> Avokia makes a piece that effectively accomplishes this.|||Database mirroring seems to be like what I thought I heard 2005 to have.
This would be more like having a separate server instance to improve
performance.
"Adam Machanic" wrote:
> Are you referring to database snapshots?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:2C6CD6C9-2655-4E5F-8DD7-83B21DE43F12@.microsoft.com...
> well.
>
>|||Database mirroring, with a snapshot running against the mirror gives you a
real-time read only copy of the data.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
>|||As Mike mentioned, database mirroring really doesn't accomplish that.
Sounds like a merge replication scenario to me...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D22D9678-ACC0-4A67-B9B0-ECB89990694E@.microsoft.com...[vbcol=seagreen]
> Database mirroring seems to be like what I thought I heard 2005 to have.
> This would be more like having a separate server instance to improve
> performance.
> "Adam Machanic" wrote:
>
in[vbcol=seagreen]
multiple[vbcol=seagreen]
transaction[vbcol=seagreen]
about[vbcol=seagreen]
Thursday, February 16, 2012
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is
a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables ar
e
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is
a
>lock being held on a table by a particular transaction id which doesn't see
m
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables a
re
>locked by transaction_id XXXX but the spid listed is 0. Is there any way t
o
>kill a transaction that has no spid without recycling the SQL Server servic
e
>(this is a production box)? Any idea how this sort of thing happens? Belo
w
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
>
>
|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
2005 -lock being held by an orphan transaction?
SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>
We have a problem that started with an index rebuild job getting hung and
blocking other users. After digging into the problem I found that there is a
lock being held on a table by a particular transaction id which doesn't seem
to be tied to a process (spid) any longer. By querying certain DMVs like
sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
locked by transaction_id XXXX but the spid listed is 0. Is there any way to
kill a transaction that has no spid without recycling the SQL Server service
(this is a production box)? Any idea how this sort of thing happens? Below
is the output of some of the queries:
--sp_lock (only columns with info)
spid dbid ObjId IndId Type Mode Status
0 5 377872513 0 TAB IS GRANT
0 5 73871430 0 TAB IS GRANT
0 5 9871202 0 TAB IS GRANT
--sys.dm_tran_locks (only the columns with info)
resource_type resource_associated_entity_id request_mode request_type
request_status request_owner_type request_owner_id
OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
--sys.dm_tran_active_transactions (only the columns with info)
transaction_id name transaction_begin_time transaction_type
transaction_state transaction_status
645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32You could trying killing it by unit of work(UOW) ID instead
of spid.
You can get the UOW from sys.syslockinfo.
-Sue
On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>We have a problem that started with an index rebuild job getting hung and
>blocking other users. After digging into the problem I found that there is a
>lock being held on a table by a particular transaction id which doesn't seem
>to be tied to a process (spid) any longer. By querying certain DMVs like
>sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>kill a transaction that has no spid without recycling the SQL Server service
>(this is a production box)? Any idea how this sort of thing happens? Below
>is the output of some of the queries:
>--sp_lock (only columns with info)
>spid dbid ObjId IndId Type Mode Status
>0 5 377872513 0 TAB IS GRANT
>0 5 73871430 0 TAB IS GRANT
>0 5 9871202 0 TAB IS GRANT
>--sys.dm_tran_locks (only the columns with info)
>resource_type resource_associated_entity_id request_mode request_type
>request_status request_owner_type request_owner_id
>OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>--sys.dm_tran_active_transactions (only the columns with info)
>transaction_id name transaction_begin_time transaction_type
>transaction_state transaction_status
>645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32|||Sue,
Thanks for responding. Unfortunately I tried to go down that route too but
the transaction has no UOW. From what I have read, those are assigned only
for distrbuted transactions. The UOW listed in that view is:
00000000-0000-0000-0000-000000000000
Chris
"Sue Hoegemeier" wrote:
> You could trying killing it by unit of work(UOW) ID instead
> of spid.
> You can get the UOW from sys.syslockinfo.
> -Sue
> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
> <ChrisC@.discussions.microsoft.com> wrote:
> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
> >
> >We have a problem that started with an index rebuild job getting hung and
> >blocking other users. After digging into the problem I found that there is a
> >lock being held on a table by a particular transaction id which doesn't seem
> >to be tied to a process (spid) any longer. By querying certain DMVs like
> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
> >kill a transaction that has no spid without recycling the SQL Server service
> >(this is a production box)? Any idea how this sort of thing happens? Below
> >is the output of some of the queries:
> >
> >--sp_lock (only columns with info)
> >spid dbid ObjId IndId Type Mode Status
> >0 5 377872513 0 TAB IS GRANT
> >0 5 73871430 0 TAB IS GRANT
> >0 5 9871202 0 TAB IS GRANT
> >
> >--sys.dm_tran_locks (only the columns with info)
> >resource_type resource_associated_entity_id request_mode request_type
> >request_status request_owner_type request_owner_id
> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
> >
> >--sys.dm_tran_active_transactions (only the columns with info)
> >transaction_id name transaction_begin_time transaction_type
> >transaction_state transaction_status
> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>|||Normally yes...and the orphaned spid usually is -2 not 0. I
just thought it would be worth a shot to see if you could
kill it by UOW.
-Sue
On Thu, 26 Jul 2007 06:16:04 -0700, Chris C
<ChrisC@.discussions.microsoft.com> wrote:
>Sue,
>Thanks for responding. Unfortunately I tried to go down that route too but
>the transaction has no UOW. From what I have read, those are assigned only
>for distrbuted transactions. The UOW listed in that view is:
>00000000-0000-0000-0000-000000000000
>Chris
>"Sue Hoegemeier" wrote:
>> You could trying killing it by unit of work(UOW) ID instead
>> of spid.
>> You can get the UOW from sys.syslockinfo.
>> -Sue
>> On Wed, 25 Jul 2007 08:14:06 -0700, Chris C
>> <ChrisC@.discussions.microsoft.com> wrote:
>> >SQL Server 2005 SP1 - 9.00.2047.00 - Intel x86
>> >
>> >We have a problem that started with an index rebuild job getting hung and
>> >blocking other users. After digging into the problem I found that there is a
>> >lock being held on a table by a particular transaction id which doesn't seem
>> >to be tied to a process (spid) any longer. By querying certain DMVs like
>> >sys.dm_tran_locks and sys.dm_tran_active_transactions I see that 3 tables are
>> >locked by transaction_id XXXX but the spid listed is 0. Is there any way to
>> >kill a transaction that has no spid without recycling the SQL Server service
>> >(this is a production box)? Any idea how this sort of thing happens? Below
>> >is the output of some of the queries:
>> >
>> >--sp_lock (only columns with info)
>> >spid dbid ObjId IndId Type Mode Status
>> >0 5 377872513 0 TAB IS GRANT
>> >0 5 73871430 0 TAB IS GRANT
>> >0 5 9871202 0 TAB IS GRANT
>> >
>> >--sys.dm_tran_locks (only the columns with info)
>> >resource_type resource_associated_entity_id request_mode request_type
>> >request_status request_owner_type request_owner_id
>> >OBJECT 377872513 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 73871430 IS LOCK GRANT TRANSACTION 645984531
>> >OBJECT 9871202 IS LOCK GRANT TRANSACTION 645984531
>> >
>> >--sys.dm_tran_active_transactions (only the columns with info)
>> >transaction_id name transaction_begin_time transaction_type
>> >transaction_state transaction_status
>> >645984531 ParallelQueryXact 2007-07-11 18:32:45.547 2 2 32
>>
Subscribe to:
Posts (Atom)