SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

When DBCC INPUTBUFFER disappoints

noreply@blogger.com (Hemantgiri S. Goswami) — Mon, 30 Jan 2012 22:44:00 PST

Today we have an alert from one of the client server about blocking, I have immediately start looking at it using below statement

 
SELECT * FROM 
MASTER..SYSPROCESSES
WHERE BLOCKED != 0

Yes, I’ve found the culprit SPID that is occupying more resources, using DBCC INPUTBUFFER(SPID). so, instead of seeing what exactly this SPID is doing I have received SP_EXECUTESQL, surprised!!! No, it’s bound to happen when SPID is running dynamic TSQL (using SP_EXECUTESQL) and/or cursor. But, yes, at the same time I would like to know what it is running behind the scene. There are two options that came to my mind

Using Profiler

using function – ::fn_get_sql(@SQLHandle)

I preferred to use 2nd option as this is one time effort (at least as of now), and, it would be very quick. So, here is what I have used

 

-- Variable that will store the SQLHandle
DECLARE @SQLHandle BINARY(20)

-- Variable that will pass on the culprit SPID
DECLARE @SPID INT

-- value for culprit SPID
SET @SPID = 52

-- this will give you the SQLHandle for the culprit SPID
SELECT @SQLHandle = SQL_HANDLE 
FROM MASTER..SYSPROCESSES 
WHERE SPID = @SPID  

-- this statement will give you the SQL Statement for culprit SPID
SELECT [TEXT] FROM ::FN_GET_SQL(@SQLHandle)

You must be thinking why I have used this function instead sys.dm_exec_sql_text? Any guess??
Yes, you are right, customer is still using SQL server 2000 {Winking smile}
BTW, if you happened to come across something relating but on SQL Server 2005 or SQL Server 2008 and greater I have a reference script for you

Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron_Bertrand.

--Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Rebuild System Databases

noreply@blogger.com (Hemantgiri S. Goswami) — Sun, 29 Jan 2012 21:00:00 PST

Yesterday night one of FB Friend have ping me, he has issue with his local SQL Server instance. Our conversation goes like:

FB Friend: Hi

I : Hi

FB Friend: I need your help

FB Friend : My SQL Server is not starting...

I : What is an error you are getting?

FB Friend : Just nothing

I : Ok, check the SQL Server Error Log and EventViewer

I : you can find errologs at C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\Log

I : open it with notepad ....

FB Friend: Ok

FB Friend: Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

I : Wow, seems like your master database is corrupt, you will need to run repair or you may want to rebuild master database.

I : Is this a new installation ?

FB Friend : Yes, this is new installation, and, I don't want to re-run the installation again, what is my option ?

I : you will need to rebuild master or restore it

FB Friend: How do I do it?

I: Here are your steps

Rebuild Master:

Step 1: Insert your setup media CD or folder where you have dumped SQL Server Installation

Step 2: Open command prompt

Step 3: Go to location (wherever you have setup, Step 1)

Step 4: setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=MyAdmin /SAPWD=somePwd

Step 5: this will pop up new command window and then disappears

Step 6: you will have to refer logs so as to see whether it was successful completed or exit with error

This is it, you system database has been rebuild successfully!!

Restoring Master Database - An alternative method:

Step 1: Take backup of master database, in your case, I assume, you already have your master database backed up.

Step 2: Stop SQL Server Services * you will need Administrator permission for this operation

Step 3: Start SQL Server using -C and -M parameter; this will start SQL Server in single user mode which is required to restore Master database

Step 4: Restore database using SQLCMD

This is it, you have successfully restore your master database!!!

Is it this simple? No, wait, there are few things we need to be careful with....

The above example have save you because below conditions are true:

You have back up of your master database, or
This server is fresh installation or
there are no user databases or
this server is not production box

If the above conditions were not true, you will have to perform below steps:

Restore / Rebuild system databases
Recreate all the Login that you have on your crashed server - backing up master db helps you here
you will have to recreate all the jobs and SSIS/DTS Packages that you have - backing up MSDB database will save you
similarly distribution database backup will help you if you have replication configured

