Category Archives: SQL

Shrinking SQL Log files in an Availability Group Cluster or Database Mirror

A very common problem that I see time and time again is the Log file growth of Microsoft SQL Server .LDF files.

This problem can cause service outages when a hard disk is filled up completely by these massive LDF files.

The problem happens when a SQL Server Database is configured for Full Recovery mode (often the Default). In Full Recovery mode, the SQL Log files (.LDF files) must be backed up themselves, in addition to backing up the SQL Database. Many people get confused and think they only have to backup the SQL Database file.

Solving the problem

Ideally, you should start backing up the SQL Log files. They are there for a reason, and full recovery mode is awesome because it allows you to restore a database to a point in time, specifically, to the point in time that you backed up the SQL database + and then the last SQL transaction log backup. So if you perform a SQL Full backup at 8pm nightly, and a SQL transaction log backup the next day at 12:00 Noon, then you can restore to any point in time up until 12:00 Noon.

If your recovery point objective (RPO) allows you to lose up to a day’s worth of data, and you are okay with restoring only to the previous night’s full backup, then by all means, change your database recovery mode to Simple and avoid the hassle of backing up the SQL Transaction logs altogether!

You might say, wait, this is all well and good, but I have a problem right now that I am trying to solve. My .LDF files have filled up my hard disk, and I need to clear them out now! First, before you proceed, it is important to understand why the logs are growing, otherwise you may find yourself having to continuously repeat this procedure. Log growth is normal when there are lots of write transactions into the database. The solution is to backup the transaction logs more frequently so that they are logically truncated, and that can prevent the physical file from growing too large.

First, find out if your database is in an Availability Group or a Database Mirror. Because your options are limited in this case. If your database is not in an AG or DM, then just switch the recovery model to simple, shrink the Log file using SQL Management Studio, then if needed, switch the recovery model back to full.  This method is the quickest, but  you lose the ability to restore to a point in time from the last full backup, so perform this at your own risk. In fact, all advice on this blog is for educational purposes, and I provide no warranty, and I assume no responsibility if you follow any of my advice. =)  If you have available disk space,  you can always backup the SQL transaction log first before performing switching the recovery model from full to simple.

Okay, so assuming you need to shrink a log file that is in an AG or DM, then the only method I have found that works is to perform the following (again, use at your own risk):

1. Identify the culprit log files by running this query in SQL Management Studio:
DBCC SQLPERF(LOGSPACE);

In my case, this showed two databases with log files > 65GB.

image

2. Next, backup the Log file to free up space within the file (logically/virtually). Ideally, if you had enough disk space, you would backup the log file to an actual file somewhere. Otherwise, if you are okay with an RPO of 24 hours (to your last full backup) then you can backup to a null device (great blog article here describing this method, please heed the disclaimers).

BACKUP LOG myDatabaseName TO DISK=’NUL:’

Note: Technically you should be able to run this command against the primary replica or the secondary replica, and the log file will be truncated in both places according to this blog article.

3. Next, verify if the log file is in a state that will allow shrinking. If your status is ‘2’ then you will need to proceed to step 4, otherwise if the Status is ‘0’ (Zero) then you can skip to step 5.

Use myDatabaseName
GO
dbcc loginfo

image

4. This step will reset the log file so that you can physically shrink it in step 5. Again, this step assumes that you are okay with a 24 hour RPO as you will only be able to restore to your last full backup.  I’ve worked with enough DBA’s that if I don’t add these disclaimers at each step then they will certainly spam the comments with ‘don’t ever do this step’ =)

DBCC SHRINKFILE (myDatabaseName_Log, EMPTYFILE);

Next, re-run step 3 (dbcc loginfo) and verify that Status is now 0 instead of 2. If it is, then proceed to step 5, otherwise re-run step 2 and 4.

5. Now that the transaction log has been backed up, and emptied, it is now possible to physically shrink the size of the log file on disk with this command:

DBCC SHRINKFILE (myDatabaseName_Log, 500);   –This would physically shrink the database size to 500 Megabytes.

Important: you can only shrink files against the primary replica. The good news is that once you shrink the primary, the physical size of the secondary replicas will shrink too, so you only need to do this in one place.

Hint: dbcc opentran shows if there are open transactions that could block the shrink operation.

Hint #2: If the log files still will not shrink, check to make sure that the secondary replica database is not marked as Suspect. In that case, you will need to manually remove the suspect database from the secondary first before the shrink operation will work.

Note: Before determining the size of 500Mb to shrink to, you may want to consider how much of the log file is in use, otherwise the shrink operation will not work. Also, you may want to consider allowing the size of the log file to be 25% of the size of the physical database file (.MDF) because otherwise when log growth happens, the database operations will block all active transactions and that will cause latency within applications (imagine users complaining).

