Granting Access to users to View System Data without Granting Access to System Objects.

There are precious few scenarios where non-DBA's in my environment have any business looking at system tables.  However, I have seen questions on the forums where someone needs to allow a developer to select information off specific DMV's or system Views/Tables.  With SQL 2005, you can build wrapper stored procedures that execute under the context of the Database Owner Account.  In my environment this happens to be the sa user account for most databases.  So for a user to get information from sys.databases, you can create a stored procedure as follows:

CREATE PROCEDURE uspGetDatabaseInfo
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT * FROM sys.databases
END
GO

Then all you have to do is grant a user execute rights to this procedure.  They can't run a selects against the system table, but they can view the information inside of them.

The Anatomy of a Deadlock

This post has been moved to my new blog site. You can find it at the following link:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx

Identifying Blocked Processes with the Blocked Process Report

If you have query delays from blocking, SQL Server has the Blocked Process Report that can generate as an XML document in Profiler, or as a WMI Alert which will provide the Blocked Spid and process information, as well as the blocking spid and process information.  However, before you can use this, you have to set a blocked process threshold to trigger the report.  By default it is set to 0 or off, but you can enable it with the following:

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 5 ;
GO
RECONFIGURE ;
GO

By using this, you can set a trace for a single event and it will generate a report much like the output of deadlock trace flag 1205/1222.  In fact if you have ever read a deadlock graph from the ErrorLog for either of these trace flags, then the Blocked Process Report will seem really friendly.

Difference between SQL 2000 and SQL 2005 system Views

Part of my job is answering questions for internal and external audits.  Today one came in that made me have to break out some code and do some investigation.  One of the queries I provided had conflicting data with the results from another query.  Basically the first query said that there were objects owned by a database user, while the second said that the database user can't create objects.  This might seem trivial since it is possible that the user once had rights to create objects, only the objects were created since the last audit, and the user never had create rights on the database.

The problem wasn't that the user created objects, it was that the queries being used were returning invalid information in SQL 2005 where they worked correctly in SQL 2000.  As a part of the upgrade to 2005, a new schema was created for DBA use in one of our databases where I copy tables (using SELECT * INTO DBA.TableName_DR_#### FROM TableName) before making changes to them in a Deployment Request, so that there is a rapid rollback point for changes being made in the event of a problem.  In SQL 2000 we would run code like the follow:

SELECT sysobjects.name AS [object Name], sysusers.name AS Owner, CASE 
WHEN sysobjects.xtype = 'S' THEN 'System Table' 
   WHEN sysobjects.xtype = 'P' THEN 'Stored Procedure' 
   WHEN sysobjects.xtype = 'U' THEN 'User Table' 
   END AS Type
FROM sysobjects 
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Where sysobjects.xtype in ('S','P','U')
Order by sysobjects.xtype desc

With schemas this pulls back incorrect users as the owner of objects.  Instead in SQL 2005, the query should look like this:

select o.name, s.name [schema], p.name [schema owner], o.type_desc [Type]
from sys.objects o 
join sys.schemas s on o.schema_id = s.schema_id
join sys.database_principals p on s.principal_id = p.principal_id

In some cases like the above the output from the compatibility is not equivalent.

How to Disable All Constraints or Triggers in a Single Command

From time to time we all make mistakes, no DBA is immune from creating a total disaster with the click of a button.  Luckily today this was on a testing database and not on a production one, but a table hint out of place, resulted in the loss of a 90GB database table in less than a few seconds.  What started as a simple data purge ended up being nothing short of a disaster, albeit on a test database. 

The idea was to purge roughly 80GB of the data from this table, so rather than issue a long running set based delete, I opened the table in the SSMS designer and made a change to one of the columns, then scripted the operation to a new window.  I did this so that I could replace the dynamic SQL Statement with a statement to grab the rows to keep.  The problem was I put the WITH (HOLDLOCK, TABLOCKX) after my WHERE clause.  Since this is dynamic SQL, the syntax will pass a syntax check, but when the code actually executes you get an exception, and no data is copied to the tmp_Tablename table from the original base table, and then the original base table is dropped, leaving you with a wonderfully empty new table.

