Cluster Diagnostics and Verification Tool (ClusDiag)

Cluster Diagnostics and Verification Tool (ClusDiag) is a graphical tool that performs diagnostics tests on a pre-production server cluster and creates log files to help system administrators identify configuration issues prior to deployment in a production environment.

Download details: Cluster Diagnostics and Verification Tool (ClusDiag.exe)

SQL 2005 SP2 templog is set to restricted and will not go back to unrestricted

It is only a presentation issue.

There's no such thing as "unlimited" in the end, since the max theoretical size
• for a data file is 32TB and
• for a log file is 2TB. 2,097,152 MB = 2 TB.
I.e., the value you see is the maximum theoretical size.

Although file size is displaying as "restricted", the MAXSIZE is indeed 2TB (2,097,152MB).
So, within the confines of capacity limitations, it is unlimited so to speak.

Working with tempdb in SQL Server 2005

SQLDebugger account

SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

Removing of the SQLDebugger account in SQL 2000

Automate SQL Server Profiler Trace

-- To create trace
exec @rc = sp_trace_create @TraceID output, 2, N'N:\SQLTrace\15FebTrace', @maxfilesize, NULL

## Note Option 2 here

--- To set trace status

exec sp_trace_setstatus @traceid, @status

where @status = 0 Stopped
1 Running
2 Closed, cleaned from server

---
To get the Traces running on the server

select * from ::fn_trace_getinfo(0)

---
To read trace content into a table
select * from ::fn_trace_gettable('N:\SQLTrace\15FebTrace.trc',default)

Terminal Server and Citrix Keyboard Shortcuts

Terminal Server and Citrix Keyboard Shortcuts

If working in an RDP or ICA Session the following are keyboard shortcut key combinations that map to the native Windows shortcut keystrokes.

CTRL+F3 Task Manager
CTRL+F2 Start Menu

SQL Profiler buffers the trace data in temporary files on the client machine

SQL Profiler buffers the trace data in temporary files on the client machine, and if the drive on which these temporary files reside doesn't have ample free space, a Profiler trace (e.g. with the standard trace template) could deplete the drive of the free space rather quickly and end up disabling the trace.


System is low on disk space on drive 'C'. All SQL Server profiler functions are temporarily disabled.

The temporary files to buffer the profiler trace data were in directory C:\Documents and Settings\Siva\local settings\Temp\1, and the names of the temporary files had this pattern: Prf*.tmp. Upon exiting SQL Profiler, these temporary files would be removed automatically.

Work around:

To work around this problem, do not use SQL Profiler to perform a client-side trace of the events in the instance of SQL Server. Instead, use system stored procedures to perform a server-side trace of the events.

You can use the following system stored procedures:

* sp_trace_create
* sp_trace_setevent
* sp_trace_setfilter
* sp_trace_setstatus

For more information about these system stored procedures, search for the stored procedures in SQL Server 2000 Books Online or in SQL Server 2005 Books Online.

Reference:

An instance of SQL Server performs slowly when you use SQL Profiler to perform a client-side trace of the events in the instance

Error while setting reporting service and share point exclusion path

Encountered the error while installing SQL 2005 Reporting Service "Error while setting reporting service and share point exclusion path"

Product : SQL Server 2005 Reporting Services

Error : The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation.

Resolution:
KB ID # 920803

Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.)

System.Data.SqlClient.SqlError: Cannot open backup device 'F:\foldername'. Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)


Then, I did a Check Name for "SQL" and added the following two SQL accounts from the list:

1)  SQLServer2005MSSQLUser$<machine name>$MSSQLSERVER

2)  SQL

Unicenter command to check the service

Unicenter command to check the service is installed on running on a server

awservices status

Multiple SQL Server Integration Services (SSIS) database instances on one machine

The registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile specifies
the location and name for the configuration file that Integration
Services service uses. The default value of the registry key is
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml.
You can update the value of the registry key to use a different name
and location for the configuration file.

Reference: Multiple SQL Server Integration Services (SSIS) database instances on one machine - Mike's Blog

SQL Server 2005 Service Pack 3 released

On Dec 15th 2008, Microsoft released the latest Service Pack 3 on SQL Server 2005. On the same day SP3 Cumulative Update 1 and Cumulative Update 11 for SQL Server 2005 Service Pack 2 (SP2) are also released.

• For SQL users not opting for SP3, Microsoft released Cumulative Update 11 for SQL Server 2005 SP2, which contains all of the hot fixes for SP2, without the new features in SP3.
  
• For SQL users upgrading from SQL Server 2005 SP2 Cumulative Update 10 or from SQL Server 2005 SP2 Cumulative Update 11, they must apply a post-SP3 cumulative update after upgrade to SP3 to obtain all the fixes
  

This Service Pack is available for download from Microsoft Download Center. Along with the Service Pack, refreshed the Feature Pack for 2005 that can be downloaded separately. The following are all the download links for SQL Server 2005 Service Pack 3 and related downloads.

