Some Transaction Log Smarts

Paul Randal writes about and demonstrates the workings of the transaction log.

Some reasons why your transaction log will not or is not shrinking.

A query to see why your log cannot be reused and is stopping the function of your database

SELECT name,log_reuse_wait_desc
FROM MASTER.sys.databases

The meanings of log_reuse_wait_desc are shown in the msdn article linked to above.

And finally, some steps for what to do when your log is full.

Making backups for non-production usage

Last night while dealing with a full transaction log, it was requested that I restore a database.  I generated the restore statement to a certain  point in time and reviewed the script.  The backup file was being retrieved from a non-standard location and when I went there, the backup file was not present!  I tracked down a job on the dev server which was backing up this DB, restoring it to dev and then deleting the file!  The result of this is that we effectively had no recovery available for the database since that backup was deleted.  How did this happen and how can we avoid this?

It’s a frequent task in SQL to create a back for a developer to use in staging, development or on their desktop.  If they can do with last nights data, you can just give them a copy of the previous day’s backup.  If they need something current, you’ll need to make another backup.  You’ll probably run a statement like this:


This file has now become a vital part of your recovery for this database.   To recover anything done past this point using transaction log backups made in the future, you’ll need this file.  So you can’t just drag this file off to another server or share.

If you want to move the file around or delete it, use this command: 


You can back up straight to a network share on another machine and save some time copying files around.  This doesn’t disturb your chain of backups and log backups and lets you do as you please with the file. 

More information on log sequence numbers and how they work can be found here .

Renewing SSL Certificates for SQL Reporting

Once a year, our certificates expire for ssl on reporting services.  These can be replaced with zero down time in reporting services.
1.    Every year, the certificate for reporting services must be renewed.
2.    Obtain certificate .pfx file and password from web infrastructure team.
3.    Connect remotely to the reporting server.
4.    Open Control Panel > Administrative Tools > Public Key Management
5.    Open Certificates (Local Computer) > Personal
6.    All Tasks > Import
7.    Let the wizard determine the store.
8.    Open IIS > Web Sites > Default WebSite > Directory Security Tab > Server Certificate
9.    Replace the current certificate
10.    Pick new cert from the store
11.    View the website and click the lock to the right of the url bar in IE.  Click the ‘view certificate’ button and ensure the date has been changed to the new certificate effective/expiry dates.

As you can see, we didn’t restart the website and you’re using the new cert.