Author Archives: Amit Banerjee

How to find out how many objects of different types are there in a SQL database

Posted on by

I have sometimes found the need on data corruption cases to compare the number of objects exported to the destination database with the source database to find out which objects got exported and which didn’t. You would need to run this script against the source and destination database and compare the output. 

One of the drawbacks of the “Generate Scripts” option in SQL Server Management Studio is that SSMS scripts out the Primary Key constraints along with the CREATE TABLE script. So, if the table is already created, the primary key will not be created if you use the script that was generated by the SSMS Generate Scripts Wizard. 

Script 

select 

CASE xtype 

WHEN 'C' THEN 'CHECK constraint' 

WHEN 'D' THEN 'Default or DEFAULT constraint ' 

WHEN 'F' THEN 'FOREIGN KEY constraint ' 

WHEN 'L' THEN 'Log ' 

WHEN 'FN' THEN 'Scalar function ' 

WHEN 'IF' THEN 'In-lined table-function ' 

WHEN 'P' THEN 'Stored procedure ' 

WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) ' 

WHEN 'RF' THEN 'Replication filter stored procedure' 

WHEN 'S' THEN 'System table ' 

WHEN 'TF' THEN 'Table function ' 

WHEN 'TR' THEN 'Trigger ' 

WHEN 'U' THEN 'User table ' 

WHEN 'UQ' THEN 'UNIQUE constraint (type is K) ' 

WHEN 'V' THEN 'View ' 

WHEN 'X' THEN 'Extended stored procedure' 

ELSE 'UNKNOWN' 

END, count(*) as counts 

from sys.sysobjects 

group by CASE xtype 

WHEN 'C' THEN 'CHECK constraint' 

WHEN 'D' THEN 'Default or DEFAULT constraint ' 

WHEN 'F' THEN 'FOREIGN KEY constraint ' 

WHEN 'L' THEN 'Log ' 

WHEN 'FN' THEN 'Scalar function ' 

WHEN 'IF' THEN 'In-lined table-function ' 

WHEN 'P' THEN 'Stored procedure ' 

WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K) ' 

WHEN 'RF' THEN 'Replication filter stored procedure' 

WHEN 'S' THEN 'System table ' 

WHEN 'TF' THEN 'Table function ' 

WHEN 'TR' THEN 'Trigger ' 

WHEN 'U' THEN 'User table ' 

WHEN 'UQ' THEN 'UNIQUE constraint (type is K) ' 

WHEN 'V' THEN 'View ' 

WHEN 'X' THEN 'Extended stored procedure' 

ELSE 'UNKNOWN' 

END

[Blog Reference]: Listing SQL instances in your environment

Posted on by

I have had multiple questions on how to enumerate SQL instances in your complete environment to run maintenance operations from a centralized environment. There are multiple tools which implement one of the methods or a variation of the methods described in the blog post below.

I keep referring people to this blog post. So, I thought I would put a referral of the same on my blog so that I can find it easily the next time.

Jonathan Sayce [Blog] has shown in his blog post what are the different methods to achieve this.

http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/10/programatically-listing-sql-servers.aspx

Monitoring Merge Agent performance using T-SQL

Posted on by

I have always loved writing T-SQL scripts and am ever-ready to write a T-SQL script when the opportunity presents itself. I just wrote another one for monitoring Merge Agent performance and spewing out some legible English to track the slow performing Merge Agents in your Replication Topology.

Read about it on:

http://blogs.msdn.com/repltalk/archive/2010/03/16/tracking-merge-agent-performance-using-distribution-database-system-tables.aspx

The script can be downloaded from either the above blog post or from the Script Center Gallery:

http://gallery.technet.microsoft.com/ScriptCenter/en-us/20467f5a-99f1-4313-9f12-6ac24346da14

Is my RESTORE operation complete?

Posted on by

When you perform a database restore operation using Management Studio Object Explorer, you might be baffled as to why the confirmation pop-up window doesn’t pop-up saying that the database restore operation is complete.

image

This is because the RESTORE operation in SQL Server can be divided into two stages if you are having a high level discussion and don’t want to get into the intricacies:

1. Copy the information from the backup media and write them into the physical files on the Windows File System

2. Perform recovery on the database to bring it online (provided in the OPTIONS tab you have selected the first radio button under the Recovery State)

The highlighted counter shows you the progress of the first stage only. The second stage doesn’t have a progress counter associated with it. If you look at the percent_complete column value in the sys.dm_exec_requests DMV.

So, if your database restore screen is showing 100% completed status and the SQL Server Errorlog is not reporting the following for the database being restored:

2010-03-13 02:56:22.650 spid61       Starting up database ‘distribution_new’.
2010-03-13 02:56:23.140 Backup       Database was restored: Database: distribution_new, creation date(time): 2009/08/04(01:37:56), first LSN: 865:804:70, last LSN: 865:833:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\distribution.bak’}). Informational message. No user action required.

Then you are phase 2 of the restore provided that session performing the database restore is not experiencing blocking.

Now for the most interesting part, Why is this happening i.e. the long recovery? One of the reasons is documented here.

How to change the TCP/IP port for a SQL Server instance

Posted on by

I wrote this script sometime back. I thought it would be a good idea to share this script. The background for writing this script was there were a bunch of SQL Server installations in the environment with the same instance names but on different boxes. The installations were pushed through programmatically and company policy was set in place which needed the TCP/IP port to be changed to a different port other than the default port i.e. 1433. The DBA obviously wouldn’t be too happy sitting and using SQL Server Configuration Manager on multiple servers doing the same monotonous task. The ask was to write a script which could be pushed to all the servers so that the port on which the SQL instance was listening on was modified programmatically.

This script changes the PORT number for a named instance of SQL Server 2008 called KATMAI to 1434. The namespace used here are:

SQL Server 2005: \root\Microsoft\SqlServer\ComputerManagement

SQL Server 2008: \root\Microsoft\SqlServer\ComputerManagement10

So if you wanted to use the script for SQL Server 2005, you would need to change the namespace to the one for SQL Server 2005. The other variables that come into play for this script are the Instance Name and the Port number:

‘Specify the instance name if any. Default is MSSQLSERVER.
‘Assumption is that we are running this for the default instance.
‘For a named instance, only provide the instance name. Eg: For a named instance LABDC\INST1, the parameter below would be strInstance = "INST1"
‘For a cluster, we need to run this script on the active node.

strInstance = "KATMAI"

‘Specify the new PORT

strPort = "1434"

So, you would have to modify the above variables for the correct Instance Name and Port number.

The script then makes use of the ServerNetworkProtocolProperty class methods to set the TCP port value.

If you need to extend this script, you can do so by creating an Array for the list of Servers in your environment and running the entire code in the script in another WHILE loop.

Another customized implementation could be if you want to change the port number for multiple instances with incremental values of Port Numbers. This can be done by querying the same namespace for getting the SQL instances on the box using SqlService. A sample WMI script is provided in the blog post below.

HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

Once you have the Instance Names, then you can again create a loop with the Port Number variable being incremented and execute the function to set the TCP/IP Port number.

Download the script from here

Reference:

How to- Configure a Server to Listen on a Specific TCP Port

Happy Automation!! 🙂