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

Could You Live Like a DBA?

Steve is presenting at an event today out of town, so we have a guest editorial.

Have you ever wondered how everyday life would pan out if you took the DBA approach to life's events? Here are a few thoughts.

Backups: You have a nightly backup routine. This means a differential backup every night, when you tell your spouse everything that has happened during the day in minute detail, and a full backup on weekends when you tell them everything that has happened during the week in your entire life. I Don't think either of you would sleep much.

Replication: You are on the phone continuously, telling your twin sibling at a remote location everything that you are experiencing. Everything. What you see, hear, smell, eat, work on etc. Your twin, of course, has no life of her own, and is living vicariously through you, ever prepared for you to drop dead so she can take over. Talk about sibling rivalry.

Restores: Remember that nightly backup you transferred to your spouse earlier? You periodically and randomly ask your spouse to repeat some of those conversations back to you, ensuring no detail is skipped, so you can confirm that the backup is working. Marriage counselor's number handy?

Scans: When you go looking for the soy sauce in your pantry, you read the label on every single item in there: all the spices, all the jars and bottles, every bag of chips, every bottle of juice and soda, everything, because the items are arranged in no particular order. It takes you a few hours to put together one meal, and your kids have gone to bed hungry.

Seeks: After scanning through your pantry multiple times and getting tired of how long it takes each time, you decide to rearrange all the items in order by alphabet. Now you can retrieve the soy sauce in no time. The kids are happy again.

Fragmentation and Re-indexing: Over time, as your spouse adds items to the pantry at random locations (because he is not a DBA) and removes items leaving gaps on the shelves, the ordering gets so out of whack that you revert to going through all items again to find the soy sauce. Eventually, you decide to create an automated re-indexing plan by having your spouse rearrange everything by alphabet once a week. Divorce lawyer's number handy?

More Backups (because, you know, a DBA can never have enough backups): When you prepare a sandwich for your kid's school lunch, you also make a second sandwich. You pack the first one in your child's lunch box, and you courier the second one to arrive at school just in time for lunch. You don't tell anybody that you have a third one in your car, you know, just in case...

Adding Records: When you discuss baby names with your spouse for the upcoming addition to your family, you very briefly consider an integer for the name, and then even more briefly think that a tinyint would be more appropriate, before you think of the divorce lawyer and decide to shut up. You do, however, bring up the Bobby Tables joke because you think it's funny. Your spouse does not.

Searching: You wonder on a daily basis why you can't just enter this into Google's search box: select * from closeby_restaurants where cuisine = 'chinese' and distance_from_home <= 2 miles and deliciousness = 'awesome' and service_time = 'quick'...

Normalization: When your spouse asks you for a copy of your shopping list before he goes shopping, you point him to the location of the original list instead because you do not want to create duplicates. He throws his hands up and leaves without your list. Your items never get bought.

Normalization 2: You do not have a driver's license, or a bank account, or a passport, or a mortgage in your name, because you are incapable of filling out any forms in duplicate, leave alone triplicate.

Set-based Operations: After the kids have gone to bed and you sit down with your spouse to watch some TV, you roll out all the TVs, desktops, laptops, tablets and phones in your house into the living room and fire up a different episode of your favorite show on each one at the same time, because you know that set-based operations are better than episode-by-agonizing-episode. The kids were very entertained when you did this with their cartoons. But the spouse - not so much.

If your domestic life isn't exactly a picture of happiness and you're getting grief from your spouse, kids, and siblings, now you know why. You're a DBA!

Hakim Ali from SQLServerCentral.com

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


ADVERTISEMENT

Use Deployment Manager for free! For up to 5 projects. Try it out and deploy software and databases faster and easier than ever.

ADVERTISEMENT
SQL Toolbelt

Want to work faster with SQL Server?

If you want to work faster try out the SQL Toolbelt. "The SQL Toolbelt provides tools that database developers as well as DBAs should not live without." William Van Orden. Download the SQL Toolbelt here.

SQL Monitor

Custom metrics from SQL Server MVPs

SQL Monitor is the only tool with a free library of custom metrics from SQL Server MVPs. Find new metrics for your servers.

SQL Source Control

Get your SQL Server database under version control now!

Version control is standard for applications, but databases haven’t caught up. So how can you bring database development up to speed? Why should you start? Find out…

Featured Contents

 

Configuring Database Mirroring

william thier from SQLServerCentral.com

This article contains a set of instructions for configuring SQL Server mirroring, including pre-requisites. It also includes notes on how to resolve a few issues that I have encountered. More »


 

PowerShell the SQL Server Way

Additional Articles from SQL Server Magazine

Although Windows PowerShell has been available to IT professionals going on seven years, there are still many IT pros who are just now deciding to see what the fuss is all about. Depending on your job, you might find PowerShell an invaluable tool. Microsoft's plan is that PowerShell will be the management tool for all of its servers and platforms. For most IT pros, it's not a matter of if you'll be using PowerShell, only a matter of when. More »


 

