Sybase

Sybase Instance Scare

Monday morning my company was putting in a new UPS at our DR facility. This is great but it means that all database instances that are running at that location are coming down as power to the entire site was down for 2 hours. No big deal right, I got a list of the instances and shut them all down properly before the host machines were shutdown. A couple hours later I get a call saying that all the power is back on and that all my hosts are ready as well. I login and I start to run my startup scripts for all my Sybase instances. I run into two servers that are not starting up and this is what I find in the log files.
The configuration area in master device appears to be corrupt.
NOT COOL… I know that I have backups so that is OK but Master really in two instances… I dig around a bit more and I find that the two instances that are having issues run on the same physical host but are in Solaris Containers that use Veritas Disk mounts. I google the error to see if it may be disk related and not corruption in Master. I found it.

I called my storage guy and he looked into it. Called me about 30 minutes later and asked me to try again. WOW everything came up and I was a happy camper. Sure made for a long morning though and also made me realize the benefit to practicing DR situations. I think I will work with my team to have outages on out Test instances just so we can rebuild Master. It will be good to know and be practiced up on if it is ever not the disk.

Update sysindexes table in Sybase 12.5.4

I am moving a Sybase instance from one host to another. I have done all the installation and patching and I have loaded all the databases with little to no issues. I ran into a big issue when attempting to change to indexes in sysservers to allow duplicate rows. I had to go and find out what the binary value for the setting that I wanted was and then find out what column held that value. I determined, from looking at my current instance, that I needed the Status colunm to be changed to 64. I then verified this by looking here at the definitions for the table.

 http://infocenter.sybase.com/help/index….

I attempted droping the indexes so that I could just recreate them but you are not able to use Drop Index when working with system tables. That is when I decided to find the value and the column to update. So then I ran a query:

Select name, status from sysindexes

where name = ‘csysservers’

and status = 146

go

This returned on row for me and I thought now I can run the update

update sysindexes

set status = 40

where name = ‘csysservers’

and status = 146

go

Now I get the following error:

Cannot update more than 1 sysindexes row at a time.

Well this did not make sense at all. When I run the select I only get one row returned. But I took the advice of the error and modified my where clause to include more columns and then I was able to make the updates that I needed.

Sybase Learning Continued

Today I came back into the office after having a week off. It is always hard to come back into the office but it is nice to get back into the swing of things as well. When I arrived I found some new Sybase books sitting on my desk. Now I know that my company is not going to send me to another class right now in these rough economic times so it was a nice surprise to see the books. Unfortunately, they are the old books of a former employee here. None the less, I plan on reading the books and trying to do as many of the labs as I can. I will be posting in the future about what I am learning here. I have some ASE Performance and Tuning as well as Advanced Administration and Troubleshooting books. I am really looking forward to the Performance and Tuning guide as I would love to make our end users experience better. I will keep you posted on my progress.

Sybase Kill Runaway Process

I am working on a stored proc that will be launched by a threshold that I set up on the log segment of my databases. It is a bit of work but I think that it will be nice not to get the page that someone has written some poor SQL and is filling up the log segment. I will not be running this in Production but I will in all other environments. I have started the proc and it is a work in progress at this time. I am pretty new to SQL as in my position I do not write a lot of SQL and this is one way that I am trying to learn. Here are the guts of it so far. It is getting me almost all of what I need. Now I need to figure out how I can get just the top row of the resultset and then kill that spid as that spid is the one with the most IO. Anyway here it is and I will let you know how it is going and when I have it complete.

Just in case you are wondering, The sp_threshold will pass the @dbname variable.

USE sybsystemprocs

go

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

BEGIN

DROP
PROCEDURE dbo.sp__killrunaway

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

PRINT
‘<<< FAILED DROPPING PROCEDURE dbo.sp__killrunaway >>>’

ELSE

PRINT
‘<<< DROPPED PROCEDURE dbo.sp__killrunaway >>>’

END

go

create
procedure dbo.sp__killrunaway

(@dbname varchar(30))

as

declare @dbid int

select @dbid = dbid from master..sysdatabases

where name = @dbname

select hostname, a.spid, b.name, physical_io, c.name

from master..sysprocesses a, master..syslogins b, master..sysdatabases c

where c.dbid = @dbid and

a.suid = b.suid and

a.dbid = c.dbid

order
by physical_io desc

go

EXEC dbo.sp_procxmode ‘dbo.sp__killrunaway’,‘unchained’

go

IF
OBJECT_ID(‘dbo.sp__killrunaway’) IS
NOT
NULL

PRINT
‘<<< CREATED PROCEDURE dbo.sp__killrunaway >>>’

ELSE

