Tag Info

New answers tagged

0

@JeremieGrund - If the database physical file architecture is same( data & log file drive names & locations), then if you shrink the log file on the principal, the shrink command will be sent to mirror and do the same on mirror. So in this way your mirror log file can be maintained.. If the files architecture is not identical then you should follow ...


1

Got a SQL database and the log file is massive and keeps growing every day. Log files growth depends on Recovery Model of a database and transaction log backups. if database is not critical and data loss is not an issue in any case then it should be in SIMPLE recovery mode. Which allows SQL Server to reuse log space when a transaction is committed and ...


3

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log. There is no way to prevent SQL Server from writing to the transaction log. This means that a large transaction can still expand the log, if the current size of the log is too small. The ...


2

Seems like an appropriate way to do it. Create a logging table: CREATE TABLE dbo.LogSpace ( dt DATETIME NOT NULL DEFAULT SYSDATETIME(), dbname SYSNAME, log_size_mb DECIMAL(22,7), space_used_percent DECIMAL(8,5), [status] BIT ); Do this before and after your load: INSERT dbo.LogSpace(dbname, log_size_mb, space_used_percent, [status]) EXEC ...


0

This doesn't answer your question, however I thought you might find it helpful to see if there have been any recent log growths: /* Description: display growth events for all databases on the instance by: Max Vernon date: 2014-10-01 */ DECLARE @Version NVARCHAR(255); DECLARE @VersionINT INT; SET @Version = ...


0

WAL vs statement log ordering Do the statements in the log file appear in the same order as they do in the WAL file? That doesn't really make sense. Statements do not appear in the WAL at all. Their effects do, in terms of transaction begins/commits/rollbacks, changes to table heap and index blocks, etc, but the actual statement is never recorded in ...


1

No, rollbacks cannot be done in the background. Both that and the double write checks have to be done before accepting connections again (meaning that if you kill it, you will have to start again on restart). REDO process, however, can and is done in the background. What you can do is kill mysql (again) and restart it skipping that step: /etc/init.d/mysqld ...


1

Since you mentioned about lot of evil things you are going to do in your database I should put it clear We currently have a transaction log thats been growing incrementally and has too many VLF files. I am going to follow a process of shrinking and then re-growing the log file to a suitable size to alleviate this problem. There can be many reason for ...


1

No, do not switch to SIMPLE recovery model since that will result in a break in your backup chain. If you are doing in FULL recovery model, do the following: CHECKPOINT BACKUP the LOG DBCC SHRINKFILE (logfile, targetsize) Repeat until you shrink the log file as small as possible. (My experience is that two or three times is usually needed.) Then set ...


0

You really need a fast HDD, but you need a properly sized innodb_log_file_size. Why not SSD for MySQL ? I learned something from this layout from a FaceBook Engineer's blog I wrote old posts about this Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations? Feb 06, 2014 : MySQL on SSD - what ...


1

This is related to a bug with new installs of mysql on debian wheezy whereby the error.log file is owned by root.root, as per this: http://tipstricks.itmatrix.eu/?p=1352 I fixed it with chown mysql.adm /var/log/mysql/error.log


1

Perhaps the log is on is a network share that is only visible to the SQL Server service account? If this were the case you could log onto the server using that same service account used by SQL Server, and "see" the drive in Windows Explorer. SQL Server will refuse to load a database if it cannot locate a valid log file. It will NOT simply create the log ...


1

If you go to server properties, in the database settings, you will find the default database data, log and backup locations :


2

You can review events from the default trace to identify the growth events you care about: DECLARE @path NVARCHAR(260); SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc' FROM sys.traces WHERE is_default = 1; SELECT DatabaseName, [FileName], SPID, Duration, StartTime, ...


1

The answer will require more investigative work on your part. The transaction log is exactly what is sounds like - a record of of all transactions and modifications made to the database. Unless your database is strictly being read and not written to, then your log will be continually increasing. How fast it will increase depends on what is being done, and ...



Top 50 recent answers are included