Huge data base size

jawwad_hussain · September 9, 2019, 7:38am

Hi team

Can you give me an idea how or is it possible to replace internal DB form SQL Lite to MS SQL server ?

jawwad_hussain · September 9, 2019, 8:42pm

Any Guess ?

Greg · September 9, 2019, 10:23pm

While I don’t know if this is possible, I’m curious as to why you would want to do this.

jawwad_hussain · September 11, 2019, 4:44pm

We have around 200 of users some how data base got above 100 GB , When repairing it if fails

given following error

UrBackup is upgrading its internal database. This may take a while. The server is inaccessible and will not do any backups during this upgrade.
Current version: 0
Target version: 0

jawwad_hussain · September 23, 2019, 4:50am

any update ???

uroni · September 23, 2019, 11:00am

Take a look/post your sever log file.

There are no plans to support different SQL servers. It’s Open Source, though, so you are free to change this yourself/submit pull requests.

jawwad_hussain · September 23, 2019, 11:31am

Urbackup-Size

Following is the size issue that i am having , so big DB file

uroni · September 23, 2019, 11:33am

Huh, backup_server.db shouldn’t get that large… Could you run sqlite_analyzer.exe on it and post the output: https://www.sqlite.org/2019/sqlite-tools-win32-x86-3290000.zip https://www.sqlite.org/sqlanalyze.html

… also post the UrBackup Server version you are using

jawwad_hussain · September 23, 2019, 1:55pm

this has been going on for hours approx 5 to 6 it is still going on

uroni · September 23, 2019, 3:25pm

You probably want to write it to a file?

Do you have clients that change their name often?

jawwad_hussain · September 24, 2019, 3:12am

,4096);
T INTO stats VALUES(‘users_on_client’,’/002/’,1142,‘leaf’,88,1860,1700,27,4
6,4096);
T INTO stats VALUES(‘users_on_client’,’/003/’,418,‘leaf’,92,2026,1510,31,17
,4096);
T INTO stats VALUES(‘users_on_client’,’/004/’,3910,‘leaf’,89,1957,1597,26,1
64,4096);
T INTO stats VALUES(‘users_on_client’,’/005/’,1149852,‘leaf’,72,1565,2091,2
9789696,4096);
T INTO stats VALUES(‘users_on_client’,’/006/’,2172887,‘leaf’,63,1382,2328,2
0141056,4096);
T INTO stats VALUES(‘users_on_client’,’/007/’,1076,‘leaf’,140,3084,164,29,4
0,4096);
T INTO stats VALUES(‘users_on_client’,’/008/’,675,‘leaf’,144,3124,100,27,27
,4096);
T INTO stats VALUES(‘users_on_client’,’/009/’,29276633,‘leaf’,143,3106,124,
9917084672,4096);
T INTO stats VALUES(‘users_on_client’,’/00a/’,3643670,‘leaf’,54,1196,2568,3
24468224,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/’,29,‘internal’,7,184,3851,2
688,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/000/’,2420,‘leaf’,125,2956,7
,9908224,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/001/’,2426,‘leaf’,134,3249,4
,9932800,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/002/’,2427,‘leaf’,107,2583,1
9,9936896,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/003/’,2428,‘leaf’,131,3100,5
,9940992,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/004/’,2429,‘leaf’,148,3500,1
,9945088,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/005/’,2430,‘leaf’,138,3283,3
,9949184,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/006/’,2431,‘leaf’,145,3531,1
,9953280,4096);
T INTO stats VALUES(‘users_on_client_unique’,’/007/’,2432,‘leaf’,148,3352,2
,9957376,4096);
T;

ogram Files\UrBackupServer\urbackup>

these are the last lines i got after completion

jawwad_hussain · September 25, 2019, 11:07am

any update to please me reduce the size of data base

uroni · September 25, 2019, 11:12am

Please don’t bump threads if you don’t add new information

jawwad_hussain · September 27, 2019, 5:30am

2019-09-27 10:17:48: Table users_on_client has 1087 rows
2019-09-27 10:17:48: Table tokens_on_client has 2423 rows
2019-09-27 10:17:48: Table restores has 4 rows
2019-09-27 10:17:48: Table user_tokens has 29679 rows
2019-09-27 10:17:48: Table alert_scripts has 2 rows
2019-09-27 10:17:48: Table alert_script_params has 22 rows
2019-09-27 10:17:48: Table mail_queue has 1 rows
2019-09-27 10:17:58: Could not open WAL file urbackup\backup_server.db-wal (wal
checkpoint thread)
2019-09-27 10:17:58: Could not open WAL file urbackup\backup_server_link_journal
.db-wal (wal checkpoint thread)
2019-09-27 10:17:58: Could not open WAL file urbackup\backup_server_links.db-wal
(wal checkpoint thread)
2019-09-27 10:17:58: Could not open WAL file urbackup\backup_server_settings.db-
wal (wal checkpoint thread)
2019-09-27 10:17:58: Starting passive WAL checkpoint of urbackup\backup_server_f
iles.db…
2019-09-27 10:20:39: Table moved_clients has 2989538046 rows
2019-09-27 10:20:39: Table mounted_backup_images has 0 rows
2019-09-27 10:20:39: Cleaning up information…
2019-09-27 10:20:39: Cleaning up database (VACUUM)…
2019-09-27 10:20:39: Transitioning urbackup server database to different journal
ing mode…
2019-09-27 10:20:39: Rebuilding Database…

jawwad_hussain · September 27, 2019, 7:53am

2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: SQLITE_BUSY in CQuery::Execute Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:17:38: ERROR: SQLITE: Long running query Stmt: [PRAGMA journal_mode = DELETE]
2019-09-27 12:17:38: ERROR: Active query(0): PRAGMA wal_checkpoint(PASSIVE)
2019-09-27 12:17:38: ERROR: Active query(1): PRAGMA journal_mode = DELETE
2019-09-27 12:18:58: Passive WAL checkpoint of urbackup\backup_server_files.db completed busy=0 checkpointed=335952 log=335952
2019-09-27 12:18:58: Syncing database urbackup\backup_server_files.db…
2019-09-27 12:18:58: Syncing wal file urbackup\backup_server_files.db-wal…
2019-09-27 12:18:58: Files WAL file urbackup\backup_server_files.db-wal greater than 1000 MB. Doing full WAL checkpoint…

Please update now

jawwad_hussain · October 1, 2019, 9:32am

i have found out Data base move client tablet has many records in it ? please guide how to remove them

uroni · October 1, 2019, 11:45am

DELETE FROM moved_clients

Content of that table isn’t that important. But you’ll have to tell why there is so much content. Otherwise it won’t get fixed, and it’ll just inflate again.

jawwad_hussain · October 1, 2019, 2:58pm

I think is because of my vm of your backup when got corrupted and when I restore with my backup image this thing is happening because of that

jawwad_hussain · October 2, 2019, 4:41am

Thanks issue has been resolved,

jawwad_hussain · October 9, 2019, 4:28am

I wonder DB again got, huge cross to 35 GB again i have to perform