Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Peter99
Constraint Violating Yak Guru
498 Posts |
 Posted - 2014-08-28 : 15:42:47
|
| backk up transaction log? What I am thinking data may be already written to data file butas there was no full and trans log backup, trans log file did not cleared. I have one instance that is not backed up (full, trans log etc) for long time. It is test server. There was disk space issue. I changed recovery model of big databaes to simple, shrink log file, changed recovery model to full again.My question is, how frequently sql server writes trans log to data file? What I am thinking data may be already written to data file butas there was no full and trans log backup, trans log file did not cleared and they took most of the disk space. Please lt me know if that is right.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-28 : 15:48:39
|
| Why would you set the recovery model back to FULL if you aren't going to backup the log? Use SIMPLE mode in this case.SQL Server writes to the tlog for all writes to the database. SQL Server is waiting for you to backup the log before it'll clear out the file.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2014-08-28 : 15:53:44
|
| Thanks Tara for quick response. |
|
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-09-01 : 10:29:20
|
quote:
Originally posted by Peter99
My question is, how frequently sql server writes trans log to data file? What I am thinking data may be already written to data file butas there was no full and trans log backup, trans log file did not cleared and they took most of the disk space. Please lt me know if that is right.Thanks
SQL server does not writes data to transaction log but writes information about changes made into data file and other relevant changes to transaction log.In Simple recovery model transaction log backup is not possible and log are truncated after checkpoint or when log file grows 70 % of its size. In full recovery and bulk logged recovery model log truncation only happens when you take transaction log backup.Evey transaction before being processed its information is written into transaction log this is called write ahead logging(WAL) this is done to secure the transaction and recover it or rollback it in case of failure.Now on how often data is written to disk/datafile depends on Checkpoint process and Lazy writer. They both work cohesively to make sure committed transactions are written to disk periodically. Checkpoint process flushes dirty pages to disk and lazywriter makes sure enough free space is there in buffer pool to being more pages in buffer pool. It creates enough free space by ageing out non used pages to disk.Hope this helpsRegardsShankyhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
|
|
|
Truncate trans logs