The Stack Overflow podcast is back! Listen to an interview with our new CEO.

New answers tagged

3

multiple log files are bad It's not that multiple log files are bad... It's that multiple log files are totally unnecessary and provide no benefit ... unless you require a log file that is larger than 2TB. With data files, SQL Server can benefit from multiple files because it will perform parallel I/O to/from both files at the same time. However, with log ...


0

Do you add a second log file, or is there something else? Alternatively, you just set reasonable autogrowth size and leave your log file grow. There is no any control on it and log file can exceed 2Tb, you cannot set it explicitly but there is nothing that will prevent your log file growth, only disk free space.


0

Microsoft recommends only one LDF file for each database. This scenario is really very particular. However, you can perform the following operations: 1. Run the BACKUP LOG [database_name] WITH TRUNCATE_ONLY command 2. Run USE [database_name] 3. Run sp_helpdb 'database_name' 4. Write down the FileID of the log files. 5. For each file run the DBCC SHRINKFILE ...


3

If you open BOL article sys.database_files (Transact-SQL) you'll find that 268435456 = Log file will grow to a maximum size of 2 TB 2 Tb is the max size of SQL Server database log file, if you need it more than 2Tb you add the second log file. Here you can find Maximum Capacity Specifications for SQL Server that states that 2Tb is the maximum log file ...


-2

Not only for tempdb but also for any other db, shrinking to a size less than needed would cause slowness. You are not only making db engine more grow work, also increasing vlf amount. Look for VLF on google please.


6

One more point to add to the two good existing answers. Once you decide what the best size for tempdb is, you should manually set it to that size. If this is not done when SQL restarts monthly as the system is patched (assuming you do monthly patches) it will reset to the smaller size and will need to grow, giving the performance issues mentioned in the ...


12

I'd ask the person who told you that, to at least hear why they think it will decrease performance. One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your ...


5

When a file grows as a result of a transaction, that transaction is paused while the file is grown. Any other transactions using the file may also be affected. This is in addition to the strain that growing the file places on the file systems, although separating your files can help with this. Log files, specifically must be grown and zeroed out before ...


1

I think there may be some confusion here on the word "Shrinking" - The log file is not ever "Shrunk" by a backup. It can be "truncated" by the log backup, but not shrunk. When a log is truncated, the segments in the log that are no longer needed for recovery (either because you are in SIMPLE recovery model and a checkpoint has ensured that the data is in ...


1

I wouldn't put it into Simple Recovery Mode. I would keep the transaction log backups as-is. I would recommend considering decreasing the frequency of the backups unless you're okay with losing an hour's worth of data if stuff hits the fan.


3

I would recommend that you strongly consider breaking the mirror and rebuilding it once the maintenance is complete. When you pause the mirror, the transactions that need to be sent over will build up in your transaction log. Taking a transaction log backup will not free this space for reuse, although the log backups will be consistent and usable for their ...


0

The NOLOGGING mechanism, as you pointed out, is to avoid the generation of redo logging information, and that can make bulk loading faster. I use it typically for big loading jobs. For example, loading terabytes of satellite imagery: it means that I will write TB of bits to the database proper (the data files), but that I will also write terabytes to the ...


2

There is no such hint as NOLOGGING. Sure, UPDATE and DELETE operations can not take advantage of NOLOGGING, but there are other operations that can: logging_clause NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode: DML: Direct-path INSERT (serial or ...


1

Could this be the reason for large log files Yes, particularly with FULL recovery model and if there is NO frequent LOG backups as the in-active VLFs in LOG file cannot be truncated without backup in full recovery model. With Simple recovery model LOG backup is not the case, most probably at one point of time there might have been a heavy transaction/...


0

log_reuse_wait_desc in sys.databases for your database can show LOG_BACKUP under 2 circumstances: waiting for a log backup to run or the log records that were backed up were all in the current VLF and so it could not be cleared. If the log backup ran but still log_reuse_wait_desc shows as "LOG_BACKUP", it's probably because of the 2nd reason. What this ...


Top 50 recent answers are included