Guidelines For Sizing Log Files In SQL 2005
Apr 16, 2008I am looking for guidelines for log file sizing, specifically where database reorgs are required
View 1 Replies
ADVERTISEMENT
SQL Server 2005 - Database Sizing Estimations
Jun 18, 2007Hi there,
I've been searching the net the entire day for accurate information regarding SQL Server
database sizing estimations. I currently need to find an accurate link that will display all of
the data types supported by SQL Server 2005, the characteristics of each data type (Max
and Min Values, etc), and the physical storage size (KB and / or MB) of each data type.
The reason why I want this information is because I want to work out the current size
of each table in a Database as well as all Index sizes, and with the calculated information
predict what size the database will be in a few months and / or years.
It has been quite a mission to find the information, and when you do it isn't very clear at
all.
Any help would be greatly appreciated.
Have a good one,
Colin
SQL Reporting Services 2005: Sizing Report For Printing
Feb 1, 2008I have a report that is a little wide, and when I export it to an Excel file it spans about 2 pages. I have it set to an 8.5 height and 11 width for printing in landscape on normal-sized paper. But is there a way to make sure that the report exports with the printing page break covering the entire width so that the report prints only one page across? The users don't want to resize in Excel before printing.
View 3 Replies View RelatedSQL Server Sizing - Hardware Requirements For SQL Server 2005
Oct 24, 2006Hi allI am having a SQL Server database of arround 110GB and 250 Users. Whatis best suited hardware requirements for handling of such a database. Iam having following requirements from the server:1. Feeding purpose : On which continously feeding is done by average200 users.2. Reporting purpose : on which average hundred of repords are runningat a time.Right now i am having two servers for both of the above said purposeswith the following configuration.Feeding ServerHPSERVER 370G43.2 Xeon Single processor3GB RAM1 SCSI 73GB 15000 RPM(OS & Transaction Log), 4 SCSI * 73GB with RAID 0(Data Files) and 2 IDE * 300GB (Backups)Reporting ServerBoard SE7520BD2V2 x XEON 3.2 GHz CPU 800 FSB with 2MB Cache4GB RAM3 SCSI * 73GB with RAID 0 (Data Files and Transaction Log), 2 SATA *300GB (Backups) , 1 80GB IDE(OS)I want to fullfill both of my requirements from a single . I want toknow what upgrades or changes I need to do in the configurationaccording to the load and performence on existing servers. Here is thetable of performence counters along with their values to show the loadand performence for both of the servers.ReportServer ValueFeedingServer ValueCounter Instance Avg. Min. Max. Avg.Min. Max.Pages/sec 0.081 0 8.001 0.020 1.993Avg. Disc Queue length _Total 18.658 13.592 51.89634.421 0 175.783% Processor Time _Total 36.841 5.469 64.45379.837 54.688 100Buffer Cache hit ratio 97.702 93.595 99.46397.41 77.261 99.989Checkpoint pages/sec 0 0 00 0 0Lazy writes/sec 1.243 0 30.003 14.2630 97.194Page life expectancy 17 4 8110 6 14Avg. DiskBytes/transfer _Total 37934.133 7903.004128427.747 53806.538 8025.801 141750.562Avg. DiskBytes/Read _Total 16737.419 8192 42895.86254228.822 8170.499 143489.965Avg. Disk Bytes/Write _Total 12298.494 0 6553611708.014 0 92842.667% Disk Time _Total 77.555 49.976 261.966 760.35612.534 4890.383Users Connections 17 17 18195 185 212Batch Requests/sec 0.859 0 2373.215 0 205.009Transactions/sec _Total 1.768 0 18.00159.314 3 241.814Page splits/sec 0 0 0 1.030 5.007Please suggest what should be the configuration for my new server.Should I consider buying an Dual Itanium Processor based system with 16Gigs of Ram and 8 Hard Disks in Raid 0. Or Should I consider buying aQuad Xeon Dual Core Processor based system. Or may be my existing HPserver can be upgraded to dual processor and 8 Gigs of Ram and 2-3 morehard disks? Pls recommend what should be the factors that sould beconsidered, guiding priciples and my approact to reach a decision.With Best RegardsParveen Beniwal
View 2 Replies View RelatedIndex Guidelines
Jul 21, 2004Hi folks, ya all are just fine i hope.
I've a question regarding indexes.
I've a table that is very often updated. My boss asked me to keep a log of each record when the table is updated. I've created an AFTER UPDATE trigger on the table and throw the old record into a log table on each update. The table would get large earlier. I want the updates on the original table fast, so i don't wanna add any kind of indexes on the log table. The log table will be scarcely queried only if the user has made a mistake and we want the old record back.
Now, i've column named event(DATETIME) log table that records CURRENT_TIMESTAMP. Should i create a clustered index on this column so the records get in sequence (the values in the column will be unique due to time and seconds being stored). I could be able to access records fastly in the log table on the basis of EVENT column. But how much it would cost the updates on the original table??
My major concern is to keep the updates on original table fast while logging the old records.
Usually it's said not to created indexes on date fields. Do i need to create non-clustered index or i am following the correct path.
Any tips and guidlines; highly appreciated.
Howdy!
Base Guidelines
May 31, 2007hi all
i want to know the basic guidelines for developing and implementing and developing sql server database
Thanks in Advance
Malathi Rao
Best Practice Guidelines
Jun 28, 2006Hi All,
We have an application requirement for a database supporting field service engineers, which calls for a central SQL Server databse, and laptops with the same database replicated onto SQL Express. I'm resposible for designing the database for this, physical and logical. I've designed and built many a database, but never had to use replication before.
I've read through BOL, and understand how the merge replication process works, and I have no problem designing the database assuming it were to run on a single server.
What I am trying to find are whitepapers, or equivalent, on "best design and implementation practice", and especialy common mistakes to avoid.
I know that the windows programmers responsible for the UI will not completely abstract the database from the code (no matter how desirable that is or how often I tell them!), and I really don't want to find I have to change the physical tables or replication logic after they've coded most of the UI .
Many thanks in advance
Richard R
SqlCeConnection Guidelines - Keep It Open?
Feb 16, 2006What is exactly the recommended way of using a SqlCeConnection object in your application?
In all the examples I see (IBuySpyDelivery for example) it opens the connection object in the constructor and leaves it open most of the time until the class gets disposed.
Is that the way to do it? Or should you open and close the connection for each database action (insert/select/delete) you're doing on the local SQL Mobile database?
I'm a bit confused, but maybe I'm messing up the way of working on remote database servers with how it should be done on a local SQL Mobile db.
NSTL Guidelines For Motorola Q
Jan 3, 2007
Hello all,
We have some doubts regarding NSTL guidelines for motorola Q. Our application is managed application. Which ocasionally connects to server. It is very helpful if we can get answers urgently.
Hopper Test How can we make it run on Smartphone 5.0 (Motorola Q)? Is this tool only applicable for pocket pc 2003?
Application Verifier Test Tool Is this tool only applicable for unmanaged code? If we are using .NET as the development environment, do we still have to use this tool?
Call Interupts Is their a special way of handling Call Interrupts in the application or the default behaviour provided by the device (Moto Q) is OK?
Send Key (Green key) on the device What is the expected behaviour of this key when the application is running? Do we have to handle it separately in the application
Is their a maximum space limit allowed for creation of the database files for an application?
Is their any specific utility like MaxfileCNT which NSTL uses for checking the behaviour of the application when there is no space available on the device?
thanks & regards,
Rashmi
SQL Security Guidelines? (i.e. Authentication, Accounts)
May 3, 2004We are about to change the sa password, currently all packages and jobs rely on this account. I imagine there is probably a better architecture that we could employ to ease this process. Any suggestions recommendations?
Also any caveats I should be aware of regarding places to look that might currently rely on the sa account so that we do not need to worry about existing processes from breaking?
I think we are going to create an NT account for DTS Packages and possibly use the same account for any DTSRun jobs, does this make sense? Or is there anything to gain by having these as separate accounts? Also should this be the same account used to run the MSSQLServer process?
I tried doing a search for this information here, thinking it was already covered, but could not find anything that informative, any resources that you could point me to would be appreciated, I will look on BOL as well as MSFT to see what I can dig up.
Error 26 - Followed The Guidelines, Still Unable To Make It Work
Nov 7, 2007
Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]"
as an error. This is what I've got:
Im using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what Ive used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider.
SQL Server 2005 Surface Area Configuration
Database Engine Remote Connections set to Local and Remote Connections Using TCP/IP Only?
SQL Server Browser is Enabled and Running
- is set to Active, under Built In Account Network Service
I have created Windows firewall exceptions for:
sqlservr.exe
sqlbrowser.exe
udp port 1434
SQL Server Configuration Manager
both SQL Server and SQL Server Browser are running.
Under SQL Server 2005 Network Configuration
Shared Memory and TCP/IP are enabled only.
SQL Native Client Configuration
Shared Memory 1
TCP/IP 2
Named Pipes 3
all enabled
I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.
I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.
Guidelines For Readiness Testing For Moving SQL2000 DBs To SQL2005?
Oct 17, 2006Hi,
We have a number of databases running on our SQL2000 Servers. The databases use Stored Procedures which we call from our VB.NET apps, developed in-house. We now wish to upgrade our servers to SQL2005.
In the past, when upgrading from VS2002 to 2003 and then 2005, we have developed and executed Test Plans to ensure that the code upgrades properly. Microsoft even supplies FXCop and other tools to help. I now wish to develop and execute a similar Test Plan to ensure that our databases will work as required.
Does Microsoft supply any similar tools or guidelines for testing that databases written for SQL2000 will work as required on SQL2005? I see the Upgrade Wizard to examine the Server but is there a similar tool for testing databases? Are there any guidelines on the tests to include in such a Test Plan?
Thanks
Database Sizing
Oct 2, 2001Hi,
can anyone answer me this? I recently inherited a SQL Server database set up. The user database size is about 3 times as big as necessary - does this matter? Does it have an impact on performance and if so is there anything I can do to reduce the size? From what I have managed to gather so far you cannot use alter db to change the database to a smaller size than it was originally created at. If anyone can help it would be most appreciated!
Cheers,
Petra
Sizing For SQL Server 7.0
Mar 31, 1999We are considering migrating our Oracle database over to SQLserver 7.0. I'd like to do some sizing estimates, but I have not been able find any methods for sizing SQL server 7.0 databases. There didn't seem to be any calculation guidlines or formulas in the manual or the online documentation. The only formulas I have found on the WEB are for version 6.5. Does anyone have (or know where I can find)formulas that are valid for sizing SQL server 7.0 databases?
Note: an archived message on this site referred to an attachment containing sizing formulas, but the attachment was not accesible.
Sizing Db Objects
Dec 28, 2004Hello, I'm looking for a sizing Excel (or any other format) file, that permits me evaluate the size of my sql server database, actually I have one excel file for Oracle, where I only put the expected # of rows, the average size of the columns, and other few data, and I can get the size (in Megabytes) of a table or an Index.
Could you provide me something similar??
Thanks !
SQL Server Sizing
Mar 8, 2007HI everybody,
We are designing a system using .NET/ASP.NET and SQL Server (2000 Standard as a customer requirement). The system will be hosted into 2 dedicated servers (an web/application and a db server) and we need to estimate the hardware requirements needed.
Could anyone help me on sizing this? Is there any literature on how to do this? I found some sizing web sites but I'd like to double check them. I already have some estimation on data storage size/growth based on some information I've got from the customer.
Thanks in advance!
Sergio
Sizing Server
Mar 13, 2007Hello
I am sizing a database server for Business Objects Enterprise XI R2. In addition to housing this database we plan on consolodating a few other databases to this server. I am wondering what type of array I should be considering for these databases.
I will be installing on HP DL380
Thanks
Sql 7 Server Hardware Sizing
Mar 5, 2002Can someone please point me in the right direction for calculating SQL server 7 hardware. Thank you in advance for your assistance.
View 2 Replies View RelatedSql 7 Server Hardware Sizing
Mar 23, 2002Can someone please point me in the right direction for calculating SQL server 7 hardware. Thank you in advance for your assistance.
View 1 Replies View RelatedRe-sizing Output Columns
Apr 2, 2002I've got a question that I can't seem to find an answer for, I was hoping someone here might be able to point me in the right direction. I've set up a stored procedure that will email someone if any entries are added to a table . However, the output is garbled looking (see below)
Client Number SSN Client Name Old SD
New SD
------------- ----------- ---------------------------------------- --------
--------
800901 899-34-3482 John Smith 04/20/20
05/01/20
400909 144-23-0029 John Smith 04/09/20
04/11/20
447788 445-89-9967 kjl;j;j 04/05/20
04/12/20
300099 234-90-7815 John Johnson 04/08/20
04/15/20
What's happened is the client name field is too wide, so the New SD field kicks down to the next line. I'd like to clean this up. Is there a way I can either increase the length of the row before it moves to the next line, or can I re-size the client name field to match the size of the data. In other words, cli_name_vc is declared as a varchar(40). If the longest name that comes up in the query is 18 characters long, can I re-size the output so that it does not take up 40 characters?
Any help is greatly appreciated.
thanks,
-scott
Sizing An OLAP Server
Apr 16, 2008Hi there,
We are experiencing some contention of resources between our RDBMS and our OLAP environment; both SQL2005; so we are going to put the OLAP side of life onto another seperate box. Therefore I need to work out what sort of requirements we are going to need; and this is where i have no idea.
I have had a look at the HP sizers, however this is looking primarily at normal RDBMS setups rather than specifically for OLAP. Is there any specifics that I need to consider/nail down before working this out?
To give you an idea:
We currently have 210 million rows in our main fact table, and each row is averaging about 330 bytes
The size of our current OLAP database is only 4GB
It appears as though we are currently getting anywhere between 6 and 20-30 concurrent users
Considering the above; I would have thought a dual core cpu setup (maybe x2) with 8-10GB of ram would have been the ticket?
Now I have done a server sizing exercise for a normal SQL2005 installation; considering temp db on seperate spindles to the log and database tables etc; but what sort of considerations do I need to do for a server running SSAS?
Thanks in advance for any assistance anyone can offer on this front
Regards
Troy
Change List Sizing
Nov 30, 2007I have defined a list with one row of data and three more optional rows of data
Row 1 is always shown - rows 2, 3, and 4 are shown if there is data available in those fields
By using the visiblity property I can have rows 2, 3, and 4 disapper when no data is available and appear when data is available
What I would like to do now is change the height of the list item based on if rows 2, 3, or 4 are visible (have data)
So if no data is available then the list item would only be one row high
If only one of rows 2, 3, or 4 contain data then the list item would be two rows high
so forth and so on
I noticed that the textbox can increase and decrease height to accomodate content. Looking for something similar in a list item.
Thanks!
Sql Server Sizing Planning - Architecture Help
Jul 20, 2005HiI joined a project where 100,000 rows were added everyday. Now due toadditional customers the expectation is 2 million reocrds/day ie 10 GB worthof textfiles. We have to estimate the hard disk, memory, # of CPUs etc.Wewill have one yearworth of data in the db. Rest will be in tapes etc.We will be using WIN2000, SQL Server2000.- Any comparable server sizing willbe appreciated.1. Tohandle every day load, I thought that we will have a table for each day(pre created in the database )and have a view with union all selectingfromall these 365 tables. (This is the only way to partition in MSSQL Serverright?).2. The requirement is to populate datawarehouse tables with all the data.However there will be only inserts mostly but there can be updates too whichhappenned in the past 12 days.Hence we have to use the data from the last12 days and massage it etc and populate into datawarehouse tables.How can I do this so that I will have the datawarehouse tables with n-12days of data and I will alwys add the last 12 days data to it.Do you have any suggestions?Ragu
View 3 Replies View RelatedIntegration Services :: SSIS CPU And RAM Sizing
Oct 20, 2015if there is any way to accurately size a single server using SSIS.Β The server will be a virtual machine.Β The data being loaded will be approximately 200 MB per load with loading to a 150 GB database on a separate server.
View 3 Replies View RelatedSizing A Reporting Services Installation
Jan 26, 2007I am converting an old Crystal Reports environment to 2005 Reporting Services. I've seen the basic memory requirements for the SSRS programs themselves, but I was wondering if there are any other guides available that will help me size the H/W based on anticipated users and reports.
Thanks!
Linked Server To Text Files: Is Possible To Detect Changes Made To Those Files? (SQL Server 2005)
Sep 3, 2007Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
Best Practises For Server Sizing For MS Reporting Services
Jul 23, 2005We are planning to deploy the MS Reporting Services. Unfortunately,there isn't much guidance on how to effectively size a server.Here are my questions:1) In an environment with 1 reporting server (hosting the reportingdata layer, application, and management layer) connecting to 1 or moreSQL servers (over 2 trunked 1Gbps switches connections), what is theconstraining resource when running large reports against a database ofsay 4GB in size? CPU, RAM, DISK, or Network speed on the ReportingServer? Or the Data hosting SQL server? Or does it depend on code?2) If I have a Duel Core CPU, do I buy 1 CPU license. MS had respondedto the HyperThreading as 2 virtual CPUs with the statement that you buyCPU licenses based on socket count effectively. This would imply thatif I buy a 4 Duel Core AMD Opteron Proliant 585 (8 CPU cores in 4packages / sockets), I can run MS Reporting Services Standard Edition.Is that true?Any input or direction would be appreciated.Paul V.
View 1 Replies View RelatedSQL 2012 :: Sizing Bar Of Horizontal Range Bar Chart In SSRS?
Sep 8, 2014I need to manage the height of an horizontal range bar chart inside a SSRS 2012 report.
In my report, I've a filter to read a particular data set. So, I could select five data rows or ten data rows or twenty data rows and so on. I'd like to maintain fixed the height of the horizontal bars while the number of data rows changes.
SQL 2012 :: TempDB - Tlog Sizing / Capacity Planning?
Nov 18, 2014I am in the process of formulating recommendations with respect to the purchase of additional storage for our current SQL 2012 SharePoint (2013) instance. My recommendation is to purchase separate storage (i.e, 15k disks) for the TempDB and Tlogs respectively (two sets of raid 10 disks). Currently, this server is hosting several instances, including SP, using two arrays (one for database and the other for Tlogs).
I am attempting to find information/recommendations on how to go about projecting the amount of storage for each of these while factoring in for growth.
Additional Details:
how to best formulate a reasonable estimate. Our largest content database belongs to IT and is currently ~80GB. That said, this is currently an outlier. The remaining content databases are less than 10GB (most are less than 2-3 GB). However, SharePoint will be used for digital document imaging in addition to, eventually, replacing file shares as our primary document storage medium once we roll it out.
Our current tempDB is ~400MB, but the instance was recently started a few days ago, as we had to failover to our backup server for hardware maintenance. I do not have any historical data on TempDB growth at this time. Also, I don't know how useful this would be given we have not fully deployed yet.
Sizing A Pagefile On Servers With Large Amounts Of Memory
Sep 19, 2007I know the standard Microsoft recommendation is to make the pagefile at least 1.5 to 3 times larger then the amount of physical memory. However, if you're talking about a server with lots of memory such as 16GB or 32GB, would following this rule be unnecessary. With SQL 2000 running on Windows 2000 Server or Windows Server 2003 I typically see pagefile usage no more then 12% for a 2GB pagefile. Anything over 15% means I need to look at other indicators to see if a memory bottleneck has developed. If I have 32GB of physical memory and make the pagefile only 1.5 x 32GB I have a 48GB pagefile. 10% of this is 4.8GB, which I would hope I never see consumed.
Any thoughts?
Thanks, Dave
Memory Sizing For A SQL 2000 Cluster - Windows 2003 Enterprise SP1
Oct 4, 2007
I am looking for some recommendations for memory sizing and options for a SQL 2000 Cluster. This is a two node cluster built on Windows 2003 ENT SP1 (x86). Both the nodes have the following hardware:
- 4 x Dual Core AMD Processors
- 16 GB Memory
- EMC Shared Disk
We are running six SQL 2000 instances and don't expect each of these instances to use more than 1.7 GB of memory. All these instances are going to support BizTalk 2004 Databases. I already have /PAE enabled on the nodes. I am looking for the following answers:
- Do I need to enable AWE on all the instances even if the instances ? Currently, we don't have that enabled and we have seen some issues regarding excessive paging even when there is physical memory available. The DBAs think that we don't need to enable AWE. I am bit confused on this one.
- We normally run 3 instances on each node and would like size the cluster in such a way that it can take six instances in case of a node failure
Any input will be highly appreciated.
Guidelines For Creating A Database Snapshot On A Mirror Database
Nov 24, 2006Hi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
SQL Server Admin 2014 :: Rule Of Thumb For Sizing Log Space During Index Maintenance?
Sep 13, 2014I've been fixing some issues lately where weekly maintenance has been causing logs to grow and filling disks.
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario? I'm thinking 3x the largest database size?
I've been watching them run on databases in the range of 50GB where the logs are growing well over that for rebuilds or even reorgs. Once you have a few databases like this on a server, you can suddenly eat through a lot of disk space just for holding logs during maintenance.