SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

The Human Cost of Managing Data

If you've ever worked in a large environment, where the numbers of nodes number in the thousands, or tens of thousands, then you know many software packages don't scale to that size very well. I've seen quite a few pieces of software that were well written, and ran well with hundreds of items, but choked miserably when the count crept past a few thousand. Typically those are edge-case sized installations, and most applications aren't designed for, or tested at, those scales. I haven't registered 1,000 instances in SSMS, but I could guess that it would not necessarily run smoothly with that many instances being examined.

Software designed at scale tends to take the "install another central node" and manage multiple central nodes individually. That quickly becomes a pain point for the humans that administer the systems as they must learn and remember where individual systems are being managed. It's not much different for hardware as well. When a system exceeds the capacity of a single node, we often find that we are adding multiple modes and managing them individually. Many backup systems work this way, adding new units to handle the additional backup space requirements.

However there's a staffing cost as well, and it's one that can be overlooked as a company grows. More systems mean more backups, more maintenance, and more failures. Even with automation and standardization, there's a physical workload increase on your staff, and also a stress level increase from managing more systems. It can be easy to overwhelm your staff, assuming they can easily handle the additional load because they have plenty of tools to do so.

Tools are required, and whether you build or buy them, you'll make use of them if you want to minimize your salary costs. However you still need to make sure you grow your staff, train them, and give them time off. The last thing you want is to overload and burn out a small staff of one or two. I'd bet that the week your staff quits or takes ill, your systems will choose to fail.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

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.

Follow Steve Jones on Twitter to find links and database related items and announcements.

Steve Jones

Windows Media Video ( 16.9MB) feed

MP4 iPod Video ( 19.7MB) feed

MP3 Audio ( 4.1MB) feed

Feeds are available at iTunes and Mevio

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


ADVERTISEMENT

12 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

ADVERTISEMENT
SQL Data Compare

Compare and synchronize database contents

“I've been really missing a neat data comparison tool - SQL Data Compare Pro seems to be the answer to my prayers.” Jan Hansen, Database Architect. Download a free trial.

SQL Monitor

What does normal looks like on your servers?

New benchmarks in SQL Monitor instantly show you if a performance spike is a problem, or within normal SQL Server behavior. Try it live.

Featured Contents

 

Five Rules For Sucessful Conversations With DBAs

Joshua Feierman from SQLServerCentral.com

As a developer working with SQL Server, you'll need to work with DBAs at one point or another. Here are some easy ways to make those conversations go smoothly. More »


 

Manage SQL Server Reporting Services in Management Studio

Additional Articles from MSSQLTips.com

Most maintenance for SSRS is performed either in Report Manager online or within the Report Services Configuration Manager from the desktop. The SSRS Management Studio connection is certainly no replacement for either of these tools, however, several benefits can be obtained using SSMS. First, you are able to connect to the SSRS even if you are having browser issues; second, it is often quicker to just switch from using Management Studio for the Database Engine. More »


 

From the SQLServerCentral Blogs - Why the Lazy Spool Is Bad

Grant Fritchey from SQLServerCentral Blogs

First thing, there are no bad operators, just bad parents, uh, I mean query writers, or database designers, or ORM... More »

Question of the Day

Today's Question (by Samith C):

What will be the result of the query?
declare @var Varchar(1000)
set @var = '-- /* select ''Samith'' name 
*/'
set @var = @var + ' select ''sas'''
exec (@var)

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 2 points in this category: T-SQL.

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

ADVERTISEMENT

SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012

The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros".

Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Raul Gonzalez):

Is it a good idea to disable the [guest] database user in the [msdb] database?

Answer: No, as for some SQL Server features to work, the guest user must be enabled in the msdb database

Explanation: SQL Server Books Online recommends that you disable the guest user in every database as a best practice for securing the database server. But this recommendation does not apply to master, msdb, and tempb system databases. In order for some Microsoft SQL Server features to work, the guest user must be enabled in the msdb database.

Ref: You should not disable the guest user in the msdb database in SQL Server - http://support.microsoft.com/kb/2539091


