Hot answers tagged

186

A Shorter Answer: You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough). There could be other reasons but these are the most common. This ...


71

Since I'm not really satisfied with any of the answers over on Stack Overflow, including the most heavily up-voted suggestion, and because there are a few things I'd like to address that Mike's answer does not, I thought I would provide my input here too. I placed a copy of this answer there as well. Making a log file smaller should really be reserved for ...


50

From http://www.sqlservercentral.com/scripts/tempdb/72007/ ;WITH task_space_usage AS ( -- SUM alloc/delloc pages SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages, SUM(internal_objects_dealloc_page_count) AS dealloc_pages FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE ...


42

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done ...


23

When carrying out the command ALTER COLUMN ... NOT NULL This seems to be implemented as an Add Column, Update, Drop Column operation. A new row is inserted into sys.sysrscols to represent a new column. The status bit for 128 is set indicating the column does not allow NULLs An update is carried out on every row of the table setting the new columnn value ...


17

The reason you are seeing this: Starting up database 'dbname' CHECKDB for database 'dbname' finished without errors Is because you have your database option set for AutoClose. To turn off AutoClose, do this: alter database YourDatabase set auto_close off go What AutoClose does is shut down the database after the last user process disconnects. And ...


17

You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization. Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment. If recovery model is simple, ...


15

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info. Do not shrink to 160MB. Determine why did the ...


14

You have two options: 1) Run a LOG backup of the database: BACKUP LOG foo TO DISK='X:\BackupLocation'; 2) Set the recovery model to SIMPLE and checkpoint it. ALTER DATABASE foo SET RECOVERY SIMPLE; CHECKPOINT; This is all part of SQL Server's different recovery models. Based on you working on this in DEV, I'd probably go the second option since it ...


12

You can also see the content of your log file. To do that, you can use the undocumented fn_dblog, or a transaction log reader, such as ApexSQL Log. It doesn't show index reorganization, but it shows all DML and various DDL events: ALTER, CREATE, DROP, trigger enable/disable, grant/revoke permissions, object rename. Disclaimer: I work for ApexSQL as a ...


12

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with: Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log ...


11

Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information. Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log ...


11

The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction. ...


11

The transaction log isn't recording the SQL statements being executed, as you might be expecting. Instead, it's recording the changes to the raw data in each database, independently. It's possible for a stored proc from one database to be working entirely in the transaction log of another database. ... database1..my_stored_procedure AS BEGIN INSERT INTO ...


11

What is a virtual log file? SQL Server divides the transaction log file for each database into smaller chunks, called 'virtual log files' (or VLFs for short). Their primary function is as truncation markers at a log backup, i.e. the SQL Server will only clear (and mark available for re-use) VLFs that are completely empty. MSDN has a piece on Transaction Log ...


10

After you dig yourself out of this hole, please, read this and learn from it. Logs are an essential part of how SQL Server works. You can't just get rid of them. But you can manage them appropriately. You're probably working off the default settings, which create all databases in Full Recovery mode, meaning, the logs are going to grow. Change this after you ...


10

You can force a ROLLBACK though by switching the DB to RESTRICTED using WITH ROLLBACK IMMEDIATE. See ALTER DATABASE. You can't force COMMIT (as per your SO question) Then... If you delete the LDF (SQL Server shut down), your database comes back "suspect". If you detach+delete, attach the MDF by itself, the LDF is re-created. Note: for the millionth time,...


10

Your file management can be a completely online operation. You have two paths, depending on your need to retain your log information for recovery purposes: Point in time recovery not needed Convert the database to SIMPLE recovery. Execute a checkpoint to write transactions to disk. Flatten the log. Resize the log to the appropriate size. I also ...


10

A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn. You also can restore log backups after restoring from a copy only. The following script demos that: CREATE DATABASE BakTst13; GO ALTER DATABASE BakTst13 SET RECOVERY FULL; GO USE BakTst13; GO CREATE TABLE dbo.tst(id INT IDENTITY(1,1)); GO INSERT INTO ...


10

Moved this over to Mike Walsh's similar question.


10

It is impossible for us to guess what is causing it, but SQL Server doesn't just grow a log file to 300 MB for the heck of it, it grows to 300 MB because at some point since your last shrink operation, it needed that much log space (whether due to some big single transaction or a lot of smaller concurrent ones). You'd have to trace log file growth events (I ...


10

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done? No, restoring a transaction log is sequential. Transaction log relies on LSN (Log Sequence Number) Also, you cannot restore your database with just transaction log. ...


10

On a technical level: is this a good practice or not? I would say not. If the database is experiencing no activity at all during the business off hours, then taking the backups is very low overhead. On the other hand, if the database is experiencing any activity during business off hours then not taking the backups is a serious problem. My stance is that ...


9

Just to add to the existing answers. The SQL Server 2008 Internals Book (pp 175-177) implies that detaching the database, deleting the log file and reattaching the mdf file ought to be quite safe as it says. Detaching a database ensures that no incomplete transactions are in the database and that there are no dirty pages for this database in memory. ...


9

You can view the individual rows that were deleted by looking for LOP_DELETE_ROWS operations still in the log: select * from fn_dblog(NULL, NULL) where Operation = 'LOP_DELETE_ROWS' If the log was recycled (in simple recovery model) or truncated by backup (in full or bulk recovery model) then you will only be able to see the log operations still ...


9

Alright, spent yesterday day and night investigating, testing and trying to reproduce the problem. Found the root cause. MODEL database simple recovery model. If the model database has been set to SIMPLE recovery model, and user databases are created with SIMPLE recovery model, SQL Server somehow is treating it as if it is on FULL recovery model. Hence ...


9

If a disaster occurred and I needed to recover the database from backup, would I be missing data? As long as all of the backups are in tact, no. The transaction log chain is not broken, and point-in-time recovery is possible. It's just that the backups that constitute a complete transaction log chain are not all in the same location. Having said that, I ...


8

you wont find the exact scripts that were executed on sql.(in the transaction log) A transaction log is a file that contains information regarding every change that has been made to the database. This includes data modifications (transactions), database modifications, and backup/restore events. The primary purpose the transaction log is to provide a ...


8

You cannot disable/remove the LDF file. It has to exist, as it stores database transactions. What you can do is: Set the database recovery model to Simple This should keep your LDF file from growing, and is set via the Options page of the Database Properties dialog. For additional reducing, which I don't think is necessary, you can: 2 Set Auto Shrink ...


8

Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Is possible that you log's last LSN point into a VLF that is at the tail of the LDF. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink.



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