Have you ever wondered about flushing databases? Well not quite possible to flush it straight away and not a best practice too on the live server, so what it is about and you need to know about transactions state when they occur. Say when the changes occur in a database the changes are formed as transactions...
Posted to
SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)
(Weblog)
by
Anonymous
on
04-22-2008
Filed under: sql server, performance, msdn, backup, blogs, database, transaction log, cache, i/o, flush, monitoring, checkpointl
Whenever the SQL Server is struggling to write the transactions to the disk, you would observe WRITELOG wait type within SP_WHO2 results. So when you observer such wait types then you should be worried on the disks performance, either you plan for shrinking the transaction log (which is a temporary workaround...
Say which is better, having multiple transaction log backups files or single transaction log backup file during a recovery point of time. In my experience both of them doesn't make any difference and if you are performing further backup to tape then ensure to test them by restoring on to the standby...
On one of the archive database (using SQL 2005) we have a monthly task to shrink the transaction log during every 1st week of the month, this is required due to clear up the space on one of the drives where the additional transaction log file located (its a long story that we cannot replace the hardware...
Yet anothe forums related question I would like to blog, as I have seen in my experience on newsgroups. There will be always a question asking about why Transaction log is filling up and blocking is ocurred when a DBREINDEX & CHECKDB processes are under execution. Well the reason is obvious that...