Voice of the DBA

Author: way0utwest

  • 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.

  • A Summit Just North of The Big Easy

    I’m off today, traveling down to The Big Easy, New Orleans for a beignet. Well, not just a beignet, but also because I’ll be speaking at SQL Saturday #628 in Baton Rouge tomorrow. It’s easier, and more fun, to fly into New Orleans, so I’ll do that, have a quick snack at Cafe Dumonde and then drive up to Baton Rouge. It’s an easy, relaxing drive, and a nice (albeit warm) part of the country.  Hopefully I’ll arrive in time for a quick jog around University Lake.

    This is the 9th SQL Saturday for Baton Rouge, one of the oldest (they started with #17) and this is my fifth time going. I always enjoy the people and crowds down there and this is one of the largest SQL Saturday events in the country. The schedule has 12 tracks, and with over 500 people coming, they need that many to keep from having every room overcrowded. I have two sessions, and I’m looking forward to seeing quite a few others from other speakers, learning a bit more about SQL Server.

    I think it’s great that this event includes lots of developers from the area and covers a huge variety of topics. People come from all over the area, from Texas to West Florida, and sometimes from Arkansas and Tennesee. There is a wide variety of data related, and a few developer, sessions, as well as a nice career track. There is a huge thirst for knowledge, and for some reason SQL Saturday Baton Rouge on the campus of LSU attracts many people from all over.

    It’s great that we have people in our community that will put together a large event to help others improve their careers and get excited about databases and software. Holding an event like this takes a lot of work, and my hat is off to the organizers. They do a fantastic job and really put on a great event for everyone. I’m glad we have large events like this, and I also am glad we have people putting on smaller events all over the world. We have a fantastic SQL Server community, and it’s always a treat to get accepted to present. I hope to see some of you tomorrow and many more in the future.

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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.

  • Who’s Smarter? Humans or AI Systems?

    I used to watch science fiction movies and imagine we’d have these incredible machines that could interact with humans in amazing ways. I always believed more in the helpful and useful AI in Star Wars than the dangerous systems of movies like The Terminator or The Matrix. I could even imagine a time when we have sentient systems, though I’m not sure they are coming anytime soon.

    I used to assume the Asimov’s Laws of Robotics would be implemented without any issue. After all, doesn’t everyone worry about security? These days I’m more cynical, and I wonder if anyone would actually bother to program limitations in a system. I expect more people would assume we’ll add in the laws later, once we know the other features are working.

    I do think we will get more and more systems like IBM’s Watson, that are designed to handle many tasks. When I read pieces like this one from HBR, I actually get a little worried. Not because machines will destroy humans, but they will greatly impact and change our society, potentially in disruptive ways for society. While we’ve seen issues with manufacturing over the years, there are still humans involved, and we have learned to find other jobs over time.

    The AI revolution, if that’s what’s happening, will occur quicker, and if the HBR piece is correct, will impact many more people, across a shorter period of time. Our economies and workforce may not adapt as quickly, which has a variety of economic implications for all of us, even those of us that might be secure in our positions. If we can teach systems to accomplish tasks at a level even close to what the average human can do, we might find ourselves either out of a job or working with some sort of robot.

    I don’t know how quickly we’ll advance, or even how far we will go, but I do find that the leverage offered by implementing advanced technology is certainly increasing in a way that worries me.

    Steve Jones

    The Voice of the DBA Podcast

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