New answers tagged

8

To answer your question, why not just grow the file to 500 GB and then shrink it. By which I mean dont' generate 500GB of transactions first. --grow file... USE [master] ALTER DATABASE <DBName> MODIFY FILE (NAME=N'<logical filename>', SIZE=512000MB) GO --shrink file... USE [database name] DBCC SHRINKFILE(N'<logical filename>', [target ...


0

Depend on your database activity and database recovery mode you can manage TLOG size in different ways. First of all to reduce TLOG usage you have to make transactions smaller. If the database is in FULL recovery mode, do TLOG backups often. As lots of guru's say - don't touch TLOG and TempDB they grew as they need to.


3

If you look at Maximum Capacity Specifications for SQL Server, you will see that the maximum size for transaction log is 2 TB: SQL Server Database | Maximum sizes/numbers | Maximum sizes/numbers Engine object | SQL Server (32-bit) | SQL Server (64-bit) ... | ... | ... File size (log) | 2 terabytes ...


4

I think it is important to make transaction log backups, but everything depends on the individual case. For example: Your database grows slowly and changes occur very rarely, so, in this case, you can use simple recovery model, and backup plan like you described. You know that simple recovery model allows full and differential database backups ...


2

You need to ensure you are running the query in the context of the correct database. Since system views such as sys.objects are context sensitive, they only return rows related to the current database. For instance, running this code in master would only show tables where the PartitionId exists in master, and those table names would likely be incorrect. ...


2

MinLSN is the LSN of the oldest uncommitted transaction. This is not exactly true LSN stands for Log Sequence Number. Each log record has a LSN and each new log record is written to the logical end of the log with a higher LSN than the previous LSN. Using this simplified Log Sequence: LSN Log 80 Begin Tran 1 82 Begin Tran 2 83 Update Tran 2 84 ...


1

How does a transaction log backup deal with active log? What happens to the log records that maintain any open uncommitted transactions when a transaction log backup starts? Will these records also be included in the log backup? Yes, active transactions are included in transaction log backups and this is how the database restore WITH NORECOVERY ...


-3

The main idea is to increase performance and place each file to it's own HDD drive. As I have to much IO related to InnoDB log files. ...


5

Good news! You don't have to query Simpana, NetApp, or PowerShell to figure out where the log file sizes have been changing. Just query the msdb.dbo.backupset table - it tracks database name, backup date/time, and backup size. You can trend this over time. I like charting this with Excel or SSRS to show volume trends, and then I can drill down into detail ...


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


2

The short answer is - after you made some changes to a table all this information are recorded into transaction log immediately, but all changes that you made are recorded into data file during the checkpoint. After the transaction log backup, all virtual log files marked to be reused.


1

Whichever transaction requires additional log space will grow the log file within that transaction, so it can complete. So if userA is performing a bulk load of data and uses up all available space in the log file, you will see a growth event for the log being called by userA.



Top 50 recent answers are included