DBA and SysAdmin World

December 22, 2008

SQL Server 2005 SP3 Released

Filed under: SQL Server — Tags: , , — solihinho @ 2:07 AM
doctor 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.


December 20, 2008

Disable or Enable Constraint

Filed under: SQL Server — Tags: , — solihinho @ 11:33 PM
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.


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"

Time outAfter 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.
Time out 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 

    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

December 14, 2008

DTSBackup 2000

Filed under: SQL Server — Tags: , , — solihinho @ 3:04 PM
sqldtslogo 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.

Thumps upThis 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.Time out 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

Filed under: SQL Server — Tags: , — solihinho @ 1:57 AM
Single user 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:

Set back into multi user mode:
Coffee MASTER, MSDB and TEMPDB cannot be set into single user mode. For this you need to start the SQL SERVER into single user mode.
Idea 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.

IdeaYou 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.

December 9, 2008

How to Get SQL Server Version?

Filed under: SQL Server — Tags: , — solihinho @ 3:34 AM
Computer_time_to_upgrade 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.

Nerd 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

   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

December 8, 2008

Moving Database File To Another Disk

Filed under: Script, SQL Server — Tags: , , — solihinho @ 3:09 PM
moveAll 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 Winking.

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.


DECLARE @dbname         SYSNAME
DECLARE @sqlstr         VARCHAR(4000)
DECLARE @filename       VARCHAR(4000)
DECLARE @result         VARCHAR(8000)
DECLARE @Counter        TINYINT

IF OBJECT_ID('tempdb..#db') IS NOT NULL

--exclude system databases
SELECT name INTO #db
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','tempdb','msdb')

IF OBJECT_ID('tempdb..#files') IS NOT NULL
   DROP TABLE #files

   DbName       SYSNAME,
   LogicalName  SYSNAME,
   FileId       INT,
   FileName     VARCHAR(1000),

IF OBJECT_ID('tempdb..#result') IS NOT NULL
   DROP TABLE #result

   sql VARCHAR(8000)

--get all files
WHILE (1 = 1)
   SET @dbname = NULL

   SELECT TOP 1 @dbname = name FROM #db	

   IF @dbname IS NULL

   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


--generate attach script
WHILE (1 = 1)
   SET @dbname = NULL

   SELECT TOP 1 @dbname = DbName
   FROM #files
   ORDER BY DbName 

   IF @dbname IS NULL

      SET @Counter = 1
      SELECT @NFiles = COUNT(*) FROM #files WHERE DbName = @dbname

      SET @result = 'exec sp_attach_db N''' + @dbname + ''''
      WHILE (@Counter <= @NFiles)
         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

      INSERT INTO #result
         SELECT @result

      DELETE FROM #files WHERE DbName = @dbname

      SELECT * FROM #result

      DROP TABLE #db


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'
Pumpkin 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.

December 6, 2008

Index Fragmentation in SQL 2005

Filed under: SQL Server — Tags: , , — solihinho @ 11:40 PM
fragment 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 Cool.

Money Eyes 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.

  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 Batting Eyelashes.

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 Winking. 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.

Coffee 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.

IdeaJust 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.

December 4, 2008

Mapping System Objects From SQL 2000 to Next SQL Server Version

Filed under: SQL Server — Tags: — solihinho @ 11:05 PM
next generation

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.

WhistlingTo 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  I dont know

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
sysindexes sys.indexes
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
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
sysmembers sys.database_role_members Returns one row for each member of each database role
syspermissions sys.database_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

Create a free website or blog at WordPress.com.