SQL restore error in NAV 6.0 SP1

cveale

Now for starters, I am not very good in SQL, but I was restoring a NAV backup in SQL, and this error message came up.

The following SQL Server error or errors occurred when accessing the Value Entry table.

Could not allocate space in the database because the 'Data Filegroup 1' filegroup is full. Does this just mean I don't have enough room on my laptop or does it mean I don't have enough room in a certain directory/folder on my laptop ?

how do I find out wether Data Filegroup 1 is full ?

Thanks.

kapamarou

Which version of SQL do you use?

Open the SQL Management Studio and check your datafiles size and also your available hard disk space...

Give us some more info on your setup.

cveale

I am using SQL 2005.

Okay, I just checked out the data files, the sizes are....
mdf = 40 MB
ndf = 10612 MB
ldf = 17218 MB

I only have 25 gig left on my computer. I think I found out why the error message came up. I thought the DB was only going to be 15 GIG. The database in a native NAV database is 15 gig, so I assumed it would be 15 gig once I restored it into SQL, but it tooks to be about twice the size in SQL.

Question, do you have a NAV/SQL install cheat sheet for implementing NAV on SQL ? For instance, putting the log files of different directories ? any default settings to check ?

Thanks in advance !!

kapamarou

cveale wrote:

Question, do you have a NAV/SQL install cheat sheet for implementing NAV on SQL ?

Yes... It's www.mibuso.com It's packed with info.

Are you talking about a test environment or a Productive System for the client? There are many things to consider. For instance, log files should be on a separate physical disk. But usually this is done by the IT department, and since you are not that comfortable with SQL Server then you should ask for assistance.

rdebath

cveale wrote:

I am using SQL 2005.

Okay, I just checked out the data files, the sizes are....
mdf = 40 MB
ndf = 10612 MB
ldf = 17218 MB

I only have 25 gig left on my computer. I think I found out why the error message came up. I thought the DB was only going to be 15 GIG. The database in a native NAV database is 15 gig, so I assumed it would be 15 gig once I restored it into SQL, but it tooks to be about twice the size in SQL.

Question, do you have a NAV/SQL install cheat sheet for implementing NAV on SQL ? For instance, putting the log files of different directories ? any default settings to check ?

Thanks in advance !!

Most of the space your DB is using is for the ldf file, but this space is only needed during the restore, once the restore is completed the log can be shrunk and the database is normally less that three times the size of the FBK files, like the Native DB and unlike the Naive DB you can shrink it to fit the data. Note: If the database isn't using "Simple" model the transaction log will have to be backed up before the ldf file can be shrunk. You may also need to 'CHECKPOINT' before you can shrink the log file all the way.

If you need to restore into limited space first thing you should try is to find some more temp space for the log file on another drive, even a USB drive (commits are very rare during the restore), once done you can shrink the log, detach the database, move the log file and reattach the database in it's new location.

Second possibility is to do the restore to SQL on a big machine and do an SQL backup on that machine to restore to the limited space machine. An SQL restore needs no additional temp space, but the location the BAK file is stored in needs to be accessible to the user that the SQL service is running as.

The third option is to

1. Create the database in simple mode
2. When the restore fails, shrink the log file
3. Select file, database, restore again and when it asks continue the restore; if it doesn't ask this won't work.
4. Repeat until the restore completes.
5. Do a final shrink of the logfile to 2MB, then expand it to the correct size.

In fact you should do (5) whichever option you use so there aren't too many virtual log files.
Also, it's normally best to preallocate the SQL database files, but for this restore I would suggest you allow auto expand with a size of about 20-30% the total size of the FBK files (on the ndf and ldf files) . Your database will (of course) be fragmented after this, but it should be a few large chunks, so not too bad.

BTW: viewtopic.php?f=23&t=17082

Once last point, shrinking the ndf file (with 'reorganise data') after an fbk restore is likely to reduce performance. But normal laptop drives aren't that fast anyway.