My Favorite Blogs

Tuesday, December 15, 2009

Using SQL Server 2008 PBM to Monitor Free Space

In this article I’m walking you through how to setup data file free space monitoring using SQL Server 2008 Policy-Based Management feature. You may think “Hey dude this is not a monitoring tool…!” but my answer would be “We can make this feature monitor data files free space on a database or instance”. Initially I wondered about introducing Policy-Based Management in SQL Server 2008. I know little bit about using policy management in Windows to enforce security policies and I felt that it is little complicated. But when I started working on Policy-Based Management in SQL Server 2008, I found it is really a cool and interesting feature which provides lots of options and flexibility to enforce your required policies in a database environment. Read more http://www.sqlservercentral.com/articles/PBM/68602/

Friday, November 27, 2009

Some useful scripts for DBAs

Every day in our life as DBA, we may encounter these queries in our environments. Hope this basic queries for SQL server 2005/2008 would stay as repository..



1. Whenever SQL server restarts, it kicks off the startup procedures. How do you identify the startup procedures in your system?

Answer:

SELECT ROUTINE_NAME FROM MASTER.INFORMATION_SCHEMA.ROUTINES WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

2. In order to fire those startup procedures, SQL Server should be configured for startup procs. How do you know whether SQL server is configured to check for Startup procedures?

Answer:

USE MASTER
GO

SELECT VALUE, VALUE_IN_USE, DESCRIPTION FROM SYS.CONFIGURATIONS WHERE NAME = 'scan for startup procs'
GO



It requires SQL server restart to take into effect..


3. How to execute procedures automatically whenever SQL server starts?

Answer:

USE MASTER
GO

EXEC SP_PROCOPTION usp_capture_process, 'STARTUP', 'ON'
GO

Importantly note that startup procedures should be in master database.

4. Filegroup can hold datafile/tables. How can I create a filegroup on a database?

Answer:

USE SQLDbops
GO

ALTER DATABASE SQLDbops ADD FILEGROUP FG_ViewOnly
GO

5. How can I add files to a filegroup?

Answer:

ALTER DATABASE SQLDbops ADD FILE ( NAME = SQLDbops_data1,
FILENAME = 'I:\SQLDbops_data1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP FG_ViewOnly
GO

6. How can I add a table to a filegroup?

Answer:

CREATE TABLE dbo.SQLDbopsOwners(ID int IDENTITY(1,1) NOT NULL,Name VARCHAR(150) NOT NULL,Areas VARCHAR(100) NOT NULL,LocationID INT NOT NULL, Location VARCHAR(100)) ON FG_ViewOnly
GO

7. How can I add an Index to a filegroup?

Answer:

CREATE INDEX IX_SQLDbopsOwners_LocationID ON dbo.SQLDbopsOwners(LocationID) ON FG_ViewOnly
GO

8. How can I move an object from a Primary file group to another filegroup?

Answer:

Let us take different scenarios. Table with Clustered Index and Table without Clustered Index.

Table with Clustered Index:

Suppose a table has the Clustered Index defined, hence moving the clustered Index will move the table along..
CREATE CLUSTERED INDEX IDX_SQLDBops_RenID ON dbo.SQLDbopsLicense(RenewID) ON FG_ViewOnly
GO

To move Non-Clustered index:

CREATE INDEX NC_IDX_LocID ON dbo.SQLDbopsLicense(LocationID) WITH (DROP_EXISTING = ON) ON FG_ViewOnly
GO

Table without Clustered Index:

Suppose if a table doesn't have any clustered Index, create a clustered Index on the filegroup where you want to move and then drop it..

CREATE CLUSTERED INDEX IDX_SQLDBops_RenID ON dbo.SQLDbopsLicense(RenewID) ON FG_ViewOnly
GO


DROP INDEX IDX_SQLDBops_RenID ON dbo.SQLDbopsLicense

9. How can I determine objects stored in which filegroup?

Answer:

USE [SQLDbops]
GO
SELECT sao.[name], sao.[type], si.[name], si.[index_id], sf.[name]FROM sys.indexes si INNER JOIN sys.filegroups sf ON si.data_space_id = sf.data_space_id INNER JOIN sys.all_objects sao ON si.[object_id] = sao.[object_id] WHERE si.data_space_id = 2
GO

data_space_id is id of filegroup where you want to look at..

below query helps to identify data_space_id of that filegroup.


USE [SQLDbops]
GO
SELECT data_space_id FROM sys.filegroups WHERE name = 'FG_ViewOnly'
GO

10. Usually in production servers, mostly windows account group will be given the permissions. How to know who are all members of that group account?

Answer:

xp_logininfo [Columbus\Webdevelopers]
GO

11. How to know schema difference between the tables on different servers?

tablediff -sourceserver "prod.columbus.sqldbops.com" -sourcedatabase "SQLDbops" -sourcetable "SQLDBopsOwners" -destinationserver "prod2.columbus.sqldbops.com" -destinationdatabase "SQLDbops2" -destinationtable "SQLDBopsOwners2" -q

it's DOS command. Table diff will be located in Binn directory.
Other way is to use master..xp_cmdhsell..

12. Date and Time functions?

Answer:

SELECT {fn curtime()}
SELECT {fn now()}

SELECT GETDATE()

13. How to find different collations available in a server?


Answer:

SELECT * FROM ::fn_helpcollations()

14. How to know Server collation?

Answer:

sp_helpsort

15. How to know collation of a database?

Answer:

SELECT DATABASEPROPERTYEX('SQLDbops','Collation');

16. How to remove windws policy for a SQL login?

Answer:

Two steps to follow:

In order to remove windows policy for a user, it should have MUST_CHANGE option to be OFF

1.To change the password and keep the MUST_CHANGE flag:

ALTER LOGIN surpal WITH PASSWORD = 'Y' UNLOCK MUST_CHANGE

2. To uncheck the checkboxes for expiration and/or policy:

ALTER LOGIN surpal WITH CHECK_EXPIRATION = OFF

ALTER LOGIN surpal WITH CHECK_POLICY = OFF

16. How to change password for a SQL login?

Answer:

ALTER LOGIN MyLogin WITH PASSWORD = 'newpassword'

17. How to check if DAC on your session?

SELECT t2.session_id, t2.program_name, t2.host_name, t1.name FROM sys.tcp_endpoints as t1 JOIN sys.dm_exec_sessions as t2 ON t1.endpoint_id=t2.endpoint_id WHERE t1.name='Dedicated Admin Connection'

Monday, October 19, 2009

Connect to SSIS Service on machine "ServerName" failed: Error loading type library/DLL.

Last week I was requested to install SSIS on an existing SQL instance, I installed the SSIS successfully but it failed to open in the SQL Server Management Studio (SSMS). After some troubleshooting and msn tips, I understood that is a declared bug on MS SQL 2005.

The Existing SQL instance version is 9.0.3215 which is running on Windows 2003 64bit Edition. So I’ve to install SISS with SP2 and Hotfix 3215. I’ve installed successfully the SSIS component (hope you aware how to add a component on an existing SQL instance or refer http://support.microsoft.com/kb/913967 ) and installed SP2 and Hotfix 3215 on top of it. All went fine and rebooted the server as per the hotfix requirement.

After the installation I tried to open the SSIS on SSMS, but it failed to open and thrown the below error.

Error Description

TITLE: Connect to Server
------------------------------
Cannot connect to ServerName.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Connect to SSIS Service on machine "ServerName" failed:
Error loading type library/DLL.

------------------------------
Connect to SSIS Service on machine "ServerName" failed:
Error loading type library/DLL.

------------------------------
BUTTONS:
OK
------------------------------

Solution

As per Microsoft Knowledge Base, when you install SSIS on an existing SQL server 2005 instance on 64-bit server, the installer unregisters the 32-bit DTS.DLL file while installing the 64-bit DTS.DLL file. The 64-bit DTS.DLL file may be installed with SP2 and also may be with hotfix.

To resolve this problem, you have to register two dll files manually 1) dts.dll and and 2) MsDtsSrvrUtil.dll. For 32-bit version of SSIS you’ve to register only 32-bit dlls but for 64-bit version of SSIS , you have to register both 32-bit and 64-bit dlls. Because as per my experience, first I’ve installed only 64-bit version of DLLs but I got the below error then I registered 32-bit version also, then it worked fine. Refer http://support.microsoft.com/kb/919224 for more details.



Registering DLLs manually

Go to command prompt (Start --Run -- Cmd)

cd c:\windows\system32

Registering 64-bit version dlls
regsvr32 "C:\Program Files\Microsoft SQL Server"\90\DTS\Binn\dts.dll
regsvr32 "C:\Program Files\Microsoft SQL Server"\90\DTS\Binn\MsDtsSrvrUtil.dll

Registering 32-bit version dlls
regsvr32 "C:\Program Files (x86)\Microsoft SQL Server"\90\DTS\Binn\dts.dll
regsvr32 "C:\Program Files (x86)\Microsoft SQL Server"\90\DTS\Binn\MsDtsSrvrUtil.dll

Wednesday, October 7, 2009

SQL PerfMon Counters Missing

SQL Server Performance Monitor Counters are Missing
To solve this problem follow the following steps:
Stop SQL Server Service
Open Command Prompt (Start – Run – Cmd)
Run the command: NET STOP MSSQLSERVER
Then, this question will appear: “The following services are dependent on the SQL Server (MSSQLSERVER) service. Stopping the SQL Server (MSSQLSERVER) service will also stop these services: SQL Server Agent (MSSQLSERVER). Do you want to continue this operation? (Y/N) “
Press “Y”
Open Command Prompt and get located on the BINN directory of SQL Server. Probable locations are:
c:\Program Files\Microsoft SQL Server\90\Tools\Binn
c:\Program Files\Microsoft SQL Server\80\Tools\BINN
Once located on the BINN directory, using the Command Prompt, execute the following command: lodctr sqlctr.ini
Restart your computer.

SQL Server OS Components



SQL Server OS Components diagram:-

Monday, September 28, 2009

Best practices of Password Change on SQL Failover cluster (Applicable n+1 nodes cluster)

As per my experience, I could suggest the following steps will help to change periodic password change on SQL Service account password on your Active/Passive and n+1 node cluster environment.

Important note: This activity is required downtime, make sure you informed your business about this outage.

Use RDP to log into each physical node of the cluster

Chaning password for Windows Cluster services.

Executed directly by: Start Run and enter Services.msc

1. Change password for windows cluster services.


Chaning password for SQL Services.

Executed directly by: StartRun and enter SQLServerManager.msc(SQL 2005) or SQLServerManager10.msc(SQL 2008)

2. In SQL Server Configuration Manager Change the Pwd for SQL services. Start from Active node.
3. Change 1st Active instance and change its other passive nodes


Once password change on all SQL services on Active and Passive nodes, SQL Instances need to be failed over to all cluster nodes to ensure that the password updated on all.

Failover test on Cluster Administrator.

Executed directly by:Start Run and entering cluadmin.exe

4. Once done on all nodes, failover the instances to another nodes and make the pwd get effected.

6. Restart the Cluster services on each nodes to make the pwd get effected.

This tip is applicable for SQL Server 2000 and 2005 on Windows 2000 & 2003 Cluster.

Thanks
Jay.

Wednesday, September 16, 2009

SQL Server Service Broker example on how to configure, send and receive messages

SQL Server 2008 and 2005 offer the Service Broker feature. In this tip we will go through the different components of service broker and step by step on how to setup Service Broker for a single database. Read further http://www.sqlservercentral.com/articles/Service+Broker/67513/ http://www.mssqltips.com/tip.asp?tip=1836