Restore Very Slow - SQL 2008 - NAV 4.0SP2

lombie
Member Posts: 15
I am preparing for a migration from Nav 4.0sp2 to NAV 2009.
I have backed up our live database (SQL 2005) and it has created 29 fbk files total 56.6gb. Our database has 4 companies. The backup took 3 hours. I can live with that.
Destination server is a DELL R7100 - 64bit dual quad core server with 40gb ram. Server 2008 64bit OS. 1gb netcard.
(dedicated no other apps installed or running or any other databases)
Raid config: 2 controllers 3 arrays.
Controller 1 array 1 raid 1 - 2 disks 15k rpm - OS
Controller 1 array 2 raid 1 - 2 disks 15k rpm - SQL logs
Controler 2 array 3 raid 10 - 12 disks 15k rpm - SQL database files
NAV client running on a DELL R7100 - 64bit dual quad core server with 18gb ram. Server 2003r2 32bit OS. 1gb netcard.
(dedicated no other apps installed or running)
Raid config: 1 controller 2 arrays.
Controller 1 array 1 raid 1 - 2 disks 15k rpm - OS
Controller 1 array 2 raid 1 - 2 disks 15k rpm - NAV backup files
Both servers are on the same network switch.
Restore has been running for 24 hours and only up to disk 50% of disk 10, 67% total database. This is far two slow!!!!!
I have read many posts but can't seem to find any obvious problems.
CPU utilisation is 3%.
Network utilisation is 6mbps
What am I missing. Database properties are listed in code below.
Cheers
Charles ](*,)
I have backed up our live database (SQL 2005) and it has created 29 fbk files total 56.6gb. Our database has 4 companies. The backup took 3 hours. I can live with that.
Destination server is a DELL R7100 - 64bit dual quad core server with 40gb ram. Server 2008 64bit OS. 1gb netcard.
(dedicated no other apps installed or running or any other databases)
Raid config: 2 controllers 3 arrays.
Controller 1 array 1 raid 1 - 2 disks 15k rpm - OS
Controller 1 array 2 raid 1 - 2 disks 15k rpm - SQL logs
Controler 2 array 3 raid 10 - 12 disks 15k rpm - SQL database files
NAV client running on a DELL R7100 - 64bit dual quad core server with 18gb ram. Server 2003r2 32bit OS. 1gb netcard.
(dedicated no other apps installed or running)
Raid config: 1 controller 2 arrays.
Controller 1 array 1 raid 1 - 2 disks 15k rpm - OS
Controller 1 array 2 raid 1 - 2 disks 15k rpm - NAV backup files
Both servers are on the same network switch.
Restore has been running for 24 hours and only up to disk 50% of disk 10, 67% total database. This is far two slow!!!!!
I have read many posts but can't seem to find any obvious problems.
CPU utilisation is 3%.
Network utilisation is 6mbps
What am I missing. Database properties are listed in code below.
CREATE DATABASE [NAV40_Test] ON PRIMARY ( NAME = N'NAV40_Test_Data', FILENAME = N'E:\Company\MSSQL\Data\NAV40_Test_Data.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), FILEGROUP [Data Filegroup 1] DEFAULT ( NAME = N'NAV40_Test_1_Data', FILENAME = N'E:\Company\MSSQL\Data\NAV40_Test_1_Data.ndf' , SIZE = 50GB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON ( NAME = N'NAV40_Test_Log', FILENAME = N'd:\Company\MSSQL\Data\NAV40_Test_Log.ldf' , [color=#FF0000]SIZE = 100MB[/color], MAXSIZE = 2048GB , [color=#FF0000]FILEGROWTH = 10GB [/color]) GO ALTER DATABASE [NAV40_Test] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [NAV40_Test].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [NAV40_Test] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [NAV40_Test] SET ANSI_NULLS OFF GO ALTER DATABASE [NAV40_Test] SET ANSI_PADDING OFF GO ALTER DATABASE [NAV40_Test] SET ANSI_WARNINGS OFF GO ALTER DATABASE [NAV40_Test] SET ARITHABORT OFF GO ALTER DATABASE [NAV40_Test] SET AUTO_CLOSE OFF GO ALTER DATABASE [NAV40_Test] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [NAV40_Test] SET AUTO_SHRINK OFF GO ALTER DATABASE [NAV40_Test] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [NAV40_Test] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [NAV40_Test] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [NAV40_Test] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [NAV40_Test] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [NAV40_Test] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [NAV40_Test] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [NAV40_Test] SET ENABLE_BROKER GO ALTER DATABASE [NAV40_Test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [NAV40_Test] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [NAV40_Test] SET TRUSTWORTHY OFF GO ALTER DATABASE [NAV40_Test] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [NAV40_Test] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [NAV40_Test] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [NAV40_Test] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [NAV40_Test] SET READ_WRITE GO ALTER DATABASE [NAV40_Test] SET RECOVERY FULL GO ALTER DATABASE [NAV40_Test] SET MULTI_USER GO ALTER DATABASE [NAV40_Test] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [NAV40_Test] SET DB_CHAINING OFF GO
Cheers
Charles ](*,)
0
Comments
-
Hello
One suggestion, run the client on the sql server on same machine and restore the database. Also have the fbk available. Put them on drive where the OS is.
Also I suggest to get the 2009 Sp1 which will be released in 3 weeks. They have added some performance features for sql 20080 -
Thanks but can't do that. 64bit OS and can't install MDAC. I have already tried this option.0
-
you don't need to install the client. just copy the client folder and run the finsql.exe0
-
Also, have you installed SP1 for sql 2008?0
-
Yes to SP1 for 2008.
No to local run of FINSQL.exe on 64 bit sql box. see attached error message. (image)
.
By the way Client box is also sunning at 3% CPU utilisation.0 -
what is the avg. disk queue length for each drive.
How much memory is sql server consuming?
where are the backup files located on the box where the client is running?
have you looked at query profiler and see anything that is taking long?
Also your log file is too small it should be set to 50 gig.0 -
Are you using 4.0 sp2 executables?
I believe you are suppose to run on sql 2008 with 4.0 sp3 with latest hotfix, or 5.0 with latest hotfixes, or 5.0 sp1, or 2009 executables.
I don't think 4.0 sp2 is supported on sql 2008.0 -
what is the avg. disk queue length for each drive.
os c: 0
logs d: 0.001
data e: 0
How much memory is sql server consuming?
16 gb
where are the backup files located on the box where the client is running?
local on nav client machine
have you looked at query profiler and see anything that is taking long?
nothing unusual just a bucket load of inserts and selects
Also your log file is too small it should be set to 50 gig.
yes I agree my initial log is small but my growth factor is 10gb chunks0 -
Are you using 4.0 sp2 executables?
YES. Although its not supported it does complete the process. Last test took 100 hours. This time still running!!!!!! All I want to do is a restore the DB and then start the upgrade process for NAV 2009.
I believe you are suppose to run on sql 2008 with 4.0 sp3 with latest hotfix, or 5.0 with latest hotfixes, or 5.0 sp1, or 2009 executables.
I have 4.0 sp3 not sure if latest hotfix.
Will try tomorrow. Will I have to convert database?
Cheers
Charles0 -
Be sure to create the data and log files large enough so that they do not need to auto-expand during the restore. Be sure the transaction log is on its own dedicated disk.There are no bugs - only undocumented features.0
-
lombie wrote:Also your log file is too small it should be set to 50 gig.
yes I agree my initial log is small but my growth factor is 10gb chunks
](*,) ](*,) ](*,)
IMO 50gig is not enough, you need more than the size of the backup. I would set it to 100Gig.
NEVER use autogrow on the LOG file. That will have a major performance hit.David Singleton0 -
lombie wrote:Are you using 4.0 sp2 executables?
YES. Although its not supported it does complete the process. Last test took 100 hours. This time still running!!!!!! All I want to do is a restore the DB and then start the upgrade process for NAV 2009.
I believe you are suppose to run on sql 2008 with 4.0 sp3 with latest hotfix, or 5.0 with latest hotfixes, or 5.0 sp1, or 2009 executables.
I have 4.0 sp3 not sure if latest hotfix.
Will try tomorrow. Will I have to convert database?
Cheers
Charles
Why don't you restore direct into a NAV 2009 database? Sp2 is a garbage product and should NEVER be used on SQL. Worst case go to 4.00SP3 with CU8. Newer if possible.
You wont need to convert the database, since this is done during the restore process. you just import the old Navision backup into the new database.David Singleton0 -
Also seriously reconsider waiting for 2009SP1.David Singleton0
-
Another option is to do a sql backup and restore it on sql 2008.0
-
David Singleton wrote:lombie wrote:Also your log file is too small it should be set to 50 gig.
yes I agree my initial log is small but my growth factor is 10gb chunks
](*,) ](*,) ](*,)
IMO 50gig is not enough, you need more than the size of the backup. I would set it to 100Gig.
NEVER use autogrow on the LOG file. That will have a major performance hit.
I thought the original db was 50 gig, and yes, the size of the created db should be the same as the original db.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions