SIVA SQL Server: 2005

Sunday, December 04, 2005

SQL Server Locking detail from Sysperfinfo

--select getdate()
go
select getdate(),counter_name,instance_name,cntr_value from sysperfinfo
where object_name ='SQLServer:Locks'
and counter_name in ('Average Wait Time (ms)','Lock Wait Time (ms)','Lock Waits/sec' )
and instance_name = '_Total'
WAITFOR DELAY '0:01:00'
---select getdate()
select getdate(), counter_name,instance_name,cntr_value from sysperfinfo
where object_name ='SQLServer:Locks'
and counter_name in ('Average Wait Time (ms)','Lock Wait Time (ms)','Lock Waits/sec' )
and instance_name ='_Total'

Tuesday, November 15, 2005

Transfer Logins

BUG: SQL 6.5 Logins upgraded to SQL 7.0 and subsequently moved to SQL 2000 through CDW cannot log in
http://support.microsoft.com/default.aspx?scid=kb;en-us;285025

Microsoft Knowledge Base Article - 331451
PRB: Copy Database Wizard Does Not Copy Server Roles When it Transfers Logins
-------------------------------------
Microsoft Knowledge Base Article - 240872
HOW TO: Resolve Permission Issues When You Move a Database Between Servers That Are Running SQL Server
.-------------------------------------
Microsoft Knowledge Base Article - 246133
INF: How To Transfer Logins and Passwords Between SQL ServersHas a script to transfer SIDs as well.
-------------------------------------
Yelena

Tuesday, November 08, 2005

SQL Server Upgrade Recommendations and Best Practices

http://www.sql-server-performance.com/jk_sql_server_upgrade2.asp

Wednesday, September 28, 2005

Transaction Log space for DBCC DBREINDEX

1.The log space required by DBCC DBREINDEX is roughly equivalent to the number of index pages multiplied by 8 KB.

2. You can use DBCC SHOWCONTIG to determine the number of pages in a given index

3.When in full recovery mode, DBCC DBREINDEX logs images of each index page

Diff between DBReindex and DBCC Showcontig
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EFAA

Wednesday, September 14, 2005

Migrating OLAP Repository database from the Access to SQL Server

Note By default, the OLAP repository database is :\Program Files\Microsoft Analysis Services\Bin\Msmdrep.mdb

More on : http://support.microsoft.com/kb/829738/

You Receive an Error Message When You Try to Register or Connect to an Analysis Services Computer as a Domain User

Cannot connect to the repository.
Analysis server:

Error:
Could not use ''; file already in use.

Thursday, September 08, 2005

SQL Serve Memory Monitoring Parameters

Memory: Available Bytes

Memory: Pages/sec

Process: Working Set ( SQLServer )

SQL Server: Buffer Manager: Buffer Cache Hit Ratio

SQL Server: Buffer Manager: Total Pages

SQL Server: Memory Manager: Total Server Memory (KB)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_8x0l.asp

Monday, September 05, 2005

Comparing SQL Server Databases

PowerDesigner, it’s a modeling tool from Sybase.

1.Just generate the SQL definition for the databases from inside the SQL Server and then import them to the PowerDesigner.
2. Once you have imported both databases in the PowerDesigner you can do many things.

3. You can compare the definitions and the PowerDesigner will generate a complete report with the differences between the two databases. The report will include tables, Keys, Indexes, views … etc

Wednesday, August 24, 2005

Auditing SQL Server Security

Auditing SQL Server Security

http://www.theiia.org/itaudit/index.cfm?fuseaction=forum&fid=5611

Saturday, August 13, 2005

Unable to connect to Citrix Server from some Workstations

Root Cause:
When Citrix client try to connect to Citrix Server, Server not able to issue client license. (Its not able to generate Client license key on Client computer HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSLicensing\Store Registry hive)

Workaround:
Open regedit.exe from client workstation Go to "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSLicensing\Store" Delete all the keys start with 'LIC' (It will remove existing Terminal Services client license key) Try to login to Citrix Server. Citrix Server will reissue new client license.

Monday, August 08, 2005

System Error No List

System Error Codes (0-499)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes__0-499_.asp

Example:

ERROR_DISK_FULL112
There is not enough space on the disk.

Microsoft Product Support's Reporting Tools

