| Author |
Topic |
|
ells
Starting Member
25 Posts |
 Posted - 2008-01-21 : 10:30:06
|
| There seem to be a load of answers on this subject. However I am a real newcomer to SqlServer. I have never done any admin so this is a vertical learning experience for me.I have a database MJE. There are two large files for this db calledMJE_Data.MDF (458,048KB)MJE_Log.LDF (833,024KB)I am put of disk space on my data drive and as such some jobs have stopped running :(How do I (in simple terms) delete/truncate/ shrink or any other method to clear the LDF files? I need to get some space back and stop them re-appearing at this size.Many thanks for you patience |
|
|
ells
Starting Member
25 Posts |
Posted - 2008-01-21 : 11:42:31
|
| The size of the log file is now constant. Just checked and it is not growing I just need to shrink it.Cheers.Mark. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-01-21 : 12:23:00
|
| you need to perform a transaction log backup. then you can shrink the logfile to recovery space.if you don't care about point-in-time recovery, you can put the database into simple recovery mode and then shrink the logfile.The bigger issue is that you probably don't have any regular backups/maintenance running in your environment. Look into using the maintenance plan wizard to create them, or if you want to be a little more advanced check out tara's blog and implement her maintenance scripts as sqlagent jobs. find them here http://weblogs.sqlteam.com/tarad/ |
|
|
|
ells
Starting Member
25 Posts |
Posted - 2008-01-22 : 06:24:39
|
| Many thanks.I think I have been seeing one problem but not identifying the real issue. The DM Maintenance Plan does a full backup and this has been failing as a whole job however it has been adding the new backups but not clearing out the old. This then has filled up the drive! Cleared some space, re ran the maintenance plans. Now loads of space.Many Many ThanxMark. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-01-22 : 08:21:56
|
| You still have a problem when your Transaction Log is twice the size of your database, so even though you have loads of space, I would still look into shrinking this. |
|
|
|
ells
Starting Member
25 Posts |
Posted - 2008-01-28 : 04:51:32
|
| Whoops.I changed the recovery mode properties from full to simple. This has freed up the space but has had a very unwanted side affect. The optimization job has gone from a few minutes to 9 hours!I am looking at swapping recovery modes bet need to really understand a long term solution for this.I know details are a bit sketchy but would gratefully receive all suggestions. Even those with pointers on how I can get more info on what the optimization is doing and what needs to be looked at to see why it takes so long.Many thanks.Mark |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-28 : 21:06:54
|
| You have to backup log in schedule as said above if the db is not in simple revocery model. |
|
|
|
hillda01
Starting Member
1 Post |
Posted - 2008-02-02 : 18:57:57
|
| Hi There,I'm having the same problem here...I am using a sql database with my VMWare server and the database was originally set to full so changed it to simple to allow the database to be larger than the log file - but now the log file is 17Gb and the database file is 700mb - I changed the recovery type back to full then performed a log file backup then changed it back but didnt help - tried to shrink database then it just grew by an extra 1Gb :-(Anyone know why this is happening and how I can address it - my server is running out of space :-(RegardsDave |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-02 : 21:55:21
|
| You should do log backup then shrink log file. |
|
|
|
YogeshDesai
Posting Yak Master
136 Posts |
Posted - 2008-02-05 : 06:57:17
|
| Hi try this to truncate the log file,use MJE(I assume that this name of the database)goselect * from sysfiles -- This will give you the name of the log file copy the log file name -- and paste into following query, I assume that the name of the log -- file is MJE_LOG.LDFgoBackupup log MJE with truncate_onlyDBCC SHRINKFILE(MJE_LOG, 10)SQL IN Minds |
|
|
|
|
big log.ldf files