In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Doc Hate explaining your database in meetings?
SQL Doc quickly documents your entire database schema so that you can easily present it to others. "This tool is embarrassingly easy to use." David Hayden, DavidHayden.com. Download a free trial now.
 
SQL Monitor Get your priorities straight with SQL Monitor
“SQL Monitor gives me the ability to quickly see what my priorities should be, and delivers the information I need to make the right decisions,” Aaron Kolysko, DBA. Start monitoring with a free trial.
 
SQL in the City SQL in the City London - Free SQL Server training the Red Gate way
SQL Server MVPs, ingeniously simple tools and an awesome training experience. Find out more and register for the next event happening in London this June.

In This Issue

Stairway to Transaction Log Management in SQL Server, Level 9: Monitoring the Transaction Log

Our major goal in terms of log maintenance for all databases under our care is to optimize for write performance, in order to support all activities that require SQL Server to write to the log, including data modifications, data loads, index rebuilds, and so on. However, it's also important to keep an eye on possible log fragmentation, which, as described previously, can affect the performance of processes that need to read the log, such as log backups and the crash recovery process. More »


Custom Metrics for Monitoring Database Mirroring

SQL Monitor comes with plenty of metrics, but there are always going to be things that you want to measure that can't be done 'out of the box'. Taking database mirroring as an example, Grant shows that custom metrics can be used to monitor and alert whatever aspect of SQL Server or database that you need. More »


From the SQLServerCentral Blogs - Discover, Diagnose, and Document ALL Your SQL Servers On Your Coffee Break

We all know that we should document our SQL Servers, right? RIGHT? So let's say you had some time on... More »


Editorial - Natural Data

Can you own data? In one sense, that's what the arguments before the US Supreme Court over a Myriad Genetics patent are about. The company has patents for a few genes, which in and of itself, are really just data about a particular organism. There are a lot of technical issues here, and I'm not completely sure how I feel about this topic or who I'd like to see prevail in the case. I do think, however, that this does have lots of implications for how we might view other data in the future if the patent is upheld. 

As data professionals, we usually think of the mechanics of dealing with the various pieces of data in our organizations. We must protect data, ensure it's integrity, make it available and more. A large part of our careers is spent manipulating or managing the bits and bytes stored in databases. We must extract information out, and often need to understand what the data represents to do this, but most of us haven't been exposed to the issues of ownership or rights with data. I think that will increasingly change as government rules, regulations, and laws scramble to catch up with the digital world.

Digital ownership is a very thorny subject, complex in its breadth and depth. We've seen technology companies fight against some laws, like CISPA, while supporting others. Often our government officials don't understand the complexity of the topics, and I'm not sure that we in the technology business explain the the situation well. Even very intelligent people often don't convey the implications of these complex topics, many of which often don't have good analogies in the analog world.

Ultimately I think we will find our jobs as data professionals becoming more and more complex as we must not only solve problems and manage data, but we will also end up being constrained by the legalities governing the data.

» Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center


Question of the Day

Today's Question:

After running the following set of statements, how many rows will be inserted in table dbo.Test (and returned with SELECT)?

CREATE TABLE dbo.Test
(
     Col_1 INT IDENTITY(1,1) PRIMARY KEY
    ,Col_2 INT REFERENCES dbo.Test(Col_1) NOT NULL
);

SET IDENTITY_INSERT dbo.Test OFF;

INSERT INTO dbo.Test (Col_2) VALUES (2);
INSERT INTO dbo.Test (Col_2) VALUES (1);
INSERT INTO dbo.Test (Col_2) VALUES (3);

SELECT * FROM dbo.Test;

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: Foreign Keys (FK). We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.


Yesterday's Question of the Day

The code for this question should work in SQL Server 2008, 2008R2, and 2012 but has only been tested in SQL 2008 R2. The question is: when this code is run, how many rows do each of the three selects return?

-- ****** first switch off nested triggers 
EXEC sp_configure 'show advanced options', 1;
go
RECONFIGURE ;
go
EXEC sp_configure 'nested triggers', 0 ;
go
RECONFIGURE;
go

-- ***** next delete any left over tables/views 
if OBJECT_ID('dbo.vtmp1') is not null drop view vtmp1;
go
if OBJECT_ID('dbo.vtmp') is not null drop view dbo.vtmp;
go
if OBJECT_ID('dbo.tmp1') is not null drop table dbo.tmp1;
go

