Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Database upgrade from Access97 to SQL6.5

Posted on 1998-11-11
8
Medium Priority
?
425 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: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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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