Browse by Tags

TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005

Published 14 July 8 5:26 AM | SSQA.net

DMVs are most helpful to find instant information on SQL instance without causing further delays, in any performance degradation time you can execute following TSQL to get top 10 queries that are generating lots of I/O on the server: SELECT TOP 10 total_logical_reads...

Filed under: sql server, dmv, tsql, sysindexes, query plan, querystats

Quick and simple way to know the stored procedures parameters using TSQL

Published 8 July 8 6:13 AM | SSQA.net

As you may be aware that you can pass parameters to the stored procedure when you have the selection of data requirement such as stored procedure with data type,length, parameter position and also the mode of parameter (Input or Output). Using INFORMATION_SCHEMA...

Filed under: sql server, stored procedure, tsql, information schema, parameter, views

FIX: Error message after you run the DBCC DBREINDEX Transact-SQL statement on a table that does not have a unique clustered index on a SQL Server 2005-based computer: "Msg 2511"

Published 3 July 8 4:46 PM | SSQA.net

The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Enterprise Edition, SQL Server 2005 Standard Edition & SQL Server 2005 Enterprise X64 Edition Notification Contents: New FIX: Error message after...

Filed under: sql server, kba, tsql, clustered index, dbcc dbreindex, 2511, unique

SQL Server 2005 DMV - quick information to find resource allocation & DDL bottleneck

Published 9 June 8 3:29 AM | SSQA.net

Initially I preferred to put this blog post within Performance tuning blog section here, but as it relates to the TSQL script thought this is best place to go. Anyways, if you have a performance problem the foremost option is to find whether the server...

Filed under: sql server, performance, dmv, tsql, contention, ddl, bottleneck

SQL Server: Creating BlackBox kind of trace with TSQL

Published 28 May 8 6:59 AM | SSQA.net

Here is the kind of scripts defined within TEchnet articles about running black-box kind of traces against your SQL Server instance, then also you can take advantage of the blackbox trace if you are facing intermittent problems, you want to make sure...

Filed under: sql server, profiler, trace, tsql, size, blackbox

How to drop all tables, all views, and all stored procedures from a SQL Server 2005 Database?

Published 20 May 8 3:12 AM | SSQA.net

It may not be a hardcore requirement on day-to-day basis to drop all tables, views and stored procedures from a SQL Server database within your environment, but it will be handy to have such a code at your end when such task is required. There are 2 ways...

Filed under: sql server, backup, table, drop, stored procedure, tsql, best practice, views

SQL Server 2005 detect DAC session with TSQL

Published 9 May 8 1:11 PM | SSQA.net

SQL Server 2005 has introduce a secret-door for Admins to identify and resolve any connect lockout issues within your database instance, as on http://sqlserver-qa.net/blogs/tools/archive/2007/08/04/dedicated-administrator-console-dac-saved-an-important...

Filed under: sql server, security, dmv, tsql, dac, session

SQL Server: Quick way to find Active & idle connections on a SQL instance

Published 2 May 8 4:32 AM | SSQA.net

Using SQL Server 2005 you can take help of DMV - sys.dm_exec_connections that will give server-level information about the connections. In the olden days usage of sysprocesses system table used to be heavy and same approach can be achieved by using system...

Filed under: sql server, dmv, transaction, connections, tsql, sysprocesses, active

SQL Server 2005 unable to drop schema error: Cannot drop schema <> because it is being referenced by object

Published 22 April 8 3:1 AM | SSQA.net

Recently I have received an email about unable to drop schemas that are adopted in the database design within an environment, here it goes. I'm setting up an automatic build process that also includes building one or more databases. Initially it was...

Filed under: sql server, access, best practices, object, 2005, tsql, schemas, alter authorisation

SQL Server Replication - resolving too many snapshots issues?

Published 15 April 8 6:15 AM | SSQA.net

In case you have many snapshots generated within your Replication setup and having troubles in finding what kind of scheduled job is running during the time of snapshot publication. By default you coudl take help of system catalog MSsnapshot_agents and...

