SQL Client config, 32 and 64 bit

Say you want to change the preferred netlib connection order. Or add a server alias.

You can do this using the “SQL Server Configuration Manager” program. But installing this on each client machine where you want to do the modification might not feel that attractive.

Another option is to use a tool like regmon while doing the config on a reference machine, sniff the registry modifications and then shoot these out to the client machines. This might be overkill, though.

Yet another option is to use the cliconfg.exe tool, which ship with Windows. This tool is already available on your machine. However, on a 64 bit machine, you need to consider whether the client app is a 32 or 64 bit app. The processor architectore for that app will determine where in the registry the app (client libraries) will look. Below is my results from working on a x64 XP installation.
64 bit (native): HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib
32 bit (WOW): HKLM\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\SuperSocketNetLib

Razvan Socol was kind enough to enlighten me, a while ago, of the fact that runnig the 64 bit version of cliconfg.exe will modify in the “64 bit” registry entry (as per above) and vice versa for the 32 bit version of cliconfg.exe. Razvan mentioned that starting cliconfg.exe from Run will start the 64 bit version, and from a 32 bit app (Explorer for instance – which I couldn’t find how to do, but I’m sure somebody will enlighten me) will start the 32 bit version.

Above made me wonder in what folder each file is. Here are my findings (on the test machine I was using – a pretty clean XP x64 machine):

64 bit version of cliconfg.exe: C:\Windows\System32
32 bit version of cliconfg.exe: C:\Windows\SysWOW64

(And, before you ask, no, above is not a typo. There is some logic behind this. 🙂 )

Watch out for that autogrow bug

Under some circumstances, autogrow for database files can be set to some 12000 percent. I think this is limited to SQL Server 2005 and for databases upgraded from SQL Server 2000 (I didn’t bother to search – feel free to comment if you know). So, if you have a reasonably sized database and autogrow kicks in, you can do the maths and realize that pretty soon you are out of disk space.