PRINT
‘<<< FAILED CREATING PROCEDURE dbo.sp__killrunaway >>>’

go

_

Sybase 15 Driver Q and A

From my post http://itknowledgeexchange.techtarget.com/dba/sybase-15-upgrade-project/ about my company migrating one instance to ASE 15 I had the following Question as a comment.

hey i need to know about the driver and provider required for Sybase 15.
Have to migrate from sybase12.5.3 to sybase 15.
and what are the corressponding compatibility issues?

The answer to this is that I am still using the Sybase 12.5.x drivers on my machine and have not noticed any issues connecting or while performing any task up to this point. I also suggest, if you want, to install the new drivers that should be included on the client install media. This is where you would go to install Sybase Central and any other client tools. I recommend installing Sybase Central as it can be a good tool to quickly look at a database, its devices, and other properties very quickly.

Thanks for the question and I hope that this helps.

Putty Connection Manager Tool

If you are working in Unix or Linux you are most likely using some tool to connect via ssh to that server for your session. Putty is a free tool that manages this very nicely. Many of you are already using Putty I am sure. If you are I did find a Putty Connection Manager that is really very nice. This allowas you to hav multiple putty sessions open as tabs. Check it out it is a very nice tool. http://puttycm.free.fr/

Sybase DBCC

Sybase DBCC or Database Consistency Checks. This was something that was talked about in my training class and so I thought I would implement this when I got back to work. I mentioned it to my team and they both said that I sounded just like them when they got back from class and they laughed a bit but said go ahead and see what happens. They told me that I would get a ton of errors that were meaningless and that to find out that they were meaningless errors I would have to call Sybase Support. They made it sound like it was more hassle then it was worth but I figured that I might want to do it any way just to get the experience and just what if I did find an error. I could be the Hero and I l love to be the Hero when I can.

I requested a couple devices from our Unix guys for the DBCC Database. I did an sp_plan_dbccdb and determined that I needed 5 gigs of space for data and only about 20 MB for log but I figured I would do 1 Gig for log. I got the devices and I created the DBCCDB and I ran the installdbcc script and boom I have DBCCDB and I am ready to go. I did also set up a named cache and bound it to DBCCDB just to make the DBCC commands run a bit faster. I also made sure that my number of worker process was sufficient for the task.

After getting everything set up I did it, I ran dbcc checkstorage on my DB and guess what. 0 Errors were found. Nothing at all to worry about and not even one spurious error that my teammates said I would have tons of. I am pretty sure that they have not run dbcc checkstorage for a couple releases and in 12.5.4 they added some error checking into the dbcc that makes it report very few if any spurious errors. Since this is the case I am going to regularly schedule this and perhaps one day it will really save us.

The dbcc does take about an hour to run on my DBA server that we load from Production once a week. I will continue to run he dbcc on this server so that I will not hinder the production server at all. Sybase does say that dbcc checkstorage is very un-intrusive but I would rather not give the application team any reason to blame the Database or my team for the application running slow. Based on this I would recommend that you run this as often ass possible as it may save you from getting in big trouble at some point. I hope to never see this report any errors but if it does I will be happy to catch it early. If you have any questions or comments please let me know here or go to www.sysadminsmith.com and send me an email.

Sybase DBCC

Sybase DBCC or Database Consistency Checks. This was something that was talked about in my training class and so I thought I would implement this when I got back to work. I mentioned it to my team and they both said that I sounded just like them when they got back from class and they laughed a bit but said go ahead and see what happens. They told me that I would get a ton of errors that were meaningless and that to find out that they were meaningless errors I would have to call Sybase Support. They made it sound like it was more hassle then it was worth but I figured that I might want to do it any way just to get the experience and just what if I did find an error. I could be the Hero and I l love to be the Hero when I can.

I requested a couple devices from our Unix guys for the DBCC Database. I did an sp_plan_dbccdb and determined that I needed 5 gigs of space for data and only about 20 MB for log but I figured I would do 1 Gig for log. I got the devices and I created the DBCCDB and I ran the installdbcc script and boom I have DBCCDB and I am ready to go. I did also set up a named cache and bound it to DBCCDB just to make the DBCC commands run a bit faster. I also made sure that my number of worker process was sufficient for the task.

After getting everything set up I did it, I ran dbcc checkstorage on my DB and guess what. 0 Errors were found. Nothing at all to worry about and not even one spurious error that my teammates said I would have tons of. I am pretty sure that they have not run dbcc checkstorage for a couple releases and in 12.5.4 they added some error checking into the dbcc that makes it report very few if any spurious errors. Since this is the case I am going to regularly schedule this and perhaps one day it will really save us.

