A community of more than 1,500,000 database professionals and growing

How to Monitor for High CPU utilization in SQL Server

Geoff Albin from SQLServerCentral.com

This is the method that Geoff Albin has used for years to monitor the CPU on his SQL Servers. More »

Exporting Master Data from Master Data Services

Additional Articles from Microsoft MSDN

This white paper describes how to export master data from Microsoft SQL Server Master Data Services (MDS) using a subscription view, and how to import the master data into an external system using SQL Server Integration Services (SSIS). The white paper provides a step-by-step sample for creating a subscription view and an SSIS package. More »

From the SQLServerCentral Blogs - Lost in Translation – Deprecated System Tables – sysforeignkeys

StrateSQL from SQLServerCentral.com

This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective... More »

Is your employer unprofessional?

It's Friday, and that's the poll question based on this blog post. In the post, Mark Rendle talks about the fact that  we are all responsible for our professional development and career, but a professional employer understands they should also be making an investment in their developers. He disagrees with Uncle Bob Martin who says that we are solely and completely responsible for our own learning and education.

My view is similar to Mark's in that I think we are ultimately responsible, but that employers should bear some burden of investment in their staff, especially as they evolve and change their technology platforms. If you are hired to be a SQL Server 2008 DBA, the company can't expect you to be skilled in SQL Server 2012 and handle deployments as soon as the product is released. A company can expect you to put some effort into learning the new platform, and perhaps over time understand how it differs from previous versions, but if the company wants you to gain that knowledge quicker, they need to invest in your knowledge themselves.

A professional employer understands that, and is willing to put some investment into you, usually if you show some initiative and effort to learn on your own, or make your own investment (time and/or money) into improving your skills.

I've been fairly lucky in that most of my employers in the past were willing to invest in me. Even the ones with very limited training budgets would provide some supplemental help if I showed them my own plan for increasing my skills. It might have been reimbursement for a book, a spare computer, or even just some time at work to spend learning something new. My current employer is one of the best, offering lots of training. Now if I could just find the time to take advantage of it ....

Let us know this week if you have a professional employer.

Steve Jones

The Voice of the DBA Podcasts

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

• Watch the Windows Media Podcast - 24.7MB WMV
• Watch the iPod Video Podcast - 18.9MB MP4
• Listen to the MP3 Audio Podcast - 3.9MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. You can also follow Steve Jones on Twitter:

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. They have a great version of Message in a Bottle if you want to check it out.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

» 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

Today's Question:

Which of the following statements is true of table variables in SQL Server 2008, 2008 R2, and 2011? (select 6)

By Tom Thomson

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

This question is worth 2 points 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 Backup and Restore

A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.

Get it from Amazon in print  or download the ebook for free from Red Gate. 

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!

What is the output of the below statement?

SELECT STR(987.65, 2, 3);
GO

By Ravi DBA

Answer: **

Explanation: When the expression exceeds the specified length, the string returns ** for the specified length.

Ref: STR - http://msdn.microsoft.com/en-us/library/ms189527(v=sql.100).aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks.

Get your copy from Amazon today.

Show Job Run Snapshot

Monte Kottman from SQLServerCentral.com

Show all job history between two dates. This is much easier than trying to navigate the job history log file viewer. More »

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

SQL Server 2005 : Administering

Question regarding TempDB - Hi all, I have a question regarding the way TempDB works. Currently, I have a database which is on a drive that...

Clearing out msdb.dbo.sysmail_mailitems... - OK, so we (I) finally noticed that the MSDB backup was rather large. In looking into the problem I found...

SQL login gets locked automatically - Yesterday, I have faced an incident that users were getting login locked out error in their applications. I wanted to check...

Types of DBA's ??? - Can any of you gentlemen tell me totally how many types of DBA's exists for an organization or overall Administrative...

Small database with 1 million users - Hi all, We've a small database (1 GB) and SQL 2005 server is clustered one. Now Business is forecasting that database...

Installing Fulltextsearch in existing named instance of sqlserver 2005 - Hi, In our server sqlserver named insance is installed Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright...

DBA Interview Questions needed - Serious only please. - Hi dudes and dudettes... The boss and I are conviced that we need a dba since the 3rd party firm providing...

shrinking logfile in SQL 2008 - I cannot manage to shrink my logfile and datafile, the command used before in SQL 2000 doesn't work anymore, Urgent...

SQL Server 2005 : Business Intelligence

Converting Stored Procedures to SSIS packages - Hi All, We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps: 1....

Quality Selection for BI Reporting Tools - [size="3"][font="Times New Roman"]Hi All, I am in dilema for selection of a BI Reporting Tool. After spending few hours I just...