SQL Server 2005 SP3 (Eval, Std, Dev, Ent Editions)	http://go.microsoft.com/fwlink/?LinkId=135960
SQL Server 2005 Express Edition SP3	http://go.microsoft.com/fwlink/?LinkId=135970
SQL Server 2005 Express Advanced SP3	http://go.microsoft.com/fwlink/?LinkId=135973
SQL Server 2005 Express Toolkit SP3	http://go.microsoft.com/fwlink/?LinkId=135974
SQL Server 2005 Feature Pack Dec 2008	http://go.microsoft.com/fwlink/?LinkId=135971
SQL Server 2005 Management Studio Express SP3	http://go.microsoft.com/fwlink/?LinkId=135772
SQL Server 2005 SP3 Data Mining Add-ins Page	http://go.microsoft.com/fwlink/?LinkId=135959
SQL Server 2005 SP3 Reporting Services Add-in Page for Microsoft Sharepoint	http://go.microsoft.com/fwlink/?LinkId=135962
SQL Server 2005 Books Online Refresh - December 2008	http://go.microsoft.com/fwlink/?LinkId=130167
What's New in SQL Server 2005 SP3	http://go.microsoft.com/fwlink/?LinkId=131442
Readme files for SQL Server 2005 SP3	http://go.microsoft.com/fwlink/?LinkId=130542
Release Notes for SQL Server 2005 SP3	http://go.microsoft.com/fwlink/?LinkId=12952
Cumulative update package 1 for SQL Server 2005 Service Pack 3	http://support.microsoft.com/kb/959195
An Incremental Servicing Model - ISM is available from the SQL Server team to deliver hotfixes for reported problems	http://support.microsoft.com/kb/935897

SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

INF: SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

The SQLDebugger Windows user account is created when the SQL Debugger Registry2 DCOM server process (Sqldbreg2.exe) is registered. By default, SQL Server 2000 SP3 registers this process, and the SQLDebugger Windows user account is created.


SQL Query Analyzer includes
T-SQL Debugger. By using T-SQL Debugger, you can control and monitor
how stored procedures run. T-SQL
Debugger uses the SQLDebugger Windows user account to connect to the database server.

Microsoft Visual Studio .NET applications use SQL Server Debugging to debug SQL Server stored procedures. The SQLDebugger Windows
user account is also created when you install Visual Studio
.NET.

The SQLDebugger Windows user account has the following characteristics :

• It belongs to the built-in Windows Users group.
• It does not have local logon rights.

How to remove / uninstall SQL Server 2005 Manually

Run the command to uninstall the SQL Server components
At a command prompt, run the following command:

"%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove"

Uninstall the SQL Server components one at a time until all the SQL Server components are uninstalled.

Note Add or Remove Programs also runs the ARPWrapper.exe program by using the /Remove option. However, the reference to the ARPWrapper.exe program may have been deleted.

Refer: http://support.microsoft.com/kb/909967

SQL Server 2005 - Cluster Tips

1. Installing Tools and Documentation
SQL Server 2005 Setup no longer installs the tools and documentation components of SQL Server 2005 on all the nodes of a cluster. The wizard will only install these
components on the node that the Setup routine is initiated from. If tools and documentation is required on the other nodes, you will need to install these components
manually after finishing the setup of the cluster.

2. Install SQL 2005 Analysis Services on separate Resource group
Microsoft recommends installing Analysis Services clustered installations in a single group with its own disk and IP resources and not on a resource group that has
the database engine installed on it.

3. Create Windows Domain Groups
It is usually recommended that you create four different domain groups when creating a cluster. One for each of the following:
1. SQL Server 2005 DB Service,
2. SQL Server 2005 Agent service,
3. SQL Server 2005 Full-Text Search service,
4. SQL Server 2005 Analysis Services service.

4.Installing Reporting Services
Unfortunately, Reporting Services is not cluster-aware and will have to be installed as stand-alone components on all nodes in the clustered environments. Reporting Services can make use of clustered databases. But when a node fails over the connections to any Reporting Services, instances on the failed node will not be automatically reconnected to the new node. Connections will have to attempt to connect to the new node using a new IP address

