Scripts to prepare SQL Server Inventory

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010

3

You never know, when you may need to rebuild a SQL sever with exact properties and configurations. Although there are dozen of 3rd party tools and software available in market to prepare enterprise level SQL Server inventory hosted in your environment like Microsoft Assessment and Planning (MAP), but keeping your own DBA script handy is big pleasure. Here is my own version of script that can bring you many hidden properties of SQL Server that are worth storing along with SQL inventory.

 

SET NOCOUNT ON
-- FIND SQL SERVER COLLATION
declare @SerCollation varchar(70)
select @SerCollation=convert(sysname, serverproperty(N'collation'))
PRINT 'SQL Server Default Collation is: '+@SerCollation
 
-- FIND SQL SERVER AND AGENT SERVICE ACCOUNT INFORMATION
PRINT 'SQL & Agent Serice Account Information: '
DECLARE @SQLserviceaccount varchar(50), @AgentServiceaccount varchar(50)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName',
@SQLserviceaccount OUTPUT, N'no_output'
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT', N'ObjectName',
@AgentServiceaccount OUTPUT, N'no_output'
SELECT @SQLserviceaccount as [SQLServer_ServiceAccount], @AgentServiceaccount as SQLAgent_ServiceAccount
 
-- FIND SQL SERVER AUTHENTICATION, AUDIT MODE & INSTALLATION PATH
PRINT 'SQL Server Authentication, Audit Mode & Installation path: '
declare @SmoLoginMode int, @SmoRoot nvarchar(512), @SmoAuditLevel int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', 
N'SQLPath', @SmoRoot OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', @SmoLoginMode OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'AuditLevel', @SmoAuditLevel OUTPUT
 
SELECT(case when @SmoLoginMode =1 then 'Windows Authentication'
else 'Mixed Mode Authentication'  END) AS [LoginMode],
(case When @SmoAuditLevel=0 then 'None.'
When @SmoAuditLevel=1 then 'Successful Logins Only'
When @SmoAuditLevel=2 then 'Failed Logins Only'
When @SmoAuditLevel=3 then 'Both Failed and Successful Logins Only'
else 'N/A' END) AS [AuditLevel],
@SmoRoot as [SQLInstallation Location]
 
-- FIND LICENSE AND PAGE FILE INFORMAITON
PRINT 'License and page file information: '
Declare @version varchar(47), @CDKey varchar(40), @PageFile varchar(50)
Select @version = @@version
create table #PageFileDetails (data varchar(500))
insert into #PageFileDetails  exec master.dbo.xp_cmdshell 'wmic pagefile list /format:list'
select @PageFile=rtrim(ltrim(data)) from #PageFileDetails where data like 'AllocatedBaseSize%'
drop table #PageFileDetails
 
