In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Backup Get compressed, verified, secure SQL server backups
Try SQL Backup Pro's new verification options to check the integrity of your backups in one automated process. Get faster, smaller, secure backups today. Download a free trial.
 
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 DBA Bundle Top 5 hard-earned lessons of a DBA
In part one, read about ‘The Case of the Missing Index’ and learn from the experience of The DBA Team. Read now.

In This Issue

Happy Thanksgiving 2012

Happy Thanksgiving from everyone at SQLServerCentral. More »


RAID and Its Impact on your SQL Performance

This article attempts to explain RAID's impact on SQL performance. More »


SQL Server Transaction Log Fragmentation: a Primer

Generally, you will have no need to worry about the number of virtual log files in your transaction log. However, if you use the default settings for 'auto-grow', you can end up with such 'fragmentation' in your transaction log as to affect performance noticably. How can this be avoided? How can you tell it's a problem? What do you do about it? Greg explains. More »


Editorial - Thanksgiving 2012 Bloopers

It's Thanksgiving in the US and I'm in the mountains of Colorado enjoying the holiday while skiing with my family. Hopefully many of you are having a quiet day if you're at work, or enjoying the day off if it's a holiday.

I've included a few humorous mistakes from the past month's podcasts, so enjoy them and I'll be back next week with new thoughts on the world of databases and SQL Server.

Steve Jones

Windows WMV Bloopers

iPod MP4 Bloopers

» 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:

It's Thanksgiving in the US today, a day when many people enjoy a large feast for dinner. Which T-SQL function seems more appropriate for this holiday?

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

This question is worth 7 points in this category: humor. 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.

Professional SQL Server 2012 Adminstration

Microsoft SQL Server 2012 will have major changes throughout the SQL Server and will impact how DBAs administer the database. With this book, a team of well-known SQL Server experts introduces the many new features of the most recent version of SQL Server and deciphers how these changes will affect the methods that administrators have been using for years. Loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server admin issues, this how-to guide deciphers topics such as performance tuning, backup and recovery, scaling and replication, clustering, and security.

Get your copy from Amazon today.


Yesterday's Question of the Day

Which one of the transaction Isolation levels would you use for a transaction that meets this criteria

1. The statements in your transaction should read only committed data.

2.  Other transactions should not be able to modify the data that has been read by your transaction

3. Other transactions can insert new data into the table with values that would fall in the range read by your transaction.

Answer: REPEATABLE READ

Explanation: REPEATABLE READ isolation level alone meets this criteria.

READ COMMITTED isolation level allows other transactions to modify data that has been read by your transaction. SERIALIZABLE isolation level does not allow to insert new data into the table with values that would fall in the range read by your transaction.

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

» Discuss this question and answer on the forums

 Inside the SQL Server Query Optimizer

This book will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Grab your copy today from Amazon!


Featured Script

Top 10 Wait State Issues Reporter

This script will produce the top 10 wait state issues along with their descriptions. 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

MSSql Server is in other than Active state - All, I have been getting tickets with this message "MSSql Server is in other than Active state" I looked into the error...

Using backup/restore DR tools with no DBA involvement - I'm interested in hearing from others where the company has tried to have systems / infrastructure / storage people handle all backups,...

Can we shrink database file - Hi All, We have the SQL server 2005 where it has three data files on three different LUN's. Each data file is...

DeadLocking issue - I want to confirm that I am reading the deadlock information properly. It looks like to me that there are...

Restore fails - Hello I am getting below error while i try to restore the backup from network drive Msg 3201, Level 16, State 2,...

SQL Server 2005 : Business Intelligence

Business Intelligence on the Developer computer - I am confused. I hope someone can give me some direction. From the old sql 2000 we used to create DTS...

ssis xls and xlsx files - Hi friends i have i small doubt in ssis plz tell me how to solve this issuse a folder contains...

SSIS Lookup Cache - We are developing packages in a SSIS environment using Version 9.00.3042.00. I’m presently having problems or a misunderstanding on when...

SQL Server 2005 : Data Corruption

Regarding database backup corrupted - Hello Team, I was facing an issue with database backup which I took from sql server 2005. Actually in my office I...

SQL Server 2005 : Development

Copy DB Tables from PRod to DEV - Hello, I have situation that my developer wants some tables from Prodction DB server to their desktop m/c. We have already sql...

