The best practice is to setup log backups periodically (if the database is not in SIMPLE recovery) in order to keep the database log size minimum. However there are some occasions even the log backup is setup, the log size keeps growing. To find out the reason you can refer, log_reuse_wait in sys.databases.
As per MSDN, log_reuse_wait is as follows; Also look at the log_reuse_wait_desc column for more detail.
Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = Other (transient)
Cheers for reading this blog post.
Subscribe to:
Post Comments (Atom)
How to interpret Disk Latency
I was analyzing IO stats in one of our SQL Servers and noticed that IO latency (Read / Write) are very high. As a rule of thumb, we know tha...
-
Sharding is a data partitioning technique which is popular in NoSQL databases like MongoDB and Cassandra. You can scale out the data set h...
-
I'm super excited to hear about the news, the first release of CTP (Community Technology Preview) of SQL Server on Linux at Microsoft ...
-
In-Memory OLTP, code named Hakaton is a separate database engine introduced in SQL Server 2014 and it is combined with SQL Server traditio...
No comments:
Post a Comment