Hi guys,
I would like to know if an SQL database can be extended? Might look funny, but i got 1 client whose SQL DB is showing, so she wants to make a backup of it and extend it.
As far as the backup is concerned, i believe i can go through a simple backup process (tools> backup), but correct me if am wrong.
Regarding the SQL showing 99%, can this be extended? (There is no ‘expand’ option under File> database). If not, how can i make this to show more free space on the DB?
Cheers
The Lord
The SQL Database should automatically expand its size. The database size limit depends on a number of things including the version of SQL you are running (Express, Standard, Enterprise, etc).
When you do File, Database, Alter, what does the Database Files tab have to say? There is a growth percentage, maximum size, etc there.
Hi Matt,
Under File > Database > Alter, if i select the ‘Database Files’ tab it shows 2 database names, 1 .mdf and the 2nd .ndf. they both showing 10% File Growth. Does taht mean the DB is only at 10%?
When i do File > Database > Information, it shows 99% used. What does this mean?
Cheers,
The Lord
I’m going to strongly recommend that you break out the NAV documentation. Whether it be the Installation and Configuration manual or something else. You should also sit with someone at your company who has more experience dealing with customers on SQL.
A database is generally defined with two files - the .mdf file for the data, and the .ldf file for the log. Each file has a primary allocation and then one or more secondary extents. It sounds like your database has the default configuration with 10% of the primary added each time more space is needed.
In general I don’t like the default, since as the database gets large each secondary extent gets larger and larger. Moreover, it takes time to format large secondary extents.
You might change the secondary extent to grow at intervals of 100MB.
The other question is what should the primary extent be changed to, such that you have some room to grow in the database. What is the total database size, and what is the value of the primary extent?
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.