Maintenance

Msg 2767, Level 16, State 1, Line 2 Could not locate statistics

The other day when tuning some indexes on a project I was being my usual lazy self and I dragged/dropped the column name into the query window. After executing DBCC SHOW_STATISTICS I was confronted with the following error message.

could not locate statistics

Wait, what? Why am I getting this error? I know there are statistics. So why do you get this error? I am not sure to be completely honest but I can tell you there is a very simple fix. Remove the brackets at the beginning and end of the column.

remove_brackets

Then execute DBCC SHOW_STATISTICS and you should be fine.

results

Hopefully the helps reduce some of the stress you have related to the indexes you are tuning.

Cheers!

Advertisements

Finding SQL Server Analysis Services listening port

I have not worked with SQL Server Analysis Server very much. That is apparently changing as of now and I am still undecided on how I feel about that. Don’t get me wrong. I really enjoy working in SQL and will keep an open mind as my learning path continues. My heart however is with the main SQL engine.

During the server setup and configuration we record basic information on the server. One of the pieces of information I needed was what port Analysis Server listens on. I had no idea. So how do we determine what port?

Search for ASConfiguration

There is a chance you may find SSAS configuration by searching for msmdredir.ini.

search

You may also try looking in this directory

emptyfolder

If you can’t find the ini file anywhere you may find it via the Process ID and netstat.

Finding Process ID

We need to start with finding the process ID that SSAS is using.

configmanager

Using the Configuration Manager we can find what ID we need to look for.

Now open a command prompt. Start > Run > cmd

open_cmd

Now with the following commands we can use netstat to find what port SSAS is listening on.

netstat_host

Reviewing the output we find what we are looking for. The port is 2383.

netstat_host_result

Using the following commands we can output the information to a text file for whatever purposes you require.

netstat_output

And the output should look something like this.

netstat_output_result

Conclusion

As we can see, there are a few different ways to track down what port SSAS is listening on. The default port for SSAS is 2383. As most of you know, the port this component listens on can be changed. The evidence in the screen shot confirms that. Knowing how to find the information you seek as it relates to SQL Server and it components is an important part of a DBAs job.

Cheers!

Routine Maintenance Setup from SQL Scripts

If I do the same thing more than a few times I look for ways to automate them. Yes, I fully admit I am lazy. (Don’t tell my wife I said that). Let me clarify. If I can figure out a way to make my job and life easier I am all over it. This is a post is about one of the things I have taken steps towards automating, not fully mind you but it is better than manually creating agent jobs one at a time.

This is one of the steps I have created as part of our Standards for SQL Server Installations. After SQL has been installed we run this process as part of our server configuration and is used to install a few stored procedures and setup routine maintenance tasks. If these were done manually could take an average of 5 or more minutes per task. Now that may not seem like a lot of time but let us image you have 3 servers to setup and you have the following 7 maintenance jobs to create for each server. System database backup, User database backup, User database transaction log backup, Backup maintenance (removing old backup files), index defragging (as needed), database integrity checks, and maintenance history cleanup. That is easy math right? 5×7=35. 35 minutes not counting any interruptions you might have while creating the jobs. Now, compare that with, for argument sake 30 seconds. It will actually take less to run but we can add in the time to copy the files to the server, open the command prompt and call the file to start the install J so 35 x 3 = 105. That is more than an hour and a half. I don’t know about you but I can find a lot more productive things to do in an hour and a half.

What We Are Setting Up

If you look at the list the files are mostly self-explanatory. These may be named a little funky or different than you name or will name your files but this is how I do it, it works for me and that is all that matters. Moving on, steps 01 through 04 are core functionality. We are working with temp DB adding and moving data files to the correct drive, in our case “T:\”. We then enable database mail, create operators, and server alerts. Step 5 is a daily script that has some basic home grown monitoring information like service SQL version and pack level, when the databases were last backed up, drive space, system logins and permission level, and any error log information. Step 6 monitors failed login attempts and sends an email at certain intervals that can be adjusted. Step 7 is a stored procedure is database integrity checks. Step 12 disables the SA account. Step 21 is a script revoking permissions on some extended stored procedures and removes connect from the Guest account. Step 30 is my personal script that creates a stored procedure or two and sends that information to a central database I use to track my project databases.

The Meat Of The Process

If you were following along closely I mentioned all the scripts in the image except one. CreateSQLJobs.sql this script is the workhorse of the entire process. This one script calls all the others as the example shows. Want a new script added to the run? Create it, drop it in the correct folder add it to this script and boom! You are done. Want to remove on for this run? Just comment out the script you don’t want run. It is as simple as that. There are probably many different ways to accomplish this same thing, but this works for me.

Kicking Off The Install

Now, how do we call the CreateSQLJob script?  Fortunately that is also very simple. (anyone catching onto the theme here?) Just open up a command prompt, navigate to the folder root and execute the following command.

Conclusion

I created this process to make the mundane work of manually creating routine maintenance jobs easier. If you like dragging and dropping sub tasks onto a design window and creating jobs manually that is fine by me. If you want to be more efficient when setting up and configuring SQL installs and you want to work smarter not harder then give this a try. Hopefully this will make your next install that much quicker.

Cheers!

Central Management Server – Setup

I am on a team supporting over 150 servers with varying numbers of instances and database ranging in size from several MBs up to a few in the TBs size now. We constantly add new projects and servers and managing all of them with be getting a little bit easier in the near future.

