Useful SQL Server DBCC Commands

DBCC CHECKFILEGROUP - Checks all tables file group for any damage.


DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.

DBCC SQLPERF - It gives report on current usage of transaction log in percentage.

DBCC SQLPERF (LOGSPACE)

This option (documented) returns data about the transaction log for all of the databases on the SQL Server, including Database Name, Log Size (MB), Log Space Used (%), and Status.

DBCC SQLPERF (UMSSTATS)

This option (undocumented) returns data about SQL Server thread management.

DBCC SQLPERF (WAITSTATS)

This option (undocumented) returns data about wait types for SQL Server resources.

DBCC SQLPERF (IOSTATS)

This option (undocumented) returns data about outstanding SQL Server reads and writes.

DBCC SQLPERF (RASTATS)

This option (undocumented) returns data about SQL Server read-ahead activity.

DBCC SQLPERF (THREADS)

This option (undocumented) returns data about I/O, CPU, and memory usage per SQL Server thread. [7.0, 2000]



DBCC CACHESTATS displays information about the objects currently in the buffer cache, such as hit rates, compiled objects and plans, etc.

DBCC DROPCLEANBUFFERS: Use this command to remove all the data from SQL Server’s data cache (buffer) between performance tests to ensure fair testing. You may first want to run the CHECKPOINT command first. Running CHECKPOINT will write all dirty buffers to disk.

DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server.

DBCC FLUSHPROCINDB (@intDBID)

DBCC INDEXDEFRAG: In SQL Server 2000, Microsoft introduced DBCC INDEXDEFRAG to help reduce logical disk fragmentation.

DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name)

DBCC MEMORYSTATUS: Lists a breakdown of how the SQL Server buffer cache is divided up, including buffer activity.

DBCC MEMORYSTATUS

DBCC OPENTRAN: This command is used to identify the oldest open transaction in a specific database.

DBCC OPENTRAN(‘database_name’)

DBCC PAGE: Use this command to look at contents of a data page stored in SQL Server.

DBCC PAGE ({dbid
dbname}, pagenum [,print option] [,cache] [,logical])

DBCC PINTABLE & DBCC UNPINTABLE: By default, SQL Server automatically brings into its data cache the pages it needs to work with. These data pages will stay in the data cache until there is no room for them, and assuming they are not needed, these pages will be flushed out of the data cache onto disk. At some point in the future when SQL Server needs these data pages again, it will have to go to disk in order to read them again into the data cache for use. If SQL Server somehow had the ability to keep the data pages in the data cache all the time, then SQL Server’s performance would be increased because I/O could be reduced on the server.

DECLARE @db_id int, @tbl_id int

USE Northwind

SET @db_id = DB_ID(‘Northwind’)

SET @tbl_id = OBJECT_ID(‘Northwind..categories’)

DBCC PINTABLE (@db_id, @tbl_id)

DECLARE @db_id int, @tbl_id int

USE Northwind

SET @db_id = DB_ID(‘Northwind’)

SET @tbl_id = OBJECT_ID(‘Northwind..categories’)

DBCC UNPINTABLE (@db_id, @tbl_id)

DBCC SHOWCONTIG: Used to show how fragmented data and indexes are in a specified table

DBCC SHOWCONTIG (Table_id, IndexID)

DBCC PROCCACHE: Displays information about how the stored procedure cache is being used.

DBCC PROCCACHE

DBCC REINDEX: Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database.

DBCC DBREINDEX(‘table_name’, fillfactor)

DBCC SHOW_STATISTICS: Used to find out the selectivity of an index. Generally speaking, the higher the selectivity of an index, the greater the likelihood it will be used by the query optimizer. You have to specify both the table name and the index name you want to find the statistics on.

DBCC SHOW_STATISTICS (table_name, index_name)

DBCC SQLMGRSTATS: Used to produce three different values that can sometimes be useful when you want to find out how well caching is being performed on ad-hoc and prepared Transact-SQL statements.

DBCC SQLMGRSTATS

Item Status

————————- ———–

Memory Used (8k Pages) 5446

Number CSql Objects 29098

Number False Hits 425490

Here’s what the above means:

• Memory Used (8k Pages): If the amount of memory pages is very large, this may be an indication that some user connection is preparing many Transact-SQL statements, but it not un-preparing them.

