Hot answers tagged

42 votes

Disable MySQL binary logging with log_bin variable

In MySQL 8, it is the option disable_log_bin which has to be provided without any parameter in the my.cnf file in the [mysqld] section: [mysqld] disable_log_bin some-other-option = any-value From the ...
Peter VARGA's user avatar
14 votes
Accepted

MySQL InnoDB page_cleaner settings might not be optimal

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically ...
Leandro's user avatar
  • 156
14 votes

Transaction Log Files Larger Than Data Files

Jeff, Brent here. We put a lot of documentation in that URL - make sure you copy/paste the URL into your web browser to read the full documentation. If you're in simple recovery model, and you've got ...
Brent Ozar's user avatar
  • 42.9k
14 votes
Accepted

Why do I have to backup a transaction log twice for it to shrink?

SQL Server's transaction log files are made up of Virtual Log Files (VLFs). When you shrink a log file, it will only release unused VLFs at the end of the file. There is always going to be at least ...
AMtwo's user avatar
  • 16.1k
12 votes

Disable MySQL binary logging with log_bin variable

From the Manual for MariaDB (https://mariadb.com/kb/en/library/activating-the-binary-log/) If you don't provide a name (which can, optionally, include an absolute path), the default will be ...
I'm Root James's user avatar
9 votes

Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your ...
Aaron Bertrand's user avatar
8 votes
Accepted

MySQL audit and general log

Audit Plugin MySQL Enterprise Audit is a new feature for security and compliance. It's not included in the Open Source / Community edition. As of MySQL 5.6.10, MySQL Enterprise Edition includes ...
Sybil's user avatar
  • 2,488
8 votes
Accepted

Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available, unused, VLF entries. If the log runs out ...
Hannah Vernon's user avatar
  • 70k
7 votes

MySQL InnoDB page_cleaner settings might not be optimal

We experienced the same problem across various clients and found out that the problem was due to setting the value of innodb_lru_scan_depth from the default of 1024 to as low as 128. Although lowering ...
Brian Fenech's user avatar
6 votes

When was second log file created for database?

This will query the default trace for any changes to the database, which will include the addition of files (but it will also include other things that you can't differentiate, like changing a ...
Aaron Bertrand's user avatar
6 votes

SQL Server logs "blob data", viewable with journalctl

This happens when there are unprintable characters in the output. It seems sqlservr is outputting \r (or ^M) (line feed) characters. You can view the raw data using: journalctl -u mssql-server....
gregmac's user avatar
  • 161
6 votes
Accepted

How to log ALL queries made by users in SQL Server 2017 Standard?

Common Criteria Compliance is an enterprise feature In the standard edition, well any edition, you can create a trace and trace "Audit Schema Object Access Event" that will give you a statement ...
Spörri's user avatar
  • 4,704
6 votes
Accepted

how to list all features installed in a sql server instance?

You can run "Installed SQL Server features discovery report" It is documented there: Validate a SQL Server Installation Some examples: how-to-find-out-what-sql-features-are-installed
Piotr Palka's user avatar
  • 1,571
5 votes
Accepted

What are the three parts of a LSN?

According to Paul Randal in his Pluralsight course "SQL Server: Logging, Recovery, and the Transaction Log" the LSN is composed of three parts: 1) VLF sequence number (4 bytes) 2) Log block number (...
Forrest's user avatar
  • 4,139
5 votes
Accepted

Raise NOTICE, Raise INFO not saved to logs in POSTGRES

#log_min_messages = debug5 # values in order of decreasing detail: You have attempted to change this value from the default, but you have not removed the comment marker (the first '#'), so your ...
jjanes's user avatar
  • 39.4k
4 votes

Transaction Log Files Larger Than Data Files

So the items in Brent's answer should be investigated first: make sure you don't have some sort of larger issue that needs fixed a different way. But once you have done so, it is absolutely possible ...
BradC's user avatar
  • 9,954
4 votes
Accepted

MySQL general_log only shows a hex number instead of SQL?

Use this query to get the argument as text: select a.*, convert(a.argument using utf8) from general_log a;
ssh's user avatar
  • 156
4 votes
Accepted

Is there a way to limit log file usage for a transaction in SQL Server?

If I am sure that none of the transactions opened against a database should not allocate more than a certain amount of log, The problem of long-running transaction is NOT that it generates too many ...
sepupic's user avatar
  • 10.7k
4 votes

Replay (re-execute) MySQL SELECT queries from a log file

I found a solution for my problem. It is a combination of Percona pt-query-digest and Percona playback. First, I filter the slow log by database name and exclude all statements that do not start with ...
Stefan's user avatar
  • 221
4 votes
Accepted

how to find out how long the last DBCC checkdb took to complete?

If it is scheduled, then getting it from the SQL Agent job's runtime duration is an option. And if you're using Ola's solution (which I'd recommend) then his dbo.CommandLog table has StartTime and ...
LowlyDBA - John M's user avatar
3 votes
Accepted

