Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ells
Starting Member
25 Posts |
 Posted - 2008-02-13 : 11:00:30
|
| on a deve/prod server I have an issue within the data directory for SQL Server. I am really running out of disk space and the obvious targets to save space are the transactional log files.The space taken up by .ldf files is phenomenal mate:for one instance----------------all .ldf files for instance - 113 GBall .mdf files for instance - 42.3 GBone database on this instance 20,026,432KB data file (mdf)111,137,920KB log file (ldf)On this database I have selected the properties and this is what is showing on the tabsData Files tab==============Auto grow file box tickedby 10%unrestricted file growthTransaction Log===============Auto grow file box tickedby 10%unrestricted file growthOptions tab===========Recovery - FULLAuto update statistics tickedAuto create statistics tickedI am really desperate to get this sorted. I have just had a week off and all it takes is for one backup routine to not work correctly and hey ho the disk drive is full.All suggestions gratefully received.Thanks.Mark. |
|
|
stevelund
Starting Member
5 Posts |
Posted - 2008-02-13 : 11:34:23
|
| How often are you backing up the transaction logs? |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-02-13 : 11:39:10
|
| You could do a BACKUP LOG <myLogFile> WITH TRUNCATE_ONLY, for an immediate hit. Have you looked at potentially shrinking the files. You could also look at spreading the files across multiple hard disksJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
ells
Starting Member
25 Posts |
Posted - 2008-02-13 : 17:18:39
|
| I am very new to this so want to tread very carefully. How can I tell hoe often the transaction logs are being backed up. The DB gets a full backup every day so It should be able to do a backup and reduce the size of the trans log files.I really need to figure a way of doing this regularly. I a, a neal newbie at dba type stuff, more used to developing.Thanks for the swift response. |
|
|
|
MuadDBA
628 Posts |
Posted - 2008-02-13 : 19:10:34
|
| A full backup isn't going to help with your tran log problem unless you really don't want any recoverability between full backups, in which case you coudl set the DB to truncate the log on a checkpoint. But this is not really the best solution.You need to set up jobs to do a backup log at an interval that makes sense for you, and then store those log backup files off to tape after a day or so and purge them.For your immediate problem, you're going to need to do dbcc sqlperf(logspace) to see the size and percentage used of your transaction logs. If it is 111GB and pretty full, I would recommend taking a maintenance window, restricting the logins on the DB, and then doing a full backup (ie backup database xxx to etc). Once the backup is completed, then truncate your transaction log and shrink it to somethign more reasonable. Only you will know how your users use the DB, for instance if they regularly load large quantities of data, you might want a trans log that's 1/4 to 1/2 the size of the database or even more. If you're having problems getting the log to shrink, look in our script library for the sp_force_shrink_log stored proc and use that to get it to the size you want.Once you have the ldf file to the size you want, put the DB back in full recovery mode, set up some transaction log backups (we do ours every hour) and then re-enable the users. REmember, though, to implement a purging schedule for your log backups or you will run into the same problem again. Hope this helps. |
|
|
|
|
|
|
|
|
Size of ldf files - needs sorting