10 Most Important handy system stored Procedures for DBA

10 Most Important handy system stored Procedures for DBA

This blog contains the most important system stored procedures which are supported by SQL Server and are used on a daily basis by DBA. These stored procedures are used commonly for maintenance of the server instance.

  1. Sp_helpdb

It provides information about the databases as mentioned in picture below. If any specific database is specified with this command then the information will be displayed about only that database.

sp_helpdb

  1. Sp_who2 

It provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.

sp_who2

  1. Sp_spaceused

It displays the disk space reserved and used by the whole database (any specific database).

sp_spaceused

  1. Sp_databases

It shows the database information for all databases on an instance of  a server as below.

sp_databases

  1. Sp_server_info

This stored procedure returns a list of attribute names and matching values for SQL Server.

sp_server_info

  1. Sp_tables

It displays the information about the tables, name and type etc.

sp_tables

  1. sp_lock

It reports information about locks.

sp_lock

  1. sp_monitor

It displays statistics about Microsoft SQL Server.

sp_monitor

  1. sp_updatestats

It runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

  1. Sp_configure

It displays or changes global configuration settings for the current server.

sp_configure

Remarks: For more information and more stored procedures, please review Microsoft Blog. It is recommended to execute these stored procedures and see the results in detail which will be beneficial.

 

 

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration, Sql Server and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

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