Solved

Database upgrade from Access97 to SQL6.5

Posted on 1998-11-11
8
377 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: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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now