» Discuss this question and answer on the forums

Featured Script

Universal foreach aka sp_uforeach

Robert Virag from SQLServerCentral.com

Syntax:

EXEC master.dbo.sp_uforeach @table_name='table name or select statement',

@column_name='existing column name', 
@where_clause='where clause only when tabla name is used', 
@command='command with ? replace character', 
@replace_character='you can define a spec character which is used for replacing the 'looping object' (default '?')
@print_command_only= 1 only print the command, 0 execute it
@print_object_name= 1 print/select the object name the script working on (def 0)
@debug=1 enable debug information

There are two options how you can provide the ‘looping objects’ which the script in the @command parameter have to run on.
First you can use an existing table name in the @table_name parameter (for example ‘sys.databases’) and in this case you can also provide a where clause without the word ‘where’ in the @where_clause parameter to filter the objects (for example ‘state = 0′).
Second, you can use a select statement with a where clause, like ‘select name from sys.databases where status = 0′. Using select statement the parameter @where_clause is ignored, but the parameter @column_name have to be provided, because the records of this column will be used as ‘looping objects’.

If you only provide the @command parameter, the stored procedure works like sp_MSforeachdb:

/*DEFAULT(foreach_db without command):*/
EXEC sp_uforeach @table_name='sys.databases', 
@column_name='name', 
@where_clause='', 
@command='', 
@print_command_only= 1

You can get help and examples using the @help=1 parameter as well.

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 2014 : Administration - SQL Server 2014

Tempdb data file fills up very often - Tempdb fills up very often. tempdb configuration is name fileid filename filegroup size maxsize growth usage tempdev 1 N:\Data\tempdb.mdf PRIMARY 29428480 KB Unlimited 10% data only templog 2 N:\Data\templog.ldf NULL 512 KB Unlimited 10% log only I want to know why its happening very often...


SQL Server 2012 : SQL 2012 - General

activity monitor in SSMS Unable to access -- error. - I receive the following messages when trying to use activity monitor in SSMS Error: The activity monitor is unable to execute...

crazy replication question - I have replication running with a 2 publications coming out of the same database. Publication number 1 has all but...

MSDB Error in backup of user database - Hi all, A new database was created on our existing single instance Sql Server 2012 Enterprise . Our over night maintenance job...

sql server 2012 licensing question - Hello all We have a fairly simple SQL server environment today (licensing wise) All of our SQL Server instances are...

SSRS report with dynamic column headers - Hi, I have a report which runs for last 12 months data. Since this is going to be last 12 months...

Adhoc queries and DB id - Dear All I want to get last executed queries on a DB. Using dm_exec_query_plan , dm_exec_query_stats ,dm_exec_sql_text gives this information. But it...

screenshot on view - I had a table like this: [code="other"]Name StartTime EndTime -------------------------------- abc 10:00 14:00 bcd 10:05 12:32 cde 13:10 14:08 dfe 11:00 11:08 [/code] We planning to create the...

Setting up multiple AG's on 1 server - Hi all, question for you please: i am upgrading sql from 2008 to 2012 and on one instance we have 3...

Failure Restoring Differential Backup - I have an issue with my SQL 2012 database server and restoring a full backup (norecovery) then applying a differential...

Build datawarehouse from scratch - Hi friends, I have couple of questions. I have experience working in datawarehouse projects but I am not sure how do...


SQL Server 2012 : SQL Server 2012 - T-SQL

Trying to build a report menu from data stored in two tables in SQL Database - I have two separate tables in my database ReportCategories and Reports. I am trying to determine how I can pull...

script - growing database. - Friends, someone could share with me a script that calculates the growth of a database and store on table? weekly and...

Replication - Hello, good, I'm Brazilian and I'm not very good English, I apologize. I have a problem before replication when replicating tables...

