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 |
|
jsimmons
Starting Member
14 Posts |
 Posted - 2005-09-21 : 11:47:16
|
| I'm getting a message that the transaction log for a specific db is full. The database size is approx. 7GB, however the backup file is 26 GB. What would cause the backup to be so large? (It's set to simple...) What is the resolution to this issue, since the current disk is out of space?Any feedback would be appreciated....thanks! |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-21 : 12:04:40
|
| remove anything you can from the disk to give it more space and then I would try shrinking the datafiles. Do you have other databases on this system?you could also add a 2nd datafile to the database on another drive. all new data will go to the 2nd datafile.-ec |
 |
|
|
jsimmons
Starting Member
14 Posts |
Posted - 2005-09-21 : 12:12:29
|
| Unfortunately, I don't have the option of another disk to use. There are 2 other databases residing on server. Should I add a second data AND log file...or just the log file for the database in question? |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-21 : 12:16:50
|
quote:
Originally posted by jsimmons
Unfortunately, I don't have the option of another disk to use. There are 2 other databases residing on server. Should I add a second data AND log file...or just the log file for the database in question?
if you don't have space, then don't add another datafile. btw, never add a second LDF datafile. Is there other data on these drives that you can move off, to give the system some room? I would go to the other datbases and shrink them (use the truncate free space at the end option, not the move the data around option) This might give you a little breathing room.How big is the LDF on the database in question? you said it was in simple mode, have you ahd a long running transaction or something that has yet to commit?-ec |
|
|
|
jsimmons
Starting Member
14 Posts |
Posted - 2005-09-21 : 12:23:15
|
| No other data can be move off...most of the other data/log files are approximately 2MB in size. The largest datafile is 2GB. The LDF on the database in question is approximately 8GB. Don't see a long running transaction. I'm just puzzled as to why the backup is so large.Thanks for the help! |
|
|
|
wiltech
Yak Posting Veteran
51 Posts |
Posted - 2005-09-21 : 12:45:35
|
| Not knowing how the backup has been configured... Is it possible that the backup is set to "Append to Media" and the backup file contains multiple backup sets.Tony |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-21 : 15:32:27
|
"The database size is approx. 7GB, however the backup file is 26 GB"This is ringing some alarm bells for me.What are the sizes of the MDF and LDF files respectively?If you look at doing a SHRINK in Enterprise Manager (without actually pressing the "Do it now"  button) how much space does it think it can "save"?Sounds to me like something has got "stuck" in the TLog and is preventing reuse of the transaction log.Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-21 : 16:23:13
|
| you could try switching the logmode to full and then back to simple. that should eliminate the logfile for good.-ec |
|
|
|
|
|
|
Transaction Log full