• Number CSql Objects: Measures the total number of cached Transact-SQL statements.

• Number False Hits: Sometimes, false hits occur when SQL Server goes to match pre-existing cached Transact-SQL statements. Ideally, this figure should be as low as possible.

DBCC SQLPERF (UMSSTATS): When you run this command, you get output like this. (Note, this example was run on a 4 CPU server. There is 1 Scheduler ID per available CPU.)

Statistic Value

——————————– ————————

Scheduler ID 0.0

num users 18.0

num runnable 0.0

num workers 13.0

idle workers 11.0

work queued 0.0

cntxt switches 2.2994396E+7

cntxt switches(idle) 1.7793976E+7

Scheduler ID 1.0

num users 15.0

num runnable 0.0

num workers 13.0

idle workers 10.0

work queued 0.0

cntxt switches 2.4836728E+7

cntxt switches(idle) 1.6275707E+7

Scheduler ID 2.0

num users 17.0

num runnable 0.0

num workers 12.0

idle workers 11.0

work queued 0.0

cntxt switches 1.1331447E+7

cntxt switches(idle) 1.6273097E+7

Scheduler ID 3.0

num users 16.0

num runnable 0.0

num workers 12.0

idle workers 11.0

work queued 0.0

cntxt switches 1.1110251E+7

cntxt switches(idle) 1.624729E+7

Scheduler Switches 0.0

Total Work 3.1632352E+7

Below is an explanation of some of the key statistics above:

• num users: This is the number of SQL Server threads currently in the scheduler.

• num runnable: This is the number of actual SQL Server threads that are runnable.

• num workers: This is the actual number of worker there are to process threads. This is the size of the thread pool.

• idle workers: The number of workers that are currently idle.

• cntxt switches: The number of context switches between runnable threads.

• cntxt switches (idle): The number of context switches to the idle thread.

• DBCC UPDATEUSAGE: The official use for this command is to report and correct inaccuracies in the sysindexes table, which may result in incorrect space usage reports. Apparently, it can also fix the problem of unreclaimed data pages in SQL Server. You may want to consider running this command periodically to clean up potential problems. This command can take some time to run, and you want to run it during off times because it will negatively affect SQL Server’s performance when running. When you run this command, you must specify the name of the database that you want affected.

• DBCC UPDATEUSAGE (‘databasename’)

Server Level Security

sysadmin

Members of the sysadmin fixed server role can perform any activity in the server.

serveradmin

Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

securityadmin

Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

Security NoteThe ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.

processadmin

Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.

setupadmin

Members of the setupadmin fixed server role can add and remove linked servers.

bulkadmin

Members of the bulkadmin fixed server role can run the BULK INSERT statement.

diskadmin

The diskadmin fixed server role is used for managing disk files.

dbcreator

Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

public

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users.

SQL Server - Database Level Security

Fixed Database Roles:


SQL Server includes a Fixed Database Role. They are mainly intended to organize the administrative tasks. A user can also have more than one role. The fixed database roles include the following:

•Db_accessadmin: It helps a user to access a database, but not manage the database security level.

•Db_backupoperator: It performs backup, checkpoints, and DBCC commands, but cannot manage the database level security.

•Db_datareader: It is used to read all the data in the database. It is moreover an equivalent of a grant on all objects, and it can be overridden by a deny permission.

•Db_datawriter: It is used to write all the data in the database. It is moreover an equivalent of a grant on all objects, and it can be overridden by a deny permission.

•Db_ddladmin: It helps to issue DDL commands like create, alter, and drop.

•Db_denydatareader: It is being used to read from any table in the database. This Deny can easily override the Grant permission.

•Db_denydatawriter: It is mainly being used to block modifying data in any table in a database. They would override any object-level grant.

•Db_owner: It has all the permissions in the database. This is also not equivalent to server sysadmin role. An object-level deny will override membership in this role.

•Db_securityadmin: It helps to manage the database-level security roles and permission.

SQL Server error messages – severity levels – sys.messages

There are about 98318 error messages as of SQL Server 2008 - including repetitions for all the languages supported.

There are only 8938 errors types in sql server 2008 for English language and 11 languages are supported so 11 * 8938 = 98318


