SQLServerCentral.com Content tagged Maintenance

Custom Maintenance Plans - the T-SQL way

Thu, 16 May 2013 06:00:00 UT

The non-SSIS/GUI method I use to deploy standardized Database Maintenance Plans.

Check Candidate Columns for FOREIGN KEY Constraints

Tue, 23 Apr 2013 06:00:00 UT

Reports table columns that are not foreign keys but that perhaps should be.

Identify Obsolete Indexes

Fri, 08 Mar 2013 07:00:00 UT

Drop indexes no longer being used.

12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

Objects Referenced and Referencing

Thu, 07 Mar 2013 07:00:00 UT

This script reports objects that are referenced by and that reference a given database object. When a column name is specified, it also includes results showing how that column is used.

Script to Rebuild/Reorganise Indexes

Tue, 19 Feb 2013 07:00:00 UT

A script to get the fragmentation of indexes in a database and then rebuild or reorganise accordingly

12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

Check Candidate Columns for NOT NULL Constraints

Wed, 23 Jan 2013 07:00:00 UT

This script reports table columns that allow NULL values but that currently have no NULL values, making them candidates for adding a NOT NULL constraint.

2008 Index Fragmentation Maintenance

Fri, 11 Jan 2013 07:00:00 UT

Just a stab at automating index maintainenance in SQL Server 2008

Defragmenting and rebuilding indexes

Wed, 09 Jan 2013 07:00:00 UT

Following code snippet will selectively rebuild indexes that have been thrashed after a big indert

The Ultimate Missing Index Finder

Mon, 07 Jan 2013 07:00:00 UT

Reports stats on what the query optimizer records in the DMVs as missing indexes and what it says the cost savings will be if they were present. Can limit by table / schema name patterns.

The Ultimate Index Usage Reporter

Thu, 27 Dec 2012 07:00:00 UT

Reports index stats, index size+rows, member seek + include columns as two comma separated output columns, and index usage stats for one or more tables and/or schemas.

Get Scheduled Jobs from Multiple Servers

Thu, 06 Dec 2012 07:00:00 UT

Script to loop thru multiple linked server and get agent scheduled jobs for the next XX hours.

Find Mismatched Views and Stored Procs

Mon, 03 Sep 2012 06:00:00 UT

Find missing and mismatched views and stored procs between 2 Databases

Get alerts within 15 seconds of SQL Server issues
SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them. Start monitoring with a free trial.

2008 Index Rebuild using DMV

Thu, 09 Aug 2012 06:00:00 UT

Index Maintenance procedure with HTML Reporting capabilities. Please refer to attached script for detailed information.

Finding Unused Database Roles

Tue, 24 Jul 2012 06:00:00 UT

This is a data quality script that locates unused Database Roles for maintenance purposes

Developing Low-Maintenance Databases

Mon, 23 Jul 2012 06:00:00 UT

Alex Kuznetsov's team of developers are geared to doing rapid development of database applications in a busy corporate setting, yet take considerable time over meticulous database design, extensive constraints, automated tests, error logs, and defensive coding. Why? Because it cuts down on the subsequent need for maintenance.

Set Database to Single user

Fri, 13 Jul 2012 06:00:00 UT

How to set the database to a single user mode

Total Space Used for all databases per disk

Mon, 21 May 2012 06:00:00 UT

Tells you how much the databases are using the space on disk taking in account the free space at the end of the database.

Auto compression all indexs on all online db

Wed, 11 Apr 2012 06:00:00 UT

Auto compression of index, if index includes > 1 million rows.

Move Physical Files

Fri, 30 Mar 2012 06:00:00 UT

Script to move physical files to a new location

12 essential tools for database professionals
The SQL Developer Bundle contains 12 tools designed with the SQL Server developer and DBA in mind. Try it now.

Identify Large Tables

Mon, 26 Mar 2012 06:00:00 UT

Reports on physical size of tables in your database to identify largest.

Script foreign keys

Tue, 10 Jan 2012 07:00:00 UT

Using this script you can create a list of sql commands for foreign keys manipulation.

SQL Server 2005 Index Maintenance

Wed, 26 Oct 2011 06:00:00 UT

Index maintenance procedure using rebuild or reorganized based on fragementation level.

Next grow for all databases per disk

Tue, 11 Oct 2011 06:00:00 UT

This script lets you to know how much disk space will be left after the next database grow taking in account the free space left in the database.

Script a Database Role

Thu, 08 Sep 2011 06:00:00 UT

Script one or all roles in a database. Includes users, obect permissions and column permissions.

List all tables without LOB columns

Tue, 30 Aug 2011 06:00:00 UT

Lists all the tables in a database that do not contain any LOB columns(text, ntext, image, varchar (max), nvarchar (max), varbinary (max) and xml). Useful when rebuilding indexes online.

Automate drive space monitoring for all production servers

Tue, 19 Apr 2011 06:00:00 UT

Monitoring space in production server is one of the top priority tasks for a DBA. This script will automate it

Reindex all indexes online or offline depending on day of week.

Mon, 18 Apr 2011 06:00:00 UT

This script will reindex (online) any index eligible to be reindexed online. Accepts a day of week parameter for full offline reindexing.

Automatic Statistics Update Slows Down SQL Server 2005

Fri, 25 Feb 2011 07:00:00 UT

I have a database which has several tables that have very heavy write operations. These table are very large and some are over a hundred gigabytes. I noticed performance of this database is getting slower and after some investigation we suspect that the Auto Update Statistics function is causing a performance degradation.

Index defrag script (21005/2008)

Wed, 25 Aug 2010 06:00:00 UT

This script will allow reindexing (either rebuild or reorg)

Index usage overview

Wed, 09 Jun 2010 06:00:00 UT

This view reports almost everything you want to know about your indexes