SQL Server 2005 : SQL Server 2005 General Discussion

Automated e-mail from sql server 2005 when the CPU utilization is high - Hi Friend, Is is possilbe to have an automated e-mail from sql server 2005 when the CPU utilization is high to add...

interview questions - any info on really good interview questions for a sql server dba?

SQL Server 2005 : SQL Server 2005 Strategies

Object Relational Modeling (ORM) Examples - I really need some help on this one. One of our development teams is looking into ORM as a way...

SQL Server 2005 : SQL Server 2005 Integration Services

Destination data checking - Hi, Destination (OLE DB) has two table with existing data. (table1 : Person with Bonus and Table2: Person without bonus) The requirement is...

SQL Server 2005 : T-SQL (SS2K5)

Stored Proc - Nullable Parameters for optional filtering - handling LinkTable LEFT JOINs - Hey all, Pretty long title, but the problem itself is fairly simple to explain. Suppose I have the following tables: [code] CREATE TABLE...

transpose rows into columns - hello all, i have to transpose rows into columns. it goes like this. i have a column say for eg: NAME it...

SQL Server 7,2000 : Administration

Working with Images - Guys, I need to upload a txt file with 10 MB to SQL Server 2000. I created the file and in...

SQL Server 7,2000 : General

What is the Remote Posting Url for SQLServerCentral blog? - Hi, This is Ram(LivingForSQLServer) blogger on SQLServerCentral. I just installed Windows Live Writer to write and post blogs remotely. I have a quick...

SQL VSS - I heard that VSS get installed with MSSQL server by default in 2005 version. Is that correct? Will VSS go...

SQL Server 7,2000 : T-SQL

Help with dynamic sql - Hi All , I need a dynamic sql query (db is sql server 2000) which assigns a value = 1 to a...

SQL Server 2008 : SQL Server 2008 - General

Sql 2012 Always On - Has anyone moved to 2012 and are you using AO. If so have you had any issues with it?

how can i write 2 select statements in single procedure - Hi all, how can i write 2 select statements in single procedure.

CHARINDEX and SUBSTRING - Hi, Below given is my requirement. declare @SPos int,@EPos int set @SPos = CHARINDEX('[','{ ActionOption : [ Past Audit Window ] }')+1 select @SPos set @EPos = CHARINDEX(']','{ ActionOption...

History of executed query - Hi Experts, I want to see if there is a way to find the spid,block by witch session,what is the blocking...

How to check mirrored database - Dear, I have implemented database mirroring with automatic failover option. My principal database is a busy one. Now I am not...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. - Hi all, Am getting the above error in my procedure. this is my procedure ALTER procedure [dbo].[usp_Fixtures_BowlingAverages_Client] ( @Fixture_SeasonKey int,@Fixture_TeamKey int,@Fixture_FixtureType int) as...

Virtual Environment for Sql server ???? - Anybody has any idea any website or URL give any virtual enviroment where a sql server (mgmt ) environment is available...

SQL Server 2008 R2 Setup How to chaneg system drive from C to D - Hi, I have changed the following the "Shared Install DIrectory and Instance Directory" to D:\Program Files\Microsoft SQL Server. But the System...

How to Restore .mdf file into sqlserver 2008 without .ldf file - I have only .mdf file How to Restore .mdf file into sqlserver 2008 without .ldf file

Help! Replication high latency problem - In live environment, I have published 5 databases from MAIN-DB to REP-DB using PULL transactional replication. When I open replication monitor...

Some questions about using partition table with multiple disk drives - let's assume that I have scenario as following: - Table tbl1 for customer 1, table tbl2 for customer 2. - tbl1 has...

Unsure as to what permission would be best - Currently, I have the following query I'm running across multiple SQL Servers to get some basic stats. At the moment, I'm...

?? on adding records to a table ?? - Hi, Not sure how to set this up? INSERT INTO PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION OID_LINK = '003A19E3D3B040958EFA16EB2585B0DC', OID=oid SELECT PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION.OID, PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION.OID_LINK FROM dbo.PROVIDER_TO_TRUSTED_PROVIDER_COLLECTION where OID = '002771F071C541F4B5478D246381E7E7' I want add '003A19E3D3B040958EFA16EB258

What ports SQL Server uses for LogShipping: - Hi, We have configured log shipping in SQL Server 2008 R2. Primary Server A has a named instance on port 1234 Secondary Server...

daily differential back up failed - guys: When i am running the differential back up using the same maintanace plans i had earlier its erroring saying: BACKUP...

two almost identical triggers working fine except one line in each - I have two triggers that fire upon an insert that fires both simultaneously with no problems except this one line...

SQL Query: Subtract data from consecutive rows uptil non zero value reached. - I need to write a query to subtract data from consecutive rows uptil a non zero value is reached. For Current...

Using Update Statement and vars to remember value from previous records - Is the following possible. declare @prevStatus char(1) update table1

CONTAINS Keyword. - Hi All, I have a list of 40 sensitive words that I need to match on a varchar string. I have...

merge replication across different networks - Hi I have two servers, they both on a different site(hence being on different networks). I need to be able to...

Deny Backup - Is there a way to deny a login from backing up any database on a sql instance, or will I...

migration form sql 2005 to 2008 - HI every one I have SQL SERVER 2005 on Windows server 2003r2. We want to upgrade it from the SQL...

To OTAP or not to OTAP? - Currently I am working in an organization, where development and production databases reside in the same SQL Server instance. The...

Time out in insert statement - Hi every body I have a large table (270 G) and i only have insert and select statement over this table....

rebuild index worsens query performance - we have a query which runs several times a day reads from several tables in one database ("W"), and two tables...

Capacity Planning - Hi All, I was asked to do capacity planning of SQL 2005 & 2008 servers. What are some of the things you...

Open Event Viewer from SQL Server - Working in restricted environment is pain.. and not having access to windows server is double pain.. Sometimes, I need to check...

Removing data collection jobs - All, I have been able to successfully disable data collection, but when attempting to delete the jobs, I get: "The delete statement...

SQL Server 2008 : T-SQL (SS2K8)

Total number of DMV in SQL Server - Hi all, Since there are number of DMV's present and there is no proper presentation on it. So if suppose some...

Input Validation - Hi I want to validate my input parameter, which is policy Id (int) So I have: declare @Valid_PolicyId int select @Valid_PolicyId = count(*) from Selestia_BI_Staging_Source.dbo.PR_REB_SNAPSHOT...

Update statement question - I'm doing a DB review and have always been taught to write updates with a Join like using this syntax. [code="sql"] Update...

Query Analyzer - Whenever I click on New Query window by default " Begin Transaction Rollback" statement appears.. How to remove this.. Is there need to...

Optimization of stored procedures - Hi, I have a number of stored procedures used for reporting and whenever we run these reports it slows down the...

Format date time - I need to output datetime data in this format: 11/19/2012 8:54:59 AM, so mm/dd/yyyy (h)h:mm:ss AM/PM I didn't think it would...

switching address with Subtring & len funtion - I have something out of order what is it I'm not sure, I want to reverse the field(s) Doe.John@CompanyABC.com to ...

How do I get database space used - Hi, How do I get database space used (not database size) for all given databases? At this moment, I do have a...

SQL Server 2008 : SQL Server Newbies

Two Rows Returned as One for Reporting - Hi, I've got a problem that may or may not have a possible solution. What I'm essentially trying to achieve is...

New SQL Server Database with security - Hi All, we have SQL Server that has several databases and users can access the databases, the problem is that we...

New DBA job, done some digging on Database and have some concerns! - I've recently started a Job at company working as the sole DBA. I come from a .net developer background and...

Stored procedure help - [code="sql"]I have a staging table which has unprocessed records of the batch.. I need a query which should validate the unprocess...

Alternative to views - Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2...

Name query resultls by different column names - Hi, I will try to explain myself as clear as I can: I am having difficulty trying to list query results...

SQL Server 2008 : SQL Server 2008 High Availability

Loss of connection between Witness and Principle - Hi all i was checking our mirroing this morning and noticed in the Mirror monitor that our Mirror server could not...

SQL JOBS Not Functioning - SQL JOBS Not Functioning :w00t: I have Stored procedure & that stored procedure needs to be executed on 20 Databases daily, Currently i...

SQL Server 2008 : SQL Server 2008 Administration

Writing speed much slower in SQL Server than a simple file copying - Hi, I have a doubt as writing to disk by the Sql Server. We use the server with the settings below,...

Plan Cache Size Discrepancy - Hi All Why do the following 2 queries yield different results? [code="sql"] --1 SELECT sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs] FROM sys.dm_exec_cached_plans --2 SELECT SUM(single_pages_kb)/1024+ SUM(multi_pages_kb)/1024 [Total...

SQL Server Agent - Unable to determine if the owner of job has server access. - I've read a few posts regarding similar messages, but I don't seem to be able to find a specific answer...

system databases mirroring - On the mirroring server (The server which is mirroring the production server databases), should it also mirror the system databases...

Setting Notification email (When the job fails) - Hi Everyone, I have setup a job(SQLS ERVER 2008 R2) in two servers Server1.Domain1 & Server1.Domain2 and I have enabled notification email...

backup/restore process - Hi, Hi, Our business can afford to loose data up-to one hour. So in-case of say if a table is dropped or a...

Tempdb log file location and Filegroup optimizations - I've been reading articles about tempdb optimizations and I already understand a lot better the different ways to archive it....

delete in batch makes drive space almost full - I have a maintenance task that cleans a table daily on a testing server. Because it is a deletes some...

Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

Programming : General

"instead of delete" trigger is not fired from "after" trigger - Hi everyone, I have a problem that "instead of delete" trigger is not fired from "after insert" trigger. Reading BOL it...

SQLServerCentral.com : Anything that is NOT about SQL!

Career Advice Needed - I have an interesting dilemma, and could use some advice. I have spent the last two years as a Network...

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

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

SQLServerCentral.com : SQLServerCentral.com Announcements

What is the Remote Posting Url (in Windows Live Editor) for SQlServerCentral? - Hi, This is Ram(LivingForSQLServer) blogger on SQLServerCentral. I just installed Windows Live Writer to write and post blogs remotely. I have a quick...

Reporting Services : Reporting Services

Cannot connect to Reporting Services with Managment Studio - I've just set up a new instance of SSRS 2012 and I'm unable to connect to it through Management Studio....

Reporting Services : Reporting Services 2005 Development

How to invoke Aspx page function from reports in SSRS 2005 - I've an Aspx page on which i'm showing an SSRS Report using Report Viewer, now i've a function in that...

Database Design : Design Ideas and Questions

Advanced group design - Hi All I ask you guidance on this design question because this seems to be one of those things where if...

Lots of Key Lookups vs. UniqueIdentifier Clustered Index - Hello. I'm working on a system that was originally designed with UUID (uniqueIdentifier) clustered indexes. Later the clustered indexes were rebuilt on...

Data Warehousing : Integration Services

Weird thing in SSIS - Hello there, I have a package that exports to a table when I debug and when i ran my package...

How to export the first 100 rows of an excel file to a flat file - How to export the first 100 rows of an excel file to a flat file ? In excel file have 2000...

Data Warehousing : Strategies and Ideas

Building SSAS Cubes - Beginner - Hi, Is it mandatory to have a data warehouse in dimensional model to build a SSAS cubes above? I'm trying to see...

Fact and dimension structior for Ticket tracking - Fact and dimensional structure for Ticket tracking (Sorry can't edit the topic) Hi, I am new to Dimensional modeling and SSAS reporting....

Datawarehouse - My PO keeps asking for more tempdb - I think we have all been in this situation before. Devs fill TEMPDB then say they need more TEMPDB. Here is...

Combining businesses with people in one dimension - In the application data that I wish to transform into a data warehouse, people and businesses are basically treated in...

Use the surrogate key - Here is the scenario. I have a fact table that looks like the following: [u]factEmployeeWorkHours[/u] PK ID LastName FirstName Hours The fact table joins to a...

Unknown dimension values or - In your DW do you always create an "-1 Unknown" record for each dimension? For example in DimEmployee: [code="plain"]EmployeeKey -1 EmployeeName "Unknown" ect.[/code] Or...

Does it make sense store measures in a dimension table? - Hi everybody: Right now I´m working in a BI solution and I received a set of tables with a multidimensional structure....

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

Show Right Column to Right User - If I have three different user with different occupation (manager, salesman, accounting) The main question is to how display right column...

Data Warehousing : Analysis Services

Store Comments in Cube - Hi everyone, I need some pointers with my problem. I have a financial application cube in SSAS 2008 R2. My client...

Creating measure groups/folders - I have an existing cube, which I will be adding a lot more measures to as we will now be...

Microsoft Access : Microsoft Access

refreshing subform after right click and filter field in main form - i am right clicking a non key field in my main form and filtering by a quote number. The key...