| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2009-05-19 : 14:39:52
|
I know similar questions to this one have been asked before, but I can't quite find the answer I'm looking for. I'm a newbie to this also, so please excuse my ignorance on this matter  I just went to take a copy of my live database and put it on my development machine, and noticed that the MDF and LDF files were 17mb each.So, I did the following:- Performed a Full backup of the database
- Performed a Transaction Log backup
- Ran the Shrink tool in Management Studio on the transaction log
The LDF file has now shrunk to 13Mb, which is obviously better. My question though is just what exactly is within the 13mb that remains? Surely after a transaction log backup and running the shrink tool there shouldn't be much left in the file, right?I've read on other sites about how its easy to damage the files by performing certain shrink commands, so I'm trying to be very careful here. Any help on this would be welcome, as my goal is to minimise the amount of file size that I have to download when copying my database to my local development environment.Thank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2009-05-19 : 23:41:57
|
| Put the database in simple mode then do backup the log should shrink a lot.... If you do full backups then do not shrink the log but do backup of logs every 15 minutes or so and full backups nightly..or even do differencial backups and say full once a week.. just depends on your company policy. On your own dev then just do simple . |
 |
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2009-05-20 : 04:16:02
|
quote:
Originally posted by tkizer
17MB and 13MB are super tiny...Are you sure it's megabytes and not gigabytes?
LOL yeah its megabytes alright.Every so often I take a copy of my live database and copy it to my laptop so that I can go out and provide demonstrations to potential clients. Also having a recent copy on my dev machine is good for testing out new code.My problem comes in two forms though. Firstly, the larger the file, the longer it takes to copy to my local network. Second, large files chew up my bandwidth allowance. At this stage obviously the files are small so there's no need to panic, but I was just trying to get my head around what is actually inside the log file after a log backup takes place?Also, how do major players with gigabytes of data take copies from their live servers? They must have enormous bandwidth allowances. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-05-21 : 23:39:28
|
| How are you making a copy of your database? backup restore? or attach/detach/re-attach and copy the MDF LDF? If it's a full backup and restore just set the database to simple mode after you restore it on your local machine or dev machine. If you are detaching your live database, which you probably shouldn't do, and copying the MDFs and LDFs, you don't really need to copy the LDF, just copy the MDF and SQL will create a new LDF when you attach it. There's no point in copying the log file really, when you detach it SQL is going to write any commited transactions anyways. |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2009-05-22 : 04:17:40
|
| HiI use the "SQL Management Object Method" because my hosts say that if I detach the database or take it offline, then it stops their own backup routine working. Within it method it won't let me just select the MDF file to copy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-05-22 : 16:03:52
|
quote:
Originally posted by R
HiI use the "SQL Management Object Method" because my hosts say that if I detach the database or take it offline, then it stops their own backup routine working. Within it method it won't let me just select the MDF file to copy.
Just use BACKUP/RESTORE method as it's much more efficient and will give you an exact replic of the database. Detach/attach takes the database offline, so it is not recommended especially in production environments.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2009-05-23 : 05:08:52
|
| Hi TaraYes my host says that if I use that method it will also wreak havoc with their own backup routine, that uses a third party product.Even for a huge database consisting of several gigabytes, I imagine that after a full backup then log backup, that the log file is still very large (presuming my original problem is a normal occurence). Is it possible to tell therefore what is inside the log file following a backup. I would have thought that there would be nothing inside it if it has been backed up then shrunk? |
|
|
|
nwalter
Starting Member
39 Posts |
Posted - 2009-05-24 : 21:05:44
|
| There is nothing left inside the log after it is backed up but SQL server will still leave the space allocated. Its basically an assumption that if it needed to use that space once it will need it again, and file expansion operations are expensive so its best to just leave it how it is after a backup. You can set the database to auto-shrink (which is not really recommended) or you can just manually shrink the files after the backup to reclaim the disk space. |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2009-05-25 : 05:19:16
|
| Okay, that's very helpfulThanks nwalter |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-05-27 : 15:00:57
|
quote:
Originally posted by R
Hi TaraYes my host says that if I use that method it will also wreak havoc with their own backup routine, that uses a third party product.Even for a huge database consisting of several gigabytes, I imagine that after a full backup then log backup, that the log file is still very large (presuming my original problem is a normal occurence). Is it possible to tell therefore what is inside the log file following a backup. I would have thought that there would be nothing inside it if it has been backed up then shrunk?
I am not saying to perform a transaction log backup, only perform a full backup. Full backups will not interfere with any software including third party products.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
Questions about shrinking the transaction log