SQLblog.com - The SQL Server blog spot on the web

Be Aware of These Loopholes in Your Referential Integrity

Alexander Kuznetsov — Tue, 13 May 2008 22:17:00 GMT

I am going to discuss several potential problems with referential integrity, because they keep coming up in the newsgroups.

Parent and Child Tables Are in Different Databases.

Although you cannot use a foreign key in this situation, there are workarounds – you can use either triggers or UDFs wrapped in check constraints. Either way, your data integrity is not completely watertight: if the database with your parent table crashes and you restore it from a backup, you may easily end up with orphans.

Parent-Child Relationship Is Enforced by Triggers.

There are quite a few situations when triggers do not fire, such as:

· A table is dropped.

· A table is truncated.

· Settings for nested and/or recursive triggers prevent a trigger from firing.

Also a trigger may be just incorrect. Either way, you may end up with orphans in your database.

Parent-Child Relationship Is Enforced by UDFs Wrapped in Check Constraints.

Such “constraints” do not prevent from deleting parent rows. Again, you may end up with orphans in your database.

In most cases the easiest and simplest way to enforce a parent-child relationship is to use a built-in tool – do not reinvent the wheel, have both tables in one database and use a foreign key.

SQL Server 2008 Page Compression: Performance impact on inserts

Linchi Shea — Tue, 13 May 2008 02:49:00 GMT

Before I begin, let me be very clear that the results I report in this post were obtained with SQL Server 2008 CTP6. Microsoft is still working on improving the performance of data compression. And in particular, the performance of inserting into a compressed table is expected to be significantly enhanced in SQL Server 2008 RTM.

In my previous post, I have shown that data compression can result in significant savings in storage space. In this post, I want to highlight the fact that not everything comes for free. Let's take a look at inserting into a B-tree table, i.e. a table with a clustered index. What is the performance impact of page compression on inserts?

Note that you must use TABLOCK hint for pages to receive page-level compression when the pages of a heap table are being populated with the INSERT statement. So, heap tables are much less interesting, and I'll hence focus only on B-tree tables.

In my tests, I ran the following script:

truncate table customer2;
-- enable page compression
alter table customer2 rebuild with (data_compression=page);
go
declare @dt datetime;
select @dt = getdate();
insert customer2 select * from customer;
select 'insert into customer2 with compression', datediff(second, @dt, getdate());
go
truncate table customer2;
-- disable compression
alter table customer2 rebuild with (data_compression=none);
go
declare @dt datetime;
select @dt = getdate();
insert customer2 select * from customer;
select 'insert into customer2 without compression', datediff(second, @dt, getdate());
go

The script measures the difference in the elapsed time of inserting 3,000,000 rows into (1) an empty table when page compression is enabled, and (2) an empty table when no data compression is enabled. The script was run multiple times to ensure that the elapsed times recorded were consistent. The following chart shows the results:

Clearly, the impact of page compression on inserts was rather significant in the tests. Without data compression, inserting 3,000,000 rows took ~80 seconds, whereas it took 217 seconds when page compression was enabled on the target B-tree table. No free lunch!

Again, these numbers were obtained with SQL Server 2008 CTP6, a work in progress build. I'll re-run the test once SQL Server 2008 RTM is released.

Now that I have shown that there can be performance penalty in using data compression, next time I'll show that data compression can also help improve your performance.

Visual Studio 2008 SP1 Beta

andyleonard — Mon, 12 May 2008 18:58:00 GMT

ScottGu has a great post about enhancements in Visual Studio 2008 Service Pack 1, released today in Beta.

Of particular interest to database developers: SQL Server 2008 support, a built-in Entity Data Modeler, and REST support!