You can determine how much of the log file is in use by running this query:

Use myDatabaseName
GO

SELECT name ,size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

So to determine the size of the log file to shrink to, subtract the “AvailableSpaceInMB” from the physical database size reported by the command: DBCC SQLPERF(LOGSPACE);. Then add some cushion so that future physical log growth does not block transactions from occurring.

SQL Transaction Logs filling up disk

This is the most common cause of SQL outages and crashes. The SQL transaction logs multiply until they completely fill up the hard disk, causing the databases to go offline and disrupting service to any application depending upon SQL including mission critical applications like SharePoint, Lync, Project Server, etc.

This is preventable by doing one of two things:

1) Change the database recovery option from Full to Simple*

or

2) Create a transaction log backup job. This can be done using the native SQL backup or a 3rd party backup software. To keep the log from filling up again, schedule log backups frequently.

When the recovery mode for a database is set to Full, then a transaction log backup job must be created in addition to backing up the database itself.

Tips:

1. The backup jobs should be monitored because if they fail for any reason, and the logs continue to grow until they fill up the disk, then SQL will crash along with any application that depends upon SQL.

2. The disk where the transaction logs are stored should be large enough to allow for multiple days of failed backup jobs (just in case your DBA is on vacation and the backups happen to fail for that duration of time, and they are not there to respond to the failed jobs).

3. For performance reasons, the transaction logs should be isolated to their own disks because SQL writes logs sequentially, whereas data written to the MDF file is written with random I/O. This is more relevant for Direct Attach Storage (DAS) and less relevant for a Storage Area Network (SAN) where large amounts of cache reside on the disk controller to offset the write transactions.

4. For high availability reasons, the transaction logs should be isolated to their own disks when databases are set to the Full recovery mode because if they reside on the same disk that hosts the MDF database file, and that disk crashes, then your ability to recover is limited to the last full backup. By having the transaction logs on a separate disk, you give yourself to recovery to the point of failure (steps: restore last full backup, then restore the transaction logs).

5. Your SQL backups should not be stored on the same server as the SQL server itself. If the server crashes, how will you get to your backups? Many companies will use a 3rd party backup software to pull the databases backups off the server, and then simultaneously use the native SQL backups to have a 2nd set of backups reside locally. The benefit is you now have two backup copies of your data, and it is usually faster and more reliable to recover data using the native SQL backups than using 3rd party backup software (this scenario is applicable when the server itself has not crashed and you are simply trying to recover data that was deleted).

*Before changing the database recovery option from Full to Simple, you should understand what the difference is between these two modes because you are choosing to only recover to the point of the last full backup. This may be okay for non-critical data, and only the custodian of the data can make that decision.  See this MSDN article for more information.

It is worth noting that backing up the transaction log will not reduce the physical size of the .LDF file (this is a common misconception). The backup will only free up the internal free space inside the LDF file. To reduce the physical size of the LDF file you must shrink the transaction log.  Normally this is not something you

If you do not have a DBA to monitor and respond to SQL backups then consider hiring a company who will monitor this for you.

How to verify that SQL Server is using the best practice NTFS Cluster Size

 

When planning the NTFS cluster size for a new volume the general consensus with the latest versions of Microsoft SQL Server is to use 64k.

So if you are auditing an existing installation, the following command-line utility can be used to query the NTFS file system to return whether this best practice was followed.

C:\Windows\system32>fsutil fsinfo ntfsinfo d:

NTFS Volume Serial Number :       0x32fe73cbfe7385bf
Version :                         3.1
Number Sectors :                  0x000000001beb87ff
Total Clusters :                  0x000000000037d70f
Free Clusters  :                  0x000000000037d1a9
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000010000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x0000000000000001
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x000000000000cca0
RM Identifier:        582D805C-3F5D-11E0-90B5-0017A4770826

 

You will notice that the result returned is slightly higher than 64k, and rounds up to 66k, but that is due to how storage is calculated and is the expected result when 64k is chosen from the format menu.

Locked out of SQL?

I was recently found myself locked of a demo environment by removing the builtin\administrators group from SQL Server’s sysadmin role – a good practice by the way. I needed to get into SQL Management Studio.

This particular installation of SQL was running under the localsystem account.  Using PSEXEC from sysinternals, I was able to launch a command prompt running as localsystem: psexec –i –s cmd.exe

I was then able to run SQL Management Studio (by right-clicking on the shortcut and pasting the path into the elevated cmd shell) and add my domain account back in as sysadmin =)