Ever wonder what DUMP_LOG_COORDINATOR_QUEUE wait is?

If you've used wait statistics to do performance tuning on your SQL Server systems, I doubt that you would ever see this on your top waits (or if even shows up at all). You would rarely find anything on the Internet about this wait type, although, I did find a placeholder from SQLServerpedia for future information. However, if you have been heavily playing around with the SQL Server undocumented function fn_dump_dblog(), you may have seen this wait type. This occurs when you run this function to read your log backups. Janice Lee (a schoolmate of mine from way back home) blogged about using this function as an alternative to restoring to a point in time. While I did not use this function to do a point-in-time restore of a database, I did find it interesting seeing the DUMP_LOG_COORDINATOR_QUEUE wait type. I actually used it to estimate the amount of INSERT, UPDATE and DELETE commands executed on my database. While it is not pretty straightforward as compared to gathering performance counters, it's the only thing I can do especially if you were brought in for a one time consulting engagement and need to work with what you have.

Note that I did not run this in a production server so I have no issues running the undocumented function. Use it at your own risk.

bass_player on Microsoft Talk TechNet

It's a privilege for me to be a part of Microsoft Talk TechNet - the Talk radio for IT Pros. This coming Friday, 8-Apr-2011 9AM Pacific Time,  join me as we talk about anything related to managing and administering SQL Server. If you know anybody who's into administering SQL Server, you can pass this information around.

Event ID: 1032483824

Language(s): English.
Product(s): Microsoft SQL Server 2008 R2.
Audience(s): IT Generalist.

For those of you who have missed the event, the recording has been made available for download

Connecting to SQL Server via Windows PowerShell with SQL Server authentication

Most of the articles I've written about SQL Server with Windows PowerShell have been using Windows Authentication. And while it is highly recommended to use Windows authentication to connect to SQL Server, the reality is that the IT infrastructures we have don't run on Microsoft Windows.

Here's an article I wrote on how to use Windows PowerShell to connect to SQL Server via mixed mode authentication

NT AUTHORITY\NETWORK SERVICE account in SharePoint Content Database

I have had the opportunity to assist a WIndows SBS MVP migrating Windows SharePoint Services 3.0 databases from SQL Server 2005 to SQL Server 2008 in a Windows SBS 2008 environment. He was following the steps outlined in this Microsoft TechNet article to move the content databases and configure them from inside SharePoint Central Administration when he hit a wall and couldn't move forward. When he was at the point where he is already pointing to the new content database, SharePoint started throwing an error. My first instinct was that it has something to do with permissions but I need to capture some activity before even doing anything. Apparently, SQL Profiler is not installed. I opted to change the Authentication mode from Windows to Mixed mode just so I can use a SQL Server login with the SharePoint configuration. After creating a SQL Server login for SharePoint to use, everything was back to normal. While I wasn't comfortable with using Mixed Mode authentication for SharePoint, I dug deeper to see what Windows account was being used thru the Application Pool from within IIS. It appears to be NT AUTHORITY\NETWORK SYSTEM - the default account when you install Windows SharePoint Services 3.0 in the same server as the SQL Server (this is a common setup with Windows SBS). What I did was to add the NT AUTHORITY\NETWORK SYSTEM account in SQL Server using the CREATE LOGIN command (you won't be able to do this in Management Studio as the account is not exposed) and, then, assign the db_owner role in the content database.

I went back to the SharePoint Central Administration after that to switch the configuration of the content database from using a SQL Server login to using Windows authentication. That did the trick.

Note that if you are moving your SharePoint databases from SQL Server 2005 to SQL Server 2008, whether within Windows SBS or a full blown Windows Server system, make sure you check the logins as they need to be moved as well for the SharePoint application to work. You can even use the transfer SQL Server logins script from Microsoft to do this

Backup on shared folders running on a local system account?

I still see a lot of SQL Servers running using the LocalSystem account, particularly, MSDE 2000, which is very popular among third-party applications that requires storing data in a database. Others run a stand-alone SQL Server system and use LocalSystem account for the service accounts. While this may be a security risk, a lot of users still have them configured. And when they need to generate database backups on a shared folder, the SQL Server service's attempt to connect to network resources are denied access because they have no credentials and they are using a null session.Of course, a typical recommendation would be to change the service account to a local Windows or a domain account with least privilege but not everybody is open to that suggestion. So how do you allow SQL Server to generate a backup on a shared folder while using a LocalSystem account? The solution: enable null session shares

While I do not advocate such workarounds as it opens up additional security loopholes, it still is a workaround. And as I usuallly say, WARNING: This is not a recommended approach. Use at your own risk

Microsoft has a documented procedure to enable null sessions shares and while the KB article mentions Windows 2000, it does work for Windows Server 2003. This should be done on the Windows machine that hosts the shared folder. A word of caution if you intend to use this approach - document every step that you do and make sure you rollback any changes made after generating your database backup. Tasks like enabling the Guest user account (this is disabled by default), modifying the registry, etc. should be rolled back as soon as you're done, otherwise, you're opening up security vulnerabilities across your network.

How to immediately shrink the SQL Server log files

Have you ever wanted to shrink your log files but couldn't do it no matter how hard you try? While I and the majority of SQL Server DBAs would not recommend shrinking the transaction log (or any data file in your database), you'll end up doing it one way or another due to emergency situations. Here's what you'll probably end up doing as defined in this Microsoft KB article:

• Backup the transaction log. This will truncate the log
• Shrink the log file

Pretty simple, right? Well, there are times when this might not work because SQL Server does not shrink the log immediately. The DBCC SHRINKFILE operation occurs only at checkpoints or transaction log backups. SQL Server divides each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. This MSDN article describes virtual log files in SQL Server. SQL Server MVP Tibor Karaszi highlights why you would not want to shrink your log files. This blog by Johnny Hughes has a script that lets you do this task.

USE databaseName
GO

DBCC shrinkfile(<file_id>,NOTRUNCATE)
DBCC shrinkfile(<file_id>TRUNCATEONLY)

CREATE TABLE t1 (CHAR1 CHAR(4000))
GO

DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)

BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 VALUES ('a')
SELECT @i = @i +1
END

TRUNCATE TABLE t1

BACKUP LOG databaseName WITH TRUNCATE_ONLY
END