Vedran Kesegic Blog – Page 5 – SQL Server Articles, Tips and Tricks

Transaction log myths

VedranPosted on 2013-03-28 Posted in Backup and Recovery, Transaction Log — 3 Comments ↓

There are some myths widely spread about transaction log that are to be debunked here:

Log backup copies log file

FALSE. Someone might think if you have 100GB log, that log backup will also be 100GB, since they think the log backup is a “copy” of the log file. But that is not true. Log backup copies only a PART of the log file, from log write point of last log backup till current log write point within the log file. That means, if you have huge 100GB log file, you can have tiny log backups of eg 1MB each if you do log backup often eg. every 1 minute. If you have 10 log backups every 1 minute, the sum of that 10 file sizes will be the same as a single file you would get by backing up log after 10 minutes. It is like 10 minutes of records that can be in single file or sliced to 10 files, but the sum is the same.

Full/diff backup will clear the transaction log

FALSE. Only transaction log backup in full and bulk_logged recovery model, or checkpoint in simple recovery model will trigger the log clearing process. Full and diff backups will only defer it until the backup finishes. If you only do a full/diff backup in full recovery model, your log will grow until the disk is full.

Log backup cannot be taken if FULL/DIFF backup is in progress.

FALSE. Since SQL 2005, log backups can be taken concurrently with full/diff backups. Only log clearing (marking VLFs free) will be delayed until full/diff backup finishes, so your log file might grow bigger during full/diff backup. See Paul Randal’s post about that.

Log truncate will make it smaller

FALSE. Log truncate just scans active VLFs (virtual log files) and those that are not needed anymore by anyone marks as inactive. That does not change the log file size.

Log shrink will make it smaller

It depends. DBCC SHRINKFILE on the log can only chop-off part of the log file after the last active VLF. If the last VLF in file is active, file shrink will not make the file any smaller.

Instant file initialization will speed-up log file allocations (growth, initialization)

FALSE. Instant file initialization only works for data files, not for log files. Log files must be zeroed-out because of recovery process. It helps the recovery process after a crash to find where the log records really ends: when it bumps into zeros or alternate 64/128 value.

Multiple log files will make performance gains

FALSE. Transaction log file is always written sequentially, in a single thread. Multiple files won’t parallelize log writing. However, you could use additional files if for example, you run out of space on current log disk to add a new log file on the other disk.

My database is in simple recovery model, so the log will not grow

FALSE. Transaction log is used in every recovery model, including simple. It will just be cleared more often (on each checkpoint, instead on each log backup) and some operations will be bulk-logged. It is important to properly size the transaction log even for databases in simple recovery model.

It is a good guideline to determine the log size as percentage of database size

FALSE. Log size is determined by the maximum amount of log generated between two log clearings. For example, small database that does lot of data changes (e,g, in-place updates) can generate 100x more log than database size. Log must also fit the biggest transaction, e.g. biggest table rebuild.

Regular log shrinking is a good maintenance practice

FALSE. Log growth is very expensive because the new chunk must be zeroed-out. All write activity stops on that database until zeroing is finished, and if your disk write is slow or autogrowth size is big, that pause can be huge and users will notice. That’s one reason why you want to avoid growth. If you shrink the log, it will grow again and you are just wasting disk operation on needless shrink-and-grow-again game. You could shrink after some extraordinary event that blew-up log size, but never shrink to a size smaller than is normal operational size for that database.

TRUNCATE TABLE is not logged

FALSE. Extents (and up to 8 pages in mixed extents, per table/index/partition) are deallocated from the table and that is logged. But it generates far less log than DELETE command.

Minimally logged operation can only happen in BULK_LOGGED recovery model

FALSE. It can occur in SIMPLE mode also.

Every change is logged, regardless of recovery model

TRUE with one exception. The only operation that is not logged is on one part of tempdb database called row version store, used for row versioning.

Log records are written to disk immediately

FALSE. There is a 60KB log cache in the RAM that log records are written to, not the disk. That cache is written to the disk, to the transaction log file, as soon as all 60KB is filled, any session of that database issues a commit (a real commit, not the nested one that just decreases @@TRANCOUNT), and on some other events. All log records flushed together to the disk will have the same block offset (LSN consists of VLF number, block offset, and log record number). Therefore, log block cannot be larger than 60KB (cache’s size).