sys.messages catalog view represents one row per error message.

You can simply run select * from sys.messages to see all the error messages. You can see that every message has a unique message_id and the description in text column.

Another interesting column is severity. When an error is raised by the SQL Server Database Engine, the severity of the error indicates the type of problem encountered by SQL Server.

Following are the severity levels:

0-10

These are only Informational messages.

11-16

Errors that can be corrected by the user.

Example: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

11

A given object does not exist.

Example: Table '%.*ls' does not exist.

12

Indicates that queries are not requesting locks since they are using query hints.

Example: Could not continue scan with NOLOCK due to data movement.

13

Transaction deadlock errors.

Example: Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

14

Security-related errors.

Example: The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

15

Syntax errors in T-SQL

Example: A TOP N value may not be negative.

16

Errors that can be corrected by the user.

17-19

Indicate software errors that cannot be corrected by the user.

17

Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.

Example: Time-out occurred while waiting for buffer latch type %d for page %S_PGID, database ID %d.

18

Indicates an issue in the Database Engine

Example: Replication-%s: agent %s failed. %s

19

Indicates that a non-configurable Database Engine limit has been exceeded. The current batch gets terminated. Error messages with a severity level from 19 through 25 are written to the error log.

Example: There is insufficient system memory in resource pool '%ls' to run this query.

20-25

These errors indicate system problems and are fatal errors. Error messages with a severity level from 19 through 25 are written to the error log.

Example: Unable to deallocate a kept page.

20

Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged.

Example: Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator.

21

Unlike 20, this one indicates that a problem has been encountered that affects all tasks in the current database.

Example: An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.

22

Indicates that the table or index specified in the message has been damaged by a software or hardware problem.

Example: The row object is inconsistent. Please rerun the query.

23

Integrity of the entire database is in question because of a hardware or software problem.

Example: Possible schema corruption. Run DBCC CHECKCATALOG.

24

Indicates a media failure.

Example: The log record at LSN %S_LSN is corrupted.

Hope you like it.

sys.dm_os_memory_clerks shows duplicate entries for memory clerks

if you run the following code in SQL Server 2008 RTM:

1 select * from sys.dm_os_memory_clerks

2 where type = 'MEMORYCLERK_SQLBUFFERPOOL'

You will get the following output:

You can observe that there are 2 entries for the BUFFER POOL memory clerk. Initially, I thought that it is for different memory nodes and I was wondering that it could have something to do with NUMA. But if you observer that memory_node_id columns shows 0 for both the entries, leaving me completely baffled, more so when the memory_clerk_address for both the records are different.

The other entry (what we see as duplicate) is actually for DAC . SQL Server 2008 RTM shows the physical memory node (0) for DAC entry which is incorrect. This issue was fixed in R2. If you run the same DMV in SQL Server 2008 R2, you will get the correct output as follows:

Memory Node ID 64 is logical and belongs to DAC.

Moving System Databases

Moving System Databases


Moving the master Database


To move the master database, follow these steps.

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.


-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\

LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\

DATA\mastlog.ldf

If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:


-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

5. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.

6. Move the master.mdf and mastlog.ldf files to the new location.

7. Restart the instance of SQL Server.



8. Verify the file change for the master database by running the following query.



SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID('master');

GO

Moving the tempdb database



Note

Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.

1. Determine the logical file names of the tempdb database and their current location on the disk.


SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

2. Change the location of each file by using ALTER DATABASE.


USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');

GO

3. Stop and restart the instance of SQL Server.

4. Verify the file change.


SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

5. Delete the tempdb.mdf and templog.ldf files from the original location.



This procedure applies to move all system databases except the master and Resource databases.

1. Stop the instance of SQL Server if it is started.

2. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

• For the default (MSSQLSERVER) instance, run the following command:


NET START MSSQLSERVER /f /T3608

• For a named instance, run the following command:


NET START MSSQL$instancename /f /T3608

3. For more information, see How to: Start an Instance of SQL Server (net Commands).

4. For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.


ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

For more information about using the sqlcmd utility, see Using the sqlcmd Utility.

5. Exit the sqlcmd utility or SQL Server Management Studio.

6. Stop the instance of SQL Server. For example, run NET STOP MSSQLSERVER.

7. Move the file or files to the new location.

8. Restart the instance of SQL Server. For example, run NET START MSSQLSERVER.

9. Verify the file change by running the following query.


SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'');

SQL CMD

SQLCMD -A -S SEGOTN2258

sqlcmd -S tcp:192.138.114.33,2021

How to find the SSIS package executed by which sql job

use msdb
go
select [name],Step_name,command,[server],database_name from sysjobsteps inner join sysjobs
on sysjobsteps.job_id = sysjobs.job_id
where [command] like '%master%'

Convert Litespeed backup to Native backup

Extractor.exe -F "" -E ""
===============================================================/> Convert LiteSpeed Backups to SQL Server Backups
The extraction utility (extractor.exe) allows you to create MTF compliant SQL Server backup files from LiteSpeed backup files through the command-line. The devices created by the extractor utility can be restored on any SQL Server using the native RESTORE DATABASE or RESTORE LOG commands. The utility must be run on the server where the backup files are located.

This topic covers:

Run the Extraction Utility

Syntax

Arguments

Example

Returns

Run the Extraction Utility
To run the extraction utility

Display a command prompt for your operating system.
If the LiteSpeed installation directory is not placed in the environment PATH, change the directory until you are in the directory containing extractor.exe. On a default installation this is: %PROGRAM_FILES%\Quest Software\LiteSpeed\SQL Server

Run extractor.exe with the appropriate arguments – see below for the command syntax.

Syntax
extractor.exe Extract
[-?]
[
[-F sqllitespeed_backup_file]
[-E base_file_name]
[-N file_number]
[-K encryption_key]
[-L 0|1|2 ]
[-I]
]



Note: Arguments are case-sensitive and can be preceded with "-" or "/".

Arguments
Argument
Description

-?
Displays the syntax summary of LiteSpeed extractor utility switches.

-E base_file_name
Specify the location and name of the Microsoft Tape Format (MSTF) base file.

This is the base file created by the extractor.exe, containing the native SQL Server backup.

The extractor utility will create one backup device file for each thread used in a LiteSpeed backup. If a LiteSpeed backup was performed using multiple backup files (e.g., a "striped" backup), one MSTF device will be created for each backup file specified in the LiteSpeed backup. If a full path is not specified, the extracted files will be created in the current directory.

Any additional files created will be named using the following naming convention:

MTF.BAKx

Where:

‘MTF.BAK’ is the specified Microsoft Tape Format base file.

x is a number or letter that represents the sequence of the files. x can take any value from [0..Y].

-F
The name of the LiteSpeed backup device file to be extracted. Multiple instances of this parameter can be supplied for multiple backup devices (e.g. striped backups).

-I
Used to re-initialize the extractor files.

0 - Do not reinitialize the extractor files (default value).

1 - Reinitialize backup service.

-L
Logging options 0..2.

-K encryption_key
String used to encrypt database backups and used for restore authorization. Default is No Encryption.

-N file_number
Number of the file to extract from backup.


Example
Extract the LiteSpeed backup Northwind.bak to native SQL Server backup NorthwindNative.bak:

extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak -N1

This generates native SQL Server backup files. You can restore from them using native SQL Server RESTORE commands.

Returns
0 (success) or non-zero (failure). Return codes represent the native error number returned from SQL Server for any errors encountered during the operation.

Access sql server using DAC

sqlcmd -Sadmin:.

sqlcmd -A -S instance_name

Change the recovery model of databases

--delcare variables
declare @vchrTable varchar(200),
@vchrMsg varchar(200)

--declare the cursor
DECLARE curTables CURSOR FOR
SELECT name from master..sysdatabases where not name in ('tempdb')

--open the cursor
OPEN curTables

--loop through the cursor lines
FETCH NEXT FROM curTables INTO @vchrTable
WHILE @@FETCH_STATUS = 0
BEGIN
set @vchrMsg = 'ALTER DATABASE [' + @vchrTable + '] SET RECOVERY SIMPLE;'
print @vchrMsg
exec (@vchrMsg)

FETCH NEXT FROM curTables INTO @vchrTable
END

--clean up
CLOSE curTables
DEALLOCATE curTables

