In this issue

Featured Contents

Editorial

Featured Script

 
 advertisement
 
SQL Monitor "It's the freaking iPhone of SQL monitoring"
everyone just gets it…that has tremendous value" - Rob Sullivan, DBA, IdeasRun. Get started with SQL Monitor today - download a free trial
 
SQL Prompt Make SQL effortless
SQL Prompt 5.3 is packed with features to make writing, editing, and exploring database code effortless. From code completion to SQL reformatting, SQL Prompt 5.3 handles the routine so you can focus on the tricky bits. Try it now.
 
SQL Backup Pro Are you sure you can restore your backups?
Run full restore + DBCC CHECKDB quickly and easily with SQL Backup Pro's new automated verification. Check for corruption and prepare for when disaster strikes. Try it now.

In This Issue

Dynamic Grouping in SSRS Reports

This article shows how you can create a dynamic report in SQL Server Reporting Services (SSRS), walking you through the steps needed to create a dynamically-grouped report. More »


Check SQL Server Virtual Log Files Using PowerShell

In a previous tip on Monitor Your SQL Server Virtual Log Files with Policy Based Management, we have seen how we can use Policy Based Management to monitor the number of virtual log files (VLFs) in our SQL Server databases. However, even with that most of the solutions I see online involve the creation of temporary tables and/or a combination of using cursors to get the total number of VLFs in a transaction log file. Is there a much easier solution?  More »


SQLSaturday #153 - Salt Lake City 2012

A free one day training event in Salt Lake City, Utah on Oct 20, 2012. More »


From the SQLServerCentral Blogs - Multiple CTEs

Multiple CTEs It’s somewhat obscure in BOL and I wasn’t able to find any examples but it turns out you can... More »


Editorial - Wasting Time

I ran across this infographic on wasting time at work. From the title I was thinking this would point out the ways in which people avoid work, and perhaps it does, but it's really geared towards showing us the framework and structure that many people have for work. I agree with the first two sections of the page that show email and meetings waste a lot of time. I certainly think they have in many jobs for me, especially when they are used as "catch-all" techniques for including everyone that is remotely relevant to an issue.

Interruptions at work are hard to quantify as a problem. They definitely can be, but stopping by someone's office to ask a question or take a quick break can be a way to recharge yourself between long periods of concentration. The issues come into play when the other person is in the middle of focusing on a task and you force a context switch on them. Recovering from the interruption can take time, time that's often wasted as a person tries to remember exactly what they were focusing on.

Meetings certainly interrupt the day, but since they are often planned in advance, you can be mentally prepared for the break. It makes me wonder if there wouldn't be some benefit to scheduling some "open time" in your day where you plan on taking a break. Others that needed a minute of your time would know to come find you at that time.

Time is very valuable, one of the most valuable resources we have at work. Management should be aware of this and working to limit interruptions, whether through email, meetings or anything else that prevents work from getting done. The fewer meetings and emails you require of your developers, the more time they have to work on the tasks they are being paid to complete.

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


The Voice of the DBA Podcasts

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

Everyday Jones

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

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.

You can also follow Steve Jones on Twitter:

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


Question of the Day

Today's Question:

When a database transaction is rolled back successfully, the data returns to its previous consistent state. Does the seed value of an identity column also get reset to the previous value?

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

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

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

 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!


Yesterday's Question of the Day

begin try

Declare @ctr int

select @ctr = 1

while @ctr <= 10
 begin
  select '1'
  if @ctr = 4
    select 10/0.00001

  select @ctr = @ctr+1
 end
end try
begin catch

select @ctr

end catch

select @ctr 'Counter'
What is the final counter value?

Answer: 11

Explanation: The loop will run through iterations until the value reaches 11, at which point it will end.

» Discuss this question and answer on the forums

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!


Featured Script

SQL Server Backup, Integrity Check, Index and Statistics Maintenance

Solution for Backup, Integrity Check, Index and Statistics Maintenance in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. 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

how to execute batch of scripts by one DTS or SSIS - I need to know how to execute batch of scripts stored in some folder one by one through DTS or...

IO intensive .ldf files - Hi there, Would like to know whether there is a way to see which .ldf files are most IO intensive? Regards IC

Deleting the tables which are not been used from long time. - HI Experts, Actually we are planning to replicate the prod data to our DR center. So, thinking to replicate only that...

Can Awe be enabled - We have SQL Server 2005 Standard Edition SP4 and Windows Server 2003 64 bit. Is it possible to enable AWE and...

how to get most queried db in the ms sql db server - Hello All.. My boss asked me to give the list of top 5 most queried database in our db server(Ms Sql...

query to find index on a column - Is there a way i can find all the indices on a given column?

SQL Server to Teradata - Ours is a dataware house environment currently using sql server and planing to migrate to Teradata as it is very...

SQL Server 2005 : Backups

Excessive log growth in SIMPLE recovery model database - SQL 2012 - Hi, Apologies if this has been posted before, but I keeping coming across excessive log growth on my reporting databases that...

SQL Server 2005 : Business Intelligence

SSIS PACKAGE - rerer

SQL Server 2005 : Data Corruption

Execution plans - Hi All, If query is not using execution plans, how we can find it and what is the action required from...

SQL Server 2005 : Development

sp_send_dbmail attachement EXCEL file has error - Hi All, Need you help on below. I am generating the excel file and send email using "sp_send_dbmail". I even receive the...

SQL Server 2005 : SQL Server 2005 General Discussion

How to Set Minimum and Maximum memory in AWE SQL2005 - Dear All, I have 16 GB server physical ram windows 2003 EE 32 Bit. I want to enable AWE with :- Minimum 7000...

foreign key and sys.dm_db_index_usage_stats - I have a question about a foreign key and how it relates to index usage stats. We are trying to...

SQL Server 2005 for 64 bit version OS (Windows 7) - Guys, I have Windows 7 64 bit. Now I would like to purchase MS SQL Server 2005 developer edition. Can you...

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 Security

SQL Server 2005 Express - Hello all, to say that I am new to SQL would be an understatement. At my company, I have been...

SQL Server 2005 : SQL Server 2005 Integration Services

To insert varchar records in datetime field - Hi, I have table "Trial1" One of the columns is DATERECEIVED DATETIME NULL Loading data from Excel to staging table using ssis...

SSIS Flat File Import - CSV Files with empty information - Hi All, Hopefully I can get a bit of help on the below. Firstly a bit of background I have a...

SSIS Import/Export Failed. Duplicate Key even w/ "Delete Rows" Checked - Running SQL 2005 SP4 I am trying to use the Import/Export Wizard to copy 40 table from Server A to Server...

How to Extract Multiple tables using SSIS - Hi, I want to Extract(Export) 4 tables from SQL SERVER 2008 R2 to a Flat File. All the four tables are related...

How to insert records based on Audit Table - Audit table: CREATE TABLE [dbo].[AUDIT_RECORD]( [FILENAME] [varchar](100) NULL, [LOAD_DATE] [datetime] NULL ) Insert into [dbo].[AUDIT_RECORD] values('Sample',12/09/2012) Insert into [dbo].[AUDIT_RECORD] values('Sample',13/09/2012) ---------------------------------------------------- DB Table: CREATE TABLE [dbo].[Sample1]( [pKey] [int] IDENTITY(1,1) NOT...

Matched and Unmatched Rows in ssis 2005 - Hi, Need to transfer data from database Db1 (Db1.sampletab1) to database Db2 (Db2.sampletab2) using ssis 2005. Condition: If matched data, leave as it...

Getting Error for Dynamic file name - Hey I am also working on same task like this In SSIS ftp task... Here I created a variable for getting...

SSIS Package Not Importing Last Row in Flat File - Hi all. I have an SSIS package that imports a bunch of .csv flat files. I am using a Flat...

SSIS ForEach file on FTP site - Hello all, Please forgive this "newbie" question. I'm currently migrating from DTS to SSIS and my google skills aren't finding the...

SQL Server 2005 : T-SQL (SS2K5)

How to Build a single row with groups of fields from several rows per key - i have several rows like: Customer sn val1, val2, Val3 ..... (sn is a seq# from 1 to 15) A 1 31 54 A...

Extract string before character occurrence. - Hi, This is bugging me, the solution must really be easy. I have a column in a table with a variable length...

Funky happenings with Data Export Wizard - I am trying to use the DEW to export the return set from a stored procedure to an Excel file. This...

SQL Server 2005 : SQL Server Newbies

Test - Test Post

How to monitor a Production Database in SQL Server 2005 - Hi, Is there any inbuilt procedure exist to monitor a [b]database in SQL Server 2005[/b]? or How to monitor a...

SQL Server 7,2000 : Administration

Update stats process not getting killed - Windows server 2003 SQL Server 2000 enterprise edition. 32 bit. Hi, I have set up a maintenance plan to update stats. Unfortunately...

SQL Server 7,2000 : Backups

Backup stored procedures,views,user defined functions from a db - Hi all! Is there a way to backup/dump all stored procedures, views, user defined functions and also triggers (if possible) from...

SQL Server 7,2000 : SQL Server Newbies

Count duplicate occurances in relational tables - I am fairly new to SQL and pressed for time. I don't think this is difficult to do but I...

SQL Server 7,2000 : Replication

transactional replication subscription deleted automatically SQL SERVER 2000 - I have replicated(transactional replication) databases in sql server2000. it had some scheduled time for doing replication. but after few days...

SQL Server 2008 : SQL Server 2008 - General

move transaction log to a different drive. - Hi SQL Guru's, When we migrated our mission critical application to a different server, one DBA placed the transaction log in...

DBCC PAGE - Hi SSC, Im not an advanced DBA so apologies if this question seems simple but I would like some guidance. I...

Restore without sysadmin permisions - I have a team that need to restore databases provided by clients, but I cannot give them sysadmin permissions. At the...

upgrade info - recently i upgraded sql 2000 to sql 2005 , where can we find the last upgrade information of sql server ?

Update records? - I have been hearing lately that SQL Server when performing update to any table, first deletes that set of records...

Alternative process except by doing using linked server? - Hi SQLites, I want to update the data from one sql server to another sql server. Is there any alternative process except...

Average Value based previous record Avg Value - Hi Experts, Here is another scenario,th some sampe data [code] CREATE TABLE #sample ( id int identity , weight float ) insert into #sample select 10...

help with log shipping - Here is a log shipping scenario."Log Shipping is set from Server A to Server B for database XSQL Server 2008...

Data Purge, please help - We are supporting a new application which is in the development stage. Prior to moving the application to production we...

Cleanup txt files from Z:\drive issue? - I have job setup on all the PROD servers(2005, 2008r2, 2012). The Job will generate a .txt file every day for...

Working on SSIS & SSRS with SQL Server Express Edition... - Hi Friends, One of my co-worker needs to connect & work on SSIS & SSRS. We are planning to install SQL Server 2008...

Unable to update SQL Server database through code - I am writing a vb.net code to update SQL SERVER database. When I run the query through the SSMS, I...

restore log backups to another server - Hi every body I have a database in server A and i have a friday job that takes a full and...

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

Backup script - Hi, I'm using ola backup script and want to have all backups in the given backup directory NOT to create Instancename\databaseName\BackupType...

Database change policy and procedure - Presently we have no defined process or policy for changes to SQL Server instances and databases in our environment. I'm...

UNIQUEIDENTIFIER vs BIGINT - Hi, I read somewhere that when ever there's a join between two tables, for example orderlines and products being joined by...

split column - Hi, I'm trying to split a single column into a number of columns, with the split being the delimiter of...

Activity monitor loads slow - Hi, I have a very general question. Does anyone know why the activity monitor in SSMS 2008 and/or 2008R2 in a...

Failed to restore a SQL2000 backup to a SQL2008R2 instance - Hello gents The SQL2000 backup is fresh without corruption but when I tried to restore it to a SQL2008R2 instance, the...

Replication error: "field size too large" - Hi, I've got two databases on the same server and replicate some tables from one database to another. The replication is configured...

SQL Server 2008 : T-SQL (SS2K8)

Nested Triggers - Hi, Is there any easy way of determining nested triggers?! For example, if I update a table that has an update trigger...

creating procudre for dynamic table - Hi friends, create tab T1(name varchar(22),age numeric(22)) create tab T2(name varchar(22),age numeric(22)) insert into t1 values('ram',22) insert into t1 values('am',26) insert into t1...

Need help to eliminate values from resultant record using view - I want to eliminate ('withdrawn','Discontinued','Matured','Withdrawn from Public') from the resultant records but not succeed QUERY : [code="sql"]select * from Vu_CurRating where ratingid not...

Using IF condition on Cursor - Hello I have build this cursor that work correctly, but i need to changing then to out an IF condition if...

select records between a date range - Hi! I am trying to select records between July 1st 2011 and August 14, 2011. I am not returning any...

Automating security within triggers - Hello, I have already a trigger that sends us a mail when a new database is created (some of our users...

I need to implement trigger on INSERTION and insert the same record to another table but avalue is showing null :( - I need to implement trigger on INSERTION and insert the same record to another table but a column value is...

SQL Server 2008 : SQL Server Newbies

osql batch taking long longgg time - hi, trying to understand whats going on and was hoping someone could help. I have 10 scripts to run. Doing them...

Roles Schemas and 2000 - Hi Im trying to understand a Database design for a 3rd party app. So apart from creating non-clusterd unique indexes...

Rebuliding Indexes - Hi All, I am rebuilding/reorganizing indexes and I am trying to locate the activity via server logs However I can't find it. My...

SQL 2005 Import\Export Wizard package save location - When i use the import\Export to move data and select the option to save as an SSIS package it says...

SQL query statement for copying data from different rows to different rows - I have a table (dbo.ACCOUNT_INF) with fields company_code (secondary key), accounts_code (unique) and other columns. I like to copy rows...

Allways wondering why so many sql server programs are installed in my PC - Hello, I'm new to sql server and allways wondering why so many sql server programs are installed in my PC. As you...

Help reading Deadlock graph - I have been going through the forums on how to read this graph. I was asked where does a certain...

Need to delete last row in Excel sheet using SSIS script task - Hi to all. I need to delete last row in excel in sheet using ssis task. can any help me regarding...

Roles and Responsibilities of Different Teams When Supporting SQL Server - Hi, I work for an organisation where we have approximately 75 IT staff. We have a development team, an application service...

SQL Server 2008 : SQL Server 2008 High Availability

Configure DB Mirroring on 2 SQL Servers on different SPs - I have 1 server with SQL Server 2008 R2 SP1 and another SQL Server on SQL 2008 R2 SP2. I have...

SQL Agent Access in Sql Server 2008 - I want to give access of a [u]SQL Agent[/u] to a user.. how do i do this:w00t:

Running SQL 2005 & 2008 in a 2 node cluster - Is it possible to have a setup running SQL 2005 & 2008 in a 2 node cluster? Thanks Pete

Configure Distribution not showing up in Replication - Hello, While right clicking on replication in SSMS, "configure distribution" option is not showing up in SQL Server 2008 r2 version....

Log Shipping Scenario- DB at Secondary Server in Restoring Mode- DB's Log file containing drive got full-What to do? - Log Shipping is set from Server A to Server B for database X SQL Server 2008 Std Edn 64 bit on...

setting up Clustering - SQL Server 2008 R2 - I have a couple of questions about installing SQL Server 2008 R2 Standard in a 2 node active\passive cluster First, a...

SQL Server 2008 : SQL Server 2008 Administration

SQL historic restore durations - Morning everyone. Can anyone point me in the way of a script to detail historic restore durations ? need to know how...

Mirroring Setup - I want to implement database mirroring, but can't afford downtime for a full backup, copy to mirror and restore.... I need...

Backup database - We have a SQL server that hosts a vendor product. The vendor setup a backup for the database. I see in...

Could not locate file in sys.database_files - Hi All I had a weird situation this morning On one of my test systems, I tried running the following command to...

import - export of dts package - Hi I am trying to import data from oracle to sql 2000 using dts package but unable to get all...

Career : Certification

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

SQLServerCentral.com : Anything that is NOT about SQL!

Which help desk software provides first-class support service - Easy to set up and effortless to maintain.The help desk solution is built to allow your support, sales, pre-sales, billing,...

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

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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

Export to PDF show error"An error occured during local report processing.Value was either too large or to small for an int32" - I have designed a drill down report in tabular form, in one cell i have used subreport. The report is...

RS Scripter - Losing credentials when deploying - So I've started setting up RS Scripter to do my team's SSRS deployments from Dev -> QA -> Production. However,...

Lookup - Hi All, Can someone help me with this problem? I have a table with product Id and another table with product...

Data Warehousing : Integration Services

Handling datatype changes after deployment SSIS - Hi All, I have created a ssis project and deployed it to the server. And after the deployment has been done,...

Execution Of EXE - Hi every one , any one help me on below requirement I had two servers server 1 -------SSIS Package is present server 2----- EXe...

SSIS- Need File Name which i am loading in Variable - I Need File Name which i am loading via Excel. Any Idea

Execution Of EXE in Remote server - Hi every one , any one help me on below requirement I had two servers server 1 -------SSIS Package is present server...

Avoid multiple processing of SSIS package (Running package cannot be re-executed) - Hi, I am executing an SSIS package with DTEXEC. How can I prevent that SSIS executes the same package more than...

Dynamic file name for FTP task. - Hey I am also working on same task like this In SSIS ftp task... Here I created a variable for getting...