I would backup the database and restore it using the WITH MOVE option. For the sake of illustration however, you could use SQLCMD to run a script as follows (assuming you are working in an environment that allows xp_cmdshell to be run, AND assuming that the SQL Server service is running under an account with the rights to move the files on the operating system (big IFs in most places))…

Script file contents:

declare @cmd varchar(2000);
select 'drop connected users'
while (select count(*) from [master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’))>0
begin
select @cmd=’kill ‘+CAST(spid as varchar(5)) from [[master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID(’$(database)’);
exec [dbo].[sp_executesql] @cmd;
end
select ’setting database to SINGLE_USER mode’
alter database $(database) set SINGLE_USER;
select ‘updating database settings to move file’
alter database $(database) modify file (name=’$(logfilename)’, FILENAME=’$(newlogpath)’);
select ‘detaching database’
exec [master].[dbo].[sp_detach_db] @dbname = N’$(database)’, @keepfulltextindexfile=N’true’;
select ‘moving file’
exec [dbo].[xp_cmdshell] ‘move “$(originallogpath)” “$(newlogpath)”‘;
select ‘re-attaching database’
create database [$(database)] on (filename = N’$(datafilepath)’), (filename = N’$(newlogpath)’) for attach;
go

Execute syntax:

sqlcmd
-S <instance name>
-d master
-E
-i <script name where the code above was saved>
-v database =”<database>” logfilename=”<current log file>” newlogpath =”<new log file path>” originallogpath =”<original log file path>” datafilepath =”<data file path>”
-o <output file>

There are a number of articles (like this one) explaining how to do this for SQL Server 2000, but it’s so much easier in SQL Server 2005 that I’ll post the 2005-compatible code here. You should read this Microsoft Research article to get the Microsoft best practices justification for storing various file sizes in the database versus on the filesystem. Note, the following code sample is extremely simplistic and various iterations of similar logic are available from Microsoft and others.

if exists(select * from sysobjects where type=N'U' and name=N'picTable')
drop table dbo.picTable;
go
create table dbo.picTable(FileName sysname, Document image)
go
if exists(select * from sysobjects where type=N'P' and name=N'spLoadImagesFromLocalFolder')
drop procedure dbo.spLoadImagesFromLocalFolder;
go
create procedure [dbo].[spLoadImagesFromLocalFolder](@directory sysname,@fileextention nvarchar(10))
as
begin
declare @cmd varchar(8000),@fname nvarchar(200),@sqlstmt nvarchar(500),@ParmDefinition nvarchar(500),@cnt int

if object_id(’tempdb..#filestoload’) is not null
begin
drop table #filestoload
end
create table #filestoload ( RowNum int identity, fname varchar(8000) null)

SET @cmd = ‘dir ‘ + QUOTENAME(@directory,’”‘) + ‘ /T:W /A:-D /b’
insert into #filestoload ([fname])
exec [master].[dbo].[xp_cmdshell] @cmd
/* print @cmd */
delete from #filestoload where fname is null or lower(fname) not like N’%.’+lower(@fileextention)
select @fname = ”

select @cnt=count(*) from #filestoload
set @ParmDefinition = N’@fname nvarchar(200)’;
while @cnt>0
begin
select @fname = fname from #filestoload where RowNum > @cnt
set @sqlstmt=N’INSERT INTO picTable(FileName, Document) SELECT ”’+@fname+N”’ AS FileName,* FROM OPENROWSET(BULK N”’+@directory+@fname+N”’, SINGLE_BLOB) AS Document’
/* print @sqlstmt */
exec sp_executesql @sqlstmt,@ParmDefinition,@fname=@fname
select @cnt=@cnt-1;
end
drop table #filestoload;
end
go
exec [dbo].[spLoadImagesFromLocalFolder] N’C:\’,N’JPG’

Hope that helps!

In today’s installment of the Quest Pain of the Week webcasts, Jason Hall and I will be discussing a reader’s question about finding missing clustered indexes, their impact on performance, and how to be proactive about monitoring performance to find those types of design issues.

MSSQLTips.com has a great post called “Finding primary keys and missing primary keys in SQL Server.”  That post includes two snippets of code, one for each of the popular SQL versions:

Finding missing primary keys in SQL 2005:

SELECT c.name, b.name
FROM sys.tables b
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id
WHERE b.type = ‘U’
AND NOT EXISTS
(SELECT a.name
FROM sys.key_constraints a
WHERE a.parent_object_id = b.OBJECT_ID
AND a.schema_id = c.schema_id
AND a.type = ‘PK’ )

Finding missing primary keys in SQL 2000:

SELECT c.name, a.name
FROM sysobjects a
INNER JOIN sysusers c ON a.uid = c.uid
WHERE xtype = ‘U’
AND NOT EXISTS
(SELECT b.name
FROM sysindexes b
WHERE a.id = b.id
AND (b.status & 2048)<>0)

You can read their post for similar queries on finding tables WITH primary keys too.

Also in today’s webcast, I’ll be showing a few screenshots from Microsoft’s SQL Server Performance Dashboard Reports.  They’re a free set of SSRS reports that are integrated into SQL Server Management Studio.  The reports I’ll be showing in the webcast are somewhat limited in that they only show data that’s currently in the plan cache.  If your server is under memory pressure, it may not retain this data for too long, and you would have to revisit these reports pretty often to get a good picture of what’s going on under the hood.

For more information about the dashboard and how to use it, check out Kevin Kline’s Tool Time article “SQL Server 2005 Dashboard Reports” in SQL Server Magazine’s August 2007 issue.

First things first - depending on the version of SQL Server that you’re using, SQL Server could be creating this problem for itself. See these articles if you’re running SQL Server 2005 pre-SP2:

• SQL Server becomes sluggish or appears to stall on 64 bit installations
• A significant part of sql server process memory has been paged out. This may result in performance degradation
• How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
• The SQL Server Working Set Message

So what’s going on? First we need to address two concepts: Virtual Address Space and Working Set. Windows uses virtual address space (VAS) to allocate memory to applications (that’s as deep as I’m going here). A program’s working set is the collection of VAS pages that have been recently referenced. When a process is created, it is assigned a minimum and maximum working set size. As the working set size increases, thereby increasing memory demand, the virtual memory manager will attempt to keep up to the maximum working set size (memory) resident without exceeding the maximum threshold.

One common misconception is that there is as much virtual memory available to a process as there is physical memory (RAM) on a machine. Not so (or I wouldn’t be going there); you can get much more depth by reading Slava OKs’s blog on VAS. One very important point from a SQL Server perspective, however, is that when you’re configuring min and max server memory, you have to take into account the MemToLeave area. MemToLeave is VAS that’s left unused when SQL Server starts so that external components have address space to consume when they’re loaded. Typically, the majority of memory allocations from MemToLeave are from non-SQL Server memory consumers like COM objects, extended stored procedures, and linked servers. You can use DBCC MEMORYSTATUS to see these, but bear in mind that what’s reported will only reflect allocations made by SQL Server directly (SQL Server has no knowledge of outside memory requests).

So, when troubleshooting out of memory errors, do your homework.

• Check the list above to see whether your SQL Server is suffering from a known SQL Server bug, and understand (at least a little bit about) VAS and memory allocations.
• You can use Performance Monitor (Task Manager won’t help here) to get an idea of how much of VAS is currently consumed inside of your process by looking at the virtual bytes counter.
• If you believe that a 3rd party application is loading DLLs into SQL Server’s VAS, or you want to see if your own CLR or external procedures are doing so use SQL Server’s DMV sys.dm_os_loaded_modules to see what’s been loaded into SQL Server’s address space.

Oh, and by the way, for anyone who’s used to configuring the working set size parameter with sp_configure, you need to be aware that it’s been deactivated in SQL Server 2005.

Hope that helps.

More: Since there are 3 machines in this mix so I’m not sure which one I should be checking. Computer A is running Windows 2000 and SQL Server 2000; computer B is running Windows XP Professional and Oracle Server 10.2.0.2; computer C is running Windows XP Professional with SQL Server 2005 client tools (Management Studio) and Oracle 10.2.0.2 client. I am using Computer C to transfer data from Computer A to Computer B. To confuse me even more, using computer D, which has SQL Server 2000 client tools, I can transfer the data from Computer A to Computer B with no problem. Doesn’t that mean A and B are configured correctly?

You need to address computer C in the scenario you outlined as that version of MDAC/ODBC/Oracle client is being used. There is likely a mismatch between the SQL Server 2005 tools and your linked server. I would try the following:
1) ensure computer C is running the latest version of MDAC and an ODBC driver that fully supports Oracle 10g
2) check that you’ve got the latest version of the Oracle client on the linked SQL Server machine. Some customers have said that they require patch 5203839
3) ensure you’ve configured the connection to “Allow inprocess” in the provider options