Conclusion : I wish that you have schedule maintenance plan / job to take full backup of your system databases i.e. Master, MSDB, and Distribution(if you have configured replication).

Disclaimer : The example used here is for illustration purpose only.

Revamping SUG 2nd Meeting

noreply@blogger.com (Hemantgiri S. Goswami) — Mon, 23 Jan 2012 04:02:00 PST

Friends,

Last Saturday, 21st we have 2nd meeting of SUG members and this time we made progress – I, Vinay and Matang was present in meeting .We have discussed following :

We have decided that portal management will be done by I, Vinay and Matang initially, here are the module that we’ll be adding content on:

Forums and Team section –> Hemantgiri S Goswami
Article Section –> Matang Panchal
Blog Section –> Vinay Pugalia

We’ll design and use common PPT theme during all our presentations
We’ll organize quize every quarter
From next meet, 1 member will make presentation of 15 minutes; starting with myself
We will try to accommodate as many as practical / self demos in initial (and, so on) events
We would also create PPT / Demo repository that we have used during event
Also, there will be a download section where we’ll share scripts/codes to download
We will open a bank account to manage the fund we’ll receive from Sponsors

See you in next meeting will be on 11th February 2012
Venue : 406, Empire State Building, Nr. Udhana Darwaja
Time : 7 PM

Image source: http://pmtips.net/wp-content/uploads/2010/03/meaningful-meetings.jpg

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Roles and Responsibilty of SQL Server DBA

noreply@blogger.com (Hemantgiri S. Goswami) — Sun, 01 Jan 2012 18:30:00 PST

What is a role of a DBA in an organization?
What are the daily activities of a DBA?
What shall I check on regular basis?
What all processes shall I automate ?
What are the precautions, I have to take additionally?

These are the common question being asked in-common on may forums, here is what my listing is

• Design and implement Disaster Recovery

• Design, development, upgrade and Migration of Database Server.

• Providing support and handling most critical situations in vast variety of database systems.

• Database Optimization / Query Performance Tuning.

• Design and implementation Automated Database & Application fail over server setup.

• Capacity Planning, Change Management and Admin. Documentation.

• Review of existing design and specifications of the system.

• Design and documentation of operational specifications of the system.

• Monitoring of the development process, in order to confirm optimum performance of the system.

• Preparation of standards and follow up rules for the back – end system, to ensure a fully secured and a robust system.

• Design and implementation of the logical & physical structure of the database.

• Monitoring of performance of database servers and providing tuning measures.

• Performing database / application wide query tuning operations.

Try automate every process that needs to be performed on daily basis and/or requires manual intervention.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com)

noreply@blogger.com (Hemantgiri S. Goswami) — Sat, 31 Dec 2011 19:19:00 PST

Wishing you all a very Happy and Prosperous new year, may success comes to your doorstep

Image source : Top CNN News

What’s new in SQL Server RC0 setup

noreply@blogger.com (Hemantgiri S. Goswami) — Wed, 28 Dec 2011 20:18:00 PST

Datacentre Edition is no longer available as a MS SQL Server 2012 RC0 candidate, Read More
MS SQL Server 2012 RC0 includes new edition – SQL Server Business Intelligence, Read More
Service Pack 1 is the minimum requirement for Windows 7 and Windows Server 2008 R2 operating systems, Read more
Data Quality Services can be installed using SQL Server 2012 RC0 Setup, Read more
Product update is the new feature available with MS SQL Server 2012 RC0, this will integrate latest update with main product including MS Update, WSUS, local folder or UNC. This feature is an extension to Slipstream Functionality which was available in SQL Server 2008 PCU1. We can use command prompt or configuration file to override default values to find updates by specifying values for UpdateSource parameter, Read more
We can now install MS SQL Server on Windows Server 2008 R2 Core SP1, not all features are supported though, Read more
SQL Server Data Tools (formerly, BIDS) is now part of setup, with this we can carry out all database design work, and can build solutions for SSAS, SSRS and SSIS. Read more
Now, Support for SQL Server multi-subnet clustering included, Read more
Databases (systems and users) can now be on UNC path, we may need to make sure that proper NTFS and File Share permissions are assigned, Read more
Local disk can be used for Tempdb in cluster, Read more
Built-in Administrator and Local system account will not be part of sysadmin role
Itanium editions are no longer supported

Revamping Surat User Group

noreply@blogger.com (Hemantgiri S. Goswami) — Mon, 26 Dec 2011 22:31:00 PST

Dear Friends, we had a user group meeting last week – I and Vinay Pugalia was here. We have discussed many things to revamp and re-launch Surat User Group. Here are the minutes of meeting:

1) Regular Meetings -> Monthly, Proposed schedule :Saturday evening, 1900 to 2000

2) Regular Events -> 1 every 2 months, 1 workshop every quarter

3) Decide and form SUG Website, with Blog,Forums section, Facebook page and twitter handle

4) Press Notes after every event

5) Help from CSI Surat -> Have to meet, discuss and encourage Local IT professional to join and PARTICIPATE

6) Seminars/ Workshop :-free, TBS on weekends (Sat-Sun) to have discussion with Professional / Students

7) Different topics and technology, every time has 1 continue and 1 different topic

8) Initially we'll take session and then, we'll encourage others to take sessions and like that

9) Form Core Team & Backup Team

10) Role definition and Role assignment

11) Encourage new speaker to start with very basic topics to build their confidence

12) More Online activities etc.

13) Ask speakers to provide technology they can confidently speak

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Maintenance Plan mystery

noreply@blogger.com (Hemantgiri S. Goswami) — Wed, 14 Dec 2011 04:22:00 PST

Last week I had interesting (or I can say weird ? ) case of Maintenance Plan. One of the server that we monitor has reported job failure – this was a part of maintenance plan. Looking at the job history and log file I came to know that this job was failing because of some database which wasn’t available – this database was dropped few days back, and, the maintenance plan has it’s reference stored somewhere. I have search around system tables etc. but nothing unusual or nothing helpful was available that indicates the source of this database.
Finally, I have edited the maintenance plan which resolved this case, here is what I did:

Alter the maintenance plan "some maintenance plan"

note down the databases name

altered it and selected all the system databases

saved the maintenance plan changes and exit

re-open the maintenance plan

re-selected those databases (from step 2)

saved the maintenance plan changes and exit

re-invoked the job

and, the job completed successfully

And, the mystery persist, can you guys suggest me if I miss something to refer/research ?

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Using NOLOCK hint

noreply@blogger.com (Hemantgiri S. Goswami) — Thu, 01 Dec 2011 23:28:00 PST

Use NOLOCK hint to avoid block - this is what I have often heard/see in many forums I participate, during local user group events and meeting. I have always advised that its not that good idea to use hints, as it may cause data corruption and blocking. And, anyways, there are many things that you can do to avoid blocking, like:

use sp for everything (almost)
try to avoid using cursor
transaction shouldn't be too big etc
and, use READPAST hint , I will still say, use this only when you don't have choice

But these all comes from the experience from the field and haven't anything concrete to quote as reference point until last night, I was googling something and this blog article from Dave, on MSDN Blog showed up. Now, I can quote SQL Server NOLOCK Hint and Other poor ideas as reference to my peers,and friends at local user group, and I am referencing it here for you to read and make a note.

I hope this helps.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/)

Download SQL Server 2008 SP3 CU2

noreply@blogger.com (Hemantgiri S. Goswami) — Tue, 22 Nov 2011 23:56:00 PST

MS has recently released SQL Server 2008 Service Pack 3 Cumulative Update 2 Released‏ which has fixes reported after SQL Server 2008 SP 3, this build version is 10.00.5768.00.
Below are the bugs that have been fixed in this CU:

FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database
FIX: CLR stored procedure returns NULL after it is recompiled in SQL Server 2008 R2
FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008

There are few more things we have to keep in consideration while applying this CU:
Pre-install:
1) You should be running on SQL Server SP3, if you haven't installed SP3 for SQL Server here is the link from where you can obtain SP3, Link => http://support.microsoft.com/kb/968382
Post-install:
* It is advisable to reboot the system once CU is applied
Please make sure you test the CU in Dev/QA/UAT environment before you apply it in the PROD environment.
Here is the link from where you can download the SQL Server 2008 SP3 CU2, link => http://support.microsoft.com/kb/2633143/en-us

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Download SQL Server 2012 Developer Training Kit

noreply@blogger.com (Hemantgiri S. Goswami) — Mon, 07 Nov 2011 23:13:00 PST

Microsoft has released SQL Server 2010 Developer Training Kit which includes :

Labs
Demos
and, Presentations

This kit will greatly help you learning Developer and BI Solutions.

One will require to have Windows 7 or Windows Server 2008 R2 to install this kit. Once you have download the kit, launch the installer and it will ask you which lab, demos and presentations you want to include and install them based on your choice.

Sounds interesting? Grab it from the link

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Resolving 701 There is insufficient system memory to run this query

noreply@blogger.com (Hemantgiri S. Goswami) — Mon, 07 Nov 2011 00:47:00 PST

In recent past while working on an assignment I have encounter an error 701 There is insufficient system memory to run this query . I had a quick look at the server and noticed that server is not configured proper for max memory, and have suggested client to make changes to the max memory settings for the server. While I've suggested changes I have quote two articles, thought they would be a help to you as well to understand better on how SQL Server Memory managed. An article by SQL Server MVP Jonathan Kehayias will help you understand the memory management very well.

1) http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

2) http://support.microsoft.com/kb/912439

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com)

DATABASE_OBJECT_CHANGE_GROUP do not audit SP or other object

noreply@blogger.com (Hemantgiri S. Goswami) — Sat, 29 Oct 2011 02:11:00 PDT

Deepak Kumar (friend of mine and founder of http://www.sqlknowledge.com ) were chatting yesterday. We were discussing about audit feature in SQL 2008, Deepak has enabled this feature for one of his client since month. And when he was looking at the log he found that there were entries but they are related to Tables only, and not other objects like SP(s).

We were discussing and google about the same and found an entry in connect where in it was answered.

If we need the other objects to be audited we have to add SCHEMA_OBJECT_CHANGE_GROUP to the audit specification. Here is an excerpt from the connect:

Thanks for your feedback. The behaviour you're seeing is by-design. In order to audit CREATE/DROP of an SP, you need to add the SCHEMA_OBJECT_CHANGE_GROUP to your audit specification. The DATABASE_OBJECT_CHANGE_GROUP is actually auditing the ALTER permission check on the SCHEMA as part of the CREATE statement.

You can find the complete entry and more information at connect article

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Refresh QA Database with Manual Scripts

noreply@blogger.com (Hemantgiri S. Goswami) — Thu, 27 Oct 2011 21:33:00 PDT

Couple of week back Megha Sharma send me an email with a document attached, this document is all about how to refresh QA database with manual script. Here is the preview of the document, and the reason why we should follow the method in her own words:

QA environment frequently needs Database refresh and hence space on its disks. QA Database is refreshed from Production followed by a data purge, which leaves a lot of free space in the database. In order to release the free space, we follow database shrink command, a lengthy & single thread I / O operation, also cause high data fragmentation. To avoid this resource & time consuming activity, we do a Refresh Database with manual scripts, in which, we create a new database, transfer tables, data & other objects via scripts. The Generate Scripts option of a database generates script for the complete database and transfers data (using Insert into command, highly logged), to avoid this we do a Select * into, to transfer table definition & data (minimal logged & fast, being a bulk operation) & then generate Table Objects (Keys, Constraints, Triggers, Indexes) via manual scripts & other database objects (Views, Stored Procedures, Functions, Users, Roles, Schemas) via generate scripts.

Sounds interesting ? Want to download the complete document ? Download Link

Please post back your review in the comment section here.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

workaround for orphaned users

noreply@blogger.com (Hemantgiri S. Goswami) — Tue, 27 Sep 2011 06:34:00 PDT

The server principal is not able to access the database under the current security context, is the error message appears for those users who was able to access the database / application earlier especially after refreshing database from production to dev, test or uat enviornment when the correspondent login is dropped.
The reason is that, when we restore the database the the SID for the user mismatches, and it became orphaned. The workaround for this issue is very simple, you will have execute the below code

 -- This part will report if there are orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='Report'    
GO    
-- This part will fix orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='update_one'    
    ,@UserNamePattern='youruser'    
    ,@LoginName='yourlogin'    
GO

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

SQL 2008 R2 Administration Cook Book

noreply@blogger.com (Hemantgiri S. Goswami) — Mon, 26 Sep 2011 01:21:00 PDT

Couple of week back I have received a special gift, from the person whom I admire as a elder bro and mentor – Satya SkJ. Recently he has written a cook book on SQL Server 2008 R2 Administration, forward written by MVP Brad McGehee, and reviewed by Vinod Kumar M – the most admirable person in SQL Server Community in INDIA. I am very excited to read this book thoroughly, the thing I like most in every chapter is “How it works” section. Very soon I will post detailed review for this book.

You can find the detailed information about each chapter at the below location :
Chapter 1: Getting Started with SQL Server 2008 R2
Chapter 2: Administrating the Core Database Engine
Chapter 3: Managing the Core Database Engine
Chapter 4: Administering Core Business Intelligence Services
Chapter 5: Managing Core SQL Server 2008 R2 Technologies
Chapter 6: Improving Availability and enhancing Programmability
Chapter 7: Implementing New Manageability Features and Practices
Chapter 8: Maintenance and Monitoring
Chapter 9: Troubleshooting
Chapter 10: Learning the Tricks of the Trade
Appendix: More DBA Manageability Best Practices

Sounds interesting ? You can purchase the one for you at Link => https://www.packtpub.com/microsoft-sql-server-2008-r2-administration-cookbook/book#in_detail

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com)

CU 16 for SQL Server 2008 SP1

noreply@blogger.com (Hemantgiri S. Goswami) — Fri, 23 Sep 2011 07:51:00 PDT

Microsoft has recently released a Cumulative Update (CU) # 16 for SQL Server 2008 SP1 which contains hotfixes which were resolved after SQL Server 2008 SP1; once you will install / apply this CU the server version would be 10.00.2850.00

As always it is recommended that you test this CU in the development/test/QA environment first before you roll it out in production environment. Once you apply this CU, you might have to restart you server.

Download => Cumulative Update # 16

Find KB article which explains what is a different model for Incremental Servicing Model (ISM) like Critical On-Demand known as COD, On Demand known as OD, Cumulative Update known as CU and General Distribution Release known as GDR. Here is the reference link => Incremental Servicing Model

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

SQL Server DBA Quiz on BeyondRelational

noreply@blogger.com (Hemantgiri S. Goswami) — Thu, 22 Sep 2011 00:13:00 PDT

Friend of my mine and fellow MVP Jecob Sebastian is running SQL Server DBA Quiz 2011 on his community web site – BeyondRelational. He asked me to submit question as a Quiz Master, so, here is my question for you all :

You have an SQL Server Integration Service (SSIS) installed on some machine and you have client tool installed on your local desktop, when you tried to connect to the machine you have SSIS service running on from your local desktop you will get an error “RPC server is unavailable”

You will have to let us know what the root cause of this issue and how to resolved it.

Know the answer already?

Click Here to Submit an Answer

-- Hemantgiri S. Goswami (http://www.sql-server –citation.com )

SQL Server Wait Types in SQL 2008

noreply@blogger.com (Hemantgiri S. Goswami) — Wed, 21 Sep 2011 07:34:00 PDT

While working on an assignment of troubleshooting performance issue few month back I have come across a situation where I have to provide good information about the different waittypes and what they do, while researching a good documentation on wait types I have found the most authentic information on PSS Engineer’s blog where Bob Ward has put them nicely. This blog was published in 2009 but its good to keep handy as reference.

Most of the time wait type says us where the bottleneck are, if you wait types like IO_Completion, PAGE IO LATCH, this is indication for disk IO subsystem has some bottleneck. We have to use perfmon counter to verify this and then work around on resolving this issues.

Let me quote some links for the further reading:

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx

http://support.microsoft.com/kb/822101

http://blogs.msdn.com/b/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Overcome update conflict in P2P replication

noreply@blogger.com (Hemantgiri S. Goswami) — Sun, 18 Sep 2011 04:55:00 PDT

When we have Peer to Peer (P2P) replication in place on SQL Server 2008, probably we would see a conflict

1) conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming)

2) conflict of type 'Update-Delete’ was detected at peer 2 between peer 1 (incoming)

Microsoft has confirmed that this is known issue with P2P replication, and Microsoft has released a fix for this with Cumulative hot fix for SQL Server 2008 SP1. I am penning down some links for you as a point of reference for troubleshooting P2P conflict.

Paul Libson has wrote an excellent article on how to handle P2P replication

http://support.microsoft.com/kb/973223

http://technet.microsoft.com/en-us/library/bb934199.aspx

http://technet.microsoft.com/en-us/library/ms147366.aspx

http://technet.microsoft.com/en-us/library/ms151865.aspx

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Script to Configuring Auto Growth to Fix MB

noreply@blogger.com (Hemantgiri S. Goswami) — Fri, 16 Sep 2011 06:08:00 PDT

Few month ago I have written a post about what is Auto Growth and what is the best practice for Auto Growth. In this post I have discussed about why should we have Auto Growth for databases set in a fixed MB instead in percentage and how it will impact us.

In this post, I am going to give you a script that will help you find the database files which have Auto Growth option set in percentage and alter them all to fixed MB in one go.

/* 
-- Created By: Hemantgiri S. Goswami
-- Date: 29th April 2011
-- Version: 1.0

*/

-- Creating the table to capture temporary data
IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')
	DROP TABLE ConfigAutoGrowth
GO	
CREATE TABLE DBO.ConfigAutoGrowth
(
iDBID		INT,
sDBName		SYSNAME,
vFileName	VARCHAR(max),
vGrowthOption	VARCHAR(12)
)
PRINT 'Table ConfigAutoGrowth Created'
GO
-- Inserting data into staging table
INSERT INTO DBO.ConfigAutoGrowth
SELECT 
	SD.database_id, 
	SD.name,
	SF.name,
	--sf.fileid, 
	--SUSER_NAME(owner_sid),
	--recovery_model_desc,
	CASE SF.status & 0x100000
	WHEN 1048576 THEN 'Percentage'
	WHEN 0 THEN 'MB'
	END AS 'GROWTH Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

-- Dynamically alters the file to set auto growth option to fixed mb 
DECLARE @name VARCHAR ( max ) -- Database Name
DECLARE @dbid INT -- DBID
DECLARE @vFileName VARCHAR ( max ) -- Logical file name
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql


DECLARE db_cursor CURSOR FOR
SELECT 
idbid,sdbname,vfilename,vgrowthoption
FROM configautogrowth
WHERE sdbname NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' ) 
AND vGrowthOption  = 'Percentage'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)
	SET @Query  = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 500MB)'
	EXECUTE(@Query)

FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption  
END
CLOSE db_cursor -- Closing the curson
DEALLOCATE db_cursor  -- deallocating the cursor

GO
-- Querying system views to see if the changes are applied
SELECT 
SD.database_id, 
SD.name,
SF.name,
--sf.fileid, 
--SUSER_NAME(owner_sid),
--recovery_model_desc,
CASE SF.STATUS 
& 0x100000
WHEN 1048576 THEN 
'Percentage'
WHEN 0 THEN 'MB'
END AS 'Growth_Option'
FROM SYS.SYSALTFILES SF
JOIN 
SYS.DATABASES SD
ON 
SD.database_id = SF.dbid
GO

--Dropping the staging table
DROP TABLE ConfigAutoGrowth 
GO

Hope this helps!!

-- Hemantgiri S. Goswami

Troubleshooting Oracle Link Server Issue

noreply@blogger.com (Hemantgiri S. Goswami) — Thu, 15 Sep 2011 06:13:00 PDT

Most of the time, due to different business requirements we do have to work on various RDBMS systems, Oracle and MS SQL Server are the widely used and popular RDBMS. Sometimes we need to import/export data from/to SQL Server and for that we’ve to use Link Server feature of MS SQL Server.

In one of my recent project, a critical application has a job that pulls in the data from an Oracle database, we have a DTS Package and some job scheduled for this task. since couple of days we’ve observed below error message in the job history and while running ad-hoc queries. I am penning down the error message and the solution (rather, temporary workaround) we have found out :

1) The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

==> In this case, rebooting MS DTS service helped us.

2) The maximum number of active transactions that the MS DTC log file can accommodate has been exceeded. You must increase the size of the MS DTC log file if you wish to initiate more concurrent transactions.

==> In this case, increasing Log file size helped us.

3)Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OraLnkSvr" reported an error. The provider ran out of memory.

4) Server: Msg 7330, Level 16, State 1, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OraLnkSvr".
OLE DB provider "MSDAORA" for linked server "OraLnkSvr" returned message "Out of memory.".

==> In case 3 & 4, rebooting SQL Server service helped us.

Hope this helps.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Checking DB Mirroring Status

noreply@blogger.com (Hemantgiri S. Goswami) — Wed, 14 Sep 2011 00:16:00 PDT

Often I see a question in community on how to quickly check the status of the database mirroring, sometime in busy environment and busy server launching database mirroring keep us waiting for a while, so is there a way we can check database mirroring status ?

Yes, of course we do have; execute below statement and you will have a status of the database mirroring for all the database you have configured mirroring on :

SELECT DB_NAME(database_id),   
mirroring_role_desc,    
mirroring_state_desc    
FROM sys.database_mirroring    
WHERE mirroring_guid IS NOT NULL;

Let me know if you are looking some specific code, and I will try to post it here!!

- Hemantgiri S. Goswami (http://www.sql-server-citation)

Accessing SSIS on a Remote Server

noreply@blogger.com (Hemantgiri S. Goswami) — Fri, 26 Aug 2011 22:20:00 PDT

In one of my recent assignment I was asked to help developers who were not able to work on SSIS on a remote computer, whenever they made an attempt to connect to SSIS on Remote server they were getting “Access is Denied” error. Researching bit on this I come across an article on MSDN which help me resolve this issue – this issue is all about a DCOM Permission.
Here are the steps which helped me resolve this issue by granting necessary permission and adding developer user[s] or group for DCOM:
1) Open Component Services MMC Snap-in by Open Control Panel –> Administrative Tools –> Component Services
2) Expand Component Services node –> Expand Computers –> Expand My Computer and click DCOM Config
3) Select DCOM Config and it will display the sub list in the right pane, select MsDTSServer100

4) Right click on MsDTSServer100 and select Security tab

5) As shown in the above snapshot assign proper permission to user[s] or user group by clicking on Edit button under (you have to select Customize before you click Edit button)

a) Launch and Activation Permissions
     a.1) Local Launch
    a.2) Remote Launch
    a.3) Local Activation
    a.4) Remote Activation
b) Access Permissions
    b.1) Local Access
   b.2) Remote Access
c) Configuration Permissions
   c.1) Full Control
   c.2) Read
    c.3) Special Permission

6) Assign the appropriate permission on “My Computer” under Component Services
7) In MSDB database grant db_dtsoperator permission to user[s] or user group
8) Restart the Integration Services services, and you are done.
source : http://msdn.microsoft.com/en-us/library/aa337083.aspx

Winner of the contest

noreply@blogger.com (Hemantgiri S. Goswami) — Thu, 05 May 2011 05:04:00 PDT

Dear Readers,

I am very please to announce winner of the contest about describing best HA & DR solution. The contest was successful, there are about 5 entries I have get from various sources like linkedin and email, I would like to thank all the participant and readers. The winner is Dave Thomas.

My apologies, I suppose to declare winner on 25th April but could not able to make it.