329

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). If it is a recovery model issue, the simple answer could be ...


116

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


28

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


15

Setting a database to full recovery is not enough by itself to ensure you have point in time recoverability. You also need to take regular log backups. You should also be taking time regularly to test that you personally know how to execute the required commands to actually perform a restore to an arbitrary point-in-time. In lieu of running loads of home ...


14

No, you need to have taken transaction logs that cover the time frame you want to use with STOPAT. You can't do this from a full database backup only - that is just a one-time copy, and that is why we have different types of backups (full, log, diff). If you have taken transaction log backups in between your full backups, please update the question with ...


13

Use Script to Shrink Log files of all databases other than the system DBs. USE MASTER GO SET QUOTED_IDENTIFIER ON GO SET ARITHABORT ON GO DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200) DECLARE DatabaseList CURSOR FOR SELECT name,recovery_model_desc FROM sys.databases WHERE state_desc = '...


11

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


9

The recovery model for the secondary will be the same as the primary database, because you're backing up the primary, restoring it on the secondary node using NORECOVERY/STANDBY, and then restoring subsequent log files to it. In a log shipping configuration, you cannot make changes to the secondary database. If you could, then the databases would be out of ...


8

It's because these methods rely on the transaction log, which itself is only reliable in the full recovery model. Please read the following page in its entirety, as there are several hints there about the differences between simple and full recovery, and why full is so important for DR: Why Does the Transaction Log Keep Growing or Run Out of Space? If you ...


8

There is no point in HA if anyhow DR technologies are allowed in simple recovery. Transaction log is a must for point-in-time recovery. The transaction log is what a DR technology uses to replay transactions that are done on Logshipped/mirrored databases, and since a log backup is not possible in simple recovery, you cannot configure HA/DR in simple recovery ...


8

I have created 5 maintenance plans on my SQL server (say 50 !) which generate database backup 5 times daily. Why not just create one maintenance plan and run it five times. My exact question: Is generating multiple daily backup with simple recovery model, similar to full recovery backup with longer period? Full backups or differential backup, no matter ...


6

However, why can I not shrink the file in full recovery mode? It's working as designed. You just need to understand more about what is going on, so you can decide which is the appropriate action to take. That, or you're just trying to shrink it at the wrong time (see my note #7 below). First of all, "log file" is a bit of a misnomer: SQL uses the ...


6

Merely setting the recovery back to FULL is not enough - you'd need to take at least a differential after setting back to FULL to bridge the gap between the time you changed to SIMPLE and the time you changed back to FULL - then your T-logs should be ok at 6am Keep in mind that you will not be able to restore to a point in time after the change to SIMPLE ...


6

This is the most frequently faced issue for almost all the DBAs where the logs grows and fills out the disk. •What are some reasons the transaction log grows so large? Long Active Transaction High logging transactions like Index rebuild, re-organise, Bulk Insert, Deletes etc. Any HA like Replication, Mirroring configured which holds the log and does not ...


6

Suppose I run the following query on a database with a recovery model of full: INSERT INTO dbo.MY_FAVORITE_MSFT_EMPLOYEES WITH (TABLOCK) SELECT 'SEAN GALLARDY' UNION ALL SELECT 'JOE SACK'; Under the full recovery model, SQL Server will write some representation of the data that was inserted to the transaction log. This allows redo threads on the secondary ...


5

I've always had such an aversion to cursors, that I wrote this as I can better understand it. It's totally based off of AA.SC's answer (thank you by the way), just put in a way that I think. If this jives with what others think, then great. Note, I didn't put it back into Full recovery mode afterward though. SELECT '--', d.name dbName, d.recovery_model, ...


5

The information about Eager writer process is available in BOL documents Eager writing The eager write process writes dirty data pages associated with minimally logged operations such as bulk insert and select into. This process allows creating and writing new pages to take place in parallel. That is, the calling operation does not have to wait ...


5

First of all I'd like to again point out that, in most cases, SIMPLE recovery is not recommended for a production database. However, if that is something you wish to change the best way I've found to shrink the LOG file, after you put the database into SIMPLE recovery model, is as follows. This will not only accomplish releasing free space from the log, ...


5

First a quick note on moving to SIMPLE recovery. This is a business decision. It is not in any way a technical decision. Your business needs to weigh the cost of staying in FULL (which may mean a new server with additional space, archiving current data to free up some space, etc) vs the cost of losing up to a day or more of data. Once they have decided ...


5

Your first stop for problems like this should always be this query: SELECT log_reuse_wait, log_reuse_wait_desc FROM sys.databases WHERE [name] = 'YourDatabaseName'; From the sys.databases documentation: Reuse of transaction log space is currently waiting on one of the following as of the last checkpoint. There's a list of possible values on that page, ...


4

the simple recovery model only affects the log files of your database (.ldf). If your datafiles are growing, you can check the tables that are occupying more space, you can use the management studio: right click on the database> reports > standard reports > disk usage by top tables this way you can determine which table is taking all the space and plan a ...


4

I can watch the database size grow from 2GB to 12GB (max space available) where it runs out of space and returns the following message: The issue is either you're simply running out of disk space and the physical file cannot grow to complete the transaction, or you have a limit on the SQL file growth settings and the physical file cannot grow more to ...


4

This is already mentioned in the documentation at Microsoft site as below: By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and ...


3

Mirroring uses the transaction log to redo everything at the mirror site. In simple recovery model, the transaction log is effectively transitory and only enough information is kept in the log for undo/redo to keep the database consistent during crash recovery. So Database mirroring is not possible in Simple Recovery model as it is not supported.


3

Your question says "I have done a log backup" indicating one backup. If you have not already, do a couple of backups then try shrinking the log. It is not unusual for some transactions to not clear with a single backup. I have done 3 or 4 sometimes before they cleared. Afterwards run DBCC LOGINFO to see what the VLF files look like. You can read Why are ...


3

First of all few housekeeping rule. You (or your DBA) should manage transaction log space depending on your recovery model. Do not let transaction log to get full and impact your database/application availability. Following two links can help you better manage transaction log file. SQL Server Transaction Log Architecture and Management Guide How do you ...


3

You are right, you cannot recover a database that in has a SIMPLE recovery model beyond the last full backup. Furthermore, you pointed out the reason you should have this DB in FULL recovery model: "it still has tons of users doing transactions so the logfile of the only database that is on this server fills the data drive everyday." Do you really want to ...


3

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned... So you know that you are playing with a double edge sword and are aware of the risks - any edge you touch, you are going to cut yourself. I've now ...


3

As I understand, the Bulk-Logged Recovery Model doesn't fully log the bulk transactions. Then why is it named like this? Without an answer from the folks that actually named this recovery model, we can only guess about the name. But it seems to me like "bulk-logged" means something similar to "buying in bulk." You can buy a single steak every day at the ...


3

Point in time recovery is the concept that a particular set of data can be restored to an exact point in time, rather than just to the time of the last backup file. In this case with SQL Server, log backups are the usual mechanism for accomplishing this to ensure database consistency. FULL or BULK recovery modes must be utilised to allow for point in time ...


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