how to delete temporary files (*.tmp) and bak files (*.bak)

You can use below commands to delete temporary files and .BAK files from all the folders and sub-folders

To delete .tmp files
del *.tmp/f/s

To delete .bak files
del *.bak/f/s

SQL Server and Protocols

TCP/IP and Named Pipes
By default, clients have TCP and Named Pipes as available protocols on most of client computer. You can manipulate the protocol ordering by using the SQL Server Client utility. The client application uses the protocols in the order specified on the client computer. If you are using SQL Server 2005, the protocol order is stored in the ProtocolOrder registry entry under the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0

IPX/SPX
SQL Server 2005 does not support IPX/SPX. The newer versions of NetWare, such as NetWare 6.5, support TCP/IP which makes it the common protocol for all clients.

VIA
The Virtual Interface Adapter (VIA) can be used only by VIA hardware.

Shared Memory
Shared Memory can only be used on the local computer and cannot be used as a network protocol.

Reference: Microsoft SQL Server 2005 Books Online (2006), Index: client connections [SQL Server], about client network connections, choosing a network protocol

Number of articles and readers

Dear Readers,

With your help and support SQLDBPOOL has completed 150 technical articles and the readers stats reached 2,00,000 +, so please keep reading, post your comments, feedbacks and questions.





Thank you for your support.

Jugal Shah

shutdown -i dos command to reboot/shutdown server remotely

[caption id="attachment_556" align="aligncenter" width="300" caption="shutdown -i command to reboot/shutdown server remotely"][/caption]

Default Startup Parameters - SQL Server

Join SQLDBPool Community on dotNetSpider

http://www.dotnetspider.com/sites/1332/-SQLDBPool.aspx

Trace Flag 3226

I have posted this article on DotNetSpider.com Please visit below link for more information.

http://www.dotnetspider.com/resources/37294-Trace-Flag.aspx

Key Points in Disaster Recovery

I have posted this article on DotNetSpider.com. Please follow link.

http://www.dotnetspider.com/resources/37324-Key-Points-Disaster-Recovery.aspx

Script to fix and list out Orphan Users on all the databases on server


USE MASTER
GO
SET NoCount ON
DECLARE @VarDbId INT,
@SQL nvarchar(4000),
@VDBName nvarchar(260),
@OUCounter INT,
@Max_OUCounter INT
SELECT @VarDbId=4,
@SQL =''
CREATE TABLE #OrphaneUsers
(
ID INT IDENTITY (1,1) NOT NULL,
DBName VARCHAR(125) NULL ,
UserName sysname NULL ,
UserSID VARBINARY(85) NULL ,
LoginExists bit NULL
)
WHILE EXISTS
(SELECT database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
)
BEGIN
SELECT TOP 1
@SQL ='Create table #OrphaneUser

(UserName sysname null,

UserSID varbinary(85) null )

insert into #OrphaneUser exec ' + name+ '.dbo.sp_change_users_login ''report''

insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ name+''' as[dbname], UserName, UserSID,0 from #OrphaneUser

drop Table #OrphaneUser',
@VDBName=name
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
ORDER BY database_id
EXEC SP_Executesql @SQL
SELECT TOP 1
@VarDbId=database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
END
UPDATE #OrphaneUsers
SET LoginExists=1
FROM #OrphaneUsers
JOIN syslogins
ON #OrphaneUsers.UserName=syslogins.NAME
SELECT @OUCounter =0,
@Max_OUCounter =COUNT(0)
FROM #OrphaneUsers
WHERE LoginExists=1
WHILE EXISTS
(SELECT TOP 1
id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
)
BEGIN
SELECT TOP 1
@OUCounter=id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Auto_Fix'', '''+UserName+''', NULL, '''+UserName+''''
FROM #OrphaneUsers
WHERE LoginExists=1
AND id =@OUCounter
EXEC SP_Executesql @SQL
PRINT @SQL
END
SELECT *
FROM #OrphaneUsers
DROP TABLE #OrphaneUsers


RAID Levels

RAID Levels
RAID 5:-  RAID 5 will provide good performance throughput and prevent data loss if there is a disk failure. For the database which has high read/write activity, RAID 5 will be a better choice when compared to RAID 0 and RAID 1 in terms of data availability and data protection. For an ideal SQL Server configuration, you should have a combination of RAID 1 and RAID 5. You should mirror the operating system using RAID 1, and place transaction logs on a RAID 1 that is separate from the RAID 1 that hosts the operating system. SQL Server writes data to the transaction logs and maintains serial information of all modifications that occurred in a SQL database. The transaction log files can be used for rollback and roll forward of information from a SQL Server database. The SQL Server files and filegroups should be placed on a RAID 5, because you get best performance throughput by placing database files on RAID 5 disk array.

RAID 10 RAID 10 is a better choice than RAID 5, but RAID 10 would be more expensive than RAID 5. RAID 10 is a combination of RAID 0+1, which is known as striping with mirroring. You should first mirror disks and then create a stripe set of mirrored disks. This provides high fault tolerance for data and excellent throughput performance.

RAID 1 RAID 1 is known as disk mirroring. You need a minimum of two disks to form a RAID 1 array. One primary disk is used for read/write operations and the data is replicated to the second disk. This RAID level offers better read performance but slower write performance.

RAID 0 RAID 0 is known as disk striping. This RAID level stripes data across disks in the array, offering better throughput on the read/write operations. However, there is no data protection offered in this RAID level. If one disk fails, the data stored on the disk will be lost.

Enabling Filestream to store compress documents

Problem: If you are in the process of enabling FILESTREAM on volume and you also want to ensure that documents will be stored in the compressed form but during that time you notice that volume F:\ drive is not formatted. You need to make sure that the volume will be able to store documents in a compressed form.  You can fix the issue by using below command.

Command to format F:\
format F: /FS:NTFS /V:MYBLOBContainer/A:4096 /C

The above command will format the volume, convert it to NTFS and then label it as MYBLOBContainer. It will also set the NTFS cluster size to 4096. It is important to note that the cluster size must be 4096 or smaller for compression to be enabled for a volume. And at last, the /c parameter will compress the volume. When you are storing the FILESTREAM data, you can store it on a compressed or a non-compressed volume. The data in FILESTREAM is actually stored on the file system. There are a number of benefits to storing the data on the file system. For instance, you will be able to compress data with the NTFS compression capabilities. When the data is stored, it is in compressed form and when the data is retrieved, it is decompressed.

Max Degree of Parallelism and MAXDOP

Max Degree of Parallelism
If you do not want to use all of the installed processors in your SQL Server operations, you can limit the use of processors by configuring the processor value to a lower number than the actual number of processors installed. For instance, if a server has four processors and you want to use only three for parallelism, then you should set the max degree of parallelism option to 3.

The default option, 0, uses the actual number of processors installed in the system for the parallel execution plans of queries and index operations.

sp_configure 'max degree of parallelism', 6;
GO
RECONFIGURE WITH OVERRIDE;
GO

MAXDOP
When you want to use a specific number of processors for an individual query or index operation, you can use the MAXDOP query hint or index option to provide better control. MAXDOP overrides the settings defined in the max degree of parallelism option. The use of MAXDOP is limited to the execution of a query that contains the MAXDOP query hint or index option.

Use below extended procedure to get the number of processors.
EXECUTE xp_msver 'ProcessorCount'

Example
SELECT
database_id AS databaseID
, QUOTENAME(DB_NAME(database_id)) AS 'databaseName'
, [OBJECT_ID] AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */
FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
WHERE avg_fragmentation_in_percent >= 30
And index_id > 0
And page_count > 8 -- ignore objects with less than 1 extent
And index_level = 0 -- leaf-level nodes only, supports @scanMode
OPTION (MaxDop 2);

Join Me on dotnetSpider.com

Dear Readers

Hope you are doing well and enjoying all these database articles. I am also helping the user community on dotnetSpider.com

I am requesting you to join  http://www.dotnetspider.com, which is a very good site for knowledge sharing. The most attractive feature of the site is, it has a revenue sharing program in association with Google. When you post any content or share your knowledge through various sections like Discussion Forums, 90% of the revenue generated from Google Advertisement in those pages are shared with the authors.

Since the revenue sharing program is approved by Google and the revenue is paid to members directly by Google when it reaches certain minimum limit, this is a very reliable program to earn some pocket money during your free time.

If you plan to join this site, please use my user id (tojugalshah123) as the referrer so I will be added in your buddy list

regards,
Jugal Shah

SQL Server 2008 R2 Editions and Key Features

The release of SQL Server is officially called SQL Server 2008 R2, which replaces the code name SQL Server Kilimanjaro. 

SQL Server 2008 R2 edition will come into below different flavors
 

1. Data Center Edition


2. Parallel Data Warehouse


3. Enterprise Edition


4. Standard Edition

 

List of Key features
 
Data Center Edition

• Application and Multi-Server Management for enrolling, gaining insights and managing over 25 instances, During the airlift event Microsoft Program Manager mentioned that  they have tested this feature by adding more than 200 servers


• Highest virtualization support for maximum ROI on consolidation and virtualization


• High-scale complex event processing with SQL Server StreamInsight


• Supports more than 8 processors and up to 256 logical processors for highest levels of scale


• Supports memory limits up to OS maximum

 
Parallel Data Warehouse

• 10s to 100s TBs enabled by massively parallel processing architecture and compatibility with hardware partners


• Advanced data warehousing capabilities like Star Join Queries and Change Data Capture


• Integration with SSIS, SSRS, and SSAS


• Supports industry standard data warehousing hub and spoke architecture and parallel database copy

 
Enterprise Edition

• PowerPivot for SharePoint to support the hosting and management of PowerPivot applications in SharePoint


• Application and Multi-Server Management for enrolling, gaining insights and managing up to 25 instances (CPU and Storage). Microsoft is planning to cover Memory in the next release


• Master Data Services for data consistency across heterogeneous systems


• Data Compression now enabled with UCS-2 Unicode support

Standard Edition

• Backup Compression to reduce data backups by up to 60% and help reduce time spent on backups


• Managed instance for Application and Multi-Server Management capabilities

Reference: Microsoft Books Online and SQL Server 2008 R2 Airlift Event

Pictures of my Kerala Visit

Download SQL Server 2008 R2 CTP

Dear Readers,

You can download the SQL Server 2008 R2 CTP version from the below link

http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx

Regards,
Jugal Shah

SQL Server 2008 R2 Airlift and Report Builder 3.0

In SQL Server 2008 Airlift there was small session on Report Builder 3.0 Microsoft Report builder is has below features and it is also integrated with the Share Point

List of features included in Report Builder 3.0

I will walk you through the below features shortly

• Shared Datasets
• SharePoint List Data Extension
• Configuring a Map Layer
• Calculating Aggregates of Aggregates
• Enhanced Support for Expressions
• Overall Page Number and Overall Total Pages
• Page Name
• Render Format
• Rotating Text 270 Degrees (Property)
• Naming Excel Worksheet Tabs while export
• Rendering Reports to Data Feeds
• Writable Variables
• Cached data
• Drill down reports

SQL Server 2008 R2 Airlift Event

Dear Readers,

During my Seattle visit I got a chance to attend the Microsoft SQL Server 2008 R2 Airlift event and it has covers the below topic.

The event was fantastic and very knowledgeable. I would like to thank all the Microsoft Program Managers who has given their best to us.

Event has captured the below topics and I will do my best to share the SQL Server 2008 R2 and New feataures.

1. SQL Server 2008 R2 Application and Multi Server Management
2. Managed Self Service Business Intelligence (Reporting Services)
3. Managed Self Service Business Intelligence (Analysis and PowerPivot)
4. Managed Self Service Business Intelligence (Microsoft Office and SharePoint)
5. SQL Server 2008 R2 Master Data Management

Script to Unlock SQL User account

Easy way without passing Password to unlock SQL user account is just check/unchecked Password policy check box.

USE [master]
GO
ALTER LOGIN [Jugal] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO
ALTER LOGIN [Jugal] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO


Insert Extended Characters into a SQL Server Table Using OSQL and T-SQL

Dear Readers,

You can read my artilce on MSQLTips.com. Please follow below link.

http://www.mssqltips.com/tip.asp?tip=1953

Thank You,
Jugal Shah

Database Migration Checklist

Dear Readers,

You can read my artilce on MSQLTips.com. Please follow below link.

http://www.mssqltips.com/tip.asp?tip=1936

Thank You,
Jugal Shah

An Apple in the Bag

Dear Readers,

Good Story, please read it.

A teacher teaching Maths to seven-year-old Arnav asked him, "If I give you one apple and one apple and one apple, how many apples will you have?"Within a few seconds Arnav replied confidently, "Four!"

The dismayed teacher was expecting an effortless correct answer (three).  She was disappointed.  "Maybe the child did not listen properly," she thought.  She repeated, "Arnav, listen carefully.  If I give you one apple and one apple and one apple, how many apples will you have?"

Arnav had seen the disappointment on his teacher's face.  He calculated again on his fingers.  But within him he was also searching for the answer that will make the teacher happy.  His search for the answer was not for the correct one, but the one that will make his teacher happy.  This time hesitatingly he replied, "Four…"

The disappointment stayed on the teacher's face.  She remembered that Arnav liked strawberries.  She thought maybe he doesn't like apples and that is making him loose focus.  This time with an exaggerated excitement and twinkling in her eyes she asked, "If I give you one strawberry and one strawberry and one strawberry, then how many you will have?"

Seeing the teacher happy, young Arnav calculated on his fingers again.  There was no pressure on him, but a little on the teacher.  She wanted her new approach to succeed.  With a hesitating smile young Arnav enquired, "Three?"

The teacher now had a victorious smile.  Her approach had succeeded.  She wanted to congratulate herself.  But one last thing remained.  Once again she asked him, "Now if I give you one apple and one apple and one more apple how many will you have?"

Promptly Arnav answered, "Four!"

The teacher was aghast.  "How Arnav, how?" she demanded in a little stern and irritated voice.

In a voice that was low and hesitating young Arnav replied, "Because I already have one apple in my bag."

"When someone gives you an answer that is different from what you expect don't think they are wrong.  There may be an angle that you have not understood at all. You will have to listen and understand, but never listen with your mind already made up."

Create A Love Account

Mother Teresa once said, "There are no great acts. There are only small acts done with great love." What small acts can you do today to deepen the bonds between you and the people you value the most? What random acts of kindness and senseless acts of beauty can you offer to someone in an effort to make his or her day just a little better? The irony of being more compassionate is that the very act of giving to others makes you feel better as well.

To practice being more loving, create a love account. Each day, make a few deposits in this very special reserve by doing something small to add joy to the life of someone around you. Buying your partner fresh cut flowers for no reason at all, sending your best friend a copy of your favorite book or taking the time to tell your children in no uncertain terms how you feel about them are good places to start.

If there is one thing that I have learned in life, it is that the little things are the big things. Those tiny, daily deposits into the love account will give you far more happiness than any amount of money in your bank account. As Emerson said so eloquently, "Without the rich heart, wealth is an ugly beggar." Or as Tolstoy wrote, "The means to gain happiness is to throw out for oneself, like a spider, in all directions an adhesive web of love, and to catch in all that comes."

From Author's desk

Dear Readers,

First of thank you very much for reading this blog

I hope this blog will help you in clearing your interviews, daily database issues and will enhance your knowledge.

As you aware WORDPRESS is not supporting Google Ads until you purchase its domain rights due to security reasons.

To earn some money by using Google Ads I have started blogging on BLOGSPOT.COM and the URL of my new blog is as below. I believe I will get the same kind of support and traffic on this blog as well.

http://dbsconsultant.blogspot.com/

Thank You,
Jugal Shah