Finally, check out these MSDN links to ensure your Microsoft software is completely up-to-date for this scenario:
http://support.microsoft.com/kb/280106
http://msdn2.microsoft.com/en-us/library/ms190618.aspx
http://msdn2.microsoft.com/en-us/library/ms189063.aspx

Hope that helps.

One of the best things about becoming a Microsoft MVP is meeting other MVPs.  I bring this up because last week was the annual MVP Summit in Seattle, WA.  I was really looking forward to meeting Steve Kass.  Steve Kass is one of the smartest SQL Server MVPs I’ve encountered, especially when it comes to SQL questions.  A while back, I noticed that Steve made an interesting recommendation for a hash function that you could use for partitioning that I thought was worth noting.  A hash function would be very useful if you wanted to implement your own variation of a range partition using a hash function rather than the standard sort of range partitioning where colA values of A-H go to partition 1, values of I-P got to partition 2, and so forth.

Steve notes that you could use the following for hashing something small in size:

   CAST(    SUBSTRING(      HASHBYTES(’SHA1′,         CAST(my_col AS NVARCHAR(appropriate_size))),8,1) AS tinyint) 

This is just an off the cuff recommendation from Steve and might need some fine tuning, for example, the CAST might throw off persistence.  However, it’s a good start.

Thanks, Steve, for sharing this and thanks, readers, for sharing any improvements you might develop out in the field.

