Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

SQL Server - Database Administration

PASS Summit 2011 – Part IV

This is the final blog for my PASS Summit 2011 series.  Well okay, a mini-series, I guess. On the last day of the conference, I attended Keith Elmore’ and Boris Baryshnikov’s (both from Microsoft) “Introducing the Microsoft SQL Server Code Named “Denali” Performance Dashboard Reports, Jeremiah Peschka’s (blog|twitter) “Rewrite your T-SQL for Great Good!”, and Kimberly Tripp’s (blog|twitter) “Isolated Disasters in VLDBs”. Keith and Boris talked about the lifecycle of a session, figuring out the running time and the waiting time.  They pointed out the transient nature of the reports.  You could be drilling into it to uncover...

posted @ Friday, November 11, 2011 2:04 PM | Feedback (2) | Filed Under [ SQL Server - Database Administration SQL Server - PASS ]

PASS Summit 2011 – Part III

Well we’re about a month past PASS Summit 2011, and yet I haven’t finished blogging my notes! Between work and home life, I haven’t been able to come up for air in a bit.  Now on to my notes… On Thursday of the PASS Summit 2011, I attended Klaus Aschenbrenner’s (blog|twitter) “Advanced SQL Server 2008 Troubleshooting”, Joe Webb’s (blog|twitter) “SQL Server Locking & Blocking Made Simple”, Kalen Delaney’s (blog|twitter) “What Happened? Exploring the Plan Cache”, and Paul Randal’s (blog|twitter) “More DBA Mythbusters”.  I think my head grew two times in size from the Thursday sessions.  Just WOW! I...

posted @ Friday, November 11, 2011 12:22 PM | Feedback (1) | Filed Under [ SQL Server - Database Administration SQL Server - PASS ]

Renaming the sa Account

Today I renamed the sa account on 23 SQL Server 2005/2008 instances.  I used the CMS to assist with this task.  Later we realized all of the SQL Agent jobs were failing on these instances with the following error: “The job failed.  The owner (sa) of job XYZ does not have server access.” We use sa as the job owner, and it had correctly changed the owner to our new name as the sids had not changed.  We were at first confused why the jobs were failing but then realized restarting the SQL Agent service might help. ...

posted @ Wednesday, August 24, 2011 2:16 PM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

65536% Autogrowth!

Twice a year, we move our production systems to our disaster recovery site.  Last Saturday night was one of those days.  There are about 50 SQL Server databases to be moved to the DR site, which is done via database mirroring.  It takes only a few seconds to failover, but some databases have a bit more involved work such as setting up replication.  Everything went relatively smooth, but we encountered a weird bug on our most mission critical system.  After everything was successfully failed over to the DR site, it was noticed that mirroring was in a suspended...

posted @ Monday, March 07, 2011 11:19 AM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Max Degree of Parallelism Server-Side Setting

Recently I opened a case with Microsoft PSS to help us through a severe performance problem on a new system.  As part of that case, the PSS engineer checked our “max degree of parallelism” server-side setting.  It is our standard to use 4 on our production systems that have 16 CPUs (2 sockets, quad-core, hyper-threaded).  The PSS engineer had me run the below query to get Microsoft’s recommended value of “max degree of parallelism” server-side setting for our 16-CPU system: select case when cpu_count / hyperthread_ratio >...

posted @ Monday, November 08, 2010 12:47 PM | Feedback (5) | Filed Under [ SQL Server - Database Administration ]

DBA job ranked #7 for Best Job in America 2010

I started my IT career as a student worker in the database team at the County of San Diego.  Although I worked on many different things in that group, it launched my career as a Database Administrator.  You can get an overview of my career here. It seems I picked the right career for a good job in America.  According to CNNMoney and PayScale, a database administrator job is ranked number 7 for the best job in America in 2010.  Check it out here. There are quite a few IT jobs on the list.  You can check out...

posted @ Tuesday, October 12, 2010 9:50 AM | Feedback (5) | Filed Under [ SQL Server - General SQL Server - Database Administration ]

Performance Tuning with Traces

On Saturday (9/18), I presented "Performance Tuning with Traces" at SQL Saturday #55.  There were about 65 people in attendance, and it was standing room only.  As promised, you can download my presentation materials here. I received such good feedback after my presentation that I will likely present again in the future if given the opportunity. 

posted @ Tuesday, September 21, 2010 2:28 PM | Feedback (5) | Filed Under [ SQL Server - General SQL Server - Database Administration ]

What are the largest SQL projects in the world?

If you are interested in finding out the largest SQL projects in the world, you should check out this PowerPoint presentation.  It’s from Kevin Cox of SQL CAT at Microsoft.

posted @ Friday, July 23, 2010 10:06 AM | Feedback (3) | Filed Under [ SQL Server - General SQL Server - Database Administration ]

Fix for SQL Server 2005/Windows 2008 cluster issue with lower/mixed case server names

Last November, I blogged about a weird bug with SQL Server 2005 on a Windows 2008 cluster.  We were having issues with Database Mail and other things and learned that it was due to the server names being in lower case.  To workaround the issue, we had to follow the steps outlined here as there was no bug fix at the time.  I’ve now learned that this bug has been fixed!  The fix is included in cumulative update package 9 for SQL Server 2005 service pack 3.  Check it out here.

posted @ Friday, July 02, 2010 12:32 PM | Feedback (1) | Filed Under [ SQL Server - Database Administration ]

Troubleshooting Application Timeouts in SQL Server