Procedure with Execute as login?

VedranPosted on 2013-03-24 Posted in Security and encryption — 7 Comments ↓

Sometimes we need a low-privileged user to do a specific administration task or task that require some server-level permissions (such as VIEW SERVER STATE, ALTER TRACE etc). Of course, we do not want to give that account server-level privilege, because it would not comply to the “least possible set of privileges” security recommendation. Sometimes we need a finer grained server-level permission, or to limit server-level permission to certain databases for example. That is advanced security task for a DBA, and I will show you here how to do it through an example.

Custom sp_who2

We will build a procedure that calls sp_who2, let low-privileged user to execute it and see all the processes on the server, without giving the VIEW SERVER STATE to that low-privileged account, as that privilege would give the user much broader rights than just calling sp_who2. The problem is that procedure is by default executed as caller, and the low-privileged caller of sp_who2 can’t see any sessions except his own. Let’s try:


-- We will create a database and a procedure which calls sp_who2.
-- We will also create a low-privileged login and give him exec permission on that procedure.

create database TestCert
create login LowPrivLogin with password='Str0ngPWD!'
GO
use TestCert
create user LowPrivUser for login LowPrivLogin
GO
create procedure dbo.show_processes
--with execute as 'dbo' -- even this wont help, because dbo is db user and can receive only privileges at db level, not server level
as
begin
	select database_user = USER_NAME(), [login] = SUSER_SNAME(), [original login]=ORIGINAL_LOGIN()
	select * from sys.login_token -- who am i
	select * from sys.fn_my_permissions(null,'server') -- what server rights do i have
	exec sys.sp_who2
end
GO
grant exec on dbo.show_processes to LowPrivUser
GO

execute as login='LowPrivLogin'
exec sys.sp_who2 -- shows only my session, not good
exec dbo.show_processes -- shows only my session, not good
revert
GO

-- We will create a database and a procedure which calls sp_who2.

-- We will also create a low-privileged login and give him exec permission on that procedure.

create database TestCert

create login LowPrivLogin with password='Str0ngPWD!'

use TestCert

create user LowPrivUser for login LowPrivLogin

create procedure dbo.show_processes

--with execute as 'dbo' -- even this wont help, because dbo is db user and can receive only privileges at db level, not server level

begin

select database_user = USER_NAME(), [login] = SUSER_SNAME(), [original login]=ORIGINAL_LOGIN()

select * from sys.login_token -- who am i

select * from sys.fn_my_permissions(null,'server') -- what server rights do i have

exec sys.sp_who2

end

grant exec on dbo.show_processes to LowPrivUser

execute as login='LowPrivLogin'

exec sys.sp_who2 -- shows only my session, not good

exec dbo.show_processes -- shows only my session, not good

revert

Output of the procedure is below:

The first recordset shows the database user, impersonated login, and my real (original) login. Second recordset (sys.login_token) shows we have LowPrivLogin and public server role context execution tokens. Third recordset shows that we have CONNECT SQL and VIEW ANY DATABASE server privileges we received from the public role. Fourth recordset is the output of sp_who2: only one row is there, our session, because we do not have VIEW DATABASE STATE on any database, and we certainly do not have VIEW SERVER STATE to view them all.

Even if we uncomment “WITH EXECUTE AS ‘dbo'” (or SELF, or OWNER, or any other user) the sp_who2 inside a procedure won’t give us all the sessions on the server.

Why “WITH EXECUTE AS ‘dbo'” won’t help?

In batch statements we can use:


EXECUTE AS USER = 'SomeDbUser'
EXECUTE schema.procedure
REVERT

EXECUTE AS USER = 'SomeDbUser'

EXECUTE schema.procedure

REVERT

The problem is that database user can only receive database-level privileges. VIEW SERVER STATE is a server-level privilege and cannot be assigned to a database user. Server-level privilege can only be assigned to a server-level principal: login.


EXECUTE AS LOGIN = 'SomeLogin'
EXECUTE schema.procedure
REVERT

EXECUTE AS LOGIN = 'SomeLogin'

EXECUTE schema.procedure

REVERT

That is promising!

