Database Size Estimation

Feb 7, 2000

Hi ,
I have a sample table with the following descriptions
CREATE TABLE "dbo"."tab1" (
"cola" "int" NOT NULL ,
"colb" varchar (30) NOT NULL ,
"colc" "int" NOT NULL ,
CONSTRAINT "PK___2__10" PRIMARY KEY CLUSTERED
(
"cola",
"colc"
)
)
GO
After adding some 100000 records to this table I run the sp_spaceused procedure and it give me the following information.
name - tab1
rows - 100000
reserved - 2122KB
data - 2084KB
index_size - 30KB
unused -8KB
Also i manullay calculated the number of datapages using the following equations which is available in the Sql server documentation.
Data row size = 8 ( Size of ffixed length columns) +
30 (Size of all variable length columns) +
1(Number of variable length columns ) +
7( over head)
= 46
Rows per page = (2032 -32)/46 = 43
32 - Page heade size
Number of data pages = 100000/43 = 2325 pages
= 2325 * 2 = 4650KB

Can any body tell me why there is a mismatch in result between the calculation?Am i wrong in some steps ? Is there any other way to estimate the size of table.Your valuable suggestions appreciated.
Regards
Jiji

View 1 Replies


ADVERTISEMENT

Estimation Of Full Backup Size

Jun 28, 2006

Has anyone come across a more accurate method than sp_spaceused to estimate the size of a full database backup for SQL Server 2000 ?

I have found this to have too great a variance (even after running updateusage) to rely on any accuracy for it. I have also looked at perhaps using the ALLOCATED Pages indicated in the GAM pages but this also seems to be pretty inaccurate.

I have a number of servers where space can be limited and backups using Maintenance Plans have occasionally failed because they delete the old backups AFTER they do the latest one. I am writing a script which can check the space remaining and adjust the backup accordingly but the variance I have observed so far with sp_spaceused is too great.

Any ideas welcomed.

View 4 Replies View Related

Database Estimation For Client

Jan 2, 2008

Dear Experts,
i'm going to create a new database for the client, the initial size might be 10GB. so please guide me what will be datafile size and log file size?

i'm not creating secondary datafiles. is it ok?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 2 Replies View Related

Estimation The Buffer Cache Hit Ratio

Sep 1, 2006

HiI have trouble with MSSQL2000 SP4 (without any hotfixes). During last twoweeks it start works anormally. After last optimalization (about few monthsago) it works good (fast, without blocks). Its buffer cache hit ratio wasabout 99.7-99.8. Last day it starts work slow, there was many blocks anddedlocks. There are no any queries, jobs and applications was added. Nowbuffer cache hit ratio oscilate about 95-98. I try update statistics andreindex some hard used tables, but there is no effect or effect is weryshort (after few hours problem return).Mayby somene know what it could be?Is it possible to estimate how each table (using DBCC SHOW_STATISTICS orDBCC SHOWCONTIG or others) how the table affect on total buffer cache hitratio?Marek---www.programowanieobiektowe.pl

View 1 Replies View Related

Is Cross-Validation (or Rotation Estimation) Available In SQL Server?

Mar 28, 2007

Dear forum users,

I am a newbie in using MS SQL server with analysis services.
There seems to be no 'cross-validation' tool in MS SQL
which is frequently used in data mining and even statistics.
Is there anyone having similar difficulties?
Is there any solution like a small scripts to divide
the given dataset with multiple folds?
Your valuable comments and feedbacks would be appreciated.

Minnetongka

View 4 Replies View Related

Table Size And Database Size

Mar 2, 2008

Hi,
i use this script that show me the size of each table and do the sum of all the table size.

SELECT
X.[name],
REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows],
REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved],
REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data],
REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size],
REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused]
FROM
(SELECT
CAST(object_name(id) AS varchar(50)) AS [name],
SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows],
SUM(CONVERT(bigint, reserved)) * 8 AS reserved,
SUM(CONVERT(bigint, dpages)) * 8 AS data,
SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size,
SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused
FROM sysindexes WITH (NOLOCK)
WHERE sysindexes.indid IN (0, 1, 255)
AND sysindexes.id > 100
AND object_name(sysindexes.id) <> 'dtproperties'
GROUP BY sysindexes.id WITH ROLLUP) AS X
ORDER BY X.[name]

