Memory Allocation
Feb 11, 2002Hi,
Can someone give me some help on find out the memory limit for SQL Server 7.0 and how to limit the memory being used by the SQL Server?
Thank you very much in advance!
Denise
ADVERTISEMENT
Memory Allocation
May 21, 2007hai,
once again pavan. i need some clarification on this issues
how sql server allocates memeory for the instances.
means
procedure cache
buffer cache
log cache
system data structure.
could you please provide information regarding this issue
regards,
pavan
pavan
Associate Oracle DBA
Memory Allocation
Mar 27, 2008I have windows 2003 with ms sql 2000. The machine has 3.6 gigs of ram and only runs ms sql, nothing else.
Whenever the first query is made sql will allocate as much as 1.5 gigs of ram which is just killing the system. This system reboots nightly so this first query happens every morning.
We have tried setting the min and max memory of sql, as well as the reserver memory setting to see if it will preallocate all that memory but we've had no luck.
Is there a way to make SQL allocate all that memory ahead of time? maybe make it cache some tables or something?
I know very little about MS SQL so please be very descriptive about possible solutions or troubleshooting steps.
Thanks,
--Carlos
SQL CLR Memory Allocation
Jan 18, 2008How to find how much memory allocated to SQLCLR by sqlserver. Also is there any way to determine how much memory my code needs to run? Thanks in advance.
Regards,
Hari Prakash.
Memory Allocation Errors
Jun 21, 2005EXECUTE master.dbo.xp_sqlmaint N' -D TempDB -CkDB -CkCat '
I run this job on say all DB's and recieve a memory allocation failure.
I run checkdb in QA and it works fine any ideas?
Thanks.
Memory Allocation - Log Shipping
Aug 20, 2015I'm log shipping to AWS. Currently, we are shipping from a server with 64 GB RAM to one at AWS with 30. It's not a massive server but does have periods of high usage when certain jobs/tasks run. It's been running fine for a year but the server size is large and is barely used. My understanding is that for simply log shipping, we don't need a lot of memory and I was hoping to drop the instance size down and use an instance with 16 GB Ram instead. It would save thousands per year.
View 2 Replies View RelatedMemory Allocation In SQL 2000
Sep 13, 2007I would like to ask regarding the memory allocation fo SQL Server 2000. For example if my Data Server have 8GB physical memory installed how much memory can SQL Server 2000 utilize? Based on my research and understing SQL 2000 Server can only utilize 3GB memory? But using the AWE you can set the memory to a maximum server memory?
View 8 Replies View RelatedAllocation Of Memory By Instance
Oct 2, 2007I’m setting up a server for development that will have one instance of SQL Server 2000 Standard Edition and one of SQL Server 2005 Standard Edition installed.
The server has a total of 3.5 GB of physical memory, so I was wondering about the best way to allocate the memory. Should I just let both instances have the default allocation and let them fight it out for memory as needed, or allocate a memory limit for each instance?
CODO ERGO SUM
Memory Allocation To SQL Server - AWE Under 32 GB
Apr 23, 2006I am setting up a server under Windows Server 2003 Enterprise Edition (SP1) and SQL Server 2000 Enterprise Edition (SP4). The new server is a AMD opteron with 32 GB of memory. I noticed that PAE is enabled automatically by Windows 2003. Should I enable AWE for SQL Server 2000 and specify Max and Min amount of memory for SQL Server ?? Is there a limit on how much I could specify in MAX memory for SQL??
This is the first time I have a server with so much memory so I want to make sure that I do the right thing. In the past I only have servers with 8 GB of memory and we just enable AWE and specify Max memory to 6 GB or so.
Any advise is very much appreciated. Thanks,
What Is Better.. Static Or Dynamic Memory Allocation ?
Dec 26, 2007I have gotten mixed comments on this topic. I have a 64 bit machine running 64 windows 2003 standard and 64 SQL 2005 standard with 8 GB of RAM. We want to upgrade it to 32 GB. What is the best approach to do this? Dynamic or Stattic giving min and max server memory a value ? and if static what value should I use for 32 GB knowing that this box is only being used for SQL.
View 3 Replies View RelatedMemory Allocation Failure Problem
May 22, 2008There is an error called:
memory allocation failure
How to fix it?
Memory Allocation In MS-SQL 2000 Instances
Mar 17, 2006Hi,From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3instances. It is a nice machine with 4 Gb of memory but the memory allocationis very weird:Instance A: 1400MbInstance B: 1000MbInstance C: 80Mb (!)Instance C is performing badly under a bit of pressure which seems not strangeconsidering these allocations.With that in mind, is there a way to check and re-allocate memory? I'd like tosee if the instances really need these amounts of memory and if not, to movesome over to other instances.Thanks!Dries Bessels
View 1 Replies View RelatedMemory Usage/allocation/paging Question
Jun 30, 2006Are there any *negative* consequences to SQL Server 2000 Standardpaging to disk more often if one reduces the amount of available RAMfrom 2.0 GB to 1.5 GB to give the OS and other apps enough RAM, otherthan things possibly being "slower" and whatever wear and tear couldhappen to the disk drives??Or is it better to add in more RAM per se to bring the server up to 4GB (Win2k3 Svr SP1)?? (it has a 4GB pagefile)Is any information available about judging how much RAM the OS (Windows2003 Server) should have available?? Does IIS require a lot of RAM??Thank you, Tom
View 1 Replies View RelatedDb Space Allocation
Sep 14, 2000Hi,
I need to create a 5GB database with 4GB for data and 1GB for log in v7.0. I know that in v6.5 I would have created five 1GB devices - to go easy on the backups.
Could someone please advise on how I should distibute allocation of space.
Should I allocate 1GB to the primary files and 1GB each to 3 secondary files?
Should I just allocate 4GB to primary?
I would really really appreciate any reponse? If there are articles I would appreciate links.
Thanks
kira
Space Re-allocation 6.5
Jan 16, 2001I have a small data warehouse which periodically has old data deleted.
However after a delete the free space within the database is not released. If I copy the tables, drop them, recreate, and copy back, the space is there.
Any ideas what is causing this anyone ?
Thanks
Processor Allocation
Nov 4, 2002I was wondering, is there a way to allocate processors to SQL2000. I have a server that has 4 processors, I would like to leave one just for the operating system and have SQL2000 use the other 3. Is this possible and do you think it would be recomended to do this? Or should i just leave the 4 processors for everything?
thanks in advance
Table Allocation
Aug 17, 2006Dear Friends
I have one database with multipe MDF files.Normally when I am creating a new table it's going to primary MDF file.How I can allocate a new table to the MDF file which I am specifying when table is creating
Thanks in Advance
Filson Paul
Db Space Allocation
Dec 15, 2007
Hi,
can some one throw some light on how the DBAs calucaulate the space allocations?
For example I have 30000 records which has 30 columns each defined as varchar(100) and if the db is full and wants to increase the space. Then how much extrac space should be allocated??
Drive Allocation
Jun 29, 2007Any comments, please. I have a new server 5x160GB drives. It needs to run IIS for a web app and SQL Server 2005. One user database. I was thinking of dividing space as follows and wanted to get some thoughts from others.
2 drives = RAID 1 set, 160GB space, OS, IIS, SQL exe, tempDB, user database log file
3 drives = RAID 5 set, 320GB space, SQL data files, user database files
Thanks for looking...
Integers && Dynamic Allocation
Mar 10, 2004Here is what I am trying to do...
I want to goup "members" togethers in a "group."
A table for members and a table for groups.
each containing coluns...
but inside Groups I would like a column that contains ID numbers for the members that be long to that group.
Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.
Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.
Hope this makes sense
Tempdb Allocation Errors
Oct 5, 2000On two different unrelated servers this week, I got these errors from a DBCC CHECKDB(tempdb):
Msg 8905, Level 16, State 1, Server NTSONYX, Procedure P_DATABASE_BACKUPS, Line 121
[Microsoft][ODBC SQL Server Driver][SQL Server]Extent (1:136752) in database
ID 2 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
A re-boot solved the problem, but what is causing it?
Allocation Discrepancy Error
Oct 25, 1999During daily scheduled maintenance the following error occurs, which causes the maintenance job to fail. How can we fix?
Thanks!
Allocation Discrepancy: Page is allocated but not linked; check the following pages and ids: allocation pg#=1085440 extent id=1085464 logical pg#=1085464 object id on extent=8 (object name = syslogs) indid on extent=0
Allocation Discrepancy Error
Sep 28, 1999I am getting the following error on a version 6.5 database
when I run the weekly database backup.
"Allocation Discrepancy: Page is allocated but not linked; check the following pages and ids: allocation pg#=491520 extent id=491720 logical pg#=491720 object id on extent=8 (object name = syslogs) indid on extent=0"
The backup script I run is as follows..
SQLMAINT.EXE -D ECAP -CkDB -CkAl -UpdSts -BkUpDB F:MSSQLBACKUP -BkUpMedia DISK -DelBkUps 8 -Rpt F:MSSQLLOGECAP_DbBkUp.rpt
This database is a 7 x 24 database. What is the least intrusive and/or best way to correct this problem?
Jim
How To Know Allocation Place For Each Object?
May 25, 2007Hi all of you,
My current dutie is try to obtain for each table their filegroup.
I'm seeing sysobjects table but I can't see nothing related to do with
Thanks a lot for your time,
How To Specify Disk Space Allocation For Databases ?
Aug 13, 2002Hello ,
I wanted to know on what basis the disk space allocation for the databases is planned . Suppose if we plan 60 GB for data files ( mdf )for a given database then what should be the space allocation for the log files ( ldf ) and the tempdb ( both mdf and ldf files ).
Is there any thumb rule or any defined ratio for the same ?
Thanks
BCP And Table Space Allocation Quandary
Jul 1, 2004Hey all, first time poster here.
Cheers!
Enterprise Edition SQL2k sp3a on Win2k3 Enterprise Edition server.
Using BCP to load ~4 million records.
Largest record width is ~350 bytes.
When finished i have over 6 million pages reserved and only about 800 thousand actually used.
Which puts me 'round about 3 rows per page and WAY too much empty space.
YIKES!
Any thoughts as to why this is happening?
Or insight into how BCP handles page allocations that may be different than the typical extent based page allocations for table data?
Thanks!
Page Allocation Error Message
May 19, 2008Hi all,
I am running SQL 2000 Enterprise SP4 on Windows Server 2003 Standard SP2
I am getting the follow error message:
Table error: Page (1:53888) allocated to object ID 546100986, index ID 0 was not seen. Page may be invalid or have incorrect object ID information in its header.
I check the hardware and didn't see any issues.
I run the following command to check the actual page:
dbcc traceon (3604)
dbcc PAGE ([Statistics], 1, 53888, 1)
Here is the output:
PAGE: (1:53888)
---------------
BUFFER:
-------
BUF @0x015D7A00
---------------
bpage = 0x73F40000 bhash = 0x00000000 bpageno = (1:53888)
bdbid = 19 breferences = 1 bstat = 0x9
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x73F40000
----------------
m_pageId = (1:53888) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 402100473 m_indexId = 0 m_prevPage = (1:50311)
m_nextPage = (1:53890) pminlen = 579 m_slotCnt = 0
m_freeCnt = 8096 m_freeData = 7688 m_reservedCnt = 0
m_lsn = (3643:60824:142) m_xactReserved = 0 m_xdesId = (0:26032361)
m_ghostRecCnt = 0 m_tornBits = 67108866
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:48528) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED
DATA:
-----
OFFSET TABLE:
-------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Please let me know what else I can do or check in order to eliminate this error.
Thank you,
T.
SS2k5 EE: SAN, Allocation Units, And All That Jazz
Feb 2, 2007I've been doing some testing on the SAN/Disk subsystem over the past few days. I've run into something that is bugging me though. Hopefully someone here has run into the same thing or can answer.
*All LUNs have been created with default selects.
Test 1:
LUN1 (H:) - Created partition with diskmanager in win2003.
LUN2 (I:) - Created partition with diskpart and aligned the sectors at 64k.
Formatted both disks to have 4k in the allocation units.
Created a new blank DB on each disk.
With Profiler running, I ran a bulk insert for both disks and also ran a "Select count(*), Sum(total) from tableA" for both databases and cleared the proc cache inbetween.
Profiler showed the reads and writes io for the import and selects as a the same number (468721 reads and 312182 writes).
Test 2:
Deleted partitions
LUN1 (H:) - Created partition with diskmanager in win2003.
LUN2 (I:) - Created partition with diskpart and aligned the sectors at 64k.
Formatted both disks to have 8k in the allocation units.
Created a new blank DB on each disk.
With Profiler running, I ran a bulk insert for both disks and also ran a "Select count(*), Sum(total) from tableA" for both databases and cleared the proc cache inbetween.
Profiler showed the reads and writes io for the import and selects as a the same number (468721 reads and 312182 writes).
Test 3:
Same as test 2 but formatted with 64k allocation units.
So here is my question: Shouldn't the I/O go down or at least change if you change the allocation unit size for the disk?
Thanks,
Daniel
How Much Space In A Db Is Available For Allocation To Tables And Indexes?
Nov 19, 2007I was trying to find out how much space is available in a 2000 db for allocation to tables and indexes. I am trying to find the amount of space that has to be used-up before another allocation is automatically made to the database. I looked at sp_spaceused but BOL is rather sketchy at defining what the numbers it returns really mean. Is the "unallocated space" the value I am looking for?
Thanks,
Michael
Problem In Row Size Space Allocation
Feb 15, 2007Hi
I am using DTS to transfer tables from Oracle 9i to SQL Server 2000 sitting in a shared environment and managed to migrate
a lot of tables without glitch..
When I was migration a table <XYZ> from Oracle to SQL Server..The table was created in the SQL Server whilst the DTS threw an error that read when it was copying data and 0 rows were copied with the error message being
"Cannot create a row of size 8387 which is greater than the allowed maximum of 8060"
Incidentally the have a table in the Oracle DB that has 152 Rows of Data with 94 Columns..
Does any change needs do be done on the Admin side of the SQL Server to resolve this problem and faciliate effective transfer of data from the DB's?
Thanks in Advance
KI
Resize Filegroups Allocation On Restore
Aug 17, 2006Hi, I have a database with about 200Gigs allocated to filegroups and 200Megs data in it presently, and am trying to create a copy of the database on the same server - except I don't have enough diskspace free for another 200Gigs. Is there a way using copy database wizard, or restore that the filegroup allocations can be resized?
Thanks,
Chris
Dynamic Allocation Into Table Partition
Feb 14, 2008
Hi,
I am trying to partition the table i have into 10 partitions based on the last digit of the employee ID.
The following is the logic I tried to use but was not successful.
Code Snippet
CREATE PARTITION FUNCTION PartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8)
CREATE PARTITION SCHEME PartitionScheme AS
PARTITION PartitionRange
ALL TO ([PRIMARY])
create TABLE PartitionedTable
(
counter INT ,
string CHAR(8000) ,
EmpID INT,
UpdateDate datetime
)
ON
PartitionScheme (EmpID % 10)
Any suggestions on how I can make the logic work as required.
Thanks in advance.
With regards,
How To Force Space Allocation To A SS2000 DB?
Sep 17, 2007I have to force a space allocation to a DB file in order to stop a Diagnostic Manager alert which states that the database is over 80% full. Now, I don't think this alert is rational, nor do I think it is useful in any way but that's the way it goes. My only option is to allocate more space to the DB so that the space used will fall below 80% so the alert will stop being issued.
So, can you tell me how to force a space allocation on an existing db file?
Thanks,
Michael