Restore Very Slow - SQL 2008 - NAV 4.0SP2

lombielombie Member Posts: 15
edited 2009-08-05 in SQL Performance
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.
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 ](*,)

Comments

  • ara3nara3n Member Posts: 9,255
    edited 2009-08-05
    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 2008
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • lombielombie Member Posts: 15
    Thanks but can't do that. 64bit OS and can't install MDAC. I have already tried this option.
  • ara3nara3n Member Posts: 9,255
    you don't need to install the client. just copy the client folder and run the finsql.exe
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,255
    Also, have you installed SP1 for sql 2008?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • lombielombie Member Posts: 15
    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.
  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • lombielombie Member Posts: 15
    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 chunks
  • lombielombie Member Posts: 15
    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
  • bbrownbbrown Member Posts: 3,268
    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.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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 Singleton
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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 Singleton
  • David_SingletonDavid_Singleton Member Posts: 5,479
    Also seriously reconsider waiting for 2009SP1.
    David Singleton
  • ara3nara3n Member Posts: 9,255
    Another option is to do a sql backup and restore it on sql 2008.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.