SSRS - Number formatting - Hi, I am using the following custom format "#,##0.00% ;(#,##0.00%)" in my SSRS numeric column and Color code :- IIF(COl.value <...

How to search using stored procedure - Hello Word and Hello all pipz Good Day!!! I need some advice on how to search using stored procedure... I have 3 tables...

Build menu with CTE .... - Hi, I've a menu that has several levels and each level has a position to order the entries. We had a cursor,...

108,000,000 row table - check for matches - Good afternoon, Long time reader - first time poster here! I am currently working on a 108m row table where some of...

Trigger Issue... - Hi, I've a table that has a trigger for insert that executes the flowing statement: [code="sql"] UPDATE ArtigoArmazem SET QtReservada = 0 FROM INSERTED...


SQL Server 2008 : SQL Server 2008 - General

Tracking database changes - Hey all, I am in need of obtaining software or a process to automatically find DDL changes to my enterprise environment...

Invalid object name ??! - Run: use xxxx go select * from [xxxx].[xxxxxx] Got: Msg 208, Level 16, State 1, Line 1 Invalid object name 'xxxxxx'. i double checked: 1) run it under right...

TSQL/SSRS: How to add "dynamics" without dynamic SQL - Hi, I'm facing a task how to deal with unknown number of user entered params, could be from 1 to 10. User...

adding another target server to a designated MSX - Hi We have a MSX server with a number of target servers already added. I am trying to add a new...

help needed in power shell script to script out sql server jobs - Hello, I created this script to script out SQL server jobs, the script creates a file matching the job name and...

identity to Flase - I want to change the identity of the column to false when the column is primary key , how to change...

How to change MSDTC name on cluster. - Hi ALL, I have configured MSDTC on win 2008 r2 cluster as a separate group.My client want to change MSDTC name...

subtract from two tables - hi everyone, Hope you are good. Need some little help. I am developing some inventory app and now need some help...

SQL Server 2008 R2 SP1 security patch failed - We have a SQL server instance whose patch level is Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) We need to apply...

Symptoms Memory Problems - what are the symptoms of Memory Problems in sql server

Unique Constraint - I have two columns Student_ID and MAC_ID in my table. I want to put Unique constraint on these two columns together. I...

How to get exact values for a constraint in SQL Server?? - Is there a way to get the exact values for a constraint on a column? I need to display these...

Rolling 3 month average cost help - Hi guys, I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out...

Stored Procedure calling other stored procedure - Hi All, Can someone help me on how to tackle this. I have a stored procedure on my server called usp_RPT_HSG_JobType. This...

Considerations for location of Reporting Services databases - Hello experts, I'm working on designing a SQL Server 2008 R2 Reporting Services (SSRS) topology, and a couple of my colleagues...

Function returns table from multiple selects - Hi All, OK I have a function that is working exactly as I had wanted it to, but I want to...

Dynamic Data Export - Hi guys, Posting after a long time. How is everyone doing? I was working on an SSIS project in which...

Backup SQL agent Job - Can I setup a single agent job to handle Full(Every Sunday at 00:00), Differential(Everyday at 00:00) and log backups(every 30...

Script to check the progress of rebuild Index? - Hi, Is there any good script available to check the progress of Rebuild Index? Would be helpful.

How to port no on Sql server - Can any one know where to find port id in sql server. I am new to sql server dba ...please help. Thanks...

SSIS to download .txt file from URL and import data in SQL Server table - There is a text file placed on a URL lets say http://mysubsite.domain.com/Customers.txt This text file has \t \Column1 \t \Coilumn2 and...

SQL Server 2008 cluster node going down unexpectedly - Last night our primary SQL Server node went down and failed over to the secondary node. I was actually on the...

Understandig row length calculation for sparse columns - Hello, I hope someone can explain the internals for row length. We have a 3rd party vendor table with following definition: 7...

Reporting Services scheduling- hourly between 8 and 5 impossible? - Trying to get a report to run daily between certain hours. I can set a start time and an "end...


SQL Server 2008 : T-SQL (SS2K8)

Phone number question - detecting and replacing - Hello, I have a website/database app with a text column that stores a user's bio where users are not allowed to...

CLUSTERED INDEX SCAN (EmpNonPrjTime) - I have a report that calls procedure. Procedure is simple select from VIEW: ... [code="sql"]FROM vwNzEmpNonPrjTime WHERE co_code = @L_CO_CODE AND ('**all**' IN (@L_ORG) OR org_code...

Incomprehensible error in OVER clause with self-JOIN and GROUP BY - I've encountered a very weird error when using an OVER clause, which i cannot explain. I'd be interested if anyone...

Aggregate minutes from varchar datatype. - Hi I have a field in a table that represents the status time spent on activity per employee. The field...

job history check - Hi friends, I have around 120 jobs in a SQL instance. Some of the jobs are scheduled on hourly basis and...

SQL Agent Job Error - String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) - I'm running into the following message, "String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)" when running a...

Rapid Growth Of Data and Purging Issues - Hi, I tried a few different things which though work well but needs atleast few minutes of downtime so I am...

Better way to write this query - I have inherited this query that is comparing data from one table to another. It is using a combination of...

Get the Maximum Value from a related table based on date - Hi Folks, Can someone help with the following on SQL Server 2008... I have a table Customer and an Orders Table... I want...


SQL Server 2008 : SQL Server Newbies

domain\server$ - Hi, SQL Server log shows several login failures from the login "domain\server$". What does that mean? I read somewhere that...

Create an Alert for ROW Size increase - Hi being a newbie, i have been asked by one of our Apps admin guys to create an alert that...

Search Using Stored Procedure - Hi to all :-) I want to have a result like this ---- SELECT * FROM TestMyView WHERE firstname = 'test5' but my code will...

