:::: 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.

SP_DATABASES
GO

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
GO
EXEC sp_spaceused [Sales.SalesOrderDetail]
GO

sp_spaceused
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

SP_MSforeachdb
'BACKUP DATABASE ? TO DISK = ''C:backup?.bak'' WITH INIT'

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

Pages:1234
Consult us to explore the Databases. Contact us