Voice of the DBA

Category: Blog

  • Backup Log to Nul– #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

    There are times when you might be working on your demo/lab system and you generate a lot of tranasaction log activity. This isn’t data you want to save, so perhaps you want to remove the activity without saving it.

    There are a few choices:

    1. Run a normal log backup to a file, then delete the .trn file.
    2. Switch to simple mode
    3. Run a log backup to nul

    The first one is easy, but it’s a pain. I have to go to explorer, or open a VM, delete the file once I find it. The second one is what I’d suggest. In fact, as soon as you install SQL on a lab system, set model to Simple.

    The third item is valid, and I ran across this recently. When you use this syntax, make sure you use “nul” and not “null”. We are trying to send to /dev/nul, which is nowhere. If you backup here, then nothing happens. You can use this command:

    BACKUP Log sandbox2 TO DISK = N’nul’

    This will run a backup, and discard all of the backup data. When I say discard, I mean it’s not written anywhere.

    However, this is a real backup. It’s marked as such. This breaks a log chain, and you can do this with a full database backup as well, which means you really need another full backup after this to reestablish a baseline.

    Again, I ran across this, but it’s not what you want to do. If you need to clear the log, use

    ALTER DATABASE xx SET RECOVERY SIMPLE

    ALTER DATABASE xx SET RECOVERY FULL

    and take a full backup.

    More thoughts from Gail Shaw.

  • Using Test-DbaOptimizeForAdHoc

    I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

    One of the cmdlets is Dba-OptimizeForadHoc, which is designed to help you determine if this particular setting is enabled on your servers. There are plenty of ways you could get this data from sp_configure or PoSh, but this is a setting you might want to check in a hurry when there are potential performance issues.

    The cmdlet is easy to use and takes a –SqlServer parameter where you can list multiple instances. There are options for credentials that can be used for a connection, just remember that this credential needs to have rights to read the setting.

    2017-07-07 18_32_11-cmd - powershell

    If this cmdlet isn’t useful, check out the full list. I bet you find some that you will want to try out.

  • Azure Labs–Minor Cluster Issues for SQL Server Setup

    During the process of building an Azure lab, I ran into a place where my cluster was not completely validated. I think this happened when a node failed and was re-provisioned, but in any case, when I ran SQL Server setup, the program failed cluster validation and would not install.

    If you find issues, try the command line to start setup. This is what I did. For the first node, I ran setup from the command line like this:

    Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster

    The same GUI popped up, and I was able to set various settings and save my config file.

    For the other nodes, I ran this:

    Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode

    Pretty simple, the platform installed and worked fine. Whatever cluster issue I had must have been transient, at least for my lab purposes.

  • DevOps–Fixing Poorly Named Constraints

    I was building some code the other day and kept getting problems in my deployment for a change. The deployment was having issues, and this came down to this statement.

    ALTER TABLE EventLogger DROP CONSTRAINT [PK__EventLog__5E548648B043C0BC]

    The problem was that this was the constraint on one developer’s workstation, but on another laptop, and in QA/Staging/Production, this constraint didn’t exist.

    When we deploy to other environments, such as QA and Production, we will always see the wrong constraint, as most deployment mechanisms look at the name of the object, not the function. Every upgrade script will typically try to run the above statement and then run an ALTER TABLE ADD CONSTRAINT later to add the PK back.

    If we have the correct name of the constraint in QA, the script will work. However, the name is likely different in each environment, so we need to fix this.

    We can find the name of the PK with this script:

    SELECT 
    A.TABLE_NAME, 
    A.CONSTRAINT_NAME, 
    B.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
       CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = ‘EventLogger’

    If we take the results of this, we can use this to produce a drop script. Here’s one way to do this. We’ll store the name of the constraint in a variable and use the EXEC() statement to execute some dynamic SQL. We then can execute the ADD CONSTRAINT with a new name later in the script.

    DECLARE @s VARCHAR(200)
SELECT @s = A.CONSTRAINT_NAME
FROM 
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, 
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE 
        CONSTRAINT_TYPE = 'PRIMARY KEY' 
     AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME = 'EventLogger'

EXEC('alter table EventLogger drop constraint ' + @s)
GO
/*
Other work
*/
ALTER TABLE dbo.EventLogger ADD CONSTRAINT EventLoggerPK PRIMARY KEY (LogId)

    This is the type of DevOps change that I would release a table at a time, slowly cleaning up the constraint names. This will smooth your process and increase the reliability of your deployments.