-- ***** now create the base table
create table dbo.tmp1 (x int identity(1,1) primary key
  , y varchar(6) not null
  , a int not null
  , b varchar(6) not null
  );
go

-- ***** create views and triggers 
create view dbo.vtmp with schemabinding as
  select t1.x, t2.a, t1.y, t2.b
    from dbo.tmp1 as t1 inner join dbo.tmp1 as t2
      on t2.b like t1.y+'%' or t1.y like t2.b+'%';
go
create trigger dbo.vtmpdel on dbo.vtmp instead of delete as
   delete dbo.tmp1 where x in (select x from deleted where LEN(y) != LEN(b));
   delete dbo.tmp1 where a in (select a from deleted where LEN(b) != LEN(y));
go


create view dbo.vtmp1 with schemabinding as
  select y,x,b,a from dbo.vtmp
go
create trigger dbo.vtmp1del on dbo.vtmp1 instead of delete as
begin
  delete dbo.vtmp where x in (select x from deleted where LEN(b)!=LEN(y));
  delete dbo.vtmp where x in (select a from deleted where LEN(y)!=LEN(b));
  delete dbo.vtmp where a in (select x from deleted where LEN(b)=LEN(y));
end
go

set nocount on
go
-- ***** populate the base table 
with tly(i) as (select 0 union all select 1 union all select 2 union all select 3 union all select 4)
insert dbo.tmp1(y,a,b) select REPLICATE(char( 65+i), i+1),5-i,REPLICATE(char(65+i),5-i) from tly;
go

/* ***** get the the count of rows in the top view three times:
before deleting anything, after the first delete, and after the second delete  ***** */
select 'select 1', x, a, y, b from dbo.vtmp1  -- select 1
delete dbo.vtmp1 where x = 4
go
select 'select 2', x, a, y, b from dbo.vtmp1  -- select 2
delete dbo.vtmp1 where x =3
go
select 'select 3', x, a, y, b from dbo.vtmp1  -- select 3

-- ***** tidy up part 1: drop the views and table created above *****
delete dbo.tmp1
go
delete dbo.vtmp
go
delete dbo.tmp1

-- ***** tidy up part 2: enable nested triggers (ie restore the default) *****
EXEC sp_configure 'show advanced options', 1;
go
RECONFIGURE ;
go
EXEC sp_configure 'nested triggers', 1;
go
RECONFIGURE;
go
EXEC sp_configure 'show advanced options', 0;
go
RECONFIGURE;
go

Answer: 5, 3, 3

Explanation: Nested INSTEAD OF triggers can cascade regardless of the setting of the nested trigger server option. See http://msdn.microsoft.com/en-us/library/ms178101.aspx.

The rest is just ordinary SQL, but just in case anyone needs help in following it what is happening is this:- The top level trigger deletes one row (matching the original row) in the next level view for each row in the deleted table that contains two equal length strings, and two for any row that contains two unequal length strings (matching the original row and the one with with the two string lengths swapped). So the first delete passes two rows to the next level and the second delete passes only one. The trigger at the level below passes on deletion of rows with two unequal string lengths to the base table, but does nothing to delete any row with two equal string lengths, so the net effect is that the first delete (which asks to delete a row where the string lengths are 2 and 4) deletes two rows and the second delete (which asks to delete a row where the string lengths are 3 and 3) deletes none.

» Discuss this question and answer on the forums

SQL Server Execution Plans

SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon!


Featured Script

List of Stored Procedures modified in past 7 days

Provide list of Stored Procedures that have been changed in past 7 days. More »


Database Pros Who Need Your Help!

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Cannot drop mapped windows login from SQL Server due to ENDPOINT permission - Trying to do some housekeeping of our SQL logins and I noticed that my active directory account has a mapped...

Database Mirroring in SQL 2008 R2 instanct - I believe this error is related to database mirrroring. I have more than 90+ databases configured DB mirroring on a...

SQL Server 2005 32-bit on Windows 2008 R2 x64 OS - Before I get started, I'm well aware that this type of setup is bizarre and I definitely did not recommend....

PBM on Sql Server 2005 ? - Hi All, I have a few critical sql 2005 production servers and I have been asked to try implement policies on...

