Hot answers tagged

157

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 ...


58

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 ...


44

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 ...


35

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 ...


20

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 ...


16

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, ...


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

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

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 ...


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

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

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 ...


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 ...


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 ...


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

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 ...


10

Moved this over to Mike Walsh's similar question.


10

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 ...


9

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 ...


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

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 ...


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 ...


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. ...


8

A full backup in 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 dbo.tst ...


8

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 ...


8

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance) That is not true. log shrink is quite benign, you are thinking data shrinks. See How to shrink the SQL Server log for an explanation why it grows, how to shrink it and why is benign. My first recommendation is to use ...


8

You do not have to shrink the log (ie. make the file smaller) but instead you have to truncate the log (ie. allow the file to be reused internally, so it doesn't grow to start with). How to achieve this depends on your database recovery model. Under SIMPLE recovery model the truncation occurs automatically. Under other models (FULL or BULK-LOGGED) the ...


8

I'm switching a lot of databases to SIMPLE recovery mode from FULL recovery mode (T-Logs and point-in-time recovery is not necessary). Will the existing transaction logs be truncated (when checkpoints are created)? In simple recovery model, the database engine will issue automatic checkpoints and its frequency is determined by the recovery interval ...


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