SQL Database Configure

ajayjain · 2013-06-18

Hi Just need some advice before I make any changes on my database

NAN 4.03 on SQL 2005 with 250 users and db size is 500 gb

Currently database is on Auto-grow - Should i change it to manual ?
Database is 95% full, should it be something between 70-80% ?
Navision Security is Enhanced - Should it be Standard ?
Currently NAV SQL server is restarted every sunday, should it be on monthly basis ?

Please advise

Thanks
Ajay

kriki · 2013-06-18

ajayjain wrote:

Currently database is on Auto-grow - Should i change it to manual ?

=> autogrowth is ok but ONLY as a failsafe. It should be grown manually. I generally put the DB-files at 50 MB (small, but fast) and the TL-file at 500MB. If this is growing, some big job is running and it can wait some more while growing the TL-file.

ajayjain wrote:

Database is 95% full, should it be something between 70-80% ?

Best is between 70% and 80%.

ajayjain wrote:

Navision Security is Enhanced - Should it be Standard ?

Standard is better. It avoids the necessity to synchronize the logins (or using dbowner for each user....).

ajayjain wrote:

Currently NAV SQL server is restarted every sunday, should it be on monthly basis ?

How about "never"? Or at least : "as little as possible". Each reset means a cold restart. The server needs again to fill up the cache and that is slow.

ajayjain · 2013-06-19

Thanks very much kirki,

I tried to change my test database security to standard, it was hanging, may be it will take a long time, will try again
Not sure autogrow will trigger at 80%, will check that too.
I saw this thread, clear the cache fix the problem viewtopic.php?f=23&t=55269&hilit=DBCC
confusing me.

kriki · 2013-06-19

ajayjain wrote:

I tried to change my test database security to standard, it was hanging, may be it will take a long time, will try again

You need to put the database in single-user mode. But if someone is in the database, the process hangs indefinitely. To check it out, go in SSMS and run "sp_who2". This shows all the users and in which database they are (be careful, also the session with which you run the command can block the database).
Make the users close NAV/SSMS/... to free the database so it can be put in single user mode.
If needed, you can also use "kill xxx" with xxx the ID to be killed to free the database (don't kill your session with which you try to change the security model!).

ajayjain wrote:

Not sure autogrow will trigger at 80%, will check that too.

It will not trigger at 80%, but only if the database is full.

ajayjain wrote:

http://www.mibuso.com/forum/viewtopic.php?f=23&t=55269&hilit=DBCC

The topic is about something else completely (and I am not sure it is correct anyway. It might have helped in that instance but I am not sure it was connected anyway).

bbrown · 2013-06-19

Don't set single user mode from NAV. Do it from SSMS instead. This has two advantages. First, it will quickly give you a message if others are in the DB. Second, it allows you to force those users out.

bbrown · 2013-06-19

Adequate free space must be maintained in the NAV SQL database. A good rule-of-thumb is to maintain free space of between 20% and 40% of total database size. The exact percentage can be adjusted depending on specific database size and growth patterns. My practice with larger databases is to expand once a year and add enough space to handle the estimated growth for the next year. Plus a bit for a safety net. When the free space drops below 20% expand the data file (*.ndf) to bring the free space to 40%. Repeat the process when it drops below 20%.

Do not rely on Auto-Grow to expand data and transaction log files. Auto-Grow should only be considered a safety net. It is there in case an unexpectedly large process suddenly consumes the available file space. Watch the available space and expand manually during low system activity. Avoid expanding in small increments as this will fragment the file on the drive.

Auto-Grow runs when the free space reaches 0% not as it approaches 0%. It does not say “I’m running out of space. Let me create more before it’s all gone”. What it says is “Oh darn! I ran out of space. Let me take over the system and create some more.”. Unfortunately, it does effectively “take over” the system. While the files are being expanded the system (SQL) will not respond to users. To the users, NAV (SQL) will basically stop responding until the expansion is complete. How long depends on the server’s speed and how much expansion must be done. With larger databases it will get longer. Very large databases can effectively stop responding for several minutes. This is why you don’t depend on Auto-Grow. You want to manage the data size so Auto-Grow never runs

kriki · 2013-06-19

bbrown wrote:

Don't set single user mode from NAV. Do it from SSMS instead. This has two advantages. First, it will quickly give you a message if others are in the DB. Second, it allows you to force those users out.

Also correct, but be sure that SSMS doesn't use that single connection, otherwise NAV will not be able to connect to it.

ajayjain · 2013-06-20

Thanks very much , really helpfull.
I tried last night, changed to single mode, tried to change security to standard
waited for 30 mins. then gave-up, can I do it from SQL

Regards
Ajay

kriki · 2013-06-20

From SQL:
-Open up SSMS
-run "sp_who2" and check who is using your database. Let the log off, kill them ("kill spid-nr").
-Once you are the only one in SSMS (if you didn't select your database, you wont even be using it). right click on your database => properties => tab options => change "Restrict Access" to "SINGLE_USER". Make sure your SSMS doesn't use the database.
-start NAV and connect to your database. Now you can change the security model.
-After changing the security model, remove the toggle "single user" in NAV (this changes the "Restrict Access" back to "MULTIPLE_USERS").

ajayjain · 2013-06-20

Hi Kirki,

I have done all these but when I change the security model from navision it is taking loooong time
Last time I waited more than 30 mins., so I had to kill my session, not sure how long it takes to change the security model.
next time i will try and wait atleast 1 hr.

I think we cannot change the security model from SSMS

Ajay

kriki · 2013-06-20

It depends how many companies and tables you have in the database.
It can take a long time but once you have it, you don't need to worry anymore about synchronizing.
it CANNOT be done using SQL because it is a NAV-thingy.

SQL Database Configure

Comments

Categories