Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

SQL Server - General

SQL Saturday #47 in Phoenix

SQL Saturday is coming to Phoenix, Arizona on February 19th, 2011.  I have submitted my session for this event.  If my session gets approved, it’ll be the same presentation that I gave recently at SQL Saturday #55.  Although my recent presentation was well received, I will be reworking it to make it even better. Hope to see you there!

posted @ Monday, October 18, 2010 11:58 PM | Feedback (1) | Filed Under [ SQL Server - General ]

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 ]

SQL Saturday #55 in San Diego

SQL Saturday is coming to my hometown, San Diego, on September 18th, 2010.  I have submitted my session and hope that it gets approved.  Let me know if anyone is attending the event in San Diego.  If you aren’t in Southern California, check their site for the location nearest you. 

posted @ Friday, July 16, 2010 3:16 PM | Feedback (3) | Filed Under [ SQL Server - General ]

What three events brought you here?

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...

posted @ Tuesday, January 19, 2010 10:14 AM | Feedback (4) | Filed Under [ SQL Server - General ]

SQL Server Magazine web article about a tool I wrote

My Database Growth Tracker tool is featured in a SQL Server Magazine web article that Kevin Kline wrote. Check out the web article here!

posted @ Thursday, October 08, 2009 11:20 AM | Feedback (2) | Filed Under [ SQL Server - General ]

FastCopy - fastest Windows copy product?

If you've ever had to copy large files on a Windows platform using the Windows copy method (copy/paste in Windows Explorer or copy/xcopy commands), then you know how slow it is.  Recently I had to setup database mirroring for a largish database, so I needed to copy the full backup to the mirror server.  The database is about 110 GB in size, but since we use Quest's LiteSpeed product, the full backup is just 35.8 GB in size.  I had heard about FastCopy, which claims to be the fastest Windows copy product, so I decided to do comparison tests.  ...

posted @ Tuesday, March 03, 2009 3:35 PM | Feedback (20) | Filed Under [ Other SQL Server - General ]

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 ]

SQL Server 2005 SP3 + SP3 CU1 is now available

Microsoft has released SQL Server 2005 service pack 3 and cumulative update package 1 for service pack 3.  For more information about SP3, check this out.  For more information about SP3 CU1, check this out.  If you upgrade to just SP3, your SQL Server version will be 9.00.4035.  If you upgrade to SP3 CU1, your version will be 9.00.4205. Make sure you also update Books Online on your client machine.  You can get the newest version of BOL here. 

posted @ Monday, December 15, 2008 9:11 PM | Feedback (5) | Filed Under [ SQL Server - General ]

1-click installation for SQL Server 2005 - FineBuild

Has anyone used SQL Server FineBuild before?  According to the web site, it "provides 1-click install and best-practice configuration of SQL Server 2005."  It doesn't mention anything about clusters though, which is what I'd be interested in as installing SQL Server on a cluster is a bit of a pain.  Do you know of other tools that simplify the installation of SQL Server?  Do they work on clusters?

posted @ Wednesday, August 13, 2008 2:41 PM | Feedback (2) | Filed Under [ SQL Server - General ]

SQL Injection Attacks

There's a lot of information out there on how to avoid SQL injection attacks, but I wanted to point you to this blog due to the recent increase in such attacks.  Thank you, Buck Woody, for bringing this to our (SQL Server MVPs) attention. 

posted @ Thursday, June 05, 2008 2:33 PM | Feedback (2) | Filed Under [ SQL Server - General ]

Data Types of Parameters and Execution Plans

In a previous blog, I mentioned a performance problem that I've been working on for a few weeks.  I can finally say that this issue has been resolved.  The problem was that the database server (SQL Server 2005) was running at 95%-100% CPU utilization at all times.  Here is the query that was causing our problem (object names have been changed):  SELECT Table1Id FROM Table1 WHERE Unit = ? We were receiving a bad execution plan for this query due to nvarchar being used for the Unit parameter.  The Unit column is defined as varchar(50) in the table.  See the below demonstration for the...

posted @ Friday, November 16, 2007 11:56 AM | Feedback (6) | Filed Under [ SQL Server - General ]

Microsoft MVP Award

