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]

Have you been backing up your transaction log? It will continue to add records to it until you back them up. SUggest you read about transaction log backups in BOL.

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

 

[nice95gle]

Why don't you try detaching your DB? Rename the Trans log file to something like PTBD_log_bak and then reattach your DB. Make sure your scripts are committing very 50,000-75,000 records or so. And clean out the Win Event Log.

Don't forget before your do anything, take a good backup of your db.

Well Done is better than well said
- Ben Franklin

 

[SQLSister]

Detaching won't do any good, he has run out of disk space.

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

 

[nice95gle]

If he creates back up log file and move it somewhere else wouldn't that free some space? Or if he just detach and move his log file to a drive with space and point to the moved file, wouldn't that free up space.

My role is changing from developer to DBA so this is very good to know.

Thanks

Well Done is better than well said
- Ben Franklin

 

[ptheriault]

Best option is to always backup your log file the run DBCC SHRINKFILE.
The detach method should be used only as a last resort, that way if he'll have the transactions in a .bak file if he ever needs them.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NervousRex]

Whats the role when you have to do it all?

Designer/Developer/DBA


Thanks guys, I created a backup, adding in some more COMMITS now. Anything else I should know for executing large scripts like this?

 

[ptheriault]

a large script???
Can you post what you are about to run?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[nice95gle]

Thanks Paul and SQLSister
Rex I would like to see the script also.

Well Done is better than well said
- Ben Franklin

 

[NervousRex]

The file is 17k lines long, I simply am not gonna post the complete file. I had it broken down into about 20 files, but I was looking for a way to just run 1 file, that will handle doing the complete transfer instead of kicking one off, and having to keep checking back.

But what it does is creates about 20 stored procedures which contain the code above broken down by the sections of the DB (eg: employees, clients...)

After creating them, it executes them, and then finally will drop them on completion. When I had them seperated into 20 files, they each worked with no problem, but now I am stuck here....

Basically its this...but for every table in the database

--	EMPLOYEE DEPARTMENT TYPE ******
SET IDENTITY_INSERT Employee_Department_Type ON

PRINT ' '
PRINT '** INSERTING DATA EMPLOYEE DEPARTMENT TYPE **'

INSERT INTO [PTDB].[dbo].[Employee_Department_Type]
           ([Department_ID]
           ,[Department])
SELECT	EDT.Department_ID,
		EDT.Department_Desc
FROM	SOLO1.PTDB.DBO.Department_Type AS EDT

SET IDENTITY_INSERT Employee_Department_Type OFF


--	EMPLOYEE EMPLOYMENT TYPES *********
SET IDENTITY_INSERT Employee_Employment_Type ON

PRINT ' '
PRINT '** INSERTING DATA EMPLOYEE EMPLOYMENT TYPES **'

INSERT INTO [PTDB].[dbo].[Employee_Employment_Type]
           ([Employment_Type_ID]
           ,[Employment_Type])
SELECT	EET.Employee_Employment_Type_ID,
		EET.Employee_Employment_Type_Name
FROM	SOLO1.PTDB.DBO.Employee_Employment_Types AS EET

SET IDENTITY_INSERT Employee_Employment_Type OFF


--	EMPLOYEE PAY RATE TYPE *******
SET IDENTITY_INSERT Employee_Pay_Rate_Type ON

PRINT ' '
PRINT '** INSERTING DATA EMPLOYEE PAY RATE TYPE **'

INSERT INTO [PTDB].[dbo].[Employee_Pay_Rate_Type]
           ([Pay_Rate_Type_ID]
			,[Pay_Rate_Type])
SELECT	EPRT.Pay_Rate_Type_ID,
		EPRT.Pay_Rate_Type
FROM	SOLO1.PTDB.DBO.Employee_Pay_Rate_Type AS EPRT

SET IDENTITY_INSERT Employee_Pay_Rate_Type OFF


-- EMPLOYEE STAFFING AGENCY ******
SET IDENTITY_INSERT Employee_Staffing_Agency ON