Technorati Tags: partitioning

There’s either a network issue with regard to that server’s DNS, or you’re having an issue that’s addressed by KB article 319723, particularly section 3: Configure the SQL Server service to create SPNs dynamically.

Let’s discuss how SQL Server integrated security works to understand what’s happening. When the SQL Server driver attempts to connect it tries to resolve the fully-qualified domain name of the SQL Server using WinSock APIs (look up gethostbyname and gethostbyaddr for details) to determine the Service Principal Name (SPN). SQL Server is using Kerberos over TCP-IP sockets via Security Support Provider Interface (SSPI) to resolve the SPN. If there is a problem verifying the fully-qualified domain name from the client or server side during this exchange, the connection fails. You can see this yourself when/if the problem occurs using NSLOOKUP from the client machine.

Hope that helps.

First, the quick answer to your question: use SQL Server Profiler, or the SQL server 2005 database management views (DMVs) for this type of problem. PerfMon can help, but not directly.

As Kevin’s recent posts illustrate, there is certainly no shortage of resources out there that can help you use PerfMon to performance tune your application environment. I’m not going to go into which counters you should use because there’s a limit to what PerfMon can do. Still, it’s a valuable tool, but you should use it more to:

1. Create a performance baseline
2. Determine how your application interacts with the operating system
3. Tune database and instance-level configurations.

In your case, unless the problem query is the only statement running PerfMon won’t [directly] help you here; SQL Server Profiler is better suited to this task, but bear in mind that the performance overhead of tracing statement executions is high (read: be careful when using Profiler to trace SQL executions as it can adversely affect instance performance).

