Signum Posted November 2, 2007 Share Posted November 2, 2007 I have a 900mb SQL database which i have just found has a 48gb LDF File for it. This database has now been put into retirement and only accessed for reference. I am doing a FULL backup on the Database and want to be able to reduce the size of the file. I have read that i can back up the Transaction log and this will reduce it, but am v limited to disk space and don't want to fill the drive up with the backup before it reduces it. How big will the backup of the transaction log be with a 48GB LDF File? How much will it reduce it by? Can i limit the size of the LDF file growth? Thanks Link to comment https://www.neowin.net/forum/topic/598247-sql-2000-uber-large-ldf-file/ Share on other sites More sharing options...
jamend Posted November 2, 2007 Share Posted November 2, 2007 http://support.microsoft.com/kb/272318/ To prevent this in the future, set the recovery model to simple... I can't really remember how to do that, just check in books online. Link to comment https://www.neowin.net/forum/topic/598247-sql-2000-uber-large-ldf-file/#findComment-588964836 Share on other sites More sharing options...
Signum Posted December 10, 2007 Author Share Posted December 10, 2007 Ask suggested by Cephas, i set the database to "Simple" recovery model and then (Making sure i had a backup of the MDF, LDF and BAK file) performed a Shrink on the Transaction Log. This did the job, it took the transaction log down from 55GB to 512kb, but increased the MDF file by 300MB. As this database has basically been retired i can cope with the "Simple" model and the backup size. Link to comment https://www.neowin.net/forum/topic/598247-sql-2000-uber-large-ldf-file/#findComment-589049294 Share on other sites More sharing options...
Japlabot Posted December 10, 2007 Share Posted December 10, 2007 Even if the database was still in use, "Simple" should do the job. The size of the log was ridiculous. And the BAK file should be a sufficient backup without the need to keep a backup of the MDF and LDF files. Link to comment https://www.neowin.net/forum/topic/598247-sql-2000-uber-large-ldf-file/#findComment-589049304 Share on other sites More sharing options...
Signum Posted December 10, 2007 Author Share Posted December 10, 2007 Only took the MDF and LDF File backups at the time of "Messing with it" just incase it all went ###### up. Happy i got 1/3rd of my HDD back now :-) Link to comment https://www.neowin.net/forum/topic/598247-sql-2000-uber-large-ldf-file/#findComment-589049460 Share on other sites More sharing options...
Garry Posted December 12, 2007 Share Posted December 12, 2007 For reference, if you're not running a maintenance plan (backup and shrink) on the database than the LDF file will just continue to grow and grow. I appreciate that you're asking about maintenance plans in the other thread, but if you were wondering why you had a 48GB LDF then that's why. If this happens in future and you want to keep the backup mode on Full then just do a manual backup of the database (then delete it, if you want) then do a shrink. Link to comment https://www.neowin.net/forum/topic/598247-sql-2000-uber-large-ldf-file/#findComment-589055305 Share on other sites More sharing options...
Recommended Posts