The Microsoft Product Support Reporting Tool facilitates the gathering of critical system and logging information used in troubleshooting support issues. The reporting tool DOES NOT make any registry changes or modifications to the operating system. There are 8 specialty versions, one for each of the following support scenario categories: Alliance, Directory Services (not for Windows NT 4.0), Networking, Clustering, SQL, Software Update Services, MDAC and Base/Setup/Storage/Print/Performance. Each version gathers some of the same basic information but there are specific reports unique to each of the support scenario categories

Link :
http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-88B7-F9C79B7306C0&displaylang=en

Tuesday, August 02, 2005

SQL Server Agent not starting

SQL Server Agent on SQL Server 2000 not started.

There are One SQL Server Service account and one connection setting for the serivce account this need to be checked for any password change and accont change if not windows authentication

It almost relevant of "http://support.microsoft.com/default.aspx?scid=kb;en-us;237604".

From Event Viewer
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Service Control
Event ID: 103
Date: 30/07/2005
Time: 4:01:50 PM
User: N/A
Computer: AAUNSW047
Description:
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).

Output on SQL Agent when tries "SQLAgent -c -v " from the command prompt
2005-07-30 16:01:50 - ? [094] SQLServerAgent started from command line
2005-07-30 16:01:50 - ! [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
2005-07-30 16:01:50 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2005-07-30 16:01:50 - ? [098] SQLServerAgent terminated (normally)

INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server

How to stop the transaction log of a SQL Server database from growing unexpectedly

SQL Server 2000 Analysis Services - INF: Permissions That You Must Have to Administer an OLAP Server

2147221466 : Your permissions on the server computer do not allow you to administer this OLAP server.Error: Path/File access error

SQL Server 2000 Analysis Services - INF: Permissions That You Must Have to Administer an OLAP Server

Thursday, July 14, 2005

Dynamic Memory Setting on SQL Server

This is given in SQL Server 2000 BOL.

When an instance of SQL Server starts, it typically acquires 8 to 12 MB of memory to complete the initialization process. After the instance has finished initializing, it acquires no more memory until users connect to it and start generating a workload. The instance then keeps acquiring memory as required to support the workload. As more users connect and run queries, SQL Server acquires the additional memory required to support the demand. The instance will keep acquiring memory until it reaches its memory allocation target, it will not free any memory until it reaches the lower limit of the target.

To acquire as much memory as possible without generating excess paging I/O, each instance of SQL Server sets a target of acquiring memory until free physical memory on the computer is in the range of 4 MB to 10 MB. This range was chosen because testing has shown that Windows NT and Windows 2000 have minimal memory swapping until the memory allocations equal the available physical memory minus 4 MB. An instance of SQL Server that is processing a heavy workload keeps the free physical memory at the lower end (4 MB) of the range; an instance that is processing a light workload keeps the free memory at the higher end of the range (10 MB). An instance of SQL Server will vary its target as the workload changes. As more users connect and generate more work, the instance will tend to acquire more memory to keep the available free memory down at the 4 MB limit.

As the workload lightens, the instance will adjust its target towards 10 MB of free space, and will free memory to the operating system. Keeping the amount of free space between 10 MB and 4 MB keeps Windows NT or Windows 2000 from paging excessively, while at the same time allowing SQL Server to have the largest buffer cache possible that will not cause extra swapping."

SQLServer FAQ Links

http://www.sqljunkies.com/WebLog/roman/archive/category/351.aspx

http://blogs.msdn.com/slavao/archive/2005/02/01/364523.aspx

On FAQ:

http://www.sql-server-performance.com/q&a_performance.asp

http://www.sql-server-performance.com/q&a_performance.asp

SQL Server 2000 Standard Backup failure

Backup job created using SQL Server Maintanence plan fail with the below error intermittently
"Executes as user: Domain\sqladmin sqlmaint.exe failed. [SQLSTATE 42000] [Error 22029]. The step failed"
Below are the error message found on SQL server Errror log.

BACKUP failed to complete the command BACKUP DATABASE [USER_DB_01] TO DISK = N'E:\MSSQL\BACKUP\USER_DB_01\USER_DB_01_db_200507092100.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

BackupMedium::ReportIoError: write failure on backup device 'E:\MSSQL\BACKUP\USER_DB_01\USER_DB_01_db_200507092100.BAK'. Operating system error 112(error not found).

Internal I/O request 0x53A97468: Op: Write, pBuffer: 0x154A0000, Size: 983040, Position: 3927840256, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0xEA1E1600, OffsetHigh: 0x0, m_buf: 0x154A0000, m_len: 983040, m_actualBytes: 0, m_errcode: 112, BackupFile: E:\MSSQL\BACKUP\USER_DB_01\USER_DB_01_db_200507092100.BAK

DTS Package Creation

Question on , who can create DTS and save the DTS packages?

What are the privileges and rights that required for user to create a DTS package?

Answer is on the below link.
http://support.microsoft.com/?id=282463

Monday, July 11, 2005

5 Seconds : Business Intelligence with Microsoft SQL Server Reporting Services - Part 1

http://www.15seconds.com/issue/041013.htm

Thursday, June 23, 2005

SQL Server 7.0 to SQL Server 2000 Upgradation

Introduction 2

Purpose 2

Pre-Upgrade Considerations 2

Things to be moved 3

Time
required to do the upgrade 3

Method to Choose 4

1. Upgrading to SQL Server 2000 from
the SQL Server 7.0 in a box (
Installing over SQL Server 7.0 ) 5

Back out plan 5

2. Uninstall existing SQL Server 7.0
and Install SQL Server 2000 ( Clean
Install on current box ) 6

Back out plan 6

3. Install
another instance of SQL Server 2000 and keep existing SQL Server 7.0 intact. 7

Back out plan 7

4. Install SQL Server 2000 on a new
machine and keep existing SQL Server
7.0 intact on the current box. (
Clean Install on new box / Two System upgrade ) 7

Back out plan 8

Introduction

This document details the three option of upgrading SQL
Server 7.0 databases to SQL Server 2000 databases.

Method 1: Upgrading to SQL Server 2000 from the SQL
Server 7.0 in a box

( Installing over
SQL Server 7.0 / Single Server Upgrade)

Method 2: Uninstall existing SQL Server 7.0 and
Install SQL Server 2000

( Clean Install on
current box )

Method 3: Install another instance of SQL Server 2000
and keep existing

SQL Server 7.0
intact.

Method 4: Install SQL Server 2000 on a new machine and
keep existing

SQL Server 7.0
intact on the current box.

( Clean Install on new box / Two System
upgrade )

Purpose

Upgrading SQL Server 7.0 Databases to
SQL Server 2000.

Pre-Upgrade
Considerations

Before deciding on upgrade the current
databases to SQL Server 2000, the following points should be considered:

·
How
well does the database server handle the current workload?

·
Have
any potential bottlenecks been identified on the current system?

·
What
is the projected growth rate, in terms of data volume, number of end users or
both? Will the current system after being upgraded to SQL Server 2000, be able
to handle this growth?

·
To
what extent will database downtime, as part of upgrade affect the business?

·
To
what extent will the database upgrade affect the application?

·
What
is involved in testing the application with the databases upgraded to SQL
Server 2000?

·
According
to Microsoft recommendation upgrade to SQL Server 2000 require 1.5 times of
disk space taken by the SQL Server 7.0 databases.

Things
to be moved

Regardless of which migration method we choose, we need to
ensure that all or whatever applicable in the below list are migrated and/or
updated:

1.
Database Data and Log Files

2.
SQL Server Logins

3.
Database users and their mappings to SQL Server Logins

4.
Full-Text Indexes (enable and repopulate on new server)

5.
Other Indexes (rebuild/repopulate on new server)

6.
Statistics (update on new server)

7. SQL
Agent Jobs

8. DTS
Packages

Time
required to do the upgrade

It is difficult to exactly determine the
time required to perform the upgrade. The upgrade process includes.,

·
Perform
Database Consistency Checks (DBCC), with no user logged on to the system. The
DBCC utility would check the integrity of the data on the database and do the
minor correction on corrupted data. It is advisable to do before the database
backup.

·
Offline
backup

·
Hardware upgrades ( If necessary )

·
Operating
System upgrades ( If necessary )

·
Installation
/ Upgrade to SQL Server 2000

·
Database
Migration from the old server to the new version

·
Creation
of Login, SQL Agent Jobs , DTS Packages,
mapping users ..,

·
Database Testing

·
Application Testing

·
Backup
of upgraded databases

Method
to Choose

On the following methods of upgrading databases from SQL Server 7.0 to
SQL Server 2000 depends on the answer to the following questions:

1. Will you need to keep the current
database functional while you are testing the migration to SQL Server 2000?

( If so, you may require new box or a
new instance for SQL Server 2000.

Methods 3 & 4 support
this )

2. If you wish to keep the current
database functional while testing with SQL Server 2000, do you have budget to
acquire a new system?

( If so, choose Method 4 )

3. If you wish to keep both
databases on SQL Server 7.0 and SQL Server 2000, you will need the box in
hardware level, in term of CPU, Memory,
Disk Spaces to support the both databases. Otherwise upgrade the hardware.

( Here Method 3 can be used )

4. If you choose not to upgrade the
existing server or acquire the new server and do not have enough disk space to
host both databases (7.0 and 2000 ), then existing databases can be
overwritten.

( Methods 1 & 2 will
satisfy this condition )

1. Upgrading to SQL Server 2000 from the SQL Server 7.0 in a box

( Installing over SQL Server 7.0 )

In this method, we would choose the
upgrade option from the SQL Server 200 Setup program, the program would detects
SQL Server 7.0 as the existing installation and automatically selects and
installs a default instance of SQL Server 2000.

·
In
this process the upgrade will overwrite existing SQL Server 7.0

·
SQL
Server 2000 Setup upgrade process would upgrade all the databases, there is no
flexibility in selecting the databases for upgrade

·
To
rollback we have uninstall the SQL Server 2000 and install SQL Server 7.0

·
High
downtime or databases on existing instance will not be available during the
upgrade process.

Back out plan

Backup out involves uninstalling
the newly installed SQL Server 2000 and

clean installing SQL Server 7.0
then restoring the databases from the backup.

This would be followed by addition
steps which are mentioned in “Things to be

moved”

1.
Before
upgrading to SQL Server 2000, Perform database consistency check.

2.
Backup
all the databases.

3.
Verify
the backup to make sure that can be used on restore.

4.
Uninstall
the SQL Server 2000

5.
Install
the SQL Server 7.0

6.
Apply
the required SQL Server Service pack, patches and hotfixes.

7.
Restore
the databases which are backed up.

2. Uninstall existing SQL Server 7.0 and Install SQL Server 2000

( Clean Install on current box )

On this methods we have four options on HOW to
migrate databases from SQL Server 7.0 server to the SQL Server 2000. They are.,

a) Backup and Restore,