I am in the process of standing up a Central Management Server (CMS). How will this make my job easier? I am glad you asked. Using a CMS will allow me “centrally” manage all the servers in our environment. I can execute T-SQL code against all servers from one query window. Or I could use Policy Based Management (PBM) to evaluate all the servers to see if they conform to the policies we establish in our environment. I will be posting more about that in the future, but let me give you an example.

Let’s say you have a policy in your shop that all databases are backed up weekly. (I know but humor me for a minute). If you write one policy you can run it against all servers to see if they have been backed up in the last 7 days. If one or more violates the policy you will know away. Nice huh? Can you imagine having to manually check every server every week, let alone every day to see if all databases were backed up?

Now that we have an idea of a few things we can do with a CMS let’s get started with how we go about setting up and using one.

First open SQL Server Management Studio. You can do that by going to Start > Programs > SQL Server  2008 > Then looking for SQL Server Management Studio.

SSMS

Optionally use the Windows key ( )and type “R” for run. Then type “ssms” and hit enter.

SSMS optional shortcut

Next we need to find the Registered Servers tab. It may be located at the bottom on the Object Explored tab or you can find in by going to the “View” menu across the top of SSMS and clicking on Registered Servers link (Ctrl + Alt + G)

View Registered Server tab

Now expand the Database Engine tree by clicking on the “+” (plus sign)

Next we want to right click on the Central Management Servers and choose Register Central Management server…

That will open window like this one.

Choose the servers from the drop down list. Since Windows Authentication is the only option you must have a domain controller to authenticate against. After choosing the server, click the button labeled “Test”. This will test the connection. After successfully connecting you will see this response.

Now that we have our CMS setup we need to add servers to it so we can manage them. You will do that by right clicking on the CMS you just created. Then choose “New Server Registration…”

Following the same process as before when adding the CMS pick the server from the drop down list and test the connection. After confirming and receiving positive confirmation save the server and you now have a server registered to your CMS.

Now that you have registered a server to use your new CMS to manage. You have several options. 1) You can run T-SQL code against all the registered servers. (more on that in a future post) 2)  You can evaluate Policies (more on PBM in a future post). 3) You could import existing Policies. 4) Create a Server Group (I have my servers grouped by project). 5) Register additional servers.

T-SQL query

Optionally, right clicking on the new registered server you are presented with the same options as above plus some additional ones.

Tasks: After creating groups if a server is move from one project to another you can move the register server simply by choosing the move option and selecting the new folder to more the server too. (Folder must already exist)

Service Control: Allows you to Start, Stop, Pause, Resume, and Restart the database service on the remote server.

As you can see, using a CMS to manage one or multiple servers can save you countless hours of work.

In the next post I will show you how to add Policy Based Management to the server and evaluating the new policies against your servers.

Cheers!

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

When reviewing my daily monitoring information this morning I noticed I did not get any emails from three different servers for one of my projects. You see, we have scripts we use to look at backups and when they were last performed, we look at job failure, we look at, well you get the picture. We monitor our servers.

I started to investigate the issue. Before I looked at the job history to see if there were any errors I looked on the server to see if the backs had actually been done, regardless of what the results I was reviewing. Yes. The databases and logs were/are being done.

When I looked at the history for the monitor Job the following error message is what I saw.

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050).
The step failed.

I also noticed the job had been failing the last two days. (I was off the last 2 days so I didn’t notice it before today, just in case you were asking why it took so long to discover)

I ran the code in the query window and it executed correctly. Hmm. What has changed since the errors started? “Light bulb!”

I added domain accounts last week to run the services. OK, what could be wrong then?
All the services are running. No errors there. What else? Looking at the error message again the job was executed under the domain account I added, so that is right. How about permissions? Ah ha! Permissions. I forgot to add the SQL Agent domain account to SQL Server and assign the proper permissions. Fixing that by adding the domain account to the server and granting admin privilages worked.

So, hopefully, you learned a valuable lesson from my mistake.

Cheers!

Nightmare on your server

I recently discoverd a nightmare on one of the servers I have taken over from another DBA in our group. The find was really by accident. Even though, I did so some preliminary documentation on the server.

You see, currently we use maintenance plans when setting up our servers. I actually have this on my list to fix. That will be another post and I am trying to put together a presentation for my first talk at our local user group, but I am getting off the subject if this post. 

As I mentioned we use maintenance plans here. We also have an installation document for installs. I took over a project from the DBA that created the documentation, so I figured  this person followed their own document. Silly rabbit tricks are for kids. 

The maintenance plans are for system and user database. Each plan has we have 4 differnet subplans. One for the full backup, one transaction logs, one to check database integrity, and one to rebuild the indexes. What I discovered was this DBA included the last two steps into one.

Rebuild indexes, update statistics, and (wait for it, wait for it) shrink the database. Oh, no not that. It never occurred to me when seeing all the angels walking the halls around here without wings. If you don’t know what I mean, then you need to get on Twitter. But again, I am getting off the subject.

I found this little tidbit as a result of an error in the logs. I looked back through the history of the jobs and *fail*, *fail*, success, *fail*… So I removed the old failing subplan and created two seperate maintenance plans. Once for Datatbase Integrity Checks and one for Rebuilding Indexes. This was several weeks ago now and guess what? No more errors in the logs.

Let this be a lesson to you. When taking over servers from others go through it with a fine toothed comb and make sure things are setup the way you want them setup. Keeping in mind project requirements and SLA agreements.

Hope this helps. Let me know if you have any questions and check back. I’ll have some addition posts about replacing the maintenance plans.

Cheers!