SQL Server - Database Administration
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...
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...
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. ...
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...
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 >...
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...
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.
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.
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.
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...
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...
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.
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...
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...
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.
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...
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...
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.
...
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...
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
...
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.
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
...
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...
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...
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...
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...
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...
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.
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!
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...
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...
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...
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...
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...
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.
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...
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,...
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.
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 ...
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...
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.
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...
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 ...
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...
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...
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...
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) :...
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...
Check out this article for white papers, scripts, and tools on SQL Server 2005 best practices.
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...
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
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...
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...
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...
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.
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!
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...
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.
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 ...
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!
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....
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...
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...
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...
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...
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...
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...
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...
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...
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 =...
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...
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...
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...
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...
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
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.
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....
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...
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...