Hot answers tagged

21

[mysqld] # Set Slow Query Log long_query_time = 1 slow_query_log = 1 slow_query_log_file = /usr/log/slowquery.log log_queries_not_using_indexes = 1 #Set General Log general_log = on general_log_file=/usr/log/general.log Note that enabling general_log on a production server has overhead you should avoid it. You can check problematic queries from slow log.


13

Locate your postgresql.conf in the datadir Find the section that looks like this #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - #log_destination = 'stderr' # Valid values are ...


11

General log - all queries - see VARIABLE general_log Slow log - queries slower than long_query_time - slow_query_log_file Binlog - for replication and backup - log_bin_basename Relay log - also for replication general errors - mysqld.err start/stop - mysql.log (not very interesting) - log_error InnoDB redo log - iblog* See the variable basedir and datadir ...


10

Since this is the type of thing you probably only want to do temporarily, it may be useful to do this from the shell instead of via the config file: > set global general_log_file = "/var/log/mysql/queries.log"; > set global general_log = "ON"; [wait some time, hit some pages, whatever] > set global general_log = "OFF";


9

You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well In SQL Server 2012 and up, you will get a nice error Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_dboption'. Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, SQL ...


8

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


8

You are confusing allocated space with used space. After running the backup use this query to see the difference between allocated and used space. select file_id , type_desc , name , substring([physical_name],1,3) AS [Drive] , physical_name , state_desc , size / 128 as 'AllocatedSizeMB' , FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB' --Addapted ...


7

This means that your binary log format binlog_format is STATEMENT, which is the default binary log format. STATEMENT-based format logs the statement issued, while ROW-based format logs how individual tables were changed. The problem with STATEMENT logging is certain statements cannot guarantee that the same data written on the Master will end up on the ...


7

Going by your other questions, it sounds like you have a short archive_timeout so your WAL archives are mostly empty, but are still the full pre-allocated 16MB file. On most platforms you just gzip them, e.g. archive_command = 'gzip -c < "%p" > /archive/path/%f"' On Windows this won't work natively due to the lack of the gzip command. You'll need to ...


