Hi all,
We are going to setup a new server,so for that How to Capacity Plan for CPU and Memory ,Hard Disk for a new SQL SERVER Setup.
Is there any specific criteria for capacity planning ?in which base we should do this part.
Thanks
Kumar
Hi!
I got two questions and please forward all your suggestions:
1: Is there any document for capacity planning of SQL Server 6.5 database?
2: Is there a way to find out total number of transactions per day in SQL Server?
Thanks in advance!
We have a client which they have production 2 node cluster environment. On it around 200 databases with single SQL instance.
Now client wants disatster plan for these 200 database. In these 200 database 3 db's are around 80 GB each databases remaing are less than 5 DB.Note: All these 200 db's are having produciton sites (i mean to say each db is having single site)
For this DR paln clinet is going to provide other DR server,they wants to setup DR between exsting produciton cluster instance to this DR server.
So in this case we have suggest SQL server AlwaysOn availability group.
Here my main question is can we keep all these databases in single AG? .If yes, guidlines to move up. if not, do we have any limitations.Also, best method to setup for this DR plan.
I set up a Database Maintenance Plan for my SMS Databases. Through the wizard, I set up an Optimization Job which is causing tons of errors (5,400 since yesterday at 1:00 am) in my SMS logs.
The messages occur in a series of three kicking off approximately every minute (see below).
Thanks everyone for any help!
~~~~~~~~~~~~~
Error 602
Severity Level 21
Message Text:
Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes.
Explanation:
This error occurs when Microsoft® SQL Server™ cannot find a row in the sysindexes table for a needed table or index (for example, when executing a stored procedure that references a table that has been dropped).
Action:
If the error occurs because a stored procedure references a dropped table, drop and re-create the stored procedure. This error may also occur in conjunction with other error messages that better point to the root cause of the problem. Execute DBCC CHECKTABLE on sysindexes; also execute DBCC CHECKDB.
If the problem persists, contact your primary support provider. Have the output from the DBCC CHECKDB statement available for review.
~~~~~~~~~~~~~
Error 3903
The rollback transaction request has no corresponding begin transaction.
Microsoft article (Q198265):
SYMPTOMS:
Failure to prepare a statement, either by means of ODBC or OLE DB, incorrectly results in the failure of the entire transaction. Potential causes for such a failure include syntax errors or incorrect object names in the statement. Subsequent operations that depend on the transaction being active, including attempts to commit the transaction, will fail.
CAUSE:
SQL Server incorrectly closes the whole transaction after any error, rather than stopping execution of the statement only.
WORKAROUND:
To work around this problem, execute the statement without first preparing it, using SQLExecDirect or ICommandText::Execute. An error during either of these operations will cause only the statement, rather than the whole the transaction, not to be processed. The application can then determine the appropriate response, such as ignoring the error and continuing with other statements, resubmitting the query without the problem, or rolling back the transaction.
STATUS:
Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.
~~~~~~~~~~~~~
SMS Discovery Data Manager failed to process the discovery data record (DDR) "C:Program FilesSMSinboxesddm.box638JL00J.DDR", because it cannot update the data source.
Possible cause: On a Primary site, it is probably a SQL Server problem.
Solution:
1. Review the immediately preceding status messages from this component about SQL Server errors.
2. Verify that this computer can reach the SQL Server computer.
3. Verify that SQL Server services are running.
4. Verify that SMS can access the SMS site database.
5. Verify that the SMS site database, transaction log, and tempdb are not full.
6. Verify that there are at least 50 SQL Server user connections, plus 5 for each SMS Administrator console.
If the problem persists, check the SQL Server error logs.
Possible cause: On a secondary site, SMS Discovery Data Manager probably cannot write to a file on the site server, so check for low disk space on the site server.
Solution: Make more space available on the site server.
Hi,
any1 know how many records can be stored in a table in sql server? is there any way to maximise it? will table capacity (number of records) vary due to our table stucture or database structure? is there any documentation bout tis?
Cheerslife's Ng
I have a flat file with about 2000 fields and 2 million records. I can reorganize the data any way I want and have a unique id field for each record.
Is SQL Server a suitable solution do you think, and also how many fields will a table take (I think access takes 255)?
If you think there may be a better solution for me to look at I would be glad to hear.
Thanks for any help
Hello,
I have been working on SQL Server Capacity Planning for a few weeks now and have gathered a lot of materials, but non of thes materials contain recommended best practices on SQL Server capacity planning and also they do not contain operational guidelines.
I would be glad if anyone can recommend a website or book that contain information on SQL Server Capacity Planning Best Practices.
Thanks,
Jide
I am just starting to build my apps in asp.net 2.0. I have build apss in v. 1.x before using sql server 2000. However, before i make the shift, I would like to know the advantages and disadvantages of using the sql server 2005 express edition then using the sql server 2000.
for example;+ How many data can it store?+ How many concurrent users it supports.+ Advantages and disadvantages of using sql server 2005 express ed.+ Any other relevant information that developers should know / beware / watch out for when using sql server 2005 express edition.
Note: I am not asking about the actual SQL Server 2005 but the express edition!
Regards and thanks in advance
We have a SQL enterprise server 2014 with two installed instance.
First instance for SharePoint 2013 DBs and second instance  Dynamics AX 2012 R3 DBs.
The server has 32 GB of RAM, currently the process usage for first instance is 26 GB and second instance is 700 MB.
Which mean that SharePoint DBs in the first instance is consuming most of the RAM.
My question is there any way to assign a fixed RAM usage for a specific instance without affecting the other instance?
for example 16 GB only first instance and 16 GB to second instance.
Is only one plan is kept for one query in plan cache?
i heard generally hash is created for a query and plan is search with this hash.
My SQL server disk space is getting close to full capacity which is causing certain reports that we run via the SQL server to time out because I don't think there is enough space on the server.
Any tips on cleaning out a SQL server? Are there any folders that can absolutely be deleted to clear space? I know on a local computer that the %temp% folder can be cleaned out. I know when dealing with servers you do not want to make to many changes because it can cause major problems down the road.
I have a table with a column Capacity which is char(10) and gets populated from user files. I want to check records which have negative Capacity value. So i first checked if its numeric and then for negative.
select * from table WHERE ISNUMERIC(LTRIM(RTRIM(Capacity))) = 1 AND Capacity < 0
BUT still it checks for char fields too giving errors like - Conversion failed when converting the varchar value 'asdf ' to data type int.
Hi
I did a load testing and found the following observations:
1. The Memory:Pages/sec was crossing the limit beyond 20.
2. The Target Server Memory was always greater than Total Server Memory
Seeing the above data it seems to be memory pressure. But I found that AvailableMemory was always above 200 MB. Also Buffer Cache HitRatio was close to 99.99. What could be the reason for the above behavior?
I am running a number of SQL instances on my PC. Within the network, I have think server with various System Center components. For compatibility reasons, some features of System Center 2012 R2 had to be delegated to different SQL databases. My question is, because there is now more than one IP address on my system, and each instance of SQL is assigned to its own IP, is there a way to setup DNS and SQL so the namespace points to the desired IP address? For Instance:
MSSQL2008 instance is set to run on = 11.12.13.1
MSSQL2012 is set to run on = 11.12.13.2
IN DNS:
A Record: Mike-PC = 11.12.13.1
A Record: Mike-PC = 11.12.13.2
If I want to use MSSQL2008 by specifying Mike-PC as the DNS name, how would I do that with 100% accuracy? If there is another way to get the job done, I am more than willing to approach this differently.
sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
thank you
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
hi, can any one tell me what is the meaning of capacity planning and where do I get info about it,and how does this help me in sql server admin role
thanks
Ahmed
What si capacity planning?
Where can i find more stuff on it?
Hi Everybody,
Our development team is doing some stuff in ASP. They are interested only in char and varchar
data types. They clearly told us that they are not going with text and image data types.
Is it possible to make a row size behind 8060 bytes in a table without text and image data types?.
tks in advance,
Sri.
Hi Everybody,
Can anybody explain me, what is Capacity Planning and what is Load Balancing?
tks in advance
sri
Hi All,
We need to do capacity planning for our SQL server database server. Capacity planning is interms of CPU usage, Memory usage and disk usage.
Can anybody help me with how should I start and what are the guidlines to do it? I have started executing perfmon utility with some counters but still not sure what to do next. Please help me. Thanks in advance.
Regards
Sachin
Don't sit back because of failure. It will come back to check if you still available. -- Binu
Hi ,
We need to upload over 100 GB file into SQL Server using SSIS. I want to know whether SSIS can upload such huge file in to a table. If yes, How ?. If not is there any work around to achieve this.
Expecting some solutions ASAP
Thanks
Regards
Pandithurai K
Singapore
Hi,
I€™m working in a DWH project the source system have data from passed 30 years and the total number of records from the source system is 10 million.
I€™m need to do the estimation for the data base for the up coming 10 years of data.
As I can predict that the 30 year of data is 10 million then expected data for 10 years would not be more then 3 million.
The ETL tool I€™m using is SQL Server Integration services and the reporting tool is Business Objects.
Can any one guide me on the implementation plan, mean memory taken for ETL jobs (As SSIS use large amount of memory) database and business objects.
Hey - I know that xp_fixeddrives gives me the amount of free space on a drive. Is there a similar (xp) function that will give me the capacity of a drive??? Any help is appreciated. Even if you can just point me in the right direction. Thanx in advance. Billy
View 1 Replies View RelatedDoes any one know where I can locate a document or template for database capacity planning on the web? Any help would be greatly appreciated. thanks.
View 1 Replies View Relatedhello,
I just completed my graduation and eventually i got a proposal from a company to develop a software for database management. I have some doubts about licensing the back-end database. My doubts are:
1. If I'm using Sql Server as the backend and the .sql file placed on the server where this .sql file will be accessed by about 20 clients, will the customer (company) have to purchase a license for the sql server? If yes, how this can be done? Does the company have to purchase a license for each client and server for accessing contents of the .sql file? Actually, there will be only 1 server and abt 20 clients.
2. Does the developer need to purchase a seperate license for distributing the .sql file?
3. Also, wat is the maximum capacity of an sql database size? I'm asking this because as per my requirements, the company will be using the database at a rate of 1GB per month. So, by end of a year, it will be 12 GB.
NOTE: No Sql Server Software will be installed on client machines.
In brief, my doubt is: Do we need license for reading and writting to .mdb or .sql file contents and do we need seperate license for distributing them?
Please, somebody can help me for the licensing information and also for the database size?
Thank you.
hello,
I dnt know if this is the right place. I'm sorry for that.
WEll, I just completed my graduation and eventually i got a proposal from a company to develop a software for database management. I have some doubts about licensing the back-end database. My doubts are:
1. If I'm using Sql Server as the backend and the .sql file placed on the server where this .sql file will be accessed by about 20 clients, will the customer (company) have to purchase a license for the sql server? If yes, how this can be done? Does the company have to purchase a license for each client and server for accessing contents of the .sql file? Actually, there will be only 1 server and abt 20 clients.
2. Does the developer need to purchase a seperate license for distributing the .sql file?
3. Also, wat is the maximum capacity of an sql database size? I'm asking this because as per my requirements, the company will be using the database at a rate of 1GB per month. So, by end of a year, it will be 12 GB.
NOTE: No Sql Server Software will be installed on client machines.
In brief, my doubt is: Do we need license for reading and writting to .mdb or .sql file contents and do we need seperate license for distributing them?
Please, somebody can help me for the licensing information and also for the database size? the database will be also used for remote access.
Thank you.
Hi there,
We are currently developing a E-Com research site where subscribed members can conduct market research. The web site is developed and ready to go, however we are undecided what is required on the Database side and could do with some of your expert help.
Users will be conducting research on our MS SQL database. The database holds every product sale made by a select number of consumer good companies. The database holds 12 million records at any one time, however overall the database is very static and is not updated online. We intend to only store 1 months data, therefore on a weekly basis we delete old & add new records.
We are offering three categories of searches on the DB.
1. By Seller
2. By Category (i.e. Hair Care, Laundry etc)
3. By Sale.
The first two searches are not performance intensive as they are compiled and stored in the database - therefore no need for calculation online. Our main concern is the 3rd report. This report is where we allow the user to search for a specified search string. Therefore in the main table that holds all the sales, we have a full-text index for the field "Product Description".
What we are unclear on is the Capacity requirements regarding providing such functionality. I.e What server do we require?
We have researched many articles and have absorbed all the information regarding multiple disk controllers, lots of memory etc.. etc.. However we still have not been able to obtain an idea on what Server would serve our purposes. For example: would the following server do the job?
2 x 2.4Ghz Hyper-threading Xeon CPUs with 512Kb Cache and 400MHz System Bus
2GB Samsung RAMBUS 800-45 Memory (RIMMS / RDRAM)
A lot is determined by the usage, i.e. how many queries are executed per minute etc. At present we do not have a clear idea on the number of users. However we are not truely concerned about this as the 3rd report needs to execute and return within a short period of time. That is our main concern.
Therefore our question is as follows: If you were going to host a database that consisted of 12 million records & full-text indexing which had to be queried in a few seconds, what would you purchase?
We ask you to consider that funds are extremely tight and although the new latest server may do the job, it is most likely out of our budget. We are looking for the most cost effective solution but still statisfying the purpose.
Kind regards
Angus
Hi Friends,
I am Gokul, I am using SQL Server MSEE 2005 (Free ware). Currently I am involved in the development of an application where I am using SQL Server MSEE 2005 and VB.Net. My incomming data size ranges from 5 GB to 7 GB per day. Can I use SQL Server MSEE 2005 as Data base with any licensing or have to get any license for storing larger capacity data.
Can any body please help me.
Thanks,
Gokul.G
I want to know how it calculates CPU utilization percentage (we see in Task Manager).
Further how can we find total capacity of our CPU in ms/sec....
We have a debate in our team about embedded SQL vs. Stored Procs.
The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?
I can't find any definitive information on pros and cons between the two methods.
If there are no major performance issues, or gotchas, I guess it comes down to developer preference.
SP Pros:
- Great SQL support in VS.NET (dev, debug, integration)
- Seperation of database specific code from middle tier.
- Less lines of code in middle tier
- VS.NET support for .xsd dataset definitions.
- Logic closer to data for more demanding processes.
Embedded SQL Pros:
- Less artifacts for version control
- Better encapsulation of logic
Any info would be appreciated.
thanks
Kevin
Hi Everybody:
4 -5 years ago, I started my career as a translator translating the MetaTexis CAT (Computer Aided Translation Software).
It's amazing to see all the improvements that have been made until now, but recently I found some problems regarding
databases:
I heard that ACCESS databases volume is limited up to 2 GB and that SQL 2005 databases volume is limited up to 4 GB, but I think this information is wrong, or at least I was only able to import 10% of that amount.
Speaking in words, 2 GB doesn't represent a database with a volume of 125,000 segments/sentences (for ACCESS) and 4 GB a volume of 250,000 (for SQL 2005).
Concrete, my "mega.mxtm" database has "only" 359 MB and suddenly I refuses to import more sentences. Is that normal? (MICROSOFT SQL 2005)
Question: Is the new SQL 2008 also limited? Is there any way to "free" or increase the volume capacity?
Point 2: As I updated the SQL 2005 into 2008 I am not able to open the "old" "mega.mxtm" anymore... : (
Regards!
De Sena Viegas
I have an existing Access database that I need to transfer over to a more powerful back-end due to the need for larger size capacity. We need to be able to have a backend that can exist up to just about any size due to us scanning in documents by ODBC. With Access I know I was limited to about 4gb size and when split onto my current SQL server I have heard I will be stuck at 10gb? If so can you recommend a better backend, but my question is about the front end. I hear Windows WPF can be linked into SQL server but does this limit the size as well?
View 3 Replies View Related