SIVA SQL Server

SQL Server 2012 official launch by March 7, 2012

2012-01-25T17:16:00.001-08:00

SQL Server 2012 to be released officially by March 7, 2012.

http://www.sqlserverlaunch.com

How to get .rdl file of the Reports on Report Server

2011-08-12T19:21:00.001-07:00

Question:

Do you want to have copy of a report on the ReportServer?
What would be the case when, tt was not saved in SourceSafe. Is there a way to get the .rdl file for that report copied to local computer from the ReportServer?

Answer:

For SQL Server 2005 Reporting Services

Go to Report on Report Manager
Click properties of Report
Under the report definition you will see Edit and Update link.
if you click on Edit you can open or Save the RDL files

For SQL Server 2008 Reporting Services

Go to Report on Report Manager
Click properties of Report
Select Download on the top right corner
Now you can open or Save the RDL files

Can we backup reportservertempdb?

2011-08-01T02:31:00.001-07:00

I have been asked by a DBA, if we can backup reportservertempdb, why not!

Here is the answer:

We should backup the database reportservertempdb as we backup the reportserver.

The reason to backup the reportservertempdb is to avoid having to recreate it if there is a hardware failure. In the event of hardware failure, it is not necessary to recover the data in reportservertempdb, but you do need the table structure.

If you lose reportservertempdb, the only way to get it back is to recreate the report server database. If you recreate the reportservertempdb, it is important that it have the same name as the primary report server database.

System database msdb size in huge! How to purge MSDB Backup and Restore History?

2011-08-01T02:20:00.001-07:00

On one of my SQL Server 2008 Instance, the msdb database sizes around 4 GB. I decided to find out the which table / index occupies major junk on this. I have used query below;

--- Query 1, if there is partition

SELECT TOP 10 SERVERNAME=@@SERVERNAME,DB_NAME=DB_NAME(),TABLE_NAME=OBJECT_NAME(I.ID),INDEX_NAME=I.NAME,INDID,USED, ROWS, SIZE_N_MB = ROUND((USED*8.0/1024.0),2),
ROWMODCTR,STATISTICDT=STATS_DATE(I.ID,INDID)
FROM SYSINDEXES I, SYSOBJECTS O
WHERE I.ID = O.ID
AND INDID IN ( 0,1)
AND XTYPE = 'U'
ORDER BY USED DESC

--- Query 2 If there are partitions

SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)

The output resulted;

The following 5 big tables takes up 88% of the total database size of 3.8 GB. These 5 tables occupy 3.3 GB on the msdb database.

Query 1: Output

TABLE_NAME	INDEX_NAME	INDID	USED	ROWS	SIZE_N_MB	ROWMODCTR	STATISTICDT
backupfile	PK__backupfi__57D1800A17C286CF	1	233057	4438738	1820.76	2646909	2/14/2011 15:57
backupset	PK__backupse__21F79AAB0E391C95	1	115791	1489763	904.62	228112	6/19/2011 6:00
backupmediafamily	PK__backupme__0C13C86F0880433F	1	50738	1489616	396.39	23386	7/26/2011 7:00
backupfilegroup	PK__backupfi__760CD67A12FDD1B2	1	43284	2967333	338.16	1775145	2/14/2011 15:57
backupmediaset	PK__backupme__DAC69E4D04AFB25B	1	29250	1489616	228.52	44462	7/22/2011 15:43
sysjobhistory	clust	1	3553	33177	27.76	2428	7/25/2011 8:14

Query 2: Output

objectName	indexName	totalPages	usedPages	dataPages	totalSpaceMB	usedSpaceMB	dataSpaceMB	Rows
backupfile	PK__backupfi__57D1800A17C286CF	233073	233043	231653	1820	1820	1809	4438738
backupset	PK__backupse__21F79AAB0E391C95	100217	100200	99826	782	782	779	1489763
backupfilegroup	PK__backupfi__760CD67A12FDD1B2	43289	43283	43077	338	338	336	1489616
backupmediafamily	PK__backupme__0C13C86F0880433F	39473	39459	39287	308	308	306	2967333
backupmediaset	PK__backupme__DAC69E4D04AFB25B	18737	18728	18654	146	146	145	1489616

Analyze and find out the minimum and maximum date range date available

SELECT COUNT(*) AS 'TotalRecords',
MIN(backup_start_date) AS 'MinDate',
MAX(backup_start_date) AS 'MaxDate'
FROM dbo.backupset

Resolution:
The solution is we have to purge these MSDB Backup and Restore history tables

1. The sp_delete_backuphistory system stored procedure takes a single parameter - a cutoff date. Any data older than the supplied date is purged from the msdb tables.

2. The sp_delete_database_backuphistory system stored procedure allows you to delete historical backup data for a specific database. Unfortunately, this procedure does not offer the finer option of choosing a cutoff date. It's all or nothing.

3. sp_purge_jobhistory Removes the history records for a job.

4. Try considering creating indexes on the above said 5 tables, if no of rows are huge.
Source: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

use msdb
go
--backupset

Create index IX_backupset_backup_set_id on backupset(backup_set_id)
go
Create index IX_backupset_backup_set_uuid on backupset(backup_set_uuid)
go
Create index IX_backupset_media_set_id on backupset(media_set_id)
go
Create index IX_backupset_backup_finish_date on backupset(backup_finish_date)
go
Create index IX_backupset_backup_start_date on backupset(backup_start_date)
go
--backupmediaset
Create index IX_backupmediaset_media_set_id on backupmediaset(media_set_id)
go
--backupfile
Create index IX_backupfile_backup_set_id on backupfile(backup_set_id)
go
--backupmediafamily
Create index IX_backupmediafamily_media_set_id on backupmediafamily(media_set_id)
go
--restorehistory
Create index IX_restorehistory_restore_history_id on restorehistory(restore_history_id)
go
Create index IX_restorehistory_backup_set_id on restorehistory(backup_set_id)
go
--restorefile
Create index IX_restorefile_restore_history_id on restorefile(restore_history_id)
go
--restorefilegroup
Create index IX_restorefilegroup_restore_history_id on restorefilegroup(restore_history_id)
go

5. Alternatively, check when the statistics of these tables are last updated. Just run sp_updatestats. Just try sp_delete_backuphistory after the statistics are updated. The result is awesome! Very quick response.

Permanent Resolution

6. Further restrict the Job history growth by setting in SQL Agent –> History tab.

7. Also on the SQL Server Maintenance Plan “History Cleanup Task” can be of helpful

SQL Server 2008: syspolicy_purge_history job

2011-07-31T23:20:00.001-07:00

This job is created automatically when on SQL Server 2008 Policy Management is enabled.

Policy Management is a new feature introduced on SQL Server 2008 onwards.

Once the Policy Management defined, the policy can be evaluated against targets to check for compliance. Each evaluation of compliance will be recorded in tables in system database msdb,

1. syspolicy_policy_execution_history_details_internal
2. syspolicy_policy_execution_history_internal
3. syspolicy_policy_category_subscriptions_internal

The evaluation condition is built on top of object facets, which is predefined by Microsoft.

Purpose of the job, syspolicy_purge_history:

It is to remove records in those tables to maintain so it does not pile up and take up space unnecessarily. It has three steps

It does what instance it is running in and then erases system health records.

Step 1 :
The first step verifies if Policy Management is enabled. If yes, it will go to the second step
IF (msdb.dbo.fn_syspolicy_is_automation_enabled() != 1)
        BEGIN
            RAISERROR(34022, 16, 1)
        END

step 2
Deletes policy evaluation history prior to cutoff date, defined by HistoryRetentionInDays.
EXEC msdb.dbo.sp_syspolicy_purge_history

step 3
It works out what instance it is running in and then erases system health records.

if ('$(ESCAPE_SQUOTE(INST))' -eq 'MSSQLSERVER') {$a = '\DEFAULT'} ELSE {$a = ''};
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()

Silent Mode Installation of SQL Server 2008 SP2

2011-07-12T05:19:00.001-07:00

Here is the command for SQL Server 2008 Express Edition Silent mode installation of SP2.

SQLEXPR_x86_ENU.exe /Q /ACTION=PATCH /INSTANCENAME=SQLEXPRESS

CHECKPOINT in SQL Server

2011-05-27T17:49:00.001-07:00

What is CHECKPOINT?

As per BOL definition “ Writes all dirty pages for the current database to disk.
Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.
Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk”

This minimizes the active portion of the log that must be processed during a full recovery of a database.

What are the Events that causes CHECKPOINT?

1. Before Database Backup

Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

2. Active Log exceeds recovery interval

The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

3. The log becomes 70 percent full, and the database is in log-truncate mode.

A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