7

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered. Yeah, this is bubbled up from the OS error the logwriter is running into. In this case it's OS error 170 which is the resource is in use. That's pretty damning that the issue is outside of SQL Server (barring any bad 3rd party DLLs loaded into the address ...


6

I would suggest not logging the "latest activity" but rather keeping a full audit trail. In order to minimize space requirements, you might want three tables: CREATE TABLE dbo.Users ( UserID TINYINT IDENTITY(1,1) PRIMARY KEY, -- assuming <= 255 users Username NVARCHAR(128) NOT NULL UNIQUE, /* , other columns */ ); CREATE TABLE dbo.Tables ( ...


6

Using the ALTER ROLE ... SET parameter; command, it was possible to tailor user-specific log parameters. Note that the parameter only takes effect after logout. Setting log_min_duration_statement=-1 (1st login): psql console P:\>psql -U postgres -h 132.156.208.45 -d my_db my_db=# SHOW log_min_duration_statement; log_min_duration_statement ------------...


6

Taking this backup will just backup the data and clear the log. The actual size of the log will need to be shrunk via a DBCC command if you really need to shrink the log. Depending on how often you are backing up your log file it will likely just grow again. Try running this to see how much actual space on your log is taken up. SELECT [TYPE] = A....


5

From mysqlperformanceblog Log Sequence Number (LSN) : Log Sequence Numbers correspond to given position in the log files and typically incremented for each log record. Innodb uses number of bytes ever written to the log files however it could be something different. LSNs are often extensively used in recovery check pointing and buffer management ...


5

What was suggested to you thus far is what can be done to bring the database to a consistent state. Here is what you need to know about InnoDB. First of all here is the InnoDB Architecture in Pictorial Form Look at the Picture. What components are essentially for InnoDB's self healing (sounds better that crash recovery)? The Double Write Buffer has the ...


5

The problem is indeed as @Phil hinted at. To fix it, set the permissions for the _mysql user: > cd ~ > touch mysql.log > sudo chown _mysql:wheel mysql.log Restart MySQL after that and the log file will be used.


5

Put these two lines in my.cnf. [mysqld] general_log = on general_log_file=/users/ugrad/linehanp/mydb/logfile.txt This will log all queries to the server, from any source, not just PHP/PHPMyAdmin. Be careful though - enabling the general log can place a heavy load on your server. To be used sparingly for short periods/debugging only. The ...


5

Now that you've changed the recovery model on the database from full to simple and back to full again, the database is running in 'pseudo-simple' mode. Taking a database backup is required now before a log backup will do any meaningful work. See here for some further details. To summarize: In order for a log backup to be useful for a restore, there needs to ...


5

I don't know how to do it for a client host, but for a single database user, you can use this (as a superuser): ALTER ROLE someone SET log_statement TO 'all'; Which will then turn on log_statement every time that that someone connects in the future. For a single connection which is cooperating with you, you could create this function (as a superuser): ...


5

You should be able to find the full text log location with the following code: xp_readerrorlog 0, 1, N'Logging SQL Server messages in file' I don't think it's possible to split them away from the normal SQL Error log as this connect suggests.


5

In Debian/Ubuntu, per policy it's the logrotate package that is in charge of handling log rotation and purge for all services, PostgreSQL included. From https://www.debian.org/doc/debian-policy/ch-files.html : Log files must be rotated occasionally so that they don't grow indefinitely. The best way to do this is to install a log rotation ...


4

you need to give it a value if you wish to preserve the binlogs: call mysql.rds_set_configuration('binlog retention hours', 24); will cause the binlogs to be stored for 24hrs, for example.


4

UPDATE: I figured it out! Because the ola hallengren scripts check for log shipping roles and skip log backups if it finds the current db in either msdb.dbo.log_shipping_primary_databases or msdb.dbo.log_shipping_secondary_databases AND because the databases I have been fighting with recently DID have old log shipping entries that were not proiperly ...


4

If you have shell access to the server on which mysql is running, you can use tcpdump + pt-query-digest. First, do a tcpdump with the appropriate switches necessary for pt-query-digest: tcpdump -i [your interface] port [3306,etc] -s 65535 -x -nn -q -tttt > tcpdump.out Then, after you are done with your packet capture, do this: pt-query-digest tcpdump.out --...


4

After some major league Googling all over the planet, I think I found the answer. http://dbaspot.com/mysql/70551-how-disable-any-logging-mysql.html https://lists.drupal.org/pipermail/support/2014-June/025447.html From these posts I saw something interesting 13679547 Connect @localhost as anonymous on test 140612 12:33:07 3 Connect gk_admin at ...


4

Should I turn to simple recovery model and truncate? That would be the worst thing to do it would break away mirroring and if you have big database you would again have to follow the whole process of creating mirroring again that could well be a tedious task. Should I stop mirroring, do what I should do to principal database and then enable mirroring? ...


4

The MySQL logs are determined by the global variables such as: log_error for the error message log; general_log_file for the general query log file (if enabled by general_log); slow_query_log_file for the slow query log file (if enabled by slow_query_log); To see the settings and the logs' locations, run this shell command: mysql -se "SHOW VARIABLES" | ...


4

To rotate the logs you just need to execute EXEC master.sys.sp_cycle_errorlog; You can schedule the execution daily or as often as it makes sense in your scenario with a SQL Server agent job.


4

The PostgreSQL Error reporting and logging documentation states: log_rotation_size (integer) When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log ...


4

The write and sync phase mean rather different things (unfortunately). In the middle phase of a checkpoint, it writes out all the dirty buffers from shared buffers. It does this in a throttled way, mainly regulated by checkpoint_completion_target. The time reported here is the time it spent writing those buffers, as well as the time it spend sleeping in ...


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