When creating a procedure, function, DML trigger, or database-scoped DDL trigger there is “WITH EXECUTE AS <something>” clause available. But, unfortunately, that clause only accepts database users, not logins! SELF and OWNER are also resolved to a certain database user: SELF is resolved to a db user that executed a “CREATE PROCEDURE” statement, and OWNER is resolved to a db user that owns a procedure. Even “dbo”, a special database user whose rights are not checked at all, is scoped to a database and can’t receive a server-level privilege. Only server-scoped triggers can have “with execute as <login>” clause: server-level DDL triggers and logon triggers.

Since we can’t define a procedure to use “WITH EXECUTE AS <login>”, our plan to create a procedure to execute under server-level privilege is doomed to a failure! Or is it?

Procedure “WITH EXECUTE AS <login>” is possible ?

Not really, not without the tricks. And the “trick” is in signing the procedure. Signing the procedure with a certificate makes it executing under login associated with that certificate. Certificate is like a glue that connects the procedure to execute under specific login. That login is a special kind of login called “LOGIN mapped to a CERTIFICATE”. Nobody can’t actually log-in with that login. It servers just to receive a server-level permissions. And the code (procedure) can run under that login if it is signed by that login’s certificate. Sounds complicated? And it is! But once you understand how it works, it’s not so hard.

Use certificate or asymmetric key?

We cannot sign the procedure with symmetric key. That leaves us to choose between certificate and asymmetric key. Certificate is asymmetric key with some metadata added, like subject and expiration date. But the most important difference for us is that certificate can be backed-up and restored separately from database. Asymmetric key can only be backed-up and restored with a database backup/restore he lives in. We will need the same certificate to be in two databases:

master – because certificate mapped to a login can only be in master database
our target database – we can sign a procedure only with a certificate that is in the same database as the procedure

Because we need to copy the same certificate to another database, it is much easier to do it with certificate. With asymmetric key we could probably restore the whole database over another database, and recreate all the objects and data previously exported, or mess with updating system tables which is not supported and of course, not recommended.

Get our hands dirty!

First, we will create a certificate in the master database, and create a login mapped to that certificate. Then copy that certificate from master to our database via backup/restore of the certificate. Then we will sign our procedure with that certificate.


-- Create login to which we will grant view server state. Login will be mapped to certificate.
-- Sign procedure with that certificate - meaning procedure will execute under that high-priv login.
-- certificate must be in master database in order to be mapped to login

use master -- create certificate in master database

create certificate HighPrivCert
ENCRYPTION BY PASSWORD = 'Str0ngPWD!'
WITH SUBJECT = 'Certificate for signing stored procedures'
select * from sys.certificates
GO

create login HighPrivCertLogin from certificate HighPrivCert -- create high priv login
grant view server state to HighPrivCertLogin
GO

-- in order to grant db user rights, we must transfer to that db the same certificate
backup certificate HighPrivCert to file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\HighPrivCert.PVK',
    DECRYPTION BY PASSWORD = 'Str0ngPWD!', -- pwd to open the key
    ENCRYPTION BY PASSWORD = 'FilePWD!1' -- must protect the file
)
GO

use TestCert
create certificate HighPrivCert from file='C:\temp\HighPrivCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\HighPrivCert.PVK',
    DECRYPTION BY PASSWORD = 'FilePWD!1', -- to read the file
    ENCRYPTION BY PASSWORD = 'Str0ngPWD!' -- pwd to protect the key
)
select * from sys.certificates

-- Manually delete cert files NOW!
GO

-- by signing procedure, it will run under login associated with that certificate
ADD SIGNATURE TO OBJECT::dbo.show_processes
BY CERTIFICATE HighPrivCert
WITH PASSWORD='Str0ngPWD!'
GO

execute as login='LowPrivLogin'
exec sys.sp_who2 -- shows only my session
exec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.
revert
GO

-- Create login to which we will grant view server state. Login will be mapped to certificate.

-- Sign procedure with that certificate - meaning procedure will execute under that high-priv login.

-- certificate must be in master database in order to be mapped to login

use master -- create certificate in master database

create certificate HighPrivCert

ENCRYPTION BY PASSWORD = 'Str0ngPWD!'

WITH SUBJECT = 'Certificate for signing stored procedures'

select * from sys.certificates

create login HighPrivCertLogin from certificate HighPrivCert -- create high priv login

grant view server state to HighPrivCertLogin

-- in order to grant db user rights, we must transfer to that db the same certificate

backup certificate HighPrivCert to file='C:\temp\HighPrivCert.CER'

