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.