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 →
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:
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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).
Read more →
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.
Read more →
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
Read more →
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:
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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).
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →