SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Truncate trans logs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Peter99
Constraint Violating Yak Guru

489 Posts

Posted - 08/28/2014 :  15:42:47  Show Profile  Reply with Quote
backk up transaction log? What I am thinking data may be already written to data file but
as 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 but
as 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

USA
36922 Posts

Posted - 08/28/2014 :  15:48:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Peter99
Constraint Violating Yak Guru

489 Posts

Posted - 08/28/2014 :  15:53:44  Show Profile  Reply with Quote
Thanks Tara for quick response.
Go to Top of Page

Shanky
Starting Member

United Kingdom
48 Posts

Posted - 09/01/2014 :  10:29:20  Show Profile  Reply with Quote
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 but
as 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 helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000