Voice of the DBA

Tag: sql server

  • Monitoring for Non Existent Events

    I was catching up on work recently, reading the third installment of The 5 Worst Days in a DBA’s life, starring The DBA Team. Someone had asked me if I enjoyed having Paul Randal (b | t) of SQLskills join them team. The piece had been edited and published while I was gone, and I hadn’t had a chance to immerse myself in the adventure. I was anxious too read how Paul helped save the day.

    It was a fun read, but one quote in the piece struck me. “A job that runs long or doesn’t run at all can sting just as bad as one that fails.” That’s a quote from my character showcasing a situation that few people actually think about. However jobs that don’t run or don’t finish are situations that DBAs should be monitoring for.

    So many of us adopt a set-it-and-forget-it mentality with our jobs. We assume that things will work, or fail, as we set them up. However it’s easy to forget that there are other states we might find ourselves or our systems in that can cause issues.

    Monitoring is critical to any well run system, but monitoring needs to be set up well. If we require that certain jobs run, we need to not only check for success or failure, but if the job has actually run and completed. It’s easy to accidentally disable the wrong job and not notice. It’s also entirely possible that a job gets stuck and doesn’t complete.

    If you’re not watching for those other states, you might find yourself in a situation where you don’t have backups and your job is on the line. However you probably won’t have The DBA Team to call on.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • T-SQL–Converting Seconds to Time

    I was working on a small piece of code the other day that was calculating the seconds for an event. I had a function that returned me the seconds as an integer. That’s good, but I wanted to get that value back in minutes and seconds. The scale wasn’t so large as to worry about hours, or days (I hope).

    In any case, I could certainly do some math. Takes seconds, divide by 60 to get minutes, and then take the remainder and add that as seconds, concatenate, convert to time. Crazy.

    There’s an easier way using CONVERT.

    DECLARE @s INT
SELECT
    @s = 325
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

    I can just add the seconds to the 0 time, which is midnight, and I’ll get the time back in the right datatype.

    This code gives me 5:25, which is correct. Five minutes and 25 seconds.

    If I increase the numbers, say into hours, I can take 4325 like this:

    DECLARE @s INT
