Database Sizing
Oct 2, 2001
Hi,
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
View 4 Replies
ADVERTISEMENT
Jun 18, 2007
Hi 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
View 4 Replies
View Related
Mar 31, 1999
We 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.
View 2 Replies
View Related
Dec 28, 2004
Hello, 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 !
View 1 Replies
View Related
Mar 8, 2007
HI 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
View 1 Replies
View Related
Mar 13, 2007
Hello
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
View 1 Replies
View Related
Mar 5, 2002
Can 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 Related
Mar 23, 2002
Can 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 Related
Apr 2, 2002
I'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
View 1 Replies
View Related
Apr 16, 2008
Hi 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
View 3 Replies
View Related
Nov 30, 2007
I 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!
View 2 Replies
View Related
Jul 20, 2005
HiI 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 Related
Apr 16, 2008
I am looking for guidelines for log file sizing, specifically where database reorgs are required
View 1 Replies
View Related
Oct 20, 2015
if 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 Related
Jan 26, 2007
I 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!
View 1 Replies
View Related
Jul 23, 2005
We 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 Related
Sep 8, 2014
I 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.
View 1 Replies
View Related
Nov 18, 2014
I 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.
View 0 Replies
View Related
Feb 1, 2008
I 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 Related
Sep 19, 2007
I 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
View 11 Replies
View Related
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.
View 4 Replies
View Related
Sep 13, 2014
I'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.
View 3 Replies
View Related
Oct 24, 2006
Hi 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 Related
Nov 15, 2007
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
View 1 Replies
View Related
Oct 14, 2015
I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?
View 6 Replies
View Related
Oct 9, 2007
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies
View Related
Aug 18, 2005
I want to create a duplicate database in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......
But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform
Is there any another method to create a duplicate database with another name
from existing database on same server ?
View 5 Replies
View Related
Sep 2, 2007
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
View 3 Replies
View Related
Apr 9, 2008
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
View 8 Replies
View Related
Oct 2, 2007
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
View 4 Replies
View Related
Jan 14, 2008
Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
View 5 Replies
View Related
Mar 27, 2007
hii want to copy one database table to onther database table using script?my database is ms-sql server 2000
View 4 Replies
View Related
Jan 11, 2006
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies
View Related
