SQL Server Mailbag: CALs, witnesses and unwanted changes

In this edition of the SQL Server Mailbag, database expert Denny Cherry tackles reader questions on SQL Server licensing, dealing with IT personnel problems and various connection issues.

Have a question of your own? Send it along to editor@SearchSQLServer.com.

If I decrease a SQL Server 2000 user's client access license from 50 to 20 on a SQL Server, is licensing still valid? -- db23

It is -- if you only have 20 people connecting to the SQL Server. Microsoft licensing is very much "on your honor." The last thing Microsoft wants is for you to be unable to conduct your business because you are short one license.

Remember that with CAL-based licenses, the license is assigned to a person or computer. That CAL allows the user to connect to all SQL Server 2000 machines in your environment.

My VB6 project on Windows XP works perfectly when connected to SQL Server 2005 using Open Database Connectivity and Driver Name: SQL Server, Version: 2000.85.1132.00, File: SQLSRV32.DLL, Date: 14/04/2008. I want to eventually migrate to SQL Server 2008, and hence cannot use the SQL Server 2000 driver. However, when I use ODBC and Driver Name: SQL Native Client, Version: 2005.90.1399.00, File: SQLNCLI.DLL, Date: 14/10/2005, I get the following error when I save a record (affecting two or more tables):

Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.

(Microsoft's link for this is KB article 180843.) What should I do? -- Trevor

You may get better results from the OLEDB driver. Also, you may want to try the SQL Server 2000 driver. As far as I know, the SQL Server 2000 driver should work fine on SQL Server 2008. You wouldn't be able to use new features --such as database mirroring -- but I believe everything will continue to work as normal.

Unfortunately, to give you a better answer I would need much more information about what is going on with the database.

I'm troubleshooting a SQL Server 2000 database problem. It's a database for our data warehouse system that pulls everyday data from a host. The size of the database is 461 GB and the log is 67 GB. Because of space concerns, I want to detach the log file and create a new one, but today I cannot open the database locally or remotely. When I click the databases tree in MMC, it hangs. I checked the event viewer application log and found logs that have run almost every 15 seconds since the previous evening. The log says:

3455: Analysis of database 'db_name' (8) is x% complete (approximately xx more seconds)

I assume this is a result of the auto-shrink feature. How do I stop this in order to enter the database and check the settings? -- Nordin

More answers from Denny How do I recover data after a disc crash - and no backup? How do I write a stored procedure for an INSERT query? Can I downgrade from Enterprise to Standard Edition?

First of all, you should never detach the database and delete the transaction log. If you don't need the ability to do point in time restores, then change the database recovery model from FULL to SIMPLE. If you need point in time recovery, then you'll want to begin performing transaction log backups.

It sounds like your database server restarted while there was a large transaction in process. SQL Server is now trying to roll the transaction back, which must complete before the database can be brought back online. The number of seconds that SQL Server says are remaining is probably pretty accurate. There's no way to cancel this operation without running major risk of corruption and losing all the data in the database. You'll need to just wait the operation out.

When using mirroring in SQL Server 2005, can I use Tomcat as my witness server?

No, you can't use Tomcat as your witness. The witness must be an instance of SQL Server. If cost is an issue, you can use the Express edition as the witness.

I have a problem where several IT personnel have administrative access to our SQL Servers. They don't know anything about SQL Server and they make changes to my maintenance plans and other settings on the instance. When I ask them who made the changes, no one takes responsibility. I have also asked them to keep their hands off to no avail.

Ok, enough complaining.

I would like to set up some kind of audit on the server that would capture who is making changes. Is there a way to do this? -- Ambrose

If you are using SQL Server 2008 you can setup auditing on the msdb database and see who is making changes to it. If you are using SQL Server 2005, your only real option is to setup SQL Profiler and monitor the commands being run against the database.

I suggest that you go to your management and explain the problem. Tell them you would like to remove the permissions from anyone who isn't a DBA (or who isn't performing DBA functions), as people are making changes to production systems without the authority to do so and that those changes can impact the availability of the production server (via index rebuilds) and the ability to recover the database via a restore in case of database corruption (backup schedules are changed).

Hopefully management can lay down an edict that says "touch it and you will be in trouble," or give you the permission to change the rights on the database so that non-DBAs can't perform DBA functions on the SQL Server.

I am using asp.net C# built-in login controls and I want to create a log (audit trail) in my project for the users. How do I do this? -- Issac

This sample code by Ripal Soni shows that you enter the SQL command yourself. Here you could either put in a second SQL Statement to log the attempt, or better yet, create a stored procedure on the SQL Server and call the SQL Server passing in the UserName and Password. Then the SQL Server can log the attempt and the result before passing the result back to the .NET application.

I'm struggling to figure out the right time to do my last transactional log backup before my full database backup. For example, say I do a full database backup once a day at 10 p.m., and a transactional log backup every 4 hours (12 p.m., 4 a.m., 8 a.m., 12 a.m., etc.). If I have to restore the database to a point in time, say 11 p.m., would I use the 12 p.m. backup, which overlaps the time I ran the full database backup? Does that matter? Or will I have duplicate transactions because the transactions are already in the database and will be applied again when I restore the log file?

No this doesn't matter -- you won't get duplicate transactions. You would restore the most recent full, and then restore all the transaction log backups taken after that full backup until the point it time you wanted to stop restoring.

When the SQL Server does the restore of the full, it keeps track of the last LSN committed. It then finds that LSN in the log files and begins processing the log file starting with the LSN after that. (It's actually more complex than that, but that should give you a good idea of what's happening.)

Have a burning SQL Server question of your own? Let us know at editor@SearchSQLServer.com, or pose it to our IT Knowledge Exchange forum for fast responses from your peers.

ABOUT THE AUTHOR:

Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog: SQL Server with Mr. Denny