SELECT
    @s = 4325
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

    And get this;

    time

  • Attaching All Databases with PowerShell–Refactoring out Write-Host

    Someone posted a note to me on Twitter that noted that Write-Host is not recommended for your scripts. I checked the link to an MSDN blog on Write-Host Considered Harmful, and it made some sense. Basically it says that since Write-Host always goes to the console, any output sent through Write-Host can’t be consumed in a pipeline by other PoSh commandlets or processes.

    At first I thought, what does that have to do with my script? I’m really just noting status information. However, the more I thought about it, the more I realized that it’s a minor change, and who knows? Maybe I’ll chain this in some other process, or more importantly, maybe someone else will.

    Today I popped open the script in the PowerShell ISE and did this:

    posh_a

    That’s an easy fix. Just write the output to the pipeline, and if there’s nothing consuming output, I get it on the screen.

    I also refactored a bit more. I added a “Debug x:” line to each Write-Output command, with x replaced by the appropriate debug level I’d checked for. This way I know what debugging output is being returned to the calling screen.

    I also found a few lines that were just output, using “Attaching as…” code. I replaced those with Write-Output.

  • Attaching All Databases with PowerShell–Attaching Missing Databases

    I wrote a PowerShell script recently to actually accomplish a task I that I needed. What’s more, this was the first time I thought that Powershell might prove more useful than other methods. This series looks at my script, and this part examines the first part that I wrote.

    In the last post, I had a script that matched up databases with the mdf files in a folder. That’s good, but that’s actually the opposite if what I want to return. I want to return the files that aren’t matched up.

    To do this, I add a few variable to my script, re-setting it for each loop of a file in my folder. I do this inside my test for the extension (shown), so I’m not executing this if I don’t need to.

    if ($file.Extension -eq ‘.mdf’)
    {
    if ($debug -eq 1)
    {
    write-host “MDF Files: ”  $file.name
    #end debug
    }

        # Reset our flag for each file
    $found = 0

    The last two lines are what I added. We set this to 0, or false, because we assume we haven’t found a database that matches this file by default. That way when we do find a file, we can trip the flag.

    The next step is to set that tripwire. Inside the loop, where we check for the file matching a database file, we add a reset of this flag.

    if ($file.FullName -eq $dbfile.FileName)
    {
    $found = 1

    Since we will check this file against every database, this logic allows the flag to be set, and it doesn’t get reset for this physical file. Any databases we check that don’t match this file won’t reach this point.

    The last step is the other end of the file loop. After we’ve left the database loop, we check to see if our file was found. If it’s not ($found is still 0), then we can do work. I’ve included the end of the foreach and the end of the mdf test for reference.

        # end foreach
    }

    if ($found -eq 0)
    {
    # attach this file
    if ($debug = 7)
    {
    Write-Host $file.Name “not found”
    #end if debug
    }

    #end if found = 0
    }

    There’s a comment placeholder in there to show the action we need to take, and there’s a debug to print things. Let’s set debug to 7 and run this.

    attach_n

    That’s bad. Certainly I noted that my test databases (db1, db2, db3) were detected. These I detached manually to play in a test environment. However why are my system objects there?

    I looked through the code and realized it’s because I had this line:

    | Where-Object {$_.ISSystemObject -eq $false}

    I’m ignoring system objects in my scan, but I don’t want to do that. I actually have these databases, so I removed that Where-Object call. Then I get this:

    attach_o

    That’s what I want to see. Now I have a list of files to attach, let’s work inside SQL Server.

    Attaching Database Files

    I have my .mdf files, but I also want my ldf files. I know these fit a pattern from looking at the files. Since I haven’t changed anything from the defaults, I can exploit that pattern. If you change things on your systems, make sure you keep a pattern.

    I did some googling, and found that the AttachDatabase method takes a few parameters. However one of them is a StringCollection so I need to create that.

    $dbfiles = New-Object System.Collections.Specialized.StringCollection

    Once I have this variable, I can then add my mdf file. The FullName property includes the path, and I call the Add() method.

    $dbfiles.Add($file.FullName) | Out-Null

    Now, I need a few more things. I need the log file and the database name. The database name is first, mostly because I thought of it first.  The BaseName is just the name of the file. I found that out by using my debug clause and writing out the various properties until I got the one I wanted.

    #get database name
    $dbname = $file.BaseName

    The next step is to get my log file. I can use the basename, along with my folder path, and include my log pattern. Once I build this file, I add it to the collection.

    # get log file, assuming same basename as mdf
    $logfile = $folder + “\” + $file.BaseName + “_log.ldf”
    $dbfiles.Add($logfile) | Out-Null

    I added a logging item, which will always run, to my script as this is output I’d want to see.

    “Attaching as database (” + $dbname + “) from mdf (” + $file.FullName + “) and ldf (” + $logfile + “)”

    Now we attach the database. I’ve seen some code that had other parameters, but this worked well for me. I captured this in a try..catch block, mostly because it failed early on and this allowed me to see the whole exception. Some of what was shown from PoSh was truncated, so this helped me to realize I needed to add the “_log” to my filename.

    try
    {
    $server.AttachDatabase($dbname, $dbfiles)
    #end try
    }
    catch
    {
    Write-Host $_.exception;
    #end catch
    }

    That’s it. I run it and get some output.

    attach_q

    A slight issue in my debug code. I had $debug = 7 and needed $debug –eq 7. Still, it records each database as being attached, with the catch block not executing. If I check SSMS.

    attach_p

    My databases are back.

    It’s not a perfect script, and there are probably improvements, but it does get me my databases back easily.