Wednesday, January 28, 2015

Backup database to disk = ‘Nul’

To understand what this is doing, first we need to understand what Nul is, in the context of a file. It is not just a misspelling of NULL.

DOS, since the earliest days, has had a number of devices that can be treated like files, eg LPT1, COM1 and CON. NUL is another of those devices (virtual files, if you like) and is equivalent to the unix \dev\nul, a ‘file’ that deletes any data that’s written to it.

What are the impact of this Backup location

Anything that’s written to NUL is discarded. So when a transaction log backup is taken at ‘NUL’ , you are actually breaking the transaction log chain. That means, you wont be able to restore the transaction logs. As SQL thinks that the log chain is intact, it discards the log records that were sent to Nul as it would after a normal log backup. It does not switch the DB into a pseudo-simple recovery model. Inactive log records are not discarded at a checkpoint and future log backups will succeed.

That’s the main difference. Log records will still be retained after a backup to NUL. Log backups will succeed after a backup to NUL. They’re useless for restoring the DB because there’s a log backup missing, but they’ll succeed.

This command runs great accept under the following conditions:

  1. If you run a differential backup, don’t run this command! The reason is that the differential backup picks up pages that have changed. Running the backup command resets the flag on the page. Since the backup to a NUL: device reads the page, but does not back it up to media, the flag is reset as if it HAD been backed up… so the database does NOT back it up during the next differential. If you have run a backup to NUL: and do differential backups, run a FULL backup immediately and everything will be fine.
  2. If you have heavy disk IO now, a backup to a NUL: device will only add more stress slowing things down more.
  3. For reason #2, run this command in off hours.

How to recover space from a huge transaction log file

Most of the novice dba’s forget to take the transaction log backups, which eventually ends up consuming a lot of disk space. This is one of the questions people ask me very often. Here are the steps which I tell them to follow.

  1. Take the full backup if the database
  2. Check to see if something is keeping SQL from reusing existing log space:
    SELECT name, log_reuse_wait_desc
    FROM sys.databases
    WHERE name = '<your_db_name>'
  3. Change the recovery to ‘Simple’
  4. Find the fileID, use the following statement
  5. USE <your_db_name>
    EXEC sp_helpfile
  6. Shrink the log file

USE <your_db_name>

     6.  Resize the log file  so that it returns free space to the OS

ALTER DATABASE <your_db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 4GB )

     7.  Change the recovery to Full and take another backup

Thursday, January 8, 2015

What causes Multi-Page allocations?

In SQL Server versions before SQL 2012 single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the -g memory_to_reserve startup parameter.

So what kinds of query result in MPA activity? Here are few scenarios

1. A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA.


2. Another workload would be large adhoc batches of the form:


In SQL 2012 all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.