Voice of the DBA

Tag: administration

  • Drop and Recreate

    Those of you that manage replicated environments have learned to have one thing handy: a script that recreates your replicated publishers, distributors, and subscribers. I was reminded of my past needs for those scripts recently when I saw this post on dropping and recreating all the synonyms for a database.

    It’s easy to depend on backup and restore to recover from issues, but how often do you face problems with an environment that aren’t related to data? If you lose a stored procedure, or have a problem with the configuration of jobs, or principals, can you easily drop and recreate an object? That code is usually tiny, but if the only copy you have is in a backup file, you have to restore a lot of data just to get some code.

    Certainly everyone should keep all this data in version control, and I’d encourage you to be sure that not only development code (tables, views, stored procedures, etc) are kept in there, but also configuration settings, jobs, roles, and the various other things a DBA is responsible for in a production environment.

    However I’d also go one step further and ensure that you have scripts to recreate all aspects of your environment if you need to do it. Many of the comparison tools will let you store a snapshot of database schema items in a folder and then easily help you recreate a script if needed. That covers the database, but for the instance level items, you need to be sure you have an easy way to checkout a copy from version control (you are using version control now, right?) and execute the scripts on a SQL Server. You can use T-SQL, PoSH, or even VBScript, but be sure you have the code handy.

    Do you?

    Steve Jones

    The Voice of the DBA Podcast

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

  • SQL Server Disk Space Emergencies

    One of the things I’ll see happen often with SQL Server instances is that the system will run out of space on a drive. This could be for a variety of reasons, some of which can be prevented, and some cannot. You might have:

    • Don’t delete old backup files
    • Data growth fills the disk over time, usually years
    • tempdb rapid growth that uses all space
    • old import files not deleted over time

    There are other reasons, but I’ve often found that some process will cause an emergency and the SQL Server stops working, or stops backing up database, and administrators are in a panic to free space so the server can continue to function.

    Here’s what I suggest to smooth the way with a series of placeholders and a job.

    Create Placeholders

    First, create a folder on your SQL Server (or really every server) called Placeholder. I’d put it in the root to make it easy to find and standardize on it.

    placeholders3

    In the folder, place a series of files to save space. If you don’t know how to do this, I can show you an easy way. I have 4GB reserved here.

    placeholders4

    Now create a SQL Server Agent job. I might standardize this on every server I have with the same name and path.

    placeholders5

    The job has one step, which is designed to delete one file, each time it’s run.

    Note that I had a slight bug in what I shot above. I had the contig.exe utility in the folder and the first execution of the job deleted that file. Not a big deal in an emergency, because I can run the job again, but I’d make sure that only the place holder files are in this folder on machines.

    Here’s the job. It’s a PoSh type of step.

    placeholders6

    The actual PoSh code is here:

    $fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
    $delete = 1;
    foreach($fileName in $fileEntries)
    {
    if ($delete -eq 1)
    {
    Remove-Item $fileName
    $delete = 0;
    }
    }

    When I run this, each time I run it, it’s just a single click or sp_start_job call.

    placeholders7

    After it runs, I have 1GB more free space. If I need more, run it again.

    placeholders8

    However, once you clear your low space condition, I’d be sure I put the placeholders back.

    For the next emergency.

  • Creating Placeholder Files

    I recently wrote about placeholders for disk space. While you can use any file, like large images, video, etc., I’ve found a really simple, easy way to build these files on Windows.

    Contig.exe

    There’s a sysinternals utility called Contig. You can download it from Microsoft and then unzip it on your system. It’s a command line utility, so you can use the /? to get the parameters from it, but here’s what you need to do: use the -n parameter, with a filename and a size.

    That simple. Here’s a sample call:

    placeholders1

    And in text:

    contig -n Hold1.place 1073741824

    That creates a 1GB file (roughly) on my system. I can then copy this file as many times as I want to save space.

    I use folder in the root called placeholder. I put one file in there and then copy it a few times to reserve space.

    Why that size? 1024 * 1024 * 1024.

  • Consolidating Again and Again and Again

    This editorial was originally published on November 12, 2009. It is being re-run as Steve is on holiday. 

    In reading about virtualization, one of the main things that drives its acceptance is cost. It’s the reduction in actual physical resources needed to run multiple servers that makes it popular with many IT departments. It doesn’t help with administrative costs, at least not the costs of the system administrators because you still have the same number of servers to manage. There are just less physical servers to buy, less power needed, less cooling, and probably other savings if you virtualize.

    Virtualization is really a one-time cost savings. Once you’ve virtualized a server, you can’t do I again, so there are no repeat savings from this event. Is consolidation the same? After all, the main driving force for that process is also cost, and once you’ve consolidated a server you can’t do it again.

    Or can you?

    There’s a great white paper by Allan Hirt that talks about the process of consolidation, and I’d urge you to read it before beginning a consolidation project. However I don’t know that consolidation is, or should be, a large, one-time project.

    If I consolidate 3 or 5 SQL Server instances onto one machine, I have a choice of how I can do this. It can be by moving databases to a new instance, having multiple instances, or even using virtual machines. But is that the end? It’s not because I can add more databases later, or I could even move a database that is receiving a heavy load back to its own server and find other instances to consolidate onto this one.

    Consolidation, unlike  virtualization ought to be something you periodically examine in your environment. On a regular basis review your baseline for all instances and see if it makes sense to perform some consolidation. Server sprawl can creep up on you, but with a little management and the multi-instance nature of SQL Server, you can keep it under control.

    And if you don’t have a baseline to examine, consider setting one up.

    Steve Jones