Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

Archives: August 2012

Drive Size - PowerShell Script

The below script is used to find the disk size for a set of servers,

#
# Script to Find List of drives and Size of Drive in given set of server
# Created by - Vinoth N Manoharan
# Version 1.1
# Date - 15/09/2011
# Script Help :-

Read more

1 comments, 912 reads

Posted in SQLTechnet on 14 August 2012

Database and Disk Size - PowerShell Script

Every time we do a capacity analysis we need to analyse the database size (free and used) and the disk size in which the database is associated frequently,We can use Powershell to append both TSQL and WMI output to accomplish this task,


# Script to find Database File(Data and Log)Space…

Read more

4 comments, 1,591 reads

Posted in SQLTechnet on 14 August 2012

Troubleshooting Performance - Top CPU Queries


Today I came accross white paper "Troubleshooting Performance Problems in SQL Server" by Sunil Agarwal, Boris Baryshnikov, Tom Davidson, Keith Elmore, Denzil Ribeiro, Juergen Thomas 
in the MSDN site after reading through i thought i can mention couple of TSQL queries in the whitepaper for CPU issues which can be useful,
1. 

Read more

1 comments, 573 reads

Posted in SQLTechnet on 14 August 2012

Find AD user detail - PowerShell Script

I developed the below script to find the details of a AD user and the AD groups he is associated with, Script 1 is user search by AD user ID and Script 2 is search by AD user name(Last or First Name)

Script 1:- 

#

# Script to Find AD…

Read more

1 comments, 2,154 reads

Posted in SQLTechnet on 13 August 2012

Find Members of AD Group - PowerShell Script

This is one of the powershell script I have been using quite regularly from the day I developed. Most of SQL Server logins have AD groups as logins and for any security issues we would need to back track the user and group he is associated with, this script will recursively loop…

Read more

2 comments, 932 reads

Posted in SQLTechnet on 13 August 2012

Service pack Native Client, SQL XML4 and VSSWriter failed


One of my client accidentally executed a 32 bit service pack on a 64 bit SQL Server, the service Pack failed with an error to execute 64 bit version of the service pack.
We then tried to execute 64 bit version of service pack which completed successfully for database services but failed…

Read more

1 comments, 632 reads

Posted in SQLTechnet on 9 August 2012

Forcing network protocol to connect SQL server


Whenever there is a network issue or a SQL server browser issue we might need to connect SQL server in different network protocols to troubleshoot. We can change the default protocol in configuration manager but it will make the change globally for all connection connecting to the SQL instance, instead we…

Read more

1 comments, 667 reads

Posted in SQLTechnet on 9 August 2012

SSIS: Transfer SQL Server Object using SMO


we often face a situation to move SQL server object beween servers or databases, when you have constant table list we can always use "Transfer SQL Server Objects Task" with the SSIS, One of my customer had a requirement of scheduling migration process frequently and would be supplying the table…

Read more

15 comments, 2,041 reads

Posted in SQLTechnet on 8 August 2012

Rollback TRUNCATE TABLE statement

Whenever I ask a SQL candidate in an interview the difference between DELETE and TRUNCATE TABLE, the first answer I get is

"DELETE is a logged operation and TRUNCATE is a NON-Logged Operation"

Is TRUNCATE really a Non-Logged Operation?

BOL refers to TRUNCATE operations as “minimally logged” operations,
So what…

Read more

1 comments, 1,733 reads

Posted in SQLTechnet on 6 August 2012

Blocking in SQL Server by SPID -2 (Orphan distributed transactions)


Blocking in in SQL Server by SPID -2 happens due to Orphan DTC transaction, for instance whenever a data source connected in MSDTC is rebooted abruptly when a transaction is active, MSDTC does not recognize that one of its destination has been rebooted and keeps the transactive forever wiating for…

Read more

4 comments, 1,871 reads

Posted in SQLTechnet on 6 August 2012

SQL Server - Oracle Architectural comparison

This is one of the session I took for SQL DBA to understand the basics of Oracle, Its a powerpoint presentation but all the images are self explanatory.










Read more

1 comments, 783 reads

Posted in SQLTechnet on 6 August 2012

Auto Shrink of all T-Log File on a SQL Instance - TSQL


This Script will shrink all the T-Log files of SQL instance if there is no active tracsaction on the database. The Script will automatically ignore the DB if there are some active transactions.
Scripted for SIMPLE RECOVERY databases, comment BACKUP LOG line for FULL RECOVERY databases. The Log file will…

Read more

1 comments, 922 reads

Posted in SQLTechnet on 2 August 2012

Replace NOT IN with JOIN

Example of how to replace NOT IN clause with JOIN


/*NOT IN query*/

USE AdventureWorks;
GO
SELECT ProductID
FROM Production.Product
WHERE ProductID
NOT IN (
SELECT ProductID
FROM Production.WorkOrder);


/*JOIN Query*/:-

SELECT p.ProductID
FROM Production.Product p
LEFT JOIN Production

Read more

1 comments, 245 reads

Posted in SQLTechnet on 2 August 2012

SQL security scripting - TSQL

SQL DBA often needs to script SQL Logins,Server roles,database users and Roles. Although its easy to script login,roles and users with SSMS, associating login to server Roles and database users to db roles is not straight forward using Script Wizard. We can easily overcome this using below TSQL scripts.


/*ASSIGN SERVER…

Read more

1 comments, 543 reads

Posted in SQLTechnet on 2 August 2012

SQL database Incremental Shrink TSQL


When we shrink a SQL database through GUI we usually find the database never releases all the free space, It is always a good practice particularly in Production OLTP systems to shrink the database in small chunks so the data databse pages can be re-arranged and can free all unused…

Read more

2 comments, 1,244 reads

Posted in SQLTechnet on 2 August 2012

Set Up SQL Server Mirroring T-SQL Script

T-SQL Script to create a SQL Mirroring:-

The first thing you need to do when setting up Database Mirroring is perform a full backup followed by a transaction log backup on the principal server.  You then must restore these to the mirror server using the WITH NORECOVERY option of the…

Read more

1 comments, 1,717 reads

Posted in SQLTechnet on 1 August 2012