SQL Server Error 5042 Can Not Remove File X Because It's Not Emty

Couple weeks ago one of the job failed with the message stating that the file can not be removed because it is not empty. I have implemented quite a few partitioned tables and created a stored procedure which switches data from a 3 months old partition and then merge the partition. The process has been working fine but suddenly it failed. I check the partitions, but there was nothing in the file. I started to search the Internet and I found a help in here by Jeffrey Yao at SQLServerCentral.com. In my case the file was created originally 5 GB in size but during the month it grew to 5.5 GB in size and the file size was still 5.5 GB. I followed Jeffrey's steps and shrank the file to it's original size of 5 GB and then the process ran successfully to merge the partition.

HTH

SQL Server Collate Clause And Case Sensitivity

The servers in the current environment I am working has been configured with case insensitive collation by default during the installation. I had to help a developer who was trying to write a clr function to compare values stored in a database. We had to find out all the uppercase first and last names stored in one of the tables. The columns in the table have been created case insensitive. I thought of using built in feature instead of doing it in CLR and finding out the results. As you see in the simple script below I am creating 4 columns 2 case insensitive and 2 case sensitive. Populate it with sample data and finally compare the columns against each other. Note that once you create a columns like this you can not compare case insensitive column against so pay attention the where clause in the select statements. With this simple solution we were able to easily produce all the records stored in uppercase in the table. For more information look in the Books Online and search for collate

HTH,


USE tempdb
GO

CREATE TABLE dbo.CaseSensitiveTest(
RowId TINYINT IDENTITY (1,1),
CIFName VARCHAR (30),
CILName VARCHAR (30),
CSFName VARCHAR (30) COLLATE Latin1_General_CS_AS,
CSLName VARCHAR (30) COLLATE Latin1_General_CS_AS

)

GO

INSERT INTO dbo.CaseSensitiveTest
SELECT 'MICKEY', 'MOUSE', 'Mickey','Mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'mickey','mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'Mickey','Mouse'
UNION ALL
SELECT 'MICKEY', 'MOUSE', 'MICKEY','MOUSE'
UNION ALL
SELECT 'bunny', 'rabbit', 'Bunny','Rabbit'
UNION ALL
SELECT 'BUNNY', 'RABBIT', 'BUNNY', 'RABBIT'

GO

SELECT * FROM dbo.CaseSensitiveTest

SELECT *
FROM dbo.CaseSensitiveTest
WHERE CIFName COLLATE Latin1_General_CS_AS = CSFName
AND CIFName COLLATE Latin1_General_CS_AS = UPPER(CIFname)


DROP TABLE dbo.CaseSensitiveTest

SQL Server Failed to Alert Operator

I just deployed a new SQL Server Server and set up some jobs. Couple of days later wanted to check the server and see how jobs were executing. I was surprised to find out one job has been failing and I remember not seeing any alert. I did remember that I set up database mail and operators. Job history tells me that sql server failed to notify the operator. I started to look around and finally thought of checking the sql server agent properties. To my surprise I found out that I forgot to set up the properties under alert and putting check for enable email profile, selecting the profile and another check for enabling fail safe operator did all the trick. After changing the setting started to work and sql server agent started to notify me with emails.

HTH

SQL Server 2008 Invalid SKU error

I was trying to set up SQL Server 2008 Cluster on Windows 2008 OS. Creating the cluster with a single node went pretty smooth. However when I wanted to add the second node to cluster I kept getting 'Invalid SKU error' and installation kept aborting the operation. I downloaded the new ISO of SQL Server 2008 from MSDN website with no help. Searching the net came out with the link below which stated that it was a know bug and ways to workaround it. I started the setup process from command line using the command below. At the end the node has been added with success.

Here is the command that I started the installation:

setup.exe /q /ACTION=AddNode /INSTANCENAME=""

Microsoft Connect bug report site:

HTH

SQL Server 2008 R2 Availalbe to Download

SQL Server 2008 R2 is available for downloading to MSDN and Technet subscribers. Here are the links:

• http://msdn.microsoft.com/subscriptions
• http://technet.microsoft.com/subscriptions

Implicit Data Type Conversion

Hello all,

Yesterday by accident I ran into a query running slow. In this case the query is generated by Nhibernate. After looking at the query and execution plan I figured out that it was the implicit data type conversion.

The data in the table is stored in column of varchar data type. The column is a primary key with clustered index. However the query is issued against the table with a parameter passed in and it's declared as NVARCHAR. Do you see where this is going? So when the query is executed with nvarchar parameter sql server does an index scan on the clustered key and converts each column data to nvarchar and then compares the result against the parameter passed in. As we all know clustered index scan is equal to table scan.

I changed the query and declared the parameter as varchar and ran it. It worked as expected. There was no need to do a data type conversion so SQL Server used Clustered Index Seek operator and completed the operation 4 times faster than the slow query. In my case the table I am talking has 199K rows. Imagine how much slower it could be if it had millions of rows.

I talked to principal architect and the solution was to update Nhibernate mappings. He said that Nhibernate by default maps to nvarchar data type and if you're not using nvarchar for unicode characters in your table you definitely want to map Nhibernate to your tables properly using correct data type.

HTH,

MileHighDBA

SQL Server 2008 R2 Released To Manufacturing

Here is the announcement from Microsoft Data Platform blog that SQL Server 2008 R2 has been released to manufacturing.