May 2008 Blog Posts
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
ALTER DATABASE dbName SET SAFETY FULL
ALTER DATABASE dbName SET PARTNER FAILOVER
--Run on new principal
ALTER DATABASE dbName SET SAFETY OFF
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.