Tuesday, May 06, 2008
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 for more information.
UPDATE: We were unable to find a case where the job would show success, yet data corruption exists. Check out Paul's blog and the thread that I mentioned for more details.
Sunday, May 04, 2008
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.
Tuesday, April 29, 2008
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:
Setup failed to start on the remote machine. Check the Task Scheduler event log on the remote machine.
Please refer to this Knowledge Base article for more information.
I get this error every now and then and usually can't remember how to resolve it. Maybe I'll be able to find my own blog entry when I search on the error!
Monday, April 28, 2008
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. Since I didn't think this should be the case for a few more months, I looked into each folder to see if any database backup was larger than it should be. That's when I noticed that our msdb backups were 6 gigabytes in size.
Typically the msdb database is larger than it should be due to backup history, but we already have a job to purge that data. The job was successful the last time it ran, so I decided to find out if there were any other tables that had an unusual amount of data in them. SQL Server 2005 makes this easy for us by providing the "Disk Usage by Top Tables" report in Management Studio. When I viewed this report, I noticed that the sysmail_attachments system table was at the very top. It showed that the table was consuming 6 gigabytes of space.
The sysmail_attachments system table is used by Database Mail to store the email attachments. We have a job that runs hourly that sends an email with an attachment to several people. The table only had a few thousand rows in it, but it took several minutes to delete everything that was older than a couple of days. While the delete was running, I checked Books Online to see if there was a system stored procedure to do the purge or if I was going to need to write my own. I found sysmail_delete_mailitems_sp.
If you send attachments via Database Mail, I would recommend that you create a job that runs sysmail_delete_mailitems_sp on a scheduled basis, perhaps daily or weekly. This recommendation can also be found in Books Online in the "Remarks" section of the "sysmail_delete_mailitems_sp" topic.
Here is an example of what to put in the job step:
DECLARE @d datetime
SET @d = DATEADD(dd, -5, GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @d
Tuesday, December 18, 2007
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. We implemented them on our production clusters and therefore needed a way to monitor them. We are now monitoring them with MOM via a Veritas management pack, but before that was implemented we used a CLR stored procedure that I wrote. It is written in C#.
The code allows you to get disk space information for the local server or for remote servers. You just need to pass the hostname to the stored procedure.
NOTE: I didn't get a chance to add error handling to this. I'd love to hear your suggestions on how better to write this, but please be respectful. I'm only a DBA!
C# code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void isp_DiskSpace(string serverName)
{
if (serverName == "")
{
serverName = Environment.MachineName;
}
PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName);
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Drive/MountPoint", SqlDbType.NVarChar, 256),
new SqlMetaData("Capacity (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Used Space (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Free Space (MB)", SqlDbType.VarChar, 256),
new SqlMetaData("Percent Free Space", SqlDbType.VarChar, 6));
SqlContext.Pipe.SendResultsStart(record);
foreach (string instanceName in pcc.GetInstanceNames())
{
PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);
PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);
float percentfree = pcPercentFree.NextValue();
float freespace = pcFreeMbytes.NextValue();
float capacity = (freespace * 100) / percentfree;
float usedspace = capacity - freespace;
if (instanceName != "_Total")
{
record.SetSqlString(0, instanceName);
record.SetSqlString(1, capacity.ToString());
record.SetSqlString(2, usedspace.ToString());
record.SetSqlString(3, freespace.ToString());
record.SetSqlString(4, percentfree.ToString());
SqlContext.Pipe.SendResultsRow(record);
}
}
SqlContext.Pipe.SendResultsEnd();
}
};
Once you've compiled the code into a DLL, run the below to get it setup inside SQL Server.
T-SQL code:
USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
ALTER DATABASE Admin SET TRUSTWORTHY ON
GO
USE Admin
GO
CREATE ASSEMBLY DiskSpace
FROM 'C:\SQLTools\DiskSpace.dll'
WITH PERMISSION_SET = UNSAFE
GO
CREATE PROC dbo.isp_DiskSpace @serverName nvarchar(4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME DiskSpace.StoredProcedures.isp_DiskSpace
GO
Example calls:
--local server
EXEC dbo.isp_DiskSpace @serverName = ''
or
EXEC dbo.isp_DiskSpace @serverName = 'LocalServer'
--remote server
EXEC dbo.isp_DiskSpace @serverName = 'RemoteServer'
Wednesday, November 28, 2007
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. In my version, you provide the database name as an input parameter. I recently received a comment that someone had modified my code so that it did not have this input parameter and instead had code added to it that looped through the databases to be defragmented. The disadvantage to this is that I update my code occassionally to fix bugs or to improve it. It is much easier to grab my update as is than to incorporate my code changes into your modified version.
Here is the code that I use in my "Defragment Indexes" job:
SET NOCOUNT ON
DECLARE @dbName sysname, @rc int
SELECT name
INTO #db
FROM sys.databases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb', 'Admin', 'OnePoint') AND
name NOT LIKE '%ReportServer%' AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
SELECT @rc = 1, @dbName = MIN(name)
FROM #db
WHILE @rc <> 0
BEGIN
EXEC Admin.dbo.isp_ALTER_INDEX
@dbName = @dbName,
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
SELECT TOP 1 @dbName = name
FROM #db
WHERE name > @dbName
ORDER BY name
SET @rc = @@ROWCOUNT
END
DROP TABLE #db
There are many ways to achieve this same functionality. You could also use sp_MSforeachdb to do the looping, however we want more control than that as we want the ability to exclude databases such as the system ones.
Tuesday, November 27, 2007
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. Use this new 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.
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. Kalen Delaney mentions this in the May 2006 edition of SQL Server Magazine (InstantDoc ID 49769).
-------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_ALTER_INDEX
--
-- AUTHOR : Tara Kizer
--
-- DATE : February 27, 2007
--
-- INPUTS : @dbName - name of the database
-- @statsMode - LIMITED, SAMPLED or DETAILED
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
--
-- OUTPUTS : None
--
-- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,
-- sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION : Defragments indexes
/*
EXEC isp_ALTER_INDEX
@dbName = 'DatabaseName',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
*/
-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[isp_ALTER_INDEX]
(
@dbName sysname,
@statsMode varchar(8) = 'SAMPLED',
@defragType varchar(10) = 'REORGANIZE',
@minFragPercent int = 25,
@maxFragPercent int = 100,
@minRowCount int = 0
)
AS
SET NOCOUNT ON
IF @statsMode NOT IN ('LIMITED', 'SAMPLED', 'DETAILED')
BEGIN
RAISERROR('@statsMode must be LIMITED, SAMPLED or DETAILED', 16, 1)
RETURN
END
IF @defragType NOT IN ('REORGANIZE', 'REBUILD')
BEGIN
RAISERROR('@defragType must be REORGANIZE or REBUILD', 16, 1)
RETURN
END
DECLARE
@i int, @objectId int, @objectName sysname, @indexId int, @indexName sysname,
@schemaName sysname, @partitionNumber int, @partitionCount int,
@sql nvarchar(4000), @edition int, @parmDef nvarchar(500), @allocUnitType nvarchar(60),
@indexType nvarchar(60), @online bit, @disabled bit, @dataType nvarchar(128),
@charMaxLen int
SELECT @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber,
index_type_desc AS IndexType,
alloc_unit_type_desc AS AllocUnitType,
0 AS Online
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE
avg_fragmentation_in_percent > @minFragPercent AND
avg_fragmentation_in_percent < @maxFragPercent AND
index_id > 0
ORDER BY ObjectId
-- LIMITED does not include data for record_count
IF @statsMode IN ('SAMPLED', 'DETAILED')
DELETE FROM #FragIndex
WHERE RecordCount < @minRowCount
-- Developer and Enterprise have the ONLINE = ON option for REBUILD
-- Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:
-- Disabled indexes, XML indexes, Indexes on local temp tables, Partitioned indexes,
-- Clustered indexes if the underlying table contains LOB data types,
-- Nonclustered indexes that are defined with LOB data type columns
IF @defragType = 'REBUILD' AND @edition = 3
BEGIN
UPDATE #FragIndex
SET Online = 1
UPDATE #FragIndex
SET Online =
CASE
WHEN IndexType = 'XML INDEX' THEN 0
WHEN IndexType = 'NONCLUSTERED INDEX' AND AllocUnitType = 'LOB_DATA' THEN 0
ELSE 1
END
-- we can't determine if the indexes are disabled or partitioned yet,
-- so we'll need to figure that out during the loop
-- we also have to figure out if the table contains lob_data when
-- a clustered index exists during the loop
END
SELECT @i = MIN(FragIndexId)
FROM #FragIndex
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@partitionNumber = PartitionNumber,
@indexType = IndexType,
@online = Online
FROM #FragIndex
WHERE FragIndexId = @i
WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM ' + @dbName + '.sys.objects o
JOIN ' + @dbName + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'
SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT
IF @indexType = 'CLUSTERED INDEX'
BEGIN
-- can't use online option if index is clustered and table contains following
-- data types: text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml
-- CHARACTER_MAXIMUM_LENGTH column will equal -1 for max size or xml
SET @sql = '
SELECT @online = 0
FROM ' + @dbName + '.INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = @objectName AND
(DATA_TYPE IN (''text'', ''ntext'', ''image'') OR
CHARACTER_MAXIMUM_LENGTH = -1)'
SET @parmDef = N'@objectName sysname, @online bit OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectName = @objectName, @online = @online OUTPUT
END
SET @sql = '
SELECT @indexName = [name], @disabled = is_disabled
FROM ' + @dbName + '.sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId'
SET @parmDef = N'
@objectId int, @indexId int, @indexName sysname OUTPUT, @disabled bit OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT, @disabled = @disabled OUTPUT
SET @sql = '
SELECT @partitionCount = COUNT(*)
FROM ' + @dbName + '.sys.partitions
WHERE [object_id] = @objectId AND index_id = @indexId'
SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT
SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +
@schemaName + '].[' + @objectName + '] ' + @defragType
IF @online = 1 AND @disabled = 0 AND @partitionCount = 1
SET @sql = @sql + ' WITH (ONLINE = ON)'
IF @partitionCount > 1 AND @disabled = 0 AND @indexType <> 'XML INDEX'
SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))
EXEC (@SQL)
SELECT @i = MIN(FragIndexId)
FROM #FragIndex
WHERE FragIndexId > @i
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@partitionNumber = PartitionNumber,
@indexType = IndexType,
@online = Online
FROM #FragIndex
WHERE FragIndexId = @i
END
DROP TABLE #FragIndex
Friday, November 16, 2007
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 table structure including constraints and indexes.
The application is written in Java and uses the JDBC driver for SQL Server from Inet software. The application does not specify the data types of the parameters in their queries and relies on the driver to do that for them. It turns out that the driver is choosing nvarchar(4000) for the Unit parameter. Once the application was modified to convert the parameter to varchar, CPU utilization dropped to below 5%.
Here is what the above query was changed to in the application:
SELECT Table1Id
FROM Table1
WHERE Unit = cast(? AS VARCHAR)
I am able to duplicate the issue with pure T-SQL. Please see below for a demonstration of the problem.
Setup the environment:
SET NOCOUNT ON
CREATE TABLE Table1
(
Table1Id int IDENTITY(1, 1) NOT NULL,
Unit varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (Table1Id),
CONSTRAINT uniq_Unit UNIQUE CLUSTERED (Unit)
)
INSERT INTO Table1 (Unit)
SELECT TOP 1000000 REPLACE(NEWID(), '-', '')
FROM SomeLargeTable
INSERT INTO Table1 (Unit) VALUES('abcdefghijklmnopqrstuvwxyz')
CREATE STATISTICS Stats1 ON Table1 (Table1Id) WITH FULLSCAN
CREATE STATISTICS Stats2 ON Table1 (Unit) WITH FULLSCAN
Turn on the option in SQL Server Management Studio to include the actual execution plan by hitting Ctrl+M or by selecting "Include Actual Execution Plan" in the Query menu.
Bad execution plan:
DECLARE @Unit1 nvarchar(50)
SET @Unit1 = 'abcdefghijklmnopqrstuvwxyz'
SELECT Table1Id
FROM Table1
WHERE Unit = @Unit1
Good execution plan:
DECLARE @Unit2 varchar(50)
SET @Unit2 = 'abcdefghijklmnopqrstuvwxyz'
SELECT Table1Id
FROM Table1
WHERE Unit = @Unit2
Here are the two execution plans together, ran as a batch to show cost (nvarchar/bad one is first):
Notice how the query that used nvarchar for the parameter costs 100% in the batch.
Cleanup the environment:
DROP TABLE Table1
This issue would have been avoided if the application used stored procedures or if it wasn't left up to the driver to determine what data type to use for the parameter.
Note: This occurs on the SQL collations only and not the Windows ones. Thanks Mladen for testing that out.
Thursday, November 08, 2007
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). Usually I wouldn't recommend shrinking the database, but this was a situation where it met the exception to the rule.
Since the amount of space to be reclaimed was around 50GB, I decided to run the shrink in smaller batches (1 GB each pass). After about an hour of shrinking the database down, I started to receive the following error:
File ID 1 of database ID 12 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
After much troubleshooting and googling, I threw in the towel and opened a case with Microsoft.
According to Microsoft, the problem was "DBCC SHRINKFILE was unable to run on the primary data file of the user database "DatabaseName" because a background operation which had obtained a latch for the required shrink operation previously was not released."
It was determined that a transaction log backup had run at the same time that the shrink was occurring which is what caused this latch problem.
There is no way to fix this issue except to restart the SQL Server service, since "such type of latches are in-memory structures" and "there are no DMVs in SQL Server 2005 which would allow us to view unreleased latches."
My recommendation to avoid this issue is to disable your transaction log backups while you are shrinking the database. Don't forget to re-enable it when you are done!
Monday, November 05, 2007
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. We haven't gotten to the bottom of it, but we are getting close.
Here's a great article that I stumbled upon recently that goes into great detail on how to troubleshoot performance problems in SQL Server 2005.
Monday, July 02, 2007
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!
Thursday, May 10, 2007
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".
Friday, April 20, 2007
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. I rebooted the nodes one at a time as is best practice for a cluster. After the reboots, I was able to successfully install the new instance.
At some point after the reboot, database mirroring had entered the suspended state for most of our databases at the primary site. This eventually caused us to run out of disk space at the primary site on the mount point where the transaction log exists as the log records keep accumulating at the principal. Backing up the transaction log does not fix this since database mirroring has not applied it at the mirror. You also can not truncate the transaction log, since this isn't allowed when the database is mirrored. The only thing I could do to fix the problem quickly was to break database mirroring.
I am not sure when database mirroring entered the suspended state as the Event Log had rolled over already, but I went under the assumption that the reboots caused the problem. I had planned on opening a case with Microsoft on this problem as soon as I could duplicate the issue.
Since we run production at our primary site, I am able to reboot the nodes at the DR site without impacting our applications. So I began attempting to duplicate the issue by rebooting the nodes one at a time. Each time the cluster groups failed to another node, I checked the state of database mirroring for each database. As expected, the state would be disconnected while SQL Server was failing over to another node. As soon as the cluster group came online, the state would change to synchronizing and then eventually synchronized. I then started rebooting 2-3 nodes at a time, but still database mirroring recovered fine. I decided to then take one of the cluster groups offline for several minutes. Database mirroring remained in the disconnected state while the cluster group was offline. I then brought it back online and database mirroring resumed without any problems.
In the end, I was unable to get database mirroring into a suspended state, so I am doubtful that the reboots that I did caused this problem. If anyone has any ideas as to possible culprits, please let me know so that I can test them.
Thursday, April 19, 2007
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."
I had bookmarked the page a few weeks back since we don't have service pack 2 installed in our production environment yet. If you've already used the reports to help resolve performance problems, let us know what you think of them.
Tuesday, April 17, 2007
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. While googling, I found this article. It goes into great detail about the best practices and performance considerations for database mirroring. I highly recommend reading it if you are using database mirroring or plan on using it.
I will be creating a new weblog that describes the database mirroring problem that we are experiencing.