:::: MENU ::::

Posts Categorized / System Stored Procedures

  • May 19 / 2009
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures

Get list of all databases through SP_DATABASES

While planning to drop many of my test databases, i was tired to write name of all such databases. While looking for any stored procedure to get list of all databases on my SQL Server instance, i found SP_DATABASES. It provided me complete list of databases along with over all size statistics for each.


Get list of all databses on SQL Server instance
From here is copied names of required databases to be dropped.

  • Apr 16 / 2009
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures, T-SQL Scripts

get number of rows and size of a table through sp_spaceUsed

sp_spaceused is system stored procedure. It provides number of rows and space used information. For example to get space used information for [Sales.SalesOrderDetail] in AdventureWorks database.

USE AdventureWorks
EXEC sp_spaceused [Sales.SalesOrderDetail]

As it returns 6 columns.

  • [Name] is name of table that we provided as parameter
  • [rows] is number of rows in that table
  • [reserved] is total space that is allocated to this table and its indexes
  • [data] is amount of space used by data in the table
  • [index_Size] is amount of space used by indexes in the table
  • [unused] is amount of space that is allocated but currently not in use by table

data + index_size + unused = reserved
If no parameter is provided to sp_spaceused, it will generate space statistics for whole database overall. To make more use we may use sp_spaceused with sp_MSforEachDB or sp_MSforEachTable.

  • Nov 12 / 2008
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures

Read SQL Server Error log info through T-SQL

SQL Server Management studio (SSMS) and SQL Server Enterprise Manager provide a good interface for listing error log file archives and reading each error log archive individually. But I have noticed that when log file entries grow in number. SSMS/SSEM take some time to load the log file content. So how to get list of error log archives through T-SQL and how to list log contents of an error log file through T-SQL. Following two commands will help you in this regard.

exec sp_enumerrorlogs

Error log Archives

And to read log content of log archive file # 1, I have used following command.

exec sp_readerrorlog 1

The parameter 1 is to point the log archive file number. You may give file number from 0 to 6 as provided in list.

Error log Archives contents

  • Nov 12 / 2008
  • 0
dbDigger, Mirroring, Monitoring and Analysis, System Stored Procedures

Monitor DB mirroring session in SQL Server 2008

Question: In SQL Server 2008, if you want to use a stored procedure to monitor the status of your database mirroring session, which one would you use?

Answer: sp_dbmmonitorresults

Explanation: The sp_dbmmonitorresults procedure will return the current status of your database that is being mirrored.

Ref: sp_dbmmonitorresults

Note: The Question is taken from SQLServercentral.com.

  • Nov 12 / 2008
  • 0
Backup and Restore, dbDigger, System Stored Procedures, T-SQL Scripts

Backup all SQL Server databases (User and system) through T-SQL

There may be some situation where you are in need to backup all SQL Server databases. If you have a number of SQL Server databases (in majority of cases) then it would be a very good idea to use T-SQL command that will backup all SQL Server databases. Do not forget to calculate available space in drive which you are going to provide as backup destination. Use following T-SQL command to backup all SQL Server databases through a single command


We have used system stored procedure SP_MSforeachdb to invoke the given command for all databases. T-SQL command for SQL Server backup is provided next to SP_MSforeachdb as parameter. ? in the above command specifies the database name which is going to be handled by the stored procedure SP_MSforeachdb. It will provide the name of databases automatically one by one. It is important to note that above command will backup all system databases along with user databases

Consult us to explore the Databases. Contact us