In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Source Control Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.
 
SQL Monitor Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.
 
sqlskills Deep technical training by world-renowned experts in 2013.
You can't get better ROI for your training budget. Read more.

In This Issue

Part 1: How to solve the transactional issues of isolation levels

Transaction Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed. More »


Identify the Cause of SQL Server Blocking

In this tip, Basit Farooq shares a query written using dynamic management views (DMVs) that will help you to quickly identify SPIDs and other useful information about the processes that are causing blocking on a SQL Server instance. More »


From the SQLServerCentral Blogs - Monitoring SQL Server Agent job run times

Introduction A few months back, I was asked how long a particular nightly process took to run. It was a super... More »


Editorial - If you're confident about a change in your DB, triple check

Today we have a guest editorial from Hilda E.

Sometimes part of a DBA career is to inherit database administration responsibilities from other DBAs that have moved on to other bigger and better opportunities. Other times a DBA gets stuck with that forgotten database that keeps that important information but that for one reason or another it was never managed correctly. Either way, when faced with such new “opportunities” a DBA needs to evaluate the database configuration and settings to appropriately bring the database to current standards.

During my IT career I have been a PC tech support person, a programmer, a system analyst, and a database administrator. I consider myself a newbie in the database administration field and although I feel like I can manage a reasonably sized database shop, I’m constantly facing problems and learning something new about SQL Server every day. I recently got my first full time job opportunity as a database administrator, and one of my first assignments was to review our database server configuration as well as the backup and restore procedures. Consistency has always been one of my mottos, and in this case I was sure to follow it not only for the sake of doing things right, but also because it’s easier on my mind to remember what steps I’ve done and how systems are configured.

I went into this journey and checked my database servers. I found that most of the database configurations were up to the standards I was accustomed to; however, the database and transaction files were residing in different hard drives locations. The previous DBA didn’t set up the database and transaction files with any particular standard directory structure. I discussed my finding with my supervisor and suggested we move the database and transaction files to be in a consistent location. In addition to consistency, backup to our remote location would be much easier to manage.

After getting management approval, scheduling time after hours to perform this task and notifying the right user groups, I felt very confident that the plan I had laid out was going to work properly.

The files were scattered around on the server, with some on the C: drive in multiple locations and others either on a second or third drive. I left the system database and transaction files in their default locations, and one by one, I reset the databases to single mode, detached them and moved the files. I moved the database files to a second drive and the transaction files to a third drive. Then I attached all the databases back again. I ran a few scripts on each database to make sure I could access the data, run GUI and web applications and all seemed to be working fine. After about three hours, nothing wasgiving me a headache. So far, so good. 

I got everything tested except for one small part of our web program.  Our intranet had the functionality to connect to our SQL Server and run precompiled programs through stored procedures. I knew of such programs but I totally forgot to test them.  SQL Server has the functionality to run CLR assemblies through stored procedures (see this link to get more information on that: How to Create and Run a CLR SQL Server Stored Procedure), which comes in handy if you want to manage your code outside stored procedure. 

