|
|
-
|
|
When a database is dropped, the jobs remain active in the SQL Server Agent and if a schedule is associated to the job, failures would be reported whenever the jobs are automatically executed. As part of the standard process that I follow whenever I drop a database, I run the following script to identify SQL Server Agent jobs which are no longer targeted to any database....
|
|
-
|
|
updated version of CompareXml...
|
|
-
|
|
It finds the Indexes on Database which need to be De-fragmented.
You just need to replace 'database_name' with your database name.
Include this Stored Procedure in to your Scheduled JOB, schedule it whenever you want.
...
|
|
-
|
|
We recently ran into this interesting problem, to verify whether the IP Address of the user is between the range of IP Addresses or not. Below script will return 1, If IP Address is between the range, otherwise it return 0;...
|
|
-
|
|
Many IT departments use pre-created ghost images to quickly setup development and quality assurance environments. When they change the name of the server after restoring the image, the SQL Server instance name remains unchanged. Here's a script to change the SQL Server local instance name....
|
|
-
|
|
Have you ever wondered when your index was last rebuilt/reorganized? Here are two versions - using the Catalog view sys.indexes that would help you to identify the last date/time when the index statistics were updated, the number & percentage of rows changed during the last operation and the associated index name....
|
|
-
|
|
SQL Server provides a way to verify a database backup using the T-SQL command - RESTORE. This verification ensures that all backup sets are readable, checksum verification (if backup is checksum protected) and checking for sufficient space on the destination drives....
|
|
-
|
|
Additional rows are added to the backup and restore history tables after each backup or restore operation is performed. Hence, it is recommended that these history tables are periodically cleaned up. These SQL Server system stored procedures help you to cleanup the backup/restore history tables which reside in the msdb database. (You may also want to refer: http://beyondrelational.com/modules/2/blogs/77/posts/11371/sql-server-administration-cleanup-database-backup-history.aspx)...
|
|
-
|
|
In a rare case when you need to control the execution sequence of database triggers, you can use the sp_settriggerorder statement to do so. However, the recommended best practice always remains that you should have business logic dependent upon execution sequence within stored procedures....
|
|
-
|
|
How do you identify system and user databases programatically? How do you hide the "master" database from the user when running an installer? Use these scripts to either list system or user databases as requried....
|
|