Insert date in yyyy-mm format - Hi, I'm trying to insert random date in yyyy-mm format to have expiry Credit card date column. Haw can I do it?...

Parse Info Out of FileName - I am new to SSIS and have a package I have been working on. My issue is I don't know...

Why doesn't my index get used? - Hi, I had a query that I was running to get the top n records (eg, top 1,000). I changed the...


SQL Server 2008 : SQL Server 2008 High Availability

Database mirroring High Safety (synchronous) with Autofailover - witness availability.. - Hi, Hope i can get some feedback regarding a scenario we have in an environment that has been designed to use...

How to find cause of failover - Does eventvwr or cluster log clearly shows the reason for a failover? What's the effective way to find the cause of...

Resetting SQL folder and NTFS permissions after a mass move - I'm about to do a mass move of hundreds of databases located on a SQL 2012 failover Cluster. We are...


SQL Server 2008 : SQL Server 2008 Administration

Which system tables save permissions and SQL server configuration - I need to add rights and permissions data to SQL source control. Which system tables from master/model/msdb do I need...

Log Shipping Revertion - Hi, This is Damodar. My question is on Log Shipping, I have to reverse the log shipping from DR to...

SQL Server backup Failures with TSM \TDP TCP\IP Connection Failure - Sql Server Versions 2005, 2008R2 Folks I'm at a loss and the TSM Administrators are also now throwing lines into the...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Tuning Advisor - Dear Experts: I am new in using tuning advisor. I have created tuning trace file, after finishing, there were some recommendations...

cxpacket wait differences for same query on different cluster nodes - Hey guys, We are running a 5 node windows server cluster with multiple SQL 2008 instances. 1 instance in particular, our...


SQL Server 2005 : Administering

Installation disc for MS SQL 2008 R2 DE from a downloaded ISO file - How do I create an installation disc for MS SQL 2008 R2 DE from a downloaded ISO file?


SQL Server 2005 : Backups

Restore issue - Hi, Any method to restore the database when the disk is full?

Restoring Differential backup issue - Hi all, Took Full backup created Table1 Took Tran Log backup Created Table2 Took Diff backup Created Table 3 Took Tran backup Now I am creating another database...


SQL Server 2005 : Business Intelligence

Finding Error column name in SSIS? - Hi Friends, I am trying to get the error column name and the respective value in SSIS. I googled but...


SQL Server 2005 : Development

Using Declared Variables - Hello all, I am new to SQL coding. I would like to set my declared global variable like so. However when...

need stored procedure code - Hi All. i have to tables like patient table with columns(patient name, DOB,Age,Sex) and table2 with column (regno). i want to...