:{> Andy

Today is your last chance to submit questions for Erland Sommarskog and Craig Freedman

Denis Gobo — Mon, 12 May 2008 14:47:00 GMT

Time is almost up; this is the last chance to submit your questions to Erland Sommarskog and Craig Freedman. I mean, you don’t want me to come up with all the questions myself do you? That would be considered cruel and unusual punishment.

SQL Server 2008 Page Compression: Compression ratios with real-world databases

Linchi Shea — Sun, 11 May 2008 23:20:00 GMT

In my previous post on data compression, I looked at how rebuilding a table with page compression works with multiple processors via the MAXDOP option. In this post, I'll focus on what compression ratios I have seen in the real-world databases. Now, if you understand how SQL Server 2008 data compression works, you know that what compression ratio you may get really depends on to what extent SQL Server can find duplicate values on the data/index pages. Consequently, one real-world database can have a very high compression ratio while another may have a very low compression ratio: there is no typical compression ratio to expect.

That said, it is still interesting to report what compression ratios I have seen with real customer databases instead of 'cooked' data. Before I give the results, let me define what I mean by compression ratio:

Compression Ratio = Uncompressed Size / Compressed Size

To see what kind of compression ratios I may run into with real-world databases, I randomly selected 14 customer databases. IN this blog post, these databases have been renamed to DB1, DB2, ..., DB13, and DB14 to shield their identities. For each database, the following steps were performed:

Record the data size of sp_spaceused for the database. This is the uncompressed data size.
For each user table in the database, rebuild it with page compression. This was done by generating and executing a script that contains the following ALTER TABLE statement for each table:
alter table <the_table> rebuild with (data_compression=page, maxdop=8);
Record the data size of sp_spaceused for the database. This is the compressed data size.
For each user table in the database, rebuild the table with no compression, which effectively decompresses the table. This was done by generating and executing a script that contains the following ALTER TABLE statement for each table:
alter table <the_table> rebuild with (data_compression=none, maxdop=8);
Record the data size of sp_spaceused for the database. This is the decompressed data size.

The data size obtained in Step 3 was used as database compressed size, and the data size obtained in Step 5 was used as the database uncompressed size. The data size obtained in Step 1 was not used because it may be polluted by fragmentation. If we used the data size from Step 1, we could potentially be using a size that was inflated by fragmentation, resulting in an incorrectly higher compression ratio.

The following two charts summarize the results:

The top chart includes the three database space measures mentioned above. As you can see, fragmentation was not a significant factor for these databases since the uncompressed size and decompressed size were quite close for each database. As it turned out, these databases were regularly maintained, including rebuilding clustered indexes, it's no surprise that there wasn't any serious fragmentation.

To highlight the compressed ratios for these databases, the bottom chart plots the compression ratios. So for these databases, the compression ratios--obtained using SQL Server 2008 CTP6 page compression--vary between 1.7 and 5.6.

The space savings were substantial with these databases. When decompressed, the total space usage was ~261GB. Compared that to the total space usage of ~88GB when compressed. In other words, SQL Server 2008 page compression would save us 173GB in storage space.

Cisco VPN Client and Vista x64

andyleonard — Fri, 09 May 2008 04:10:00 GMT

I am disappointed in Cisco.

I have Vista Ultimate x64 installed on my snappy new Red laptop but I cannot install Cisco VPN Client software on it because Cisco VPN Client does not run on Vista 64-bit platforms.

Digging around for a work-around, I came across a couple interesting comments. One comment, from someone who claims to be with Cisco VPN Client Support, states:

"...as mentioned many times on this thread, NO x86-64 support for Windows. Cisco IPSec client will NOT be ported to support 64bit version of Windows now or in the future. If you require 64bit support on Windows please look at migrating to AnyConnect."

Does Cisco think 64-bit OS's are a passing fad? Are they holding out for the 128-bit operating systems before bothering to release an upgrade? What's the logic behind such a move? There has to be some logical explanation...

Release Notes for AnyConnect version 2.2 indicate something new (hardware?) is required to work with 64-bit Vista, but it's entirely possible I'm misreading the notes. I do not know what an Adaptive Security Appliance is.

Other applications install and run in 32-bit mode. Is there some reason - security-related or other - that the Cisco VPN client cannot run in this mode?

I'm forced to create a virtual PC with a 32-bit OS installed just so I can communicate with clients remotely. It is, as I wrote earlier, disappointing.

:{> Andy

Kevin Hazzard on LINQ To SQL

andyleonard — Fri, 09 May 2008 03:54:00 GMT

Kevin Hazzard did an outstanding job presenting to the Richmond SQL Server Users Group this evening on LINQ To SQL!

I really like the ORM / code generation aspects of this new feature of the .Net Framework 3.5. Seeing it in action made me yearn (a little) for my application developer days.

Kevin's VPC crashed a couple hours before his presentation so he recreated the presentation and three demos in 1.5 hours before the meeting - and they were not simple demos, especially the last one.

It's always cool to watch technology in the hands of a master.

:{> Andy

SSMS Tools PACK 1.0 is now available

Denis Gobo — Thu, 08 May 2008 19:25:00 GMT

Mladen Prajdić just released SSMS Tools PACK 1.0. SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express.

Here are some of the features

Uppercase/Lowercase keywords.
Run one script on multiple databases.
Copy execution plan bitmaps to clipboard.
Search Results in Grid Mode and Execution Plans.
Generate Insert statements for a single table, the whole database or current resultsets in grids.
Query Execution History (Soft Source Control).
Text document Regions and Debug sections.
Running custom scripts from Object explorer's Context menu.
CRUD (Create, Read, Update, Delete) stored procedure generation.
New query template.

Check it out here: http://www.ssmstoolspack.com/Main.aspx

How to log when a function is called?

Denis Gobo — Thu, 08 May 2008 17:25:00 GMT

This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb

--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go

--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go

--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

Now call the function a couple of times

SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)

And look inside the table

SELECT * FROM LogMeNow

What if you were to run this?

SELECT dbo.fnBla(4),* FROM sys.sysobjects

See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!

I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

SQL Teaser: @@ROWCOUNT

Denis Gobo — Thu, 08 May 2008 14:50:00 GMT

Without running this what do you think will be printed?

SET ROWCOUNT 0
DECLARE @ int
SET @ =6
IF @@ROWCOUNT = 1
PRINT 'yes'
ELSE
PRINT 'no'
PRINT @@rowcount

SQLblog: New SQL Server Forum is now available

Peter DeBetta — Thu, 08 May 2008 07:01:41 GMT

After numerous questions and requests, we decided to create a forum for all SQL Server related questions. You can view the new forum at http://sqlblog.com/forums/57/ShowForum.aspx or use the Forums menu item on the site to browse there. We hope this new addition to the SQLblog community provides the members a valuable benefit and look forward to seeing your forum posts.

On Developer Communities: A Sponsor's Case Study

andyleonard — Thu, 08 May 2008 03:01:00 GMT

Introduction

I wrote a series of posts recently about the developer community. I started each post with a linked summary of the previous posts, which I will continue here:

On Developer Communities...

I hold the following hypotheses about successful, growing, and thriving developer communities:

A Sponsor's Perspective

I recently emailed one of our sponsors to ask them "What do you get out of this?" With his permission, I share the response from Brock Barnett of MaconIT, a direct hire staffing and consulting firm in the Richmond, Charlottesville, and Roanoke Virginia markets:

"MaconIT has been involved with the Richmond .NET and Richmond SQL Server User Groups for a couple of years. Over the past two years we've become Platinum sponsors and our investment (both time and money) has been well worth it. Our involvement with the User Groups has allowed us to get introduced to new clients, potential new candidates, learn cutting edge technology and expand our name recognition in the area. The committment that the User Groups have made to Richmond have also helped to promote the growth of Microsoft products in the area which in turn has helped to keep the local IT market very dynamic. While it's sometimes difficult to measure ROI, we feel that our sponsorship of the User Groups has been vital to our growth. We feel very fortunate to have been a part of the .NET and SQL Server Groups and look forward to continued invovlement!"

Thanks to Brock, Gregg, and Carin at MaconIT and dozens of companies in the Richmond market, the Richmond Developer Community is experiencing record participation, growth, and event attendance. We appreciate all our sponsors do for us!

Conclusion

I believe successful user groups are part of the local business ecosystem. We fill a niche by providing free training to folks who are interested enough to give up an evening or day of personal time to attend an event. These people are the passionate developers (or developers-to-be) that companies like MaconIT want to represent in the market. And, believe it or not, schmoozing with the local developer community is an excellent networking opportunity. At each meeting, someone is in attendance looking for a developer or DBA. Cards and email addresses are exchanged and announcements are made.

It's really cool of local technology companies like MaconIT to participate in our endeavors. It's even better that they realize tangible results for their participation. I love win-win scenarios!

:{> Andy

On Technical Leadership: Things Get Easier

andyleonard — Wed, 07 May 2008 18:00:00 GMT

Introduction

A long time ago in a place not so far away from Farmville, I learned Motorola 6800 machine code. It wasn't easy but with patient instruction from my neighbor, I was soon making things happen on the computer screen and in memory. I was hooked!

Fast Forward 3.3 Decades...

I'm still hooked! That thrill was and remains my motivation for doing this work. I've learned it's contagious and I think that's a good thing.

I now use higher-level programming languages to accomplish screen and memory interaction, but it's the same thrill when it fires up and runs. I've noticed a trend over the past three decades or so: interacting with the machine is now easier. It's not just that I can do more in less time - the things I can do are easier to learn (for the most part) while being more complex, flexible, and powerful.

I am reminded of a scene from a Star Trek movie (Star Trek IV - I'm talking old school Star Trek here) where Scotty interacts with a 1984-era desktop. At first he speaks to the computer. When the computer doesn't answer Dr. McCoy hands him the mouse, which Scotty then speaks into as if it's a microphone. It's a funny scene and one that makes sense. The rest of the scene departs from the reality expressed in the first part as Scotty cracks his knuckles and begins frantically typing at the keyboard to ultimately reveal the molecular structure of tranparent aluminum.

The reason I call this a departure? Things get easier. Scotty could no more return to 1984 and interact with a program than you or I could travel back in time to the mid-1940's and program ENIAC.

And We Liked It!

Things were different when I learned M6800 hex. I had to learn about registers and accumulators and bit-shifting - things that still occur inside the CPU but that we rarely have to think about to develop software these days. Why? We use higher order languages.

As mathematics gives way to geometry and algebra, and then to the Calculus, our knowledge of software development has built upon itself as more powerful and more complex generations of programming languages have evolved. Gone (mostly) are the days of punch cards and keying base 16 numbers - which, believe it or not, were a vast improvement over previous methods.

Even though some of us grumpy old men may have liked it that way, things changed.

Things Got Better

Abstraction allowed us to do more. It allows us to manage (or at least mask some of) the complexity of software development. It also allowed us to do it faster. There's a natural progression from simple-and-less-functional to complex-and-more-functional. We're surrounded by it in nature. It's here and it's not going anywhere soon.

Grow with it or be overgrown.

It's the law of nature we inherited, overloaded, and extended to use in software development. It will not change.

That doesn't mean it's perfect - it's not perfect. Joel Spolsky (very effectively) argues abstractions leak and leaky abstractions ultimately slow us down and add work.

In The Box

So how can I say things got better? Allow me to qualify that statement: Generic things got better.

So long as one remains within the confines defined by good people in charge of the abstraction in the first place, things will usually go well. It's when one approaches (or crosses) the edge that stuff gets all whacky (that's the technical term). Stay within the box or prepare to slay the dragons.

Should abstraction work this way? That's open for debate, in my opinion. The fact that it is - and finding some way to effectively deal with it - is a more productive discussion.

How Not To Do It

As a consultant I visited a shop that had experienced a recent turnover in their database department. The new team was in place and they were committed and eager and excited to set things right about the previous crew's work.

Now.

If you walk into a situation like this as a consultant, several red flags should be flapping loudly as they are hastily raised in an increasingly turgid breeze in your consulting brain. If you find yourself sitting in an interview and the interviewer says something along the lines of "Everyone quit," several questions should leap to mind, including:

Why did everyone quit?

But I digress...

I was tasked with converting a process from an older platform to a newer platform. The older platform was poorly documented. Which is to say there was nothing written down, but there did exist a screencast recorded by one of the developers of the application - no doubt after he'd submitted a notice - containing a rambling explanation that most likely made perfect sense to anyone who built the application in the first place, but did very little for someone walking in the door with no experience using the application.

So I asked one of the fresh new team members for help. The response: "Have you seen the video?"

It's difficult to grasp the tone of this response when written as above. So let me add the additional message that was being communicated: "When I started this position n months ago this was all I had and I hated that I did not have more to go on, but I also ignored the large collection of red flags during the interview process and found myself stuck in this job with no net after leaving my last position. And now you walk in with your fancy I'm-here-to-save-the-day attitude and high hourly rate and you expect me to share anything I've learned with you? Ha!"

Did I mention this was a fun gig?

My point is this: You don't do anyone any favors by amplifying the inherent difficulties of abstraction. You don't personally benefit from it, and neither does anyone else.

Footnote: All members of the "new" team at this location have either moved on or are about to.

How To Do It

The way to overcome the inherent difficulties of approaching the boundaries of abstraction is to understand the stuff that's being abstracted.

"But Andy," you ask, "doesn't that undo the benefit of abstraction in the first place?" I'm glad you asked. It certainly can, but this can be mitigated. How? In stark contrast to the exchange above, team communication and collaboration is one way.

Another way is to only hire people who know everything. This second option can be pricey, but can also be worth the price. To quote Andy Warren, "It depends."

Conclusion

Regardless of how we choose to deal with the overhead associated with managing abstraction, manage it we must. Even with its inherent difficulties, it is the way we will progress for the forseeable future.

:{>

Should SQL Server Have The CREATE [OR REPLACE] PROCEDURE Syntax?

Denis Gobo — Wed, 07 May 2008 15:53:00 GMT

I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage

First the advantages

Advantage
When scripting out a database you don’t have to generate if exists.....drop statements

When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)

Disadvantage
I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.

Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either

So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?

Some things don't change - DB Snapshots & Backups of Snapshots

RickHeiges — Wed, 07 May 2008 14:40:00 GMT

Again, I wanted to blog about something that still does not change between 2005 and 2008. At a recent conference, there were some great questions/ideas that should make it to Connect if they haven't already.

When DB Snapshots were first introduced, one of the questions that I always heard was "Can I perform a Backup on the Snapshot?".

Well - does this mean tht you can actually perform a SQL Backup of the DB Snapshot? No - you can't do that. Can I backup the DB Snapshot file with Windows Backup? Yes, you can do that, but there is no way to "re-attach" a DB Snapshot file to a database. So why bother?

DB Snapshots are still available on a Mirrored DB. We find that many people like this feature because of the ease of setup/management, but they do not like the fact that it is still an Enterprise Edition feature.

New England SQL Users Group & Craig Freedman

Andrew Kelly — Tue, 06 May 2008 19:50:23 GMT

Adam Machanic has already blogged about Craig's visit to New England coming up on May 8th but I wanted to re-iterate some points for the benefit of the folks planning to attend. Due to the large crowd expected we really need you to RSVP if you plan to attend to ensure we have enough chairs and Pizza for everyone:). Red Gate Software is sponsoring the event and it would be great to get the head count as close as possible to maximize the event potential. They made it possible to get a great speaker like Craig as out guest. See the link below if you need more info on how to RSVP or just want more details.

http://sqlblog.com/blogs/adam_machanic/archive/2008/05/02/new-england-nesql-special-meeting-featuring-craig-freedman.aspx

Thanks and hope to see you there...

What Would You Like To Ask Erland Sommarskog?

Denis Gobo — Tue, 06 May 2008 19:39:00 GMT

In the Who do you want to see interviewed next? blog post I asked for some names of people who YOU would like to see interviewed. Erland Sommarskog's name was submitted in comments a couple of times. I contacted Erland and am happy to anounce that he has agreed to do this.

The first time I heard of Erland Sommarskog was in the SQL Server programming newsgroups. I also read all the articles on his site. When I answer questions I usually give the link out to these 4 articles

Arrays and Lists in SQL Server

Implementing Error Handling with Stored Procedures

Error Handling in SQL Server – a Background

and of course The curse and blessings of dynamic SQL

And from Hugo's comment Dynamic Search Conditions and How to share data between stored procedures

I always wondered why he didn't write a SQL book. Hey, now I can ask him that :-)

Before you think of any questions please visit Erland's page first (http://www.sommarskog.se/index.html) and read some of the articles he wrote.

You can submit questions until Monday May 12th and then I will forward the questions to Erland.

What Would You Like To Ask Craig Freedman?

Denis Gobo — Tue, 06 May 2008 18:03:00 GMT

In the Who do you want to see interviewed next? blog post I asked for some names of people who YOU would like to see interviewed. Craig Freedman's name was submitted in comments a couple of times. I contacted Craig and am happy to anounce that he has agreed to do this.

Before you think of any questions please visit Craig's blog first(http://blogs.msdn.com/craigfr/) to get a feel of what kind of questions to ask. It would make sense to keep the questions focused on query processing, query execution, and query plans. I told Craig that if he doesn't like a question that he does not have to answer it.

You can submit questions until Monday May 12th and then I will forward the questions to Craig

Do you depend on sp_depends (no pun intended)

Denis Gobo — Tue, 06 May 2008 16:50:00 GMT

I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this

First create this proc

CREATE PROC SomeTestProc

SELECT dbo.somefuction(1)

now create this function

CREATE FUNCTION somefuction(@id int)

RETURNS int

BEGIN

SELECT @id = 1

RETURN @id

END

now run this

sp_depends 'somefuction'

result: Object does not reference any object, and no objects reference it.

Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT specific_name,*

FROM information_schema.routines

WHERE object_definition(object_id(specific_name)) LIKE '%somefuction%'

AND routine_type = 'procedure'

BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character

Use a SQL Agent Proxy for Special Tasks

AllenMWhite — Tue, 06 May 2008 11:34:00 GMT

When SQL Server Agent runs its tasks it normally does so using the context of the service account you assigned to the Agent service. This may or may not be the best context for your application, so you can set up a Proxy. This allows you to run the task under the context of the account you set up in the proxy, and it's pretty easy to do.

First, set up a Credential. This defines the ADS login credentials the proxy will use. You can do this under the Security tree in Object Explorer, or you can set it up in Transact-SQL like this:

CREATE CREDENTIAL [cred_MyCredential] WITH IDENTITY = N'MyDomain\MyLogin', SECRET = N'S0meComp1exP@ssw0rd'
GO

Once the credential exists then you define the proxy. Expand the tree under SQL Server Agent/Proxies, select the job task type you plan to use the proxy for, then right-click and select New Proxy, or use this Transact-SQL:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'proxy_MyProxy',@credential_name=N'cred_MyCredential',
		@enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'proxy_MyProxy', @subsystem_id=3
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'proxy_MyProxy', @msdb_role=N'SQLAgentUserRole'
GO

In the sp_grant_proxy_to_subsystem stored procedure, @subsystem_id=3 indicates this proxy will be used to run Operating System commands.

Once the proxy exists it can be selected in setting up a job task by specifying the new proxy in the Run As dropdown in the task dialog. If setting up the job through Transact-SQL you specify the proxy in the sp_add_jobstep stored procedure with the @proxy_name parameter:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 01',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'CmdExec',
		@command=N'"d:\ApplicationDirectory\MyJobStep.bat"',
		@flags=0,
		@proxy_name=N'proxy_MyProxy'

This may seem like a lot, but really it's not, and it ensures that the automation you're implementing is being done using the right context.

Allen

When was my database / table last accessed?

AaronBertrand — Tue, 06 May 2008 05:14:00 GMT

A frequently asked question that surfaced again today is, "how do I see when my data has been accessed last?" SQL Server does not track this information for you. SELECT triggers still do not exist. Third party tools are expensive and can incur unexpected overhead. And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging. Even in cases where all table access is via stored procedures, it can be quite cumbersome to modify all the stored procedures to perform logging.

SQL Server 2008 will offer Server Auditing for all actions, and this can be logged to a file, or to the Windows Application or Security Log. You can do something as narrow as record when a specific login queries AdventureWorks.Person.Address.City, and as wide as recording information about every query against every database on the entire instance. Here is a quick sample that audits all select queries against Person.Address in the AdventureWorks sample database:

USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
    TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = ON);
GO

USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT ON Person.Address BY PUBLIC)
    WITH (STATE = ON);
GO

SELECT *
    FROM Person.Address;
GO

SELECT *
    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO

USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

For those of us who don't want to wait for SQL Server 2008 and cannot use stored procedures to log select activity, there is another answer: the DMV sys.dm_db_index_usage_stats, introduced in SQL Server 2005. By showing the last read and write to a table, this DMV allows us to answer the questions we couldn't before:

when was database x accessed last?
when was table y accessed last?

We can answer the question about access to a database simply by aggregating the data in the DMV to the database level:

USE AdventureWorks;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
(
    SELECT
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT last_user_seek, NULL FROM agg
    UNION ALL
    SELECT last_user_scan, NULL FROM agg
    UNION ALL
    SELECT last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);

Switching focus to each table is accomplished by adding the object name to the GROUP BY (and as Jerry pointed out, this will require SP2 to use OBJECT_SCHEMA_NAME(), otherwise you can join against sys.tables and sys.schemas):

USE AdventureWorks;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
(
    SELECT
        [object_id],
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    [Schema] = OBJECT_SCHEMA_NAME([object_id]),
    [Table_Or_View] = OBJECT_NAME([object_id]),
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT [object_id], last_user_seek, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_scan, NULL FROM agg
    UNION ALL
    SELECT [object_id], last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
    OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
ORDER BY 1,2;

One word of note is that sometimes an UPDATE can look like a simultaneous read and write. For example:

USE AdventureWorks;
GO
UPDATE Person.Address SET City = City + '';
GO
SELECT *
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID()
    AND index_id = 1
    AND [object_id] = OBJECT_ID('Person.Address');
GO

See that for index_id 1, last_user_scan and last_user_update are identical and fairly recent.

Another note is that unless a view is indexed, you cannot reliably track access to a view -- instead the references to the underlying tables are updated in the DMV.

UPDATE - Mike C# and dave ballantyne brought up a great point that applies to all DMVs: the values do not survive a SQL Server restart, or detach/attach, or even Auto-Close. So, if you restart your server and then want to see when something was last accessed, all objects will either be NULL or very recent. One way to work around this is to create a SQL Server Agent job that polls the DMV periodically, and stores a snapshot of the data. This way you can have a running history of "last access" and maybe roll it up once per day (or whatever granularity is suitable).

Even when SQL Server 2008 is released, auditing of some kind will be required if you want more information, such as a history of who ran which queries. And if you are looking for more details about information that has been added, updated or deleted, you are going to want to look into the Change Tracking and/or Change Data Capture features. But in the meantime, this DMV provides a quicker and much lighter-weight approach to at least determining when your data was accessed last.