From the SQLServerCentral Blogs - Rebuilding server level collation setting

CarlaAbanes from SQLServerCentral.com

Scenario You have just completed setting up a new development database server for a new project. After that, everyone in... More »

Question of the Day

Today's Question (by Steve Jones):

You are getting ready to enable TDE on the database named "CRM". You have created a master key already in the master database and in the CRM database. In which database do you need to create a certificate to protect the database encryption key?

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 1 point in this category: Encryption.

Did you miss yesterday's question, FTS Near Operator? Answer now.

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

Professional SQL Server 2012 Internals and Troubleshooting

The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Get your copy from Amazon today.

Featured Script

The Ultimate Index Width Stats Reporter

Jesse Roberge from SQLServerCentral.com

Util_IndexWidth
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Reports index length stats for indexes who's lengths exceeding parameterized thresholds (defaults to 0 - get all) for
total length and seek length, optionally filtering schemas and tables.

Required Input Parameters:
none

Optional Input Parameters:
@SchemaName sysname='' Filters schemas. Can use LIKE wildcards. All schemas if blank. Accepts LIKE Wildcards.
@TableName sysname='' Filters tables. Can use LIKE wildcards. All tables if blank. Accepts LIKE Wildcards.
@MaxLength int=0 Limits output to indexes with sum of all column max lengths exceeding this (OR with @SeekMaxLength)
@SeekMaxLength int=0 Limits output to indexes with sum of seek column max lengths exceeding this (OR with @MaxLength)

Usage
EXECUTE Util_IndexWidth 'dbo', 'order%', @MaxLength=250, @SeekMaxLength=100

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.

see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

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

Excecution plan slows - Hi, We have problems with a view created from a select sentence with a few joins. This view runs properly some days...

orphaned users in sql server - hi this is reddi Krishna i created a new login at server level and mapped to user level permissions after...

how to move table from .mdf to .ndf - I have table of 150GB. I need to move it from E:\SQL\database.MDF location to k:\SQL\database.NDF. can help me. Thanks in advance....

BACKUP WITH NORECOVERY - CAN Any one clarify my doubt please, What exactly BACKUP ......... WITH NORECOVERY will do Difference b/n BACKUP... AND BACKUP...WITH NORECOVERY thanks

SQL Server 2005 : Backups

Monitoring script - what is monitoring script in sqlserver 2005 how to implement it?

SQL Server 2005 : Business Intelligence

OLEDB Error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER - I have a OleDB connection mgr being used in a SQL Server Destination task in a pkg in which I...

SQL Server 2005 : Development

Hashbyte in persisted computed column? - Hi, I'm trying to create a persisted computed column using the hashbyte function. But when adding this column to my table...

SQL Server 2005 : SQL Server 2005 General Discussion

code help where clause - What is the correct way of coding this WHERE clause? By putting or, I am getting some records with the...

SQL Server 2005 : SQL Server 2005 Security

Restrinct for insert update and delete from a specific table - hello experts, In my existing database one table we are going to create like Sql_Audit_table. I want to restrict each and every...

Can it be possible SQL Login creation with an empty password - Hi I would like to know that, Is that possible to create SQL Login with blank password in SQL Server... Please advise...

SQL Server 2005 : SS2K5 Replication

Snapshot Replication Error - Hey guys, So I have setup replication between our source production database to replicate to our reporting database so we are...

SQL Server 2005 : SQL Server 2005 Integration Services

FTP is not sending user define varible - I am using SSIS package. FTP Task Editor=> IsLocalPathVarible:- True Local Varible :- User::Final Operation :Send Files ISTransferASCII :- False Remote Parameter IsRemotePathVarible :-False REmote Path :- \Location OverwriteFileAtDest :- YES Final:-D:\test20130724_230456_662000000.xls which having...

SQL Job does not executes SSIS package but Job goes to executing state and don’t stop. - Hi, There is one SSIS package which is executed successfully when run from Visual Studio. When the package is executed through...

SSIS job failing intermittently, connectionmanager failing - I have an SSIS package running fine 90% of the time, but for stretches of the day it fails over...

Event Handlers in SSIS(OnError & OnPostExecute) - Hi, I am using a SSIS package to write the records to the flat file destination. During this process I...

SQL Server 2005 : T-SQL (SS2K5)

What is Cross Apply ? - Hi I'm newbie to SQL ... Please explain in simpler words. What is Cross Apply ? Examples pls...

basic sql question - Hello! First time post here... hoping for help with what is probably a very, very simple question.... I have a table...

SQL Server 2005 : SQL Server Newbies

Need urgent help with query - Hello friends, One table (table1) cardno, datetime1, channel_no another table (table 2) cardno, datetime1, channel_no values in channel_no can be either 1 or 2. I...

Barcodes Code128 generator function - I'm looking for a [b]barcode generating Function[/b] (in SQL 2005), that uses the standard [u]Code128[/u]. The result of calling the...

SQL Server 7,2000 : SQL Server Newbies

Backup failed - Hello Maters, I have one SQL Server 2000 in my database environment, Our wintel team gimme a message that their filesystem...

SQL Server 7,2000 : T-SQL

Sorting issue when meet there is ASCII - Hi there, I am facing a sorting issue explained as below and looking for help please, Result from first query, Select...

SQL Server 2008 : SQL Server 2008 - General

Conditional print for testing scripts. - Dear Reader, For testing I want to replace PRINT statements with a procedure call. A Global substitute should replace all 'PRINT' with...

SQL 2008 SSIS package runs in BIDS but not job - I have a SQL Server 2008 64-bit SSIS package that runs just fine in BIDS. However, when i create and...

add an url link in a pdf report using report builder - This seems to be a simple question but I haven't found a way to do it. I have a report...

How can I write this in single select query ? - Hi I have a requirement which is little bit tedious as of now, can anyone please help me to solve...

Impersonation and connection pooling - Hi, I'm working on a project to improve the application and SQL Server security. We have some applications that access the server...

How to change default port 1433 - Hi, One of our Client requests to move away from the default SQL port, if that is port on 1433 Can...

Unknown Traces - Keep restarting when stopped - We have an application that detects when tracing is enabled on our SQL server and it has been requested that...

DELETE FROM Table WHERE PKID = 0 -runs for over 5 min without completing - We have a table, and just noticed if we try to delete a row from it (for example the first...

TDS DONE Message - Hello, I am facing a "problem" with the TDS network protocol. When I send a batch like the one below I...

SQL dependency - Is there any free script or software for SQL dependycy? Red-gate has one but need to purchase.

Edit multiple stored procedures - I need to create 5 test databases by copying the live db's. There are many stored procedures in each db....

How get some informations about MSSQL (options , memory...)? - Hi everybody , I need help : 1) I want to know the value of options "Partitioning" and "Bit-Mapped" (In oracle , informations...

Can someone help me with a script please? - Hi, I got very nice assistance last time i had an issue on this site. Can anyone assist me? I would...

SSIS Package with a Send Mail Task works perfectly within BIDS but does not work in a SQL Agent Job. - Hi, I have a SSIS Package with a Send Mail Task in it. This works perfectly within BIDS...I can get the...

Fuzzy searching - Hello everyone, I'm having trouble finding a way to use fuzzy searching (specifically Levenstein) in my database. For now, I've...

RAID 5 Vs RAID 10 Performance Questions - I'm guessing this could lead to a lot of theory and complex answers, however, I'm curious on your thoughts between...

checkpoint for committed transactions.. - Hi Can some body say that Is Checkpoint will write pages only from committed transactions... please help me on this.

Need A Help in DATA MASKING in SQL SERVER 2008 - Hello Friends, I want to mask certain fields in employee_bank_account_info table, which are very sentive information. I searched for it , i found...

MSDTC errors - SQL server stopped responding to any request - Hi Rather a strange issue with multiple SQL server instances - SQL server abruptly not responding to client applications for a brief...

SSIS list file names in directory - Hello All, I am trying to create an SSIS package that will look at a directory, get a list of all...

SQL Server 2008 : T-SQL (SS2K8)

relative position of field in a recordset - I need to know the value in a field at a variable offset, or relative position, in a cursor. (I...

Need help to build query - [code="plain"] create table sample ( a date,b date, c date ,d date ) insert into sample values (null,GETDATE(),GETDATE(),GETDATE()) insert into sample values (null,GETDATE(),GETDATE(),null) insert into...

audit my code for current request_id? - Hai friends, I made on web appplication depends create table onward_joureny ( joureny_id int identity, departuredate datetime, from varchar(100), to char(100), is_draft varchar(100) ) create table users ( user_id varchar(100) PK, username...

handle data which contains space - Hi all, I am having table JobDetail whcih contains jiobname as a one of column.here the data entered from front end...

tsql function with dynamic number of parameters? - Hi, in a SELECT, I would like to have a function which returns the minimum of dynamic list of values, like...

How to code for already exist date message in my porcedure? - Hai friends , i m creating web application behind using sql server 2000 these is my table structure: create table journey ( joureny_id int...

Using DelimitedSplit8k against a Space Delimited Field - Problems - Hi All - I came across Jeff Moden's Splitter Code earlier today and have been trying to run the function against...

Is it possible to return a value from a SP like this? - Or should I really be using a function? That's what I was attempting at first, but got pretty stumped on...

Audit log for INSTEAD OF DELETE trigger - Hi, I'm trying to write an INSTEAD OF DELETE trigger that also logs some data about the attempted delete. It doesn't...

Simple Interview Question - select 5 & 17 What is the Output? Ans is :1 1)how it will return 1 ? 2)what is the use of...

Temporary Functions? - I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again....

SQL Server 2008 : SQL Server Newbies

Trying to convert varchar to datetime - I am trying to convert 20114 which is stored as varchar to datetime but I am getting the following error Syntax...

SQL Server 2008 : SQL Server 2008 High Availability

shrink data files - we have deleted some rows and released around 500 GB of space from a table. We applied filters so that...

log shipping restore job - Sometimes our log shipping restore job takes too long to complete. Though the file sizes(around 1 GB) are small it...

Splitting and shrink a Windows Volume without Cluster's downtime? - Ok. Got a quick question, maybe for Perry or whoever can clarify. Got a SQL 2012 failover instance running on Windows 2008...

Replication configure in Log Shipping Database - Hi, We have 5 databases in two different servers and its already configured in log shipping and its running successfully also....

SQL Server 2008 : SQL Server 2008 Administration

Cluster backup drives - I cannot see the main backup drive in SQL cluster when attempting to do a restore. The backups for the...

Webservice to establish connection with SQL Server - Hi, Can i get a sample [b]webservice [/b] which can: 1. Connect to SQL Server 2008 2. Take 'Database table name' as input...

delete old SSIS log files using maintenance plan - We have a SSIS log folder that has a lot of daily log files for SSIS packages. I am hoping to...

Job STep with multiple commands - Good day I want to make a job step,but it must do various commands.The steps is as follows. 1.Copy a file over...

shared & Exculsive latches? - Hi, PAGEIOLATCH_SH --------------- wiat_time_S 175094.26 Pct 1.05 Running_Pct 96.99 PAGEIOLATCH_EX -------------- wiat_time_S 119320.6 Pct 0.71 Running_Pct 98.53 these shared & Exculsive latchs acquired in waits stats in cummulative number since restarted server,...

litespeed restore - we have db backup file(litespeed backup file). Litespeed is not installed in the server. we need a command to restore the litespeed...

LCK_M_RIn_NL lock blocking inserts - Has anyone seen this specific lock contributing to a significant wait/blocking time? What could be the cause/solution? The description here is...

Career : Certification

How many tests do I need for 2012 after I get my MCITP - OK, I'm desperate to figure out how many exams I have to take after I finish the SQL 2008 exams...

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Programming : XML

Render XML report using XSL transform - Hi, I am upgrading reports from 2005 to 2008R2 and I noticed that two reports are failing on the new...

Programming : Powershell

Powershell error - Ok in ISE , fails in Agent job - Morning all. I am trying to run script to get Translog details. The script works fine in ISE and produces report...

SQLServerCentral.com : Anything that is NOT about SQL!

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

SQLServerCentral.com : SQLServerCentral.com Website Issues

What happened to "Yesterdays Question" and answer - I love "Todays Question" and have picked up quite a few tidbits that have been very useful. Often even more...

Reporting Services : Reporting Services

tablix countrow ?? - Hi, I have a report using a tablix with a row with uses child column with expression =countdistinct(Fields!DayofMonth.Value) puts a 1...

Column appearing blank in SSRS query builder but fine in SQL Management Studio - Hi All, I've created a column in by sql query where by if i run the report from 27/05/2013 to...

SSRS Report runs in BIDS / Not in SharePoint - Hi geniuses How come a report runs in BIDS, but not when deployed to SharePoint 2010. No problems with the query, the...

SSRS Data Driven Subscriptions - Hi all, I'm relatively new to SSRS (my version is 2008 r2) so I apologize if this is a question that...

Data Warehousing : Integration Services

SSIS - Can anyone please tell how to import data from an excel file to sql server table in SSIS using a...

Flat File Source -> DB table overwrite in T-SQL 2008R2 - Hello SQLSC, I am extremely new to SQL. As you can tell is my first post on the forum, I have...

Problem with Data source, Pls help me - Hi All, Greetings! I have requirement like, -- We are maintaining queries in a table -- By using single ssis Package we need to...

Add row value from a data flow to an object variable - Hi, I would like to add a column value in a data flow Task to an object variable using the script...

Source data in multiple languages - Hi, confusing myself with possible options for this scenario - Reporting from our DW must be in both English and French, therefore...

Data Warehousing : Analysis Services

Please help with the mdx query - Below is the calculated member [Estimated Value]= IIf([Measures].[PRC]=0,null,Sum([Product].[MM#].members-[Product].[MM#].[All],[Measures].[PRC])/[Measures].[MM Count]) SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Value]} ON COLUMNS, { ([Business Unit].[Business Unit].[Business Unit].ALLMEMBERS )} ON ROWS FROM...

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