SQL Server 2005 SP3 Released

Microsoft has released SP3 for SQL Server 2005. You can download it here.

Read this knowledge base to find out the list of bugs that are fixed.

Disable or Enable Constraint

Issue
As there are 3 database environments such as development, testing and production, sometimes there is a request to refresh master or transaction tables in testing from production with specific period.

1st issue is sometimes some data already exists in testing. So I need to delete the existing data. For deleting data we have to delete starting from detail table to master table.

2nd issue is when inserting data from production to testing, we need to insert data starting from master table to detail table. If we do not follow this rule, the action will be failed due to foreign key constraint. It is little bit take time for DBA to analysis which the master table or detail table. For data movement from one to another environment which have identical schema, the fastest way is disable the constraints during the data movement and enable it back after it is finish. Below script only valid for check and foreign key constraint.

Script

Disable Constraints All Tables

sp_msforeachtable @command1 = "print 'Disable constraint on ? '" , @command2 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Enable Constraints All Tables

sp_msforeachtable @command1 = "print 'Enable constraint on ? '" , @command2 = "ALTER TABLE ? CHECK CONSTRAINT ALL"

After you move the data and enable the constraints, this enabling constraint doesn’t check the data that already exists in the table. So you will not receive any violation message though there is a violated data. Use DBCC CHECKCONSTRAINTS to check whether there is a violated data or not and just clean it manually.

Since the script enable/disable all the constraints and triggers, just make sure before you run the script, note the constraint already disabled before you want to enable all the constraints vice versa by running below script.

List All Constraints Status 

SELECT CASE WHEN OBJECTPROPERTY(constid, 'cnstisdisabled') = 0 THEN 'Enabled' ELSE 'Disabled' END AS Status ,O1.Name AS ConstraintName ,O2.Name AS TableName ,C1.name AS ColumnName ,O3.name AS ReferencedTable ,C2.name AS ReferencedColumn FROM sysforeignkeys FK INNER JOIN sysobjects O1 ON O1.id = FK.constid INNER JOIN sysobjects O2 ON O2.id = FK.fkeyid INNER JOIN syscolumns C1 ON C1.id = FK.fkeyid AND C1.colid = FK.fkey INNER JOIN sysobjects O3 ON O3.id = FK.rkeyid INNER JOIN syscolumns C2 ON C2.id = FK.rkeyid AND C2.colid = FK.rkey ORDER BY TableName, ConstraintName, FK.KeyNo

DTSBackup 2000

DTSBackup 2000 is a designed to help with both backup and transfer of DTS package. This version of DTSBackup provides full support for Microsoft SQL Server 2000. New features introduced in this version of DTSBackup include a new DTSBackup file format and direct transfer of packages between SQL Servers. These methods do not suffer the loss of layout information encountered when using the DTS object model.

For further information and download the tool just visit this link.

Beside DTS (Data Transformation Services) tool, there is SSIS (SQL Server Integration Services) tool also. Just visit this link.

This tool has helped me to transfer thousand DTS package when there is a request from developer to get DTS from production and put it into development. After relocate, just check all the package make sure there isn’t  information of production environment such as server name, user and password in development.

How to Set Database into Single User Mode

Today when I ran DBCC CHECKDB on my development server, I found errors on one database. When I tried to repair it, there is a message “Repair statement not processed. Database needs to be in single user mode.“

Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

To change a database into single user mode, we can use sp_dboption. But since this feature will be removed in future version after SQL Server 2000, we use ALTER DATABASE instead of.

Using T-SQL

Set to single mode: ALTER DATABASE [database name] SET SINGLE_USER; Set back into multi user mode: ALTER DATABASE [database name] SET MULTI_USER;

MASTER, MSDB and TEMPDB cannot be set into single user mode. For this you need to start the SQL SERVER into single user mode.

If the command ALTER DATABASE is failed, may be there are another sessions use this database. Use SP_WHO2 without active parameter to find out. Kill related spid that connecting to the database. If using SQL Server 2000, simple way is using EM, right click on the database and choose detach database. On detach database window just clear the connections than click cancel button (don’t detach the database). Close the EM and try to run the ALTER DATABASE again.

Using SQL Server Management Studio
1. Right click the related database then click properties.
2. Choose Option page
3. From Restrict Access option, select Single
4. Click OK and Open Connections dialog will appear. Click Yes if you want to close all connection connected to the database and change the database on single user mode.

Using Enterprise Manager
1. Right click the related database then click properties.
2. Choose Option page.
3. Tick Restrict Access and select Single User.
4. Click OK. If other users connect to the database, this process will be failed. You have to clear the connection manually and try again.

