?
Solved

Database upgrade from Access97 to SQL6.5

Posted on 1998-11-11
8
Medium Priority
?
419 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 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

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!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

801 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