Database Engine Stored Procedures 2008


SQL DBA hand tool i.e Database Engine Stored Procedures in SQL Server 2008:-

sp_add_data_file_recover_suspect_db sp_droptype
sp_add_log_file_recover_suspect_db sp_executesql
sp_addextendedproc sp_getapplock
sp_addextendedproperty sp_getbindtoken
sp_addmessage sp_help
sp_addtype sp_helpconstraint
sp_addumpdevice sp_helpdb
sp_altermessage sp_helpdevice
sp_attach_db sp_helpextendedproc
sp_attach_single_file_db sp_helpfile
sp_autostats sp_helpfilegroup
sp_bindefault sp_helpindex
sp_bindrule sp_helplanguage
sp_bindsession sp_helpserver
sp_certify_removable sp_helpsort
sp_configure sp_helpstats
sp_control_plan_guide sp_helptext
sp_create_plan_guide sp_helptrigger
sp_create_plan_guide_from_handle sp_indexoption
sp_create_removable sp_invalidate_textptr
sp_createstats sp_lock
sp_cycle_errorlog sp_monitor
sp_datatype_info sp_procoption
sp_dbcmptlevel sp_recompile
sp_dbmmonitoraddmonitoring sp_refreshview
sp_dbmmonitorchangealert sp_releaseapplock
sp_dbmmonitorchangemonitoring sp_rename
sp_dbmmonitordropalert sp_renamedb
sp_dbmmonitordropmonitoring sp_resetstatus
sp_dbmmonitorhelpalert sp_serveroption
sp_dbmmonitorhelpmonitoring sp_setnetname
sp_dbmmonitorresults sp_settriggerorder
sp_dboption sp_spaceused
sp_dbremove sp_tableoption
sp_delete_backuphistory sp_unbindefault
sp_depends sp_unbindrule
sp_detach_db sp_updateextendedproperty
sp_dropdevice sp_updatestats
sp_dropextendedproc sp_validname
sp_dropextendedproperty sp_who
sp_dropmessage

 

* DBCC SQLPERF(logspace)

DBCC SQLPERF -logspace – is an absolutely functional command if you are only interested in consumption of your database log files. It provides the cumulative size for each log file for each database on the SQL Server instance as well as the amount of space consumed -as a percentage of total log file size -. A drawback is the fact that the results are an aggregate for the database.

logspace can be 1 of the value below:
UMSStats – SQL thread management
WaitStats – resources, wait types
IOStats – outstanding reads & writes (note: always zeros)
RAStats – read ahead activity (note: always returns zeros)
Threads – I/O / CPU / memory usage per thread
SpinLockStats – statistics on spinlocks
UMSSpinStats – statistics on UMS
NetStats – ODS statistics
LRUStats – LRU-MRU chain statistics (Note: free page scan always zero).

* SQL @@ROWCOUNT

Returns the number of rows affected by the last statement. It will let you to do a checking on the record you updated.

If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

Example
USE DB2008;
GO
UPDATE User
SET JobTitle = ‘Manager’
WHERE UserID = ‘u10021’
IF @@ROWCOUNT = 0
PRINT ‘Warning: No rows were updated’;
GO

.

Khan – MCTS www.addarr.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s