select query - i need a select query for below. there is 3 date colums in a table. how to find out the greatest value...

CPU Usage using T-SQL - Hi, Please help me, how can I get CPU usage in % using T-SQL? Regards - MSR

SQL Server 2005 : Backups

SQL server logs backup / reduction - Intro: I have sql server installed on c drive but the logs (LDF) is stacking up and I am getting out...

Log backups while Full/Differential backups are running - A few weeks ago I was at an event where someone came up and asked me if it's bad to...

SQL Server 2005 : SQL Server 2005 General Discussion

Unable to change to SA as database owner - Hi guys, I've encountered problem upon changing database owner from specific user to SA. Lock request time out period execeeded. (Microsoft SQL...

Severe Server Error with function in default value - I've been playing around on a test database today, and one of the things I've done is created a function...

A domain error occurred in Sql server 2005 while updating the data - I have created a function like the one below Create FUNCTION fn_Calc (@Lat1 Float, @Lng1 Float, @Lat2 Float, @Lng2 Float) RETURNS Float AS BEGIN Declare @x...

SQL Server 2005 : SQL Server 2005 Security

PCI Compliance - Hello, We have a database which needs to be secure in order for PCI compliance. What I am trying to establish...

SQL Server 2005 : SS2K5 Replication

Invalid Object Name in Transaction Replication Error 208 - Hi Experts I have set up a Transaction Replication and its working fine on most of the database in one...

Transaction Log Growing due to Pending Transaction (Replication) - SQL Server 2005 SP4 with Transactional replication. I have this Database Log, which keeps growing due to a Pending transaction in...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Database Data File Physical Fragmentation From Small Auto-Growth Setting - I've placed this post in the SS2005 Performance Tuning section of the forum because it was totally ignored in the...

SQL Server 2005 : SQL Server 2005 Integration Services

Copy a CSV file to another network drive - I am using SSIS 2008. The task is following. 1: Execute a storted proc and using the output create a CSV...

No tables or views could be loaded. - In the OLE DB Source Editor, the drop down select list for "Name of the table or view" returns "No...

SQL Server 2005 : T-SQL (SS2K5)

nested left and right query? - Is it possible to nest (or perhaps stack via CTE) left and right? For example, if i had a list...