the problem is that the sum of all tables is not the same size when i make a full database backup.
example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when
i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?

THX

View 5 Replies View Related

SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.

Sep 4, 2007


I am trying to resize a database initial log file from 500M to 2M. I€™m using€?

ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "



And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.



Any help with this process?

View 1 Replies View Related

Getting Database Size And Log Size

Aug 11, 2000

Hi,
I am using
exec sp_helpdb
go
dbcc sqlperf(logspace) for
getting database size and log size. Is this gives the correct
database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.

Thanks in Advance.

Seenu. S

View 4 Replies View Related

The Database File Is Larger Than The Configured Maximum Database Size.

Mar 20, 2007

I'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.

Thanks for any help any of you can give!

Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091

View 3 Replies View Related

Database File Is Larger Than The Configured Max Database Size.

Feb 18, 2008

Hello,

I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.

My database size is around 350MB. So when I am trying to synchronize this is the error message that I get.
" The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].

I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.

connstr= "Data Source=Storage CardItems.sdf;Max Database Size=500;"

Any help regarding this would be appreciated.

Thank you
.

View 6 Replies View Related

Database Log Size

Jun 7, 2007

hi
my database on remote server i cannot access directly.
i can access it only with query analyzer.
my log file size is 9mb but nothing in database. only few tables there so how i can reduce log file size with query.
 
thanks

View 4 Replies View Related

How To Get The SQL Database Size

Jul 15, 2004

Is it possible to get the SQL Server database size programmatically?
I have an WEB application in c# ASP NET, but I can found any information about.

Thanks for your attention

Freedeveloper

View 2 Replies View Related

Database Size

Mar 1, 2002

We have a SQL server database most time for read only because the data source comes from another database( which is not SQL server database, now it is about 2G). Every day we have a job running as following:
Step 0. extra the data from another database, and create plain text files for each table for BCP job.
Step 1. drop all of the index
Step 2. truncate all of the tables.
Step 3. BCP in all of the data from plain text file.
Step 4. create all of the index again
Step 5. shirnk the database.

Everything runs fine but the database grows 1 G from yesterday's database. I am sure we do not have so much data entry in one day.
Any one can give some suggestion?
I wonder if I need do shirnk the database or shirnk the data file before create the index.
How can know how much size for all of the index file?
Thank you very much.
Judy

View 1 Replies View Related

SQL 7 Database Size

May 4, 2001

I am a beginner with sql and I have been inputting data (txt files) to my database and now have approached 4GB in size and it will not let me expand any further on the primary file group. My 'boss' said that sql has no size. I am using desktop

View 4 Replies View Related

Database Size

Aug 13, 2001

Is there a stored procedure that returns the current size of a database, and the maximum size of a database whose growth property IS NOT set to unlimited?

TIA,

Chris

View 1 Replies View Related

Max Size Of A SQL Database

Jul 25, 2000

Just wondering if anyone knew the maximum size of a database that SQL Server 7 can handle.

joe

View 1 Replies View Related

Database Size

May 9, 2000

When I run sp_spaceused on my database it give me a total size of 258MB
The backup of this database reads 226MB
The Properties screen tells me that my data is a total of 278MB
I am running SQL 7.0 .
Why am I getting different sizes?

View 2 Replies View Related

Database Size ?

Mar 30, 2000

I have a simple question, why Am I running out of space with my current 3.5GB database, I am constantly expanding it, yet when I dump the database nightly the dump is just around 1.5GB?

View 1 Replies View Related

Database Size

Dec 17, 2002

Newbie here ....I started with a datbase of 90 megs...it now grew to 300 megs in 2 months...what is the best way to see the largest table or to find out why this database grew so fast? Thanks!

View 3 Replies View Related

Database Size

Jul 6, 2003

Hi..
My database data file are over 2.1 Gbs now. can anyone tell me is my data file too big?? or not safe?? or there's a limite for MSSQL 2000. my last choice are move some data to another database. but i have consider the report i have to make. so i really dont want to do that. can anyone advice. thanks

View 4 Replies View Related