WITH PRIVATE KEY

( FILE = 'C:\temp\HighPrivCert.PVK',

DECRYPTION BY PASSWORD = 'Str0ngPWD!', -- pwd to open the key

ENCRYPTION BY PASSWORD = 'FilePWD!1' -- must protect the file

)

use TestCert

create certificate HighPrivCert from file='C:\temp\HighPrivCert.CER'

WITH PRIVATE KEY

( FILE = 'C:\temp\HighPrivCert.PVK',

DECRYPTION BY PASSWORD = 'FilePWD!1', -- to read the file

ENCRYPTION BY PASSWORD = 'Str0ngPWD!' -- pwd to protect the key

)

select * from sys.certificates

-- Manually delete cert files NOW!

-- by signing procedure, it will run under login associated with that certificate

ADD SIGNATURE TO OBJECT::dbo.show_processes

BY CERTIFICATE HighPrivCert

WITH PASSWORD='Str0ngPWD!'

execute as login='LowPrivLogin'

exec sys.sp_who2 -- shows only my session

exec dbo.show_processes -- shows all sessions! We are running under HighPrivCertLogin which have VIEW SERVER STATE permission.

revert

Now, let’s see the result:

As you can see in first recordset, I am still low-privileged login and user. But in second recordset you see two new rows: HighPrivCert and HighPrivCertLogin. In third recordset you can see one new permission is added: VIEW SERVER STATE. And the last recordset shows all the processes on the servers, exactly what we wanted!

You can view the list of signed code through sys.crypt_properties view:


select
  object_name = object_name(major_id),
  signed_by = cp.crypt_type_desc,
  cert_or_asymkey_name = ISNULL(c.name, ak.name),
  *
from sys.crypt_properties cp
  LEFT JOIN sys.certificates c on c.thumbprint = cp.thumbprint
  LEFT JOIN sys.asymmetric_keys ak on ak.thumbprint = cp.thumbprint

select

object_name = object_name(major_id),

signed_by = cp.crypt_type_desc,

cert_or_asymkey_name = ISNULL(c.name, ak.name),

from sys.crypt_properties cp

LEFT JOIN sys.certificates c on c.thumbprint = cp.thumbprint

LEFT JOIN sys.asymmetric_keys ak on ak.thumbprint = cp.thumbprint

This is tested on SQL2012, but should work on 2005+.

Conclusion

You can grant server-level privileges to a code (procedure, UDF, trigger) indirectly, through a code signing. We had example with sp_who2, but you can encapsulate any functionality that requires elevated privileges in a secure way, without giving that high-level privilege to a user.

Set transaction log size and growth

VedranPosted on 2013-02-09 Posted in Backup and Recovery, Transaction Log — No Comments ↓

Introduction

What is the best practice, how to properly set-up transaction log initial size, growth, and number of VLFs? There are many articles on the net on that theme, and some of them are suggesting really wrong concepts, like setting initial size of the log as the percentage of the database size (e.g. 10% or 25%) – that does not have any reasoning at all, and I’ll explain why. Also, you will get a method to determine a proper size for your transaction log.

Transaction log initial size

The rule of thumb for initial size is:

Set it large enough so it will not grow during normal operation.

If you have set some initial size, and the log grows without something extraordinary happened to db (your log backup job has broken, or someone accidentally left transaction opened forever etc), your size estimate was too low. That simple. If it grows, initial size was not good, it was too small.

The most precise method to determine right initial size is to wait for at least a week or one business cycle (a month, including maintenance operations like index rebuilds to pass), and see what size the log will grow. And that is the actual initial size you should keep. Never shrink the log below that value, because it would be just wasting of IO operations (and increasing fragmentation and number of VLFs, hurting performance) as it will grow back again to that size!

If you do not want to wait so long, you could roughly estimate the log size needed to rebuild your largest index in db (that is usually clustered index of the biggest table) and set the initial log size to that value. That would be the “fit largest transaction” method. Imprecise, but is very simple.

Only in full and bulk logged recovery model you can use another method: to measure maximum transaction log generation rate, multiply by time between two transaction log backups, and set that as initial transaction log size (shorter interval between log backups equals smaller log size). You can measure the log generation rate of your application by monitoring the System Monitor counter Log Bytes Flushed / sec for the database. It is also imprecise method because it is a prediction, but it is a starting point. Later, you will have to check and probably adjust log initial size by using the precise method, first one described, which takes time.

We know how to detect is initial size too low (if log grows, it is too low), but how do detect if your log size is too high? If log never grows and log used percentage never reaches not even close to 100%, you have probably over-sized your transaction log. To be able to detect that, you have to monitor percentage of log file used for a longer period of time to be sure, not just a few days. Over-sizing doesn’t hurt performance (as opposed to under-sizing which is), it just hurts the disk space. So, it is better to over-size than to under-size initial transaction log size.

Tip: If db is in full recovery model and rebuilding indexes has biggest impact on that log size (you see that if log used percentage only grows close to 100% during index rebuilds and at other times it is very low), switch to bulk logged recovery model (just temporarily) during index rebuilds. That will probably significantly decrease transaction log size you need.

Set transaction log file growth

This is almost equally important as initial size. By default it is 10% of log size, and you should definitely change that. Set it as a fixed size in MB, never percentage. If you set it too low, you will end up with double fragmentation in transaction log: file itself will become fragmented, and there will be created too many VLFs that also can causes slow transaction log operations. If you set file growth too high, you should know that instant file initialization does not work for transaction log files. That means, every time transaction log grows the newly allocated chunk will have to be zeroed-out (overwritten with zeroes). During that operation all write activity in that db stops, until your disks finishes zeroing-out that new chunk! So, you definitely do not want to set it too high, either!

The rule of thumb would be to set is as large as possible, but not so large that users would notice the wait because of zeroing-out. That would be somewhere between 50 and 512 MB, depending mostly on the speed of the disk, and a bit less on the size of the log. I usually set it to 50MB for transaction logs that are really small, so larger growth has no sense if total log size is e.g. 10MB. For larger logs, file growth of 128MB or 256MB would be file on average-fast disks, and 512MB for really fast disks. For example, if your storage sequential write performance with one queue is 50MB/s and you set the log file growth to 500MB, all write activity in the db will stop for 10 seconds each time log growth occurs. Bear that in mind, because users will probably notice!

Remember, log growth normally should not happen at all during normal db operation if you have properly set the initial size of transaction log!

Transaction log Truncate vs Shrink vs VLF number

VedranPosted on 2013-02-08 Posted in Backup and Recovery, Transaction Log — 10 Comments ↓

Transaction log truncate – why it didn’t shrink my log ?
(Truncate vs Shrink)

Introduction

One term that makes the most confusion between people dealing with sql server transaction log is “log truncation”. In this article I’ll explain what log truncation is and the difference between log truncation and the log shrinking. Everything said here applies to all recovery models (simple, full, and bulk logged) unless otherwise specified.

Log Truncation

If you look-up the word “truncate” in the Cambridge dictionary, it says “To make something shorter or quicker, especially by removing the end of it“. It is no wonder that many people think log truncation will chop-off the end of the transaction log, making it smaller – very wrong! “Log truncation” is a poorly chosen term for marking parts of the log free for reuse. Obviously, marking something (changing a bit that says is something free or not) certainly will not change the file size. So, the term is a kind of misnomer. Much better term to use is “log clearing“. It does not overwrite parts of the log with zeroes, it just clears the bit to say “this part of the log is not needed anymore and can be reused/overwritten”. Overwriting with zeroes happens only on log growth for the newly allocated part and cannot be avoided. Even instant file initialization cannot avoid zeroing, and there is a good reason for that (not because of security, but because of functionality – I’ll explain in some other post).

Log file is internally logically divided into smaller chunks called Virtual Log Files (VLF). Each VLF is the smallest (and the only) unit that can be marked as available for reuse (free) or not available for reuse (used). If VLF contains one or more log records that are needed by somebody and thus must not be overwritten, the entire VLF is marked “active” (used). If VLF does not contain any records that are needed by anyone, entire VLF is marked as “inactive” (free) during log truncation process. “Inactive” means VLF can safely be reused / overwritten.

Log truncation means “marking VLFs as free“

The process of marking active VLFs as inactive does not occur all the time the log is written to because it would be too often and it does take resources. It occurs only on CHECKPOINT if we are in simple recovery model, and only on LOG BACKUP if we are in full or bulk logged recovery model. It is very important in full recovery model to set a periodic log backup, because otherwise log clearing process will never occur, thus once active VLF will never become inactive and your log will grow until the disk is full. Log clearing process inspects active VLFs and it can happen that no VLFs can be marked as inactive because none of them met the required conditions to be cleared.