You can use SQL Server Management Studio to connect to database on SQL Server 2000. Since SQL Server Management Studio is able close all connection when we try to change database to single user mode, it is more simple than we use Enterprise Manager.

How to Get SQL Server Version?

Last time I faced problem with Sharepoint 2003 search. It is related to SQL Server 2000 Full Text Catalog. When I tried to get support from Microsoft, they do not support SQL Server 2000 SP3 anymore. Because of this cause, finally we upgraded our SQL Server 2000 to SP4. Yes, there are some problems after upgrading but my Sharepoint problem resolved by this upgrading and I am able to get support from Microsoft again if there is an issue on my database server in the future.

Before apply Service Pack, do not do it directly on production environment. Do it on development and testing environment first if possible. Find out other DBA issues from forum and prepare the solution. Prepare the worst case to downgrade the Service Pack ( usually the SQL Server need to reinstall)

Below script is to get version of the SQL Server

SELECT SERVERPROPERTY('servername') AS ServerName , SERVERPROPERTY('edition') AS Edition , SERVERPROPERTY('ProductVersion') AS ProductionVersion , SERVERPROPERTY('ProductLevel') AS ProductLevel

Values return on ProductLevel:

1. ‘RTM’ : Original Release Version

2. ‘SPn’ : Service Pack Version

3. ‘Bn’ : Beta Version

Moving Database File To Another Disk

All our databases server using SAN (Storage Area Network) for the disk storage. Last time we used DELL AX100 for the storage. After that we upgraded to DELL MD3000.  DBA was asked to move the databases to the new disk.

We have hundred of databases on one server. The best way to do this is using detach and attach method. In this case, we want to move all of user databases, so what we did is just detach all databases, stop the SQL Server, move the data file to the new disk, start the SQL Server and attach all the data files. We need downtime quite long to copy the files to new storage. If you does not allow to downtime quite long, just detach and attach the databases one by one. In my case, it is fine to downtime about 1 hour .

What I want to share is a script to generate T-SQL for detaching and attaching the database. Can you imagine if we use Enterprise Manager to detach and attach thousand or hundred databases ?

Step by step to move the data file

1. Before you stop the SQL Server, run below script to generate attach script for all user databases.

SET NOCOUNT ON DECLARE @dbname SYSNAME DECLARE @sqlstr VARCHAR(4000) DECLARE @filename VARCHAR(4000) DECLARE @result VARCHAR(8000) DECLARE @Counter TINYINT DECLARE @NFiles TINYINT IF OBJECT_ID('tempdb..#db') IS NOT NULL DROP TABLE #db --exclude system databases SELECT name INTO #db FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','tempdb','msdb') ORDER BY name IF OBJECT_ID('tempdb..#files') IS NOT NULL DROP TABLE #files CREATE TABLE #files ( DbName SYSNAME, LogicalName SYSNAME, FileId INT, FileName VARCHAR(1000), ) IF OBJECT_ID('tempdb..#result') IS NOT NULL DROP TABLE #result CREATE TABLE #result ( sql VARCHAR(8000) ) --get all files WHILE (1 = 1) BEGIN SET @dbname = NULL SELECT TOP 1 @dbname = name FROM #db IF @dbname IS NULL GOTO _FINISH1 SET @sqlstr = ' USE ' + @dbname + ' INSERT INTO #files (DbName, LogicalName, FileId, FileName)' + ' SELECT db_name(), name, fileid, filename ' + ' FROM sysfiles ' + ' ORDER BY fileid ' EXEC (@sqlstr) DELETE FROM #db WHERE name = @dbname END _FINISH1: --generate attach script WHILE (1 = 1) BEGIN SET @dbname = NULL SELECT TOP 1 @dbname = DbName FROM #files ORDER BY DbName IF @dbname IS NULL GOTO _FINISH2 SET @Counter = 1 SELECT @NFiles = COUNT(*) FROM #files WHERE DbName = @dbname SET @result = 'exec sp_attach_db N''' + @dbname + '''' WHILE (@Counter <= @NFiles) BEGIN SELECT @Filename = FileName FROM #files WHERE DbName = @DbName AND FileId = @Counter SET @result = @result + ',@filename' + CAST(@counter as VARCHAR) + ' = N''' + RTRIM(@FileName) + '''' SET @Counter = @Counter + 1 END INSERT INTO #result SELECT @result DELETE FROM #files WHERE DbName = @dbname END _FINISH2: SELECT * FROM #result DROP TABLE #db SET NOCOUNT OFF

This is an example of the result of above script for AdventureWorks and AdventureWorksDW.

exec sp_attach_db N'AdventureWorks',@filename1 = N'D:DataAdventureWorks_Data.mdf' ,@filename2 = N'E:LogAdventureWorks_Log.ldf' exec sp_attach_db N'AdventureWorksDW',@filename1 = N'D:DataAdventureWorksDW_Data.mdf' ,@filename2 = N'E:LogAdventureWorksDW_Log.ldf'

In my local PC, I put all data files (*.mdf, *.ndf) in D:DATA and log files (*.ndf) in E:LOG

Keep this result to another text file

2. Detach All User Databases
Run below query than run the result of this query to detach all user databases. To remainder, detach process will fail if there is an application that using this database. Just make sure there is no process hold this database if detach process is failed. If you detach database using Enterprise Manager, you will see how many connection that holding specified the database. Just clear the connection if you want to detach that database immediately without want to wait the process is done.

--exclude system databases SELECT replace('exec sp_detach_db ''@dbname'',''true''','@dbname', name) AS result FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model')

3. Stop SQL Server Database Engine

4. Move all data files and log files to the new disk

5. Start SQL Server Database Engine

6. From result of step 1, change old path with new path e.g.: D:DATA be F:SQLDATA than run this script to attach back all databases.

Index Fragmentation in SQL 2005

Last Tuesday, our email administrators had a problem. One of their 3rd tools became slow from one day to another day. After they contacted the vendor, the vendor asked to check the database which is using SQL 2005. Because our email administrators do not have knowledge about database, it’s time for DBA take an action .

Some companies think that they don’t need a DBA. These days almost all windows applications using SQL Server as their database. Unfortunately almost of these applications do not provide a task for maintenance their own database. My company has many 3rd party applications that using SQL Server. If there is a performance issue, the vendor always ask the customer to check the database and the box health. So as DBA is possible that we have to maintenance an application of another section like email, citrix, proxy, web application, sharepoint, K2 workflow, Business Object etc besides our in house database application .

After this issue came up, I ask the email admin to granted me as sysadmin role to their database which is located on their box. When I checked the index fragmentation, some of indexes were very fragmented.

Detecting Index Fragmentation
In versions prior to SQL Server 2005, for displaying fragmentation information for the data and indexes of specified table or view using DBCC SHOWCONTIG command. We still can use this command for SQL Server 2005 with some limitations but this feature will be removed in the next version according to MSDN. For the replacement, SQL Server 2005 and next version provide dynamic management function to display information about index fragmentation is sys.dm_db_index_physical_stats. This function need parameters such as database id, table id, index id, partition number and the mode. For further information just visit the MSDN or see SQL Server book online. There are many enhancement from this function than DBCC SHOWCONTIG command such as handle index partition information which is a new feature on SQL Server 2005.

The most important information from this function is column avg_fragmentation_in_percent. The range value from 0 to 100. 100 means the index is fragmented and DBA should reorganize or rebuild the index.

Here is an example how to display the index fragmentation for active database. From function sys.dm_index_physical_stats join to table sys.objects to get the table name, join to sys.indexes to get the index name and current fill factor setting and join to sys.partitions to get the approximate number of rows in the index partition.

SELECT db_name() AS DatabaseName , B.name AS TableName , C.name AS IndexName , C.fill_factor AS IndexFillFactor , D.rows AS RowsCount , A.avg_fragmentation_in_percent, A.page_count FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.objects B ON A.object_id = B.object_id INNER JOIN sys.indexes C ON B.object_id = C.object_id AND A.index_id = C.index_id INNER JOIN sys.partitions D ON B.object_id = D.object_id AND A.index_id = D.index_id

Reorganize OR Rebuild Index
In SQL Server 2000, we use DBCC INDEXDEFRAG to reorganize index and DBCC DBREINDEX to rebuild index. This feature is still available on SQL Server 2005 but this feature will be removed in future version of Microsoft SQL Server. In SQL 2005 and next version, these commands will be replaced with ALTER INDEX command. For reorganize the index use REORGANIZE option and for rebuild the index use REBUILD option with ALTER INDEX command.

Rebuild : ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD; Reorganize : ALTER INDEX [IndexName] ON [SchemaName].[TableName] REORGANIZE;

If you have reorganized or rebuilt the index, you can check again avg_fragmentation_in_percent and the page_count value will reduce.
Reorganize index uses minimal system resources and other processes such as update, insert and delete on this table won’t be blocked.  But if you rebuild the index, other processes on this table won’t be proceeded until the rebuild process is finished.
Rebuild the index is better rather than reorganize the index. But if it is hard to find the right time to rebuild the index especially the huge table so just reorganize the index. It is much better rather doing nothing .