5. Installing SQL Server Integration Services
To install SSIS, you must install SSIS on all active nodes and then make the SSIS Service a clustered resource. To make SSIS a clustered resource, perform the following tasks:
1. Open the Cluster Administrator
2. On the File menu, point to New and then click Resource
3. On the New Resource page of the Resource Wizard, type a Name and choose “Generic Service” as the Service Type. Change the value of Group to SQL group. Click Next.
4. On the Possible Owners page, add or remove the nodes of the cluster as the possible owners of the resource. Click Next.
5. To add dependencies on the Dependencies page,select a resource under Available resources, and thenclick Add. In case of a failover, both SQL Server 2005
and the shared disk that stores Integration Services packages should come back online before Integration Services is brought online. After you have selected the
dependencies, click Next.
6. On the Generic Service Parameters page, enter MsDtsServer as the name of the service. Click Next.
7. On the Registry Replication page, click Add to add the registry key that identifies the configuration file for the Integration Services service.
a. This file needs to be located on a shared disk that is in the same group for the Integration Services service, and fails over to the next node along with for
the Integration Services service.
b. In the Registry Key dialog box, type SOFTWARE\Microsoft\MSDTS\ServiceConfigFile. Click OK, and then click Finish. The Integration Services service
has now been added as a clustered service.
8. Locate the configuration file at %ProgramFiles%\Microsoft SQL Server 2005\90\DTS\Binn\MsDtsSrvr.ini.xml. Copy it to the shared disk.
9. Create a new folder named Packages on the shared disk. Grant List Folders and Write permissions on the new folder to the built-in Users group.
10. Open the configuration file from the shared disk in a text or XML editor. Change the value of the ServerName element to the name of the virtual SQL Server 2005 which is in the same group.
11. Change the value of the StorePath element to the fully-qualified path of the Packages folder created on the shared disk in a previous step: ??:\Packages.
12. Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile in the Registry to the fully-qualified path and filename of
the service configuration file on the shared disk
13. In the Cluster Administrator, select the Integration Services service, right-click, and select Bring Online from the popup menu. The Integration Services service
is now online as a clustered service

How to Grant ALTER TRACE permission using Management Studio?

In SQL Server 2005 / 2008 to grant SQL Profiler access for a user;

Security tab --> Logins --> right click name to modify select properties --> securables tab --> Click add --> Select the server you want to add the permissions to. The permissions should appear in the effective permissions list


USE master;
GRANT ALTER TRACE TO [Username];

-->Where User Name for domain account is [Domain\accountname];

PMP - Formula

DOS Batch file to create a file name in data stamp

:: 2. Set logfile name based on today's date

for /F "tokens=1,2,3,4 delims=/ " %%I in ('date /T') do set Logfile=%%J%%K%%L.log

:: 4. Delete all but 7 most recent log files

:: Get a sorted list of the log files oldest at the bottom of the list

Dir /b /o-d %Logfolder%\*.log > %Workfolder%\%LogList%

:: The find command counts how many log files there are and for sets TempVar with this value

For /f %%A in ('Dir /b /o %Logfolder%\*.log^| Find /c /i "log"') do set TempVar=%%A

:: If there are more logs than needed skip the number to keep and delete the oldsest one(s)

If %TempVar% GTR %LogsToKeep% For /f "skip=%LogsToKeep%" %%B in (%Workfolder%\%LogList%) do Del /q %Logfolder%\%%B
Del /q %Workfolder%\%LogList%

Project Management Processes – PmBOK Ver 3.0

Query TSM Archive to check file exits

@echo off
set script_dir=c:\progra~1\tivoli\tsm\scripts
echo dsmc query archive %1 -su=yes -fromdate="%2" -todate="%2"
dsmc query archive %1 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt" -fromdate="%2" -fromtime=00:00 -todate="%2" -totime=23:00

echo.%ERRORLEVEL%

IF ERRORLEVEL 12 GOTO LABEL12
IF ERRORLEVEL 0 GOTO LABEL0

:LABEL12
echo ####################### FAILED #########################################
echo #
echo # The files you are searching for are not there . Check your syntax
echo # Date range has to be MM/DD/YYYY
echo # Directory must exist , put an asterisk on the end e.g
echo # h:\extend\*
echo # or h:\extend\Orbis_1\OFAS_CN_PRD.001
echo # Archive are kept for 14 days as per Todd Rd setup
echo #
echo ########################################################################
GOTO end

:LABEL0
echo ###################### SUCCESS #########################################
echo #
echo # This is the list of available archives within the date range you specified
echo # Use this list in the DSMC RETRIEVE command
echo #
echo # dsmc query archive %1 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt" -fromdate="%2" -fromtime=00:00 -todate="%2" -totime=07:00
echo #
echo #
echo #
echo ########################################################################

:end


-------------------------------------------
To retrieve the data from Remote Server
-------------------------------------------
@echo off
set script_dir=c:\progra~1\tivoli\tsm\scripts
dsmc set access archive %1 %3 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt"
dsmc query archive \\%3\%1 -optfile="c:\progra~1\tivoli\tsm\baclient\dsm_swift.opt" -fromnode=%3 -fromdate="%2" -fromtime=00:00 -todate="%2" -totime=23:00

Who dropped objects from database?

Balmukund : Who dropped objects from database?

Sometime I have been asked that how to find out who
dropped/altered/created table/stored procedure. Well, SQL 2005 has
out-of-the-box solution to this problem.

Schema Change History
is the report which would give details. This report takes data from
default trace which is enabled by default in SQL Server 2005 default
installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'default trace';
GO