SQL Server – Restore Master Database

I have been working toward a SQL Server certification (70-432). This has resulted in me getting quite a good knowledge of how things work. Here is something interesting I have learned:

Steps (this only works if you have a good backup of Master)

1. Stop the SQL Server instance.

2. From a command prompt (must be running the command prompt as administrator), go into the Binn directory of the SQL Server instance and run “sqlservr -m”. This will start up a special single user mode.

3. Start another command prompt as administrator. Run sqlcmd and then perform the restore, something to the effect of: restore database master from backupdevice if you are using preprogrammed backup devices (something that is recommended).

4. Start SQL Server up normally.

5. Restore MSDB, then Model if necessary.

If you don’t have a valid backup, shame on you! But you can run setup again which will rebuild Master but you have to reattach all databases.

 

Powershell – Top Command

For those of you who love the top command in Linux…

 

function Get-Top{

#######################################

##Get-Top

##

##Written By: John Glasgow

#######################################

<#

.SYNTAX

Get-Top [-delay interval] [-pid pid_number]

 

.SYNOPSIS

Emulates the top command from Linux/Unix.

 

.EXAMPLE

To set delay of 5 seconds.

Get-Top -delay 5

 

.EXAMPLE

To watch a particular process.

Get-Top -pid 4408

#>

$delay = 2

$proc = -1

 

for($i = 0; $i -lt $args.Count; $i += 1){

if( $args[$i] -ilike “-d*” ){

$i++

$delay = $args[$i]

}

if( $args[$i] -ilike “-p*” ){

$i++

$proc = $args[$i]

}

if( $args[$i] -ilike “-q*” ){

$i++

$delay = 0

}

}

 

while ($true){

Clear-Host

if($proc -gt 0){Get-Process | Sort-Object -Descending cpu | Where-Object{ $_.Id -eq $proc } | Format-Table}

else{Get-Process | Sort-Object -Descending cpu | Select-Object -First 20 | Format-Table}

Start-Sleep -Seconds $delay

}

}

Set-Alias top Get-Top

SQL Server Performance Tips For The Programmer

Through the years I have amassed some tips and trick to make SQL Server run more efficiently. For right now, I would like to tell you about some common mistakes that developers make, I know I have made some of these!

Tip 1 – It’s all about the cached execution plan!

Ok so SQL Server has execution plans, big deal,  what does this mean to you? Here’s what you need to know, for every query you execute SQL Server determines the best possible way of getting data from the database files. For some very complex queries, the possibilities could be well into the millions! To save time executing repeated queries, it stores the best possible way of executing these queries in a cache.

Those are the details but here is how to take advantage of this. Store the queries you are using in Stored Procedures. This will help SQL Server store the execution plan. There is one secret to this, assign the parameters to local variables within the Stored Procedures!

Tip 2 – Reduce Network Traffic!

You may have heard that Select * is a bad thing well turns out that there a number of reasons. The big one extra packets are getting sent from the SQL Server. Secondly it also increases disk I/O on the server. In SQL Server, less is more. Also if you SET NOCOUNT ON, the server won’t send messages back to your program throughout it’s execution so traffic is further reduced.

Tip 3 – Set the Initial Database Size Accordingly!

If you set it too small, as the database expands, the data file(s) will grow in size but not necessarily together. These files will become fragmented on the disk. This results in more disk operations, which unfortunately result in more CPU usage as well as more disk operations. Sizing it appropriately will prevent this fragmentation from occurring for the most part. If you feel that you databases are too fragmented you could rebuild the databases or detach them from the SQL Server engine and run a disk defrag on the disk where the database files are stored.

There is another aspect of this. Set the growth increments large enough. If the increments are too small you will create more fragmentation for the database.

Tip 4 – Use Indexes!

Use indexes where your “where” clauses point to.  When indexes are not used the query has to scan every record in the table to find the results. If it is indexed, SQL Server scans through the index (containing just the field(s) that you need). Which results in less disk I/O and much faster results. Don’t do this on fields that are constantly written over because because writing to the field has two writes (one in the table and one in the index).  Also constant changes can lead to index fragmentation, which will slow down queries where many rows are returned.

That’s it for now but more will be posted in the future.  If you found this useful, please subscribe.

SQL Server Maintenance Plans vs. Powershell Scripts

