December 2, 2013 at 11:25 pm
Hello,
Can I add both -T845 and -g startup parameters to my SQL Server?
Will they cause any startup or run time problem to my SQL Server?
My configuration
SQL Server 2005 Standard Edition, 64-bit, 9.0.4226,
Windows Server 2003, Enterprise x64 Edition, Physical memory: 14GB,
Windows Clustering
My existing startup parameters:
-dR:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;
-eR:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;
-lR:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;
-T845
We added -T845 3 years ago because of the lock page memory issue.
Recently, we got the following error and it made our Clustering system switched over unexpectedly.
2013-12-02 19:21:13.49 spid55 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536
2013-12-02 19:21:13.49 spid55 Error: 701, Severity: 17, State: 123.
2013-12-02 19:21:13.49 spid55 There is insufficient system memory to run this query.
2013-12-02 19:21:13.57 spid55
This web site suggest us adding a -g startup parameter to avoid the above "Failed Virtual Allocate Bytes" error.
http://technet.microsoft.com/en-us/library/ms190737.aspx
Could you tell me:
(1) Can I add both -T845 and -g startup parameters to my SQL Server 2005 ?
(2) Will they cause any startup or run time problem to my SQL Server (production) ?
(3) How much memory should I add to the -g startup parameter ?
Thanks a lot.
December 2, 2013 at 11:35 pm
-g has no effect on a 64-bit SQL instance. It's for 32 bit only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2013 at 12:33 am
Disappointed.
Thanks a lot.
December 3, 2013 at 12:44 am
It has no effect on 64 bit because it's not needed. On 32 bit, a process only had a 2GB virtual address space (or 3GB with /3GB). the -g flag set how much of that 2GB of virtual address space should be left unallocated when SQL allocated the buffer pool. This is why that memory is called 'Mem_To_Leave', it was the amount of virtual address space to be left unallocated.
On 64bit, the virtual address space is 8TB. Since it's exceedingly unlikely that SQL will allocate 8TB of buffer pool (would require a server with probably close to 9TB of physical memory), there's no need to leave some virtual addresses unallocated and hence there's no Mem_To_Leave in 64 bit and no use for the -g flag.
If you're getting "There is insufficient system memory to run this query." in 64 bit, it suggests that you're server's memory is over-utilised. Nothing to do with virtual address space, which is what -g affect, just plain vanilla low on physical memory.
If that is the case, consider reducing max server memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply