Sql Server Perfmon counters for Memory

Here there are few sql server perfmon counters which will tell you about memory bottleneck for your sql server instance.Monitor these counters and compare your value with below given values.

1- Buffer cache hit ratio--

-Indicates how often sql server can get data from the buffer rather than disk.
-Buffer cache hit ratio > 90% for OLAP
-Buffer cache hit ratio > 95% for OLTP

2-Free list stalls/sec--

-The frequeny that requests for database buffer pages are suspended because there's no buffer available.
-Free list stalls/sec < 2
-if value is high that means memory shoud be increase.

3-Free pages--

-The total no of 8k data pages on all free lists
-Free pages > 640

4-Lazy writes/sec--

-The no of times per second that lazy writer moves dirty pages from buffer to disk to free buffer space.
-Lazy writes/sec < 20
-greater value will indicate memory bottleneck.


5-Page Life Expectancy--

-No of seconds a data page stays in the buffer.
-Page Life Expectancy > 300,otherwise memory pressure is at play.

6-Page Lookups/Sec--

-No of requests to find a page in the buffer.
-Page lookups/sec)/(Batch request/sec)<100

7-Page Reads/sec and Page writes/sec--

-no of physical db page reads and writes issued,respectly
-Value should be < 90

HOWTO: Change Microsoft SQL Server Authentication mode from windows to Mixed Mode

There are two method to change the authentication mode from windows to mixed.

1-GUI
2-Registry

1-GUI Method-

Connect to Instance then go to Instance property.Choose security option from left side,here you can change from windows to Mixed mode.after changing restart the sql server services.

2-Registry Method-

For SQL Server 2005 Express Edition,You need to go it manually. The first step is to change the login-mode.

Open registry editor (launch application %WINDIR%\regedit.exe) and go to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in the tree on the left.

On the right, look for an entry named LoginMode. The default value, when installed is 1. Update it to 2. The next step is to restart the service.

Launch your Service Manager (Start -> Run -> Type services.msc) and look for a service named MSSQL Server (SQLEXPRESS). Restart the service.

How to move a table to a different filegroup

We can change a table from one filegroup to any other filegroup through droping and recreting the clustered indexes.below is given ALTER cmnds which will move your whole table from filegroup1 to filegroup2.

Cmnds are-

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO

where TAB1 is table name PK_TAB1 is clustered index key and TEST_DATA_2 is target filegroup.

Point in Time Recovery

sometimes detrimental command will probably be issued against one of your databases and you will need to recover the lost data. There are several actions that you might be able to take to recover the lost data, but what option makes the most sense. One option that SQL Server offers is the ability to do point in time restores of your data in order to restore your database back to the point right before that detrimental command was issued.

Point-in-Time Restore:-

1-This topic is relevant only for databases that are using full or bulk-logged recovery mode. Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.
2-Restore the last full database backup and, if any, the last differential database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
3-Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM WITH STOPAT=time, RECOVERY).
4-The basic syntax is:

RESTORE LOG database_name FROM WITH STOPAT =time, RECOVERY…


Cmnds are:

-- Restore the full database backup.
RESTORE DATABASE AdventureWorks
FROM disk='backup file location'
WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks
FROM disk='backup file location'
WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM';
GO

Not able to open DTS package in sql server 2008 or sql server 2005.

When you get below error during accessing the DTS packages :

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

Then to resolve this issue follow the below processes.


1-Install Sql Server 2000 DTS Designer Components
Download from here. This is the most current version as of the writing of this article.
2-Install Sql Server 2005 backward compatibility components
Download from here. This is the most current version as of the writing of this article.
This is available with the installation media for SQL Server 2008.
3-Verify your path environment variable. The SQL 2000 path should be placed in the string prior to the SQL 2008 variable. As a sample, this is what mine looks like.
%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\
*You can access the environment variables in Windows 7 by: Right Click My Computer -> Properties -> Click Advanced System Settings -> Click Environment Variables -> Scroll to “Path” -> Click “Path” and then click Edit…
4-SSMS – Manually Copy Files
DLL Files to copy
semsfc.dll, sqlgui.dll, sqlsvc.dll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\
Destination
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\
RLL Files to copy
semsfc.rll, sqlgui.rll, sqlsvc.rll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
Destination
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\
5-BIDS Manually Copy Files
DLL Files to copy
semsfc.dll, sqlgui.dll, sqlsvc.dll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\
Destination
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\
RLL Files to copy
semsfc.rll, sqlgui.rll, sqlsvc.rll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
Destination
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\
I had to create the Resources Subdirectory
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\%lang_id%\
The last step was not applicable in my case since the file version already matched
stardds.dll File version: 2000.80.2151.0

How to Kill all sql server process

Sometimes we need to kill all sql server transactions to perform any task.If you have to do this pls follow any one of the given steps.

1-Through management studio,when you will try to detach the db there is a option to kill all process,kill all process from here but after this click on cancel button rather than ok button.If you click on ok db will be detached.

2-Run below cmds:
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER

3-Script to accomplish this, replace 'DB_NAME' with the database to kill all connections to:

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

Error 14151:Replication Agent failed to start.

Error:The SQL Server Replication Agent may not start, and you receive the following error message:
The replication agent is not registered properly. Rerun SQL Server setup. The step failed. [SQLSTATE 42000] (Error 14151). The step failed.
Cause:
The Replication Agent that is running on the server may not restart when a named instance of SQL Server is removed from the server and replication is configured on the server. When you remove a named instance of SQL Server, the registry entries that relate to the Replication Agent are removed from the server. This behavior may also occur when the 8.3 name creation on the NTFS file system partition is disabled on the computer where SQL Server is installed. You disable 8.3 name creation on the NTFS partition by setting the NtfsDisable8dot3NameCreation registry key to 1.
Solution:
To work around this problem, follow these steps:
1-Set the NtfsDisable8dot3NameCreation registry key to 0.
To change the registry key, use these steps:
2-On the Start menu, click Run. Type regedit, and then click OK.Locate the following key in Registry Editor:
HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\CONTROL\FileSystemDouble-click the NtfsDisable8dot3NameCreation key of type REG_DWORD.Set the Value Data of the registry key to 0, and then click OK.
Note: By default, the value for the entry is set to 0. Close Registry Editor.

3-Restart your computer.

Use one of the following methods to register the replication agents:
Method -1

Re-register the Replication Agent executables. To do this, use these steps:
1-Stop the SQL Server Agent service.

2-Copy the following command to a batch file:
snapshot.exe -regserver

distrib.exe -regserver

replmerg.exe -regserver

logread.exe -regserver

qrdrsvc.exe –regserver
3-Run the batch file you created in step 2b from the \Microsoft Sql Server\80\Com folder.

4-Restart the SQL Server Agent service.
Method 2-

Rebuild the registry. To do this, use these steps:

Insert the Microsoft SQL Server 2000 disk in the CD-ROM drive. If the disk does not run automatically, double-click Autorun.exe in the root directory of the disk.Click SQL Server 2000 Components, and then click Install Database Server.In the Welcome screen of the SQL Server Installation Wizard, click Next. In the Computer Name dialog box, click Next.In the Installation Selection dialog box, click Advanced Options.In the Advanced Options dialog box, click Registry Rebuild, and then click Next. You will see a message that informs you that Setup rebuilds the registry based on information you supply in the subsequent screens.

Caution: You must enter the same choices for the setup options as you did for the initial installation. If you do not know or are not sure of this information, do not use this registry rebuild process. Instead, you must remove and re-install SQL Server to restore the registry. To prepare for the registry rebuild, enter the same information and options that you entered during the initial installation of SQL Server in the setup screens as they appear.
Note: Rebuilding the registry includes re-copying external components such as Microsoft Data Access Components (MDAC) and Microsoft Distributed Transaction Coordinator (MS DTC).

Rebuild and Restore Master database

How do you know if your master database is corrupt?

Let's pretend that your company had a power surge and your SQL Server rebooted. Upon reboot, SQL Server would not start. If you check the error log , you'll see that the master database is either corrupt or missing.Rebuild your master database:
Your first step in recovering your master database is to use the Rebuild Wizard (Rebuildm.exe), located in the \Program Files\Microsoft SQL Server\80\Tools\BINN directory. Let’s walk through the Rebuild Wizard to see how it works.Start by double-clicking Rebuildm.exe to bring up Rebuild Master screen.
On this screen, you can specify the collation settings of your database server and the location of your data files during your original install. To make the latter easier and faster, copy the x86 directory from the SQL CD to your hard drive and point to the local copy. Once you have verified all of this information, click Rebuild. You'll then be prompted to confirm the operation,
Click Yes. Once the process is completed, you'll see a message telling you that the rebuild was successful. You now have a brand new master database and are ready to restore your master database.

Restore Master db with a Backup file:First, start SQL Server in single-user mode by opening up a command prompt and issuing the command sqlservr.exe –c -m from the \Program Files\Microsoft SQL Server\MSSQL\BINN\ directory.

After you start SQL Server in single-user mode, you can restore your master database from a backup. You can restore it using either the Query Analyzer or SQL Enterprise Manager.

If you're using Enterprise Manager, right-click on the master database, choose All Tasks Restore Database, and browse to where your device is located, . Click OK twice, and you have successfully restored your master database.
Once you've restored your master database, exit single-user mode and restart SQL Server in normal operation mode.If for some reason your restore operation does not work, you can try an alternative method. Simply rebuild the master database and attach all of your databases that reside in the data directory. You can attach the databases using Enterprise Manager or Query Analyzer. In Enterprise Manager, right-click on Databases and choose Attach Database.

Rebuild and Restore Master database

How do you know if your master database is corrupt?

Let's pretend that your company had a power surge and your SQL Server rebooted. Upon reboot, SQL Server would not start. If you check the error log , you'll see that the master database is either corrupt or missing.Rebuild your master database:
Your first step in recovering your master database is to use the Rebuild Wizard (Rebuildm.exe), located in the \Program Files\Microsoft SQL Server\80\Tools\BINN directory. Let’s walk through the Rebuild Wizard to see how it works.Start by double-clicking Rebuildm.exe to bring up Rebuild Master screen.
On this screen, you can specify the collation settings of your database server and the location of your data files during your original install. To make the latter easier and faster, copy the x86 directory from the SQL CD to your hard drive and point to the local copy. Once you have verified all of this information, click Rebuild. You'll then be prompted to confirm the operation,
Click Yes. Once the process is completed, you'll see a message telling you that the rebuild was successful. You now have a brand new master database and are ready to restore your master database.

Restore Master db with a Backup file:First, start SQL Server in single-user mode by opening up a command prompt and issuing the command sqlservr.exe –c -m from the \Program Files\Microsoft SQL Server\MSSQL\BINN\ directory.

After you start SQL Server in single-user mode, you can restore your master database from a backup. You can restore it using either the Query Analyzer or SQL Enterprise Manager.

If you're using Enterprise Manager, right-click on the master database, choose All Tasks Restore Database, and browse to where your device is located, . Click OK twice, and you have successfully restored your master database.
Once you've restored your master database, exit single-user mode and restart SQL Server in normal operation mode.If for some reason your restore operation does not work, you can try an alternative method. Simply rebuild the master database and attach all of your databases that reside in the data directory. You can attach the databases using Enterprise Manager or Query Analyzer. In Enterprise Manager, right-click on Databases and choose Attach Database.