cancel
Showing results for 
Search instead for 
Did you mean: 

Freeing disk space by nulling nvarchar columns

0 Kudos
1,445

Hello, all In my DB there are multiple log tables, which store app info connection property. After certain time passes, this value is no longer used and the field can be annulled in some records. Does DB space usage decrease when long nvarchar is set to null?

Thank you Arcady

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor
0 Kudos

Not immediately, because SQL Anywhere never shrinks the database files by itself, as documented here. You will need to do a reload to shrink the database files.

However, deleting rows or setting fields to NULL will mark their previous place as "free" so further data can be placed there, that way reducing the need for further file growth.

Please also have a look at that question, which also lists some diagnostic tools.

0 Kudos

And how long would it usually take before the freed space is reflected in sa_table_page_usage() procedure? This is what we need, not actual shrink of the DB file.

VolkerBarth
Contributor
0 Kudos

Can't say that because I'm not sure whether sa_table_page_usage() will show when pages are only "partially freed" because there is still data on them. You may also look at the sa_table_fragmentation system procedure.

Here's another FAQ why it may take some time until the process of free'ing deleted data is really observable.