I'm proud to announce that I have received the Microsoft MVP Award for my contributions to the SQL Server community.  I'd like to thank Ben Miller from Microsoft for the nomination.  Thanks Ben!

posted @ Monday, July 02, 2007 9:46 AM | Feedback (16) | Filed Under [ SQL Server - General ]

SQL Server "Katmai"

Microsoft announced yesterday that the new version of SQL Server, code-named "Katmai", will be delivered in 2008.  Check this out for information on "Katmai".

posted @ Thursday, May 10, 2007 4:18 PM | Feedback (4) | Filed Under [ SQL Server - General ]

32-bit/64-bit aliases

We don't use the default port for SQL Server for security reasons, so we usually have to explicitly tell the client how to connect to SQL Server by creating an alias on the client machine. There are many ways to create an alias on a client machine.  My favorite is via the registry as it allows me to save the values to a reg file so that we can quickly deploy them to other client machines.  Here is the location of these aliases: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo Yesterday I was configuring transactional replication between two databases on different servers.  The servers are on different...

posted @ Tuesday, April 10, 2007 10:23 AM | Feedback (0) | Filed Under [ SQL Server - General ]

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 ]

Management Studio slowness

We've been having performance problems on a new 64-bit SQL Server 2005 cluster in our test environment.  We opened a ticket with Microsoft to assist us with this.  After modifying several things, the only thing that was slow was Management Studio.  On both nodes of this cluster, it would take about 3-5 minutes to load Management Studio.  The first MS engineer suggested that we add the nosplash switch to the Management Studio shortcut, like this: This did not increase performance at all on either node.  The second MS engineer suggested that we uncheck two options in Internet Explorer's Advanced Security settings. ...

posted @ Thursday, October 05, 2006 10:47 AM | Feedback (6) | Filed Under [ SQL Server - General ]

Generating SQL Scripts in SQL Server 2005

In SQL Server 2000, you could generate a SQL script easily using the Generate SQL Script wizard.  You could get to it from pretty much anywhere from within Enterprise Manager.  For instance, you can get to it by right clicking on any object in the database, then selecting All Tasks, and then selecting Generate SQL Script.  In SQL Server 2005, it is now called Generate Scripts.  The only place that I can find where you can get to it is if you right click on the database, then selecting Tasks, and then selecting Generate Scripts.  The 2005 wizard does not...

posted @ Wednesday, September 20, 2006 4:47 PM | Feedback (10) | Filed Under [ SQL Server - General ]

Commenting Blocks of Code in SQL Server 2005

In Query Analyzer, we could easily comment blocks of code using Ctrl+Shift+C and uncomment blocks of code using Ctrl+Shift+R. These keyboard shortcuts do not work in Management Studio unless you switch the keyboard scheme to SQL Server 2000. In Management Studio, you can comment blocks of code using Ctrl+K, Ctrl+C and uncomment blocks of code using Ctrl+K, Ctrl+U.  These are the same keyboard shortcuts that you would use to comment blocks of code in Visual Studio .NET, so I'm sure that's why Microsoft decided to switch the shortcuts.  To see other keyboard shortcuts for T-SQL code in Management Studio, open a query window...

posted @ Wednesday, September 13, 2006 4:57 PM | Feedback (6) | Filed Under [ SQL Server - General ]

Red Gate Software

Last week, I received a new laptop.  For the past few days, I've been installing all of the applications that I need.  Today, I decided to tackle Red Gate's SQL Compare.  On my old laptop, I was running version 3.  I hadn't kept a copy of the installation, so I went to their site to see if I could download it.  I couldn't find version 3, so I decided to download the new version to see if my serial number would work as I wasn't sure if we had renewed our support contract.  My serial number didn't work in version...

posted @ Tuesday, June 20, 2006 4:02 PM | Feedback (2) | Filed Under [ SQL Server - General ]

SQL Debugging in SQL Server 2005

Some of our developers regularly debug stored procedures in SQL Server 2000.  In fact, they can't live without it.  The next version of their application will use SQL Server 2005. Today, one of the developers asked me how to use SQL Debugging in SQL Server 2005.  I barely even know how to use it SQL Server 2000, but I wanted to help him so I started reading about it on MSDN. In SQL Server 2000, I had to grant execute permission on master.dbo.sp_sdidebug extended stored procedure in order for them to debug T-SQL code.  According to this article, the connection account must...

