Tag Info

New answers tagged

2

I ended up solving my own question with the below code: --Using the AdventureWorks2012 database in Full mode with log shrunk to 64MB --setup test table USE AdventureWorks2012 go IF OBJECT_ID('TestTbl') IS NOT NULL DROP TABLE TestTbl GO CREATE TABLE TestTbl ( RowID INT IDENTITY(100,100) , ...


0

Free Space BACKUP DATABASE (full backup, differential backup, file/filegroup backup, copy only) does not free up space in any files. If anything, it uses up a bit of space in the log file to record its actions. BACKUP LOG truncates the log, which generally does free up space. A Simple recovery model database truncates its log when a checkpoint happens. ...


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 :



Top 50 recent answers are included