Notify icon Script Task help - Hi all, I have a problem when trying to display a message in a NotifyIcon through a script task of SISS...

SQL Server 2005 : Development

UDF AND Stored Procedures - Hi Guys, I know, the question is Funny, but i want to know the details of stored procedures and UDF I have...

SQL Server 2005 : SQL Server 2005 General Discussion

Getting space back after manually deleting records - We have a database with lab tests that we are going to merge with a different database. The original has...

Flattening XML problem - Using, SQL2005, I've been given a project that requires shredding some predefined XML. I'm having a hell of a time...

Find all queries which use a perticular index. - If i want to delete my IX_Unused_Index but before that i want to identify which queries will be affected if...

SQL Server 2005 : SQL Server 2005 Strategies

Redesign help - Hi, Ive finally been allowed to redesign some tables which are "wide" (Think Field1, Field2, Field3 ... Fieldn). There are multiple fields...

SQL Server 2005 : SS2K5 Replication

MSreplication_objects seems to be out of sync - I am into a new job and today I got some requests related to a database involved in replication. The...

SQL Server 2005 : SQL Server 2005 Integration Services

Incorrect concatenation in SSIS expression. What could be wrong? - I have this SSIS expression and it is evaluated as below with the default values replacing the SSIS variables. This...

An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "Failure creating file.". - Hi Everyone, I need urgent help on this one. I am running an SSIS package for 32 bit on a 64...

SQL Server 2005 : T-SQL (SS2K5)

Random account selection for each staff person - The problem is to extract a random sample of 3 accounts for each staff person. In reality, my table has...

How to trim the decimal places? - Hi Guys I have a column with the figures which have 4 decimal points. I want to trim them to two decimal...

SQL Server 7,2000 : Administration

SQL 2000 RTM -> SP4 Upgrade Failing - So, I have 4 SQL instances, 3 clustered and 1 standalone. I can't seem to get SP4 to install to...

SQL Server 7,2000 : General

Should I become a DBA?!? - Hi All, I've been offered a job as the sole MSQL DBA at an up and coming online retailer. I'm from...

SQL Server 7,2000 : In The Enterprise

SQL 2008 x64 clustering active / active - Hello all, I've been trying for days now to get my new SQL Server x64 Enterprise cluster online in an...

SQL Server 7,2000 : SQL Server CE

0x80040e38 DB_E_CONCURRENCYVIOLATION Error - Hi All, I got this error when I tried to open one of the table in my sdf. [quote]Error: 0x80040e38 DB_E_CONCURRENCYVIOLATION Native Error:...

SQL Server 7,2000 : T-SQL

display duplicate columns - Hi, I'm trying to import a table to a new database design and I have a unique constraint on the 'email'...

SQL Server 2008 : SQL Server 2008 - General

SSIS - ConnectionString Property of OLE DB Connection Manager - Good morning, My expression for the ConnectionString property evaluates with no errors but no connection is made at run time. Any...

Dynamic Query in sql - Hi, Is there any possibility to declare variables and assign value to those variables dynamically in SQL Server 2008?

Split without delimiter based on position - I am having a query i need to split the query based on the position i specify Say string is 12345678910111213141516........ i...

Re-entered : Searching Columns for Seperate Strings - Hi Team, Small change in my requirement. having 5 columns with 5 columns expiry date, each column will have a expiry date, and...

STATISTICS IO/TIME - Hi all I have a query and i get alot time out in a day i check that with tuning advisor and...

Is it possible to Connecting MYsql with sql server ? - I am using an open source software which support mysql and i create application that uses asp.net 4.0 and sql...

Index on View having some repeated rows in sql server 2008r2 - Hi, Please let me know how to create index on view when view have some repeated rows, i tried to use...

