Friday, September 19, 2014
 
SQL Server Script Repository
 
     

SQL Script - Database Filegrowth Information

By Saleem Hakani on 8/25/2014

This script/SPROC allows you to report filegrowth information from single database or all databases of your local SQL Server instance. It provides you with the following information:

1. Local Server Name

2. Database Name

3. Logical FileName

4. Initial File Size

5. File Growth Value/Percentage

6. Any Growth Restrictions

Please run the SPROC without any parameters to obtain help on how to execute this stored procedure.

File Restrictions
Filegrowth
Percent Growth

SQL Script - Database Compatibility

By Saleem Hakani on 8/25/2014

Setting the database to the right compatibility level is very important as it can have a negative impact on the performance and behavior of your databases. I've seen that many customers upgrade their SQL Servers from 2008 -> 2012 or from 2012 -> 2014 but forget to upgrade the compatibility of their databases. This basically means that though you have an upgraded version of SQL Server, your database cannot benefit from any new or enhanced features of SQL Server since your database is running on an old compatibility level.

Incompatible database compatibility could also lead to inefficient query plans. Therefore, it is important that you upgrade the compatibility of databases right after the database is upgraded to a newer version of SQL Server. This script can help go thru all the databases and provide you with a report on what databases are on old compatibility level and what compatibility level they need to be upgraded to.

Database Compatibility
SET COMPATIBILITY_LEVEL
Upgrading a database

SQL Script - Attach and Detach Databases

By Saleem Hakani on 8/25/2014

This script allows you to generate script to detach and attach one or all databases from your local SQL Server instance. This can be very useful when performing maintenance, upgrade, backups of files, relocating data/log files to other drives, etc. You no longer need to remember the long hard-coded file path after detaching the database since this procedure takes care of generating the entire command along with the path for you.

This script also takes care of the 16 file limit when using SP_Attach_DB system stored procedure. Please follow the help for successful implementation of this script.

Attach and Detach Databases
Attach database
Create Database For Attach
Detach database
Moving a Database
Relocating Database Files
sp_attach_db
sp_detach_db
Upgrading a database

SQL Script - Database Backup Solution

By Saleem Hakani on 8/24/2014

This script allows you to Perform Full/Log or Differential backups of User databases. It also allows you to perform full System database backups from your local SQL Server instance.

Backup Database
Compressed Backups
Database Backups
Diffrential Database Backup
Full Database Backup
Multi Device Backup
Transaction Log Backup

SQL Script - Update Statistics in SQL Server

By Saleem Hakani on 8/24/2014

Statistics are important for query optimizer to generate optimal query plans. If you have outdated or stale statistics for your index then your query's performance will suffer. Therefore, it is important to update the statistics to make sure query optimizer always generates the best and optimal plan.

This script allows you to report and/or update outdated/stale statistics for either individual indexes or for all indexes in a databases and for all indexes across all user databases.

This script provides the following information:
1. Name of the Database
 2. Table Name
 3. Index Name
 4. Last date when Statistics was Updated
 5. Row Count of the Table
 6. Number of Rows Changed
 7. Percentage of Row Changes Occurred Since Last time Statistics were Updated
 8. Script for you to manually execute Update Statistics on individual objects

DBCC SHOWCONTIG
Outdated Statistics
SP_UpdateStats
Stale Statistics

SQL Script - Index Fragmentation Management

By Saleem Hakani on 8/24/2014

This procedure allows you to check and fix fragmentation in your databases. Alternatively, it can also provide you with recommendations without fixing fragmentation issues. Please review the help from the Procedure to learn more about various parameters/options it supports.

This Script Provides the following information:
1. Database name in which fragmentation was found, Table Name, Index Name that is fragmented, Percentage of Fragmentation,
2. Recommendation on what to do about fragmentation and script that you can use to individually and manually defrag or rebuild the index.
3. This script is generated based on the recommendations provided by SQLCOMMUNITY. Note: This script does not generate any scripts for heaps.

Alter Index
Index Fragmentation
Index Rebuild
Remove Index Fragmentation

SQL Script - List Orphan Users Accounts

By Saleem Hakani on 8/24/2014

User accounts in SQL Server can become orphan whenever a new database is restored or attached. Orphan account means there is no associated login for that user and the when a user logs in to SQL Server using the account which has an orphan user in the database, login account will not be able to access database objects since the user in the database is orphan. Identifying and fixing orphan accounts from all user databases is important for any database administrator. Please note: After identifying an orphan user account, you will need to use SP_Change_Users_Login System Stored Procedure to either 'Auto_Fix','Report','Update_One'. (Example: EXEC sp_change_users_login 'Update_One', 'sqlcommunity', 'sqlcommunity');

Orphan SQL Accounts
Orphan Windows Accounts
SP_Change_User_Login
Minimize
Script Categories
HOME   |   ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   VIDEOS   |   BEST PRACTICES   |   JOBS   |   TRAINING   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM