Solved

Database upgrade from Access97 to SQL6.5

Posted on 1998-11-11
8
416 Views
Last Modified: 2012-05-04
I tried to move an Access table(about 53M) to SQL6.5.
The upgrade wizard from Access doesn't quite work so I use BCP. The problem is it always complains about syslogs is full. I have tried to move the log to another device as the online book suggested.(The installation settings are all Default, except I increased the number of locks because the upsize wizard complained about that):

I have tried

DUMP transaction master with no_log

but again the same "Cannot allocate space because segment is full...."


My questions:
1. I don't mind using BCP to move the table, but how to increase the size of the syslog?  
2. the table size is considerable, how do I prevent the log from overflowing when I use BCC?
I am a newbie in RDMS so I will appreciate a detail how-to.
Thanks.
0
Comment
Question by:amlai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:amlai
ID: 1091221
Edited text of question
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091222
BCP load is performed as one transaction, so you need to either break your table into several parts and load it in several steps or increase log size . To increase log size you need to go to Enterpise manager-> Server Manager window, right click on the database you want to expand and choose expand button.in the Log combo box select the device  you want to store the log on and specify size. If you don't have a device with free space you need to create one. In Server Manager right-click on Database Devices and select New from the submenu, all fields there are self-explanitary.
0
 

Author Comment

by:amlai
ID: 1091223
First I have to thank you for your input.
I think I haven't stated my situation more in detail.
I will restate my question and see if you have any insight:

- Transaction log is not important as backup so "truncate log on checkpoint is always check". Also I want to minimize the log size.
 
- Install SQL 6.5 using standard default setting.
Master device is now 93M. Model 1M. tempdb 2M.Pub 3M

- Create 2 more devices called MAINDATA(120M) and MAINLOG(20M)

- Put database dbase1 into maindata, used up all 120M. log in mainlog, so far so good.

- then I started to migrate the 53M table. The wizard complained like crazy.

- Used BCP. But always complained about syslog full
(I have some success in moving part of the table, about a few hundred records)

- tried to move syslogs to MAINLOG(I have done what you mentioned for the last 3 weeks)use expand, then choose log to MAINLOG.

got error:

error5013:Cannot extend the master database into any device other than 'Master'. The later database was aborted.

- create another device call SYSTEMLOG. Tried to use as log for Master. same error

What i want to achieve:
- increase the syslog to 100M so that it will "never" run out.
that 53M database will be update once a week. If the log is bigger than the database itself it should be OK, right?
- slow update on some other small tables(combined no more than 100M)
- no one need to dump log in the future

Question:
1.Is the syslogs the same as tranaction log for Master
2. Does all tranaction will have impact on the master log?(even operation on other databases and tables)
3. What do you think most likely the problem is? Any parameter that you want to know to help you diagnose?

I am now trying this on my workstation and I have to perform the same thing on a remote machine(Access to SQL6.5). That's why I do not want a re-install.

Thank you very much.

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 7

Expert Comment

by:spiridonov
ID: 1091224
When you get the error about full log it specifies database name also. You should increase log for that database , not master. (if you are not loading table into master database .) If you are loading into dbase1 you should increase log of dbase1.
0
 

Author Comment

by:amlai
ID: 1091225
The recommendation has been done and questions 1 and 2 were not answered.

0
 
LVL 1

Expert Comment

by:kponder
ID: 1091226
You are probably running a "Slow" bcp operation. This will log every entry, which can fill up your transaction log. To perform a "Fast" bcp, you need to drop all indexes, and set the SELECT INTO/BULCOPY option to true. This will import the data but not log the modifications.

After doing this, you should perform a full database dump.

1. Yes
2. No
0
 

Author Comment

by:amlai
ID: 1091227
That's the first thing I tried.
Also 20M of log on 53M is already quite excessive.
The problem may lie in somewhere else, which I am desparately trying to locate.

0
 

Accepted Solution

by:
rvindust earned 100 total points
ID: 1091228
You will have to increase the log size until you are able to complete the bulk copy, as stated. I have had the same problem and my Dbase size is 70m and my log size is 100M. Also you should truncate log before beginning the transaction to clear everything out!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question