WHY my log file is not smaller after DBCC SHRINKFILE ?

The reason is written in log_reuse_wait_desc column of sys.databases view. Query it:


SELECT name, log_reuse_wait_desc FROM sys.databases

SELECT name, log_reuse_wait_desc FROM sys.databases

The conditions for an active VLF to become inactive during log clearing process in all recovery models are:

VLF must not contain any part of active transaction (uncommitted). Beware of long running transactions. log_reuse_wait_desc in sys.databases is ACTIVE_TRANSACTION.
checkpoint has not occurred after that VLF. For recovery process we need log records of the changes that are not yet hardened into data files. log_reuse_wait_desc is CHECKPOINT.

In full and bulk logged recovery models there are additional conditions:

Log backup – all the log records i that VLF are backed-up. log_reuse_wait_desc is LOG_BACKUP.
VLF does not contain any log records needed by some process (transactional replication log reader agent, mirror log reader agent)

For a complete list of conditions that must be met see the BOL on sys.databases column log_reuse_wait_desc.

List your Virtual Log Files (VLF)

You can inspect a list of VLFs in your transaction log by issuing a command DBCC LOGINFO. Here is example output:


DBCC LOGINFO
-- SELECT * FROM sys.dm_db_log_info(NULL) -- new function

DBCC LOGINFO

-- SELECT * FROM sys.dm_db_log_info(NULL) -- new function

Each row represents one VLF, so in our example we have 7 VLFs in the transaction log. FileSize is the size of each VLF in bytes. If you sum them all and add 8KB header (you can see that first VLF offset is always 8192 bytes, not zero) you will get the exact log file size. Status is the most interesting for us at the moment: value of 2 means “ACTIVE”, and 0 is inactive (free). There will always be at least one active VLF, the one log writer is currently writing to.

When the log writer bumps to the end of the log file, it will wrap-around and start writing over the first free VLF (which automatically becomes “active”). If there are no free VLF’s that log writer can write to, the log file will, you probably guessed – GROW! It will grow by the amount specified in FILEGROWTH attribute of the database file. It is best practice to set that growth manually to fixed size, not percentage.

Log shrinking

WARNING: Do NOT shrink the log below the size you know it will grow-over again in a week or a month! You are just wasting disk IO on shrink-and-grow-again game, and users will notice because all writers STOP while log grows and zeroes-out the new area!

Log shrinking actually can make log file smaller. But only if there are inactive VLF’s at the end of transaction log. The command is DBCC SHRINKFILE:


DBCC SHRINKFILE(2, TRUNCATEONLY)

DBCC SHRINKFILE(2, TRUNCATEONLY)

Instead of truncateonly you can specify the new transaction log file size in MB you do not want to go below. “Truncateonly” is the same as zero. What this actually does is find last active VLF and chop-off the log file after it, making the log file smaller:

After executing SHRINKFILE command, the number of VLFs has decreased:

Conclusion

To summarize, log truncation is a misnomer and is better called “log clearing”. It will not shrink the log file at all because it just marks some VLFs as inactive. DBCC SHRINKFILE will actually truncate the log after last active VLF. That makes “log truncation” even worst term than just misnomer, because truncation does not occur in log truncation process at all and the real truncation occurs in completely different process (dbcc shrinkfile) often creating confusion among sql server newbies.

If the last VLF is active, you won’t be able to shrink the log. But log_reuse_wait_desc column in sys.databases will tell you the reason why that last VLF is still marked “active”.

Transaction log survival guide: Shrink 100GB log

VedranPosted on 2013-02-06 Posted in Backup and Recovery, Transaction Log — 1 Comment ↓

Introduction

You got yourself in situation where transaction log has massively grown, disk space became dangerously low and you want a quick way to shrink it before server stops ?

There are many articles on the net on that theme, but unfortunately many of them are wrong, giving false and even dangerous information. This will instruct you how to do a log shrinking properly, and give you the two methods: quick one that breaks log backup chain, and the one that does not break the log backup chain which is not as quick. But remember, this only buys you the time to learn about transaction log and how to properly set it up. That is your homework after that log crisis is gone!

