Thursday, December 24, 2009

DBCC log

DBCC log ( {dbiddbname}, [, type={01234}] )

PARAMETERS:Dbid or dbname - Enter either the dbid or the name of the database in question.type - is the type of output:

0 - minimum information (operation, context, transaction id)

1 - more information (plus flags, tags, row length)

2 - very detailed information (plus object name, index name, page id, slot id)

3 - full information about each operation

4 - full information about each operation plus hexadecimal dump of the current transaction log's default type = 0

To view the transaction log for the master database, you can use the following command:

DBCC log (master)

Tuesday, December 22, 2009


How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005:

Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage:

select * from sys.dm_os_performance_counters
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks

SELECT TOP 10 type,
SUM(single_pages_kb) as [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
ORDER BY SUM(single_pages_kb) DESC

(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb

Monday, December 21, 2009

Check Databases

USE [AutoISSUEdotNet]

USE [ReportServer]
DBCC CHECKDB(N'ReportServer', NOINDEX) -----noindex

SQL Server 2005 Error log manaagement

By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

To cycle or force a new error log to be created you can restart your SQL server or truncate the error log using the Database Consistency Check (DBCC) command DBCC ErrorLog as in the example here:

Exec ('DBCC ErrorLog')

Exec Sp_Cycle_Errorlog

By default, the SQL Server error logs are kept in the SQL Server's Log subfolder. By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6

Limit the number of the error log files before they are recycled
Check to limit the number of error logs created before they are recycled. A new error log is created each time an instance of SQL Server is started. SQL Server retains backups of the previous six logs, unless you check this option, and specify a different maximum number of error log files below.
Maximum number of error log files
Specify the maximum number of error log files created before they are recycled. The default is 6, which is the number of previous backup logs SQL Server retains before recycling them.To open the Configure SQL Server Error Logs dialog box
In Object Explorer, expand your server, expand Management, right-click SQL Server Logs, and then click Configure.

transaction log backup issue

In SQL Server 2005, you must perform a full database backup before you back up the transaction log for a database. Otherwise, when you try to back up the transaction log for a database, you receive the following error message:

Msg 4214, Level 16, State 1, Line 1 BACKUP LOG cannot be performed because there is no current database backup. Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.

Additionally, messages that resemble the following are logged in the SQL Server Errorlog file:
2006-10-10 11:16:05.63 spid52 Starting up database 'SampleDatabase'. 2006-10-10 11:16:05.92 spid52 Setting database option RECOVERY to FULL for database SampleDatabase. 2006-10-10 11:16:06.41 Backup Error: 3041, Severity: 16, State: 1. 2006-10-10 11:16:06.41 Backup BACKUP failed to complete the command BACKUP LOG SampleDatabase. Check the backup application log for detailed messages.

In SQL Server 2000, if you try to back up a transaction log before you back up the full database, you only receive a warning message that resembles the following:

The CREATE DATABASE process is allocating 0.63 MB on disk 'SampleDatabase'. The CREATE DATABASE process is allocating 0.49 MB on disk ' SampleDatabase_log'. There is no current database backup. This log backup cannot be used to roll forward a preceding database backup. Processed 1 pages for database ' SampleDatabase ', file ' SampleDatabase_log' on file 1. BACKUP LOG successfully processed 1 pages in 0.145 seconds (0.007 MB/sec). Additionally, a successful backup is reported in the SQL Server Errorlog file. The text in the log file resembles the following:

2006-10-10 11:11:04.31 spid51 Starting up database ' SampleDatabase '. 2006-10-10 11:11:04.80 backup Log backed up: Database: SampleDatabase, creation date(time): 2006/10/10(11:11:04), first LSN: 8:23:1, last LSN: 8:25:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TestTlog.trn'}).

The warning message indicates that you cannot use this transaction log backup for any restore operations if you do not have a full database backup.

Friday, December 18, 2009

powermt Manages a PowerPath environment.

powermt check ----Checks for, and optionally removes, dead paths.

powermt Command Syntax

powermt check [class=symmclariionall] [force]
[hba=hba#all] [dev=pathdeviceall]

powermt check_registration
powermt config
powermt display [pathsports] [class=symmclariionall]
powermt display [ports] [dev=deviceall]
[class=symmclariionall] [every=seconds]
powermt display options
powermt load [class=symmclariionall] [file=pathname]
powermt remove [class=symmclariionall] [force]
hba=hba#all dev=pathdeviceall
powermt remove [class=symmclariionall] [force]
hba=hba#all dev=pathdeviceall
powermt restore [class=symmclariionall] [force]
[hba=hba#all] [dev=pathdeviceall]
powermt save [file=pathname]
powermt set periodic_autorestore=onoff
powermt set mode=activestandby
[class=symmclariionall] [hba=hba#all]
powermt set policy=lilbrrre
[class=symmclariionall] [dev=deviceall]
powermt set policy=sonr [dev=deviceall] (Symmetrix only)
powermt set policy=bfco [dev=deviceall] (CLARiiON only)
powermt set priority=priority#
[class=symmclariionall] [dev=deviceall]
powermt version
powermt set write_throttle=onoff
[class=symmclariionall] [dev=deviceall]
powermt set write_throttle_queue=queue_depth#
[class=symmclariionall] [dev=pathdeviceall]

Backing up and restoring server clusters


insert time (CTRL+SHIFT+semicolon) and date (CTRL+semicolon)

Thursday, December 17, 2009

Full backup progress

Here is what happens on a full database backup

1. lock database blocking all transactions

2. place a mark in the tran log

3. release the database lock

4. back up all data pages in the database

5. lock database, mark transaction log, unlock database

6. write all the transactions between the two marks to the full backup file (but they also stay in the log file)

Wednesday, December 16, 2009

SQL Server 2005 clustering DM



cluster.exe Example

Cluster.exe is part of the default installation of Server 2008. But clusters can only created after the feature “Failover Clustering” has been installed. Otherwise an error message like the following will be displayed

C:\>cluster testcluster /create /ipaddress:

Creating a Cluster:

C:\>cluster testcluster /create /ipaddress:

Deleting/Destroying a Cluster:

C:\>cluster testcluster /destroy /y

Without the optional parameter /CleanupActiveDirectory the cluster’s computer account will not be deleted but only deactivated.

Shutting Down and Restarting a Cluster:

C:\>cluster testcluster /shutdown /y

move group:

cluster.exe Spann group "Cluster Group" /moveto:Shannon

cluster.exe Syntax (windows 2003):

Sql Server Integration Service in clustering

In Sql Server 2005, you cannot cluster Sql Server Integration Service (SSIS).
During database cluster install, you can pick to install Integration Service. SSIS will only be installed on the node where installation process starts from. For example, suppose you have Node1 and Node2, and the install is started from Node1, then database service and binaries will be installed on both nodes automatically, but SSIS will only be installed on Node1.

Now, suppose the database cluster name is VirtualDb. When it is running on Node1, to connect to Integration Service in Sql Server Management Studio, you can choose to connect to VirtualDb, because under the cover, you are really connecting to Node1, which does have SSIS installed. However, if VirtualDb happens to run on Node2, then you have a problem, because there is no SSIS on Node2. Instead, you will have the message below:

Connect to SSIS Service on machine “VirtualDb” failed:Class not registered——————————BUTTONS:

So, SSIS wise, you can only connect to Node1 or VirtualDb when it is running on Node1, but we are not done yet. You will get an error when you try to browse Stored Packages -> MSDB in Object Explorer. Below is the error you will get:
The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic “Configuring the Integration Services Service” in Server 2005 Books Online.Login timeout expiredAn 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.Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)

The reason is that the meta data of package location is controlled by a file located on Node1, and part of it is incorrect. If you browse through the path of Sql Server, look for
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. Open it up and you will see this piece of code:

Here, you can see that the ServerName is a dot, which means default instance of Sql Server on this local machine. To fix the problem you encountered earlier while browsing packages, you will need to change it to VirtualDb. After that, restart SSIS, and it will work for you in Management Studio. The dot here would have worked, had the database service been a non-clustered service. I do believe this should be treated as a Microsoft bug. Instead of just putting a dot here by the installer, it should put in the virtual database server name, when database service is installed as a cluster.

SSIS in a clustered environment

If you’re configuring SQL Server Integration Services (SSIS) in a clustered server environment, remember to change the value for the element in the SSIS configuration file to the virtual server name on each node of the cluster. If you don’t, SQL Server will probably throw this error:

SSIS MsDtsSrvr connection error
Login timeout expired. 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. Named Pipes Provider: Could not open a connection to SQL Server (MsDtsSrvr)

Run this query on the machine where you have SSIS installed:

select srvname from master.dbo.sysservers where srvid = 0

Navigate to the nodes of the machine where SSIS is installed and modify the configuration file:%Program Files%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml

You will have to replace the value of the element from a dot to the Virtual Server Name that was returned by the query because the dot resolves to the default SQL Server instance on the local server.

With SQL Server Named instances, change the value for the element in the MsDtsSrvr.ini.xml configuraton file.

Fire up SQL Server Configuration Manager, navigate to SQL Server 2005 Services and then restart SQL Server Integration Services.

Note: You may also have to configure remote access for Dcom Config for the MsDtsServer Properties (Security tab) in Component Services if running with a non-admin account.

Configuring the Integration Services Service :

Configuring Integration Services in a Cluster:

Monday, December 14, 2009

Microsoft Cluster Server (MSCS)

Microsoft Cluster Server (MSCS) uses poll intervals to determine the status of the resources it is protecting.

The "Looks Alive" poll occurs frequently, performing a quick check to verify that the resource is still running, while the "Is Alive" poll performs a more comprehensive check of the resource and occurs less frequently.

Pending Timeout value for the resource. This is the period of time that the resource can be in an Offline Pending or Online Pending state before MSCS fails the resource.

SQL Server database single user mode

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

A query that initiates an update of out-of-date statistics must wait for those statistics to be updated before compiling and returning a result set. This can cause unpredictable query response times and may cause applications that have aggressive time-outs to fail.

In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan. Queries that start after the updated statistics are ready will use those statistics. This may cause the recompilation of cached plans that depend on the older statistics version. Updating asynchronous statistics cannot take place if any data definition language (DDL) statements, such as CREATE, ALTER, and DROP statements, occur in the same explicit user transaction.

Before you set a database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. If the option is set to ON, perform the following tasks:


Alter database AdventureWorks set AUTO_UPDATE_STATISTICS_ASYNC OFF

2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

select * from

3. If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

This example shows how to terminate the statistics update associated with a job where the job_id = 53.


1.use master
select, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where = 'AdventureWorks'

2.kill 51

3.sp_dboption 'testdb01', 'single user', 'TRUE'
USE master;

the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks sample database will be immediately disconnected.

Friday, December 11, 2009

dtexec ---UWS

The following example shows how to run the same package and capture the return code:

DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'



/D[ts] package_path

(Optional). Loads a package from the SSIS Package Store. The package_path argument specifies the relative path of the SSIS package, starting at the root of the SSIS Package Store, and includes the name of the SSIS package. If the path or file name specified in the package_path argument contains a space, you must put quotation marks around the package_path argument.

The /DTS option cannot be used together with the /File or /SQL option. If multiple options are specified, dtexec fails.

/Ser[ver] server

(Optional). When the /SQL or /DTS option is specified, this option specifies the name of the server from which to retrieve the package. If you omit the /Server option and the /SQL or /DTS option is specified, package execution is tried against the local server. The server_instance value may be quoted.

/M[axConcurrent] concurrent_executables

(Optional). Specifies the number of executable files that the package can run concurrently. The value specified must be either a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.

/CheckP[ointing] {on\off}

(Optional). Sets a value that determines whether the package will use checkpoints during package execution. The value on specifies that a failed package is to be rerun. When the failed package is rerun, the run-time engine uses the checkpoint file to restart the package from the point of failure.
The default value is on if the option is declared without a value. Package execution will fail if the value is set to on and the checkpoint file cannot be found. If this option is not specified, the value set in the package is retained. For more information, see Using Checkpoints in Packages.
The /CheckPointing on option of dtexec is equivalent to setting the SaveCheckpoints property of the package to True, and the CheckpointUsage property to Always.

Thursday, November 26, 2009

AWE SQL Server 2005

To turn on AWE, there are three steps:

  1. Add the /pae switch to boot.ini to allow Windows 2000/2003 to access more than 4 GB of memory
  2. Grant “Lock Pages in Memory” to the user account that runs the SQL Server process
  3. Set the configuration setting “AWE Enabled” to 1.

sp_configure 'show advanced options', 1 RECONFIGURE GO  sp_configure 'awe enabled', 1 RECONFIGURE GO

sp_configure 'min server memory', 1024 RECONFIGURE GO  sp_configure 'max server memory', 6144 RECONFIGURE GO

Tuesday, November 24, 2009

Manually remove log shipping using T-SQL :

Manually, using T-SQL :

1. On the primary server, execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server.

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'AdventureWorks'
,@secondary_server = N'LogShippingServer'
,@secondary_database = N'LogShipAdventureWorks'

2. On the secondary server, execute sp_delete_log_shipping_secondary_database to delete the secondary database.

sp_delete_log_shipping_secondary_database N'LogShipAdventureWorks'

3. On the primary server, execute sp_delete_log_shipping_primary_database to delete information about the log shipping configuration from the primary server. This also deletes the backup job.

sp_delete_log_shipping_primary_database N'AdventureWorks'

Friday, November 20, 2009

Alter Index


Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.


Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.


All pages that contain large object data are compacted.

Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. For more information, see Creating Indexes with Included Columns.

When ALL is specified, all indexes that are associated with the specified table or view are reorganized, and all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.


Pages that contain large object data are not compacted.

OFF has no affect on a heap.

The LOB_COMPACTION clause is ignored if LOB columns are not present.


sp_updatestats effectively executes UPDATE STATISTICS, by specifying the ALL keyword, against all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables. Statistics on disabled nonclustered indexes are also updated by sp_updatestats. sp_updatestats ignores tables with a disabled clustered index.

In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.

For databases with a compatibility level below 90, executing sp_updatestats resets the automatic UPDATE STATISTICS setting for all indexes and statistics on every table in the current database. For more information, see sp_autostats (Transact-SQL). For databases with a compatibility level of 90 or higher, sp_updatestats preserves the automatic UPDATE STATISTICS setting for any particular index or statistics.

Alter index on SQL Server 2005 Standard Edition Errors

Online index operations can only be performed in Enterprise edition of SQL Server.