Email validation question - Hi, A stored procedure calls on this UDF. I have to update it to exclude email addresses with '.@' or '@.' in them. [code="sql"] GO /******...

Using the CONTEXT_INFO Function for multiple objects in the same database - I have a situation where CONTEXT_INFO is currently being used in one area of our PROD instance on a database...

Get count while insert is still running - I'm running something like this: [code="sql"]more: waitfor delay '00:00:05' insert top (1000) into TableB Select * from TableA A where A.ID not in (select...

DDL Trigger to prevent dropping of a specific table? - Hi, I know DDL Triggers can monitor changes on both the Server level and Database level, which could track/prevent changes for...

SQL Server 7,2000 : Administration

Moving TEMPDB - Hi all, I have a default installation and I need to move just the TEMPDB database from C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data...

SQL server 2000 - Profiler error - Hi, I am using Query Analiyer to execute procedure integrated into database ( I am not author and I dont know what...

SQL Server 2008 : SQL Server 2008 - General

Error="8952" Severity="16" State="1 what is error number 8952 stands and solution? - What is the error number 8952 and remedy to fix the same. <DbccResults> <Dbcc ID="0" Error="8952" Severity="16" State="1">Table error: table 'backupmediaset'...

Language British - I have one of dozen users who is getting the date in US format, this despite running a script to...

Need an update statement - [code="sql"] /****** Object: Table [dbo].[PhoneTable] Script Date: 4/23/2013 4:55:46 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[PhoneTable]( [PhoneNumber] [varchar](10) NULL, [PhoneType]...

Similar strings. - In the past week, I saw somewhere a string similarity function. (I think I saw it in my 'spare' time when...

Help with Second highest date - I have a stored procedure and I am selecting Renewal Date and the Last Renewal date (second last greater date) [code="sql"] ALTER...

Access is Denied within SSMS after host failover - NTFS allows access - We had a VM host that had issues and the guest OS didn't failover cleanly. We got the new host...

double hopping - can any one explain me what is double hopping

SQL Server 2008 r2 Express installation failed - I tried to install sql 2008 r2 express edition on windows 7 .After support files installation set up is not...

Add x amount off days and don't count Weekends - Good Day I am struggling to figure this one out. I have a column with Startdate, I want to add x...

Import comma delimited text file greater than 255 columns using OpenrowSet - Hi Guys, I used the below query for read greater than 255 columns from comma delimited file, select * from OpenRowset('MSDASQL', 'Driver={Microsoft Access...

Qurery to know tha no of instances - How to know No. of instances in my sever using Query

Script - Script to find the all the db roles of all the users in all the databases in sql 2005\2008

Rebuild Clustered Indexes (Constraint) on a Replicated Database - Good Day, I need to rebuild the Clustered Indexes(Constraints) . The only way I am aware of is the loooong and tedious...

sql partiton view - hi, i build view that contain lat say 10 table, each table contain check constraint on column name origtime type datetime , after...

Script to find specific schema_owner for all databases - Hello, Do you know a script to find schema_owner for all databases ? I have this script but I can use it...

Trouble with removing a defunct file - Hello gents Syptoms are as follows: [code] select file_id, name, physical_name, state_desc from dbname.sys.database_files go [/code] Returns: file_id name physical_name state_desc 1 dbname D:\Data\dbname.mdf ONLINE 2 dbname_log D:\Log\dbname.ldf ONLINE 3 dbname2_ARCHIVE D:\Data\dbname.ndf DEFUNCT 4 dbname_ARCHIVE D:\Data\dbname.ndf ONLINE [code] alter database dbname remove

Invalid colum Name - Hi, I am following query in any database, the table is exists, but the column my be exists in some database. But...

Name Parser - I am working with a number of large data extracts (10 - 50 million records) and need to parse full name...

jobs execution monitoring - Hi I wonder if there is an article about monitoring jobs, something similar to backups recent article script, we have...

Slow performing query! - The following SQL query isn't scaling very well! Works great on record sets of 10-20 million. However, now that I'm...

Check String for some chacters - I need to check to see if string contains (,-, / or . and pull out only the numbers from the string.... 212/2222255 should be...

Urgent help! - Hi I ran a xp_cmdshell 'copy con.....' command on my live server to create a file. It didn't work for some...

Different in count of records. - Hi All, when i am trying to find the count of records, i am getting two different counts. example: select * from table --1500 select count(*)...

DATA consitency errors - I dont have the backup when we ran the dbcc check db got the data consistency errors , how can we...

Very interested to know what can be done in the case of a long running select query - Hi everyone. I'd love to get some help understanding why SQL Server 2008R2 x64 Enterprise Edition does what it does. The...

Help with Triggers - I have to create 3 triggers for a table - INSERT, DELETE, and UPDATE. The triggers will be used to update...

Excel 64 bit driver - Hi all, I am running the following query in sql server 2008 r2(64-bit), MS office 2007 excel file. SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel...

Database Data File Physical Fragmentation From Small Auto-Growth Setting - To All, I need some advice. I've just started a new job and one of the first problems I've encountered is...

Differences between sql server 2008 and 2008 R2 - I did some research to find the differences between between sql server 2008 and 2008 R2 but couldnt find much....

SQL Server 2008 : T-SQL (SS2K8)

Order By in Sql Server - Hi All, In a table i want to apply order by. My database is 20-20 and table is errorreport where I...

Difference between cursor and While loop - Hi all, Can any one explain me What is the difference between Cursor and While Loop? I have read about...

Distinct Query Help - I HAVE DATA LIKE THIS ID DATE PID

filter duplicate students via T-SQL - /* I need to filter for duplicate students: --------------------------------------- For same student, if one or both instance/s of IDNo is null then do...

Merge Statement over a linked server - Does anyone know a way of running a MERGE statement across 2 tables over a linked server? Getting the message "The...

Is there any native support available for JSON in SQL SERVER ? - Is there any native support available for JSON in SQL SERVER ? ie. Is there any inbuilt tools in SSIS which...

SQL Server 2008 : SQL Server Newbies

Count instances across fields by date? - I need to search a table and count instances of a particular string across multiple fields by date, I've used...

Unable to run SSIS package built on 2005 under 2008 - I hope Im on the right forum for this, if not please point this out to me. I have migrated a...

can any body explain normalization and types?/ - can any body explain normalization and types?

SQL Server rename concern with IIS - We have a windows server which will be renamed shortly. It has SQL Server 2008 R2 and IIS v7.5 installed....

Slows system performance - Dear All I am running a process (updation/deletion/insert) of 2 million records. After some time this process makes my system very...

SQL Server 2008 : Security (SS2K8)

DB permissions on basis of application - Hi A few users who has access to a database via a windows group which has modify rights, now also need...

SQL Server 2008 : SQL Server 2008 High Availability

Which dell server is best for SQL 2008 R2 - Hi I am going to buy a server for SQL database. Mostly it will use for network data server. I found a...

Slow Disc Access on Single Cluster Node - Hi there, We currently have an issue with one of our SQL 2008 R2 Clusters. The cluster contains 2 virtual nodes...

SQL SERVER 2008 r2 cluster - Hi everyone , i have a query regarding the SQL SERVER 2008 r2 clustering , we have now one cluster on the...

Changing Owner or Mirrored DBs - How would I go about changing owners of a database that are within a mirror? Jonathan

SQL Server 2008 : SQL Server 2008 Administration

NO. of instances - can any one suggest me that how to find out the No. of instances in a server using query

Capturing workload for analysis - Hi. I'd like to capture all queries executed against my database to be able to make the right index need decisions. I'm...

weird dbid from sys.dm_exec_query_stats - Hi. If I execute the sys.dm_exec_query_stats I get back dbids like f.e. 32512 which I can't resolve with db_name. Which database...

CDC Capture Performance issues - Hi All, I have database which is 1.6 TB and enabled CDC on it with retention of 8 days. Over the...

SQL Performance Monitoring software - Hey guys, I'm looking for a good performance monitoring software for our sql servers. So far I was able to find...

Index Maintenance Strategy - Hi SQL-folks, I've asked myself, what would be the best strategy to keep my indexes always optimal and up-to-date. What are...

Min and Max server memory - Am a junior DBA and We have an issue from last couple of days. We have a server of Windows...

Why powershell? - Hi all experts, I have being reading about Powershell a lot this days. What i came to know about powershell is...

CU installed or not? - We single node cluster running SQL 2008 R2 SP2. We noticed the Application Even Log contianing thousands of messages like Information...

CPU on SQL server - Our .net application runs slow from a couple of weeks ago, it is using the SQL database, I see the...

No performance gain on queries on different servers with considerable hardware change - I have a dedicated Server for SQL Server. SQL Server runs on SQL Server 2008 Standard edition with SP2 applied....

Career : Certification

70-461 - Done, but more difficult than expected - Just passed the 70-461 SQL Developer Exam Today. This exam is much harder than I expected. Its quite syntax heavy, and asks...

SQLServerCentral.com : Anything that is NOT about SQL!

Ok, so it sort of is about SQL.... - ...but this made me laugh today... Seen in some production code: [code="sql"] LEFT OUTER JOIN [highlight="#ffff11"]Northwind[/highlight].dbo.orders_actioned ON scheme.opheadm.order_no = [highlight="#ffff11"]Northwind[/highlight].dbo.orders_actioned.order_no [/code] ...just WOW!

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

Reporting Services : Reporting Services

Reporting Service Home Page - Instead of getting the Reporting Service Home Page with the Content & Properties tab when clicking on a given URL, I...

Is it possible to bind a ServerReport to a ADO.Net Datasource? - I'm kind of new to Reporting Services, so bear with me. But as we use it now we write reports...

One Dataset populating multiple Reports - Good Morning I have a stack of data and I want to be able to create a single report, this report...

barcode generation problem in RDLC local report - I am trying to encode [url=http://www.aspper.com/barcode-rdlc/tutorial.html]barcodes in RDLC reports[/url], I am using vs 2008 and C# class library. It works well...

Data Warehousing : Integration Services

ssis data flow task taking an extremely long time . - Hello everyone . I have a log table that I am planning to import from my Transaction Database to my reporting...

How to avoid technically the null values while concatenating columns - HI, In SSIS by using dervied column i manipulate fields and end up in my desired results. But for an record if...

SSIS package failure on renameing files Error code: 1073548540 - Hi, I am getting following error when package is executed from BIDS: Error: 0xC002F304 at File System Task, File System Task: An...