?
Solved

Database upgrade from Access97 to SQL6.5

Posted on 1998-11-11
8
Medium Priority
?
428 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
8 Comments
 

Author Comment

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

Expert Comment

by:Victor 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:Victor 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 200 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

571 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