EDIT: fixed problems found by gam.
I have made yet another change to the DBCC DBREINDEX stored procedure. I modified it so that when a clustered index gets rebuilt, we don't rebuild the other indexes since that happens automatically for us.
Read more →
EDIT: Please see my new version of the isp_Backup stored procedure.
Here's the new version for the backup transaction log stored procedure:
---------------------------------------------------------------------------------------------------- -- OBJECT NAME : isp_Backup_TLog -- -- AUTHOR : Tara Duggan -- DATE : May 12, 2004 -- -- INPUTS : @Path - location of the backups -- @Retention - number of days to retain transaction log backups -- OUTPUTS : None -- DEPENDENCIES : None -- -- DESCRIPTION : This stored procedure performs a transaction log backup on the non-log -- shipped user databases that do not have SIMPLE set as the recovery model.
Read more →
EDIT: Please see my new version of the isp_Backup stored procedure.
I decided to finish posting the new versions of my database maintenance routines. Here's the new version for backing up your databases using SQL LiteSpeed:
Read more →
EDIT: Please see my new version of this stored procedure.
I've been asked a few times if there was a way to change the number of days to retain the backup files for in my backup stored procedure.
Read more →
Due to the problem mr_mist found in the DBCC DBREINDEX stored procedure, I have an updated version of the DBCC INDEXDEFRAG sproc as well:
------------------------------------------------------------------------------------------------------ OBJECT NAME : isp_DBCC_INDEXDEFRAG---- AUTHOR : Tara Duggan-- DATE : May 11, 2004---- INPUTS : @dbName - name of the database-- OUTPUTS : None-- DEPENDENCIES : None---- DESCRIPTION : This stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the database.
Read more →
EDIT: This stored procedure has been updated.
Earlier this month, I posted a stored procedure that runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc.
Read more →
In our test environment, we have a SQL Server that has four named instances on it. For one of the instances, I was configuring it to be the distributor for replication.
Read more →
EDIT: Updated the link to the new version of isp_Backup.
Over the past couple of weeks, I have posted various database maintenance stored procedures. Here's a listing of them:
isp_Backup - full, differential, and transaction log backups isp_DBCC_CHECKDB - perform integrity checks on all databases isp_ALTER_INDEX - defragments and rebuilds indexes isp_UPDATE_STATISTICS - performs update statistics on each table in the database isp_Restore - restore newest BAK file in specified directory Do not put these in the master database.
Read more →
Kristen recently asked why his msdb database had grown so large (750MB). He found out that it was due to the sizes of the backup system tables. These tables store information used for backup and restore operations.
Read more →
EDIT: This stored procedure has been updated.
The below stored procedure runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc. I recommend putting it into an Admin database (hey just name it Admin!
Read more →
EDIT: This stored procedure has been updated.
The below stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the user database that is inputted into the sproc. I recommend putting it into an Admin database (hey just name it Admin!
Read more →
EDIT: This stored procedure has been updated.
The below stored procedure will backup the transaction log for each of the databases that aren't being log shipped (you don't want to break log shipping by running a transaction log backup outside of the log shipping plan) and also aren't using SIMPLE for the recovery model.
Read more →
EDIT: This stored procedure has been updated.
Yesterday, I posted a stored procedure that performs a full backup on the databases. I mentioned that I had a SQL LiteSpeed version. This version backs up all databases except master, model, msdb, tempdb, pubs, NorthWind.
Read more →
EDIT: This stored procedure has been updated.
This stored procedure performs a full backup of the databases. It has two input parameters. The first input parameter is the path to where you would like to backup the databases.
Read more →
EDIT: This stored procedure has been updated.
Here's a stored procedure that will check the integrity of each of the databases. I've got it scheduled to run every night on each of our servers.
Read more →
Ever wanted to see the ERD for the system tables in MS SQL Server 2000? If you can't get your hands on the poster, then you can download a soft copy here.
Read more →
A question came up on SQLTeam that asked if anyone has a script to copy new backups to a remote server. Of course, you could do this in VBScript, but how would you know about backups that were put into a different directory than what your code is expecting?
Read more →
There are several things that a DBA sets up in a production environment, one of these things is rebuilding the indexes. This is done with DBCC DBREINDEX. Some systems can not afford the locks that are held for long periods of time with DBCC DBREINDEX so they use DBCC INDEXDEFRAG instead.
Read more →
Have you ever needed to quickly disable all jobs on a SQL Server? If you have a lot of jobs to disable, then this stored procedure will help you out. We move our systems to our disaster recovery site twice per year for disaster recovery testing.
Read more →