sp_send_dbmail - date compare in query-variable - Hello, i have the following problem in a query-variable of the internal procedure sp_send_dbmail. 1. I have declared a variable like this and...


SQL Server 2005 : SQL Server 2005 General Discussion

sqlstate s1t00 error - I have been reading up on this, it is a very generic error. Our users tell us it is random....

How to Write Query for the Below Requirement? - Hi everyone Can Any Body Help me how to write Query for the below requirement. I have Table in my Attachment I want...

How to Write Query for This - i want to write a query to get data of two different columns with two different conditions from a single...

Auto Save / Auto recover in Management Studio? - I have come in today to find my PC rebooted, I suspect due to windows updates, (it decides to reboot...


SQL Server 2005 : SQL Server 2005 Integration Services

Sharepoint List Source Error -A possible reason might be you are trying to retrieve too many items at a time (Batch size) - [b][/b]I am trying to get data from a Sharepoint List using the following CAML Query in SharePoint List Source <Query><Where><Contains><FieldRef Name='Function...


SQL Server 2005 : T-SQL (SS2K5)

Where clause - Where "string" in any field in specified table - I have written a large complex stored procedure which produces a dataset of 39 items or so per record. These...


Reporting Services : Reporting Services

SSRS 2012 vs 2008R2 -- are there any compelling differences? - Sorry for the lame title... I"m just wondering (since I already have 2008R2 Developer) if there are any reasons to...

Use of SSRS in desktop applications like web applications - Like we use reports on web application, can we use it on desktop applications?

SQL Book Questions - Couple of SQL realted Book Questions I am seeking a book on SSRS 2012 and am considering the recently published title...

Jasper Smith's SSRS Scripter - Anyone happen to have this tool handy? I used in the past to successfully migrate a 2005 SSRS instance to...


Reporting Services : Reporting Services 2008 Development

Is it possible in SSRS? - I would like to create a drill through action that calls a report (passes parameters ) and the detailed report has...


Data Warehousing : Integration Services

Connecting Remote SQL Server DB to SharePoint using SSIS - We are trying to connect a SQL Server database on a remote server to a corporate SharePoint site. From the...

Configuration settings for SQL Server 2012? Is the major consensus to stay with file based dtsconfig or move to SQLServer Table based configs - My boss likes using the XML file based configs and passing the file in as a parameter for a DTEX...

How to import top row only and ignore other rows using SSIS - Hi, I want to import flat files into a sql table. I only require the top row each time a file...

For Each Loop Uploads the Same File Infinitely - I have set up an SSIS package to upload nearly 400 Excel files into a table. The problem I'm having...

SSIS - BypassPrepare what does this mean ? - Please look at SQL task, General > SQL Statement > BypassPrepare. It says that BypassPrepare indicates whether the task should...

SSIS mysql to mssql - I make same package ssis in visual studio 2010. I load data from mysql to mssql 2012. I created ADO.NET source(connect...

SSIS 2008 package not running the right configuration file in the SQL Agent Job - I've written and tested an SSIS package which uses an xml configuration file. When I deployed this to the Production...


Data Warehousing : Analysis Services

auto generated mdx from reporting services - hi All, Am trying to get my head around mdx generated from reporting services when a parameter is entered e.g SELECT NON...


Database Design : Design Ideas and Questions

Struggling with this Data Mart - I'm doing dimensional modeling and i'm struggling with this data mart. Can someone help? I'm building a data warehouse for a...


SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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


SQL Server 7,2000 : Administration

application issue with these SQL errors - Following error messages are logged in SQL Server 2000 instance. Database model: IO is thawed Database master: IO is frozen for snapshot The...


SQL Server 7,2000 : General

Having Problem in restoring transaction log backup - Hi, While restoring a database, which was damaged due to hardware failure. I Got the following Error Message, "The log in this...


SQL Server 7,2000 : T-SQL

TempDB best practices - Doubt - Hello guys, I have two doubt: -- 1 script: SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count],...


Career : Employers and Employees

Second stage interview - Just found out that I have passed the first stage interview for a Service Desk Team Leader role. Yay! :-D However,...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com