Shrink a Training MDF

[suzy_reid]

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

[creese]

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.

[creese]

Sorry, just realised you said MDF.

 

Surely 2Gb is not that large.

 

Edit: Just looked at my Secondary training Mdf. It is nearly 2Gb

Edited May 20, 2010 by creese

[Sarconia]

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.

[suzy_reid]

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!

[creese]

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.

[creese]

Why exactly is it too large? Are you moving the files around or something?

[suzy_reid]

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....

[matt40k]

I think you want to do a backup, compress the backup, encrypt it (if it has actual real data).