BULK_LOG Recovery: A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.
An ALTER DATABASE statement is executed that adds or deletes a file in the database

4. Stopping a SQL Server issues a checkpoint in each database on the server

5. CHECKPOINT:
A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.

6. Database Shutdown
An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

SQL Server Management Studio – SSMS is a 32 bit application

2011-05-19T07:06:00.001-07:00

SQL Server Management Studio – SSMS is a 32 bit application. Even when we install 64 bit SQL Server Database Engine, we will get 32 bit SSMS installed on the server.

Default SSMS install location

32 Bit Server	C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
64 Bit Server	C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

Maintenance Plan Error: Alter failed for Server SQLServer_Instance

2011-05-16T16:55:00.001-07:00

Error message: Alter failed for Server ‘SivaSQL2008’

When I ran SQL Profiler and checked why its failing, observed;
Errors when it was trying to execute

SP_CONFIGURE 'USER OPTION',0 ;
RECONFIGURE

Error: 5808, Severity: 16, State: 1
Ad hoc update to system catalogs is not supported.

The reason for why AD HOC UPDATE TO SYSTEM CATALOGS is not supported.
Why it is trying to change SYSTEM CATALOGS at first place?

In SQL Server 2000 and older, this option allowed users to make updates directly to the system tables.
In SQL Server 2005 and higher, the system tables are, replaced with the Resource Database and system views.
This option is no longer supported in SQL Server 2005 and higher version, and though we can set Allow Updates to 1 with no error. As soon as we run RECONFIGURE, we will receive the error that ad hoc updates are not supported.

Because Allow Updates had been set to 1 and my Maintenance Plan runs the RECONGIFURE statement, this error was thrown and the Maintenance Plan failed.

As per Books On Line:

Changing the allow updates option will cause the RECONFIGURE statement to fail. Changes to the allow updates option should be removed from all scripts.

Resolution:
So to resolve is configured ALLOW UPDATE back to 0 and again ran Maintenance Plan and it executes fine.

sp_configure 'allow update', 0
reconfigure

How to create scripts for an entire database or specific object in SQL Server 2008?

2011-05-16T05:48:00.001-07:00

In this post I would like to address the requirement of;

How to create scripts for an entire database ?
How to create scripts limit it to specific objects?
How to restore SQL Server 2005 database on SQL Server 2000?

SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server.

It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window.

If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.

We can use the Generate and Publish Scripts Wizard to create scripts for transferring a database from one instance of the Database Engine to another. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Azure.

The generated scripts can be run on another instance of the Database Engine or SQL Azure.

We can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services.

We can create scripts for an entire database, or limit it to specific objects.

What version of SQL Servers are supported on this?

	Source Database	Target Database
SQL Server Instance Version Supported	SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Azure	SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 SQL Azure SQL Server 2000
Permission needed	db_ddladmin	db_ddladmin
Pre-existence		The target database must be created at the hosting provider before the source database is published. Publishing overwrites objects in that existing database

Supported Objects for Publishing

The following table lists the objects that can be published and the versions of SQL Server on which they are supported by the Generate and Publish Scripts Wizard.

Database object	SQL Server 2008 R2	SQL Server 2008	SQL Server 2005	SQL Server 2000
Application role	Yes	Yes	Yes	Yes
Assembly	Yes	Yes	Yes	No
CHECK constraint	Yes	Yes	Yes	Yes
CLR (common language runtime) stored procedure¹	Yes	Yes	Yes	No
CLR user-defined function	Yes	Yes	Yes	No
Database role	Yes	Yes	Yes	Yes
DEFAULT constraint	Yes	Yes	Yes	Yes
Full-text catalog	Yes	Yes	Yes	Yes
Index	Yes	Yes	Yes	Yes
Rule	Yes	Yes	Yes	Yes
Schema	Yes	Yes	Yes	No
Stored procedure¹	Yes	Yes	Yes	Yes
Synonym	Yes	Yes	Yes	Yes
Table	Yes	Yes	Yes	Yes
User²	Yes	Yes	Yes	Yes
User-defined aggregate	Yes	Yes	Yes	No
User-defined data type	Yes	Yes	Yes	Yes
User-defined function	Yes	Yes	Yes	Yes
User-defined table	Yes	Yes	No	No
User-defined type	Yes	Yes	Yes	No
View¹	Yes	Yes	Yes	Yes
XML schema collection	Yes	Yes	Yes

1 Published without encryption.
2 Any non-system users that exist in the database are published as Roles.

How to generate SQL Scripts using Generate and Publish Scripts Wizard

We can create Transact-SQL scripts for multiple objects by using the Generate and Publish Scripts Wizard.. You can also generate a script for individual objects or multiple objects by using the Script as menu in Object Explorer.

Use the Generate and Publish Scripts Wizard to create a Transact-SQL script for many objects.
The wizard generates a script of all the objects in a database, or a subset of the objects that you select. The wizard has many options for your scripts, such as whether to include permissions, collation, constraints, and so on.

Step 1:

In Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Generate Scripts. Follow the steps in the wizard to script the database objects.

Step 2: On the Choose Objects page, select the objects to be included in the script

Script entire database and all database objects
Click to generate scripts for all objects in the database and to include a script for the database itself.

Step 1.3: On the Set Scripting Options page, select Save scripts to a specific location.

Select Save scripts to a specific location

Summary:

Click Finish.

To generate the Script for the Data on the table:

Set “Script Data: True “

Select specific database objects
Click to limit the wizard to generate scripts for only the specific objects in the database that you choose.

For example I have selected Tables Object

Within Tables Object, I have chosen HumanResources.Employee Table

Select Save scripts to a specific location

Script Wizard Summary

View of the Output file generated:

References:

Using the Generate and Publish Scripts Wizard

How to: Generate a Script (SQL Server Management Studio)

Useful Microsoft SQL Server websites on SQL Server

2011-05-11T01:44:00.001-07:00

Here, is the list of useful Microsoft SQL Server Websites.

Title	Description / Content	Website
Microsoft SQL Server	Commercial Site Buy, About Pricing, Edition, License Positioning whitepapers Case Study Edition Product Information Customers Partners Support	http://www.microsoft.com/sqlserver/en/us/default.aspx
SQL Server Books On Line ( BOL )	Books On Line - BOL	http://msdn.microsoft.com/en-us/library/ms130214.aspx
MSDN SQL Server Forum	SQL Server Online Community Forum	http://social.msdn.microsoft.com/Forums/en/category/sqlserver/
	SQL Server Training Webinars Community Blogs Learning Resources	http://msdn.microsoft.com/en-us/sqlserver/default.aspx
SQL Server Technet	Troubleshoot an issue Top Support Solutions	http://technet.microsoft.com/en-us/sqlserver/default.aspx
SQL Server Certification	Certification Path Certification Title Courses	http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx

Msg 3701, Level 11, State 5, Line 1 Cannot drop the trigger ‘ ‘, because it does not exist or you do not have permission.

2011-05-11T01:22:00.001-07:00

I got the error message below, when i was playing with DDL Trigger on SQL Server 2008.
The trigger exists, i have cross verified querying;

select *
from master.sys.server_triggers
where name = 'connection_limit_trigger'

select objectproperty(1435152158, 'isTrigger') isTrigger
,objectproperty(1435152158, 'ownerid') ownerID

Error Message:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'connection_limit_trigger', because it does not exist or you do not have permission.

Resolution:

After scratching my head, i found the resolution. It is simple !

If the trigger is DATABASE Level scope use below

Drop TRIGGER connection_limit_trigger on Database

Otherwise if it is Server Level scope please try below;

Drop TRIGGER connection_limit_trigger on all server

Cause:
The lost bold command i.e. scope level makes the different.
When we use DDL Trigger and trying to drop using the DROP command, we should match the scope level of the trigger when it created.

The server principal is set as the execution context of a trigger or event notification and cannot be dropped.

2011-05-10T18:40:00.001-07:00

I got error message below when i try to drop a login named ‘test_login’

Error Message:
Msg 15186, Level 16, State 1, Line 1
The server principal is set as the execution context of a trigger or event notification and cannot be dropped.

It means that there are execute as username in a stored procedure or function or triggers, and that is why it will not allow me to drop the user.

EXECUTE permission - a check is made to see if this permission is granted. If yes the user is allowed to run the stored procedure

EXECUTE AS - when the stored procedure is run, it will run under the context of the user specified in the clause rather than the context of the user who has run it.

Use the query below find the execute as object

select object_name(object_id)
from sys.sql_modules
where execute_as_principal_id = user_id('test_login')

How to find the Dynamic Management Views on SQL Server Management Studio?

2011-05-10T17:19:00.001-07:00

Expand the Views –> System Views on each database nodes.

Change Tracking Vs. Change Data Capture ( CDC ) in SQL Server 2008

2011-05-03T18:46:00.001-07:00

Here we find the difference between the Change Track vs Change Data Capture in SQL Server 2008. Both helps in tracking the table changes and security compliance.

Change Track	Change Data Capture – CDC
It is about fact: It captures only the fact as the tracking table has changed. It does NOT capture the data. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured	It is about the Data: Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed.
Storage: Internal tables are placed on the same filegroup as the parent entity. You could use the sys.internal_tables catalog view to show all the internal tables and parent entities. For example: select name, object_name(parent_id) as parent_object from sys.internal_tables	Storage: When change data capture is enabled for a database, a few things are added to the database, including a new schema (called cdc), some metadata tables, and a trigger to capture Data Definition Language (DDL) events. The two function names are, respectively, fn_cdc_get_all_changes_ and fn_cdc_get_net_changes_, with the capture instance name appended. Note that (like the change tracking feature) this functionality requires the table to have a primary key or other unique index.
Supported on “Simple” recovery model also. It is recommended that you use snapshot isolation when change tracking is enabled. Snapshot isolation itself can add significant workload overhead and requires much more careful management of tempdb.	Prevents Log truncation. Forces full logging of some bulk operations. One major point to note here is that once change data capture is enabled, the transaction log behaves just as it does with transactional replication—the log cannot be truncated until the log reader has processed it. This means a checkpoint operation, even in SIMPLE recovery mode, will not truncate the log unless it has already been processed by the log reader.
It uses synchronous tracking mechanism. once a database is enabled for change tracking, a version number is instituted, which allows ordering of operations	Change Data Capture (CDC) uses the asynchronous process that reads the transaction log.
Change Tracking has minimal impact on the system.	It has almost nil impact as it asynchronous mechanism reads from the transaction log.
It uses TempDB heavily	It uses transaction log.
DDL Restriction: There are restrictions on the DDL that can be performed on a table being tracked. The most notable restriction is that the primary key cannot be altered in any way. The other restriction worth calling out here is that an ALTER TABLE SWITCH will fail if either table involved has change tracking enabled.	No such DDL restriction
SQL Agent not needed	It requires SQL Agent to be running. SQL Agent Job & Transaction Replication: Two SQL Agent jobs may be created: the capture job and the cleanup job. I say "may be created" because the capture job is the same as the one used for harvesting transactions in transactional replication. If transactional replication is already configured, then only the cleanup job will be created and the existing log reader job will also be used as the capture job
Permission required to enable: SYSADMIN	Permission required to enable: DBOwner

Reference:

Comparing Change Data Capture and Change Tracking

Tracking Changes in your Enterprise

How to find the status of Backup / Restore in SQL Server 2000

2011-05-02T04:05:00.001-07:00

To know the status of backup or restore in SQL Server 2000 ;

1. Find the SPID which is restoring the DB using sp_who2 or from sysprocesses

2. Execute dbcc outputbuffer(SPID)

From the output get the %completed status:

Schema in SQL Server

2011-04-26T01:54:00.001-07:00

Users and schemas are completely separate. The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users;A schema is simply a container of objects. Schemas own objects and principles own schemas.

A single schema can contain objects owned by multiple database users.
A database user can be dropped without dropping objects in a corresponding schema

A schema can be owned by any user, and its ownership is transferable.

Why Schema introduced on SQL Server?

The Users and Schemas separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user. A schema can only be owned by one user at a time, but a single user can simultaneously own many schemas.

How to transfer Schema ownership?

The following example transfers ownership of the schema LabProduction to user Sivaprasad.

ALTER AUTHORIZATION ON SCHEMA::LabProduction TO Sivaprasad;
GO

Changes the ownership of a securable – using ALTER AUTHORIZATION

ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals

How to move objects between Schema?

ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.

For example, below modifies the schema HumanResources by transferring the table Address from schema Person into the schema

ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

-- Create TestDB Database
Create database TestDB
go

-- Change the DB Context to TestDB
Use TestDB
go

-- Created Schema Student
Create Schema Student
go

--– Created table in Student schema –
Create Table Student.Student_Info
(
Student_No int Primary Key identity(1,1),
Student_Name varchar(50)
)
go

--– Insert Data
Insert Into Student.Student_Info Values('Sivaprasad')
go

--– Select Data
Select * From Student.Student_Info
go

--– Create another schema Course
Create Schema Course
go

--– Transfer Objects from Student Schema to Course Schema
ALTER SCHEMA Course
TRANSFER Student.Student_Info
go

--– Assign Permission to Schema
GRANT SELECT ON SCHEMA::Course TO Sivaprasad
go

MS Outlook: The form you selected could not be displayed. Contact your administrator.

2011-04-08T23:05:00.001-07:00

Error message: The form you selected could not be displayed. Contact your administrator.

Cause: Problem with the form cache

Resolution:

Select –> Tools –> Options –> Others Tab

Select Advanced Options..

Select Custom Forms..

Click Manage Forms

Clear Cache

Otherwise try copying the file OUTLOOK.box from other user profiles.

How to set environment variable permanent in DOS

2011-03-14T19:48:00.001-07:00

By using the SET the environment value persist till the current session live.
To set the environment value permanent in DOS use the SETX

Download SETX

Download Microsoft SQL Server 2005 Service Pack 4 RTM

2010-12-18T21:09:00.001-08:00

Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download.

SQL Server 2005 SP4 includes SQL Server 2005 SP3 cumulative update 1 to 11, customer requested fixes, along with instances of the SQL Server 2005 SP4 database Engine support for DAC operations.

It can be downloaded from

Microsoft SQL Server 2005 Service Pack 4 RTM

SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

2010-12-16T06:15:00.001-08:00

Error:
SQL Server has encountered 2 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

Cause:
When we take the database OFFLINE this error raised.

Reference: http://support.microsoft.com/kb/917828

The server principal is not able to access the database under the current security context.

2010-12-15T17:23:00.001-08:00

I reproduced the error below;

Issue 1:

Msg 916, Level 14, State 1, Line 1
The server principal "Test_Login" is not able to access the database "testdb" under the current security context.

1. Created database ‘testdb’

2. Created the login ‘Test_Login’. The login not granted access on the database ‘testdb’

3. On logging on ‘Test_Login’, tried accesing the database ‘testdb’ and got the error.

From the error message, when the server principal ( login) does not have access on the database the error occurs.

Resolution 1:
Grant the database access for the login. For example;

Use testdb
go

Create User Test_Login for Login Test_Login
go

Issue 2:

The database is in “Restricted Mode”

Try the query below, to access the database in different user context

Use testdb
go
execute as user='test_login'
go

Msg 916, Level 14, State 1, Line 1
The server principal "Test_Login" is not able to access the database "testdb" under the current security context.

Reference :
"The server principal LoginName is not able to access the database DatabaseName under the current security context"

TSM TDPSQL Backup failed

2010-12-05T00:28:00.001-08:00

Recently when i tried TSM TDPSQL backup with the new server on SQL Server 2008 failed with the error below.

“ TSM TDP SQL Error: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. Process Exit Code 1914. The step failed. “

Resolution:
After investigation, installed the TSM TDP SQL version 5.5.4.0 from 5.5.0. This resolved the issue.

Uninstall SQL Server 2008 Cluster

2010-12-04T23:46:00.001-08:00

Here goes the SQL Server 2008 un-installation screenshots.

TSQL Query to find the DB Maintenance status

2010-11-18T02:30:00.001-08:00

Are you curious to find the Database Backup / Restore status?
Do you have to find out the status the DBCC CHECKDB ?
Do you have to report when the ROLLBACK would finish ?
Do you want judge how long the Database Shrink will take?

Here is the answer!

The query listed below can be used to find the Percentage of work completed for the following commands:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE,
ROLLBACK
TDE ENCRYPTION

We have to replace the where condition filter based on the requirement.

SELECT command,
            sqltext.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests Req
CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) sqltext
WHERE req.command in ('DBCC CHECKDB')

On SQL Server Management Studio, How to list only the database, which user has access ?

2010-10-20T17:04:00.001-07:00

The default behavior of SSMS when we login, it will show all the databases available on the SQL Server Instance. Irrespective of access on the databases user has.

The reason behind is, to load the SSMS faster. Otherwise, while loading the SSMS SQL Server has to verify for each databases if the user has access or not. Also if any of the databases or offline or closed, it has to figure out if that database has to be listed or not. All this takes time.
Hence by default the SSMS will show all the databases on SSMS.

If we really required to show only the database user has access, please use the command below to restrict the show all databases on SSMS,

DENY VIEW ANY DATABASE TO login_name

