Returning the parameters for a specified stored procedure or function

 

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>; GO SELECT SCHEMA_NAME(schema_id) AS schema_name     ,o.name AS object_name     ,o.type_desc     ,p.parameter_id     ,p.name AS parameter_name     ,TYPE_NAME(p.user_type_id) AS parameter_type     ,p.max_length     ,p.precision     ,p.scale     ,p.is_output FROM sys.objects AS o INNER JOIN sys.parameters AS p ON o.object_id = p.object_id WHERE o.object_id = OBJECT_ID('<schema_name.object_name>') ORDER BY schema_name, o.object_name, p.parameter_id; GO

 

Reference: http://msdn.microsoft.com/en-us/library/ms190324.aspx

List database objects modified in the last ‘N’ days

 

Before you run the following query, replace <database_name> and <n_days> with valid values.

 

USE <database_name>; GO SELECT name AS object_name   ,SCHEMA_NAME(schema_id) AS schema_name   ,type_desc   ,create_date   ,modify_date FROM sys.objects WHERE modify_date > GETDATE() - <n_days> ORDER BY modify_date; GO

 

Source: http://msdn.microsoft.com/en-us/library/ms190324.aspx

System database msdb size in huge! How to purge MSDB Backup and Restore History?

 

On one of my SQL Server 2008 Instance, the msdb database sizes around 4 GB.  I decided to find out the which table / index occupies major junk on this. I have used query below;

--- Query 1, if there is partition SELECT TOP 10 SERVERNAME=@@SERVERNAME,DB_NAME=DB_NAME(),TABLE_NAME=OBJECT_NAME(I.ID),INDEX_NAME=I.NAME,INDID,USED, ROWS, SIZE_N_MB = ROUND((USED*8.0/1024.0),2), ROWMODCTR,STATISTICDT=STATS_DATE(I.ID,INDID) FROM SYSINDEXES I, SYSOBJECTS O WHERE I.ID = O.ID AND INDID IN ( 0,1) AND XTYPE = 'U' ORDER BY USED DESC

--- Query 2 If there are partitions SELECT object_name(i.object_id) as objectName, i.[name] as indexName, sum(a.total_pages) as totalPages, sum(a.used_pages) as usedPages, sum(a.data_pages) as dataPages, (sum(a.total_pages) * 8) / 1024 as totalSpaceMB, (sum(a.used_pages) * 8) / 1024 as usedSpaceMB, (sum(a.data_pages) * 8) / 1024 as dataSpaceMB FROM sys.indexes i INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id GROUP BY i.object_id, i.index_id, i.[name] ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)

The output resulted;

The following 5 big tables takes up 88% of the total database size of 3.8 GB. These 5 tables occupy 3.3 GB on the msdb database.

Query 1: Output

TABLE_NAME	INDEX_NAME	INDID	USED	ROWS	SIZE_N_MB	ROWMODCTR	STATISTICDT
backupfile	PK__backupfi__57D1800A17C286CF	1	233057	4438738	1820.76	2646909	2/14/2011 15:57
backupset	PK__backupse__21F79AAB0E391C95	1	115791	1489763	904.62	228112	6/19/2011 6:00
backupmediafamily	PK__backupme__0C13C86F0880433F	1	50738	1489616	396.39	23386	7/26/2011 7:00
backupfilegroup	PK__backupfi__760CD67A12FDD1B2	1	43284	2967333	338.16	1775145	2/14/2011 15:57
backupmediaset	PK__backupme__DAC69E4D04AFB25B	1	29250	1489616	228.52	44462	7/22/2011 15:43
sysjobhistory	clust	1	3553	33177	27.76	2428	7/25/2011 8:14

Query 2: Output

objectName	indexName	totalPages	usedPages	dataPages	totalSpaceMB	usedSpaceMB	dataSpaceMB	Rows
backupfile	PK__backupfi__57D1800A17C286CF	233073	233043	231653	1820	1820	1809	4438738
backupset	PK__backupse__21F79AAB0E391C95	100217	100200	99826	782	782	779	1489763
backupfilegroup	PK__backupfi__760CD67A12FDD1B2	43289	43283	43077	338	338	336	1489616
backupmediafamily	PK__backupme__0C13C86F0880433F	39473	39459	39287	308	308	306	2967333
backupmediaset	PK__backupme__DAC69E4D04AFB25B	18737	18728	18654	146	146	145	1489616

Analyze and find out the minimum and maximum date range date available

SELECT COUNT(*) AS 'TotalRecords', MIN(backup_start_date) AS 'MinDate', MAX(backup_start_date) AS 'MaxDate' FROM dbo.backupset

Resolution:
The solution is we have to purge these MSDB Backup and Restore history tables

1. The sp_delete_backuphistory system stored procedure takes a single parameter - a cutoff date. Any data older than the supplied date is purged from the msdb tables.

2. The sp_delete_database_backuphistory  system stored procedure allows you to delete historical backup data for a specific database. Unfortunately, this procedure does not offer the finer option of choosing a cutoff date. It's all or nothing.