posted @ Thursday, June 08, 2006 3:45 PM | Feedback (5) | Filed Under [ SQL Server - General ]

SQL Server 3rd Party Tools

Which SQL Sever 3rd Party tools do you use?  We've been using Red Gate's SQL Compare and SQL Data Compare for a few years now.  I rarely use the data compare tool though, but not because it's not a great tool.  We also use Quest's SQL LiteSpeed product.  It reduced our backup files and backup times by 75%!  The disk savings on our SAN was huge.  We are considering purchasing a few Idera products.  I've recommended their SQLschedule product to a group here that manages over 250 jobs across 2 database servers.  If you aren't using any 3rd Party tools, is...

posted @ Thursday, May 25, 2006 4:53 PM | Feedback (5) | Filed Under [ SQL Server - General ]

SQL Server 2005 to be launched the week of November 7

Paul Flessner, Senior Vice President at Microsoft, announced this morning in his Tech.Ed keynote that SQL Server 2005, Visual Studio 2005 and BizTalk Server 2006 will launch the week of November 7.  

posted @ Tuesday, June 07, 2005 9:41 AM | Feedback (7) | Filed Under [ SQL Server - General ]

SQL Server 2000 service pack 4

Service pack 4 for SQL Server 2000 was released this morning.  Check this out for details.

posted @ Friday, May 06, 2005 10:10 AM | Feedback (2) | Filed Under [ SQL Server - General ]

SQL Server 2005 Roadshow

Get Ready for SQL Server 2005 “Get the facts about migrating to SQL Server™ 2005 in one information-packed day in a city near you! SQL Server experts from Scalability Experts, DevelopMentor, and Hitachi Consulting will present practical, real-world information in three tracks—administration, development, and business intelligence. You can find answers to specific questions in the Ask the Experts session. You'll come away from the Get Ready for SQL Server 2005 Roadshow with a clear understanding of how to implement a best-practices migration to SQL Server 2005 and how to use SQL Server 2005's new capabilities to improve your database computing environment.“ I'll...

posted @ Thursday, April 14, 2005 4:22 PM | Feedback (0) | Filed Under [ SQL Server - General ]

Paging in SQL Server 2005

Over in the SQLTeam forums , we had a long discussion/debate about paging in SQL Server 2005 .  Adam Machanic shows us his solution to this problem.

posted @ Thursday, November 04, 2004 7:14 PM | Feedback (4) | Filed Under [ SQL Server - General ]

Auditing DDL in SQL Server 2005

Benjamin Jones shows us an example of using extended triggers to capture DDL statements.  Very cool! For those of you wondering...DDL - data definition language - examples: CREATE/ALTER/DROP TABLE, CREATE/DROP INDEX

posted @ Thursday, October 21, 2004 6:07 PM | Feedback (5) | Filed Under [ SQL Server - General ]

Trigger tip

A common mistake when first creating a trigger is thinking that the inserted or deleted tables will contain only one row.  This is not true.  However many rows were affected by the INSERT, UPDATE, or DELETE is how many rows will be in the trigger table.  So you must code the trigger to handle multiple rows.  In the forums of SQLTeam.com, we often see trigger code like this: CREATE TRIGGER trg_Table1 ON Table1 For UPDATEAS DECLARE @var1 int, @var2 varchar(50) SELECT @var1 = Table1_ID, @var2 = Column2FROM inserted UPDATE Table2SET SomeColumn = @var2WHERE Table1_ID = @var1 The above trigger will only work for the last row in...

posted @ Tuesday, September 14, 2004 3:54 PM | Feedback (5) | Filed Under [ SQL Server - General ]

SQL Server 2005 Beta 2 available for download for MSDN subscribers

If you've got an MSDN subscription, head on over to MSDN to download SQL Server 2005 Beta 2.  The iso image file is 595.5 MB.  I downloaded it this morning in under 30 minutes.  Now I've just got to install it. 

posted @ Monday, July 26, 2004 11:25 AM | Feedback (8) | Filed Under [ SQL Server - General ]

Top 10 Cool Things about SQL Server 2005 Express Edition

You've probably already heard by now about SQL Server 2005 Express Edition.  It's the next evolution of MSDE 2000.  Here's the Top 10 Cool Things about SQL Server 2005 Express Edition: Integrated common language runtime (CLR) support. Stored procedures and functions can be written using your favorite programming language. Native XML support. XCopy support for moving databases from one location to another. Robust and reliable installation using either graphical user interface (GUI) or silent modes. Automated servicing and patching. Deep integration with Visual Studio to provide access to rich data controls like the DataGridView, DataNavigator, and DataConnector. Support for databases up...

posted @ Tuesday, June 29, 2004 3:06 PM | Feedback (8) | Filed Under [ SQL Server - General ]

xp_cmdshell

Why is it that you can pass a built string to xp_cmdshell but you can't build your string at the same time.  What I mean is this: This is legal: DECLARE @cmd VARCHAR(255)DECLARE @ServerName SYSNAME SET @ServerName = 'Server1'SET @cmd = 'copy C:\temp\test.txt \\ + @ServerName + '\SomeShare\' EXEC master.dbo.xp_cmdshell @cmd   This is not legal: DECLARE @ServerName SYSNAME SET @ServerName = 'Server1' EXEC master.dbo.xp_cmdshell 'copy C:\temp\test.txt \\ + @ServerName + '\SomeShare\'  Output: Server: Msg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near '+'.Any comments?

posted @ Monday, March 29, 2004 2:22 PM | Feedback (18) | Filed Under [ SQL Server - General ]

Analog Clock

Duane Dicks has a script that can display an analog clock in Query Analyzer. Make sure your results are in text and your font is Courier.

posted @ Monday, March 15, 2004 1:46 PM | Feedback (3) | Filed Under [ SQL Server - General ]

SQL Server Books Online January 2004 Update

MS updated SQL Server 2000 Books Online again: http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en

posted @ Wednesday, January 21, 2004 12:13 PM | Feedback (2) | Filed Under [ SQL Server - General ]

Convert from/to Java time

I recently had to create a UDF that would convert a Java time value to a datetime value.  We have an application that stores dates and times as the number of milliseconds since Jan. 1, 1970.  Well that isn't very pleasant to the human eye when trying to debug things.  So, here is a function to convert it to datetime: CREATE FUNCTION udf_ConvertJavaTimeToDateTime(@milliseconds BIGINT)RETURNS DATETIMEAS BEGIN DECLARE @DateTime DATETIME SELECT @DateTime = DATEADD(s, @milliseconds/1000, '1970-01-01 00:00:00.000' ) RETURN @DateTime END   And here is a function to convert a datetime value to Java time: CREATE  FUNCTION udf_ConvertDateTimeToMSSince1970(@DateTime DATETIME)RETURNS BIGINTAS BEGIN DECLARE @seconds BIGINT SELECT @seconds = DATEDIFF(s, '1970-01-01 00:00:00.000', @DateTime) RETURN...

posted @ Monday, November 10, 2003 4:16 PM | Feedback (1) | Filed Under [ SQL Server - General ]

Controlled DELETE

So how do you DELETE thousands/millions of rows from a table without blocking anyone for long periods of time?  Well, here is an example: DECLARE @Count INT DECLARE @Error INT SELECT @Count = COUNT(*)FROM SomeTable MLEWHERE SomeDateColumn < (GETDATE() - 182) -- anything older than 6 months SET ROWCOUNT 10000 WHILE @Count > 0BEGIN BEGIN TRAN DELETE FROM SomeTableWHERE SomeDateColumn < (GETDATE() - 182) SELECT @Error = @@ERROR IF @@ERROR = 0 COMMIT TRAN ELSE ROLLBACK TRAN   CHECKPOINT SELECT @Count = COUNT(*)FROM SomeTableWHERE SomeDateColumn < (GETDATE() - 182) END SET ROWCOUNT 0SET NOCOUNT OFF

posted @ Wednesday, October 15, 2003 1:09 PM | Feedback (15) | Filed Under [ SQL Server - General ]

Powered by:
Powered By Subtext Powered By ASP.NET