When VIEW ANY DATABASE is revoked, a user can only see master, tempdb, any database he owns, and the user’s current database context.

Impact of renaming “sa” account on SQL Server 2008

2010-10-14T04:31:00.001-07:00

Error: 912, Severity: 21, State: 2

Microsoft has confirmed that this is a problem in the Microsoft products that are listed

Microsoft SQL Server 2008 Workgroup

Microsoft SQL Server 2008 Enterprise

Microsoft SQL Server 2008 Developer

Microsoft SQL Server 2008 Standard

This problem was first corrected in SQL Server 2008 Service Pack 1

Cause:

The upgrade process includes a script to create the SP_Syspolicy_Create_Purge_job stored procedure. The procedure creates the syspolicy_purge_history job and hardcodes the owner of the job to be the sa account. Because the sa account is renamed, the job creation fails.

Workaround: recreate the sa account

Reference: FIX: Error message when you try to upgrade an instance of SQL Server 2005 to SQL Server 2008: "Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes"

SQL Server 2008 Service Pack 2 - Installation

2010-10-14T04:17:00.001-07:00

Here Let me show the step by step on SQL Server 2008 SP 2 Installation

SQL Server Job fails with error “Unable to open step output file. The step failed”

2010-10-14T02:58:00.001-07:00

Are you getting the error message below on a SQL Server Job?

Resolution:

See if the SQL Agent Service account have sufficient privileges.

I have encountered this error when I change the SQL Server Agent account.
Resolved giving proper rights on the folder.

Processing order of SELECT Statement

2010-10-09T18:47:00.001-07:00

The following steps show the processing order for a SELECT statement.

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

This is the reason where we can not use column alias on Where clause, however we can use it on Select / Order by.

If interested on the logical order of the query processing poster, download it from here.

Reference: http://msdn.microsoft.com/en-us/library/ms189499.aspx

SQL Server 2008 Service account Privileges

2010-10-07T23:28:00.001-07:00

Please refer the http://msdn.microsoft.com/en-us/library/ms143504.aspx
on setting up Windows Service Accounts.

It discuss about the

Domain User Account
Local User Account
Local Service Account
Network Service Account
Local System Account
Windows NT Rights and Privileges Granted for SQL Server Service Accounts
Access Control Lists Created for SQL Server Service Accounts
Windows Permissions for SQL Server Services

Can we have SQL Server 2000 default Instance and SQL Server 2005 default Instance on the same server?

2010-10-06T18:31:00.001-07:00

The default instance could be an installation of

SQL Server 2000,
SQL Server 2005,
SQL Server 2008, or
SQL Server 2008 R2.

Only one installation of SQL Server, regardless of version, can be the default instance at one time

SQL Server 2008 Service Pack 2 released

2010-09-29T17:39:00.001-07:00

SQL Server 2008 SP 2 released on 29 Sep 2010.

To download the SQL Server 2008 SP2 release please select from the links below:

SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550

SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551

SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815

Oracle Linked Server issue

2010-09-14T18:27:00.001-07:00

Today I got the error below on the SQL Server job it runs a SSIS Package.

Error:
Msg 7302, Level 16, State 1, Server USIVA007, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
"SYS01_ORACLE01"

Resolution:
Strange but recreating of the Linked Server connection, resolved the issue.

Reference:
Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider

SQL Server Performance Optimization Seminar in Chennai by Sep 24 – 15, 2010

2010-08-31T18:57:00.001-07:00

This seminar series targeting the audience across the world. It is begin from Chennai, India.

What is unique about the seminar & why i am interested in it

Presenter Pinal Dave, well known on SQL Server community for his presentation skill
Solid Quality Mentors differ from usual training / mentoring by giving real time & practical sessions
It’s worth the time spent since learning the tips may take years
More of interactive session with presenter rather dull theoretical sessions
Real time experience on the issues faced by customers
Unique in design
Immerse in deep on the concept of SQL Server Performance optimization
Follow up sessions to keep engaged on what we learned

What can we get out of the two day session;

Day 1: Sep 24th 2010	Day 2: Sep 25th 2010
Resolution to popular misconception on SQL Server features	Evils of Index and workarounds
The unknown Tricks and Tips of sub queries	The Intelligence of Statistics
The Relation between External and Internal Joins	Query Optimization for unknown values
Triggers and Views	Query Optimization techniques
Question hour	Question hour

Registration Form

Contacts : For clarifications, please call +91 98202 24429

Further details: http://www.solidq.in/Training/DirectionsSeminar/SQLChennai.aspx

About the Solid Quality Mentor:

Solid Quality Mentors is the only company in the world with the largest number of the top experts on Microsoft’s Data Platform.
A Microsoft Gold Certified Partner

Website: http://www.solidq.com/

About the seminar presenter – Pinal Dave:

Pinal Dave is a Database Architect and Corporate Trainer specializing in SQL Server Query Optimization and Performance Tuning having 7 years of rich, hands-on experience. He has been awarded as the Microsoft Most Valuable Professional (MVP) in SQL Server technology and is also a Microsoft Certified Trainer (MCT)

Website: http://blog.sqlauthority.com/

MOSS – Microsoft Office Sharepoint Server 2007 SQL Server 2005 / 2008 Database Maintenance

2010-08-10T04:14:00.001-07:00

There is a whitepaper in detail SQL Server 2005 /2008 Maintenance for MOSS ( Sharepoint ).

Database Maintenance for SharePoint

SQL Server 2008 R2 Update for Developers Training Kit

2010-08-08T01:40:00.001-07:00

The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

Download it from here, it sizes 216.5 MB.

It contains PPT Slides, Video, Demo and Hand on Lab

Come and Play on SQL Server 2008 R2

2010-08-01T03:32:00.001-07:00

Register or login to experience the SQL Server 2008 R2 virtual lab.

The environment also host the SharePoint 2010 and MS Office 2010.

http://www.previewsqlserver.com/index.htm

Me in SQL Data Camp in Chennai

2010-07-28T02:19:00.001-07:00

I attended the SQL Data Camp in Chennai and received a RedGate book as a token of one the most active participant of the event.

Attending SQL Data Camp @ Chennai on 17th July 2010

2010-07-15T04:19:00.001-07:00

I will be attending the SQL Data Camp, in Chennai on 17th July 2010

How to find who dropped the database? Is that logged on SQL Server?

2010-07-14T18:55:00.001-07:00

Are you one looking to find answer for questions like below.,

· How to find who dropped a database?

· How to find who created the database?

· How to find who altered the database?

· How to find who altered the database configurations?

· How to find who dropped the schema?

· How to find who altered the schema?

· How to find who altered the server configuration?

· How to find who modified the login?

· How to find who modified the table?

If it is then, are you wondering is that possible on SQL Server?

Yes, SQL Server can beginning from version SQL Server 2005 onwards.

That is where SQL Server Default trace come into picture.

What is SQL Server Default Trace?

Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.

To get the default trace options

SELECT * FROM ::fn_trace_getinfo(default)

To know what is audited on default trace

SELECT Trc.EventID, Trc.ColumnID, Evt.name as Event_Description, Col.name as Column_Description

FROM ::fn_trace_geteventinfo(1) Trc

JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id

JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id

Example:

Below is the screenshot “Schema Changes History” of Standard Reports at Server Level.

See that “it shows the event of Drop and Alter of the database

The same can be queried from SQL Server Default Trace like;

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

Usage
Default trace can be queried to find answers on

To finmd the Create / Alter / Drop i.e., DDL changes at Database,Schema and Object level
To find the configuration changes at SQL Server Instance, Database, Schema and Object Level
To find Login creation , modifications and failures
To find performance issues like missing of statistics, join predicate, hash or sort warnings
Database file growth or shrinkage
DBCC operations

Points to ponder

Default trace is a Server Side trace and it is enable by default.
Default trace properties can not be modified, other than enabling and disabling of this trace.
Default trace by default enabled and stored on the “Log” folder
Default trace file will be created newly on start of the SQL Server service.
A single trace file can store up to 20 MB, when it is full new file will be created.
SQL Server would maintain 5 default trace file, where the file with highest number is the latest.
Default trace is light weight and it is not replacement for DDL Trigger. Default trace log only basic information, when detailed information needed DDL trigger is the option.

Using Spatial Data type to draw circle in SQL Server 2008

2010-07-12T22:30:00.001-07:00

--- Draw Circle in SQL Server 2008
declare @g Geometry
set @g = 'Point(10 5)'
select @g.STBuffer(5)

SQL 2000 SP4 Installation Error sp_vupgrade_replication(1)

2010-06-19T16:20:00.001-07:00

Error

• Msg 170, Level 15, State 1, Server MS, Line 1

Line 1: Incorrect syntax near '-'.

The sqlsp.log file indicates:

Process Exit Code: (0)

Error running script: sp_vupgrade_replication (1)

Action CleanUpInstall:

Installation Failed.

Resolution:
1. Check if distribution database exist though the Replication is dropped
2. If so drop the distribution database
3. To drop the distribution database by over - restoring of some user database
4. Perform the patching / SQL SP again.

SQL2000 SP4 Installation Error sp_vupgrade_replication(1)

2010-06-16T16:11:00.000-07:00

SQL Server 2005 Patching fails CREATE DATABASE failed.

Error:

Error Number : 29537

Error Description : MSP Error: 29537 SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed.
Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.

CAUSE:
By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location,
If the location doesn't exist, it fails to create the database and the setup fails.

RESOLUTION:
1. In SSMS, go to Server Properties > Database Settings > Database Default Locations ,check default location for Data and Log .

2. Try to access locations specified at Data and Log, Make sure files are available there.
3. If any of the files missing from above location , we hit the above error.
4. In that case, change the path to point to reall location of mdf/ldf files .
5. Setup should now succeed.

1. Reference: a recently applied update, kb955706, failed to install

Delete files using DOS command FORFILES

2010-06-10T05:19:00.001-07:00

To delete te

--Delete all .bak files in the named directory and its subfolders where the file modified date is more than 2 days old.

EXEC xp_cmdshell 'FORFILES /p "L:\MSSQL$PRODSQL2KWEB\Backup" /s /m *.bak /d -2 /c "CMD /C echo Deleting the file : @file del /Q /F @FILE && del /Q /F @FILE " '

FORFILES Parameters

Parameter Name Description

/p Path

/m Search Mask (default is *.*)

/s Subdirectories will be searched recursively if this parameter is included

/c <command> Command to be executed against each file in the result set, commands must be enclosed in double-quotes, default is "cmd c/ echo @file"

/d Date range for file selection, using Last Modified Date as the criterion for the file. When the /d parameter is in the form of MM/DD/YYYY, file meeting the criteria of +/- the specified date are included. When in the format of a smallint (-32,768 - 32,768) the files +/- the files with a modified date +/- that number of days from the current date are included in the file result set.

Variables

Variable Name Description

@FILE File name

@FNAME File name without extension

@EXT File extension

@PATH Full path of the file

@RELPATH Relative path of the file

@ISDIR Evaluates as TRUE if the file type is a directory

@FSIZE File size in bytes

@FDATE Last modified date stamp on the file

@FTIME Last modified timestamp on the file

Privileges needed to run SQL Server Profiler without sysadmin privileges

2010-06-08T16:24:00.000-07:00

In starting with SQL Server 2005 and above a SQL Server user or login does not need to have SYSADMIN privileges to use the SQL Server Profiler.

It can be accomplished by,

GRANT ALTER TRACE to [Login]

BOL explanation:

By default, running SQL Server Profiler requires the same user permissions as the Transact-SQL stored procedures that are used to create traces. To run SQL Server Profiler, users must be granted the ALTER TRACE permission

References: Permissions Required to Run SQL Server Profiler

Query to find when the table last accessed on SQL Server 2005 and above

2010-05-29T16:45:00.001-07:00

------ Query to find when the User Table Last accessed ---------

WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

------ Query to find when the Database Last accessed ---------

SELECT name, last_access =(
                select X1= max(bb.xx)
                from ( select xx =
                        max(last_user_seek)
                        where max(last_user_seek)is not null
                        union all
                        select xx = max(last_user_scan)
                        where max(last_user_scan)is not null
                        union all
                        select xx = max(last_user_lookup)
                        where max(last_user_lookup) is not null
                        union all
                        select xx =max(last_user_update)
                        where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
group by d.name

Deny specific user from Remote Login to SQL Server

2010-05-29T16:36:00.001-07:00

SQL Server Query to deny user on remote login feature, let user connect to database engine only from localhost (or 127.0.0.1).

This query below applies to SQL Server 2005 with SP2 and above higher version. It uses the SQL Server Logon Trigger

CREATE TRIGGER Deny_Remote_Login_Login1_trigger

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

BEGIN

IF ORIGINAL_LOGIN()= 'Login1' AND

(SELECT * FROM sys.dm_exec_sessions

WHERE is_user_process = 1 AND

original_login_name = 'Login1' --- Checking the given login

and Host_Name not in ( 'SQLServerHostName') --- Connecting Remotely

and Session_ID = @@SPID )

BEGIN

PRINT 'RemoteLogin Attempt attempt of ' + ORIGINAL_LOGIN()+' denied, please login locally'

ROLLBACK;

END

END;

SQL Server 2005 Uninstall Error: The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error: 2147749896 (0x80041008).

2010-05-18T16:32:00.000-07:00

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008).

Resolution: Copy and run the DOS batch file below

@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End
:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer
:SkipSrv
goto End
:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt
:End

Thanks to: http://social.msdn.microsoft.com/Forums/en/Vsexpressinstall/thread/632ca405-1c38-405b-9ed3-01785c9f99d1

How to remove the subscription from Publication in SQL Server Replication

2010-05-17T16:28:00.000-07:00

1. On SQL Server 2000 connect to Enterprise Manager
On SQL Server 2005 / 2008 connect to SQL Server Management studio

2. Expand Replication à Publication, select the Publication from which the subscribers to be removed.
On SQL Server 2005 / 2008 from the Replication à Local Publication select the Publication

3. Right click on the Publication

4. Select the Subscription tab

5. Select the Subscriber and click Delete button, that’s it

6. If you hold the “Shift” key you can delete more than one subscriber. However you have to execute the sp_subscription_cleanup each subscribers manually.

7. Rather if you delete one subscriber at a time, it is simple and no need to execute the stored procedures at subscriber.

How to apply Service pack on SQL Server 2005 Cluster

2010-05-04T03:59:00.001-07:00

1. Backup of all the System + User Database

2.Windows Login account with "Local Administrator" privileges on all the nodes involved

3. Ensure all the recourses part of the SQL Server Cluster resource group are online.
I recommend to reboot both nodes before installation. (to avoid pending reboot, hanging installers, terminal sessions etc. problems.)

4. Cross check if the cluster failover on the nodes involved successfully.

5. shut down all applications that use SQLServer (because you know the instance(s) will get offline at some point(s) during the upgrade)

6. Disable if any DDL Triggers

7. Verify if following services online on the nodes involved.

Task Scheduler
Com+ System Application Service
Cryptographic Services
Remote Registry Service

8. Pause Database Mirroring

Log on the active node, apply the Service pack. ( It is opposite in case of SQL Server 2008 ! ). You cannot install SP3 from any passive nodes in the failover cluster.

Please note SP will not update the client tools on the passive node. you need to do that manually; though it is cluster aware.

Reference:

How to install SQL Server 2005 SP3

SQL Server 2005 SP3 Release Notes

SQL Server 2008 failover cluster rolling patch and service pack process

SQL Server 2005 Service Pack 4

2010-05-01T04:20:00.000-07:00

SQL Server 2005 Service Pack 4 ( SQL Server 2005 SP4 ) will be released by end of the year 2010 ( Quarter 4 of 2010 ). It will be the last Service pack for the SQL Server 2005.

SQL Server 2008 Service Pack 1 ( SQL Server 2008 SP1 ) will be released by third quarter of the year 2010 ( Quarter 3 of 2010 ). It will be the last Service pack for the SQL Server 2005.

Source: Shared by the Microsoft SQL Server release services group manager.

Learn SQL Server 2008 for free

2010-04-26T01:47:00.001-07:00

Microsoft makes it easier than ever to attend SQL Server 2008 training, at little to no cost, without any travel necessary.

It just needed a email account on hotmail or msn.

Subject matter includes:

· Database Administration

· Database architecture

· Database, programming and security.

Click the courses below to see full descriptions or visit Microsoft Learning to browse the course catalog

DBA Track

Course 6339: Database Fundamentals in Microsoft SQL Server 2008

Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform

Collection 6187: What’s New in Microsoft SQL Server 2008

Developer Track

Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

Clinic 10164: Essential SQL Server 2008 for Developers

Clinic 10258: SQL Server 2008: Upgrade and Application Compatibility

Reporting Service

Clinic 6258: New Features Of Microsoft SQL Server 2008 Reporting Services

Business Intelligence

Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence

DAC – Dedicated Administrator Connection

2010-04-25T18:40:00.001-07:00

Use Command below to connect as DAC

SQLCMD -A

It is on by Default for local server.

For remote(via network) enable it by running query below

sp_configure
'remote admin connections'

For Clusters, recommended to enable remote admin connections as no local DAC available

---- Query to Find if DAC - Dedicated Admin Connection used -----

select s1.session_id, s1.program_name,s1.host_name,t1.name

from

sys.tcp_endpoints t1

join
sys.dm_exec_sessions s1

on t1.endpoint_id = s1.endpoint_id

where t1.name =
'Dedicated Admin Connection'

--- End of the Query -----

--- Query to check the DAC ----

--- Scheduler_ID = 255 & Status = "VISIBLE ONLINE (DAC) ---

select
*
from
sys.dm_os_schedulers

Usage:

when SQL Server hangs, SQL Server does not respond, when SQL Server does not accept new connection

Use DAC sparingly for monitoring and Troubleshooting
Connect to master database first
Use lightweight queries.
Not advised for long running queries like ( DBCC CHECKDB, DBREINDEX, INDEXDEFRAG )

SQL Server Default Collation

2010-04-23T04:59:00.001-07:00

Technorati Tags: SQL Server Setup

SQL Server Default Collation

SQL Server Latest Service Pack and Security Hot fixes

2010-04-22T03:42:00.001-07:00


Applies to	Version Range	Type	Description	Target Version	Release Date	Security Bulletins	KB	Download
SQL Server 2000	Below 8.00.2039	Service Pack	SQL Server 2000 SP4	8.00.2039	6-Jun-05	MS03-031	KB884525	http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
SQL Server 2000	8.00.2039-8.00.2054	GDR	SQL Server 2000 SP4 GDR	8.00.2055	10-Feb-09	MS09-004	KB960082	http://www.microsoft.com/downloads/details.aspx?familyid=d5bb816a-6e1a-47cb-92be-51c565ee184c&displaylang=en
SQL Server 2000	8.00.2148-8.00.2281	QFE	SQL Server 2000 SP4 QFE	8.00.2282	10-Feb-09	MS09-004	KB960083	http://www.microsoft.com/downloads/details.aspx?familyid=A93F3CFE-18C9-4218-A551-13BF415E418A&displaylang=en
SQL Server 2005	Below 9.00.4035	Service Pack	SQL Server 2005 SP3	9.00.4035	15-Dec-08	N/A	KB955706	http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en
SQL Server 2005	9.00.4035-9.00.4052	GDR	SQL Server 2005 SP3 GDR	9.00.4053	12-Oct-09	MS09-062	KB970892	http://www.microsoft.com/downloads/details.aspx?familyid=0d878f4b-71e8-4170-9a14-1bce684811ce&displaylang=en
SQL Server 2005	9.00.4205-9.00.4261	QFE	SQL Server 2005 SP3 QFE	9.00.4262	12-Oct-09	MS09-062	KB970894	http://www.microsoft.com/downloads/details.aspx?familyid=e6f307c1-8b21-406e-9c6f-b1a3a1e9a98f&displaylang=en
SQL Server 2008	Below 10.00.2531	Service Pack	SQL Server 2008 SP1	10.00.2531.00	27-Aug-09	N/A		http://www.microsoft.com/downloads/details.aspx?FamilyID=66ab3dbb-bf3e-4f46-9559-ccc6a4f9dc19&displaylang=en

	It lists only the latest SQL Server Service Pack and Security Hot fixes as on 22nd April 2010

Software Asset Management - SAM

2010-04-22T03:36:00.001-07:00

Software Inventory Assistance

Whether we have just a few computers or a large network, a software inventory tool can make it easier to quickly know what we have and how it's being used.

The Microsoft Software Inventory Analyzer (MSIA) is a free tool available for download that can help us with our software inventory. It is built specifically to be a starting point to working with SAM.

We can use the MSIA to scan and inventory the Microsoft software that is installed on a single computer, or on multiple computers throughout a network. It generates a report that provides details of all installed Microsoft products, including the type and the number of licenses. The MSIA will work with networks that have 250 or fewer computers. The latest MSIA 5.1 features enable you to:

Identify and report all installed Microsoft products listed under Add or Remove Programs.
Consolidate the MSIA report with the Microsoft Licensing Statement (MLS) report (.xls only).

This information is useful for you, if you are taking care of the system administration, licensing and inventory of Microsoft software products in your organization.

BgInfo

2010-04-22T03:34:00.001-07:00

BgInfo – Microsoft Windows SysInternal tool

How many times have you walked up to a system in your office and needed to click through several diagnostic windows to remind yourself of important aspects of its configuration, such as its name, IP address, or operating system version If you manage multiple computers you probably need BGInfo. It automatically displays relevant information about a Windows computer on the desktop's background, such as the computer name, IP address, service pack version, and more. You can edit any field as well as the font and background colors, and can place it in your startup folder so that it runs every boot, or even configure it to display as the background for the logon screen.

SQL Server 2005/ 2008 Query to find when the database last used

2010-04-21T05:15:00.001-07:00

Below is the TSQL Query to find when the database last accessed for SQL Server 2005 / 2008

-- Query to find when the database last accessed on SQL Server 2005 / 2008 ---

select d.name, x1 =

(select X1= max(bb.xx)

from (

select xx = max(last_user_seek)

where max(last_user_seek) is not null

union all

select xx = max(last_user_scan)

where max(last_user_scan) is not null

union all

select xx = max(last_user_lookup)

where max(last_user_lookup) is not null

union all

select xx = max(last_user_update)

where max(last_user_update) is not null) bb)

FROM master.dbo.sysdatabases d

left outer join

sys.dm_db_index_usage_stats s

on d.dbid= s.database_id

group by d.name

Differentiate your connections to SQL Instances by coloring it

2010-04-21T05:11:00.001-07:00

What would be the case. have you ever connected to a server, thought it was a testing or development system, and only a split second after you pressed "F5" to run that command, realized that it was the production server? Yeah….me neither :).

To make sure that's never you, when you start SQL Server 2008 Management Studio (SSMS), stop for a moment on the connection dialog and press "Options". You'll see that you can select a color for the connection – I set mine to red for production servers, yellow for testing servers, and green for development servers.

Once inside SSMS, you can also set the default color for all connections – I do this and set them all to red, so that if I forget, at least I'll have that visual indicator to make sure the change I'm making is on the right server.

It helps on classifying the SQL Server Instances by Production, Development, Test or QA etc.

Format Date or Time without “/”

2010-04-20T03:21:00.001-07:00

Format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Output
select replace(convert(varchar, getdate(),111),'/','')	20100413
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	20100413 004426
Select cast(int, convert(char,getdate(),111))	20100413

Remote Desktop Error - The terminal server has exceeded the maximum number of allowed connections

2010-04-19T04:48:00.001-07:00

When trying to connect using a remote desktop session you may get this Login Failure Message, because there are too many remote sessions

To resolve the exceeded connection issue execute "mstsc /admin" ( mstsc /console )command from the command prompt as shown below

If a terminal server has exceed the maximum number of sessions an "/admin" session can still be created and this will allow you to still connect to the server.

The /admin sessions don't count towards the session limit that may be configured on a terminal server to limit the number of remote sessions, so this will give you a back door into the server if you get the message "The terminal server has exceeded the maximum number of allows connections".

SQL Server - Patching : An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

2010-04-12T05:44:00.001-07:00

An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems

The SQL Server team is moving away from the current priority-driven hotfix release model to a scheduled delivery model. In the scheduled delivery model, a customer can receive a hotfix to address their most critical situations with a short turn-around time. Additionally, a customer can receive a fix that has undergone more testing and that is released on a schedule basis. Therefore, the SQL Server team has created the following delivery mechanisms.

Critical On Demand - COD	On Demand- OD	Cumulative Update - CU	General Distribution Release - GDR
The hotfix is released on or before a mutually agreed upon date based on the customer's need	The hotfix is released on or before a mutually agreed upon date based on the customer's need	The update is released every 2 months.	A GDR addresses an issue that has a broad customer impact, that has security implications, or that has both. A GDR is determined and issued by Microsoft as appropriate and when appropriate. GDRs are kept to a minimum.
		It contains all previous critical on-demand hotfixes to date.

ISM eliminates the need for hotfix rollup releases. The first CU after a new release contains the hotfix rollup, plus any additional QFEs.

A hotfix package does not replace a service pack. A hotfix package is optional. A hotfix package can be installed or uninstalled at any time. Additionally, hotfix packages are cumulative. Therefore, the latest OD hotfix package or CU hotfix package includes all previously released hotfixes.

Quick Fix Engineering - QFEs are used for the majority of fixes where the effects of the problem are not widespread or severe enough to warrant a GDR.

Can we have database on SIMPLE Recovery model for Transactional Replication?

2010-02-08T15:54:00.000-08:00

Yes. We can have Publisher database on SIMPLE recovery model.

The reason is that;

