Voice of the DBA

Tag: SQLNewBlogger

Checking Your Database Properties–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was reading Grant’s Database Fundamental Series on Database Properties, and it got me thinking. I think this is a good set of knowledge to have, but building on the properties, can you check them programmatically?

You can, and here’s how.

There is a function, DatabasePropertyEX(), that provides you a way to check properties. You can use this with two parameters to check your database. These parameters are:

database name – The name of the database, where you can use dbname() for the current database.

Property name – These are a series of items to check a value for.

As an example, one of the items Grant mentions is the recovery model. I can check that with this code:

SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Recovery’)

In the current database, I get this:

There are many properties I can check, and I can see a nice list here from SQL Prompt, or I can check the BOL page.

As nice as it can be to pop open SSMS and look at dialogs, learn to check things programmatically. Once you can do that, you can start to let the system check and alerts you to changes.

August 3, 2017
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.
July 31, 2017
FizzBuzz–#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’s been a programmer test that was used for awhile to see if a simple program could be constructed. It’s FizzBuzz, after a kids game, and designed to see if someone knows some logical program construction.

The test is to produce a list from 1 to 100 and for multiples of 3, write “Fizz”, multiples of 5 produce “Buzz” and multiples of both produce “FizzBuzz”. This means you get:

It’s a simple test, but I wonder how long it would take you to write it in T-SQL? It took me about 2 minutes.

Give yourself a little quiz today. For a challenge, also produce this in PoSh or some other language. I took about 5 minutes in PoSh, mostly because I had to look up some syntax.

In case you think this doesn’t matter, I agree with Jeff Atwood. I’d like to look at someone’s code before I interview them for development. They should have some public code, maybe something they recommend.

SQLNewBlogger

If you’re looking for a blog topic, show us you can write this code.

July 24, 2017
DevOps Basics–Git log

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.

In a few previous posts I’ve looked at getting going with git, and in this post we continue by looking at how we can get some information about the actions we’ve taken.

If we want to see what has happened in our repo, lots of clients will show a list of changes, but from the command line we use a simple “git log”. When I do this, I see the reverse chronological view of commits.

There are a lot of options for the log command, but there are a few I use often.

Limit Entries

I often use a –n, where n is a number, to limit what’s returned. For example, I’ll use –3 to show the last 3 commits.

I also like the –p option, which will show differences. As you can see here, I added the UserRoles.SQL file, putting in new lines.

At times, I like the –decorate option, which lets me know which branch was affected. This is helpful if I’m moving around on branches and I get confused. That does happen.

There are lots of search options, and I use them at times, but rarely, so I’m usually searching for the documentation to know the dates or patterns. I do look at the –committer= syntax with my name. That lets me find my changes among others.

I also like to keep things small, so using the –pretty=oneline option is handy.

Now I can easily see what I’ve done lately.

There are lots of ways to look at history, and certainly a client makes things easier, but I’d say that you should learn the command line, just in case there’s some issue and your client doesn’t display it properly.

Last thing, when you run git log and end up with a colon prompt, you’re in the less utility (I think, been a long time since Unix). To get out just type:

q

July 19, 2017