Post reply

Space on the table

  • April 23, 2009 at 9:54 am

    #131510

    I have a table in my production db:

    DataSpace318.461 MB

    IndexSpace1.320 MB

    RowCount 3051417

    I need to insert 375,000 records to this table. Is this enough space in my table, do I need to increase it. Thank you

  • April 23, 2009 at 10:11 am

    #983067

    You didn't provide enough information.

    You need to add 375,000 rows, and you're concerned about having enough space.

    So, you need to determine how much space each row will take in that table. (Check each column's datatype/size against the Data Types in BOL.)

    Multiply that by the # of rows and that's what you need FOR THE DATA alone.

    Then you need to see how that is going to impact any indexes. For sure your PK, and probably others.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 23, 2009 at 10:19 am

    #983074

    Rows ReservedMB DataMB IndexSize UnusedMB

    3051417319.813 318.4611.320 0.031

  • April 23, 2009 at 10:20 am

    #983078

    Depends on table structure and many other factors, provided information is in-sufficient. For more information see SQL BOL and search for "Database Design Considerations".

  • April 23, 2009 at 10:30 am

    #983088

    This is a design of my table: How do I find out how much space each row takes, I will have 375000. Thank you

    I have clustered index for field1 and field2

    fileld1varchar(10)Unchecked

    fileld2varchar(15)Unchecked

    fileld3varchar(10)Checked

    fileld4varchar(10)Checked

    fileld5varchar(10)Checked

    UpdateDatevarchar(32)Checked

    UpdatedDatedatetimeChecked

    InsertedDatedatetimeChecked

    Data will be like this:(Example) Inserts to first 5 rows

    STN2B102OF2031ALLCOVERS1995EACH

  • April 23, 2009 at 10:56 am

    #983114

    I suggest you look at: http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx and http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx to get an idea of your specific storage requirements. Add up the storage requirements for all of the columns, and multiply by 375000.

    Then, check out http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx for figuring out index space requirements.

    Add these all together, and you have a fairly close estimate of your space requirements.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 23, 2009 at 1:46 pm

    #983233

    I am new with this and having difficult time to understand it, so if I can some one help me to figiore out without going to links,I thought I provided all the information about my table.What else do I need to give you?

  • April 23, 2009 at 2:37 pm

    #983272

    by keeping in view table structure and max. utilization, you would need to have more space to insert these new rows in the existing table, because by using table structure you would need max 40 MB in total, but can be less than if value length is less than actual column length.

    Tariq
    master your setup, master yourself.
    http://mssqlsolutions.blogspot.com

  • April 23, 2009 at 5:18 pm

    #983325

    I gues I am still confused, I put all my info together:

    RowCount 3051417

    Rows ReservedMB DataMB IndexSize UnusedMB

    3051417 319.813 318.461 1.320 0.031

    have clustered index for field1 and field2

    fileld1 varchar(10) Unchecked

    fileld2 varchar(15) Unchecked

    fileld3 varchar(10) Checked

    fileld4 varchar(10) Checked

    fileld5 varchar(10) Checked

    UpdateDate varchar(32) Checked

    UpdatedDate datetime Checked

    InsertedDate datetime Checked

    Data will be like this:

    Example :

    IMROFSG150

    PCHKWSSZ130P

    STYLES

    WSS

    STYPHID06

    All rows will be the similar just letter of styles different . How do you know if Ineed to add space and how to do it?Thank you very much

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply