Questions tagged [compression]

The name given to the process of encoding data such that it uses lesser number of bits as compared to the original representation.

Filter by
Sorted by
Tagged with
4
votes
1answer
9k views

Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly

The login running all these commands is a member of sysadmin server role on a SQL 2012 Developer instance. It is the owner of the DB where this is being deployed to. EXTERNAL ACCESS ASSEMBLY ...
3
votes
1answer
1k views

Attempting to compress an existing table results in no change to the table size

There is an existing table in SQL 2012 Enterprise DB, which is ~120MB in size with ~2000 records. Its largest field is nvarchar(max) containing spongy XML (ranging from 20 to 300 kB) which on its own ...
0
votes
1answer
399 views

Does dbms_lob.getlength return the compressed or uncompressed length?

If you have tablespace compression turned on, does dbms_lob.getlength return the compressed or uncompressed length of lobs stored on that tablespace? What about LOB compression?
1
vote
1answer
95 views

Database Backup compression

I am trying to see all of my backups are compressed or not. I ran below query to check but not getting good result: select top(10) database_name, case when backup_size = compressed_backup_size then ...
4
votes
1answer
466 views

Backup Failure on SQL TDE Database with Maxtransfersize Specified for Compression

Recently, my company has upgraded to 2016 and enabled TDE on all of our databases. Additionally, we have the production databases replicated on high availability servers. Our backups are taken from ...
1
vote
1answer
370 views

What Backup Compression Rate Can I Expect for a Database with Mostly JPG Data?