I recently received the following email from a blog reader: "We are having an OLTP database instance, using SQL Server 2005 with little to moderate traffic (10-20 requests/min). There are also bulk imports that occur at regular intervals in this DB and the import duration ranges between 10secs to 1 min, depending on the data size. Intermittently (2-3 times in a week), we face an issue, where queries get timed out (default of 30 secs set in application). On analyzing, we found two stored procedures, having queries with multiple table joins inside them of taking a long time (5-10 mins) in getting...

posted @ Monday, March 08, 2010 5:44 PM | Feedback (6) | Filed Under [ SQL Server - Database Administration ]

Defragmenting/Rebuilding Indexes in SQL Server 2005 and 2008

I have modified isp_ALTER_INDEX, which is the stored procedure that I wrote and use to defragment/rebuild indexes in SQL Server 2005 and SQL Server 2008.  The code change has just one bug fix.  The bug would only have been encountered on SQL Server 2008 systems that have XML indexes that need to be rebuilt based upon the input parameters.  In SQL Server 2005, the sys.dm_db_index_physical_stats data management function outputted “XML INDEX” for the index_type_desc column when the index contained a column with the XML data type.  In SQL Server 2008, the DMF was modified so that you could have either...

posted @ Tuesday, November 03, 2009 11:04 AM | Feedback (10) | Filed Under [ SQL Server - Database Administration ]

Fast and Reliable Backup and Restore of VLDBs in SQL Server 2008

The SQLCAT team released a very informative whitepaper regarding backup and recovery of very large databases in SQL Server 2008.  It is a must read if you are a SQL Server DBA.  Check it out here. If you are not already following the SQLCAT team’s blogs and you are a SQL Server DBA, you need to get with the times and start following one of the best online resources for SQL Server.

posted @ Thursday, September 10, 2009 4:03 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Backup SQL Server 2005 and 2008 Databases

I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases.  This new version includes one bug fix and three new features: Bug Fix – removed ReportServerTempdb from exclusion list Bug Fix – fixed file retention code to handle database names with spaces Feature – support for SQL Server 2008 including compression Feature – archive bit option Feature – COPY_ONLY option I decided that excluding the ReportServerTempdb from the backups was unnecessary.  This database, used...

posted @ Tuesday, September 08, 2009 11:26 AM | Feedback (4) | Filed Under [ SQL Server - Database Administration ]

Backup SQL Server Databases

EDIT: new version of stored procedure is located here.  Use the new version for 2005/2008.  Use the below version for 2000. I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases.  In this version, I fixed two bug and added a feature.  Here are the changes: Fixed "2000 backupset" bug by qualifying the object to msdb.dbo.backupset Fixed the full backup check for SIMPLE recovery model databases (bug found by Eugene from PerfSpot.com) Added Red Gate SQL Backup functionality If you have scheduled jobs...

posted @ Tuesday, April 07, 2009 1:00 PM | Feedback (7) | Filed Under [ SQL Server - Database Administration ]

SQL Server 2008 System Views Map

Microsoft published the ERD for the system views about a month ago, but I'm just now seeing it.  It is available in PDF or XPS format.  You can download them here.

posted @ Friday, February 27, 2009 10:53 AM | Feedback (1) | Filed Under [ SQL Server - General SQL Server - Database Administration ]

How to refresh a SQL Server database automatically

As a DBA, we are often asked to refresh a database, which means to overwrite an existing database using a different database's backup.  If you are rarely asked to do this, you may decide to do it manually.  If you are asked to do this on a regular and perhaps scheduled basis, then you'd want to automate it.  Developers often want to write and debug code against a copy of the production database, so it makes sense to refresh their databases on a daily or weekly basis.  If you were to do the refresh manually, you would probably follow these...

posted @ Wednesday, February 25, 2009 12:32 PM | Feedback (8) | Filed Under [ SQL Server - Database Administration ]

How to track database growth across multiple SQL Server instances

It is easy to track database growth on a single SQL Server instance.  We simply just need to store the results of sp_databases or loop through the databases and call sp_spaceused for each database. If you support hundreds of SQL instances like I do, you'd want to store the database growth information in a central repository.  From this central server, you could create a linked server for each SQL Server instance to track, but I hate creating linked servers.  I especially hate having to create hundreds of them on one SQL Server instance.  Instead of using linked servers, I created...

posted @ Tuesday, December 16, 2008 10:28 AM | Feedback (6) | Filed Under [ SQL Server - Database Administration ]

Defragmenting Indexes in SQL Server 2005

EDIT: There is a new version of this stored procedure. I've made a couple of changes to my defragment indexes stored procedure, isp_ALTER_INDEX, based upon feedback I've received from my blog readers.  For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command.  Check the comment header block for its documentation. Here are the changes: Added support for database and object names with special characters. Added support for indexes with the ALLOW_PAGE_LOCKS option enabled.  Online reindexing is not available for indexes with this option enabled. ...

posted @ Wednesday, September 03, 2008 9:56 PM | Feedback (14) | Filed Under [ SQL Server - Database Administration ]

Backup SQL Server Databases

EDIT: There is a new version of this stored procedure. I've made several changes to my backup stored procedure, isp_Backup.  I fixed a few things and added some much needed features.  Here are all of my changes: Fixed the retention code so that only those files that pertain to the type of backup are deleted.  I had previously decided that I wanted all files older than @retention to be deleted, however I've changed my mind due to requests from my blog readers. Added support for database names with spaces, dashes, single quotes, and other...

posted @ Tuesday, August 19, 2008 2:09 PM | Feedback (12) | Filed Under [ SQL Server - Database Administration ]