So on to fixing the problem and the purpose of this post.  To solve the problem, I used an integration package to pull the data from the production database table into a flat file which I then could import back into the testing server.  The problem came when I went to load the data, that check constraints failed, and the load failed.  What to do??  Well, it turns out that disabling all constraints and triggers in database is really simple, you just need to use the undocumented sp_msforeachtable stored procedure as follows:

--Disable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

--Disable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 

-- Load data Now

--Enable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

--Enable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 

this allowed for the data to be loaded, and then I was able to resolve the orphaned records as needed.

Free "Basic Training" for SQL Server

There are often a number of questions that get asked on the forums that are really SQL Server 101 things.  My favorite posts are from people eager to get their start in SQL Server Development and or Administration.  Alot of links have been provided on these posts, and Buck Woody has posted a wonderful series of links for people wishing to strengthen their SQL Server Administration Skill Set on his blog.  

Carpe Datum : Free "Basic Training" for SQL Server

Other Links of interest from working the forums are:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3537678&SiteID=1

SQL SERVER - 2008 - Introduction to SPARSE Columns - Part 2 « Journey to SQL Authority with Pinal Dave

SQL MVP Pinal Dave has a great new series on Sparse Columns in SQL 2008.  I would definitely recommend that you give it a look:

SQL SERVER - 2008 - Introduction to SPARSE Columns - Part 2 « Journey to SQL Authority with Pinal Dave

New Tool, though not for SQL Server

Lately I have been getting information/feedback from visitors to SQLCLR.net that the site is not rendering properly in all browsers.  Looking at the Analytics for the site, most people are coming to the site with IE 7, probably something I should have paid attention to since the site wouldn't render in IE 7 correctly.  I have spent the better part of a week learning CSS, CSS Hacks, and all other forms of Web Development stuff to come to the conclusion that IE is the hardest browser to make render correctly if you have no clue.

Along the way I happened on a neat tool called FireBug.  It is for Firefox and lets you play with the CSS locally from a live rendering of a site.  This is what ultimately let me figure out how to resolve all of the cross platform CSS formatting.  A new skin is now up and is actually better for SEO, since I was able to figure out proper content positioning in the site for non-Styled Rendering with Firefox.  The non-styled layout now allows the search engines to find the content for indexing first and moves all of the Links and Recent Items to the bottom of the page.  This should make for better indexing of the site data for better hits on searching.  Time will tell.

SqlDateTime Overflow after Upgrading to SQL 2005 From SQL 2000

If you upgrade to SQL 2005 by Backup/Restore, it is possible that you will receive the following error when you issue a select statement against a table with a Datetime column in it:

An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

This error is not caught by a standard DBCC CHECKDB.  The books online for DBCC CHECKDB says:

Under some circumstances, values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server 2005 cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error.

Once you follow these directions, your should no longer encounter this problem, since databases in SQL 2005 have data validation checks in place.

For more information see:

Troubleshooting DBCC error 2570 in SQL Server 2005.

SSMS Options in SQL 2008

Glenn Allen Barry posted a neat new feature in the SQL 2008 SSMS options that made me wonder what else has changed, so I decided to do a feature check against the 2005 Management Studio Options.  First and foremost, I am happy to see that Microsoft decided to persist the SQL 2000 Keyboard configuration as an option.  I long ago learned the hot keys to comment code (Shft+Ctrl+C) and uncomment code (Shft+Ctrl+R) in the old query analyzer and old habits die hard, so I was extremely happy when I found this option in 2005.

The next change down the list is the addition of Transact-SQL in the Text Editor Options.  This is where you will control your Intellisense options, and how the editor for TSQL works.