If you’re using SQL Server 2005 you can use the database management views (DMVs) to find more information. Specifically, you can use the sys.dm_exec_query_nnnn views to see cached query execution specifics. I would start with sys.dm_exec_query_plan to see the statement’s plan. Using the XML Showplan information in this view you can determine whether the statement is being recompiled, if it’s performing table scans, and glean lots of other supporting information to see what your next steps should be.

Quest and others have tools that take the guesswork out of this investigation, but I have also posted a number of articles on this site explaining how to leverage these tables to get to the bottom of your bottleneck if you want to roll your own solution. You should also make use of the information in the following publications to ensure you’re aware of what Profiler and the DMVs are telling you:

• SQL Server I/O Basics
• SQL Server I/O Basics Chapter 2
• Troubleshooting Performance Problems in SQL Server 2005
• Statistics Used by the Query Optimizer in Microsoft SQL Server

What you’re asking is partially covered in the Books Online topic entitled Rollbacks and Commits in Stored Procedures and Triggers - the article is worth reading if you haven’t looked at it before. Having said that, I’m not a huge fan of the advice provided because it’s rule-of-thumb-esque; it ignores the fact that in some cases business rules are business rules and “imperfect” code needs to be implemented. Theoretically you shouldn’t ROLLBACK within a trigger. So, just like “If it hurts when you do that, don’t do that” if you don’t want the client to see the transaction count message don’t rollback in the trigger. By the way, whatever you do, DO NOT open a nested transaction within the trigger just to make the error go away, i.e. don’t implement a worse practice to buffer the consequences of a not-the-best practice.

Anyway, so far we’re not having any fun…so let’s leverage a cool feature implemented in SQL Server 2005 to solve your problem. Using TRY…CATCH you can custom-tailor your error message like so:

/* Setup my lame-named table and trigger */
IF EXISTS(SELECT * FROM sysobjects where type='U' and name='MyTable')
DROP TABLE MyTable;
GO
CREATE TABLE MyTable([id] int,[desc] nvarchar(500));
GO
IF EXISTS(SELECT * FROM sysobjects where type=’T’ and name=’MyTableTrigger’)
DROP TRIGGER MyTableTrigger;
GO
CREATE TRIGGER MyTableTrigger ON MyTable AFTER INSERT
AS
BEGIN
IF (SELECT COUNT(*) FROM MyTable)=2 AND @@TRANCOUNT>0
BEGIN
ROLLBACK TRAN;
– RAISERROR(’This is my triggered error message’,16,1);
END
END
GO
/* Open a transaction */
BEGIN TRAN
BEGIN TRY
INSERT INTO MyTable SELECT TOP 2 [id],[name] FROM sysobjects;
END TRY
BEGIN CATCH
IF (SELECT ERROR_NUMBER()) IN(3609,3616)
RAISERROR(’This is my coded error message’,16,1);
ELSE
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
IF @@TRANCOUNT>0
COMMIT TRAN;

Running that code in SQL Server will return your custom message (remove the comment in the CREATE TRIGGER section to display a different error). So, there are two ways to get what you’re looking for. Oh, and if you’re thinking, “that’s great, but the INSERT is being executed by code” just wrap the INSERT in a stored procedure and have your code call the procedure - you’ll be implementing a certifiable best practice and increasing the likelihood of query plan reuse at the same time.

Hope that helps!

Fragmentation: The scattering of parts of a file throughout a disk, as when the operating system breaks up the file and fits it into the spaces left vacant by previously deleted files.

That’s how the dictionary defines fragmentation, which can be a very real performance problem for certain databases. But when is fragmentation truly a problem, and how can you deal with it? The folks posting on the SQL Server Storage Engine blog have a nice series on fragmentation that can help debunk some myths and show you how to evaluate and fix problems once they arise.

On that note, Kalen Delaney has also posted some good information on why going to lengths to defragment system tables is often effort best expended elsewhere. See her posts on this subject here and here.

Finally, you can read the MSDN article Microsoft SQL Server 2000 Index Defragmentation Best Practices for the official word on the subject.