This post was most recently updated on December 11th, 2018<\/p>
Transaction log principles The transaction log is a circular file i.e. when the end is reached any free entries at the start will be used. The system maintains the MinLSN which is a pointer to the first active log record. If the database is in simple recovery mode all entries prior to the MinLSN will be freed at checkpoint. Enterprise manager t-sql Detach the database, delete\/rename the log file, attach the database – this will create a minimum size log file. Enterprise manager t-sql This post was most recently updated on December 11th, 2018Transaction log principlesWhenever a data update is made entries are added to the transaction log.It is not possible to prevent this as it is part of the way sql server maintains integrity – particularly during recovery. The transaction log is a circular file i.e. when the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-130","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/posts\/130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/comments?post=130"}],"version-history":[{"count":1,"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/posts\/130\/revisions"}],"predecessor-version":[{"id":305,"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/posts\/130\/revisions\/305"}],"wp:attachment":[{"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/media?parent=130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/categories?post=130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bleuanus.nl\/index.php\/wp-json\/wp\/v2\/tags?post=130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
<\/font><\/strong>
Whenever a data update is made entries are added to the transaction log.
It is not possible to prevent this as it is part of the way sql server maintains integrity – particularly during recovery.<\/p>\n
This means that all being well the file will stay at a constant size as the current entry cycles round.<\/p>\n
Any log records before this (in the circular file) are free.
The MinLSN will be prevented from moving forward by any open transactions – i.e. the oldest open transaction entry will be >= the MinLSN.
The MinLSN is updated at checkpoint so committing a transaction will not immediately free entries and anything that holds up the checkpoint can cause problems.<\/p>\n
If the database is in full recovery mode (and a full backup has been taken) the entries prior to the MinLSN will only be freed by a transaction log backup (not full backup).<\/p>\n
Common cause of large transaction log file (.ldf)
<\/font><\/b>
Unfortunately the sql server default (except local editions) leaves the databases in full recovery mode.
This menas that if no action is taken no tr log entries will be freed and the log file will eventally fill the disk and crash the system.
The SQL Server installation process is very simple and commonly carried out by inexperienced personel. This will appear to work happily but cause problems later.
I would recommend always setting the model database to simple recovery mode to set the default for new databases.<\/p>\n
Stopping the transaction log file (.ldf) from growing
<\/font><\/b>
If the log file has grown do to being in full recovery mode then set it to simple before going any further. This should immediately stop the log from growing.
Enterprise manager
Right click on the database, properties, Options, set model to simple, OK.
t-sql
sp_dboption [dbname], ‘trunc. log on chkpt.’, ‘true’<\/p>\n
Shrinking the transaction log file (.ldf)
<\/font><\/b>
Before this make sure there are free entries by setting the recovery model to simple or backing up the log.<\/p>\n
Right click on the database, All tasks, Shrink database, Files, Select log file, OK.<\/p>\n
dbcc shrinkfile ([db_log_name])
Here [db_log_name] is the logical name of the log file as found from sp_helpdb or the table sysfiles<\/p>\n
Shrinking the log file via detach\/attach
<\/font><\/b>
Always take a full backup before a detach.<\/p>\n
Note that the log file must be deleted\/renamed otherwise it will be re-used even though it is not mentioned in the attach.<\/p>\n
Right click on the database, All tasks, Detach database, OK.
Delete\/rename the disk log file.
Right click on databases, All tasks, Attach database, Select the .mdf file, OK, Yes (to the create new log message).<\/p>\n
sp_detach_db [dbname]
Delete\/rename the disk log file.
sp_attach_single_file_db [dbname], [filename]
\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"