The Editor Tab and Status Bar Options affect what information is displayed in the tab for a specific connection, as well as what information is displayed in the status bar.  Perhaps you only want to show the FileName on the Tab, while you want to show the Server, LoginName, and DatabaseName in the Status bar.  This is now possible with this options set.  You can also move the Status bar from the bottom of the window to the top if you so choose.

Multi-Server Query Execution in SQL 2008

As a DBA, have you ever needed to run a query against one of the system databases in all of your servers? The tedious process of opening a Query in SSMS and then running it, changing the connection and running it again are over. SQL 2008 now has the ability to run multi-server queries. To utilize this new feature, requires at least one registered server group to run a script against multiple server. To setup a registered server group and servers, refer to

http://msdn2.microsoft.com/en-us/library/ms181228(SQL.100).aspx
http://msdn2.microsoft.com/en-us/library/ms183353(SQL.100).aspx

Once you have your group created, you can right click on it and select the New Query Option:

When you do this, some specific environmental changes will occur. First, the Database Dropdown box will only have the common databases to all servers listed in it.

Second, the the status bar will turn from beige to Pink to show that this window is in multi-server mode.

Now we can run a simple database script. For the purposes of demonstration I am going to use the script from the following SQL Examples article:

Find Last BackUp Date Of All Databases On Your Server

What you will notice when your run this command is that you now have an added column in the output, ServerName:

You can configure the output of this columns in the SSMS Options under Query Results -> SQL Server -> Multiserver Results.

What is really nice is that as long as your query can execute on all of the servers, this works with SQL 2000 and SQL 2005 Registered Servers as well. Yet another tool available to ease the tasks as a DBA.

Comparing 2 Results Sets to see if they are identical

A post on the MSDN forums asked how to check if two results sets were identical, and Jim McLeod offered a pretty simple method to check this, that was worth sharing:

SELECT CASE WHEN COUNT(*) = 0 THEN 'Same' ELSE 'Different' END
FROM (
        (
        SELECT * FROM Table1 
        EXCEPT
        SELECT * FROM Table2
        ) 
        UNION 
        (
        SELECT * FROM Table2
        EXCEPT
        SELECT * FROM Table1 
        )
) dv

This query basically gets all the rows that are in Table 1 but not Table 2, then UNIONS all rows that are in Table 2 but not Table 1.  If there's zero rows for both, the result sets must be the same.

Fast Simple, and easy to implement.

TSQL versus CLR Performance: Which is faster, why and when?

I am beginning to work on a series to determine which is faster TSQL or CLR, why, and when to use which. I am going to be working in parallel with some other experts here to make sure that the TSQL and the CLR are both working optimally and doing similar operations to make sure that this testing is valid and covers all of the bases. This is being sparked by the following post on the MSDN forums, and will be posted on SQLCLR.net.

New Database Engine Troubleshooting Feature in SQL 2008 - Extended Events

This is the first that I have seen this feature covered for SQL 2008, and it is definitely an something I am excited to see.  I'll be blogging about this more in the coming days/weeks.

http://blogs.msdn.com/psssql/archive/2008/07/01/get-ready-for-sql-server-2008.aspx

Setting Auto-Recovery/AutoSave in SQL Server Management Studio

A recent forum post asked about how to configure the auto recovery / auto save options in SQL Server Management Studio.  I was surprised to find that you can't do any configuration of this in SSMS.  There are 2 connect feedbacks for this, both closed, and both unresolved.

Auto Save SQL Scripts option in Management Studio Feature Request
How to turn off Auto Recovery in SQL Server Management Studio 2005?

There is a work around published in one of them that will work.  You can edit the following registry key and set the Value of the AutoRecovery to 0(zero).

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover

What is really surprising to me is that this also was not addressed in SQL 2008 as of RC0 either.  I would have expected that this would have been corrected, but have found that it wasn't.