Logging procedure, package errors to OS's Syslog

Of course it can be done, the question is, do you really want this? Scheduler jobs can raise events (put in a queue). You can capture (dequeue) those events using Advanced Queueing. For example: ...
Balazs Papp's user avatar
  • 40.6k
3 votes
Accepted

What do the values after `execute fetch from` mean in postgresql query logs?

S_26 is a statement name and C_775 a portal name in the extended-query protocol. See https://www.postgresql.org/docs/current/static/protocol-overview.html In the extended-query protocol, ...
Daniel Vérité's user avatar
3 votes

MySQL InnoDB page_cleaner settings might not be optimal

This StackOverflow thread can be useful... https://stackoverflow.com/questions/41134785/how-to-solve-mysql-warning-innodb-page-cleaner-1000ms-intended-loop-took-xxx This basically means, Your DB is ...
swayamraina's user avatar
3 votes

How do I hide sensitive information like plaintext passwords from the logs?

The currently accepted answer did not work for me on Postgres 9.4, for preventing the plaintext password from showing up in the logs. What worked for me instead was generating the password hash ...
robut's user avatar
  • 181
3 votes

Transaction Log Maintanance While Using AlwaysOn Availability Group

When you use AlwaysOn Availability Group, even if you take a proper backup the log file might grow larger and the log drive might get full over time. in order to maintain proper (minimize) log file ...
Hailegziabher Dechassa's user avatar
3 votes
Accepted

How to shrink over sized logs in Database Mirroring?

Just do the shrink on the primary: Can I shrink the transaction log file on a mirror database? No need to fail over and shrink the secondary.
Brent Ozar's user avatar
  • 42.9k
3 votes
Accepted

SQL Server : log folder too large

It is recommended that you recycle your error log frequently. You can set that up as sql agent job to do that. I do this every night at midnight but you need to decide what is suitable for your ...
SqlWorldWide's user avatar
  • 13.1k
3 votes
Accepted

Log (ldf) file grows continuously

Conceptually this is what should be happening within your database when the end of the physical file is reached: However, it sounds like you have a log header that doesn't want to wrap back around to ...
John Eisbrener's user avatar
3 votes
Accepted

Auditing Failed Logins in SQL Server

The password is never recorded. If it was it would be the first place a hacker would check for clues about correct passwords. By default, TDS (Tabular Data Stream, the network protocol used by SQL ...
Hannah Vernon's user avatar
  • 70k
3 votes
Accepted

Why am I able to create MV logs using PK — even though there is no PK?

A unique constraint on a NOT NULL column (which you have, as I checked this table in a database where Maximo was installed) is a good enough substitute for a primary key in this case. SQL> create ...
Balazs Papp's user avatar
  • 40.6k

Only top scored, non community-wiki answers of a minimum length are eligible