When executing, the Log Reader Agent first reads the publication transaction log and identifies any INSERT, UPDATE, and DELETE statements, or other modifications made to the data transactions that have been marked for replication. Next, the agent batch copies those transactions to the distribution database at the Distributor.
The Log Reader Agent uses the internal stored procedure sp_replcmds to get the next set of commands marked for replication from the log. The distribution database then becomes the store-and-forward queue from which changes are sent to Subscribers. Only committed transactions are sent to the distribution database.
Only committed transactions are sent to the distribution database by LogReader agent.
After the entire batch of transactions has been written successfully to the distribution database, it is committed. Following the commit of each batch of commands to the Distributor, the Log Reader Agent calls sp_repldone to mark where replication was last completed.
Finally, the agent marks the rows in the transaction log that are ready to be truncated. Rows still waiting to be replicated are not truncated
The transaction log on the Publisher can be dumped without interfering with replication, because only transactions not marked for replication are purged.

SQL Server 2005 Cluster: SQL Server Service not coming online, error on TCP port is already in use

2010-01-14T15:29:00.000-08:00

SQL Server Error Log:

2010-06-04 15:19:37.520 Server Server is listening on [ 10.0.0.136 <ipv4> 2813].

2010-06-04 15:19:37.530 Server Error: 26023, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0xa.

2010-06-04 15:19:37.530 Server Error: 17182, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server TDSSNIClient initialization failed with error 0x2740, status code 0x1.

2010-06-04 15:19:37.530 Server Error: 17826, Severity: 18, State: 3.

2010-06-04 15:19:37.530 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2010-06-04 15:19:37.530 Server Error: 17120, Severity: 16, State: 1.

2010-06-04 15:19:37.530 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event Viewer Error

Event Type: Error

Event Source: MSSQL$PROD2K5ISD

Event Category: (2)

Event ID: 26023

Date: 4/06/2010

Time: 2:51:31 PM

User: N/A

Computer: PROD2K5ISD

Description:

Server TCP provider failed to listen on [ 10.0.0.136 <ipv4> 2813]. Tcp port is already in use.

Resolution

The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid

SQL Server 2005 /2008 Express Edition Backup automation

2010-01-11T15:59:00.000-08:00

SQL Database Backup Script

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)

select @IDENT=min(DBID)
from
SYSDATABASES
WHERE [DBID] > 0 AND NAME NOT
IN
('PUBS',
'NORTHWIND',
'TEMPDB','AdventureWorks')

while @IDENT is
not
null

begin

SELECT @DBNAME = NAME FROM
SYSDATABASES
WHERE
DBID
= @IDENT

/*Change disk location here as required*/

SELECT @SQL =
'BACKUP DATABASE '+@DBNAME+' TO DISK = ''C:\SQLBACKUP\Backup\'+@DBNAME+'.BAK''WITH INIT'

PRINT @SQL

EXEC (@SQL)

select @IDENT=min(DBID)
from
SYSDATABASES
WHERE [DBID] > 0 and
DBID>@IDENT AND NAME NOT
IN
('PUBS',
'NORTHWIND',
'TEMPDB','AdventureWorks')

end

DOS Batch files to backup the databases

@echo off

if not exist c:\SQLBackup\Logs MD C:\SQLbackup\Logs

if not exist c:\SQLBackup\Backup MD C:\SQLbackup\Backup

cd c:\SQLBackup

Echo Backup Started >> c:\SQLBackup\Logs\SQLBAckup.log

Date /T > c:\SQLBackup\Logs\SQLBAckup.log

Time /T >> c:\SQLBackup\Logs\SQLBAckup.log

Echo.

echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log

isql -S (local)\Express -E -i c:\sqlbackup\backup.sql >> c:\SQLBackup\Logs\SQLBAckup.log

echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log

echo.

Date /T >> c:\SQLBackup\Logs\SQLBAckup.log

Time /T >> c:\SQLBackup\Logs\SQLBAckup.log

echo Backup Finished >> c:\SQLBackup\Logs\SQLBAckup.log

echo.

echo *************************************************************************** >> c:\SQLBackup\Logs\SQLBAckup.log

Folders will look like,

Schedule it on Windows Task Scheduler

That's it the SQL Server 2005 / 2008 Express Edition database backup is automated now!

SQL Server Lever collation change

2009-12-13T06:50:00.000-08:00

1. Backup all databases.(both system and User)

2. If applicable, backup DTS packages, Jobs, SSIS packages, mailing profiles or other objects installed by the application.

3. Script/take screenshot of logins with level of access on server level as well as the database level, user objects for example - tables, functions, types and stored procs, extended stored procs, if any on model or master db.

4. Detach all the user databases.

5. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

6. Check the tempdb collation and it should be ?Latin1_General_CI_AS? create users with help of screenshot/Script and restore user objects from step 3. Run scripts for functions and types if any.

7. Attach user databases from step 4 / Restore all the databases from the backup from step 1 and all the objects from the backup taken in step1. Refer client mail for further clarifications.

Error message when you open SQL Server Configuration Manager in SQL Server 2008: "Cannot connect to WMI provider. You do not have permission or the se

2009-11-25T15:28:00.000-08:00

While opening of SQL Server Configuration Manager if you get error message of

"Cannot connect to WMI provider. You do not have permission or the server is unreachable"
Cause:
This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server 2008. The 32-bit instance and the 64-bit instance of SQL Server 2008 share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

Workaround:
To work around this problem, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Refer the KB# 956013

The SQL Server Service Broker or Database Mirroring transport is disabled or not configured

2009-11-24T16:37:00.000-08:00

MOM raised alert of "The SQL Server Service Broker or Database Mirroring transport is disabled or not configured".

The Service Broker is enabled by default in SQL Server 2005. Service Broker is enabled by default when a new database is created. Please try to run the following query to see the Broker option on a database.

SELECT name, is_broker_enabled FROM sys.databases;

This alert is triggered by event 9666 in the Application Event log for the OpsMgr database server.However, this event is always generated when you restart SQL because neither the Service Broker nor Database Mirroring are enabled for the Master or
Model databases. "SELECT is_broker_enabled FROM sys.databases" identifies
that the Service Broker is enabled for all other DBs. The alert is a bit counter productive if the rule is there to try to identify which databases are missing this option that actually need it; e.g. the OpsMgr db.Currently my only option is to disable the rule.

How to Script Database Schema

2009-11-23T20:40:00.000-08:00

1. On the database schema to be scripted out, right click
2. Select Tasks --> Generate Scripts
3. Select all the objects in the selected databases
4. Select the appropriate General & Table Options
5. Select the output format

Testing a Connection from Client to SQL Server using UDL

2009-11-19T22:59:00.000-08:00

A connection from client to SQL Server can easily be checked with the help of a .udl file. This is also helpful for building a connection string.

Step 1 - Create a .udl file
Create a text file and name it for example test.udl
Double click on test.udl to open the connection dialog.

Step 2 - Choose the type of connection
Go to the first tab “Provider”
Choose “Microsoft OLE DB Provider for SQL Server” or any other provider according to your needs.

Step 3 - Set up connection properties
Go to the second tab “Connection”, choose the server, enter username/password and choose a database from the drop down list.

Step 4 - Connection String
Once you click on “OK” a connection string based on your connection settings is written to test.udl. After you open test.udl you will find a connection string like this.

[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Password=wrf%32g;Persist Security Info=True;User ID=siva;Initial Catalog=msdb;Data Source=SQLTEST

CPU usage reaching 100% with SP3 on SQL Server 2005

2009-10-11T18:07:00.000-07:00

Symptom:
You install an instance of Microsoft SQL Server 2005 on a computer that has many processors. You upgrade to SQL Server 2005 Service Pack 3 (SP3) or to Microsoft SQL Server 2008. You run a query that uses the Like operator in the WHERE clause or that uses a string comparison function, such as the CharIndex() function. When you run the query many times on multiple connections at the same time, the CPU usage reaches 100 percent, and the server performance decreases significantly.

Then refer the Microsoft article and apply the cumulative update

No Surface Area Configuration (SAC) Tool in SQL Server 2008

2009-10-11T17:58:00.000-07:00

In SQL Server 2008, Surface Area Configuration (SAC) Tool has been added to the deprecated feature list.

So how to do the functionality of SAC configuring Service, Connection and features?

1. For Instance and Database Engine Features:
Select Facets on Properties of Instance / Database on SQL Server Management Studio.

2.For Connections, Protocols and StartUp options:
Use SQL Server Configuration Manager

3.SSAS Features:
It can be done at Property Settings

4.SSRS Features
It can be done by editing the RSReportServer.config file or by using the facets feature

Feature Pack for Microsoft SQL Server 2005 and SQL Server 2008

2009-09-26T00:14:00.001-07:00

Please have a look on the Feature pack of SQL Server 2005 and SQL Server 2008.

SQL Server 2005

SQL Server 2008

SQL Agent fails on SQL Server 2005

2009-09-26T00:11:00.000-07:00

The error log from starting sqlagent is the following:
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, 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. [SQLSTATE 08001]
[000] Unable to connect to server '\SQL2005'; SQLServerAgent cannot start
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, 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. [SQLSTATE 08001]
[382] Logon to server '\SQL2005' failed (DisableAgentXPs)
[098] SQLServerAgent terminated (normally)

Resolution:
-----------

Client side setting would have messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no".

If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client). Just run the sqlncli.msi