====================================================
EXEC sp_MSForEachDB 'PRINT ''ALTER DATABASE [?] SET RECOVERY FULL''; PRINT ''GO'''

Moving tempDB Database

Moving tempDB Database
------------------------
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'J:\SQL2005.1\Data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'J:\SQL2005.1\Data\tempdb.ldf')

Add file to TempDB filegroup (with Autogrow OFF)
------------------------------------------------

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\Data\tempdev2.ndf' , SIZE = 10240KB , FILEGROWTH = 0)
GO
--------------------------
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 1024000KB )

USE [master]
GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 512000KB )
GO

Fragmantation in Tables

SELECT object_name (object_id),* FROM sys.dm_db_index_physical_stats
(DB_ID(N'NIIP'), NULL, NULL, NULL , 'DETAILED')

dbcc showcontig

http://msdn.microsoft.com/en-us/library/ms175008.aspx

List size of all tables in SQL 2008

List size of all tables in SQL 2008

with table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )
AS (SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o ON o.object_id = p.object_id
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id
WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)

SELECT t.schema_name
, t.table_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
,sum(t.tbl_rows) as rows

FROM table_space_usage as t

GROUP BY t.schema_name , t.table_name

ORDER BY used_in_kb desc

How to check Restore history of a database

Restore history

select * from msdb..restorehistory
where destination_database_name like '%cmdb%'

RESTORE HEADERONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

List user-defined stored procedures, views, tables, or triggers:

o Stored Procedures:

Select Routine_name
From Information_schema.Routines
Where Routine_type = 'PROCEDURE' and Objectproperty
Object_id(Routine_name), 'IsMsShipped') = 0
Order by Routine_name

o Views:


Select Table_name as "View name"
From Information_schema.Tables
Where Table_type = 'VIEW' and Objectproperty
(Object_id(Table_name), 'IsMsShipped') = 0

o Tables:

Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty
(Object_id(Table_name), 'IsMsShipped') = 0

o Triggers:

Select Object_name(so.parent_obj) as "Table name", so.[name] as "Trigger name"
From sysobjects so
Where Objectproperty(so.[id], 'IsTrigger')=1 and
Objectproperty(so.[id], 'IsMSShipped')=0
Order by 1,2

How to find Last Execution Date of Stored Procedure

To get last execution date of the stored procedure if SQL Server has the Query Plan for it.

SELECT


qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,
qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text like ‘%USP_CDS_GetUserDetails%’

Start Powershell Application with “RunAs” Admin

Check out how to start Windows Powershell Application with “RUNAS” administrator

PS C:\Users\sufian> start-process powershell_ise -verb runAs

Check Enabled Traces

How to check how many Traces are enabled on the server and how traces are set (globally or session)

In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server.

The following example displays the status of all trace flags that are currently enabled globally.

DBCC TRACESTATUS(-1);
GOThe following example displays the status of trace flags 2528 and 3205.

DBCC TRACESTATUS (2528, 3205);
GOThe following example displays whether trace flag 3205 is enabled globally.

DBCC TRACESTATUS (3205, -1);
GOThe following example lists all the trace flags that are enabled for the current session.

DBCC TRACESTATUS();
GO

How to truncate Mirrored Database Log File

Mirroring is a new feature comes with SQLServer 2005.
Mirroring provides High Availibity of databases without any dataloss.
Mirroring Comes with three flavours
(1) High Performance (asynchronous).
(2) High safety without automatic failover (synchronous).
(3) High safety with automatic failover(synchronous) need withness server.

(Read more in BOL Look For : mirroring databases [SQL Server])

This document will focus on how to shrink log file while databases are participating in mirroring.

Database files participating in mirroring can’t be shirnked by using truncate_only option with Backup log command.

To shrink Log file of database participating in mirroring, Backup the log file at any location (can be a local drive or network location)

BackupLog to disk=‘D:\ff\dbname.trn’

Lets check if all transaction is written to disk.

DBCC LOGINFO(‘database name’)

Transaction which are written on disk will have status=0

In case if last transaction show status=2 then backup the database log once again.

Now shrink the file

DBCC SHRINKFILE(Logfileid)

or

DBCC SHRINKFILE(Filename,minsize)

The file will shrink to its maximum extent.