:::: MENU ::::

Posts Categorized / System Stored Procedures

  • Nov 11 / 2008
  • 0
DBCC Commands, dbDigger, System Stored Procedures

To perform a T-SQL task for all databases in SQL Server

Sometimes we need to perform a specific task on all databases. Like to run a specific SP on all database. In that case if you have a number of databases (likely in most of cases) then an automated method is required rather than to perform the task on each database one by one. In SQL Server T-SQL we have a system stored procedure (sp) sp_msforeachdb for this purpose. For example if you want to check database integrity of all databases, then you may run the following command.

 
EXEC sp_msforeachdb 'DBCC CHECKDB()'  
 GO  

The command DBCC CHECKDB() will be executed for all databases one by one.

  • Nov 05 / 2008
  • 0
dbDigger, Security and Permissions, System Stored Procedures

Cross-Database Ownership Chaining Enabled (DB_CHAINING) in SQL Server

Cross-Database Ownership Chaining Enabled is a security feature that controls database access by external resources, such as objects from another database. When set to TRUE in SQL Server Management Studio or ON when using the ALTER DATABASE command, the database can be the source or target of a cross-database ownership chain. Setting the Cross-Database Ownership Chaining to FALSE prevents participation in cross-database ownership chaining.
This option is effective only when the instance wide cross db ownership chaining server option is set to 0.
When the cross db ownership chaining option is enabled via SQL Server Management Studio or set to 1 via the sp_configure stored procedure, this option is ignored and all databases in the server can participate in cross database ownership chaining.

  • Oct 06 / 2008
  • 0
dbDigger, SQL Server Agent scheduled Jobs, System Stored Procedures, T-SQL Scripts

Create new SQL Agent logs archive files without restart in SQL Server 2005

Just like SQL Server Logs Files, SQL Agent logs archive files are also processed during restart of SQL Server agent. A new SQL Agent file is created and oldest one is replaced if maximum count is exceeded. In situations where production servers remain online with critical jobs scheduled, SQL Agent is not restarted for days and even months. So a way is required to process the SQL Agent logs files without restart. Following system stored procedure will do it for you in SQL Server 2005.

 
USE MSDB  
 GO  
 EXEC sp_cycle_agent_errorlog  
 GO  

As a result new logs archive file for SQL Agent logs will be created, just as it would be through restart of SQL agent Service.

  • Oct 06 / 2008
  • 0
dbDigger, System Stored Procedures, T-SQL Scripts, Transaction log files

Recycle SQL Server logs archives without restart

Every time SQL Server restarts, a new error log file is created. If file count is going to be increase the maximum count specified, then oldest file is replaced by new one else just a new file is added. This process helps to manage the size of logs file. But in situations where production servers remain online for days and months without any restart, the logs file may be heavily populated. This causes difficulty while loading the file with more log records for analysis.
So a mechanism is required to process the log files without SQL Server restart. Following system stored procedure will do it for you

USE MASTER  
 EXEC sp_cycle_errorlog  
 GO  

As a result a new logs file will be craeted and oldest will be replaced if maximum count is going to be exceed.

  • Sep 02 / 2008
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis, System Stored Procedures

Check size and disk allocation for a database

Space is an important element to be considered. A DBA has to keep track of space in all aspects and it is good practice to keep an eye on all trends that your DB is going on in case of space. Here are some use full T-SQL commands that will help you in space analysis and tracking.

DBCC showfilestats
This DBCC command will show use full information regarding to data file of current database.

sp_spaceused
This command will provide use full information about total size, used size, unused size, index size and data size of current database.

sp_databases
This use full command will provide you size information of all databases. The size shown is combined size of data and log files.

DBCC CHECKALLOC
This use full DBCC command will give you a very detailed view of disk allocation for current database. You will find every information here about storage allocation.

DBCC CHECKALLOC
sp_helpdb adventureWorks
This use full command will provide value able info about mentioned database. It gives other usefull information along with size of data and log files.

helpdb

  • Aug 20 / 2008
  • 4
dbDigger, Monitoring and Analysis, Security and Permissions, System Stored Procedures, T-SQL Scripts

List permissions on SQL Server 2000 objects through sp_helpProtect()

I was required to list all permissions on a DB or table. Although this can be done through EM/SSMS but the analysis of permissions is more efficient through T-SQL than by any other means. I have found some very use full T-SQL stored procedure sp_helpProtect in this regard. It lists users permissions for an object.
General syntax for sp_helpProtect is

 
sp_helprotect [ [ @name = ] 'object_statement' ]  
 [ , [ @username = ] 'security_account' ]  
 [ , [ @grantorname = ] 'grantor' ]  
 [ , [ @permissionarea = ] 'type' ]  

You may use it in following ways

 
--List all user permissions of all Database objects  
 sp_helprotect  
 GO  
 -- List all user permissions of a table  
 sp_helprotect 'tableNme'  
 GO  
 -- List all user permissions of stored procedure  
 EXEC sp_helprotect 'spName'  
 GO  
 -- List all user permissions of sp granted by dbo  
 sp_helprotect 'spName', NULL,dbo  
 GO  
 -- List all Object type user permissions  
 sp_helprotect NULL, NULL,NULL,'o'  
 GO  
 -- List all statement type user permissions  
 sp_helprotect NULL, NULL,NULL,'s'  
 GO  
 -- List all permissions for a user  
 sp_helprotect NULL,'user'  
 GO  

Note: Basically sp_helprotect is for SQL Server 2000.For SQL Server 2005 and later sp_helprotect has no information regarding securables that were introduced in SQL Server 2005. Read here about List the permissions on SQL Server 2005 objects by using sys.database_permissions and fn_builtin_permissions instead.

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