How to generate Cluster.log for Windows or SQLServer Cluster troubleshooting

If you are doing troubleshooting with your Windows/ SQLServer Cluster you need to generated Cluster.log

Use below command to generate the cluster.log
C :\> cluster /cluster:clustername log /gen

The logs will be generated on each cluster node under %WinDir%\Cluster\Reports folder.You need to login into each node in order to collect the stats.

You can use below command to generate the cluster.log for all nodes on the node where you are running the command.

C:\> cluster /cluster:clustername log /gen /copy:.

Using Powershell - PowerShell is a cluster scripting language.

For the Windows Server 2008 R2 you may access PowerShell management
Start à Administrator Tools à Windows PowerShell Modules.

You can load PowerShell with Failover Clustering in two ways:
1. Open “Failover Cluster PowerShell Management” from the shortcut in Administrative Tools
2. Run powershell.exe as administrator

You will find the powershell.exe under C:\Windows\System32\WindowsPowerShell\v1.0

You can also generate the cluster log using PowerShell.
Syntax:- Get-ClusterLog [-InputObject ] [[-Node] ] [-Cluster ] [-Destination ] [-TimeSpan ] []

Use below command to generate a log file for each node of the local cluster
Get-ClusterLog

Use below command to generate a log file for each node of the local cluster, and copies all logs to the local TEMP folder
Get-ClusterLog – denstination c:\Temp

Use below command to generate a log file for the local cluster in the cluster reports folder on each node of the cluster. The log covers the last 5 minutes
Get-ClusterLog -TimeSpan 5

You can refer below link for Failover Cluster Cmdlets in Windows PowerShell
http://technet.microsoft.com/en-us/library/ee461009.aspx

Windows PowerShell user guide
http://technet.microsoft.com/en-us/library/bb978526.aspx

Regards
Satishbabu Gunukula
http://sqlserver-expert.com/

Index was outside the bounds of the array

I was trying to connect to SQLserver 2012 using SQLserver 2008 Management studio and received below error. I was able to connect and able to query.

I tried the same with SQL2005 Management studio and same error. I didn’t see useful information under Technical details.

Cause: Please note that old versions of SSMS (SQLServer Management Studio) are not forward compatible. Using 2012 SSMS you can connect to SQLServer 2005/2008 databases as it is backward compatible.

It looks like Microsoft will not fix the forward compatiable.

Solution: Upgrade all your client tools to highest version, and then you won't be missing out on any functionality or getting error messages.

Regards,

Satishbabu Gunukula

http://www.sqlserver-expert.com

Manage Database files and file groups in SQLServer

SQLServer has three types of files

Primary – Primary data file contains the startup information of the database and also has information about other files in the database. The file extension is .mdf

Secondary – Secondary data file is used to store user data and these files are optional. The secondary files are used to spread data across multiple disks. The file extension is .ndf

Transaction log- Transaction log file holds the log information that is needed to recover the database. The file extension is .ldf

Every database has one primary file group and it is the best practice to create a user-defined file group for user data.

Create a user-defined file group:  Use below syntax to create user defined file group “USER_DATA”

USE master
GO
ALTER DATABASE TestDB
ADD FILEGROUP USER_DATA;
GO

ALTER DATABASE TestDB
ADD FILE
(
    NAME = USER_DATA1,
    FILENAME = ‘D: \DATA\user_data.ndf',
    SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB
),
TO FILEGROUP USER_DATA;
GO

You can also create a user-defined file group using Microsoft SQLServer Management Studio

1       . Right click on the database and choose Properties

2       . Go the FileGroups page, check on add and provide the file group name.

3       . Select the Default checkbox to make it as default file group

Modify User-defined/seconday file group as default:

USE master
GO
ALTER DATABASE [TestDB] MODIFY FILEGROUP [USER_DATA] DEFAULT

Please note that all the user objects will create in default file group.

Resize the file or Modifying a file:  Use below syntax to increase the max size of the file

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
    (NAME = USER_DATA1, MAXSIZE = 2000MB);
GO

Add a data file to existing file group:  Many of the users will come across this If you primary data file drive is out of space. Create a new drive and add a new ndf file to the file group.

USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE ( NAME = N'USER_DATA2', FILENAME = N'E:\SQLData\user_data2.ndf' , SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB) TO FILEGROUP [USER_DATA]
GO

Moving a file to a new location: You must physically move the file to the new disk/direcotry before running this syntax.

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
( NAME = USERd_ATA2,
    FILENAME = N’E:\SQLData\user_data2.ndf'
); GO

Make sure to stop and start the SQL Server or take the database OFFLINE and then ONLINE To implement the change.

Removing a file from database: Use following syntax to remove a file from database

USE master;
GO
ALTER DATABASE TestDB
REMOVE FILE USER_DATA2;
GO

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com/