3. sp_purge_jobhistory Removes the history records for a job.

4. Try considering creating indexes on the above said 5 tables, if no of rows are huge.
Source: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

use msdb go --backupset Create index IX_backupset_backup_set_id on backupset(backup_set_id) go Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid) go Create index IX_backupset_media_set_id on backupset(media_set_id) go Create index IX_backupset_backup_finish_date on backupset(backup_finish_date) go Create index IX_backupset_backup_start_date on backupset(backup_start_date) go --backupmediaset Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id) go --backupfile Create index IX_backupfile_backup_set_id on backupfile(backup_set_id) go --backupmediafamily Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id) go --restorehistory Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id) go Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id) go --restorefile Create index IX_restorefile_restore_history_id on restorefile(restore_history_id) go --restorefilegroup Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id) go

5. Alternatively, check when the statistics of these tables are last updated. Just run sp_updatestats.  Just try sp_delete_backuphistory  after the statistics are updated. The result is awesome! Very quick response.

Permanent Resolution

6. Further restrict the Job history growth by setting in SQL Agent –> History tab.

7. Also on the SQL Server Maintenance Plan “History Cleanup Task” can be of helpful

How to create scripts for an entire database or specific object in SQL Server 2008?

In this post I would like to address the requirement of;

• How to create scripts for an entire database ?
• How to create scripts limit it to specific objects?
• How to restore SQL Server 2005 database on SQL Server 2000?

SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server.

It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window.

If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.

We can use the Generate and Publish Scripts Wizard to create scripts for transferring a database from one instance of the Database Engine to another. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Azure.

The generated scripts can be run on another instance of the Database Engine or SQL Azure. 

We can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services.

We can create scripts for an entire database, or limit it to specific objects.

What version of SQL Servers are supported on this?

	Source Database	Target Database
SQL Server Instance Version Supported	SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Azure	SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Azure SQL Server 2000
Permission needed	db_ddladmin	db_ddladmin
Pre-existence		The target database must be created at the hosting provider before the source database is published. Publishing overwrites objects in that existing database

Supported Objects for Publishing

The following table lists the objects that can be published and the versions of SQL Server on which they are supported by the Generate and Publish Scripts Wizard.

Database object	SQL Server 2008 R2	SQL Server 2008	SQL Server 2005	SQL Server 2000
Application role	Yes	Yes	Yes	Yes
Assembly	Yes	Yes	Yes	No
CHECK constraint	Yes	Yes	Yes	Yes
CLR (common language runtime) stored procedure1	Yes	Yes	Yes	No
CLR user-defined function	Yes	Yes	Yes	No
Database role	Yes	Yes	Yes	Yes
DEFAULT constraint	Yes	Yes	Yes	Yes
Full-text catalog	Yes	Yes	Yes	Yes
Index	Yes	Yes	Yes	Yes
Rule	Yes	Yes	Yes	Yes
Schema	Yes	Yes	Yes	No
Stored procedure1	Yes	Yes	Yes	Yes
Synonym	Yes	Yes	Yes	Yes
Table	Yes	Yes	Yes	Yes
User2	Yes	Yes	Yes	Yes
User-defined aggregate	Yes	Yes	Yes	No
User-defined data type	Yes	Yes	Yes	Yes
User-defined function	Yes	Yes	Yes	Yes
User-defined table	Yes	Yes	No	No
User-defined type	Yes	Yes	Yes	No
View1	Yes	Yes	Yes	Yes
XML schema collection	Yes	Yes	Yes

1 Published without encryption.
2 Any non-system users that exist in the database are published as Roles.

How to generate SQL Scripts using Generate and Publish Scripts Wizard

We can create Transact-SQL scripts for multiple objects by using the Generate and Publish Scripts Wizard.. You can also generate a script for individual objects or multiple objects by using the Script as menu in Object Explorer.

Use the Generate and Publish Scripts Wizard to create a Transact-SQL script for many objects.
The wizard generates a script of all the objects in a database, or a subset of the objects that you select. The wizard has many options for your scripts, such as whether to include permissions, collation, constraints, and so on.

Step 1:

In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate Scripts. Follow the steps in the wizard to script the database objects.



 

Step 2: On the Choose Objects page, select the objects to be included in the script

Script entire database and all database objects
Click to generate scripts for all objects in the database and to include a script for the database itself.

Step 1.3: On the Set Scripting Options page, select Save scripts to a specific location.

Select Save scripts to a specific location


Summary:



Click Finish.

To generate the Script for the Data on the table:

Set “Script Data: True “

 

Select specific database objects
Click to limit the wizard to generate scripts for only the specific objects in the database that you choose.

For example I have selected Tables Object

 

Within Tables Object, I have chosen HumanResources.Employee Table

 

Select Save scripts to a specific location



Script Wizard Summary
 

View of the Output file generated:

 

References:

Using the Generate and Publish Scripts Wizard

How to: Generate a Script (SQL Server Management Studio)

How to find the status of Backup / Restore in SQL Server 2000

To know the status of backup or restore in SQL Server 2000 ;

1. Find the SPID which is restoring the DB using sp_who2 or from sysprocesses

2. Execute dbcc outputbuffer(SPID)

From the output get the %completed status:

SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)

 

Error

• Msg 170, Level 15, State 1, Server MS, Line 1

Line 1: Incorrect syntax near '-'.

The sqlsp.log file indicates:

Process Exit Code: (0)

Error running script: sp_vupgrade_replication (1)

Action CleanUpInstall:

Installation Failed.

Resolution:
1. Check if distribution database exist though the Replication is dropped
2. If so drop the distribution database
3. To drop the distribution database by over - restoring of some user database
4. Perform the patching / SQL SP again.

How to remove the subscription from Publication in SQL Server Replication

 

1. On SQL Server 2000 connect to Enterprise Manager
On SQL Server 2005 / 2008 connect to SQL Server Management studio

2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication

3. Right click on the Publication

4. Select the Subscription tab

5. Select the Subscriber and click Delete button, that’s it

6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.

7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.

SQL Server 2000 Service account privileges

For SQL Server 2000, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, then the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) must have these user rights:

* Act as Part of the Operating System = SeTcbPrivilege
* Bypass Traverse Checking = SeChangeNotify
* Lock Pages In Memory = SeLockMemory
* Log on as a Batch Job = SeBatchLogonRight
* Log on as a Service = SeServiceLogonRight
* Replace a Process Level Token = SeAssignPrimaryTokenPrivilege

SQLDebugger account

SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

Removing of the SQLDebugger account in SQL 2000

Free SQL Server tools that might make your life a little easier

check out on below link for free SQL Server tools that might make your life a little easier
http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/free-sql-server-tools-that-might-make-your-life-a.aspx

Getting Detail of SQL Server from Registry

From a Command prompt you can run the following REG instructions

To Get remote server details:
- Add \\servername\... before HKLM to get remote machine details


reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CurrentVersion


For SQL Server 2000
-------------------
reg query "HKLM\Software\Microsoft\Microsoft SQL Server" /v InstalledInstances



reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\MSSQLLicenseInfo\MSSQL8.00" /v ConcurrentLimit

-- For each instance, replace MSSQLServer with 80\InstanceName from in the following
reg query HKLM\Software\Microsoft\MSSQLServer\Setup /v Edition
reg query HKLM\Software\Microsoft\MSSQLServer\Cluster /v ClusterName
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion /v CSDVersion
reg query HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp /v TcpPort


----- Using Registy Server Listing ---
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" ServerN (where N represents the server number )

reg query "HKLM\Software\Microsoft\Microsoft SQL Server\80\Tools\Client\PrefServers" UserN (where N represents the default login corresponding the ServerN )


For SQL Server 2005
-------------------
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names"
- Subkeys represent a different SQL Server Product (SQL=SQL Server, RS=Reporting Server, etc)


- Replace MSSQL.1 with each of the keys from "Inteance Names" on below
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Setup" /v Edition
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\CurrentVersion" /v CurrentVersion
reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll" /v TcpPort

Bring the database which is in suspect

Steps intend to do to get the database online:

1. We then execute

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
- So that it would allow us to make changes directly to the SYSTEM tables(this option is used in extreme circumstances).

3. The following command lets us put the database in an emergency mode

Update SYSDATABASES set status = 32768 where name = 'SampleDB'
- In Emergency Mode it would allow us to perform certain actions which would not be possible in either Normal or Suspect Modes.


4. Having put the database in Emergency Mode we can then create a new log for the database since the current one can corrupt or inaccessible.



DBCC REBUILD_LOG('SampleDB', ‘C:\Data\SampleDB_newlog.ldf’)
- After this we can check the current files of the database using sp_helpfile command after changing the database context in the Query Analyzer to the affected database

5. We then check for consistency issues on the database by executing:
DBCC CHECKDB('SampleDB')
Go
- If the above command fails it usually recommends the next available option to use. You have to clearly understand the option before you try it since it could result in data-loss

6. Turn off the option to make direct changes to the SYSTEM tables using:
sp_configure 'allow updates', 0
reconfigure with override
Go

7. Then proceeded to take a complete database backup of the database.

Query to find computed columns

Query to find computed columns:

SELECT TABLE_NAME= OBJ.NAME,COLUMN_NAME=COL.NAME,FORMULA=CMT.TEXT
FROM SYSCOLUMNS COL, SYSOBJECTS OBJ, SYSCOMMENTS CMT
WHERE COL.ID = OBJ.ID
AND OBJ.ID = CMT.ID
AND COL.COLID = CMT.NUMBER
AND ISCOMPUTED = 1
AND OBJ.TYPE = 'U'

How to reassign the DTS package owner

In most cases, it doesn't really matter who owns the package - who is


executing it is more important. However, there is an undocumented


stored procedure called msdb..sp_reassign_dtspackageowner, which you


can use.

sp_reassign_dtspackageowner

powered by performancing firefox