I wrote a proc that I schedule that check for out-of-bounds values in sys.database files. The proc generates a bunch of messages it prints (handy if you have as Agent job with output file) and also constructs an error message and does RAISERROR (handy if you implemented alerting, for instance according to http://karaszi.com/agent-alerts-management-pack).

I prefer to schedule below as Agent job and use Agent alerts to notify me if we do have db with autogrow out-of-whack. As always, don’t use code if you don’t understand it.

USE maint
GO

IF OBJECT_ID('check_autogrow_not_percent'IS NOT NULL DROP PROC check_autogrow_not_percent
GO

CREATE PROC check_autogrow_not_percent
AS
DECLARE 
 @db sysname
,@sql NVARCHAR(2000)
,@file_logical_name sysname
,@file_phyname NVARCHAR(260
,@growth VARCHAR(20)
,@did_exist bit
,@msg NVARCHAR(1800)
,@database_list NVARCHAR(1000)

SET @did_exist CAST(AS bit)
SET @database_list ''

--For each database
DECLARE dbs CURSOR FOR
 SELECT name FROM sys.databases
OPEN dbs
WHILE 1
BEGIN
  FETCH NEXT FROM dbs INTO @db
  IF @@FETCH_STATUS <> BREAK

  SET @sql 'DECLARE files CURSOR FOR
 SELECT CAST(growth AS varchar(20)), physical_name, name
 FROM ' QUOTENAME(@db) + '.sys.database_files
 WHERE is_percent_growth = 1
 AND growth > 20'
  EXEC(@sql)
  OPEN files
  WHILE 1
  BEGIN
    FETCH NEXT FROM files INTO @growth@file_phyname@file_logical_name
    IF @@FETCH_STATUS <> BREAK
    SET @did_exist CAST(AS bit)
    SET @database_list @database_list '["' @db '": "' @file_logical_name '"]' CHAR(13) + CHAR(10)
   SET @msg 'Out-of-band autogrow in database "' @db '"' 
   ' with growth of ' @growth +
   ', logical file name "' @file_logical_name '"' 
   ', physical file name "' @file_phyname '"' 
   '.'
   RAISERROR(@msg101WITH NOWAIT
  END
  CLOSE files
  DEALLOCATE files
END
CLOSE dbs
DEALLOCATE dbs
IF @did_exist CAST(AS bit)
  BEGIN
   SET @msg 'Databases with out-of-control autogrow in databases: ' CHAR(13) + CHAR(10) + @database_list
   RAISERROR(@msg161WITH LOG
  END
GO

Remove transaction log files

Say you happened to get too many transaction log (ldf) files. Can you remove log files from the database? Yes, but only if a file isn’t in use, and you cannot remove the first (“primary”) log file.

So, be prepared to investigate the virtual file layout, using DBCC LOGINFO, to see if a log file is in use or not. You can find information about how to investigate the virtual log file layout in my shrink article. The basic steps are a bit similar to shrinking a log file: Investigate virtual log file layout, backup log, possibly shrink file, try removing it. Do this again as many times as it takes (repeat, rinse and lather).

Below is a script that, if you take the time to study it and play with it, will prepare you to remove transaction log files from a database. As always, don’t execute it if you don’t understand what it does!

USE master
IF DB_ID('rDb'IS NOT NULL DROP DATABASE rDb
GO

CREATE DATABASE rDb
ON
PRIMARY
NAME N'rDb'FILENAME N'C:\rDb.mdf' SIZE 50MB FILEGROWTH 1024KB )
LOG ON
(NAME N'rDb_log2'FILENAME N'C:\rDb_log2.ldf'SIZE 3MBFILEGROWTH 2MB)
,(NAME N'rDb_log3'FILENAME N'C:\rDb_log3.ldf'SIZE 3MBFILEGROWTH 2MB)
,(NAME N'rDb_log4'FILENAME N'C:\rDb_log4.ldf'SIZE 3MBFILEGROWTH 2MB)
GO

ALTER DATABASE rDb SET RECOVERY FULL
BACKUP DATABASE rDb TO DISK = 'C:\rDb.bak' WITH INIT
CREATE TABLE rDb..t(c1 INT IDENTITYc2 CHAR(100))

INSERT INTO rDb..t
SELECT TOP(15000'hello'
FROM syscolumns AS a
CROSS JOIN syscolumns AS b

--Log is now about 46% full
DBCC SQLPERF(logspace)

--Check virtual log file layout
DBCC LOGINFO(rDb)
--See that file 4 isn't used at all (Status = 0 for all 4's rows)

--We can remove file 4, it isn't used
ALTER DATABASE rDb REMOVE FILE rDb_log4

--Check virtual log file layout
DBCC LOGINFO(rDb)

--Can't remove 3 since it is in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--What if we backup log?
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'

--Check virtual log file layout
DBCC LOGINFO(rDb)
--3 is still in use (status = 2)

--Can't remove 3 since it is in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--Shrink 3
USE rDb
DBCC SHRINKFILE(rDb_log3)
USE master

--... and backup log?
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'

--Check virtual log file layout
DBCC LOGINFO(rDb)
--3 is no longer in use

--Can now remove 3 since it is not in use
ALTER DATABASE rDb REMOVE FILE rDb_log3

--Check explorer, we're down to 1 log file

--See what sys.database_files say?
SELECT FROM rDb.sys.database_files
--Seems physical file is gone, but SQL Server consider the file offline

--Backup log does it:
BACKUP LOG rDb TO DISK = 'C:\rDb.bak'
SELECT FROM rDb.sys.database_files

--Can never remove the first ("primary") log file
ALTER DATABASE rDb REMOVE FILE rDb_log2
--Note error message from above

sp_altermessage is back in business!

Just a quick note that we again can modify whether system messages are to go to eventlog/errorlog again. I.e., we can change the is_event_logged column in sys.messages. This is very valuable in general and specifically is you want to define Agent alerts (for which Agent polls the Eventlog). For instance:

SELECT * FROM sys.messages
WHERE message_id = 1205
AND language_id = 1033

Notice the value for the is_event_logged column. Now, run below:

EXEC sp_altermessage
 @message_id = 1205
,@parameter = ‘WITH_LOG’
,@parameter_value = ‘true’

Now, re-run the select statement and see that you modified the behavior for the system message. Now, re-run the sp_altermessage with ‘false’ to reset to default.

The ability to modify this behavior for system messages was available prior to SQL Server 2005, but some re-architecturing in 2005 removed the functionality. kozloski informed me in this blog post that 2005 sp3 re-introduced the functionality and obviously as of sp1 the functionlity is back in 2008 as well.

 

Is it only me or are "uncomfortable" answers largely ignored?

I do quite a bit of posting on forums, and I have noticed that in many cases when you take your time to explain how something work, or why you should do something perhaps in a not so obvious way, that answer tend to be ignored and never replied to.

Instead the original poster replies to the short answer which perhaps ignored things like best practices, performance, readability etc. And, very often I see endless discussion based on that reply, where the original poster never gets a satisfactory solution or even perhaps a working solution. And there I posted an elaboration which held the answer days ago! I also explained why solution X doesn’t work and solution Y only work in some circumstances etc.

In many cases, we have taken the time to write articles or blog posts where we have predicted most misunderstandings and explain why you should do it this or that way and what drawbacks workaround A has, and also what drawbacks workaround B has. So, we post the URL to the article only to find that it apparently never was read because the following discussions in the thread goes on for days and waste time to elaborate on things that could be understood by spending some 10 – 20 minutes to actually read the article or blog post we pointed to.

I find this state mildly depressing. Have we reached the sad state where we don’t tend to be interested in why something works, how it works, or even understand what we are actually doing? Quick fixes, anyone? Only 10 cents each!

Is this something new? I’ve been on forums for > 10 years now, let me try to think….. I believe that tendency has always been there, but perhaps it has been getting worse with time. What are your experiences? Am I only having a bad day?

Where’s that sys.messages management pack?

I’ve often been thinking that “somebody” should produce a list and perhaps some ready-made TSQL code of the SQL Server error messages we want to be alerted for. Something like a Management Pack, but not tied to any particular sys mgmt software. Essentially, what error messages would we typically want to be alerted for, and some ready made TSQL code which defines an alert (msdb.dbo.sp_add_alert, see also http://www.sqlskills.com/BLOGS/PAUL/post/Easy-monitoring-of-high-severity-errors-create-Agent-alerts.aspx) and operator for each of those.

Honestly, how many of you have been going through all SQL Server messages and determined which of those we want to be alerted for? Sure, we can decide to specify alerts for all high-severity level messages (like 19, 20, 21, 22, 23, 24 and 25). But unfortunately the severity classification isn’t as coherent and we would hope.

For instance, Paul Randal just recently blogged about error 825, which has as low severity as 10! Do we want to be alerted for 825? You bet! Check out Paul’s blog at http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx.

We can, however make it a little bit easier for us. Since Agent detects the message by sniffing the eventlog, we can immediately discard all messages which aren’t written to the eventlog (there’s no use defining alert for something which the alerting mechanism never detects, right?):

SELECT 
FROM sys.messages
WHERE language_id 1033
AND is_event_logged 1
ORDER BY severity

Now we are down to 699 messages. It used to be that we could configure whether a message should be written to eventlog (sp_altermessage), but this functionality disappeared in 2005. I haven’t checked Connect, but my guess is that if there is such a wish/entry in the first place, it doesn’t have too many votes.

My suggestion is that we create some small community project somewhere where we add what messages we want to be alerted for. This can be as simple as a table with error numbers and some insert, and then we use this to generate our sp_add_alert commands based on that. Question is where to do this, so it can be maintained and refined over time? Or if it has already been done by somebody…

 

Troubleshooting login problems

I’ve been learning a few things about troubelshooting login problems over the last few months. I first want to say that I’ve learned most of this through other blogs, comments etc. At the end of this blog post you will find an URL list where you can look for more details.

It started a few months back where I somewhere read a blog stating that we can use the state in a “login failed” error message to determine why the login failed. As you can imagine, there can be several reasons for login failure (user doesn’t exist, pwd doesn’t match, windows login name isn’t a trusted login etc). For some reason, I didn’t save that URL and of course I needed it some time later and couldn’t find it.

One place where you can find such a list of states is… Books Online. BOL 2008 documents every error number, so it is a matter of knowing what error number to search for: 18456. You can also search for “login failed” (pretty easy to remember) and the first hit is a different page (from above) but with similar information. I just tried a search in 2005 BOL for the same and had similar hits. This information might have been added to BOL 2005 in a more recent BOL 2005 release, though.

Now, don’t be surprised if the client app only receive state 1 for the login failed messages. This is a good thing, and on purpose. We don’t want the system to disclosure too much information to a malicous user (app) about why a login fails. So what we do is to look in the errorlog or for the “real” state (the EventLog doesn’t seem to carry this information, for some reason). Below is an example from SQL Server 2005, sp2, with timestamps removed:

Error: 18456, Severity: 14, State: 5.
Login failed for user ‘gurka’. [CLIENT: <local machine>]

So, state 5 tell us, according to SQL Server 2005 BOL, that “User ID is not valid.”.

Now, doing the same in SQL Server 2008 was interesting. Looking in the EventLog, we do see an explanation as to why the login failed. We still don’t see the state in the EventLog, but we have an explanation so we might not need the state. And in the errorlog file, I had below message:

Error: 18456, Severity: 14, State: 5.
Login failed for user ‘gurka’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

So, not only do we have a state, we also have a decription for why the login failed. Is it pathetic that such a thing can make you happy? Perhaps it is… 😉

Here are some other blog posts on the subject:
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
http://jmkehayias.blogspot.com/2008/03/understanding-login-failed-error-18456.html

Preparing for the SQL Summit 2008

I’ve started to produce my presentations for the “SQL Summit 2008”. It is always fun to dive down into the details about some certain feature. For instance one of my presentations is about policy based management, which will also include a few words on configuration servers and also news in general in SSMS. So I took the time to look around in SSMS for new stuff and I found surprisingly much. This is one of the upsides with speaking, you need to take your time to actually study a broader area about a subject, and then dive down into details.

The SQL Summit 2008 will take place from Oct 6 to Oct 10, over 4 cities in Sweden (Umeå, Malmö, Göteborg and Stockholm). I’m happy to be in good company among other speakers like Per Westerlund, Roger Cevung, George Thörngren and Patrik Löwendahl. And I’m really happy that the keynote (and other presentations) will be delivered by my good friend Kalen Delaney. The slightly sad part is that one my my presentations is scheduled at the same time as one of Kalen’s. But who knows, perhaps my room will be empty <g>?

Check it out at SQL Summit 2008.

Rebuilding msdb on SQL Server 2008

Because of the problems I had removing Data Collector I decided to rebuild msdb. You probably heard about instmsdb.sql, but it was a long time since I actually used it. I asked about rebuilding in the MVP group and Dan Jones (MS) pointed me to a Blog post from Paul Randal on how to do this on SQL Server 2005. Here’s Paul’s blog post:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx

Since above is for SQL Server 2005 I realized that it might not work smoothly on 2008. And It didn’t. Below are some of the things I discovered (also posted as a comment on Paul’s blog). Read below in light of Paul’s blog. I should also say that nowhere does Paul states that his instructions work on 2008. It was me taking a chance. 🙂

You need to add startup parameter -s <instancename> if it is a named instance. Now, this I knew, but for the sake of other potential readers…
I initially started the instance from the Windows services applet by adding -T3608. That didn’t allow for detaching msdb. So I started from an OS command prompt and also added -c. This allowed me to detach msdb.
I now ran instmsdb, but that produced a number of errors. Here are a few comments about some of them:
* Complaints on xp_cmdshell. I did try enabling this first and then ran instmsdb again but same result.
* Bunch of errors when creating various Data Collector objects. This wasn’t good, because cleaning up DC was the reason to rebuild msdb in the frist place.
* 3 errors about sp_configure and -1 wasn’t allowed value (two for Agent Xps and one for xp_cmdshell).
Just for the sake of trying, I now tried to connect to the instance using SSMS Object Explorer. But I now got some error regarding Agent Xp’s when connecting. I tried to explicitly enabling Agent XP’s using sp_configure but same error. When connected there’s no node in Objects Explorer for Agent.
I took this as an indication that Agent isn’t healthy. Whether it was me doing something fishy or it isn’t as easy as just running insmsdb.sql for SQL Server 2008 – I don’t know. But I’m in for a rebuild of system databases. This isn’t that bad since it is a just a test machine. But these issues might serve as example why you want to follow Paul’s initial advice: always backup msdb (also on test machines).

We’ve come a long way …

For various reasons I decided that I want virtual machines with older (pre-2008) SQL Server versions available on my various machines. For me, virtualization (in this case VPC) is great:

  • I rarely use these installs, most often I just boot it and check some detail.
  • I don’t have to litter the host OS.
  • I don’t pay anything (performance) in the host OS, except for some disk. The overhead for an extra XP install is some 1.5 GB which nowadays isn’t that bad.

So I did a several copies of my XP VPC folder (I don’t do diff drives for various reasons).

And then started with installing SQL Server 2000 (I already had VPCs with 2005). I do work with 2000 now and then, but I mainly use SSMS to connect to 2000. So it was a bit of flashback to play around with EM again.

Next was 7.0. OK, 7.0 didn’t look that different from 2000…

Installing 6.5 was more fun. I had forgot for instance that SQL Server Agent was called “SQL Executive” back then. Also, Enterprise Manager was a totally different tool compared to 7.0/2000.

I decided to skip 6.0 since the 6.5 BOL is basically 6.0 BOL with added “What’s new” section. So having above 6.5 VPC for me also covers 6.0.

The most interesting part was to 4.21a for NT:

I first realized I made a mistake when copying the files from diskettes to CD – I put all the files in same directory. Setup expects some folder structure like DISK1, DISK2 etc. And since I don’t have the diskettes anymore, how to know what files go in which folder? What I ended up doing was to copy the setup files locally (a whopping 4.4 MB !) and modify SETUP.INF. Interestingly enough I did figure out how to modify the INF file successfully. Imagine doing that today – without knowing anything about the installation…

Anyhow, installation was successful and I checked out what tools we had. Hehe, this is where nostalgia kicked in. I already have a OS/2 VPC with SQL Server 1.1, but I can barely navigate that OS nowadays. And there were no GUI’s at all with SQL Server 1.x. Since I hadn’t seen SQL Server 4.x for many many years now, I think this was more fun than re-living 1.1.

What strikes you are of course the tools. Looking at the engine (using “Books Online”) you mainly see that a lot of todays functionality wasn’t there of course. But using the GUI makes it so much more apparent what was there and what wasn’t. And of course the whole feel of the GUIs were totally different.

The help file start page has some 9 buttons, for various sections like Datatypes, Expressions, LIKE and Wildcards, Transact-SQL Statements etc. No tree-like structure…

The release notes explain for instance that Extended stored procedures are a new thing and with that came SQL Mail.

What we nowadays call SQL Server Agent was called “SQL Monitor”.

The “SQL Administrator Win32” tool had some very rudimentary dialogs for looking at “devices”, “DB”, “Login” etc. There are some dialogs available from the menus like “Database Consistency Check”, “configure SQL Server”. I could not find for instance where to schedule backups with SQL Monitor…

The “SQL Object Manager Win32” tool wasn’t actually that bad. The “Objects” window list one row per object in the database and you can double-click it to “edit” it. Interesting enough I believe this is the first version where we had “Generate Script” functionality in the tools, for instance. Hehe, there’s even a GUI for assisting in creating a SELECT statement with rows allowing you to type text for the WHERE clause, the ORDER BY clause etc.

There’s a separate tool called “SQL Transfer Manager” which functionality over the years have been exposed in various places (EM, DTS, SSIS, DMO, SMO etc).

Back to reality. Firing up SSMS 2008 I realize how much has changed… The engine has so much more functionality. Perhaps only, say, 10-15% of what we have today we also had in, say, 4.x – if even that. Not to mention things like SSAS, SSIS, RS, etc. So, even though it was fun nostalgia to fire up an old version, I really enjoy being where we are today. 🙂