Investigate the cause – quick!

To see the log size and percentage of uncleared log space (active/used log part cannot be overwritten. When log cannot wrap-around and reuse the space marked as “free”, log grows):


DBCC SQLPERF (LOGSPACE)

DBCC SQLPERF (LOGSPACE)

Normally, that percentage should be low or close to zero for bigger log files.

Run this query to see the reason why the log grows (why it is not reused):


select name, log_reuse_wait_desc, recovery_model_desc
from sys.databases

select name, log_reuse_wait_desc, recovery_model_desc

from sys.databases

The most often cause you will see is “LOG_BACKUP”, so the next steps solve this reason of log growth. If you see other reason, like “ACTIVE_TRANSACTION”, you have to deal with that reason prior to log shrinking (e.g. commit that long-running transaction. Use “DBCC OPENTRAN” to find it). “NOTHING” means log will not grow anymore because it can be reused.

Shrink the log – fastest method

If your log is really big, like 100GB, or you do not have enough time or space to backup your 100GB transaction log, you can use this very fast method. But, be aware that it breaks the log backup chain. That means you will lose point-in-time recovery ability in time period between last successful log backup and end of the next differential backup we will take here. Point-in-time recovery to time after backup will be possible again. This method should take no more than one minute, no matter how big your log is.

First, execute sp_helpfile to find the log name, and use it here:


-- This breaks transaction log backup chain.
-- No point-in-time recovery after this!
alter database MyDatabase set recovery SIMPLE

-- Only in SIMPLE recovery CHECKPOINT initiates log clearing process
CHECKPOINT -- marks VLFs free for reuse

-- Once cleared, you can shrink the log file.
-- 1000 MB, Choose anything you like but not too small !
DBCC SHRINKFILE(2, 1000)

-- This breaks transaction log backup chain.

-- No point-in-time recovery after this!

alter database MyDatabase set recovery SIMPLE

-- Only in SIMPLE recovery CHECKPOINT initiates log clearing process

CHECKPOINT -- marks VLFs free for reuse

-- Once cleared, you can shrink the log file.

-- 1000 MB, Choose anything you like but not too small !

DBCC SHRINKFILE(2, 1000)

Done! Your log should now be shrinked to about 1GB.
Now put your database back to full recovery model:


alter database MyDatabase set recovery FULL

alter database MyDatabase set recovery FULL

Although sys.databases will now show that database is in full recovery model, it is actually still in SIMPLE recovery until full or differential backup is taken! We will enter full recovery model and initialize log backup chain by taking a differential backup because it is much much faster than full backup:


--full recovery model will take effect only after a backup
backup database MyDatabase to disk='D:\mydb.dif'
with differential, compression, stats

--full recovery model will take effect only after a backup

backup database MyDatabase to disk='D:\mydb.dif'

with differential, compression, stats

Shrink the log – without breaking the log backup chain

If you do not want to lose point-in-time recovery ability (you don’t want to break the log backup chain), and you have the time and space to backup your 100GB log file, or log is not really so big – this is the method for you!

Execute the job that takes transaction log backup and wait for it to finish OR do it yourself with this command:


-- change the file name on each execution!
BACKUP LOG MyDatabase TO DISK='D:\transactionLog1.trn'
WITH COMPRESSION, STATS

-- change the file name on each execution!

BACKUP LOG MyDatabase TO DISK='D:\transactionLog1.trn'

WITH COMPRESSION, STATS

That first log backup will take a VERY long time because almost all 100GB must be copied. Subsequent log backups will be very fast and very small even if the log file itself is still 100GB!

Use “sp_helpfile” to find the log name and use it in this shrink command. Do not shrink to less than it’s normal operational size (do not truncate to zero!):


DBCC SHRINKFILE(2, 1000) -- Shrink to e.g. 1000 MB

DBCC SHRINKFILE(2, 1000) -- Shrink to e.g. 1000 MB

Repeat this 2 commands (log backup to filename2, shrink, log backup to filename3, shrink, …) until the log is shrunk. Usually 1 or 2 cycles are enough.

Be careful to change the filename in each log backup iteration!

Phew, log is small again. What now?

After your log is back to manageable size, do this:

Ensure your log backup job is set and is functioning properly.

Optimize the initial log size, growth, and number of VLF’s.

Read and learn about transaction log