I have been working on maintenance plans for some time now and while they are very powerful I am starting to wonder if Powershell might be a better platform to perform my server maintenance. I am starting to lean towards Powershell and here is why. Keep in mind that this is up for debate and I am sure this could stir up some controversy. 

With Powershell it is easier to get access to the OS.  With maintenance plans it gets a little more complicated. Maintenance plans are built for SQL DBAs, not for the person who owns the whole box. Sometimes DBAs have limited access to the server, so for those people Powershell isn’t the best choice. Also if you don’t have a programming background, Powershell might not be for you although you might want to consider it since there are some very simple scripts that can get some serious information to you very quickly, but I digress.

Scripts are highly portable. SSIS packages are as well but you can’t edit them easily without BIDS. Notepad is all you need for Powershell.

I am going to keep you all posted as I am making changes to my scripts.  Once I make a really good script I will post it.

Visual Studio 2005 Tip of the Day – Class Diagram

I just have a really brief entry for today. In my continued efforts of self-improvement, I have stumbled upon a rather interesting feature in VS that I never knew (or bothered to look at). For some developers, they need to visualize what they are doing in one way or another. Well the good people at Microsoft have developed a GUI for creating classes called Class Diagram.

Right mouse click on your solution in the Solution Explorer and select Class Diagram. In there you will discover that you will be able to create the shells of your classes very simply and quickly. You can add methods, background fields, properties to new or existing classes you have created. In each of these you can include parameters and comments.

Once you include some code in the classes, you can even test the results with Test Bench. Basically you can enter values into the parameters of functions and see the output, without even writing the old main function and running through test case after test case. You still might want to do that anyway, so you can repeat your tests.

Well that is it for now!

Downfalls of IT Consultants

IT Consultants – My Story

So for the past couple months I and my whole department have been rewriting code from IT Consultants. We were never “consulted” to even know what the consultants were working on. The terms of the contracts were vague and the software that was developed was incomplete. Lucky us!

There are a lot of lessons I have learned that could be very useful to both IT Consultants and IT Technicians. I am going to share some with you.

  1. In the contract, always list not only the expected outcomes but also things that will not be included in the final product.
  2. An extension of rule one; clear, concise criteria is required from the client; if the client does not give this to the consultant, then the consultant needs to ask each question he/she feels pertinent several different ways. This will flush out hidden gems that the client might not thought of earlier.
  3. INFORM ANYONE AND EVERYONE of developments, including stumbling blocks and progress. This should be done, depending on the time-frame of the project, on a daily or weekly basis.
  4. Get everything in writing. You will be amazed how many agreements and understandings are misinterpreted or forgotten with no written proof.
  5. Cut down on the meetings, less meetings more email results in better communication.

Thanks again to my followers, if you find my site useful, feel free to tell your friends!

Things I have learned today: Word and Access Tricks!

Business meeting I have come across some things today that I need to share, because I have not found them many places! My main purpose on here is to be helpful to others as well as maintain a digital image of my brain; simply because, like many people in IT, I have to address many different things in any given day.

So on with the tricks!

Access:

Turning Off Stubborn Warning Messages

Today I was helping a colleague with a macro they were creating. The person turned the warnings off and were still getting the warning message about appending records. Here is how you get around warning messages that will not turn of the default way.  Use the SendKeys Action immediately BEFORE the append query (or whichever action is generating the warning) you need to hide. Be sure to set it’s “Wait” attribute to “Off”. The enter key is represented as a tilde “~”, which will automatically post on the message box. If you need to tab to the the next button, you can use {TAB}.

Open Access Database on a Network

Another thing I learned today is that the Window XP will not allow you to open an Access database file when it is on a network (non-Active Directory anyways). Also the file must not be open by anyone else.

Word:

Dynamically Loading Images Into Mail Merge

If you are doing a mail merge and need an image to change on each record here are some tips. Paths have to include an extra backslash so \’s are now \\. The format is {INCLUDEPICTURE {MERGEFIELD ImagePathNameHere}}. Once you generate the output to a different file, select all (Ctrl + A) and then press F9 to have all the pictures load. If you this incorrectly one of two things happen: you see the same image for all the records; if you see this, chances are you did not select all the records and refresh them. If all the images appear like broken links, you did not include the extra backslashes in the file path.

If you have any questions about this or anything else, I will do my best to answer you. Just leave me a comment!