Autogrowth Error AND Transaction Log is Full 4

[NervousRex]

While running a script I created that will be transferring data from our existing database (SQL 2000) and converting the data to our new system in SQL 2005 and inserting it. The script got about 1/4 of the way through, and then errored with:

Msg 1105, Level 17, State 2, Procedure Data_Transfer_List, Line 18
Could not allocate space for object 'dbo.SORT temporary run storage: 191681214480384' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.


I attempted to rerun it and then got: (I don't like that last line)

Msg 9002, Level 17, State 2, Procedure Data_Transfer_List, Line 18
The transaction log for database 'PTDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Msg 9002, Level 17, State 2, Procedure Data_Transfer_List, Line 18
The transaction log for database 'PTDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Msg 3314, Level 21, State 4, Procedure Data_Transfer_List, Line 18
During undoing of a logged operation in database 'PTDB', an error occurred at log record ID (561:25696:98). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Msg 9001, Level 21, State 5, Procedure Data_Transfer_List, Line 18
The log for database 'PTDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Msg 3314, Level 21, State 4, Procedure Data_Transfer_List, Line 18
During undoing of a logged operation in database 'PTDB', an error occurred at log record ID (561:25760:3). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Msg 9001, Level 21, State 1, Procedure Data_Transfer_List, Line 18
The log for database 'PTDB' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Msg 3314, Level 21, State 5, Procedure Data_Transfer_List, Line 18
During undoing of a logged operation in database 'PTDB', an error occurred at log record ID (557:3274:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

 

[SQLSister]

Don't leave it at 10 mb. That's too small. Inthe spot where it grwos drastically , look at the table that is being inserted to - is there a trigger on the table that might be updating all the rows rather than just what was inserted? That could cause the big growth. On the other hand now that you have room on your hard drive and can run the script is it really a problem? As long as you do transaction log backups, you won't fill up your hard drive again.

"NOTHING is more important in a database than integrity." ESquared

 

[NervousRex]

Ok, I can't explain why it isn't growing after the big spot. And I kinda figured out what the issue is with that. Its not one query that is causing it like originally thought. What happens is the ouput in the message window stops and I guess queues up, then dumps the next few statements all at once. So it appears one is taking longer than it should, but in fact it is a few steps beyond where it is.

As you can see in my above post where I displayed the output of my script, I PRINT the current statement its executing, and it displays the row(s) affected.

So I guess the next question is, how can I be sure the output is being flushed so that I know whats going on without having to open profiler to see where its at. Like that last 30 steps don't even display till its finished.