Database size control

[menghuihantang]

I am using Labview database tools to access my database. Two questions puzzle me

One is abou the database lock. It seems every time a connection to the database, it changes to lock mode. Sometimes you still can open it, other times you cannot. I am wondering how this database lock can be controlled.

The other question is about the size. In the database, it requires deleting certain even all the tables at times. But I noticed that the size of the database kept increasing. Even it appeared to be empty, its size still larger than before. Does the deleting only make the tables invisible or what? Why the size keeps going up?

They might not be directly related to Labview, but hopefully someone can help me with the puzzles. Thanks much.

[alukindo]

Hi:

Regarding your first question, I am not sure what you mean by 'database lock' and how it avoids re-connecting. Please explain that further.

Non Elastic Behavior of Databases

---------------------------------

Otherwise, databases such as MS Access and MS SQL Server have a behavior of expanding in size when data is added and will not reduce in size when data is deleted. What actually happens is that the database continues to occupy 'un-used' space. This behavior is also described as non elastic expansion behavior of databases.

Reclaiming Un-used Space

---------------------------

However, you can still intervene to re-claim the unused space in two ways: (1) Manually, by using a database management tool; (2) Programmatically, by writing a special SQL command to shrink the data file.

To shrink the database manually see illustrations below. To shrink an MS SQL Server database programmatically, you can use the following Command:

DBCC SHRINKDATABASE (0,0).

You must log on to the target database before launching this command.

Anthony L.

[menghuihantang]

QUOTE (alukindo @ Sep 11 2008, 09:12 PM)

Hi:

Regarding your first question, I am not sure what you mean by 'database lock' and how it avoids re-connecting. Please explain that further.

Non Elastic Behavior of Databases

---------------------------------

Otherwise, databases such as MS Access and MS SQL Server have a behavior of expanding in size when data is added and will not reduce in size when data is deleted. What actually happens is that the database continues to occupy 'un-used' space. This behavior is also described as non elastic expansion behavior of databases.

Reclaiming Un-used Space

---------------------------

However, you can still intervene to re-claim the unused space in two ways: (1) Manually, by using a database management tool; (2) Programmatically, by writing a special SQL command to shrink the data file.

To shrink the database manually see illustrations below. To shrink an MS SQL Server database programmatically, you can use the following Command:

DBCC SHRINKDATABASE (0,0).

You must log on to the target database before launching this command.

Anthony L.

Thanks much, Anthony. That Non Elastic Behavior is interesting and explains all the puzzles I have about database size staff.

About the first question, it's about the the automatic generated .ldb file when you open a database. I think it's about access security. But I don't understand why at times you still can open it, other times cannot at all, and other times only one user can access it exclusively. I don't set up any database in anyway, therefore it doesn't make sense they behave differently. I am sure it's something easy, but I just haven't touched it yet.

[alukindo]

Hi:

Regarding *.ldb (lock database file) this one is specific to MS Access databases. This file stores info on users logged-on, as well as other info that protects your database from concurrent editing. Google the keyword: "ldb Access" and you will get plenty of info.

So by-and-in-itself the *.ldb file does not necessarily prevent multiple users from using the database or from re-connecting multiple times.

But to prevent problems you need to make sure that the following is implemented:

1. Close your connections to the MS Access database and set the reference to nothing (destroy reference) after the LabVIEW session is done using the database. If you forget to do this and LabVIEW re-opens the connection multiple times, then MS Access database will at one point lock out new connection attempts.

2. Make sure that the shared folder hosting the MS Access database has 'read/write' access and if-needed 'delete' priviledges as well.

I hope that this helps

Anthony L.