Tag Info

New answers tagged

2

Start with the manual page on Write Ahead Log wal_writer_delay (integer) Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of ...


0

If fn_dblog is hanging you may try some third party tools from ApexSQL, RedGate or Idera. Or you can try to backup the transaction log file and use fn_dump_dblog just to exclude problems with an online transaction log. You can find more info on this DBA post


3

I think if you have databases that you know have less (or especially no) activity during certain periods, you should simply back up the log less often during those periods. There is always going to be some log churn and determining whether it was due to user or system activity is going to be a nightmare (and there will almost always be some minute level of ...


-5

What you can do is 1- do an initial backup followed by an DBCC SHRINKFILE BACKUP LOG DatabaseName TO DISK = N'D:\Backup\DatabaseName_log.bak' GO DBCC SHRINKFILE('MyDatabase_Log', 1) GO 2- Before running the backup run a check to see if the transaction log size increased from the 1 MB size we shrieked it to ; If is bigger then 1 MB then you backup it. ...


5

One alternative here is to calculate the size of active transactions in your transaction log by using DBCC LOGINFO. Your logic would look something like this: CREATE TABLE #vlfs( RecoveryUnitID int , FileID int , FileSize bigint , StartOffset bigint , FSeqNo bigint , [Status] bigint , Parity bigint , CreateLSN ...


0

Will something like this help? It will tell you when the last transaction occurred and that's a start. How to read the SQL Server Database Transaction Log Are you planning on restoring those backups by hand or do you have a script that reads the file list and restores them in sequence? If the second option is your situation, I would suggest grabbing the ...


2

Transaction log files are written to and used in a sequential manner. This can be observed by using DBCC LOGINFO to examine the use of Virtual Log Files(VLFs) within your log file. For example, if I have a database and a table, where I insert 1000 rows into it, DBCC LOGINFO will provide the following: RecoveryUnitId FileId FileSize ...


2

Transaction log writes are sequential. Only one of the log files will ever be written to at any one time, so having multiple files - in and of itself - can't possibly change your I/O patterns for that database. Unless you are getting lucky. For example, you've added a second log file to an SSD or otherwise faster or less busy disk, or split the log files ...


2

Your initial thoughts were correct: There is no benefit to having multiple transaction log files. SQL Server utilizes the transaction logs sequentially, not simultaneously. why would increasing the number of log files end up have a very noticeable improvement on the Write speed to the Disk? It wouldn't. There simply must be another factor involved ...


1

It is certainly possible to use a Maintenance Plan to handle the fragmentation issues. However, many people use the free scripts at: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html I am part of the 'many people'. These scripts by default implement the suggested standards for reducing the fragmentation of indexes. So, if you ...


4

I don't need my transaction log. Yes you do. can i delete my log to increase space? No you can't. In this case, is there an "easy way" to copy the data from the old tables into new tables? Before you go throwing the towel in with your current database, you should step back and analyze why you think your transaction log is too large in your ...


6

It's a good idea to keep them separated, even if the drives are currently backed by the same storage device. It buys a lot of future flexibility, at a slight expense in management overhead for both the DBAs and the Windows/storage admins. If you want to change the physical storage that backs either of the drives in the future, you won't have to go through ...



Top 50 recent answers are included