SQL Profiler best practices

SQL Profiler is a great tool that allows you to see what's going on inside SQL Server.  You can find out what your worst performing queries are, how often a query is executed, and loads of other stuff.  I'm typically interested in the things that are impacting performance such as long durations of stored procedures. One important thing to know when running a trace using SQL Profiler is that the trace can degrade performance.  To minimize its impact, you should follow these best practices: Never run SQL Profiler on the database server ...

posted @ Wednesday, August 06, 2008 5:36 PM | Feedback (6) | Filed Under [ SQL Server - Database Administration ]

How to get physical CPU count on a server

With hyper-threading and multi-core CPUs, it isn't easy to find out how many physical CPUs (or sockets) there are on a server. Recently we were given such a task to ensure we were properly licensed.


Read more here.

posted @ Tuesday, August 05, 2008 9:48 AM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

SQL Server jobs on production instances

The most important thing that a Database Administrator does is backups.  To automate them, we schedule them as jobs.  But what other jobs are important on SQL Server instances? Here are the jobs that I have on every SQL Server 2005 production instance: Name ...

posted @ Monday, June 30, 2008 9:46 AM | Feedback (18) | Filed Under [ SQL Server - Database Administration ]

Idera's SQL admin toolset

We are required to start auditing our SQL Servers for various data points.  We did not want to write a custom tool if at all possible, so we started looking for third-party products.  We found Idera's SQL admin toolset.  It doesn't collect all of the information that we want to audit, but it's a great start. Excerpt from their site about the tool: Are you a DBA or developer tired of spending countless hours on routine administrative and troubleshooting tasks, like figuring out why a user can't connect, or moving databases, or producing reports for your boss to show that the backups...

posted @ Wednesday, June 11, 2008 1:43 PM | Feedback (7) | Filed Under [ SQL Server - Database Administration ]

How to troubleshoot SQL Server connectivity problems

We often get asked in the SQLTeam forums why they can't connect to a SQL Server instance.  They provide the typical connectivity error.  In SQL Server 2000, the error is: SQL Server does not exist or access denied. In SQL Server 2005, the error is: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Both errors are misleading as they are not usually resolved by providing access or enabling remote connections.  In...

posted @ Friday, May 23, 2008 10:53 AM | Feedback (8) | Filed Under [ SQL Server - Database Administration ]

Backup databases stored procedure - new version

I have updated my backup stored procedure, isp_Backup, as the old version had two issues. It did not exclude database snapshots, which can't be backed up. It also was not checking for the number of CPUs in the registry properly. The CPU check only affects backups when SQL Litespeed is used. In the next version, I will exclude those databases that are being log shipped by SQL Litespeed as we don't want to interfere with the LSNs. I am already checking for this condition for native backups. I will also add code to perform a full backup when it is...

posted @ Wednesday, May 21, 2008 10:05 AM | Feedback (16) | Filed Under [ SQL Server - Database Administration ]

Optimizing tempdb

To improve query performance when you've found tempdb to be a performance bottleneck, you can create multiple data files of equal size.  It is recommended that you add one file per CPU (per core, not per socket).  The below script will do this for you.  It is written as a stored procedure as I like to keep all of my scripts in an admin-type database.  You will probably only run this once per instance though.  To determine how big each file should be, look at the size of your tempdb's data file which by default is named tempdb.mdf.  You should check...

posted @ Tuesday, May 20, 2008 11:17 AM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

How to properly check for data corruption via a SQL job

I recently found out that we are not properly checking for data corruption in our "Integrity Checks" SQL job.  I thought that a SQL job would fail if the job step that runs DBCC CHECKDB returned errors, but apparently that's not the case.  Check out Paul Randal's blog for more information. To properly check for data corruption via a SQL job, you should raise an error if @@ERROR does not equal zero. In the next few days, I'll rewrite my isp_DBCC_CHECKDB stored procedure to include this.  I will be testing my code change against a database that contains corruption.  See this thread...

posted @ Tuesday, May 06, 2008 4:13 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

How to failover Database Mirroring in SQL Server 2005 using T-SQL

I've previously shown how to setup Database Mirroring in SQL Server 2005 with T-SQL, but how do you failover to the mirrored databases using T-SQL?  Here's how: --Run on principal USE master GO ALTER DATABASE dbName SET SAFETY FULL GO ALTER DATABASE dbName SET PARTNER FAILOVER GO --Run on new principal USE master GO ALTER DATABASE dbName SET SAFETY OFF GO If you are using synchronous database mirroring, just ignore the "SET SAFETY" commands.  We use asynchronous database mirroring for performance reasons as our sites are 300 miles apart.  Even though we have a fast network in between the two sites, the latency is too high when we tried it in synchronous mode.  

posted @ Sunday, May 04, 2008 2:50 PM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Error when installing instance on a cluster

If you need to install a SQL Server 2005 instance on a cluster, make sure that there aren't any remote connections to any of the other nodes.  Otherwise, the installation may fail with the following error: Setup failed to start on the remote machine. Check the Task Scheduler event log on the remote machine. Please refer to this Knowledge Base article for more information. I get this error every now and then and usually can't remember how to resolve it.  Maybe I'll be able to find my own blog entry when I search on the error!

posted @ Tuesday, April 29, 2008 4:52 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Purging Database Mail data from msdb

On Sunday morning, I got paged by one of our monitoring systems that we were low on free disk space on one of our production servers.  The specific resource that was low on free disk space was our "Backup" mount point.  Since I didn't think this should be the case for a few more months, I looked into each folder to see if any database backup was larger than it should be.  That's when I noticed that our msdb backups were 6 gigabytes in size.  Typically the msdb database is larger than it should be due to backup history, but...

posted @ Monday, April 28, 2008 4:04 PM | Feedback (1) | Filed Under [ SQL Server - Database Administration ]

CLR stored procedure to get disk space information

I've seen lots of tools/scripts to get disk space information about a particular SQL Server, but none of them include the information about mount points.  Mount points are now supported in SQL Server 2005.  We implemented them on our production clusters and therefore needed a way to monitor them.  We are now monitoring them with MOM via a Veritas management pack, but before that was implemented we used a CLR stored procedure that I wrote.  It is written in C#.  The code allows you to get disk space information for the local server or for remote servers.  You just need...

posted @ Tuesday, December 18, 2007 3:45 PM | Feedback (4) | Filed Under [ SQL Server - Database Administration ]

Running my code against multiple databases

I've received a few comments in the past about people modifying my code so that it loops through the databases rather than having to call my code for each database.  One such example is with my isp_ALTER_INDEX stored procedure.  In my version, you provide the database name as an input parameter.  I recently received a comment that someone had modified my code so that it did not have this input parameter and instead had code added to it that looped through the databases to be defragmented.  The disadvantage to this is that I update my code occassionally to fix bugs...

posted @ Wednesday, November 28, 2007 11:53 AM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Defragment Indexes stored procedure - new version

UPDATE: This stored procedure has been updated. I have fixed a bug in my isp_ALTER_INDEX stored procedure.  My old stored procedure was not handling the online option correctly for clustered indexes when there were LOB data types anywhere in the table.  Use this new version instead of the old one. This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels. If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot.  Kalen Delaney mentions this in the May 2006 edition...

posted @ Tuesday, November 27, 2007 12:09 PM | Feedback (6) | Filed Under [ SQL Server - Database Administration ]

Shrinking a Database and Transaction Log Backups problem

Recently we purged millions of rows from a database as it was determined that we did not need to retain it.  Since we were low on free disk space, we decided to shrink the primary data file (MDF).  Usually I wouldn't recommend shrinking the database, but this was a situation where it met the exception to the rule.  Since the amount of space to be reclaimed was around 50GB, I decided to run the shrink in smaller batches (1 GB each pass).  After about an hour of shrinking the database down, I started to receive the following error: File ID 1...

posted @ Thursday, November 08, 2007 2:31 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Troubleshooting Performance Problems in SQL Server 2005

For a few weeks, I've been battling performance problems of an application whose database is hosted on SQL Server 2005.  The database server has been running at very high CPU utilization for a few months now and we've been slowly ruling things out as the culprit.  We haven't gotten to the bottom of it, but we are getting close. Here's a great article that I stumbled upon recently that goes into great detail on how to troubleshoot performance problems in SQL Server 2005.

posted @ Monday, November 05, 2007 11:41 AM | Feedback (1) | Filed Under [ SQL Server - Database Administration ]

Database Mirroring problem

Recently, I rebooted all nodes in our 4-node cluster at our disaster recovery site because I had attempted to install another SQL Server 2005 instance but the installation had indicated I needed to reboot the nodes first.  I rebooted the nodes one at a time as is best practice for a cluster.  After the reboots, I was able to successfully install the new instance. At some point after the reboot, database mirroring had entered the suspended state for most of our databases at the primary site.  This eventually caused us to run out of disk space at the primary site on the...

posted @ Friday, April 20, 2007 11:10 AM | Feedback (1) | Filed Under [ SQL Server - Database Administration ]

SQL Server 2005 Performance Dashboard Reports

If you have SQL Server 2005 service pack 2 installed on your database server, then you might want to check this out.  "The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio" and "are used to monitor and resolve performance problems on your SQL Server 2005 database server." I had bookmarked the page a few weeks back since we don't have service pack 2 installed in our production environment yet.  If you've already used the reports to help resolve performance problems,...

posted @ Thursday, April 19, 2007 9:37 AM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Database Mirroring Best Practices and Performance Considerations

We are using database mirroring to sychronize our data at the disastery recovery site for our SQL Server 2005 databases.  We are currently encountering a problem with it, so I've been googling to see if there is a fix.  While googling, I found this article.  It goes into great detail about the best practices and performance considerations for database mirroring.  I highly recommend reading it if you are using database mirroring or plan on using it. I will be creating a new weblog that describes the database mirroring problem that we are experiencing. 

posted @ Tuesday, April 17, 2007 10:59 AM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Defragment Indexes stored procedure - new version

I have fixed some bugs in my isp_ALTER_INDEX stored procedure. Use this version instead of the old one. This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels. If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot.  Kalen Delaney mentions this in the May 2006 edition of SQL Server Magazine (InstantDoc ID 49769). ------------------------------------------------------------------------------------------- -- OBJECT NAME : isp_ALTER_INDEX -- -- AUTHOR ...

posted @ Tuesday, April 17, 2007 10:29 AM | Feedback (5) | Filed Under [ SQL Server - Database Administration ]

Defragment Indexes stored procedure

UPDATE: This stored procedure has been updated. This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels. If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot.  Kalen Delaney mentions this in the May 2006 edition of SQL Server Magazine (InstantDoc ID 49769). ------------------------------------------------------------------------------------------- -- OBJECT NAME : isp_ALTER_INDEX -- -- AUTHOR : Tara Kizer -- -- DATE...

posted @ Wednesday, March 07, 2007 4:10 PM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Critical update for SQL Server 2005 service pack 2

SQL Server 2005 service pack 2 was recently released and already Microsoft has a critical update for it.  *Sigh* Service pack 2 was updated to include this, so you only need to install the critical update if you downloaded service pack 2 prior to March 5, 2007.  Check this out for more details.  

posted @ Wednesday, March 07, 2007 10:32 AM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Defragmenting indexes in SQL Server 2005

To defragment your indexes, we now use ALTER INDEX in SQL Server 2005 rather than DBCC DBREINDEX and DBCC INDEXDEFRAG, which have both been deprecated in 2005. The REBUILD option of ALTER INDEX is equivalent to the old DBCC DBREINDEX; the REORGANIZE option of ALTER INDEX is equivalent to the old DBCC INDEXDEFRAG. I have not yet written a stored procedure to run ALTER INDEX on the fragmented indexes, so I'll point you to two different resources.  1. In the sys.dm_db_index_physical_stats topic in SQL Server 2005 Books Online, you will find a script to defragment the indexes of a particular database.  This...

posted @ Monday, February 26, 2007 5:05 PM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Backup databases stored procedure - new version

EDIT: A new version of this stored procedure is available here. I've completely modified my isp_Backup stored procedure.  The new version supports both SQL Server 2000 and 2005.  It also gets rid of the need to use my other backup stored procedures, such as isp_Backup_TLog and isp_Backup_LS. The code is heavily commented to help others understand what I am doing in the code.  I don't normally comment this much, but I realize that non-DBAs may be using this stored procedure instead of maintenance plans.    set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ---------------------------------------------------------------------------------------------------- -- OBJECT NAME ...

posted @ Monday, February 26, 2007 2:20 PM | Feedback (23) | Filed Under [ SQL Server - Database Administration ]

Database Mail on a cluster

In our production environment, we have two 4-node clusters.  One cluster runs at the primary site; the other cluster runs at our disaster recovery site.  Each cluster is running 11 SQL Server 2005 instances. We setup Database Mail on all of the instances at both sites, so that we could e-mail internal customers the results of various ad-hoc queries.  It was soon realized that Database Mail was not working properly at the primary site on any of the 11 instances.  We got our Exchange, server, and network administrators involved, but we were unable to figure out what was wrong.  We never...

posted @ Tuesday, February 20, 2007 11:49 AM | Feedback (0) | Filed Under [ Gripes SQL Server - Database Administration ]

How to setup Database Mirroring in SQL Server 2005 using T-SQL

Our disaster recovery and database availability solution involves clustering and Database Mirroring in SQL Server 2005.  At our primary site and our disaster recovery site, we have a 4 node cluster with several SQL Server 2005 instances on it.  So that's 8 nodes and 2 clusters at both sites.  The cluster is Active/Active/Active/Active at the primary site. As we add databases to any of the instances on the cluster at the primary site, I have to setup Database Mirroring on them so that our DR site is ready in case of a real DR scenario or if we decide to move...

posted @ Tuesday, February 13, 2007 12:40 PM | Feedback (3) | Filed Under [ SQL Server - Database Administration ]

KILL spids

To quickly disconnect all non admins from a database, I use this: ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE One of the development teams recently requested that their production databases be copied to a development server on a weekly basis.  In the development environment, developers have db_owner access to all of their databases.  In order to restore a database, no one can be connected to it if it already exists.  Since my ALTER DATABASE statements will only work on non admins, I also need to run this to kick out developers: DECLARE @spid varchar(10) SELECT @spid...

posted @ Thursday, February 08, 2007 1:43 PM | Feedback (1) | Filed Under [ SQL Server - Database Administration ]

DBCC CHECKDB stored procedure - new version

I've updated my isp_DBCC_CHECKDB stored procedure as I was getting errors on a SQL Server 2005 server when one of the databases was the mirror in a database mirroring scenario or when one of the databases was in a loading state (NORECOVERY option of RESTORE command).  This issue will actually appear on SQL Server 2000 as well; I just hadn't noticed it on 2000 yet.   ---------------------------------------------------------------------------------------------------- -- OBJECT NAME         : isp_DBCC_CHECKDB -- -- AUTHOR               : Tara Duggan -- DATE     : May 3, 2004 -- -- INPUTS    : None -- OUTPUTS    : None -- DEPENDENCIES         : None -- -- DESCRIPTION         : This stored procedure runs DBCC CHECKDB for each of the databases. -- -- EXAMPLES (optional)  :...

posted @ Wednesday, February 07, 2007 2:55 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

SQL Server 2005 Jobs

We use MOM 2005 to monitor our production environment.  We hadn't been receiving any alerts from MOM when a SQL Server 2005 job failed, so I was tasked with tracking the problem down.  I checked the MOM configuration to ensure everything was setup properly.  I compared the SQL Server 2005 rules to the SQL Server 2000 rules since we were getting alerts when a SQL Server 2000 job failed.  The rules were configured the same.  I then checked the Application Log for the 2005 job failures since that is what MOM uses.  I noticed that when a 2005 job failed, it...

posted @ Thursday, December 14, 2006 2:55 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

SQL Server 2005 - Best Practices

Check out this article for white papers, scripts, and tools on SQL Server 2005 best practices.

posted @ Wednesday, December 13, 2006 1:06 PM | Feedback (1) | Filed Under [ SQL Server - General SQL Server - Database Administration ]

SQL Profiler 2005 and Duration Data Column

For those of you who have been using SQL Profiler 2005 for a while, you probably are already aware of this.  We've been using SQL Server 2005 for a few months now, but I hadn't touched SQL Profiler 2005 very much yet.  Today I had to run a trace to check if any of the queries in our performance environment were taking too long.  I always save the trace data to a table, so that I can easily run queries against it.  Here is my typical query: SELECT TOP 100 Duration, TextData FROM TraceTable WHERE TextData IS NOT NULL ORDER BY Duration DESC At...

posted @ Wednesday, October 04, 2006 11:46 AM | Feedback (4) | Filed Under [ SQL Server - Database Administration ]

Enabling xp_cmdshell in SQL Server 2005

By default, xp_cmdshell is disabled in SQL Server 2005 for security reasons.  I am currently using xp_cmdshell in my backup stored procedure to delete files, so I must enable xp_cmdshell on my SQL Server 2005 instances.  There are two ways to enable this option: Surface Area Configuration tool or sp_configure.  Enabling this option via Surface Area Configuration tool is rather straight forward.  Here is the script to enable xp_cmdshell in SQL Server 2005 via sp_configure: EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE

posted @ Thursday, September 14, 2006 4:51 PM | Feedback (5) | Filed Under [ SQL Server - Database Administration ]

UPDATE STATISTICS stored procedure

Here's the stored procedure that I use to update statistics on a database.  I don't use sp_updatestats as it doesn't allow you to change the number of rows to sample.  I typically run it weekly on the databases that I support.  CREATE PROC isp_UPDATE_STATISTICS(@dbName sysname, @sample int)AS SET NOCOUNT ON DECLARE @SQL nvarchar(4000)DECLARE @ID intDECLARE @TableName sysnameDECLARE @RowCnt int CREATE TABLE ##Tables( TableID INT IDENTITY(1, 1) NOT NULL,  TableName SYSNAME NOT NULL) SET @SQL = ''SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) 'SET @SQL = @SQL + 'SELECT [name] 'SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects ' SET @SQL...

posted @ Monday, August 14, 2006 3:26 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

SQL Server 2005 instance number

If you've installed multiple instances of SQL Server 2005 on the same database server, perhaps you've noticed Microsoft's naming convention for the file locations.  They are now using MSSQL.n, where n is the instance number.  We have 4 instances installed on a database server.  One of them is the default instance; the other 3 are named instances.  Here is what our directory structure looks like:E:\MSSQL.1\MSSQL\... E:\MSSQL.2\MSSQL\... E:\MSSQL.3\MSSQL\... E:\MSSQL.4\MSSQL\... This quote, from this article, explains the naming convention: The first instance ID generated is MSSQL.1; ID numbers are incremented for additional instances as MSSQL.2, MSSQL.3, and so on. If gaps occur in the ID sequence due to...

posted @ Wednesday, June 07, 2006 12:59 PM | Feedback (9) | Filed Under [ SQL Server - Database Administration ]

Windows Group Access and SQL Server 2005 Management Studio

Yesterday, a developer sent me an e-mail indicating he was having problems creating a table in SQL Server Management Studio.  He was using the GUI rather than the CREATE TABLE T-SQL statement.  When he tried to save his changes, he encountered the following error: Property DefaultSchema is not available for Database '[DatabasName]'.  This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (SQLEditors) We add developers to the db_owner role in each of the development databases, so I didn't think that permissions was the issue.  I did some googling and found this. You'll encounter this...

posted @ Wednesday, May 24, 2006 4:31 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

64-bit server article in SQL Server Magazine

If you are considering using SQL Server 2005 on a 64-bit platform, then I'd highly suggest reading Michael Otey 's “The Essential Guide to 64-BIT Server” article in SQL Server Magazine's May 2006 issue. I received a free subscription by attending a SQL Server 2005 seminar last year.  While your subscription is active, you get online access to all previous issues. 

posted @ Friday, May 12, 2006 2:20 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

SQL Server 2005 Service Pack 1

Service Pack 1 is now available for SQL Server 2005.  You can download it here. Here is the list of the new features and improvements included in this service pack. Database Mirroring is enabled in this service pack!

posted @ Wednesday, April 19, 2006 10:49 AM | Feedback (6) | Filed Under [ SQL Server - Database Administration ]

Index Fragmentation

A while ago, I posted my DBCC DBREINDEX and DBCC INDEXDEFRAG stored procedures.  They perform the DBCC operation on all indexes in the database, which is probably not a good idea.  This article provides an abundance of data as to why.  Paul Randal, Lead Program Manager at Microsoft for SQL Server Storage Engine and SQL Express, gives additional information in this thread.  So how does one decide when performance degradation is caused by index fragmention?  Paul Randal's response: “All you need to do is see which queries are using which indexes for range scans rather than singleton lookups.  These are the indexes that you...

posted @ Tuesday, April 11, 2006 3:33 PM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Microsoft SQL Server 2005 System Table Map

You can find the poster of the SQL Server 2005 System Table Map in the December 2005 issue of SQL Server Magazine.  Thanks to Rob Volk, here is the online version.

posted @ Friday, December 16, 2005 10:06 AM | Feedback (7) | Filed Under [ SQL Server - Database Administration ]

RESTORE stored procedure

The below stored procedure performs a RESTORE of the newest BAK file in the specified directory (@bkpDir).  I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.  ------------------------------------------------------------------------------------------------------ OBJECT NAME         : isp_Restore-- AUTHOR                    : Tara Duggan-- DATE                          : November 3, 2005-- INPUTS                      : @dbName - name to use for the restored database--                                       @bkpDir - full path to the directory where the BAK files exist--                                       @newDataLocation - new location and file name for the MDF, can be NULL--                                       @newLogLocation - new location and file name for the LDF, can be NULL-- OUTPUTS                    : None-- RETURN CODES       ...

posted @ Tuesday, November 08, 2005 1:23 PM | Feedback (11) | Filed Under [ SQL Server - Database Administration ]

HOWTO run SQL Profiler without sysadmin rights

Over in the SQLTeam forums, eyechart has posted information about running SQL Profiler without sysadmin rights.  I recently granted sysadmin rights to a developer so that he could run SQL Profiler in the development environment.  Permissions were granted for only a couple of days.  Now I won't have to do this anymore!

posted @ Monday, March 21, 2005 4:33 PM | Feedback (9) | Filed Under [ SQL Server - Database Administration ]

DBCC DBREINDEX Stored Procedure - new version #2

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. I made this change a little while ago, but I hadn't posted it yet. It's a good thing since I had to change it again. It turns out that only when a non-unique clustered index gets rebuilt, that the non-clustered ones get rebuilt as well. So when a unique clustered index gets rebuilt, we still need to rebuild the non-clustered ones....

posted @ Tuesday, January 04, 2005 11:46 AM | Feedback (32) | Filed Under [ SQL Server - Database Administration ]

Backup transaction log stored procedure - new version

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. -- -- EXAMPLES (optional)  : EXEC isp_Backup_TLog @Path = 'C:\MSSQL\Backup\', @Retention = 5 -- -- MODIFICATION HISTORY : ---------------------------------------------------------------------------------------------------- -- ---------------------------------------------------------------------------------------------------- CREATE        PROC isp_Backup_TLog (@Path VARCHAR(100), @Retention INT...

posted @ Thursday, September 23, 2004 3:32 PM | Feedback (9) | Filed Under [ SQL Server - Database Administration ]

Backup databases stored procedure using SQL LiteSpeed - new version

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: ---------------------------------------------------------------------------------------------------- -- OBJECT NAME         : isp_Backup_LS -- -- AUTHOR               : Tara Duggan -- DATE   : December 18, 2003 -- -- INPUTS  : @Path - location of the backups -- OUTPUTS  : None -- DEPENDENCIES         : None -- -- DESCRIPTION         : This stored procedure performs a full backup on all of the user databases -- -- EXAMPLES (optional)  : EXEC isp_Backup_LS @Path = 'G:\MSSQL\Backup\', @Retention = 5 ---------------------------------------------------------------------------------------------------- CREATE           PROC isp_Backup_LS (@Path VARCHAR(100), @Retention INT = 2) AS SET NOCOUNT ON DECLARE @Now CHAR(14) -- current date in the...

posted @ Thursday, September 23, 2004 3:30 PM | Feedback (21) | Filed Under [ SQL Server - Database Administration ]

Backup databases stored procedure - new version

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.  Due to this, I have modified the sproc so that you can now input the number of days to retain the files for.  Here is the updated version: ---------------------------------------------------------------------------------------------------- -- OBJECT NAME         : isp_Backup -- -- AUTHOR               : Tara Duggan -- DATE     : December 18, 2003 -- -- INPUTS    : @Path - location of the backups --        @dbType - which databases to backup - All, System, or User --        @Retention - numbers of days...

posted @ Wednesday, August 04, 2004 11:03 AM | Feedback (31) | Filed Under [ SQL Server - Database Administration ]

DBCC INDEXDEFRAG stored procedure - new version

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.---- EXAMPLES (optional)  : EXEC isp_DBCC_INDEXDEFRAG @dbName = 'GT'----------------------------------------------------------------------------------------------------CREATE   PROC isp_DBCC_INDEXDEFRAG(@dbName SYSNAME)ASSET NOCOUNT ON DECLARE @objName SYSNAMEDECLARE @idxName SYSNAMEDECLARE @SQL NVARCHAR(4000)DECLARE @ID INTDECLARE @RowCnt INT CREATE TABLE ##Indexes( Indexes_ID INT IDENTITY(1, 1) NOT NULL,  IndexName SYSNAME NOT NULL,  ObjectName SYSNAME NOT...

posted @ Friday, July 30, 2004 3:13 PM | Feedback (30) | Filed Under [ SQL Server - Database Administration ]

DBCC DBREINDEX Stored Procedure - new version

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.  Well it turns out that the code was incorrect as it was only reindexing one index per table.  Thanks mr_mist for finding this!  This is an updated version of that sproc: ------------------------------------------------------------------------------------------------------ OBJECT NAME         : isp_DBCC_DBREINDEX---- AUTHOR               : Tara Duggan-- DATE     : May 11, 2004---- INPUTS    : @dbName - name of the database-- OUTPUTS    : None-- DEPENDENCIES         : None---- DESCRIPTION         : This stored procedure runs DBCC DBREINDEX for each of the indexes...

posted @ Friday, July 30, 2004 3:08 PM | Feedback (11) | Filed Under [ SQL Server - Database Administration ]

Missing objects in MSDB database

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.  Upon configuring it, I received an error that sp_add_jobstep_internal system stored procedure was missing from the msdb database.  I thought to myself how could a system object be missing in a system database.  It turns out a lot of objects were missing from msdb and it occurred on all four instances.  Now I don't know why it happened as I didn't do the installations and don't know if errors were...

posted @ Friday, July 09, 2004 10:29 AM | Feedback (3) | Filed Under [ SQL Server - Database Administration ]

Database maintenance routines

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.  An Admin database should be created...

posted @ Friday, July 02, 2004 10:50 AM | Feedback (23) | Filed Under [ SQL Server - Database Administration ]

Delete Backup History stored procedure

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.  If you've ever used the restore database wizard in Enterprise Manager (and if you have, you might want to start using Query Analyzer instead to get more familiar with backup and restore operations), you might notice that it displays a listing of previous backups to choose from for the restore.  It gathers this information from these backup system tables.  If you backup your transaction logs...

posted @ Friday, July 02, 2004 9:59 AM | Feedback (39) | Filed Under [ SQL Server - Database Administration ]

DBCC DBREINDEX stored procedure

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!) rather than in master.  Then just schedule isp_DBCC_DBREINDEX for each of the databases you want defragged.  Only run this routine when there is very little activity on your system as DBCC DBREINDEX will block queries from running.    CREATE    PROC isp_DBCC_DBREINDEX(@dbName SYSNAME)ASSET NOCOUNT ON DECLARE @objID INTDECLARE @objName SYSNAMEDECLARE @idxName SYSNAMEDECLARE @SQL NVARCHAR(4000) SET @objID = 0 SET @SQL = ''SET @SQL =...

posted @ Friday, July 02, 2004 9:15 AM | Feedback (4) | Filed Under [ SQL Server - Database Administration ]

DBCC INDEXDEFRAG stored procedure

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!) rather than in master.  Then just schedule isp_DBCC_INDEXDEFRAG for each of the databases you want defragged.  Remember that DBCC INDEXDEFRAG isn't as good as DBCC DBREINDEX in large scale environments (for more information on this, see this article), so see my DBCC DBREINDEX weblog coming soon.  It'll be better than the other one I posted a while back. CREATE  PROC isp_DBCC_INDEXDEFRAG(@dbName SYSNAME)ASSET...

posted @ Tuesday, June 22, 2004 4:56 PM | Feedback (10) | Filed Under [ SQL Server - Database Administration ]

Backup transaction log stored procedure

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.  It has one input parameter, which is the path to where you would like to backup the databases.  It'll create a sub directory underneath that path for each of the databases.  It also deletes any files that are older than two days in that sub directory.  If a...

posted @ Friday, June 18, 2004 1:59 PM | Feedback (3) | Filed Under [ SQL Server - Database Administration ]

Backup databases stored procedure - SQL LiteSpeed version

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.  It has one input parameters, which is the path to where you would like to backup the databases.  It'll create a sub directory underneath that path for each of the databases.  It also deletes any files that are older than two days in that sub directory.  If a sub directory doesn't exist, it creates one for you.  It...

posted @ Thursday, June 17, 2004 4:33 PM | Feedback (15) | Filed Under [ SQL Server - Database Administration ]

Backup databases stored procedure

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.  It'll create a sub directory underneath that path for each of the databases.  It also deletes any files that are older than two days in that sub directory.  If a sub directory doesn't exist, it creates one for you.  The second input parameter is to control which databases you want to backup.  You can pass it All, System, or User.  All means backup...

posted @ Wednesday, June 16, 2004 3:01 PM | Feedback (15) | Filed Under [ SQL Server - Database Administration ]

DBCC CHECKDB stored procedure

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.  I recommend putting it into an Admin database (hey just name it Admin!) rather than in master. CREATE    PROC isp_DBCC_CHECKDB AS SET NOCOUNT ON DECLARE @dbid INT DECLARE @DBName SYSNAME DECLARE @SQL NVARCHAR(4000) SET @dbid = 0 WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases) BEGIN  SELECT TOP 1 @dbid = dbid, @DBName = name  FROM master.dbo.sysdatabases  WHERE dbid > @dbid  ORDER BY dbid  SET @SQL = 'DBCC CHECKDB(' + @DBName + ')'  EXEC sp_executesql @statement = @SQL END RETURN 0 GO

posted @ Wednesday, June 16, 2004 2:36 PM | Feedback (7) | Filed Under [ SQL Server - Database Administration ]

Microsoft SQL Server 2000 System Table Map

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. I got my poster from our MS representative.  I don't have room in my office to hang it, so I'll probably use the online version now as pulling out that poster each time is a pain. 

posted @ Friday, April 02, 2004 4:41 PM | Feedback (20) | Filed Under [ SQL Server - Database Administration ]

T-SQL script to copy the last full backups of the user databases to a remote server

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? Backup history information is stored in system tables in the msdb database. I posted code in the comment section of Mark's blog that displays the last backup (full or transaction log) of the user databases. Using that code, you can copy the files to a remote server using xcopy and xp_cmdshell....

posted @ Friday, April 02, 2004 2:41 PM | Feedback (5) | Filed Under [ SQL Server - Database Administration ]

DBCC DBREINDEX

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.  DBCC DBREINDEX still needs to be run occassionally even on these types of systems.  For systems that have lots of tables, it can be tiresome to write out the DBCC DBREINDEX command for each of the tables.  To make it easier, you can run this to write out the statements for...

posted @ Monday, December 15, 2003 11:40 AM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Disable all jobs on a SQL Server

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.  As part of this process, we need to disable all jobs on our SQL Servers.  It doesn't take a whole lot of time to do this inside Enterprise Manager, but when your goal is to complete your work quickly so that the customer impact is minimal, you want to save all of the seconds...

posted @ Friday, October 17, 2003 1:41 PM | Feedback (3) | Filed Under [ SQL Server - Database Administration ]

Powered by:
Powered By Subtext Powered By ASP.NET