+ Post New Thread
Results 1 to 9 of 9
MIS Systems Thread, Shrink a Training MDF in Technical; Hi we use a few different SIMS.Mdf for training purposes.. these have evolved over the years and are now an ...
  1. #1

    Join Date
    Jan 2008
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Shrink a Training MDF

    Hi
    we use a few different SIMS.Mdf for training purposes.. these have evolved over the years and are now an unwieldy 2GB.

    Is there anything I can do to shrink the MDF to a more manegable size - we have shrunk the LDF but cant see how to reduce the MDF? Its only training data so would be happy to remove historical/old data..

    thanks

  2. #2

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28° 31' 48.89", +28° 25' 37.42" ... if only.
    Posts
    3,258
    Thank Post
    182
    Thanked 375 Times in 304 Posts
    Rep Power
    183
    From SupportNet:

    The size of the sims.ldf fluctuates constantly


    Instructions on running ShrinkDBLog.bat


    ShrinkDBLog.zip
    This will need to be unzipped onto a local drive on the machine SQL is installed.

    The batch file will need to be edited - right hand click select Edit


    Edit the following line:-


    osql -S %1 -d %2 -Usa -P -n -i"%temp%\shrink.sql"


    The SA password needs to be added into the osql command line which comes immediately after the -P
    for example, if the SA password is PASS the OSQL line will need to read as follows:-


    osql -S %1 -d %2 -Usa -PPASS -n -i"%temp%\shrink.sql"


    Once this line has been edited, the batch file needs to be run from a DOS prompt.


    Type in the following:-


    e.g. ShrinkDatabase {Server}\{Instance} {DatabaseName}


    A proper example is e.g.


    ShrinkDblog AdminSrv\SIMS SIMS


    This will have truncated the LDF file

    Advice
    User can either:

    1. Create a backup using System Manager with no other users logged in.

    2. Run ShrinkDBlog.bat on the SQL Server. This is available to download from the private files area on SupportNet

    Retrieval code for this file is QYWX / 7362

    In both cases this should shrink the size of the SIMS.ldf to around 1 Mb.

    Please request the batch file from the Service Desk if required.

  3. #3

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28° 31' 48.89", +28° 25' 37.42" ... if only.
    Posts
    3,258
    Thank Post
    182
    Thanked 375 Times in 304 Posts
    Rep Power
    183
    Sorry, just realised you said MDF.

    Surely 2Gb is not that large.

    Edit: Just looked at my Secondary training Mdf. It is nearly 2Gb
    Last edited by creese; 20th May 2010 at 11:44 AM.

  4. #4
    Sarconia's Avatar
    Join Date
    Mar 2010
    Location
    London
    Posts
    315
    Thank Post
    15
    Thanked 34 Times in 31 Posts
    Rep Power
    16
    2GB isn't really that big for an MDF file.

    Do you have an older copy of the .MDF? One which hasn't been messed around so much and is smaller in size? If so, I'd suggest restoring to that database, doing the training and then restoring back to that smaller database each time.

  5. #5

    Join Date
    Jan 2008
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    thanks for the replies - it isnt that big for a School database but we wanted to get it down in size and make it more manageable for training courses.

    We only use a few pupils in the current year for training purposes - so wondered if anyone had any clever way of removing redundant data (eg all pupils with surname not beginning with A, old years of attendance etc) and then compacting? The database has built over the years and we just wanted to streamline it!

  6. #6

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28° 31' 48.89", +28° 25' 37.42" ... if only.
    Posts
    3,258
    Thank Post
    182
    Thanked 375 Times in 304 Posts
    Rep Power
    183
    I can't think of anyway of reducing the size. If you make the A's leavers their data is still in the database. Maybe someone with SQL skills would know how to completely delete some data.

  7. #7

    creese's Avatar
    Join Date
    Feb 2009
    Location
    -28° 31' 48.89", +28° 25' 37.42" ... if only.
    Posts
    3,258
    Thank Post
    182
    Thanked 375 Times in 304 Posts
    Rep Power
    183
    Why exactly is it too large? Are you moving the files around or something?

  8. #8

    Join Date
    Jan 2008
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    yes - there is some movement of them between our different regional centres.

    The databases are sometimes updated by a member of staff prior to a training course - and then distributed round 5 regional centres - so takes a while. i thought there may be a clever way to archive/shrink....

  9. #9

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,485
    Thank Post
    372
    Thanked 657 Times in 537 Posts
    Rep Power
    162
    I think you want to do a backup, compress the backup, encrypt it (if it has actual real data).



SHARE:
+ Post New Thread

Similar Threads

  1. how to shrink an ldf file
    By ful56_uk in forum How do you do....it?
    Replies: 2
    Last Post: 18th May 2010, 05:36 PM
  2. Xibo - Shrink whole website in a window?
    By reggiep in forum AV and Multimedia Related
    Replies: 1
    Last Post: 8th February 2010, 02:21 PM
  3. Missing file: "newsimsdatabase.mdf"
    By button_ripple in forum MIS Systems
    Replies: 1
    Last Post: 8th June 2009, 10:46 AM
  4. Patch city and large mdf file
    By GrumbleDook in forum MIS Systems
    Replies: 10
    Last Post: 4th October 2007, 11:20 PM
  5. Training etc ... what do you do ?
    By pooley in forum Mac
    Replies: 35
    Last Post: 19th April 2007, 08:18 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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