Online Rebuild Index
When working on SQL Server 2000, as our production server must online 24×7, it is little difficult to do rebuild index on big table because during this process other processes will not be able to do DML activities on this table. This is a major hindrance of rebuilding index.

SQL Server 2005 introduces the ability for DBA to do online rebuild index so other processes can access the table when the rebuild is occurring. This feature is really helpful so we can rebuild our index any time. But of course it is better to do it on off peak hours . When doing online rebuild index, just make sure that you have enough disk space on your box because during this process, actually SQL Server create a new index that mimics of current index. After this processing is finished, the old index will be dropped and replace with the new one.

Online rebuild index is available only on SQL Server 2005 Enterprise Edition.

Online Rebuild Index : ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD WITH (ONLINE = ON);

Best Practice
When collecting index fragmentation status, it is recommended to keep the data in table. Add timestamp column to this table. Just schedule this task to do it either weekly or monthly.

Just modified above query for detecting index fragmentation. You can make it loop to all database and keep the result on DBA database. We have a database called SQLDBAdb to keep all information that we need to keep and review.

From this data we can review how fast the indexes become fragmented so we can adjust the fill factor of the index. (See fill factor information from BOL for further information). To see current setting fill factor of the index, we can query from table sys.indexes. To change fill factor value of the index, use ALTER INDEX with FILLFACTOR option.

Mapping System Objects From SQL 2000 to Next SQL Server Version

As a SQL Server DBA, knowledge of system objects is necessity. On SQL Server 2000 we may know system objects such as dbo.sysdatabases on master database, dbo.sysobjects on each databases etc. I use these objects repeatedly when creating a script for monitoring and also for maintenance databases. Unfortunately when microsoft launch SQL Server 2005 or 2008, some system objects are changed.
Do I need to change all my scripts that using those system objects? For a while the answer is no, For backward compatibility, SQL 2005/2008 provide system view which is have a name same with system object name on SQL Server 2000. Microsoft does not recommend to use this system view because this feature will be removed in a future version of SQL Server. But for a while some of my scripts can run both SQL 2000 or SQL next version.

To test this just type sp_helptext sysdatabases at master database on SQL 2005. You will find this  view query data from sys.databases table. But when I am trying query from master.sys.objects there is no object sysdatabases

Mapping System Objects

Category	SQL 2000	SQL 2005/08	description
Databases and files	sysdatabases	sys.databases	Returns one row for each database in the server
	sysfiles	sys.database_files	Return one row for each file of the database
	sysaltfiles	sys.master_files	Contains a row per file of a database as stored in the master database
	sysfilegroups	sys.filegroups	Contains a row for each data space that is a filegroup
	sysdevices	sys.backup_devices	Contains a row for each backup-device registered by using sp_addumpdevice or created in SQL Server Management Studio
Object	sysobjects	sys.objects	Contains a row for each user-defined, schema-scoped object that is created within a database
	syscolumns	sys.columns	Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns: * Table-valued assembly functions (FT) * Inline table-valued SQL functions (IF) * Internal tables (IT) * System tables (S) * Table-valued SQL functions (TF) * User tables (U) * Views (V)
	syscomments	sys.sql_modules	Returns a row for each object that is an SQL language-defined module. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view
	syslanguages	sys.syslanguages	Contains one row for each language present in the instance of SQL Server
	sysmessages	sys.messages	Contains a row for each message_id or language_id of the error messages in the system, for both system-defined and user-defined messages
	sysconstraints	sys.check_constraints sys.default_constraints sys.key_constraints sys.foreign_keys
	sysindexes	sys.indexes sys.partitions sys.allocation_units sys.dm_db_partition_stats
Process	syslockinfo	sys.dm_tran_locks	Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted
	sysprocesses	sys.dm_exec_connections sys.dm_exec_sessions sys.dm_exec_requests
	sysperfinfo	sys.dm_os_performance_counters	Returns a row per performance counter maintained by the server
	syslocks	sys.dm_tran_locks	Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.
Security	syslogins	sys.server_principals sys.sql_logins
	sysmembers	sys.database_role_members	Returns one row for each member of each database role
	syspermissions	sys.database_permissions sys.server_permissions
	sysusers	sys.database_principals	Returns a row for each principal in a database
Link Servers	sysservers	sys.servers	Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0

For complete system objects just visit
http://msdn.microsoft.com/en-us/library/ms187997.aspx