I am considering using a database to store one or more images (.JPG) associated with a record identifier and filename. CREATE TABLE Images ( RecordID BIGINT NOT NULL, ImageFilename VARCHAR(50)...
0
votes
1answer
13 views

Impact of table compression on HA Synchronous-commit mode scenarios

I am working with a HA SQL 2014 instance with Synchronous-commit mode. I did some table compression analysis with sp_estimate_data_compression_savings and I learned that table compression can be very ...
2
votes
3answers
2k views

Use of table compression in SQL Server 2014

I am going through articles about table compression in SQL Server. To me it seems quite easy to setup compressed tables and the benefits make the decision no brainer. I wanted to consult with this ...
1
vote
0answers
153 views

MariaDB replication - enabling compression

We've got a MariaDB database that's already being replicated and we wanted to turn on compression. I found the slave_compressed_protocol variable and see that it has to be turned on both on the ...
1
vote
4answers
363 views

External compression tools for database backup files

Can I use external compression tools like WinZip, 7zip or winrar to compress rman backup files? The DBMS is Oracle. A senior DBA claims that backups could be damaged by external compression tools. ...
2
votes
1answer
8k views

I wish to restore a gzipped tarball - what does mongodump --archive do? Howto do a mongorestore with --gzip?

I created a backup with mongodump -h $RANDOM_SECONDARY_SUP -u $BACKUP_USER -p $BACKUP_PASSWD --out /data/$BACKUP_USER/sup-repl/sup_$DUMPFILE tar cvzf /data/$BACKUP_USER/dbt/st_$DUMPFILE.tar.gz /data/...
1
vote
1answer
398 views

SQL Server Compression Estimate Is Significantly Lower When Estimating Current Compression Type

UPDATE - I originally said 'estimate significantly lower when set to 'NONE'. I'm amending that to say 'estimate significantly lower when set to [the compression in use]. When I execute the ...
14
votes
2answers
1k views

Compression on a heap

The follow is a paragraph from Microsoft Docs: New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and ...
1
vote
1answer
156 views

How to remove Windows NTFS Compression from Oracle Database datafiles?

We know Windows NTFS compression on Oracle Database datafiles is a bad idea. See here and here. It prevents Asynchronous Random Disk I/O which can increase the risk of corruption and generally slow ...
0
votes
1answer
472 views

MySQL compression on Google Cloud SQL

As the Google Cloud SQL is a managed instance of MySQL under the hood, limited to an absolute max on 2nd generation machines of 100GB of RAM, should I be compressing some of my table and index data ? ...
10
votes
2answers
8k views

Please explain the difference between row and page compression

I'm trying to determine which to apply and when is the appropriate time to apply compression. I'm posting this question to get insight from this community. I have read several articles, but wanted to ...
0
votes
1answer
219 views

MySQL MyIsam Compressing tables

Im packing big MyISAM tables with myisampack and came up with the following issue. (MariaDB 10.1.14 64bit windows) Indexes are rebuilt after packing the tablewhile running myisamchk -rq, so, it doesnt ...
0
votes
1answer
368 views

PostgreSQL values to unique dictionary

I have table with one billion rows and more than 50 columns. I need to reduce size and speed up queries, backup, exports, etc. Some columns contain f.e. only hundreds of distinct values which are long ...
2
votes
1answer
7k views

Restore a database with compression on a non-enterprise edition SQL Server

Is it possible to restore a compressed an object from backup without the Enterprise Edition? I'm getting the following error after a RESTORE DATABASE operation. Msg 3013, Level 16, State 1, Line 2 ...
0
votes
2answers
651 views

UnShrunk SQL Server database restore which is larger than available disk space

I cannot restore a Microsoft SQL Server database due to lack of hard drive space. The original database was 1.6TB and approximately 1.4TB was deleted from it and a backup was taken without shrinking ...
13
votes
2answers
968 views

Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

I was hoping to get a granular view of which database files contained which allocation units for the various HoBTs (both aligned and non-aligned) living in a database. The query I've always used (see ...
7
votes
1answer
1k views

How does SQL Server calculate the initial size of a (compressed) backup?

When a backup is created, SQL Server guesses(?) the a size of the intial backup file. Later on, maybe when it appends the log the size will be re-adjusted, sometimes multiple times until the final ...
7
votes
1answer
536 views

Effect of changing data types with row compression enabled

The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014. Big data types When I look ...
1
vote
2answers
1k views

Migrate database from SQL Server 2008 Enterprise to SQL Server 2014 Standard

I need move database with "data compression" from SQL Server 2008 Enterprise to SQL Server 2014 Standard. But Standard edition does not support "data compression". How can I disable "data ...
1
vote
1answer
199 views

Are toast columns compressed also in shared_buffers?

Reading here I cannot find a clear answer: http://www.postgresql.org/docs/9.1/static/storage-toast.html I need to know if setting storage to EXTERNAL for a particular column, I can gain a little ...
0
votes
0answers
111 views

Does MySQL InnoDB compression combined with Windows compression save more space?

We have a very large MySQL InnoDB database, compressed using the following syntax at the end of the table declaration: ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 We also have windows ...
3
votes
2answers
467 views

will table partitioning and compression help heavily used OLTP database?

We have a very big table which is over 300gb with over half billion rows in it. This table is used for audit purpose for all critical applications. A lot of inserts are happening per seconds. Due to ...
0
votes
1answer
753 views

error recovering pg_dump compressed file

I produced this backup: pg_dump -v -Fc -Z5 -f thebackup.dmp.gz thedb I can't open the dump with gzip, 7zip... I could recover the same files in the past without using -Fc parameter. Is it possible ...
2
votes
2answers
2k views

Export a table and zip with password

I would like, from TSQL, to use a view to extract data from a table, output it with BCP, then use non 3rd party tools to ZIP it with a password and AES encryption. Optionnally, I could use a 3rd party ...
1
vote
0answers
484 views

Estimating compression ratio for SecureFile LOBs?

We made a migration from Oracle 10 to 11.2. Our lob objects are currently stored in the original LOB storage architecture. But 11g introduced the SecureFile storage architecture and we want to use ...
5
votes
1answer
293 views

Postgres: Is migrating old records to separate table and creating new aggregated records a good strategy to improve search speed?

I have a table with 1M+ records. New records are created and updated each day each time an event occurs for each type of event (which there are many). I frequently need to find sums across many ...
1
vote
1answer
3k views

Sql Server thinks I have different Data Compression when swaping partitions

I am setting partition switching for my database. I created a script using SqlCmd variable so that the same script would make both my source and target tables for partition swapping. Here is an ...
9
votes
1answer
1k views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
1
vote
0answers
211 views

Data compression within Internal Tables in SQL server 2008R2

We are doing downgrade from enterprise to standard for couple of servers which are not using any enterprise features: For one of our TFS(Team foundation server) database TFS_resx, when we ran ...
9
votes
1answer
8k views

What are the compression options for storing large amounts of text in Postgres?

I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data ...
6
votes
1answer
1k views

IO request time and less write latency

I am facing issues related to constantly growing log file due to which I am getting error. When I checked SQL Log I found below messages (error log is filled of these messages almost 90%) SQL ...
1
vote
1answer
221 views

How can I create a database where entries are compressed files searchable by index? [closed]

I am trying to design a database solution for logging where the search indices are dates (days), and the values are zipped files. I am doing this because the sheer number of logs I am processing is ...
1
vote
2answers
5k views

Compressing partition - what about index?

To save space on disk I want to compress some partitions of my tables: alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations; I have also indexes on this partition. Should I ...
3
votes
1answer
591 views

Backup File Size Almost 50% of Database File Size with TDE Enabled

I receive a full backup of a very large database once per week from a vendor. It contains transactional data which we then load into our data warehouse. Fully restored, the database is around 707GB. ...
1
vote
1answer
3k views

Mongo Db server status with wired Tiger

I was testing my DB with Mongo 3.0 wiredTiger setup. After restoring my database with wiredTIger i ran db.serverStatus. I found following in console output. "bytes currently in the cache" : ...
6
votes
1answer
2k views

GZipStream/DeflateStream compressions alternatives

I have created a simple CLR functions for compressing/decompressing NVARCHAR columns: [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)] public static SqlBinary Compress( string ...
0
votes
1answer
60 views

Will canceling InnoDB compression that's already in progress corrupt data?

I have a 155GB table that is in the process of being compressed. ALTER TABLE $table_name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; This compression has been running for a long time and is impeding my ...
1
vote
1answer
941 views

Correct place to put very large text field for mysql/mariadb

I have an app where a queue is being processed, Each time an entry is processed the result is stuck in a separate table. The queue table gets inserts and updates, the result table inserts only. The ...
15
votes
2answers
11k views

Alternative way to compress NVARCHAR(MAX)?

I am trying to compress some tables that have NVARCHAR(MAX) fields. Unfortunately, the row and the page compression do not have the desire impact (only ~100/200 MB saved for 20 GB table). Also, I am ...
3
votes
1answer
402 views

sp_estimate_data_compression_savings for column store compressions

Is there a way to get compressions saving for column store and column store column store archival compression? In order to check these compressions saving I have to drop all clustered and non ...
5
votes
2answers
486 views

Page compression not working?

I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month. I now would ...
12
votes
1answer
6k views

Benefits of Barracuda and Compression

I've been reading about MySQL's file formats Antelope and Barracuda a while ago, and I wonder if I could benefit with having Barracuda and Compression. My server is currently using Antelope, as it is ...
14
votes
1answer
9k views

ROW_FORMAT - COMPRESSED vs COMPACT vs DYNAMIC

In MySQL InnoDB, what is the difference between COMPRESSED, COMPACT and DYNAMIC for ROW_FORMAT? What are the benefits between each other?
2
votes
1answer
178 views

Best Practice in storage allocation for compressed and partitioned tables

I have a fairly large database that basically consists of one table, this table is paritioned by month and all partitions are compressed with page compression. After the initial load of the data there ...
0
votes
1answer
611 views

pg_basebackup: could not set compression level -1

When trying to run pg_basebackup I received the following error message: pg_basebackup: could not set compression level -1 (null) using the -z option. I received a similar error message when trying -...