Tuesday, January 19, 2010
Paul Randal recently blogged about three life-changing events that brought him to where he is today. His blog is a very interesting read and idea, and so I bring to you my three events that brought me to where I am today in my technical career as a SQL Server DBA.
Right out of high school and during the first two years of college, I worked as a student worker for the District Attorney's Office at the County of San Diego performing data entry and other forgettable tasks. During this job, I worked with a previous classmate whose mom worked in another division at the County of San Diego. It was communicated to her mom that I was interested in a technical career. The mom was able to get an interview for me with the DBA team in the Information Services division at the County of San Diego. The position was only as a student worker, but it was a technical position so I jumped at the opportunity when the job was offered to me. I remained a student worker in the DBA team until I graduated from college with a Bachelor of Science in Mathematics with emphasis in Computer Science. I was then hired full-time to be a Junior DBA in the same group.
I remained a Junior DBA until the IT department was outsourced to Computer Sciences Corporation. This is the second significant event. Rather than take the generous severance package that was offered to any employees who did not wish to be part of the outsourcing, I decided to instead transfer to CSC as an Intermediate DBA. CSC was located in San Diego, so I didn't need to move or commute far away. At CSC, I met my husband which is a very significant event in my life but not relevant to this blog.
After about two years at CSC, I was so unhappy with my management that it drove me to find another job. I had been interviewing for about six months without any success when I happened to be at an interview and saw a former co-worker there. It turns out that I was interviewing for his position which he had already vacated (he was just there visiting and picking up some of his things). I surely wasn't qualified for the position, but this chance encounter is the third significant event. He contacted me a few weeks later and let me know of a DBA position that had opened up in his group at the new company, Qualcomm Incorporated. I interviewed for it a few days later. Although they were looking for a Senior DBA, my former co-worker decided to take a chance with me since he had mentored me at the County of San Diego and was aware of my possibilities. They hired me as an Intermediate DBA. The rest is history.
I have been at Qualcomm for 8 years and love every minute of it. I am now a Staff DBA (one level higher than Senior). My mentor still works at the same company as me, however he has moved on to other things. I am eternally grateful to him for all of the things that I learned and for taking a chance when there were more seasoned candidates interviewing for the position.
If it weren't for my former classmate's mom landing me an interview for a student worker position in the DBA team, I don't know where I would be in my technical career. I suppose I would have eventually made it into a DBA position since the skills match me perfectly, but it probably would have been a long journey to get there.
If it weren't for transferring to the new company during the IT outsourcing, I may never have ended up at my current company where I absolutely love my job. I also wouldn't have met my husband.
And finally if it weren't for my mentor and former co-worker, I wouldn't be where I am today. I owe my career to him. Hopefully he accepts my gratitude instead of a monetary gift, haha!
Tuesday, December 29, 2009
I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server 2005 and 2008 databases. This new version includes one new feature, which I think is critical to SQL instances with multiple databases on them.
In the old version, when a backup failure was encountered such as due to lack of free disk space, the stored procedure would end without proceeding with the other databases. Since the stored procedure backs up databases alphabetically, you could end up with many missed backups. To be more clear, let’s say we have a SQL instance with three databases on it: ABC, JKL, and XYZ. If we encounter a backup failure on ABC, JKL and XYZ will not be backed up until ABC finally succeeds at a later run.
In the new version, I use Try/Catch logic when performing the backups. When a backup failure is encountered, I capture the database name into an “array” and then proceed with the next backup. At the end, I raise an error (RAISERROR) if there were any backup failures and in that error show which databases failed to backup. By using the RAISERROR statement, our SQL job will still fail so that we alert the DBA to a failed backup.
You can download the new version here.
Tuesday, November 24, 2009
A few weeks ago, we were preparing to move our SQL Server 2005 databases from a 4-node Windows 2003 cluster to three 2-node Windows 2008 clusters. On the new clusters, we noticed that Database Mail and the monitoring application for Database Mirroring weren’t working on four of the SQL Server 2005 instances that we had installed. For the monitoring issue, we found that we were missing all of the sys.dbmmonitor* system stored procedures in the msdb database. We had installed six SQL Server 2005 instances across the six servers using the same media on each, so we were very perplexed as to why the issue was happening on four of the instances and not on all six. After doing our own research and coming up dry, we decided to open a case with Microsoft Product Support Services (PSS).
The Microsoft engineer assigned to our PSS case determined that the issue was due to five of the server names being in lower case. This caused the Resource database to not get patched when we installed service pack 3 and then October’s critical SQL hotfix (MS09-062). The Resource database was still at the RTM build, 1399, but it should have been at build 4262 like everything else.
When we first heard what the root cause was, our reaction was to laugh as this sounded absurd. I confirmed the bug with another Microsoft engineer just to be safe. Unfortunately there is no bug fix for it, but there is a workaround/solution which is documented here. The workaround involves evicting the nodes with lower case server names, upper casing the server names, and then joining the fixed node back into the cluster. After this is complete, you then reapply the service pack or hotfix. We only needed to reapply the hotfix.
The reason why we only encountered the issues on four of the six SQL Server instances that we installed is because on our third cluster one of the nodes had its server name in upper case. SQL Server 2005, service pack 3, and MS09-062 were installed from the server whose name was in upper case. Had we performed the installations on the other node with a lower case server name, we would have encountered the issue there too. We decided to perform the workaround on the working cluster since we could run into this issue again if we forgot to perform the SQL Server 2005 installations from the server with an upper case name. You do not need to perform the workaround on any servers that are properly named in all upper case.
Monday, November 09, 2009
You can only have one clustered index on each SQL Server table, however there are two ways to create pseudo clustered indexes on a table:
- Create clustered indexed on a view (indexed view) that covers the table
- Create covering index on the entire table
Let's look at the following table:
CREATE TABLE t1
(
c1 int,
c2 varchar(5),
c3 bigint,
c4 datetime,
CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (c1, c2, c3, c4)
)
GO
For option 1 above, here's the indexed view and clustered index:
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW v1 WITH SCHEMABINDING
AS
SELECT c1, c2, c3, c4 FROM dbo.t1
GO
CREATE UNIQUE CLUSTERED INDEX cidx_v1 ON dbo.v1(c2, c3, c4, c1)
GO
For option 2 above, here’s the covering index:
CREATE INDEX idx_PseudoClust ON t1(c3, c4) INCLUDE (c2, c1)
GO
So why would you want to do this? I'm sure that there are some very good reasons, but I've never had the need to do it. I recently had the discussion with a Microsoft engineer and thought it was interesting enough to share.
Tuesday, November 03, 2009
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 “XML INDEX” or “PRIMARY XML INDEX” for XML indexes in the index_type_desc column.
I don’t have any systems that use the XML data type, so it wasn’t until we had the SQL Server 2008 AdventureWorks database on a development system that we found the bug.
Here’s the code change in case you are interested:
SET @online =
CASE
WHEN @indexType IN ('XML INDEX', 'PRIMARY XML INDEX') THEN 0
WHEN @indexType = 'NONCLUSTERED INDEX' AND @allocUnitType = 'LOB_DATA' THEN 0
WHEN @lobData = 1 THEN 0
WHEN @disabled = 1 THEN 0
WHEN @partitionCount > 1 THEN 0
ELSE 1
END
You can download the new version of the stored procedure
here.
Thursday, October 08, 2009
My Database Growth Tracker tool is featured in a SQL Server Magazine web article that Kevin Kline wrote.
Check out the web article here!
Monday, September 21, 2009
After attaching or restoring a database to SQL Server 2005, you may encounter an error when executing a CLR object.
Error:
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65538. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.
This KB article describes the issue and shows the fix, however it says the bug occurs when the database is attached/restored to a different SQL instance. I’ve encountered the error three times now. In at least one occurrence of the error, it has happened when I’ve restored the database to the same SQL instance.
Fix:
USE master
GO
ALTER DATABASE dbNameGoesHere SET TRUSTWORTHY ON
USE dbNameGoesHere
GO
EXEC sp_changedbowner 'sa'
Thursday, September 10, 2009
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.
Tuesday, September 08, 2009
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 by Reporting Services, is in FULL recovery model by default, so this exclusion could have caused you to run out of space in its transaction log.
Bryan Conlon, one of my blog readers, noticed that the file retention code was not working when a database name had spaces in it. It took me a while to come up with a solution for this one, but I came up with a solution that involves an additional temporary table that stores just the file names and then synchronizes with the original temporary table that handles the dates. If you are wondering why I don’t just use the FORFILES command along with the DEL command which probably wouldn’t have this bug, it is because you can’t use UNC paths with FORFILES. I didn’t want to limit storage of backups to local or mapped drives. To see a file retention solution that uses FORFILES with DEL, check out this blog.
The archive bit option was added in case you don’t want to delete files that haven’t been backed up to tape. If you aren’t backing up your files to tape, then do not use this feature unless you are setting this option to true on the files. The default value of this new input parameter is false, so you don’t need to do anything if you don’t want to use it.
The COPY_ONLY option was added due to a feature request from one of my blog readers, “The Baking DBA”. This option allows you to perform a backup without impacting the normal sequence of backups. For more details on this, please check out the COPY_ONLY option in the BACKUP DATABASE topic in SQL Server Books Online.
I also dropped support for SQL Server 2000, so this new version only supports SQL Server 2005 and SQL Server 2008. I decided to drop SQL Server 2000 support from this new version, and all future versions, as keeping it in there was making it harder to release new versions. If you still need SQL Server 2000 support, like I do on a legacy system, then do not deploy this new version to those systems, simply use the last version.
You can download the new version here.
Thank you to Bryan Conlon for also testing and finding bugs in the “beta” version of the stored procedure. All bugs that were found have been fixed. Thanks Bryan!
Wednesday, September 02, 2009
For various reasons, you may need to manually remove SQL Server replication. Microsoft has a good KB article which shows how to do it. It has always worked in the past, but I ran into a situation a couple of weeks ago where it couldn’t remove the distributor via sp_dropdistributor. I don’t recall what error we were getting, but I didn’t have time to figure out what was wrong so with the help of a Microsoft engineer we were able to setup another distributor and then get replication working again. Later I tried “drop database” command on the original distribution database, but it said it couldn’t be dropped since it was being used for replication. It no longer was being used for replication, so it seemed to be orphaned.
Yesterday, I opened a case with Microsoft to see how I could get rid of the orphaned distribution database. It’s a simple fix:
use master
go
alter database distribution set offline;
drop database distribution;
Monday, August 31, 2009
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005 and 2008. The new version has a minor bug fix.
Recently I was comparing the fragmentation results between the DETAILED and SAMPLED modes of sys.dm_db_index_physical_stats and noticed that the DETAILED mode had “duplicate” entries for indexes. Due to this, my old code would have ran ALTER INDEX against the same index multiple times if you used DETAILED for @statsMode. To fix this, I simply added “index_level = 0” to the WHERE clause of the sys.dm_db_index_physical_stats query.
I consider this to be a minor bug as most people can not afford to run sys.dm_db_index_physical_stats in DETAILED mode.
You can download the new version of the stored procedure here. Let me know if you run into any issues with it.
Tuesday, June 23, 2009
UPDATE: A new version of this stored procedure is available. Please check it out here.
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005. The changes include two bug fixes and one feature request.
The first bug fix was reported by Fedor Baydarov. He found that @lobData was not being re-initialized to zero after a LOB data type was encountered. This meant that the rest of the indexes to be processed were being done offline even if the online option was available. D’oh!
The second bug fix is that it didn’t check for LOB data types of the included columns in a non-clustered index. The online option is not available for this type of index, so the bug caused the stored procedure to fail when such a condition was encountered. This bug fix might have been found by a blog reader, but I’m unable to find an email regarding it. I came across the bug recently on a system that had such an index.
The feature request was to add the option to do the sort operation in the tempdb database. This is recommended if your tempdb is optimized according to best practices, such as by having a tempdb data file for each of the CPUs. See ALTER INDEX topic in SQL Server Books Online for more details.
You can download the new version of the stored procedure here.
Let me know if you run into any issues with it. I’d also be interested to hear if it works on SQL Server 2008.
Wednesday, June 10, 2009
I was going through my scripts today and found one that I’d like to share.
This SQL Server script will display job history. The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly.
select job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
order by job_name, run_datetime
I know I stole the code from the inner-most derived table from someone, but I didn’t make a note of the source. I did a quick search for the source, but I came up with too many possibilities.
Tuesday, April 07, 2009
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 that are using any of my previous versions, you might need to modify the job step so that it uses the new @bkpSwType input parameter rather than the @liteSpeed parameter.
You can download the revised stored procedure here.
In the download, I have provided a script, ModifyJobStep.sql, to update the job step by replacing @liteSpeed with @bkpSwType. This only needs to be run if the job step includes @liteSpeed. You may not have this if you were using native backups since "@liteSpeed = N" was the default. If the script fails, run it one more time. There is some sort of SQL Server bug that is encountered on some systems, but it doesn't appear if you run it a second time. It is not a bug with the script though.
Let me know if you encounter any issues with this version so that I can get them fixed.
In the next version, I will add support for SQL Server 2008. It is a rather quick modification, but I need to do some additional testing of it before I release it to the Internet. If you'd like the next version before I finish my testing, just send me an email.
Friday, March 27, 2009
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment indexes in SQL Server 2005. The changes include one bug fix and one feature request.
The bug fix was simply to add "SET QUOTED_IDENTIFIER ON" to the script. You'd only see the bug in special circumstances, such as if you were using indexed views.
The feature request was to add the ability to log the fragmentation data. The logged data could be used to determine if you want to rearrange the index or perhaps add/remove columns to reduce fragmentation. The logged data is stored in a table called DefragmentIndexes. If you don't want to log the data, then you'll still need to create the table if you want to use this new version, otherwise the CREATE PROC statement would fail due to a missing object. To configure the stored procedure to log the data, simply pass 1 to the new input parameter, @logHistory. The default for this new input parameter is 0, so you do not need to modify your job if you don't care about this new feature.
You can download the new version of the stored procedure and the required table here. I put them in the same script to make it easier to deploy the new version.
Let me know if you run into any issues with it.