NetApp Perfstat tool Usage

Perfstat is a NetApp tool that you can monitor and reveal your NetApp Storage devices . You can download it from NetApp web site.

Usage of perfstat is;

perfstat -f IPAddressofNetAPP -t 10 -i 12 > perfstat.out

10 : Get statistics for every 10 minutes;
12 : Number of sample
perfstat.out : Name of the file that log will be saved into.
Read more...

Reindex SQL Server Database

Sometimes you may need to reindex your database while you transport it from SQL 2000 to SQL 2005.

Execute the command below(Write your db name instead of mydatabasename)



DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
-- SQL 2000 command
--DBCC DBREINDEX(@Table,' ',@fillfactor)
-- SQL 2005 command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Read more...

Hide SQL Server Database(s)

Sometimes you may want to hide SQL Server databases from unauthorized users.(Like hosting firm etc.)
To show SQL databases to only authorized users follow the steps below;

Open New Query;

Execute the command below to remove "view all database(s) right" from public role;

USE master
GO
DENY VIEW any DATABASE TO public

Thsn Execute command below to append users to view and own database

USE database_name
GO
EXEC sp_changedbowner username
Read more...

Event ID: 17896

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17896
Description:

The time stamp counter of CPU on scheduler id 2 is not synchronized with other CPUs.







Check the Links Below;
http://blogs.msdn.com/psssql/archive/2006/11/27/sql-server-2005-sp2-will-introduce-new-messages-to-the-error-log-related-to-timing-activities.aspx



http://support.microsoft.com/default.aspx?scid=kb;en-us;931279

CPU number	Statements to enable processor affinity
02 CPUs	exec sp_configure 'affinity mask', 0x00000003 GO reconfigure GO
04 CPUs	exec sp_configure 'affinity mask', 0x0000000F GO reconfigure GO
08 CPUs	exec sp_configure 'affinity mask', 0x000000FF GO reconfigure GO
16 CPUs	exec sp_configure 'affinity mask', 0x0000FFFF GO reconfigure GO
32 CPUs	exec sp_configure 'affinity mask', 0xFFFFFFFF GO reconfigure GO

Read more...

Get Missing indexes on your Database(s)

Execute the command below;


SELECT DB_NAME(database_id) AS [DATABASE],
Equality_Columns, Inequality_Columns,
Included_Columns, STATEMENT
FROM sys.dm_db_missing_index_details
ORDER BY 1

where DATABASE is your DB Name where missing index(es) found.
equality_columns index is required on these columns
inequality_columns is list of columns that are used for inequality comparison and index is required on these.
included_columns is list of columns that are used in query for other than comparison and covering index on these is suggested.
Statement is name of table along with column where index is missing

Read more...

SQL Server 2005 recovery Models

The recovery model of database determines the type of database transactions are logged and the degree of concern that provided to data loss in case of any failure.

Full Recovery Model of SQL Server 2005

The full recovery model does the most extensive logging and allows the database to be recovered to the point of failure. Full recovery model presents the highest protection against data loss. You should always configure all production databases to use full recovery.

Bulk-logged Recovery Model of SQL Server 2005

The bulk-logged recovery model fully logs transactions but mostly bulk operations, such as bulk loads, Select statements. Bulk-logged recovery model allows the database to be recovered to the end of a transaction log backup only when the log backup contains bulk changes. Recovery to the point of failure is not supported.

Simple Recovery Model of SQL Server 2005

Simple recovery model logs most transactions, logging only the information required to ensure database consistency after a system crash or after restoring a database backup. With simple recovery model the database can be recovered only to the most recent backup. This recovery model has the should not be used where data loss in the event of a crash cannot be tolerated.

Read more...

View SQL Server Error log's detail by SQL Commands

Execute the command below to get error logs Archive number.




exec sp_enumerrorlogs






Then execute ;


exec sp_readerrorlog archivenumber






Read more...