how to make a split function for this parameter? - here iam using a parameter declare @Questions varchar(MAX)='1-2|32,42-41|44' this should be splited in to three column [code="sql"] declare @Questions varchar(MAX)='1-32,42-41' ;WITH CTE1 AS( SELECT...

Restore database - I am working a database and created some stored procedures in that about 10 days before..somehow today I restore it...

Online Platform to dod Sql Query Practice - We have some websites like [url]http://sqlzoo.net/[/url]where anyone can try or practice Sql queries .Do we have another sites too ? Please...

Log file Management - I've recently gone through the article about log file management in SQL Server. I was told that in the article,...

SSIS Expression doesn't work for concatenation. - I have this SSIS expression and it is evaluated as below with the default values replacing the SSIS variables. This...

XSD Question - Has anyone seen an XSD that does something like this: <element name="SystemCode" type="nskf:SystemCodeType" /> I'm used to seeing (from SSIS generated .xsd...

Scema Issue - I have a user on which i have acess over 2 databases say AdventreWork and AdventureWorkDW. Now i have to...

Searching Columns for Seperate Strings - Hi SQL ServerCentral Team, I having a table with 5 columns [b]Record_id Col1 Col2 Col3 Col4 Col5 ------------------------------------------------------ 101 A A B A A 102 B B NULL NULL B 103 C C C NULL C 104 D C D D D 105 AA A A A A 106 B C A AA D[/b] if record 101 contains all 5 columns same (i.e:A) then only...

Automated backup plan - I've been reading about and configuring an automated backup strategy, and would appreciate a comment or two on my plans....

truncate table - I have a table with name roleMaster( Roleid,rollname). Roll id is a PK in this table which is coming from...

Plan to build read-only database - Hi, I have a plan to built read-only database. This database (read-only) store all payments data from another database (read and...

How do you admin your databases? - So I would like to try and find out - ideally for those working for a single employer - not consulting multiplexers working...

Checking values in multiples columns - ;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5) AS (SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL SELECT 102, 'B',...

slow dm_db_index_physical_stats query - I have a query I use to report on index fragmentation. SELECT TOP 25 db.name AS 'DatabaseName' , SCHEMA_NAME(obj.schema_id) AS 'SchemaName' , OBJECT_NAME(ps.OBJECT_ID) AS 'TableName' ...

Commit, truncate of log file and checkpoint in SQL Server - .

Create select statement for the query - Hi, I have a requirement to create a CASE statement for the following condition: SUM of NCV_BL.TEU where NCV_BL.POL_LOCATION_CD or NCV_BL.POD_LOCATION_CD is...

Database Restore Problem - Hi Gurus, We've a DR server which is using VM machine, running Windows 2008 R2 (Standard). Had configured MS SQL Server...

SQL job error - NT AUTHORITY\NETWORK SERVICE. Login failed - hello , I have transferred a job from one server to another and I get this error when trying to execute...

Tempdb - Hi I have a tempdb that is 45gb in size. When I go to shrink db it says there is...

A quick query puzzle: - Hi I have a table with below data. Requirement is to replace all integers with continous 6 or more occurances with...

Multiple columns search with some blank/null values - Hi, I have a Form of multiple fields and there are some fields that are optional. How I can perform that...

How do I find what is consuming SQL Server's memory? - Hi Guys I'm trying to find which processes are the most memory consuming on my SQL Server, when I monitor the...

SP_msforeachdb - I am trying to use SP_msforeachdb to query one table in every DB I have, but no system db's. here...

Migrate Sql 2000 Enterprice SP3 To SQL 2008 Std, Side By Side - Hi I am starting "the" upgrade and have found out that the server is only on SP3. I have only one...

SQL Server 2008 : T-SQL (SS2K8)

Sql Server Snaphot Performance. - Hi, I am running an update to a large number of rows on a table in a database which has a...

T-SQL query to add multiple valued report parameter - SELECT Persons.P_id ,Persons.LastName ,Persons.FirstName ,Persons.Address ,Persons.City FROM Persons where Persons.P_id IN (@P_id) the above query consider only one parameter when I try to...

t-sql - How can track database server startups/shutdowns via t-sql and save this information in a table.

Converting MSAccess SQL to TSQL - newbie - Replace Access DMin function with TSQL Min - Can someone give me some support in migrating this to SQL Server? The Req_Fin_Priorities are numeric (1, 2, or 3...

Get all the domain and SQL users from an instance, per database - Hello, For SQL 2k5, 2K8 and 2K8R2, I need a T-SQL query to get all the users listed in: SSMS => "Instance"...

SSIS Package Inside Trigger keeps running endlessly - Hi, Following some requirements, that came in yesterday, I have been assigned a task of executing an SSIS package from inside...

Logic problem in "SET [Quantity_Remaining] = [Quantity_Remaining] - 1" - Any ideas?... I have a trigger (not working obviously) set up that decrements from my [tblBulkPurchases].[Quantity_Remaining] field an amount by...

Capturing Historical/past data from Current data in tables - Below is some sample ddl and data. Each row represents an item that is on rent between a period of...

Extracting Data from single table - I am attempting to pull only open tickets from my table. Thing is we have duplicate records with different status...

SQL Server 2008 : Working with Oracle

DTS_E_PRIMEOUTPUTFAILED when connecting to Oracle - I thought I'd log this topic here after concluding an exhaustive investigation of this issue I've been having and thought...

SQL Server 2008 : SQL Server Newbies

Just taken new Job as a SQL Server DBA, can anyone recommend some learning material? - I come from a SQL developer background but have now taken a position as a DBA. Recovery plans , security policies...

Multiple Rows of Data To Single Row With One ID - Hi, I'm trying to alter a table to contain data in a horizontal (row) format rather than a vertical (column)...

Upgraded SQL Server 2005 to SQL Server 2008, but it still reports 2005 - I upgraded my SS2k5 instance to SS2k8 using the standard upgrade process from MS, but the instance still shows as...

SQL Server 2008 : Security (SS2K8)

sql server permission - I have a question about setting up permissions on a sql server 2008 r2 datbase. I basically wrote and enhanced...

SQL Server 2008 : SQL Server 2008 High Availability

Would a smaller number of VLFs in the log speed up the redo phase of recovery of a mirror snapshot? - From time to time, when an intensive log operation takes place on the principal database, we observe very long snapshot...

SQL Server 2008 : SQL Server 2008 Administration

Index question - I indexed a char datatype column but my query is still doing a table scan. So, indexes can be applied...

Database mirroring in workgroups with windows authentication - I want to configure database mirroring having witness server in workgroup environment using administrator as a user and password of principal,mirror...

Performance Monitoring tool - hi all Good morning, can you suggest free MS SQL database performance monitoring tools? Thanks Selva

Discrepancy in gigabytes between a bak file and restored database - Hello -- I completed a database restore that appeared to be successful. The object here was to restore a bak file...

server propeties and @@ servername have different server name!!! - There is different name in server properties and @@server name !!! can somebody explain how and why this happened and how...

SQL 2008 R2 Replication conflicts doesn't show up in Resolver - Have a weird problem. I had to redo a merge publication where it was causing some issues. It was a...

Programming : Connecting

IIS 7, SQL, and Kerberos - Hey gang, We're trying very hard to connect an internal webapp to an SQL Server 2008 R2. I really want to know...

SQLServerCentral.com : Anything that is NOT about SQL!

What are reading now? - So I had hopped over to Gails blog site ([url=http://sqlinthewild.co.za/]SQL in the Wild[/url]) (and just realized how long it has...

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

Issues with SSRS after migration - Hi, I was hoping someone can help me in here. I am fairly new to administration/maintenance of SSRS, but im learning...

SSRS 2008 R2 report parsing error. - Hi, I am getting following error on client machine while browsing report. The Server has Windows 2008 OS and Sql Server...

Database Design : Disaster Recovery

Graph to show downtime / data loss? - Not long ago I came across a graph plotting acceptable system downtime and acceptable data loss in an easy-to-understand format...

Data Warehousing : Integration Services

How to read Excel file name from stored procedure and file path in a variable in ssis - Hi, I have few excel sheets in a folder i need to load that sheets in a table the sheet...

Basci Flow to work Look up - Hi Gurus , Our team very new to SSIS ,we are started to work with SSIS 2005 from Monday. We need information...

how to genertae meta data of OLE DB Source/Flat File Destination dynamically? - Gentlemen, i use DataFlow Task in a 'foreach loop' container to transfer different data sets to different files. DataFlow Task consists of...

Whats wrong with this concatenation? - I have this SSIS expression and it is evaluated as below with the default values replacing the SSIS variables. This...

SSIS package to import daily SQL trace file to table - I need to create SSIS package to copy daily .trc file that is created every 2 hours in a new...

Send file with dynamic name through SFTP - Hi, I appended date and time(yymmddhhmm) to the csv file generated by the SSIS package through adding variables and expression in...

Generate XML using SSIS - Can someboy tell me is it possible to generate XML using SSIS script task and SSIS variables (not from SQL...

Assign value to variables - Hi there BOL says "SQL Server 2005 Integration Services (SSIS), expressions can be used to define conditions for CASE statements, create...

Data Warehousing : Strategies and Ideas

SSIS using Stored Procedures to do incremental loads into DW - Is it insane to have SSIS to use a Stored Procedures to do incremental loads into DW, if the frequency...

Data Warehousing : Analysis Services

Cube processing error - 1) Scheduling the processing of the full cube was working fine until I disabled one of the columns from browsing and...

SSAS- - hello I am getting error when i am processing qube on instance . But it works fine on one instance but not...

Date Filter in Excel for pivot table with cube data source not filtering - hello all, after much googleing and setting of parameters , member values etc, i am at a dead end I am trying to...

Data Warehousing : Data Transformation Services (DTS)

DTS package executing for Long time in SQL server 2005 - Hi All, i'm working in SQL server Management Studio 2005 as a application support member, which holds around 100 jobs scheduled to...

Article Discussions by Author : Discuss Content Posted by Santveer Singh

SQL Query to get all the databases and permissions on them for a login - Hi, I have situation where i need to prepare a report to find all the users and what access have they...