The dbcc does take about an hour to run on my DBA server that we load from Production once a week. I will continue to run he dbcc on this server so that I will not hinder the production server at all. Sybase does say that dbcc checkstorage is very un-intrusive but I would rather not give the application team any reason to blame the Database or my team for the application running slow. Based on this I would recommend that you run this as often ass possible as it may save you from getting in big trouble at some point. I hope to never see this report any errors but if it does I will be happy to catch it early. If you have any questions or comments please let me know here or go to www.sysadminsmith.com and send me an email.

Sybase Training Day 5

Sorry that I have not done this entry yet. I have been busy with the holidays and my wife just graduated from ASU. I am back now and should be getting back to a more regular schedule. So I have touched on the first 4 days of training and it was a five day class. The last day of class was very short but we did cover some good stuff. One thing in the book that we did not cover was Sybase Central. We did not cover this because it is a GUI and the instructor, as well as myself and the other student, figured that we can learn to click on our own. It does look like the book has some good information about Sybase Central so I will go over it myself and write a post about it.

The main topic on the last day was about monitoring the system. We talked about some third party applications that are around that will do this by monitoring some “Fake” tables that Sybase keeps only in memory just for monitoring purposes, and we also discussed monitoring the old fashioned way. My company does this the old fashioned way by monitoring the alert log and backup logs for any errors or events that we may want to know about. We also have functionality built in to monitor other files if we choose to. Essentially this works buy parsing the alert log and the backup log files at some interval and looking for key words. When a keyword is found the system then sends us an alert that something is wrong and we need to look into it. I am still pretty new to the Unix world so I will not be sharing the scripts with you as I do not know everything about them. I can tell you how to do this in the Windows world using Windows Powershell. I have a series of blog posts here on how to monitor SQL server using Powershell and I am essentially doing the same thing that we do in the Unix environment. Some things are a bit different but for the most part it is the same.

The big thing about this is that you need to have some sort of automated system set up to alert you when something is not correct in the database. If you do not do this then you will not know when things are going wrong until a user calls and tells you, and that may be too late, or you will spend your entire day watching the alert log and the backup log looking for errors. That is not a task that I would wish on my worst enemy. Be a good DBA and make your life easier, set up some monitoring and get some important things done during your day.

No matter what my title or what my job is, I will always work harder on making my job easier then I actually work on doing my job. In the Windows world I would script as much as I could. Sometimes the script writing would take me days to complete even though the task itself only took 30 minutes to do manually. I tell you what though, the next time I needed to do that task and I was done in 30 seconds it was well worth it. So please set up some monitoring and some auto responces to certain errors if you can. That way you do not even get a page. Life is good if you make your job as simple as possible. Good luck with this and if you need any help just let me know by leaving a comment or head over to www.sysadminsmith.com and send me an email. I am always happy to help when I can.

Sybase Training Day 4

Day 4 of training was really all about backup and restore. This was a good class and I learned a lot about why my company does some of the things that they do. I knew how to do a Database dump and a Database load, but I did learn a lot and found a couple things to be very interesting.

First, I did learn that it is easy to backup all of the DDL for everything in the database and that it is a good idea to do so. This way if disaster strikes you can quickly get everything back up and running. Even if you have backups of the databases you might not have the ability to quickly get all of you disks re inited and done in the correct order. If you have to rebuild master then you have to get all the devices bult with the original vdevno #. I do not know about most of you but I do not specify the Vdevno # when I do a disk init. Sybase automatically finds the lowest available vdevno # and assigns it to the device. If you do not have the original DDL for this then you may have a hard time getting this correct. So you can use Sybase Central or other third party tools to extract the ddl of objects, but a cooler faster way is to use ddlgen on the server host and output this to a file or set of files that you will keep someplace safe just in case.

Second, I learned that compression is pretty awesome. You can specify the level of compression that you want to use when backing up a database. The levels aare 0-9 and 9 is the most compressed. We found though, that you do not really gain much after the first level of compression. The first level will dramatically reduce the backup file size. Anything after that may not be worth the extra work that has to be done by the server. The difference between the first level of compression and the last is not that much.

Third is that backups are very light on the actual database server. Since backups use the backup server to do the backup you can do the backup on-line and users will most likely not even know that you are doing a backup.

Fourth is that you should do a dbcc check of the database to make sure that you do not have any sort of problem before doing the backup. Do this as much as you can anyway. This will help prevent a problem from going unnoticed for a long time, if this happens then you might not have a backup without the error and that can get you into a world of hurt.

And of course we talked about how important it is to do regular backups of your databases. If you want to be able to recover to the most recent point in time then you will want to do transaction log dumps as well. Just do them often and try to check your dbs using dbcc’s as often as possible as well.

Once again if you have any questions or need anything just let me know.