PRINT ' '
PRINT '** INSERTING EMPLOYEE_STAFFING_AGENCY **'

INSERT INTO [PTDB].[dbo].[Employee_Staffing_Agency]
           ([Agency_ID]
           ,[Agency_Name])
SELECT	ESA.Agency_ID,
		ESA.Agency_Name
FROM	SOLO1.PTDB.DBO.Staffing_Agency AS ESA

SET IDENTITY_INSERT Employee_Staffing_Agency OFF


-- EMPLOYEE TYPE *******
PRINT ' '
PRINT '** INSERTING EMPLOYEE_TYPE **'

SET IDENTITY_INSERT Employee_Type ON

INSERT INTO [PTDB].[dbo].[Employee_Type]
           ([Employee_Type_ID]
           ,[Employee_Type])
SELECT	ET.Employee_Type_ID,
		ET.Employee_Type
FROM	SOLO1.PTDB.DBO.Employee_Type AS ET

SET IDENTITY_INSERT Employee_Type OFF

-- EMPLOYEE TEAM TYPES *********
PRINT ' '
PRINT '** INSERTING EMPLOYEE_TEAM_TYPES **'

SET IDENTITY_INSERT Employee_Team_Types ON

INSERT INTO [PTDB].[dbo].[Employee_Team_Types]
           ([Employee_Team_Type_ID]
           ,[Employee_Team_Name]
           ,[Active]
           ,[Team_Leader_Employee_ID])
SELECT	ETT.Employee_Team_Type_ID,
		ETT.Employee_Team_Name,
		ETT.Active,
		ETT.Team_Leader_Employee_ID
FROM	SOLO1.PTDB.DBO.Employee_Team_Types AS ETT

SET IDENTITY_INSERT Employee_Team_Types OFF

The Output:

** INSERTING DATA EMPLOYEE DEPARTMENT TYPE **

(14 row(s) affected)
 
** INSERTING DATA EMPLOYEE EMPLOYMENT TYPES **

(2 row(s) affected)
 
** INSERTING DATA EMPLOYEE PAY RATE TYPE **

(2 row(s) affected)
 
** INSERTING EMPLOYEE_STAFFING_AGENCY **

(6 row(s) affected)
 
** INSERTING EMPLOYEE_TYPE **

(27 row(s) affected)
 
** INSERTING EMPLOYEE_TEAM_TYPES **

(5 row(s) affected)

 

[ptheriault]

NervousRex,
I was just looking for your syntax on how you plan on backing up your log. We don't need to see the insert script.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[NervousRex]

Oh, I thought you meant the transfer script (which I thought was the problem)

I don't have a script for the backup log. I'm still reading on what I need to do with it. I thought simply creating a backup of it from Management Studio was going to reset the file, and get me through testing the script.

Which I did, but the log file is still 20gb. So still messing with that.

 

[ptheriault]

Which I did, but the log file is still 20gb

Now look up DBCC SHRINKFILE in BOL. that command will truncate your log back down to manageable size.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

Look up how to truncate the transaction log inthe backup command.

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

 

[NervousRex]

So this is what I got:

USE PTDB
GO
CHECKPOINT
GO
BACKUP LOG PTDB WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (PTDB_log)
GO

 

[SQLSister]

Rememeber once you do this and free up some space, immediately back up your database and then schedule regular transaction log backups.

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

 

[NervousRex]

So as I run my transfer script, it hits a point (1/4 of way in, but not same point as in original post), and takes a good minute or so on a simple insert statement, and the log file grows by about 2GB at that point....then rest of the of the script runs and file size never changes after that

 

[ESquared]

If you read up on how data and log files are allocated and used, you'll see this makes sense.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[SQLSister]

Check your settings on autogrowing your transaction log. It might be set to grow by a large amount, then the rest of your script doesn't fill it up, so it doesn't grow anymore.

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

 

[NervousRex]

It was set at 10% of file size, I changed it to 10MB, ran it again...grew by 10MB, 10MB again, then same thing, hits a spot and grows to 2GB but then never grows again.

Max file size is set to 20GB, which is where it was at in the initial post.