Filed under: sql server, replication, snapshot, sqlagent, script, publisher, tsql, schedule

SQL Server 2008 TSQL enhancements - what I like?

Published 4 April 8 9:58 AM | SSQA.net

Simple, short and crisp - I would like to say about TSQL enhancements within SQL Server 2008. You may already aware about what's new and how best you can take advantage within your code, most of the Developers would agree about IntelliSense functionality...

Filed under: sql server, performance, blogs, technet, merge, tsql, plan guide, new features, tvp, table-value

SQL Server 2008 - TSQL behavioural changes and enhancements

Published 31 March 8 2:0 PM | SSQA.net

As usual the new release of SQL Server version 2008 had few changes from 2000 to 2005 and as usual this control-of-flow sequence should occur in order to take advantage of performance with transaction/rollback advantages. Such direction of flow should...

Filed under: sql server, performance, transaction, article, 2008, tsql, enhancement, begin

Error while executing DMV - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('.

Published 17 March 8 4:39 AM | SSQA.net

DMV - has got very special place for DBAs since the inception of SQL Server 2005. In this case one of the thme is very important to find out the index statistics within your database, such as sys.dm_db_index_physical_stats statement. Recently one of the...

Filed under: sql server, blocking, dmv, version, 2000, pack, 2005, tsql, errorr, tablels, incorrect syntax

TSQL to change collation of database, whats new in SQL 2008 then?

Published 12 March 8 1:24 PM | SSQA.net

Cross post from my BlogCasts site....( read more ) Read More...

Filed under: sql server, blog, table, tsql, collation, changes

Let a user with minimal privileges see another user, simple with TSQL - SQL Server 2005

Published 11 March 8 7:5 PM | SSQA.net

You may be aware that by default, users that have minimal privileges cannot see other users in the sys.database_principals and sys.server_principals catalog views. This means that a user with minimal privileges that owns a table cannot see other users...

Filed under: sql server, BOL, permission, tsql, privilege, user schema, definition

TSQL to get clustered index information in SQL Server 2005

Published 10 March 8 3:0 AM | SSQA.net

Recently within a supportal case with CSS we have been given the following TSQL to get information on indexes, where I have modified a bit to get 'Clustered' index information alone that was helpful to see which tables lack of clustered index...

Filed under: sql server, dmv, tsql, schema, information schema, declare, clustered index

SQL Server Audit Trace - things you need to take care!

Published 6 March 8 3:17 AM | SSQA.net

Audit trace is very helpful when you want to monitor a particular processes on your SQL Server, even for the successful logins and recent users activity on the databsaes. But the default trace would have many columns with the information and you need...

Filed under: SOX, sql server, microsoft, solution, dmv, trace, audit, tsql, trace flag, c2

SQL Server Management Studio snap-in - get alerted when executing a TSQL code!

Published 15 February 8 4:26 AM | SSQA.net

Do you like color-coded alert say when you are executing a TSQL script or code against a SQL Server instance, say if its a production server with 'red' indicator or 'green' when it is on testing platform! I have no doubt in stating the...

Filed under: sql server, management, ssms, download, demo, alert, sample, tsql, code, toolkit, management studio, snap-in

TSQL to get current executing statements - SQL Server 2005

Published 12 February 8 2:27 PM | SSQA.net

Using SP_WHO or SP_WHO2 is a common way to get list of executing processes currently on the server, within SQL Server 2005 you could take advantage of DMV that lists currently-executing statements. select r.session_id ,status ,substring(qt.text,r.statement_start_offset...

Filed under: sql server, dmv, cache, sp_who, cpu, tsql, queue, execution, runnable

SQL Server 2005 TSQL to obtain buffers by object (table, index) in the buffer cache?

Published 1 February 8 6:30 AM | SSQA.net

As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do...

Filed under: sql server, indexes, memory, dmv, cache, buffer, table, tsql, configuration