|
To shrink sybase database to reclaim physical space we are doing dbunload for whole db. As it is taking more time, we are planning to do unload only specific tables to reclaim the physical space. Can any one please give me an example of how to do unload specific tables. dbunload -d -e |
|
There is no mechanism to reduce the size of an existing database file. The "dbunload - dbinit - reload" process results in a NEW database file that is smaller than the old one so the overall effect is one of shrinking. So... running dbunload for only a few tables isn't going to shrink anything. Question: Does your database grow in size again after you shrink it? If so, what is gained by the process of shrinking it? Free space is re-used for new rows if you leave it alone, but if you shrink it, extra effort is required to re-grow the file to make room for new rows... and that newly allocated space will be fragmented if you're running on Windows. Folks often do exactly the opposite: ALTER DBSPACE SYSTEM ADD to expand the file beyond what is required, defragment the physical file, and then leave it alone for SQL Anywhere to use and re-use pages within the file. If you are unloading and reloading to reorganize the tables inside the database, you can accomplish that with the REORGANIZE TABLE statement without having to do an unload/relaod ... but you should check first to make sure it's necessary. Hi Breck, Thanks for you reply. Currently we are doing the same. We are creating a new database file and replacing the older file. Coming to your question, after compression, the db size is growing in size based on data logging. We are periodically checking the DB size and doing operations.If the DB size limit exceeds the limit, some of the operations we are not able to do. So to get out from that we are doing DB compression. Since it is taking more time, we are planing for dbunload for specific tables. If REORGANIZE TABLE will help to our case, can you please explain me the procedure and how it is differ from dbunload.
(16 Nov '11, 05:28)
srisudha
Replies hidden
Reorganize table is all about minimizing table fragmentation inside the database file, rather than about saving space. From the help: Reduce table fragmentation Table fragmentation occurs when rows are not stored contiguously, or when rows are split between multiple pages. These rows require additional page access and this reduces the performance of the database server. The effect that fragmentation has on performance varies. A table might be highly fragmented, but if it fits in memory, and the way it is accessed allows the pages to be cached, then the impact may be minimal. However, a fragmented table may cause much more I/O to be done and can significantly reduce performance if split rows are accessed frequently and the cost of extra I/Os is not reduced by caching. While reorganizing tables and rebuilding a database can reduce fragmentation, doing so too frequently or not frequently enough, can also impact performance. Experiment using the tools and methods described in the following section to determine an acceptable level of fragmentation for your tables. If you reduce fragmentation and performance is still poor, another issue may be to blame, such as inaccurate statistics. See Diagnosing performance issues. Determine the degree of table fragmentation Checking the table fragmentation just once is not helpful in determining whether to defragment to improve performance. Instead, rebuild your database and check the table fragmentation to establish baseline results. Then, continue to check the table fragmentation periodically over an extended length of time, looking for correlation between the change in fragmentation to changes in performance measures. This method helps you determine the rate at which tables become fragmented to the degree that performance is impacted, and so determine the optimal frequency at which to defragment tables. To obtain information about the degree of fragmentation of your database tables, use one of the following methods: The sa_table_fragmentation system procedure. See sa_table_fragmentation system procedure. The Fragmentation tab in the SQL Anywhere plug-in. The Fragmentation tab provides a graphical representation of the results from running sa_table_fragmentation system procedure on base tables. See Using the Fragmentation tab (SQL Anywhere plug-in). To determine the degree of fragmentation (SQL) Connect to the database as a user with DBA authority. Run the sa_table_fragmentation system procedure. For example, run the following statement to get information about all tables in the database: CALL sa_table_fragmentation( ); See sa_table_fragmentation system procedure.
(16 Nov '11, 05:41)
Justin Willey
Please explain what you mean by this statement "If the DB size limit exceeds the limit, some of the operations we are not able to do." Generally speaking, the file size has little to do with SQL operations.
(16 Nov '11, 05:53)
Breck Carter
Are you facing a limited disk space situation, or what does prevent you from doing "some operations" when the DB size exceeds the limit?
(16 Nov '11, 05:53)
Volker Barth
|
|
Just to answer the direct question in the subject line. You could create a new user, give that user permission only to the tables you want to unload, and then run the dbunload utility with the authentication credentials of the new user. AFAIK, dbunload expects DBA permissions. At least in my tests, reloading with a non-DBA user failed with missing permissions to select from SYSUSERPERM.
(22 Nov '11, 03:30)
Volker Barth
|
|
We are facing diskspace issues. The limit is 150GB, If the DB size reaches to >150GB, after deleting the data from tables, actual DB size is reducing to 120GB,but physical size is showing as 150GB. To reclaim the physical DB we are doing DBUnload for compressing DB option. As it is taking more time(more than 6 hours) we are planning to do with specific tables. Please suggest me which one will help in this case... 3
Given the low prices of disk space nowadays, I would suspect that any unload routine you plan to develop might very easily be more expensive in the long run than a bigger disk... Just my 2 cents
(17 Nov '11, 05:19)
Volker Barth
1
I agree with Volker... buy a bigger disk drive. High performance 500GB for U$150. If you don't need the speed, 2TB for $200. Buy two, one for backups. And/or buy external USB drives for backups. You may be taking unnecessary risks... what if your current disk drive fails? Have you tested your backup and recovery plan?
(17 Nov '11, 06:12)
Breck Carter
|
|
FWIW, just in the particular case that "too limited disk space" is your concern and more disk space isn't affordable (not everyone has a budget...), and you're willing to adapt your database design or maintenance: To reduce the space needed, you might consider the following hints (among others):
As said, just some hints to reduce the database size... I'm sure others have even better suggestions! All this should allow to reduce disk space while the database is running instead of having to fix it afterwards. |
|
I have tried with REORGANIZE TABLE, but it is not reducing the physical size of the db. Can any body please give me an example for dbunload -e -d option for unload tables alone. Well, as the docs tell
So given you would like to export only tables T1 and T2, you might use something like "%SQLANY12%\bin32\dbunload" -c "DSN=..." -d -t T1,T2 -r c:\myreload.sql -xx c:\myUnload The created myReload.sql will not contain any DDL but simply consist of LOAD STATISTICS and LOAD TABLE statements for these tables.
(22 Nov '11, 03:26)
Volker Barth
FWIW, as @Justin has explained in his comment, REORGANIZE TABLE is not expected to shrink the file size - it reduces fragmenation inside the database file. So your results are as expected.
(22 Nov '11, 03:34)
Volker Barth
Hi Volker, Thanks for your reply. I have one doubt. While doing dbunload, do we really required to stop db. Currently for DBUnload for full db, we are stopping the DB. Can you please explain for unloading a table alone do we need to stop db?
(22 Nov '11, 07:59)
srisudha
Replies hidden
Note, I have turned your posting into a comment below this answer - I would recommend to classify your coming postings as answers or comments, as this forum has nice features to add comments to answers or to other comments ... see the buttons "add new comment" and "reply" on the right bottom egde of each answer/comment. To your question: As you can see from my sample, I have run DBUNLOAD against a running database (specified by the -c connection parameters), so there's no need to stop the database for this usage. I guess that's different when reloading/unloading the whole database (say, with -ac/-an/-ar). That should require a non-running database.
(22 Nov '11, 08:14)
Volker Barth
|