Voice of the DBA

Tag: sql server

  • More Evolution, More Complexity

    I'm not sure, but we might have more knobs and dials in SQL Server than this in 2012.
    I’m not sure, but we might have more knobs and dials in SQL Server than this in 2012.

    Recently I had the chance to learn about some of the changes coming in the SQL Server platform in the next few years. At the MVP Summit we had the chance to talk with people that showed us features and changes coming in the next version of SQL Server as well as some ideas and thoughts about what might come after that. I greatly enjoy the latter sessions, since the ideas and goals of brilliant architects are always interesting to listen to.

    As I heard about changes and additions, it occurred to me that while some of the features and functions become easier for people managing platforms, others become more difficult. The tuning decisions we might make with regards to resources become more complex. We have new knobs to turn, and more decisions to make on how to best balance the hardware available on our servers.

    I’ve known a lot of people over the years that have feared for the evolution of software, which eliminates some of the “easier” tasks that administrators and even developers have had to manage in the past. There’s no denying that the days of making a career of babysitting boxes, changing tapes, scanning logs manually, and more are likely over. Those menial, easy-to-automate tasks will become the domain of software at some point. We still might perform them, but it will be rarely.

    As platforms advance, however, they do not become more autonomous. New decisions and management tasks are needed. That means new skills for those of us that want to remain in information technology. It’s means a little more work for us, more studying and more time. However your career will span decades, so plan your learning along those lines. Don’t try to learn everything this year. Pace yourself and plan on regular learning across the next five years and you might be amazed what you accomplish.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Attaching an MDF with no Log (AdventureWorks)

    Attaching an MDF file without an LDF file can be a little tricky. I had to go through this recently when I downloaded the AdventureWorks2008R2 database, which was just an MDF file. I know I’ve done this before, but I had to stop and look things up, which means that I should blog about it (hint, hint).

    How do I attach a new database?

    If I go into SSMS, I can pick the “Attach” dialog by right clicking the “databases” folder.

    attach1

    Once I pick my MDF, I see the dialog populated.

    attach2

    However if I click “OK”, I get an error.

    <mini-rant>

    Don’t click OK for precisely this reason. If there’s an error, it can be hard to figure out what happened, and the error handling in SSMS isn’t the greatest. I’ve even seen people click “Cancel” without realizing there was an error”.

    If you use the GUI, please click the “script” button instead and cancel out of the dialog.

    </mini-rant>

    attach3'

    The error occurred because the log file didn’t exist. If you select the log file in the lower dialog, and click Remove, you can use the GUI here.

    However removing the log file and pressing the click button gives me this code:

    USE [master]
GO
CREATE DATABASE [AdventureWorks2008R2] ON 
( FILENAME = N'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf' )
 FOR ATTACH
GO

    .csharpcode, .csharpcode pre
    {
    font-size: small;
    color: black;
    font-family: consolas, “Courier New”, courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    }
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    {
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    }
    .csharpcode .lnum { color: #606060; }No mention of a log file. If I run this I’ll get an error in the messages pane, but the database will attach:

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.DENALIRCO\MSSQL\DATA\AdventureWorks2008R2_log.ldf" may be incorrect.

    New log file ‘D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_2_log.ldf’ was created.

    Converting database ‘AdventureWorks2008R2_2’ from version 705 to the current version 706.

    Database ‘AdventureWorks2008R2_2’ running the upgrade step from version 705 to version 706.

    That works well, but what about the rebuild log option?

    If you look in the CREATE DATABASE topic, you’ll find the option ATTACH_REBUILD_LOG. What if I use this?

    create database AdventureWorks2008R2_2
 on ( filename = 'D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data2.mdf')
  for attach_rebuild_log

    .csharpcode, .csharpcode pre
    {
    font-size: small;
    color: black;
    font-family: consolas, “Courier New”, courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    }
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    {
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    }
    .csharpcode .lnum { color: #606060; }If I run this, I get essentially the same messages:

    File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.DENALIRCO\MSSQL\DATA\AdventureWorks2008R2_log.ldf" may be incorrect.

    New log file ‘D:\SQLServer\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_2_log.ldf’ was created.

    Converting database ‘AdventureWorks2008R2_2’ from version 705 to the current version 706.

    Database ‘AdventureWorks2008R2_2’ running the upgrade step from version 705 to version 706.

    I’m not sure why this option exists, or if it’s been deprecated in 2012 since the functionality appears to exist in FOR ATTACH. The documentation mentions that if the log file is not there, it will rebuild. I guess this is included in case you wish to force a rebuild, which is a good thing.

    In any case, if you run into issues attaching a database with the GUI, this should help you.

  • Fix the Little Things

    2188

    I thought this was a great blog post and it got me to read, even though it’s a topic I know about, have written about, and didn’t think the post would reveal anything amazing: Finding Fragmentation Of An Index And Fixing It. It’s no-nonsense and it lets me know right away that this author intends to help me.

    But I’m somewhat amazed how many fragmentation questions, posts, articles, and more that I still see on a regular basis. Shouldn’t this be a low level feature of SQL Server that just works? In 2008, or I guess SQL 11 now, should there be any reason for a DBA to monitor this and write custom code to ensure that it’s fixed on a regular basis? Should this not be something built into the system?

    I dream about the day of right clicking a table and choosing “defragment.” This wonderful built in utility would then return my table to it’s initial fill factor and clean up all the allocations of extents to ensure that they were contiguous. It would have options I could pull up that would set the minimum and maximum levels of fragmentation I accept, based on server defaults of course, and it would ensure that my tables were kept within those limits. Sure it would be a background process, it would consume CPU cycles and require a good amount of disk space, but those things are relatively cheap.

    Especially when compared with the cost of time for a DBA to manage this process. Even a few hours a year is too much time wasted by DBAs on a task like this.

    It’s not sexy, but there are a lot of DBAs out there that would really appreciate it. As much as I know large sales dominate the market and this isn’t something that reps at Microsoft can point to, it’s the little guys, those DBAs in lots of 1 and 2 person shops, 10 and 20 instance companies, that make the recommendation to upgrade. And if Microsoft spent some time cleaning up little features, making things work better, I bet there would be a lot of recommendations to change versions.

    And, Microsoft, if you deliver small things that work well, along with the big, sexy features that flake or that we don’t understand, you might be surprised how many of us would be looking to upgrade before Service Pack 1.

  • Off to Redmond

    Actually I’m off to Bellevue this week for the annual MVP Summit that Microsoft puts on each year. This is a conference, with over 1000 MVPs attending where we get the chance to hear presentations and interact with the developers in our particular areas. I’ll be in downtown Bellevue for a few days this week, hopefully learning more about how SQL Server works and getting an idea on the directions the product will take in the next year or two. With all the attention on Azure, and work continuing on the next boxed version of SQL Server, I’m looking forward to many of the sessions.]

    As with most of the MVP events I attend, I can’t talk about much that goes on. We are under strict NDAs and until Microsoft decides to release something publicly, I won’t be writing.

    However I will be getting prepared to talk about things when they come out, along with many other MVPs. Hopefully it won’t be too long before we can disclose information to you that will make your jobs easier.