If charindex('2000',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration',
@value_name='CD_KEY', @Value=@CDKey OUTPUT
SELECT 'SQL 2000' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
 
Else If charindex('2008',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
 
Else If charindex('2008 R2',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\150\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008 R2' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
Else
SELECT @version AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses

Setup a low disk space alert for SQL Server drives

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010

2

This is the pretty easy and clean script that you may use to check periodically your database server drive space. You can create a job to run on hourly basis to execute this code, along with a database mail code to send you alert if  any of drive  has less than 15% or 20% threshold value.

-- Create a global temp table
CREATE TABLE ##space( dletter varchar(3), tspace BIGINT, fspace int, percentfree numeric(5,2))
-- Insert drive details
INSERT INTO ##space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
-- Declare variables
DECLARE   @oFSO   INT, @oDrive INT, @drsize VARCHAR(255), @ret   INT
-- invoke OACreate
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
DECLARE @dletter VARCHAR(3), @fspace INT, @tspace BIGINT
while (select count(*) from  ##space where tspace is null)>0
begin
   select top 1 @dletter = dletter  + ':\',@fspace = fspace from ##space where tspace is null
   EXEC   @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
   EXEC   @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
   UPDATE   ##space SET   tspace = CAST(@drsize AS BIGINT) WHERE   lower(dletter) + ':\'   = lower(@dletter)
   EXEC master.dbo.sp_OADestroy @oDrive
end
EXEC master.dbo.sp_OADestroy @oFSO
update   ##space set   percentfree = fspace/((tspace/1024.0)/1024.0)*100 
-- Select your data
select [Drive] = dletter ,
         [Total Space GB]= convert(numeric(10,3), (tspace/1024.0)/1024.0/1024) ,
         [Free Space GB]=convert(numeric(10,3),fspace/1024.0) ,
         [% Free]= percentfree 
         from   ##space
-- Drop temporary table
drop table ##space

Get Notification as soon as SQL Services are restarted

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev | Posted on 17-09-2010

1

Being production DBA and responsible for a dozen to 100+ SQL Servers running in your organization or datacenter, Sometimes, you must be wondering that how you can make sure you are fully aware what is going when you are not at work. There is an easy way to stay in touch with your SQL Server(s), you can create a SQL job on the server, relax and hope you never get such alarm. yup, as soon as SQL Services will restart, the first thing its going to do is send you an email for restart notification.

 

DECLARE @UpTimeDays int
DECLARE @SQLSvcStarted varchar(20)
DECLARE @rc int
DECLARE @msg varchar(1000)
SET @UpTimeDays=(select DateDiff(D, CrDate, GetDate()) from master..sysdatabases where name='tempdb')
IF @UpTimeDays = 0
BEGIN
SET @SQLSvcStarted = (select convert(varchar(20), CrDate, 113) 
from master..sysdatabases where name = 'tempdb')
SET @msg='The SQL Services on <b>'+@@SERVERNAME+'</b> was restarted on <b>'+@SQLSvcStarted +'</b>'
EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfileName',
@recipients = 'dk@sqlknowledge.com',
@importance = 'high',
@subject = 'SQL Server Restart Notification!',
@body_format = 'html',
@body = @msg,
@exclude_query_output = 1
IF @rc = 1 RAISERROR('xp_smtp_sendmail Failed', 16, 1)
END

Script to find used and free space in Database files

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 02-08-2010

2

In an Enterprise world, you may be responsible to monitor 1000+ databases hosted on hundreds of SQL Server instances. On a lazy afternoon, suddenly low disk space alarm hit your inbox. What will you do, storage can not be added or expended on the fly. Here is the tested script to find used and free space from SQL Server database’s data files that you can shrink to easy the current situation.

-- Find Log file information and save in temp file using dbcc sqlperf logspace
CREATE TABLE #LogSpace (DatabaseName varchar(40), LogSize int, LogSpace int, status bit)
INSERT #LogSpace exec ('dbcc sqlperf(logspace) WITH NO_INFOMSGS') 
 
-- Find data file information using cursor and dbcc showfilestats
Declare @DatabaseName varchar(500)
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, 
Used_Space int, Name1 varchar(25),NameofFile Varchar(900) )
Declare curDB cursor for select name from master..sysdatabases 
open curDB
fetch curDB into @DatabaseName
while @@fetch_status = 0
begin
    if databasepropertyex(@DatabaseName,'Status') = 'ONLINE'
    begin
    insert into #DBSpace exec ('USE [' + @DatabaseName + ']  DBCC SHOWFILESTATS WITH NO_INFOMSGS')
    end
    fetch curDB into @DatabaseName
end
close curDB
deallocate curDB 
 
-- Select data in tabular format with proper headings & order by
select left(sd.name,30) AS 'DatabaseName',
(ff.[DataFileSpace(MB)])+ls.LogSize as 'DatabaseSize(MB)',ff.[DataFileSpace(MB)],
ff.[DataFileUsedSpace(MB)], ff.[DataFileFreeSpace(MB)],
ls.LogSize as 'LogFileSize(MB)', ls.LogSpace as'LogFileSpaceUsedIn(%)'
from #DBSpace dbs join master..sysdatabases sd on sd.filename=dbs.NameofFile
join #LogSpace ls on sd.name=ls.DatabaseName
join
(select  sf.dbid, sum(dbss.TotalSpace/16) as 'DataFileSpace(MB)', 
sum(dbss.Used_Space/16) as 'DataFileUsedSpace(MB)', 
(sum(dbss.TotalSpace/16)- sum(dbss.Used_Space/16)) 
as 'DataFileFreeSpace(MB)' from #DBSpace dbss
join master.sys.sysaltfiles sf on rtrim(sf.filename)= rtrim(dbss.NameofFile)
group by sf.dbid) ff on ff.dbid=sd.dbid
order by 'DataFileFreeSpace(MB)' desc 
 
-- Drop temporary tables manually
Drop table #DBSpace
DROP TABLE #LogSpace 

Audit databases – Using DDL triggers

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, Uncategorized | Posted on 05-06-2010

3

As a DBA, do you ever wonder what is going inside your production database? Who is changing database schema during production time without informing you. Now, you need not to worry, you can setup DDL triggers to easily records all changes in table; like what was changed, when and by whom etc. Given is two set of commands that you need to use and leave all worries aside. Also, you can prepare customized email alerts on top of data table to notify you as soon as an event occurs.

Create a table that will store all records. For example I am using MSDB database to store this table.
a DDL trigger need to created inside database; for that you want to enable audit. This trigger will capture all DDL events and save in table. Test and check process by creating test procedure, tables etc.

1. Script to create table

USE [msdb] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[DatabaseChangeLog]( 
    [LogId] [int] IDENTITY(1,1) NOT NULL, 
    [DatabaseName] [varchar](256) NOT NULL, 
    [EventType] [varchar](50) NOT NULL, 
    [ObjectName] [varchar](256) NOT NULL, 
    [ObjectType] [varchar](25) NOT NULL, 
    [SqlCommand] [varchar](max) NOT NULL, 
    [EventDate] [datetime] NOT NULL CONSTRAINT [DF_DBChangeLog_EventDate]  DEFAULT (getdate()), 
    [LoginName] [varchar](256) NOT NULL 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
 

clip_image002[4]

 
2. Script to create database trigger

USE [SQLKnowledge] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE trigger [TrgDBChangeLog] 
on database 
for DDL_DATABASE_LEVEL_EVENTS 
as 
set nocount on 
If Object_ID('msdb.dbo.DatabaseChangeLog') IS NOT NULL 
BEGIN 
declare @data xml 
set @data = EVENTDATA() 
insert into msdb.dbo.DatabaseChangeLog(databasename, eventtype, objectname, objecttype, sqlcommand, loginname) 
values( 
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 
) 
END 
GO 
SET ANSI_NULLS OFF 
GO 
SET QUOTED_IDENTIFIER OFF 
GO 
ENABLE TRIGGER [TrgDBChangeLog] ON DATABASE 

 

clip_image004

3. Script to test process and check Audit records

-- Test table creation 
use SQLKnowledge 
create table test (fname varchar(10), lname varchar(10)) 
 
-- Test procedure creation 
create proc test1 
as 
select * from msdb.dbo.DatabaseChangeLog 
 
-- Check table records in msdb database 
select * from msdb.dbo.DatabaseChangeLog 
 

clip_image006

Please leave a comment, if you have questions or need suggestions

How long does a SP stay in the cache?

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 14-04-2010

0

 image As a SQL Database Administrator, developer, designer you must be eager to know how long does a stored procedure execution plan stay in the cache? On what basis and parameters Microsoft SQL Server decide age of an execution plan to reside in cache? The quick answer is, it is based on the cost factor and number of reference to that object in cache. Let’s see how it goes in details:

Once the execution plan is generated for a SP, it stays in the procedure cache. SQL Server 2000 Lazy writer keep looking and throwing out unused plans out of the cache “only when space is needed in cache”.

Each query plan and execution context has an ‘associated cost factor’ that indicates how expensive the structure is to compile. These data structures also have an age field. Each time the object is referenced by a connection, the age field is incremented by the compilation cost factor.

For example, if a query plan of SP has a cost factor of 8 and is referenced twice, its age becomes 16. The lazywriter process periodically scans the list of objects in the procedure cache. The lazywriter decrements the age field of each object by 1 on each scan.

The age of your SP Execution plan is decremented to 0 after 16 scans of the procedure cache, unless another user references the plan. The lazywriter process deallocates an object if these conditions are met:

  • The memory manager requires memory and all available memory is currently in use.
  • The age field for the object is 0.
  • The object is not currently referenced by a connection.

The same way, certain changes in a database can cause an execution plan to be either inefficient or invalid, like below:

  • Any structural changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • New distribution statistics generated either explicitly from a statement such as UPDATE STATISTICS or automatically.
  • Dropping an index used by the execution plan.
  • An explicit call to sp_recompile.
  • Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.

You can read more from SQL 2000 BOL: Topic under “Lazy Writer”, ‘Freeing and Writing Buffer Pages’ OR online at: Link

CHECKPOINT

CHECKPOINT Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages modified after entered into the buffer cache, but the modifications have not yet been written to disk.

SQL Server Buffer Mgr: Lazy Writes/Sec:

This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space.

Below are some documented and undocumented DBCC commands available in SQL Server 2000 to deal and find more information about SQL Server cache.

To monitor the cache:

DBCC SQLPERF (LRUSTATS)
DBCC CACHESTATS
DBCC MEMORYSTATUS
DBCC PROCCACHE

To clean the cache:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Hopefully I have not put too many new questions in your mind. But if I have, feel free to post your comments!

List of Undocumented DBCC command..

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 10-03-2010

0

List of Undocumented DBCC command..

DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name)

DBCC addinstance (objectname, instancename)

DBCC adduserobject (name)

DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

DBCC balancefactor (variance_percent)

DBCC bufcount [(number_of_buffers)]

DBCC buffer ( {‘dbname’ | dbid} [, objid [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])

DBCC bytes ( startaddress, length )

DBCC cachestats

DBCC callfulltext

DBCC checkalloc [(‘database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

DBCC checkcatalog [(‘database_name’)] [WITH NO_INFOMSGS]

DBCC checkconstraints [( ‘tab_name’ | tab_id | ‘constraint_name’ | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]

DBCC checkdb [(‘database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, DBCC TABLOCK]

DBCC checkdbts (dbid, newTimestamp)]

DBCC checkfilegroup [( [ {‘filegroup_name’ | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC checkident (‘table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )

DBCC checkprimaryfile ( {‘FileName’} [, opt={0|1|2|3} ])

DBCC checktable (‘table_name'[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC cleantable (‘database_name’|database_id, ‘table_name’|table_id, [batch_size])

DBCC cacheprofile [( {actionid} [, bucketid])

DBCC clearspacecaches (‘database_name’|database_id, ‘table_name’|table_id, ‘index_name’|index_id)

DBCC collectstats (on | off)

DBCC concurrencyviolation (reset | display | startlog | stoplog)

DBCC config

DBCC cursorstats ([spid [,’clear’]])

DBCC dbinfo [(‘dbname’)]

DBCC dbrecover (dbname [, IgnoreErrors])

DBCC dbreindex (‘table_name’ [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]

DBCC dbreindexall (db_name/db_id, type_bitmap)

DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

DBCC dbtable [({‘dbname’ | dbid})]

DBCC debugbreak

DBCC deleteinstance (objectname, instancename)

DBCC des [( {‘dbname’ | dbid} [, {‘objname’ | objid} ])]

DBCC detachdb [( ‘dbname’ )]

DBCC dropcleanbuffers

DBCC dropextendedproc (function_name)

DBCC dropuserobject (‘object_name’)

DBCC dumptrigger ({‘BREAK’, {0 | 1}} | ‘DISPLAY’ | {‘SET’, exception_number} | {‘CLEAR’, exception_number})

DBCC errorlog

DBCC extentinfo [({‘database_name’| dbid | 0} [, {‘table_name’ | table_id} [, {‘index_name’ | index_id | -1}]])]

DBCC fileheader [( {‘dbname’ | dbid} [, fileid])

DBCC fixallocation [({‘ADD’ | ‘REMOVE’}, {‘PAGE’ | ‘SINGLEPAGE’ | ‘EXTENT’ | ‘MIXEDEXTENT’}, filenum, pagenum [, objectid, indid])

DBCC flush (‘data’ | ‘log’, dbid)

DBCC flushprocindb (database)

DBCC free dll_name (FREE)

DBCC freeproccache

DBCC freeze_io (db)

DBCC getvalue (name)

DBCC help (‘dbcc_command’ | ‘?’)

DBCC icecapquery (‘dbname’, stored_proc_name [, #_times_to_icecap  (-1 infinite, 0 turns off)]) Use ‘dbcc icecapquery (printlist)’ to see list of SP’s to profile. Use ‘dbcc icecapquery (icecapall)’ to profile all SP’s.

DBCC incrementinstance (objectname, countername, instancename, value)

DBCC ind ( { ‘dbname’ | dbid }, { ‘objname’ | objid }, { indid | 0 | -1 | -2 } )

DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})

DBCC inputbuffer (spid)

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { ‘dbname’ | dbid | 0 | -1 }, { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

DBCC latch ( address [, ‘owners’] [, ‘stackdumps’])

DBCC lock ([{‘DUMPTABLE’ | ‘DUMPSTATS’ | ‘RESETSTATS’ | ‘HASH’}]|[{‘STALLREPORTTHESHOLD’, stallthreshold}])

DBCC lockobjectschema (‘object_name’)

DBCC log ([dbid[,{0|1|2|3|4}[,[‘lsn’,'[0x]x:y:z’]|[‘numrecs’,num]|[‘xdesid’,’x:y’]|[‘extent’,’x:y’]|[‘pageid’,’x:y’]|[‘objid’,{x,’y’}]|[‘logrecs’,{‘lop’|op}…]|[‘output’,x,[‘filename’,’x’]]…]]])

DBCC loginfo [({‘database_name’ | dbid})]

DBCC matview ({‘PERSIST’ | ‘ENDPERSIST’ | ‘FREE’ | ‘USE’ | ‘ENDUSE’})

DBCC memobjlist [(memory object)]

DBCC memorymap

DBCC memorystatus

DBCC memospy

DBCC memusage ([IDS | NAMES], [Number of rows to output])

DBCC monitorevents (‘sink’ [, ‘filter-expression’])

DBCC newalloc – please use checkalloc instead

DBCC no_textptr (table_id , max_inline)

DBCC opentran [({‘dbname’| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]

DBCC outputbuffer (spid)

DBCC page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])

DBCC perflog

DBCC perfmon

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}, targetfile, targetpg, order={1|0})

DBCC pintable (database_id, table_id)

DBCC procbuf [({‘dbname’ | dbid}[, {‘objname’ | objid}[, nbufs[, printopt = { 0 | 1 } ]]] )]

DBCC proccache

DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

DBCC readpage ({ dbid, ‘dbname’ }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])

DBCC rebuild_log (dbname [, filename])

DBCC renamecolumn (object_name, old_name, new_name)

DBCC resource

DBCC row_lock (dbid, tableid, set) – Not Needed

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC ruleon (  rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC setcpuweight (weight)

DBCC setinstance (objectname, countername, instancename, value)

DBCC setioweight (weight)

DBCC show_statistics (‘table_name’, ‘target_name’)

DBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])

DBCC showdbaffinity

DBCC showfilestats [(file_num)]

DBCC showoffrules

DBCC showonrules

DBCC showtableaffinity (table)

DBCC showtext (‘dbname’, {textpointer | {fileid, pageid, slotid [,option]}})

DBCC showweights

DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]])

DBCC shrinkdb  is no longer supported. Please use shrinkdatabase instead

DBCC shrinkfile ({fileid | ‘filename’}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])

DBCC sqlmgrstats

DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} | {THREADS} | {LOGSPACE})

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, ‘THREADID’}] )]

DBCC tab ( dbid, objid )

DBCC tape_control {‘query’ | ‘release’}[,(‘\\.\tape<n>’)]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({‘database_name’|database_id}[, ‘FULL’ | FAST] )]

DBCC textalloc ({‘table_name’|table_id}[, ‘FULL’ | FAST])

DBCC thaw_io (db)

DBCC traceoff [( tracenum [, tracenum … ] )]

DBCC traceon [( tracenum [, tracenum … ] )]

DBCC tracestatus (trace# [, …trace#])

DBCC unpintable (dbid, table_id)

DBCC updateusage ({‘database_name’| 0} [, ‘table_name’ [, index_id]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]

DBCC upgradedb (db)

DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC useroptions

DBCC wakeup (spid)

DBCC writepage ({ dbid, ‘dbname’ }, fileid, pageid, offset, length, data)

© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com