b) Detach/Attach.

·
To
rollback we have uninstall the SQL Server 2000 and install SQL Server 7.0

·
High
downtime or databases on existing instance will not be available during the
upgrade process.

Back out plan

1.
Before
upgrading to SQL Server 2000, Perform database consistency check.

2.
Backup
all the databases.

3.
Verify
the backup to make sure that can be used on restore.

4.
Uninstall
the SQL Server 2000

5.
Install
the SQL Server 7.0

6.
Apply
the required SQL Server Service pack, patches and hotfixes.

7.
Restore
the databases which are backed up.

3. Install another instance of SQL Server
2000 and keep existing

SQL Server 7.0 intact.

·
Minimal downtime of the existing instance

·
In parallel databases on SQL Server 7.0 and 2000 co exist in same box.

·
We can install only named instance of SQL Server
2000 with an existing installation of SQL Server 7.0

·
Ensure the current box has the required
capability in terms of load on CPU, I/O Throughput, Memory and disk space to
host new instance of SQL Server

On this methods we have four options
on HOW to migrate databases from SQL Server 7.0 server to the SQL Server
2000. They are.,

a) Run the Copy Database Wizard that
comes with SQL 2000,

b) Run DTS with the Copy Objects and
Data between SQL Servers,

c) Backup and Restore,

d) Detach/Attach.

Back
out plan

Since the existing instance untouched, the
back out plan is quite simple as to
uninstall the newly installed instance of SQL Server 2000.

4. Install SQL Server 2000 on a new machine and keep existing

SQL Server 7.0 intact on the current
box.

( Clean Install on new box / Two
System upgrade )

·
This
process minimize the risk of problems during the upgrade process.

·
Minimal
database downtime

·
Current
databases on SQL Server 7.0 are accessible during the upgrade process.

·
Flexibility
to upgrade on the select databases while leaving the other database in the
existing system

·
Ability
to move or copy databases to the SQL Server 2000 at a convenient time

Back out plan

In case of any issues, the databases
and application on the existing system are unchanged which can be used.

On this methods we have four options on HOW to
migrate databases from SQL Server 7.0 server to the SQL Server 2000. They are.,

a) Run the Copy Database Wizard that
comes with SQL 2000,

b) Run DTS with the Copy Objects and
Data between SQL Servers,

c) Backup and Restore,

d) Detach/Attach.