Database Size

Aug 27, 2003

Hi,
I have 30 servers and about 600 databases. I want to know the size of databases ( data files as will log files) both used and allocated space.
Can any one tell me the easiest way to get this information?

Thanks.

View 7 Replies View Related

Database Size

Jul 27, 1999

Hi all,
We are using sql6.5. srvpack 3.
I noticed that one of our database was maxed out, and increased the size of the device and then increased the size of the database.
when I checked the database it is still showing the database is maxed out. I did it twice, it still shows that the space has been used.
Can anyone please explain to me why this is happening and how to get the actual space measurement.
Thanks in advance.

View 2 Replies View Related

Database Size

Nov 13, 1998

After we ran sp_spaceused on one database, the size for the UNUSED column is a negative number -1324KB. Can someone explain how this could happen? I really appreciate.

View 1 Replies View Related

Database Log Size

Aug 20, 2001

Hi,

I need to check the log size of my database periodically and send a notification if the size exceeds 8 GB. I want to set this up as job that runs every 30 minutes or so and page the DBA if it exceeds the above said limit. Can anyone give me the command which can be used to obtain the logsize into a variable. I am new to SQL and would appreciate your expert advise.

Thanks,
Marcus

View 1 Replies View Related

Database Size

Jan 2, 2006

Hi,
Someone can telle how with a query can i get the use size of all my db of akll my server.

I use the table sysfiles but is the total size of my file and not the use size.

Thanks a lot and happy new year.

Best regards.

View 2 Replies View Related

Database Size

May 4, 2006

Is there any shortest way to database size (data & log)

I refer to Article ID: 256650 (reduce log file size)
Quit a lot of step

I want to reduce physical occupied HDD size

View 2 Replies View Related

Database Size

Nov 5, 2007

Is there any way I can join all the information produced by this into a single output rather than multiple outputs

EXEC sp_MSforeachtable @command1=" EXEC sp_spaceused '?'"


Like this
Listing_Images43 16 KB8 KB8 KB0 KB
User 3 16 KB 8 KB 8 KB 0 KB

I want to fill a gridview in ASP but don't know how to deal with data produced through multiple queries

View 3 Replies View Related

Database Size

Apr 24, 2008

Hi All,

Will the database size decrease once I delete data from the audit tables?

View 3 Replies View Related

Database Size

May 18, 2004

I want how can i do to do capacity planning in sqlserver 2000.
Do you make scripts (vbs) who query the sysfiles tables and transfer the answer in excel ?
Please help me.
Phil

View 3 Replies View Related

Size Of Database

Jun 1, 2008

I have a database that is almost 1.55 GB in size according to the database properties window.

However I checked the size of all the tables and indexes using the stored procedure 'sp_spaceused' and the sysindexes table and the total size of all tables and indexes is only 162 Mb.

Does anyone know why the database is so big, and how I may be able to reduce the size of it?



Steve

View 1 Replies View Related

What Is The Size Of Database

Jul 13, 2005

what size of database for express

View 7 Replies View Related

Database Size &> 4GB

May 5, 2006

I know I have read that MSDE had a 2GB limit on database size and that Express should have one on 4GB, but I am by 'accident' ended up with a database with an .mdf of 4400MB and .log 2000MB in my Express SP1 ... when will it stop me ... and how ?

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

View 3 Replies View Related

Database Size

May 22, 2007

I'm the web designer for my company and as a result I'm also the server administrator. SO I'm new to MS SQL and I had a question.

We have MS SQL 2000 and we have a listserv database that's 3.5 GB. When I use Enterprise Manager > right-click on the db > Properties > Data Files tab > the file properties are set to Automatically grow file and the Maximum file size is set to Unrestricted file growth. But on the General tab it says "Space available: 0.00 MB". We definitely have hard disk space (100+ GB) on the server and we haven't received any error messages about not enough space. Also, the other databases have some amount of MB in the Space available field, only the listserv db says 0.

I've found other posts similar to this on this forum but I couldn't find an answer that made sense to me. Do I increase the space available? Am I supposed to run a stored procedure? I'm really new to stored procedures and other SQL operations so please go easy on me

Cheers,
Brian

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved