Ramblings of a DBA

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

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

May 2008 Blog Posts

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 ]

Powered by:
Powered By Subtext Powered By ASP.NET