Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    54

    Unanswered: Access to Sql server : file Size

    I have migrated an Access Database to SQL server.

    The original Access MDB file was around 50Mo.

    After migration, I get 2 files in SQL directories :
    xxx_data.mdf of around 100 Mo
    and xxx_log.ldf of around 130 Mo

    So the needed space is more than 4 times more than used under Access.

    Is this normal, or is there something Wrong somewhere ?

    Thanks for any Infos,
    Pierre.
    Pierre (Pl-Arts)

  2. #2
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Re: Access to Sql server : file Size

    Firstly the "data" component for your database is typically only stored in the xxx_data.mdf file, so it looks like it is around twice the size of your access db. You should back up your database, truncate and shirnk your transaction log.. see BOL for more info on transaction logs. This will control the size of your xxx_log.ldf file.

    Next you can check how much of the 100Mb your data is actually consuming by running a a command like sp_spaceused. I'm not sure if Access has the concept of "devices" but I don't believe that it does. Have a read of this in BOL and maybe that will give you a hand..

    Hope this helps.

    Originally posted by Plarde
    I have migrated an Access Database to SQL server.

    The original Access MDB file was around 50Mo.

    After migration, I get 2 files in SQL directories :
    xxx_data.mdf of around 100 Mo
    and xxx_log.ldf of around 130 Mo

    So the needed space is more than 4 times more than used under Access.

    Is this normal, or is there something Wrong somewhere ?

    Thanks for any Infos,
    Pierre.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    As specified make sure you didn't hit any errors during this upsizing from Access. And also assess the size of the files and schedule correct intervals of Tlog and database backups to avoid any out of space errors.

    Occassionally refer to SQL error log for any information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Jan 2003
    Posts
    54

    Re: Access to Sql server : file Size

    Thanks for your advise.
    But I am completly new with SQL server, and I do not see any tools
    in SQL Manager to shrink/compact the database.

    Where are these tools ?

    Even in the BOL, I do not see anything talking about reducing space.

    Thanks for any help.
    Pierre (Pl-Arts)

  5. #5
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Re: Access to Sql server : file Size

    What version of SQL Server are you running?


    Originally posted by Plarde
    Thanks for your advise.
    But I am completly new with SQL server, and I do not see any tools
    in SQL Manager to shrink/compact the database.

    Where are these tools ?

    Even in the BOL, I do not see anything talking about reducing space.

    Thanks for any help.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    True, you didn't mentioned version of SQL server used and make sure your login has SYSADMIN privileges to carry on those tasks.

    Refer to BOL for SP_ATTACH_DB & SP_DETACH_DB topics which involves attaching/detaching process.

    And make sure you have client tools installed on your machine to talk to the SQL server which has been migrated.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •