Vidhya Sagar – Blog One Stop for SQL Server related Queries

9Apr/10

Capture SQL Performance Counters through TSQL

Performance counters are useful to gather information about server status and we can analyze the server status later using these data. In my environment one of my client is not ready to pay or buy metrics tools (normally we will be using BMC, HP etc). Our windows team has written they own custom scripts to capture the counters related to them, then it comes to our team how we are going to capture these data. Immediately it strikes my mind that we have a DMV “sys.dm_os_performance_counters” to get SQL performance counter values. So I thought of utilizing this procedure to get SQL Performance counter values and storing it in a table which will help us in the future to analyze the data.

VN:F [1.9.3_1094]
Rating: 3.8/5 (4 votes cast)
VN:F [1.9.3_1094]
Rating: +2 (from 2 votes)
21Dec/09

Change \ Transfer Schema for all Objects

In my working environment we will be using different schema for production and development environments. Our development apps are configured to Dev schema and Production apps are configured to Prod schema, so when we restore the db from production to development environment, all our dev apps stopped working since the objects \ securable will be using prod schema. Here I need to change the schema to dev from prod, so the syntax will be like below

VN:F [1.9.3_1094]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.3_1094]
Rating: 0 (from 0 votes)
11Nov/09

Script to Retrieve Security Information – SQL Server 2005 and above

In my working environment I’ve asked to write a script to find security information which should return Server Logins, Database Logins and object level permissions. I’ve written that and I’m sharing this to you since this could be helpful to you.

VN:F [1.9.3_1094]
Rating: 4.7/5 (3 votes cast)
VN:F [1.9.3_1094]
Rating: 0 (from 2 votes)
11Nov/09

Script to Retrieve Security Information – SQL Server 2000

In my working environment I’ve asked to write a script to find security information which should return Server Logins, Database Logins and object level permissions. I’ve written that and I’m sharing this to you since this could be helpful to you.

VN:F [1.9.3_1094]
Rating: 5.0/5 (3 votes cast)
VN:F [1.9.3_1094]
Rating: +1 (from 1 vote)
17Sep/09

Script to find SQL Job activity details

I’ve written a script to find the job activity details, this script will give the output similar to job activity monitor in SQL2K5 or SQL2K8. In my working place we had a request to collect the job details from all the servers which includes all version of SQL Server. I’ve tried sp_help_jobactivity for SQL2K5 \ SQL2k8, however it doesn’t provide the columns needed. So I’ve written a script to get customized output, this script will also work with SQL2K. The script will fetch the following details

VN:F [1.9.3_1094]
Rating: 5.0/5 (3 votes cast)
VN:F [1.9.3_1094]
Rating: +2 (from 2 votes)