SQL Server Memory Woes

Consider this: a server with 8 GB of RAM, Windows 2003 64-bit, and SQL Server 2005 x64 had 100 MB available at its lowest point (600 MB at its highest), as revealed by performance monitoring. There was insignificant traffic on the application side to be using this much memory. The issue, in other words, was that SQL Server memory usage was increasing as time passed. So off we go to find out what’s going on.

The first thing to check was the setting for max memory. This server had a setting of 2147483648 MB. That’s way more than 8 GB available. It means that SQL Server would use all available memory until the OS told it to stop as there wasn’t any left (How to adjust memory usage by using configuration options in SQL Server). Following Suggested Max Memory Settings for SQL Server 2005/2008, max memory was set to 6700 MB (How to: Set a Fixed Amount of Memory (SQL Server Management Studio)).

Another suggestion, in case of performance degradation, was from SQL Server Memory Usage – Is this normal? to update statistics. Using the update statistics method, we did the following:

select 'update statistics '+name from sys.tables order by name

Above query gives a nice list of tables in the database in a format ready to run. Just copy/paste the results of the query into Query Analyzer or Management Studio and run them. This will update statistics for all tables one by one, improving query execution plans. This query should be run periodically.

Since these changes were made recently, the results are not in. As soon as we know more, we will share with you if things improved.

SQL Server 2005 Error: Exception from Hresult

On a SQL Server 2005 (Standard x64) two-node active-passvie cluster using Windows 2003 (Enterprise x64), we tried to create a backup maintenance plan. Lo and behold! We got the error: “cannot show editor for this task. exception from hresult: 0xc0010014 (microsoft.sqlserver.dtsruntimewrap)”. We tried looking up on Google and came up with a solution recommended many times: install Service Pack 2. But before we went this way, we researched some more, and found the solution.

We are indebted to 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap). They provided a solution which we tried and it worked flawlessly. Just run the following command on the command line (DOS prompt):

regsvr32 "\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTS.dll"

This command should give a successful message upon execution, and when you do get a positive response, the problem with SQL Server should just go away.

Standby Database Out of Sync

Our setup has SQL Server 2000 running in Log Shipping mode for high availability. One day we received a message saying: “Standby database has been out-of-sync for x minutes”. To check for the job which was running, open Your Database then Management then SQL Server Agent then Jobs. Right-click the job which is giving error then View Job History then check Show Step Detail. Read through the messages and then click close. Again right-click the job and then Properties. Go to Steps tab and then choose each step and then click Edit. Copy the Command and paste in a text file. Do this for all steps in the job and close everything.

In Query Analyzer, run these commands one by one. Essentially what you are doing is running the job manually. Whatever message you get, just copy and paste it in a text file. Go through these messages to see exactly what the problem is.

The message we received was as follows (changed names of DB for reasons):

NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
Logged on to SQL Server 'MYDBSERVERNAME' as 'NT AUTHORITY\SYSTEM' (trusted)
NULL
NULL
Attempting to load file e:\tlog_200805242100.TRN to database MYDBNAME
NULL
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101: [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
NULL
SQLMAINT.EXE Process Exit Code: 1 (Failed)
NULL

After researching error 310, we came across a very good thread: sqlmaint.exe failed. Some issues were:

  • Disk space is full
  • Trying to create transaction log backups for Master database
  • ‘NT AUTHORITY\SYSTEM’ doesn’t have dbo rights
  • Other things are using the DB

We did not have the first three problems so we thought maybe someone else was using the database. So we ran the following:

sp_who

alter database MYDBNAME set single_user with rollback immediate

sp_who

And now if any user was connected to MYDBNAME, we could just log them off forcefully using

kill SPID

where SPID of the user is gotten from the sp_who command.

We did and when we ran the commands in the job again, we got the same error. So if it wasn’t another user connected, what else could it be? Also, you have to run the job commands under Master database, not under the database for whom the jobs are being run.

We thought it might be a process. So in Enterprise Manager we looked at Management then Current Activity then Locks / Objects and then MYDBNAME. Right-click the process and see if any job is running. Kill the last process run.

We ran the commands in the job again and this time it ran without any errors. So the issue was solved.

Remember to set the DB back to multiple users:

alter database MYDBNAME set multi_user

This is how we fixed the problem. Remember, this was a standby server where databases were Read Only. If your database is live and not on standby, these instructions are not for you.

Database Cluster with Windows 2003 and SQL Server 2005

For now this post is a collection of articles on how to set up clustering. Later, once I have more first-hand knowledge, I will write these guides myself.

Install Windows Cluster

Install SQL Server

MS SQL Server 2000: Create an Off-site Standby Server

This post is an extension to the “Poor Man’s Log Shipping” post written earlier on this blog. To summarize, the main server uses log shipping to maintain a standby server on-site. It also creates a nightly full backup and periodic backups of the transaction logs. I wrote a batch script to FTP these log backups once a day to an off-site location.

The reason for this was to create an off-site standby server. With daily log backups being received at this site, I just brought one of the nightly backups here. First things first: how do I get a multi-GB backup file to FTP over the Internet on a high-speed connection? It would take a long time. I could look at several options: FTP, BitTorrent, HTTP, or more. What I liked was the simplicity of FTP. All I had to do was compress the backup file and send it. However, even after compression, the size was multi-GB. So I used 7-zip to compress and also split the resulting file into 100MB chunks. Using the built-in command line FTP client in Windows and the mput command, I was able to transfer the data easily over a period of time. At the receiving end, I again used 7-zip to uncompress the data.

The next step was to restore the backups. First I needed to restore the full backup. I went with the GUI method through Enterprise Manager. All the options required are there but I wanted to understand the process and control it. Therefore, I abandoned the idea and tried the T-SQL approach. This was exactly what I was looking for. I got the best help from Microsoft’s Transact-SQL Reference for Restore.

First thing I needed to do was to get the names of the logical files in the full backup. This is necessary because of some reasons excellently mentioned in the Copying Databases article. The reason for me to do it was the directory structure was different in this server from the server where the backup was created. But how to do it? I got help from RESTORE FILELISTONLY. The actual command I used was this:

RESTORE FILELISTONLY FROM DISK = 'e:\fulldbbackup.bak';

It showed me logical files as well as the full path where the database would actually put the physical files. Since the path on this server was different from what the backup wanted, I had to make sure the database was restored to the correct path for this server. I had to specify exactly where to put the files during restore. The restore script I used was:

RESTORE DATABASE mydbname
FROM DISK = 'e:\fulldbbackup.bak'
WITH
MOVE 'datafile' TO 'e:\dbdata.mdf' ,
MOVE 'logfile' TO 'e:\dblogs.ldf' ,
STANDBY = 'e:\undofile.dat' ,
STATS = 10

I used STANDBY because I needed to restore subsequent transaction log backups. It took some time but the restoration completed. Then I needed to restore the log backups. One thing to remember is that logs need to be restored or applied in the sequence they were created. During my explorations, I noticed that if you try to apply a log backup that was created before the full backup was created, SQL Server will give an error and not proceed. If you apply a backup that has already been applied, it will process the backup but will also say that zero pages were processed. So it is my opinion that even if you make a mistake in applying the wrong log backup, it will not destroy your database. Of course, I did not skip a log backup and apply the next one so I cannot say what will happen if you do something like that. The script to restore one log backup is:

RESTORE LOG mydbname
FROM DISK = 'e:\logs\log1.trn'
WITH STANDBY = 'e:\log_undofile.dat',
STATS = 10

I had approximately two weeks worth of transaction log backups that needed to be restored. I could not manually change the name of the log file for each backup. So I thought of writing a script in Python to read the contents of the ‘e:\logs\’ directory and run the script each time with each file name in the directory. Since I am lazy, I sought an easier way. So I did the following:

In Windows command line, I ran:

dir e:\logs\ > e:\allfiles.txt

This created a list of all the files in that directory. But the format was what you would normally get using the dir command. So I used the find and replace feature of my text editor to replace all spaces with a semi-colon. Then I replaced multiple semi-colons with a single semi-colon. Something like:

Find: ‘ ‘ (it means a single space but without the quotes)
Replace: ;

And then

Find: ;;
Replace: ;

I continued replacing multiple semi-colons with a single semi-colon until I got just one after each data. I then opened this csv-type file in Excel (actually, it was OpenOffice.org’s Calc), copied the column with the file names, and then saved it in a text file.

Again find and replace came to help out. Each file was named like log1.trn, log2.trn, and so on. So I did this:

Find: log
Replace: RESTORE LOG mydbname FROM DISK = ‘e:\logs\log

And another find and replace was:

Find: .trn
Replace: .trn’ WITH STANDBY = ‘e:\log_undofile.dat’, STATS = 10

This created a file with scripts like so:

RESTORE LOG mydbname FROM DISK = 'e:\logs\log1.trn' WITH STANDBY = 'e:\log_undofile.dat', STATS = 10
RESTORE LOG mydbname FROM DISK = 'e:\logs\log2.trn' WITH STANDBY = 'e:\log_undofile.dat', STATS = 10

I saved and opened this file in SQL Analyzer and ran the script. Since there were a whole bunch of the log backup files, it took quite some time to finish the process.

After all the current backups were restored, I made a habit of collecting a week’s worth of log backups and applied them in a similar fashion.

I know this is a very manual process and I could write a Python script once to do all this stuff for me. I intend to write such a script but right now I do not have the time. Besides, this procedure was just for me to learn how to restore backups and then apply transaction log backups.

Some good resources include: (a) Using Standby Servers; (b) SQL Server 2000 Backup and Restore; (c) SQL Server 2000 Backup Types and Recovery Models;

Connect to Database Using Python

Although you can connect to databases using Python on many platforms, the specific examples here are on Ubuntu.

MySQL

If you would like to connect to MySQL using Python, the process is quite simple. You will need MySQLdb. In Ubuntu, it is quite easy to install

sudo apt-get install python-mysqldb

Once you have installed it, you are ready to go. How do you actually connect to it? I would rather let people smarter than me explain it. One such resource is Using the MySQLdb Interface.

Microsoft SQL Server

A fantastic guide for beginners has been provided at Accessing MSSQL using Python on Ubuntu. Head over there to read how to do it. Some useful links: pymssql, freetds, and ubuntu freetds.

However, I can also try to customize install instructions here.

sudo apt-get install python2.5-dev freetds-dev build-essential

You have to download the pymssql module, but choose the platform independent files, not Windows specific. After downloading (I am assuming the .zip file), you may install it using the following steps:

unzip pymssql-0.8.0.zip

cd pymssql-0.8.0

sudo python setup.py install

To see how to use it in your scripts, try Example script – pymssql module, Example script – _mssql module, or Accessing MSSQL using Python on Ubuntu.

Pivot Tables in Databases

Sometimes there are situations where you have to pivot a table or view. It could be that you need to pivot columns into rows or pivot rows into columns. There are always elegant ways to accomplish database tasks but not all of us are that good. So we resort to brute force hacks. This post will explain the brute force methods which will give you a very good chance to accomplish your task. It won’t be pretty and it may not be efficient, but it gets the job done.

The reason for discussing pivots is to invite everyone to provide better ways to accomplish this cumbersome task. I have employed this technique on MySQL so it should work. If Oracle, MS SQL, DB2 and other commercial DBMSes have better ways to do this stuff, and you know how to, please share with us.

I don’t like to use keywords INNER JOIN because to me it is more readable to have an equal to sign than inner join keywords.

Pivot Columns into Rows

Say you have a table such as this:

ID | STUDENT | GRADE1 | GRADE2 | GRADE3

Say you want to pivot it so that you get a view such as this:

ID | STUDENT | GRADE1

ID | STUDENT | GRADE2

ID | STUDENT | GRADE3

Your best friend in this case is UNION. For example,

SELECT ID, STUDENT, GRADE1 FROM SOMETABLE UNION ALL
SELECT ID, STUDENT, GRADE2 FROM SOMETABLE UNION ALL
SELECT ID, STUDENT, GRADE3 FROM SOMETABLE;

There you have it. Columns have been pivoted to rows. It is a brute force method but doesn’t it seem logical enough?

Pivot Rows into Columns

This is where it gets tricky. It is not very easy to pivot rows into columns. However, it can be done. The secret ingredient is the use of aggregate functions along with group by. The function I like to use is min() along with an arbitrary rank assigned to every row based on how you want to pivot the data.

Let’s say you have a table such as this:

TARGET_PHONE_NUMBER | SERVICE_PROVIDER | RATE_PER_MINUTE | QUALITY_OF_SERVICE

If you need to generate a horizontal listing of carriers based on what they charge for a particular destination (target_phone_number), from the lowest (to the left of the listing) to the highest (to the right of the listing) cost, first assign a rank to each row.

In MySQL 5, what I did was create a table with the same structure as our example table, plus an auto_increment column.

CREATE TABLE RANKING_TABLE LIKE ORIGINAL_TABLE;

ALTER TABLE RANKING_TABLE ADD COLUMN RANK INT AUTO_INCREMENT PRIMARY KEY;

The inserts, however, have to be grouped and ordered according to the way we wish to rank them. For example,

INSERT INTO RANKING_TABLE (TPN, SP, RPM, QOS) SELECT TARGET_PHONE_NUMBER, SERVICE_PROVIDER, RATE_PER_MINUTE, QUALITY_OF_SERVICE FROM SOMETABLE ORDER BY TARGET_PHONE_NUMBER, QUALITY_OF_SERVICE, RATE_PER_MINUTE, SERVICE_PROVIDER;

This will insert all data according to the order you defined and give a rank to each row. You will then have to group your data together based on TPN (TARGET_PHONE_NUMBER).

In other databases that provide you with rank() functions, you may use those. Consider the brute method of ranking as a poor man’s ranking.

Once you have the rankings, you just need to create a mega-script to pivot based on ranks. The bad thing about this method is that as you add more rows to be pivoted, your script grows as well and it becomes very difficult to maintain after some point.

SELECT TPN, MIN(RANK) FROM RANKING_TABLE GROUP BY TPN;

This was your first pivot, where the one with the lowest rank is supposed to be the best phone company to deal with. The next best carrier we get by:

SELECT A.TPN, B.RANKONE, A.MIN(RANK) RANK_TWO FROM RANKING_TABLE A, (SELECT TPN, MIN(RANK) RANKONE FROM RANKING_TABLE GROUP BY TPN) B WHERE A.TPN = B.TPN AND A.RANK <> B.RANKONE GROUP BY A.TPN;

Here you have created one row with two phone service providers. The left-most is the best and the next one is one notch less. Now we add a third provider to the query.

SELECT C.TPN, D.RANKONE, D.RANKTWO, MIN(C.RANK) RANKTHREE FROM RANKING_TABLE C, (SELECT A.TPN, B.RANKONE, A.MIN(RANK) RANKTWO FROM RANKING_TABLE A, (SELECT TPN, MIN(A.RANK) RANKONE FROM RANKING_TABLE GROUP BY TPN) B WHERE A.TPN = B.TPN AND A.RANK <> B.RANKONE GROUP BY A.TPN) D WHERE C.TPN = D.TPN AND D.RANKONE <> C.RANK AND D.RANKTWO <> C.RANK GROUP BY C.TPN;

You can add more layers to this query as you pivot more and more rows. As I said, it can become very unwieldy as you need to create more and more columns. However, once you know one way to do something, you can always fall back on it as default.

Conclusion

People say I like to write mega-queries, and I do. I like the step-by-step way of going over the query later on or for debugging while I am writing it. Using temporary tables is also something I like to do as less of as possible. Why create tables when a query can work just as well?

I have read that DBMSs are now coming out with pivot operators which are much cleaner to use. As soon as MySQL implements it, I would definitely like to check it out. Otherwise, I know that this method will work with or without pivot operators.

Brute force methods are very much possible to pivot tables. It takes a lot of analysis to come up with better ways to do the same thing, especially when it comes to customizing to your situation. If you know you can always rely on at least one solution to give you accurate results, you can use it to test whatever you need to test. For production, you may come up with a better alternative. If you do know to improve pivoting data in tables, please share it with the world.