Having failed to test the CLR SQL Server Stored Procedures, I went home that night only to come back the next morning with tickets flowing through our help desk.  It took us about an hour to figure out the problem.  When a restore or attach process is done for a database, some properties for the database get reset.  One of those settings is the database TRUSTWORTHY Property.  By default this property is set to OFF for security reasons.  This property is used to indicate whether the instance of SQL Server trusts the database and the contents within it (see http://msdn.microsoft.com/en-us/library/ms187861(v=sql.105).aspx).  We required this property to be set on. An easy alter database statement was all it took to fix the problem.

ALTER DATABASE AdventureWorks2008R2 SET TRUSTWORTHY ON;

Once we set the database property TRUSTWORTHY to ON, we were able to run our website and access the stored procedures that contained the CLR assemblies. 

Some days while I’m checking the databases and the servers I feel too confident, but other days like this I feel overwhelmed and realized how powerful SQL Server is. I realize there is an extensive volume of knowledge that I have yet to acquire, either by reading about it every day or by experiencing moments like this one. Every day is a new day for a DBA, some days go easy, and others are hard and come without notice even if you think your plan it accordingly.

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

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


Question of the Day

Today's Question:

I execute the following T-SQL statements

SET ANSI_WARNINGS on 

DECLARE @S VARCHAR(5)

--I execute Statement #1
SET @S = REPLICATE('1,1, ',12.5)

CREATE TABLE #T(S VARCHAR(10))

INSERT INTO #T

SELECT @S

I then execute Statement #2.

SELECT ISNULL(DATALENGTH(S),0) AS 'DataLength of S in table'
 FROM #T

The questions are: (choose two answers)

  • Does the SET @S (statement #1) return an error message?
  • What DATALENGTH of 'S' is returned (Statement # 2) ?

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

This question is worth 1 point in this category: T-SQL. 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.

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Yesterday's Question of the Day

What would be the output of this batch?

DECLARE @ToCreate bit;

IF @ToCreate = 1
    DECLARE @Table TABLE
    (id int, name varchar(50) )
ELSE
    INSERT INTO @Table (id, name)
    select 1, 'a';
    
SELECT * FROM @Table;

Answer: The batch succeeds and last query returns 1 record from table @Table

Explanation: Variables, including table variables, are initialised during parsing time, not run time. Run-time flow control logic does not apply to DECLARE statements. @Table is created during the batch compilation and gets populated in run-time, as per flow control logic (@ToCreate is NULL, ELSE part is executed).

Ref: http://msdn.microsoft.com/en-us/library/ms188927.aspx

» Discuss this question and answer on the forums

SQL Server Transaction Log Management

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. An effective response to a crisis requires rapid decisions based on understanding its role in ensuring data integrity.

Get your copy from Amazon today.


Featured Script

PowerShell to search for domain Computer objects

Query Active Directory Computer objects to verify existence 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

What is 'available physical memory' in DBCC MEMORYUSAGE - Hi, Am new to SQL Server. Am trying to query the total memory in the system that runs the SQL...

SQL Server Profiler - Hi Friends, I am running an external web application. I just need to trace all the DML queries alone using...

Tried to remove filegroup - I'm rolling back my partitioning. I am doing it by merging range in partition function, deleting files and filegroups. I...

SQL Server 2005 : Business Intelligence

Why can´t I see all dates when creating attribute relationship the "adviced way"? - I have a problem with my time dimension, I can only see some of the dates, why is this? [img]http://www.sqlservercentral.com/Forums/Attachment13505.aspx[/img] [img]http://www.sqlservercentral.com/Forums/Attachment13506.aspx[/img] When I...

Use of MDX in SSAS - I have a database in sql 2008.Normally I see all data in database engine in form of tables.Now can I...

Cube report #error issue - Hi, All My report dataset contains 3 date fields,, date1, date2, date diff datediff field formula is "[b]=cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1)[/b]" and the expression for...

How can I store the username and password without config file - How can I store the username and password not in the config file or SQL Server in SSIS, so that...

SQL Server 2005 : Development

Rows Value into Columns - hi Guys, Need your help to sort out the Query, i have fix columns Field1 to Field10, and Variant Rows...

SQL Server 2005 : SQL Server 2005 General Discussion

Update Join Query Help!!!! - hi Guys I need help with a simple update join query that i need to do. below are my example of...

Database mail with @query issue! - I'm trying to send db mail using @query parameter and I get the error below: Msg 22050, Level 16, State 1,...

SQL Server 2005 : SS2K5 Replication

Steps to change SA Password in Replication Environment - Dear All, Due to security reasons we are planning to change the SA password. We are maintaining Updatable transitional replication between two...

replication design advice - Hello Folks, we are planning to replicate database for reporting purpose and we need to restrict data based on companyID' we are...

SQL Server 2005 : SQL Server 2005 Integration Services

Problem in loading filenames through ForEachLoop container - Hi, I am using SSIS 2005 ForEachLoop container to pick the files from a location. My files have mainly 3 types...

SQL Server 2005 : T-SQL (SS2K5)

SQL Report formatting with UNION Clause - I have an sql like this: SELECT 1 AS GROUPID, 'A' AS GROUPNAME, ''GROUPID, ''GROUPNAME UNION SELECT 2 AS GROUPID, 'D' AS GROUPNAME,...

Help with sql statement to delete old records for user if multiple records found - Here is how the data looks like for UserSession Table UserName LastUpdated sessionID --------- ------------- --------- User1 1/1/2013 10AM SESS01 User1 1/1/2013 11AM SESS02 User1 1/1/2013...

cannot insert duplicate key row in object 'tablename' with unique index 'indexname' - Hi, I have an MS Access application whose tables are linked via ODBC connection to SQL server 2005. In one of...

SQL Server 2005 : SQL Server Newbies

Generate two columns from single column with alternating data - I have what I thought would be a simple PIVOT example, but I have not yet solved it without using...

SQL Server 7,2000 : Administration

Need Help Stopping a Rouge Extended Procedure (XPSMTP) - I'm experiencing a very unusual problem with one of our old SQL 2000 installs. Backstory: we've used xpsmtp80.dll on all...

SQL Server 7,2000 : Data Corruption

ERROR 823: I/0 error (torn page) detected during read at the offset 0x00000000182e000 in file D:\program files\microsoft\SQL server\MSSQL\data\msdbdata.mdf - In our server suddenly we are getting this problem and we couldnt solve this ERROR 823: I/0 error (torn page)...

SQL Server 7,2000 : T-SQL

Stored procedure Script to display and add two different currency columns using case for conversions - Hi there, I have a table cost, with costs of products from one colum(cost). This column has has different currency values...

Deleting Rows in a table.. - I have a table like that MemberId(F.K) ! Event ! Event Date 1 'Test Event1' "2012-03-20 05:39:51" 1 'Test Event1' "2012-03-21 05:39:51" 1 'Test Event1'...

SQL Server 2008 : SQL Server 2008 - General

Peer-to-peer replication - Question. if I insert a row on one server in the peer-to-peer topology, is it possible that an update is...

Blocking - Hi, We are running one job which runs SELECT query from a view from another linked server. We are getting blocking...

Negative SPID -4 - Hello All, Can someone share some information on -4 SPID? I am aware about -2 and -3 but have never seen...

Restoring SQl SERVER 2008 R2 - Hi Guys: Any guide how to restore sql server 2008 r2 backup on sql server 2000 thanx symun

SQL query to XML ??? - Hi, I',m new to XML and here is what I need to do... I want to create a SQL query to pull...

SSIS - Excel 2010 - Provider=Microsoft.ACE.OLEDB.12.0 - I am requesting that the Microsoft Access Database Engine 2010 Redistributable be installed on my SQL 2008R2 Enterprise SP1 running...

Installing SQL Server Express 2008 & Studio on Windows 8 - Hello all. My first post here. My question probably will seem amateur as I'm sure I is. I've been hired...

SQL Script To Find Non Basic Latin - I'm having issues with Invalid Unicode charters in my 2008 DB, caused by people using copy and paste into the...

Shrinking the log file does not reduce size - I have a database which had mdf size of 350 MB and ldf size 4.9 GB When i try to shrink...

Generating a counter table in SQL - Hi All, I am currently looking at a project that requires acounter table - I have done this very easilly in Excel...

Use of rownum function in a select - Hi I need to get the first row from a subselect. Have read a couple posts on this topic, but not...

VIEW vs. DynamicSQL - Hello, I have few questions, I would like remove DSQL, but our design table is not good, but redesign table is...

Discriminator Field - Hi could anyone tell me whya discriminator column would be added to a table? what is a discriminator column? what is...

SQL Server Activity Monitor - Good Day . We are running SQL Server 2008R2 as a named instance on a virtual Server . I am able to...

"SET STATISTICS TIME" and "SET STATISTICS IO" questions - 1) As I understood from here [url=http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/][/url] "elapsed time" is the total time of statement's execution, but how than it can be...

MDX Query - Hi, While running the MDX query to return prevoius month data, i am getting getting Null for previous month data. With member [measures].[T1_pytd_sales] as sum(parallelperiod([Time].[Fiscal].[Year],1, [Time].[Fiscal].currentmember),[Measures].[Spend]) member...

Join Issues - Customer Penetration project - Maybe I'm just trying to get confirmation that this is possible, but essentially I'm trying to run the main query...

SQL QUERY to find maximum and minimum column value of a primay key column in a year - Hi Experts, C_KEY ENTER_DATE 1 2008-12-31 00:00:00.000 2 2008-12-31 00:00:00.000 3 2008-12-31 00:00:00.000 4 2009-12-31 00:00:00.000 5 2009-12-31 00:00:00.000 6 2009-12-31 00:00:00.000 7 2010-12-31 00:00:00.000 8 2010-12-31 00:00:00.000 9...

Moving multiple similar access databases to SQL - I'm planning on moving around 4 or 5 MS Access databases to SQL Server and need some insight on what...

SSIS 2008 - renaming files - Hi, I have placed a file system task inside foreach loop container and the operator is rename file. I'm wondering if...

How to Determine New SQL Box Specs for Migration from 2005 to 2008 - So I've been researching this, but can't get a clear how to on this. I just joined a new job...

SQL Server outof space question. - Hello Experts, One of our clients server ran into this issue. The server has both application and SQL Server installed on...

Mega Problems installing SQL Server 2008 R2 Developer - I normally install SQL Server 2008 R2 Developer without any problems, for example on an unupdated version of Windows XP....

Using MERGE tsql for FACT loading - Hi All, I m using the below tsql MERGE statement for loading data to one of the fact table in DWH...

Rebuild or DBREINDEX - Hi I have a table with a clustered index and 6 non clustered indexes.The size of the table is 400G...

queries on Logins - Hi If I execute the stored procedure multiple times on same day, the first time category should not repeat for the...

Assigning a fieldname with rollup - I have two tables that have racing data, and I need to add up the number of cancelled races and...

RSClintPrint - So we are in the process of converting over from Crystal to SSRS. Things seem to be going fine; however,...

MCM Free Videos - Demo Scripts from Paul/Kim/Brent/Ben - http://www.sqlskills.com/T_MCMVideos.asp I read a blog post from 01/2011 from Paul saying the scripts would be released through blog articles. "[url=http://www.sqlskills.com/BLOGS/PAUL/post/All-MCM-free-training-videos-now-available.aspx]PS Some people...

Odd Error Message - Morning Just going throught my 2012 SSMS to a 2008r2 server, and I got the following error message, which I've never...

SQL Client Access Licence requirements - Hi SQL 2008R2 Std Ed. We are having an internal debate on what does (does not) require a SQL CAL for the...

2008 Central Management Server - Schedule Multi Server Query? - Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL...

SQL Server 2008 : T-SQL (SS2K8)

Parent/Child and Recursion - I have data in a table like this: [b]Parent[u][/u][/b] [b]Child[u][/u][/b] 300001 110081 300001 102157 300001 102158 300001 102159 110081 101000 110081 101504 110081 102129 Child 110081 of parent 300001...

Stuck with a getdate time range? - Hi All, Sorry if this has been asked somewhere all ready but I've struggled to find the answer! I have...

creating table error? - Hai Friends, Im creating one table for web application these is my table structure. after wrote the code ll show one...

How to pass where condition as parameter of stored procedure - How to pass where condition as parameter of stored procedure

T-SQL Greatest function workarounds - timing tests - EDIT: About four posts down is an expanded test script that if you get the chance to run for timing...

Table variable is not automatically dropped in TempDB - hi, I have created procedure like . USE [master] GO /****** Object: StoredProcedure [dbo].[b] Script Date: 04/28/2013 01:09:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc...

Cannot shrink log - My Transaction log is about 3GB and I can't shrink it. It is using a FULL backup and I try over...

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...

XML Question (?) - Hi, Let's say I have the following table with just 1 record: SELECT Field1, Field2, Field3 FROM tbl returns 11, 22, 33 What would...

SQL Server 2008 : SQL Server Newbies

Rollback in trigger - Dear All I have read that if there is a rollback in the trigger then it rollback the transaction in the...

Random Number Generator - I am trying to create a random number generator that would populate a number between 8-48 to 9 different lanes...

Store proceure with multiple result sets - Dear All I have one procedure PROC1 wich resturn multiple result sets. No of coulmn and Datatype of the result set...

Updated muliple rows into one column? Why is it hard? HELP - Trying to update a table using integer number in 3 columns and a signing texts name to results in the...

inherited an app, Error started the next day - Hi, First post, I've been using SQL for about 3 years, I just changed jobs and inherited a bunch of...

Selecting/Deleting rows with the same column information - I need to delete some rows from our system that have the same transaction id based on an accounting date....

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...

SQL Server 2008 : Security (SS2K8)

SQL Server 2008 Audit_Amazing_Expecting Comments - We enabled SQL Server external audit feature in our production server. We enabled only DML(Insert,Update,Delete) single audit event instead of...

SQL Server 2008 : SQL Server 2008 High Availability

Mirroring with replication question - Before I watched CBT Nuggets dicussing replication & mirroring. In it he described the following scenario (pictured below as I drew...

cluster validation test failing while adding node to the existing cluster group - cluster validation test failing at Validate SCSI Device Vital Product Data (VPD) test. There's a hot fix to resolve this...

Can't start mirroring that was previously disabled. - sqlserver 2008 R2. The error is: TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'FASTContent_CrawlStoreDB_2d3e9be9ef044393a736f16d569f0952'. (Microsoft.SqlServer.Smo) For help, click:...

SQL Service account change - Hi, Log shipping (backup job) failed after we change SQL service account in our production database and DR database server. Earlier...

SQL Server 2008 : SQL Server 2008 Administration

Help with a Deadlock Issue - Recently we have started seeing some deadlocks reported on a database instance. I have enabled the trace -T1222 and am...

2005 to 2008 R2 Migration Specs - So I've been researching this, but can't get a clear how to on this. I just joined a new job...

Sql agent jobs not working automatically after hardware upgrade - Hi All, I am facing issue in sql agent jobs after a RAM upgrade in a multi -instance server. Details are as...

huge log file - Dear Experts I have a 3 GB mdf file and 355 GB ldf file. What should I do, I have taken a...

Maintaining Statistics - Hi, I am having a little issue in understanding how to implement a good maintenance plan in updating statistics. I have...

How to relocate the msmdsrv.ini file for OLAP - We recently purchased a high-performance SAN and I'm in the process of migrating stuff off from the old SAN drives...

Avamar Transaction Log Backup Issue - Hi, We just recently started using Avamar to backup everything including an hourly SQL server transaction log backup. The issue we’re...

Programming : General

Updating information in a datagrid by using a button - Hi, Another C# question here. What I am trying to do is update information within a datagrid that could contain multiple...

Creating database,stored procedures from batch file - Hello, I am using below code to execute a sql scripts through batch file.....[passing parameters ]. [code="plain"] rem This script executes the scripts...

Programming : XML

New to XML....??? - Hi, I',m new to XML and here is what I need to do... I want to create a SQL query to pull...

SQLServerCentral.com : Anything that is NOT about SQL!

crystal reports version 8 user guide - I am looking for a Crystal Reports version 8 user guide. I have done an exhaustive google search with no...

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

Jump to URL paramater not working in ssrs 2005 - Hello, I am using ssrs 2005 I have a main report and a subreport. When the user click on the dollar...

Database Design : Design Ideas and Questions

Schemas vs Several Databases - Hi Folks, I would like to discuss the following design issue: I'm working in a Project in which several systems are involved,...

Data Warehousing : Integration Services

Extract Speed from DB2 - Hello again all, I am back again. This time with a new company, new city, and new role! I am now in...

Data Warehousing : Strategies and Ideas

Data Warehouse Models - Someone from my company's strategy has said that the Data Warehouse model needs to be: - Atomic - 3rd NF - Relational But I...