| Author |
Topic |
|
helpme
Posting Yak Master
141 Posts |
 Posted - 2004-11-12 : 09:05:59
|
| I am trying to execute a dts package. When I execute it, it runs for about 2 seconds and cancels. When I click on the step that cancelled, it says the log file for the database is full, back up the transaction log (transaction log is 1 gig). I back up the transaction log (the backup takes about 2 seconds and only outputs a very small file) and try again and I still get the same message (again, running only a couple of seconds). It's hard to believe that it's truly filling up the transaction log in such a short amount of time. What am I missing? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-12 : 09:13:28
|
| Is the tr log already full? i.e. is there an open transaction and your backup isn't doing anything. Have a look at the free space.Also check the message to see that you are dealing with the correct database (not tempdb for instance) and correct server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-12 : 11:18:35
|
| It's definitely the correct database and server. On the backup, it says it's completing successfully, and it replaces the backup file on disk. But I must not be doing something right, because the dts package immediate cancels and says the log file for the database is full. I'm pretty new at sql server, so I could be overlooking the obvious somewhere. |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-13 : 08:18:49
|
| Any other suggestions. Any replies are appreciated. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-13 : 10:27:02
|
| What does it say about the free space in the log?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-13 : 19:20:12
|
| There is over 150 mg free space |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-14 : 00:42:00
|
| 1g log file, did backup now 150 mb free - something wrong there.Do you need log backups? can you set the database to simple mode.Did you check for open transactions?What is the dts package trying to do?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-15 : 06:13:19
|
| The DTS package is creating and populating a table. How do you check for open transactions (bear with me, I'm new at this)? |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-16 : 12:54:49
|
| Any help/suggestions on this are appreciated. |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-11-16 : 14:54:17
|
| how do you determine the log file size??=========do you have enough space in disk?*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-16 : 18:32:44
|
| Please post the exact error message.Tara |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-16 : 18:42:41
|
| there is several gig free on the disk drive. I do a backup of the transaction log, and it says it's successful, but I don't think it's doing anything. Just for a test, after I backed up the transaction log, I tried doing the following:create table testtbl (testcol char(1))and I still get the 'log file for database is full' message. So it seems likethe backup on the transaction log can't be working. I can't figure out why it isn't working. Have you ever run into this situation? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-16 : 18:44:05
|
| Please do not paraphrase what the error is saying. We need to see the entire thing exactly as it is in order to help.Tara |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-16 : 18:48:11
|
| The message is "The log file for database 'XYZ' is full. Back up the transaction log for the database to free up some log space". |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-16 : 18:49:38
|
| I just wanted to make sure it didn't mention tempdb in there. What command are you using to backup the transaction log?Tara |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-16 : 18:53:05
|
| I'm just using EM. Right clicking on the database in question, selecting all tasks, then selecting backup database, and selecting transaction log backup. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-16 : 18:55:16
|
| Do you have regularly scheduled transaction log backups?Tara |
|
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2004-11-16 : 19:09:04
|
| Not yet. This is a new database that we are trying to build (we're really just getting started on it). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-16 : 19:10:55
|
| If you aren't performing regular transaction log backups, then change your database recovery model to simple (right click on DB, properties, options tab).Tara |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-16 : 20:13:32
|
| or you can unrestrict the growth of your log file if it's set to restricted.--------------------keeping it simple... |
|
|
|
chetancool
Starting Member
34 Posts |
Posted - 2004-11-17 : 02:07:04
|
| Unrestricting the growth of the file may solve the issue until and unless u r having a good amout of space in the hard dirve. Other wise u can take a transaction log back up with truncate only option.. it will backup the transaction log and erase all the commited transaction from the log file.. so that size of the log file can be reduced.cjain |
|
|
|
Next Page
|
backup message