Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Data size difference between SQL Server 7 and Access 2000

MyraMyra Member Posts: 2
Hello,
I have an Access 2000 database that is 70 mb. I do a nightly dts from that one to a SQL database (the SQL db's tables are first emptied and the new data is sucked in). The SQL 7 db is currently over 300 MB. Why is the SQL 7 db so much bigger? I have already gotten rid of all nulls and such and both have the same amount of records.

Comments

  • AstroZombieAstroZombie Member Posts: 39
    : Hello,
    : I have an Access 2000 database that is 70 mb. I do a nightly dts from that one to a SQL database (the SQL db's tables are first emptied and the new data is sucked in). The SQL 7 db is currently over 300 MB. Why is the SQL 7 db so much bigger? I have already gotten rid of all nulls and such and both have the same amount of records.
    :
    1. First, I would try DBCC Shrinkdatabase. When you delete the data from the prep table, it doesn't decrease the size of the db in SQL. So your db in SQL just keeps growing.

    2. You may be logging all transactions. If this is the case, you are constantly logging all of the imports from the access db.

    Hope this helps.
  • MyraMyra Member Posts: 2
    That makes sense. What I did when I looked at the data differences though was to right click the database in Enterprise Manager and then select "Shrink Database" from All Tasks. I checked both the Compact database and free up the space at the end of the file...but no difference was made to the 300 MB database. If I use this DBCC ShrinkDatabase would it do the same thing or something different.
    One of the tables in this database is over 70000 records and grows 100 records daily. Should I use the ShrinkFile for this and would it make a difference. I am sorry for my questions, as you can see I am relatively new to SQL server and am just trying to get the differences in compacting between it and Access 2000. THank you so much for your help!
  • melissa_may1melissa_may1 Member Posts: 937
    Hi Myra!

    Go into Enterprise Manager, expand the server in question (click the + sign to the left of the server name), expand the Daabase list (click the + sign to the left of "Databases"), then click on the database in question.

    After a short delay, during which SQL gathers info on that database, you'll see a display that lists, among other things, the size of the database, and the space available.

    The database size is specified when the database is originally created. Most DBAs, create fairly large database files, in order to minimize have the file be non-contiguous.

    If you click on "database properties", you can also see how much the database has been set to "grow" as data is added. File growth is triggered whenever a row is added and more space is needed. The default when creating the database is usually to increase the size by 10%. So, a 300MB database will first increase to 330MB with the addition of as little as one row added, if there's insufficient space for that row at the time.

    The next file growths would be to 363MB, 399MB, 439MB, 483MB, etc.


    [purple]Melissa[/purple]

  • Justin BibJustin Bib USAMember Posts: 0

    ________ < http://forcoder.org /> free video tutorials and ebooks about // C Ruby PHP PL/SQL Perl JavaScript Go Scratch Delphi Visual Basic Objective-C R Assembly Swift C# C++ Python Java Visual Basic .NET MATLAB SAS Lua Dart Apex Lisp FoxPro Alice Rust Scheme VBScript LabVIEW Bash Clojure Kotlin COBOL F# D Crystal Scala Prolog Julia Ada Transact-SQL Erlang ML Hack ABAP Logo Fortran Awk // ______________

Sign In or Register to comment.