Microsoft SQL Server 2008 advantages over Oracle 11g

2009-09-21T01:58:00.000-07:00

SQL Server compared to Oracle

Microsoft SQL Server 2008 advantages over Oracle 11g

* Better Performance and Scalability: SQL Data Services, Filtered Indexes, Advanced Sparse Columns, Column Prefix Compression
* Better Security: Module Signing using Certificate, and integrated updates and patching
* Better Business Intelligence (BI) tools: Optimized cube designers, on-demand processing and instance-based rendering, optimized integration with the 2007 Microsoft Office system

Find more on here

Get up to 25% off Select Microsoft Certification Exams

2009-09-17T10:16:00.000-07:00

Get Your Voucher Code for your Discounted Exams

Find the exam of your choosing from the lists below and select "Get Voucher," complete the registration form and your discounted voucher code will be emailed to you. Once you have obtained your code, plan and schedule your next exam at http://www.prometric.com/Microsoft/default.htm.

http://www.prometric.com/microsoft/careeroffer.htm

* Step 1: Using the voucher number, schedule and pay for your initial exam via Prometrics's web site
o You will need to have your voucher number available during the registration process.
* Step 2: Take your exam.

Candidates must register, schedule and take their discounted exams by December 31

How to reset Internel Protocol TCP / IP

2009-09-16T22:13:00.000-07:00

One of the components of the Internet connection on your computer is a built-in set of instructions called TCP/IP. TCP/IP can sometimes become damaged or corrupted. If you cannot connect to the Internet and you have tried all other methods to resolve the problem, TCP/IP might be causing it.

Use the command below:
netsh int ip reset c:\resetlog.txt

Reference : How to reset Internet Protocol (TCP/IP)

You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP

2009-09-14T04:26:00.000-07:00

You may be unable to use a Cisco VPN client and IPsec at the same time in Windows XP or in Windows 2000 or in Windows Server 2003

You may be unable to use the following components at the same time in Microsoft Windows XP, in Microsoft Windows 2000, or in Microsoft Windows Server 2003 Standard Edition with Service Pack 1 (SP1):

* A Cisco virtual private network (VPN) client
* The Internet Protocol security (IPsec) implementation that is built into Windows XP, Windows 2000, and Windows Server 2003

In this situation, you cannot use a VPN tunnel to access resources on an enterprise network on which Domain and Server Isolation policies are deployed.

Refer this here

Do we need SSIS to create Maintenance Plan

2009-05-14T10:21:00.000-07:00

Before SQL Server 2005 SP2 was released Maintenance plans were integreated with SSIS but after SP2 Microsoft has changed that and we do not need SSIS to be installed to run maintenance jobs. And it carried over to SQL Server 2008 too.

How to change the owner of a maintenance plan

2009-05-04T10:50:00.000-07:00

Scenario 1:
-------------
One of our members of staff had left and we had the usual case of a few jobs failing with:

"Unable to determine if the owner (DOMAIN\xxx) of job has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."

Scenario 2:
------------
A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected.

The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days. On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.

/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers

UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'

For SQL Server 2008:

update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'MaintenancePlan'

SQL Server 2000 Service account privileges

2009-05-03T10:21:00.000-07:00

For SQL Server 2000, if you do not want the SQL Server or the SQL Server Agent startup account to be a member of the Local Administrators Group, then the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) must have these user rights:

* Act as Part of the Operating System = SeTcbPrivilege
* Bypass Traverse Checking = SeChangeNotify
* Lock Pages In Memory = SeLockMemory
* Log on as a Batch Job = SeBatchLogonRight
* Log on as a Service = SeServiceLogonRight
* Replace a Process Level Token = SeAssignPrimaryTokenPrivilege

SQL 2005 Standard Edition on Cluster

2009-04-27T21:15:00.000-07:00

SQL Server 2005 Standard Edition will support two-node MSCS,
but Windows 2003 Server Standard won't:

http://technet.microsoft.com/en-us/library/cc758523.aspx

Idera SQL Safe Backup Script

2009-04-10T07:39:00.000-07:00

DECLARE @counter INT
DECLARE @maxCount INT
DECLARE @result INT
DECLARE @failed INT
DECLARE @dbname NVARCHAR(128)
DECLARE @backupSetName NVARCHAR(256)
DECLARE @backupSetDescription NVARCHAR(512)

SET @counter = 1
SET @maxCount = 1
SET @result = 0
SET @failed = 0

-- Create the temporary database table
IF object_id('tempdb..#databases') IS NOT NULL DROP TABLE #databases

CREATE TABLE #databases
(
id INT IDENTITY,
databaseName NVARCHAR(128),
backupSetName NVARCHAR(256),
backupSetDescription NVARCHAR(512)
)

-- Insert the databases to be backed up
INSERT INTO #databases VALUES ('User_DB1', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
INSERT INTO #databases VALUES ('User_DB2', 'Full backup for policy Backup_USER_DB_Full(Noencriptions)', '')
SELECT @maxCount = MAX(id) FROM #databases

-- Loop through and backup each database
WHILE @counter <= @maxCount
BEGIN

SELECT @dbname = [databaseName], @backupSetName = [backupSetName], @backupSetDescription = [backupSetDescription] FROM #databases WHERE id = @counter

EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = 'D:\SQLData\MSSQL\BACKUP\IderaSQLBackups\USER_DB_BACKUPS\FULL\%instance%_%database%_%timestamp%.safe',@delete = '1days',@backupname = @backupSetName,@desc = @backupSetDescription,@verify = 1,@compressionlevel = 'ispeed',@threads = 3
IF (@result != 0) SET @failed = 1

SET @counter = @counter + 1

END

-- If one or more failures occurred, signal an error
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)

SQL Server 2008 Service Pack 1 released

2009-04-08T08:55:00.000-07:00

SQL Server 2008 Service Pack 1 (SP1) is now available at the following URL :
You can use these packages to upgrade any SQL Server 2008 edition.

SQL Server 2008 SP1 is primarily a roll-up of previous cumulative updates, quick fix engineering updates and minor fixes made in response to requests reported through the SQL Server community. While there are no new features in this service pack, customers running SQL Server 2008 should download and install SP1 to take advantage of the fixes which increase supportability and stability of SQL Server 2008.

With this service pack, Microsoft has made investments to ease deployment and make management of service packs more efficient. Key improvements in SP1 include Slipstream, Service Pack Uninstall and Report Builder 2.0 click-once deployment.
• Slipstream allows administrators to install SQL Server 2008 and Service Pack 1 in a single instance. This decreases the total time for an installation, including a fewer number of reboots thereby increasing productivity and deployment availability.
• Service Pack Uninstall allows administrators to uninstall the service pack separately from the database release. This feature also improves DBA productivity, reduces the cost of deployment and improves overall supportability
• Report Builder 2.0 Click Once improves the existing SQL Server end-user report authoring application by easing deployment to business users

For more information about SQL Server 2008 Service Pack 1, please review the Release Notes :

Cluster Diagnostics and Verification Tool (ClusDiag)

2009-03-15T01:07:00.001-07:00

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

2009-03-13T19:06:00.000-07:00

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

2009-03-01T19:52:00.000-08:00

SQL Server 2000 SP3 Creates a SQLDebugger Windows User Account

Removing of the SQLDebugger account in SQL 2000

Automate SQL Server Profiler Trace

2009-02-14T20:07:00.000-08:00

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

2009-02-05T04:54:00.001-08:00

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

2009-02-01T03:34:00.000-08:00

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

2009-01-31T03:51:00.000-08:00

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

2009-01-22T08:39:00.001-08:00

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

2009-01-19T02:05:00.001-08:00

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

2009-01-13T04:23:00.001-08:00

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

2009-01-10T23:16:00.001-08:00

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

2008-12-22T19:52:00.001-08:00

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

2008-12-21T06:21:00.000-08:00

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

2008-12-21T02:33:00.000-08:00

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?

2008-12-18T10:24:00.001-08:00

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];