Script to identify DeadLock -Longtime_Query

select s.session_id, s.login_time, s.login_name
, s.host_name, s.program_name, s.last_request_end_time
, r.start_time, r.command, r.open_transaction_count
, SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) as statement_text
, coalesce(QUOTENAME(DB_NAME(st.dbid)) + N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
+ QUOTENAME(OBJECT_NAME(st.objectid, st.dbid))

, '') as command_text
from sys.dm_exec_sessions as s
join sys.dm_exec_requests as r
on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) as st

order by s.last_request_end_time;

Script to script out logins and permissions

--select * from master.DBO.TEMP_TABLE_FOR_USERS
DECLARE @DBNAME VARCHAR(50),
@STORAGEDBNAME VARCHAR(50)
SET @DBNAME='ReportingDatabase'
SET @STORAGEDBNAME='master'

SET NOCOUNT ON
DECLARE @CMD VARCHAR(350)
SET @CMD= 'IF  EXISTS (SELECT * FROM '+@STORAGEDBNAME+'.sys.objects WHERE object_id = OBJECT_ID('''+@STORAGEDBNAME+'.[dbo].[TEMP_TABLE_FOR_USERS]'')
AND type in (''U''))
DROP TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
CREATE TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
([COMMAND] TEXT)'

EXEC (@CMD)
CREATE TABLE #TEMP
(   NUMBER INT IDENTITY(1,1) NOT NULL,
USERNAME VARCHAR(100),
ROLENAME VARCHAR(100)
)
CREATE TABLE #TEMP1
(   NUMBER INT IDENTITY(1,1) NOT NULL,
OBJECTNAME VARCHAR(100),
TYPE_DESC VARCHAR(100),
PERMISSION_NAME VARCHAR(100),
STATE_DESC VARCHAR(100),
USERNAME VARCHAR(100)
)
CREATE TABLE #TEMP2
(   NUMBER INT IDENTITY(1,1) NOT NULL,
USERNAME VARCHAR(100)
)

DECLARE @CMD1 VARCHAR(500),
@USER INT,
@USERNAME VARCHAR(50)

SET @CMD1= 'SELECT U.NAME , G.NAME
            FROM '+ @DBNAME+'.DBO.SYSUSERS U,' +@DBNAME+'.DBO.SYSUSERS G,' +@DBNAME+'.DBO.SYSMEMBERS M
            WHERE   G.UID = M.GROUPUID
            AND G.ISSQLROLE = 1 AND U.UID = M.MEMBERUID AND U.NAME<>''dbo'''
INSERT INTO #TEMP
EXEC (@CMD1)

INSERT INTO #TEMP2
SELECT DISTINCT USERNAME FROM #TEMP
DECLARE @CMD2 VARCHAR(5000)
SET @CMD2= 'DECLARE @COUNT INT,@USER INT
SET @COUNT=1 SELECT @USER=COUNT(*) FROM #TEMP2 WHILE @USER>=@COUNT
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] WITH DEFAULT_SCHEMA=[DBO]'' FROM #TEMP2
WHERE NUMBER=@COUNT SET @COUNT=@COUNT+1
END'
EXEC (@CMD2)
-------------------- Schema Starts Here ------------------

CREATE TABLE #TEMP3
(   NUMBER INT IDENTITY(1,1) NOT NULL,
SCHEMANAME VARCHAR(100)
)
INSERT INTO #TEMP3 SELECT NAME FROM SYS.SCHEMAS WHERE [SCHEMA_ID] BETWEEN 5 AND 16383


DECLARE @CMD5 VARCHAR(5000)
SET @CMD5= 'DECLARE @SCHEMA INT,
@COUNT3 INT
SET @COUNT3=1
SELECT @SCHEMA=COUNT(SCHEMANAME) FROM #TEMP3
WHILE @SCHEMA>=@COUNT3
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
SELECT ''IF NOT EXISTS (SELECT * FROM SYS.SCHEMAS WHERE NAME = ''''''+SCHEMANAME+'''''')
EXEC SYS.SP_EXECUTESQL N''''CREATE SCHEMA [''+SCHEMANAME+''] AUTHORIZATION ['' +SCHEMANAME +'']'''''' FROM #TEMP3
WHERE NUMBER=@COUNT3 SET @COUNT3=@COUNT3+1
END'


EXEC (@CMD5)

-------------------- User Role Starts Here ---------------
DECLARE @CMD3 VARCHAR(5000)
SET @CMD3= 'DECLARE @ROLE INT,
@COUNT2 INT
SET @COUNT2=1
SELECT @ROLE=COUNT(ROLENAME) FROM #TEMP
WHILE @ROLE>=@COUNT2
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''EXEC sp_addrolemember ''''''+ROLENAME+ '''''' ,''''''+ USERNAME+'''''''' FROM #TEMP
WHERE NUMBER=@COUNT2 SET @COUNT2=@COUNT2+1
END'

EXEC (@CMD3)

--------------------- Secure Permission Starts Here ---------------
INSERT INTO #TEMP1
SELECT O.NAME COLLATE LATIN1_GENERAL_CI_AS_KS_WS AS OBJECTNAME ,TYPE_DESC,
PERMISSION_NAME,STATE_DESC,U.NAME AS USERNAME
FROM SYS.DATABASE_PERMISSIONS P
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID=MAJOR_ID
INNER JOIN SYSUSERS U ON U.UID=P.GRANTEE_PRINCIPAL_ID

DECLARE @CMD4 VARCHAR(5000)
SET @CMD4= 'DECLARE @SECUR INT,
@COUNT1 INT
SET @COUNT1=1
SELECT @SECUR=COUNT(*) FROM #TEMP1
WHILE @SECUR>=@COUNT1
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''''+STATE_DESC+'' ''+PERMISSION_NAME+'' ON ''+OBJECTNAME+''  TO [''+USERNAME +'']''FROM #TEMP1
WHERE NUMBER=@COUNT1 SET @COUNT1=@COUNT1+1
END'
EXEC (@CMD4)

-------------------- Final Output ---------------
DROP TABLE #TEMP
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
SET NOCOUNT OFF

SQL Agent Script

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colMonitoredProcesses = objWMIService. _
ExecNotificationQuery("select * from __instancedeletionevent " _
& "within 1 where TargetInstance isa 'Win32_Process'")
Do While i = 0
Set objLatestProcess = colMonitoredProcesses.NextEvent
If objLatestProcess.TargetInstance.Name = "sqlservr.exe" Then
Set objEmail = CreateObject("CDO.Message")
SmtpMail.SmtpServer = "indblrvmsg00"
objEmail.From = "Test@aditi.com"
objEmail.To = "pradyothanapd@aditi.com"
objEmail.Subject = "SQl Server is down on" & strcomputer
objEmail.Textbody = "SQL Sever is down"
objEmail.Send
End If
Loop

The transaction log for database DBNAME is full

ERROR :-

Msg 9002, Level 17, State 2, Line 4
The transaction log for database DBNAME is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-1-Running-out-of-transaction-log-space.aspx

SSIS Deployment Issue - In cluster Environment

Resolution OR Workaround
-------------------------------
SSIS is not a part of cluster service, Its an individual component and server name in the xml will refer to default or local instance by .(DOT). This we need to change as SQL Server will be on a cluster with a VIRTUAL NAME.


1. Stop the SSIS Service
2. Open up MsDtsSrvr.ini.xml file which is available in "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\” folder
3. Replace the Server Name node with Clustered Instance Name (replace “.” With cluster instance name)
4. Restart the SSIS Service


This will resolve the issue.Good Luck.


Note :- Will get error if we dint restart the SSIS services.

Setup SSRS in SharePoint Integrated Mode

This post is about configuring SQL Server Reporting Services in SharePoint Integrated mode the quick and clean way with no fuzz in 40 steps.

I'm not discussing the default values, I will only talk about the values which need to be changed or selected in order to get SSRS set up in the least amount of steps.


The scenario:
SharePoint 2010 Enterprise Edition + named instance SQL Server 2008 Express Edition which is deployed during the SharePoint setup. Name of the SQL Server instance: SHAREPOINT.
Separate default SQL Server 2008 R2 instance (Enterprise Edition).
SSRS currently installed in native mode on the SQL Server 2008 R2 default instance.
AdventureWorksDW2008R2 hosted on the SQL Server 2008 R2 default instance.
The procedure contains 5 parts:

A. SSRS Configuration {SQL Server}
B. Web application {SharePoint}
C. Site collection {SharePoint}
D. SSRS integration {SharePoint}
E. Content types {SharePoint}
Let's start!

A. SSRS Configuration {SQL Server}
Log on to your SharePoint 2010 box.
Fire up the SQL Server Reporting Services Configuration Manager.
Connect to the Report Server instance. In my case this is the default SQL Server 2008 R2 instance (MSSQLSERVER)
The first thing you will notice is the Report Server Status. Check the Report Server Mode. It should say Native (we start from native mode in this scenario).
Click Database in the left pane.Click Change Database. Select Create a new report server database.
Enter the proper credentials.
Enter a database name and select SharePoint Integrated mode.
Enter the credentials and do not forget the \ (.\ will do as well).
Choose Report Manager URL in the left pane and click the apply button. This will configure the Report Manager virtual directory.
Backup the encryption keys.

B. Web application {SharePoint}
Open the SharePoint Central Administration website.
Choose Application Management > Manage web applications.
Click the New button in the ribbon. This will bring up the Create new web application popup.
Keep the default values to keep things simple except for:
Choose a name for the new IIS Website. I will choose SSRSDemo in this example.
Choose a name for the application pool. I will again choose SSRSDemo.
Make sure you connect to the right database instance. In my example I will connect to the named SQL Server 2008 Express Edition instance named SHAREPOINT.
Choose a database name or keep the default value with the GUID suffix. I will call the database WSS_Content_SSRSDemo.
Hold your horses before you hit the OK button when this information pops up:
The Microsoft SharePoint Foundation Web application has been created.

If this is the first time that you have used this application pool with a SharePoint Web application, you must wait until the Internet Information Services (IIS) Web site has been created on all servers. By default, no new SharePoint site collections are created with the Web application. If you have just created a Forms Based Authentication (FBA) Web application, then before creating a new site collection, you will need to perform some additional configuration steps.

Learn about how to configure a Web application for FBA.

Once you are finished, to create a new site collection, go to the Create Site Collection page.


5. Click Create Site Collection page to move on to C. Site collection {SharePoint}


C. Site collection {SharePoint}
Enter a title for the site collection. SSRSDemo for example.
Choose a template. I will choose Business Intelligence Center because I want to store my SSRS reports in a BI related environment.
Enter a username for Primary Collection Administrator. Make sure you enter the full domain name. e.g.: \ (.\username won't work here).
D. SSRS integration {SharePoint}

Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Reporting Services Integration.
Enter the Report Server Web Service URL which you can find the SQL Server Reporting Services Configuration Manager.
Choose the Authentication Mode. I will choose Windows Authentication. Entering .\ will do.
Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Add a Report Server to the Integration.
The server name should already be provided and enter the name of the SQL Server instance which hosts the report server database.
Enter the credentials.

E. Upload a report {SharePoint}
Open your new top level web site we created in C. Site collection {SharePoint}
In case you forgot the URL of the web site, go to SharePoint Central Administration > Web Application and look for the URL.
Go to All site Content > Documents.
In the ribbon, go to Library Tools > Library.
Click Library Settings.
Click Advanced Settings.
In Content Types, check Yes for Allow Management of content types and click OK.
In the columns section, click Add from existing site columns.
In the Select site columns from drop-down box, select Report Server Content Types.
Select all available site content types and click the Add button followed by OK.
Go back to All site content > Documents.
Click the Add Document link.
Browse for a Report provided by the AdventureWorksDW200R2 samples from CodePlex.
Enter a title for the report.

More Information on :-
-------------------------------
http://sqlug.be/blogs/steves_sql_blog/archive/2011/04/05/setup-ssrs-in-sharepoint-integrated-mode.aspx
------------------------------

NO Start Button,, Need Manage Server ..Here you go

Manage Windows Server 2008 R2 Core with SCONFIG

The Server Core installation option of Windows Server 2008 and R2 provides the following benefits:

Reduced maintenance - Because the Server Core installation option installs only what is required to have a manageable server for the AD DS, AD LDS, AD CS, DHCP Server, DNS Server, File Services, Print Services, Web Server and Hyper-V server roles, less maintenance is required than on a full installation of Windows Server 2008.
Reduced attack surface - Because Server Core installations are minimal, there are fewer applications running on the server, which decreases the attack surface.
Reduced management - Because fewer applications and services are installed on a server running the Server Core installation, there is less to manage.
Less disk space required - A Server Core installation requires only about 1.5 gigabyte (GB) of disk space to install and approximately 2 GB for operations after the installation.
Lower risk of bugs - Reducing the amount of code can help reduce the amount of bugs.


To run SCONFIG simply enter sconfig.cmd in the command prompt window, and press Enter.

UPDATE STATS - FOR ALL DB's AND ALL OBJECTS

EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''UPDATE STATISTICS ? WITH FULLSCAN'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Stats Updated'''''' END',
@replaceChar = '#'

Interview Questions -DAC

1)What is a DAC. How do you connect to server through DAC?

This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.

This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. The DAC only allows changing the user context to another admin user.

SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.

To connect to a server using the DAC

In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.

In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.

Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.

The connection is made.

If the DAC is already in use, the connection will fail with an error indicating it cannot connect.

Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

http://msdn.microsoft.com/en-us/library/aa337324%28SQL.90%29.aspx

To retrive Data from Excel

1. Step

http://www.microsoft.com/download/en/confirmation.aspx?id=23734

Install the Driver AccessDatabaseEngine.exe

2. Step

select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\details.xlsx;',
'SELECT * FROM [DServer$]')

More Help on http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/

TEMPDB

The following operations cannot be performed on the tempdb database:

Adding filegroups.

Backing up or restoring the database.

Changing collation. The default collation is the server collation.

Changing the database owner. tempdb is owned by dbo.

Creating a database snapshot.

Dropping the database.

Dropping the guest user from the database.

Enabling change data capture.

Participating in database mirroring.

Removing the primary filegroup, primary data file, or log file.

Renaming the database or primary filegroup.

Running DBCC CHECKALLOC.

Running DBCC CHECKCATALOG.

Setting the database to OFFLINE.

Setting the database or primary filegroup to READ_ONLY.

Enabling TDE

When you enable TDE, the data in a database is encrypted, however two other things occur. All subsequent transactions in the transaction log for that database are encrypted, and tempdb is encrypted.

If you have a 4 CPU socket server that has two NUMA nodes with CPUs (0,1) and (2,3), can you create a soft-NUMA node with CPUs 1 and 2?

No
Explanation:
The soft NUMA nodes that SQL Server can create cannot cross hardware NUMA boundaries. So you cannot create a soft NUMA set with CPUs 1 and 2.

Maximum Instances - SQL Server

50 instances on a stand-alone server for all SQL Server editions.

SQL Server supports 25 instances on a failover cluster.

SP_Update_stats

Many of you might have lot of tables or have large databases where you only want to update statistics using ‘UPDATE STATISTICS’ or ‘sp_updatestats’, but only for those tables that have large row modifications. Here is a script that you can use to get the output of each index that has significant row modifications. You can pipe this to a temp table and choose to update statistics on only these tables. You can do this by looking at the “ModifiedPercent” column.

select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type='BASE TABLE'
where 0 < i.indid and i.indid < 255
and table_schema <> 'sys'
and i.rowmodctr <> 0
and i.status not in (8388704,8388672)
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

Blocking OR Lock - Dead, Live

DEADLOCK

When two processes are waiting for each other to release locks the other needs. Each process would wait indefinitely for the other to release the lock. SQL Server detects deadlocks and choose one process as a deadlock victim, rolls back the transaction and return error 1205.

How would I face deadlock:

First I will try to find cause of deadlock. I need to know which processes led to the deadlock scenario and what resource and types of locks are involved. In SQL Server 2008 and higher, I will look output of system_health Extended Event session. It's started by default in SQL Server 2008, so if administrator didn't turn it off, we have information about processes, resources and types of locks involved in deadlock scenario. It's excellent way for retroactively finding deadlock information. And in this situation we can immediately start to work on resolving the deadlock problems.

If system_health Extended Event session is stopped I will start it to capture all detected deadlocks in the system. But I need to wait to deadlock happened again to know exactly what is going on. To capture deadlock information in older version I will:

If it's SQL Server 2005 I will turn on trace flag 1222 or will start server side trace to capture Deadlock graph. For SQL Server 2000 trace flag is 1204.

When I find information about processes, statements, resourdes and types of locks that are involved in deadlock I will run queries through DTA (Database tuning advisor). DTA will tell me if some indexes are missing. Usually deadlock can be resolved by adding missing index (adding covered nonclustered index resolved almost all my deadlocks).

If one of the involving statements is SELECT operation, I can add NOLOCK hint or set isolation level to READ UNCOMMITTED (if it’s OK that SELECT operation read uncommited data). If that’s not option, in SQL Server 2005 or higher I will set READ COMMITTED SNAPSHOT or SNAPSHOT isolation level (it will remove shared lock caused by reads). It’s always good that the query is using the minimum necessary transaction isolation level.

Also it’s good to check that transactions involved in deadlock are as short as possible and if they change the same tables, change them in the same order.

BLOCKING

Blocking occurs when one process holds a lock on a specific resource and a second process attempts to acquire a conflicting lock type on the same resource. This is normal behavior and may happen many times with no noticeable effect on system performance.

If blocking increase to the point where it impact on system performance I will first find what cause blocking (need information about queries that are involved in blocking and types of locks). I can use SQL Trace Blocked Process Report or can use DMVs (sys.dmoswaitingtasks, sys.dmtranlocks, sys.dmexecrequest, sysdmexecsql_text) to get that informations.

Solutions of blocking problems are the same as for deadlocking problems:

Identify and add missing indexes, ensure that all transactions are as short as possible, use hints to modify locking behavior, use the lowest isolation level acceptable, rewrite problematic code, ensure that indexes aren’t fragmented and that statistics are up to date.

Cluster Logs - Windows Cluster 2008

=============================================================
1. Go to CMD
2. Type as below,
cluster /cluster:nameofcluster log / gen /copy:"d:\clusterlog"
3. Read the logs from the location d:\clusterlog

=============================================================

Other Windows Servers, we can find the logs in

%systemroot%\Logs\Cluster

Poor Performance Querys

/*-------------------------------------------------------------------------------------------------------------------------------Description : This stored procedure will send out alert email if there is a blocking which lasted more than specified duration) -- Copyright 2011 - DBATAG -- Author : DBATAG -- Created on : 09/01/2011 -- Modified on : 09/01/2011 -- Version : 1.0 -- Dependencies : -- Table Procedure Permissions -- No Dependencies No Dependencies View Server State Permissions Required ----------------------------------------------------------------------------------------------------------------------------*/ -- List expensive queries DECLARE @MinExecutions int; SET @MinExecutions = 5 SELECT EQS.total_worker_time AS TotalWorkerTime ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO ,EQS.execution_count As ExeCnt ,EQS.last_execution_time AS LastUsage ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count AS AvgLogicalIO ,DB.name AS DatabaseName ,SUBSTRING(EST.text ,1 + EQS.statement_start_offset / 2 ,(CASE WHEN EQS.statement_end_offset = -1 THEN LEN(convert(nvarchar(max), EST.text)) * 2 ELSE EQS.statement_end_offset END - EQS.statement_start_offset) / 2 ) AS SqlStatement -- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!! --,EQP.[query_plan] AS [QueryPlan] FROM sys.dm_exec_query_stats AS EQS CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP LEFT JOIN sys.databases AS DB ON EST.dbid = DB.database_id WHERE EQS.execution_count > @MinExecutions AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE()) ORDER BY AvgLogicalIo DESC ,AvgCPUTimeMiS DESC ================================================================================= Note : The above mentioned query will list down queries which at least have been executed once in the past month or the last SQL Server restart, which ever is earlier View Server State permissions are required to execute this query. The result sets is based on total average CPU and IO used per execution. This Script might take 1-5 minutes in execution , depends on data in DMV.

Script to Check SQL COnnections

/*-------------------------------------------------------------------------------------------------------------------------------Description : This stored procedure will send out alert email if there is a blocking which lasted more than specified duration) -- Copyright 2011 - DBATAG Author : DBATAG Created on : 09/01/2011 Modified on : 09/01/2011 Version : 1.0 Dependencies : Table Procedure Permissions No Dependencies No Dependencies View Server State Permissions Required ----------------------------------------------------------------------------------------------------------------------------*/ -- Connectivity informations ;WITH con AS (SELECT SES.host_name AS HostName ,CON.client_net_address AS ClientAddress ,SES.login_name AS LoginName ,SES.program_name AS ProgramName ,EP.name AS ConnectionTyp ,CON.net_transport AS NetTransport ,CON.protocol_type AS ProtocolType ,CONVERT(VARBINARY(9), CON.protocol_version) AS TDSVersionHex ,SES.client_interface_name AS ClientInterface ,CON.encrypt_option AS IsEncryted ,CON.auth_scheme AS Auth FROM sys.dm_exec_connections AS CON LEFT JOIN sys.endpoints AS EP ON CON.endpoint_id = EP.endpoint_id INNER JOIN sys.dm_exec_sessions as SES ON CON.session_id = SES.session_id) -- Detailed list SELECT * FROM con ORDER by con.TDSVersionHex,con.HostName ,con.LoginName ,con.ProgramName; /* -- Count of different connectivity parameters SELECT COUNT(*) AS [Connections #] ,COUNT(DISTINCT con.HostName) AS [Hosts #] ,COUNT(DISTINCT con.LoginName) AS [Logins #] ,COUNT(DISTINCT con.ProgramName) AS [Programs #] ,COUNT(DISTINCT con.NetTransport) AS [NetTransport #] ,COUNT(DISTINCT con.TDSVersionHex) AS [TdsVersions #] ,COUNT(DISTINCT con.ClientInterface) AS [ClientInterfaces #] FROM con */

Script to check DB Size on FileSystem

SET NOCOUNT ON DECLARE @counter SMALLINT DECLARE @counter1 SMALLINT DECLARE @dbname VARCHAR(100) DECLARE @size INT DECLARE @size1 DECIMAL(15,2) SET @size1=0.0 SELECT @counter=MAX(dbid) FROM master..sysdatabases IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo') DROP TABLE sizeinfo CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000)) WHILE @counter > 0 BEGIN SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter TRUNCATE TABLE sizeinfo EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES') SELECT @counter1=MAX(fileid) FROM sizeinfo WHILE @counter1>0 BEGIN SELECT @size=filesize FROM sizeinfo WHERE fileid=@counter1 SET @size1=@size1+@size SET @counter1=@counter1-1 END SET @counter=@counter-1 SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)] SET @size1=0.0 END SET NOCOUNT OFF

Maximum number of supported nodes in a cluster

http://support.microsoft.com/kb/288778

OR

Differene between affinity mask and max degree of parallelism

Affinity mask is instance level configuration
Degree of Parallelism is Query level configuration
----------------------------------------------------
If you have Multiple physical processors or logical Cores in your machine .It is very easy to understand the benefit of Afinitymask.For example u have 4 cores and 2 instances in your machine then ,u can share processors for each instance like 2 cores per instance.(for better resource balance).


Maxdop is for query execution purpose. By default it will be on for all processors.(All processors will work on one query execution).You can change this behavior for particular query like alter index .for ex if u give maxdop =2 while executing query ,2 processors will dedicated for this query execution where as other processors can handle server work load.

LOG TRUNCATION

Log truncation occurs at these points:
======================================
>> At the completion of any BACKUP LOG statement.

>> Every time a checkpoint is processed, provided the database is using the simple recovery model. This includes both explicit checkpoints resulting from a CHECKPOINT statement and implicit checkpoints generated by the system.

>> The exception is that the log is not truncated if the checkpoint occurs when a BACKUP statement is still active. For more information about the interval between automatic checkpoints, see Checkpoints and the Active Portion of the Log..

>> Transaction logs are divided internally into sections called virtual log files. Virtual log files are the unit of truncation. When a transaction log is truncated, all log records before the start of the virtual log file containing the MinLSN are deleted.

CHECKPOINT

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.

Events That Cause Checkpoints
=============================
>> Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

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

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

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

>> Also, stopping a server issues a checkpoint in each database on the server. The following methods of stopping SQL Server perform checkpoints for each database:

Using SQL Server Configuration Manager.

Using SQL Server Management Studio.

Using the SHUTDOWN statement.

Using the net stop mssqlserver command in a command-prompt window.

Using Services in Control Panel, selecting mssqlserver, and clicking Stop.

Bringing an instance offline in a cluster..

> A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.

> A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.

> An ALTER DATABASE statement that adds or deletes a file in the database is executed.

Note

The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database. This may cause the subsequent restart to take a longer time than usual to recover the databases on the server.

Scripts

1> To find recent Backups for all DATABASES
============================================
SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type

2) Rebuild System Databases in SQL Server 2008
================================================

setup.exe /QUIET/ACTION=REBUILDDATABASE /INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS= accounts [/SAPWD=password] [/SQLCOLLATION=collation_name]

1. Find setup.exe either from your original media or the "local" setup.exe as found in the directory where you have installed SQL Server in the 100\Setup BootStrap\Release directory. So on my machine, I changed directory to C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release.

2. Run setup.exe with the following syntax from a Windows command prompt:

If you have SQL configured for Windows Authentication Mode use this syntax:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= /SQLSYSADMINACCOUNTS=

where

is either the name of your named instance or MSSQLSERVER for the default instance

are Windows groups or individual accounts to provision as sysadmin

If you have SQL configured for Mixed Authentication Mode use the same syntax except you must also provide the /SAPWD parameter to specify the SA password. If you don't, you will get an error.

If you want to rebuild the system databases with a different collation than what you used to install SQL Server, you would need to supply the /SQLCOLLATION parameter. If you don't supply this parameter, then SQL Server will rebuild the system databases with the collation you selected when you installed SQL Server.

3. When setup has completed rebuilding the system databases, it will return to the command prompt with no messages (It always first prints out the version). If you have any syntax problems or issues with parameters you will see these errors in the command window. If you don't see any errors, then you will need to examine the "Summary" log file to verify it was completely successful.

4. If you immediately go to the directory where logs are stored for setup (100\setup bootstrap\logs), you can open up a file called Summary.txt. This file represents the most recent summary of any execution of setup. If you run setup for any other reason after rebuilding the databases before you look at the summary.txt file you will have to look for a folder inside the logs directory that matches the datetime when you run setup to rebuild the system databases. This may not be something that is simple to do if you have run setup several times so a tip here is to use findstr.exe from the command prompt like the following:

findstr /s RebuildDatabase summary*.*

3) To find database restore history from MSDB
============================================

SELECT TOP 10 *

FROM restorehistory WITH (nolock)WHERE (destination_database_name = ‘Database Name’)ORDER BY restore_date DESC

All Databases

SELECT TOP 10 * FROM restorehistory WITH (nolock)ORDER BY restore_date DESC

4) How to Move Resource Database?
=================================

Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf

Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.

You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.

5) Query when log shipping breaks on secondary server due to an out-of-sequence log
===================================================================================

SELECT TOP 20 b.physical_device_name, a.backup_start_date, a.first_lsn, a.user_name FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.type = 'L'
ORDER BY a.backup_finish_date DESC









1

SELECT SERVERPROPERTY(‘RESOURCEVERSION’);





2

GO





3

SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);





4

GO


To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.

You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608
Named Instance
NET START MSSQL$instancename /f /T3608

Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.










1

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')





2

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')

Differences Between Logshipping and Mirroring

Log Shipping:
============

Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required

Database Mirroring:
===================

Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes <3 seconds but not more than 10 sec
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0

Copy-Only Backups

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. For this purpose, copy-only backups were introduced SQL Server 2005. The types of copy-only backups are as follows:

Copy-only full backups (all recovery models)

A copy-only full backup cannot serve as a differential base or differential backup and does not affect the differential base.


Copy-only log backups (full recovery model and bulk-logged recovery model only)

A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create another routine, current log backup (using WITH NORECOVERY), and then use that backup together with all other previous log backups that are required for the restore sequence. However, a copy-only log backup can be created for performing an online restore.

To create a copy-only backup (Transact-SQL)
-------------------------------------------

Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups.


How to: Back Up a Database (SQL Server Management Studio)


How to: Back Up a Transaction Log (SQL Server Management Studio)


The essential Transact-SQL syntax for a copy-only full backup is:

BACKUP DATABASE database_name TO … WITH COPY_ONLY …



COPY_ONLY has no effect when it is specified with the DIFFERENTIAL option.



The essential Transact-SQL syntax for a copy-only log backup is:

BACKUP LOG database_name TO … WITH COPY_ONLY …

Tail-Log Backups

>> If the database is ONLINE and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

BACKUP LOG database_name TO WITH NORECOVERY

>>If the database is offline and does not start.

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

BACKUP LOG database_name TO WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

SQL Server 2008 R2 -Uninstall issues

Go to this registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

Assume need to unintall the "Microsoft SQL Server 2008 Database Engine Shared",

* Search for SQL Server 2008 keys in the uninstall registry.
* Keep searching until you find the Database engine shared install location
Note the “Install source”, copy the path and access that path via Start :: Run
* Right click on the msi and choose uninstall.

Truncate LOG- Replication

Truncating the log of a previously replicated database

(The following is specific to SQL Server 2000 and might not apply to more recent versions.)

I occasionally restore production databases to a test system. Normally I just flip the recovery model from full and simple and I’m good to go. Unfortunately, if the database was being replicated it’s not so easy.

Even if you restore the database without “KEEP REPLICATION”, which would imply all the replication bits would be cleaned up for you, the transaction log will still have a replication marker that prevents it from being truncated. This means the log file, even in “simple” mode, will grow unbounded (not good!).

I’m always reminded of this when I try to clean up an ever-growing log with this command:

BACKUP LOG yourdb WITH TRUNCATE_ONLY

and I get this error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed

Not one to ignore the advice of error messages, I then try running the following commands:

-- see what's going on
DBCC OPENTRAN

-- not too much? just clear the replication marker
EXEC sp_repldone @xactid = NULL,
@xact_seqno = NULL,
@numtrans = 0,
@time = 0,
@reset = 1

Unfortunately, this fails with the following error:

The database is not published.

OK, so part of SQL Server knows it’s not being replicated, I guess that’s good. A lot of sites suggest physically removing the log file by detaching the database, renaming or deleting the log file, and reattaching the database. There’s a much simpler, gentler way:

-- publish database (this doesn't actually create
-- a snapshot--it only takes a cople seconds)
sp_replicationdboption 'yourdb','publish','true'

-- clear that replicaton marker (yourdb should be selected)
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

-- unpublish database
sp_replicationdboption 'yourdb','publish','false'

Yes, you simply enable replication long enough to clear the marker. This only takes a few seconds as it doesn’t actually generate a new snapshot or anything expensive like that. Now you’re free to truncate the log!

OR

http://blog.wassupy.com/2011/03/truncating-log-of-previously-replicated.html

Truncating the log -Replication

Truncating the log of a previously replicated database

(The following is specific to SQL Server 2000 and might not apply to more recent versions.)

I occasionally restore production databases to a test system. Normally I just flip the recovery model from full and simple and I’m good to go. Unfortunately, if the database was being replicated it’s not so easy.

Even if you restore the database without “KEEP REPLICATION”, which would imply all the replication bits would be cleaned up for you, the transaction log will still have a replication marker that prevents it from being truncated. This means the log file, even in “simple” mode, will grow unbounded (not good!).

I’m always reminded of this when I try to clean up an ever-growing log with this command:

BACKUP LOG yourdb WITH TRUNCATE_ONLY

and I get this error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed

Not one to ignore the advice of error messages, I then try running the following commands:

-- see what's going on
DBCC OPENTRAN

-- not too much? just clear the replication marker
EXEC sp_repldone @xactid = NULL,
@xact_seqno = NULL,
@numtrans = 0,
@time = 0,
@reset = 1

Unfortunately, this fails with the following error:

The database is not published.

OK, so part of SQL Server knows it’s not being replicated, I guess that’s good. A lot of sites suggest physically removing the log file by detaching the database, renaming or deleting the log file, and reattaching the database. There’s a much simpler, gentler way:

-- publish database (this doesn't actually create
-- a snapshot--it only takes a cople seconds)
sp_replicationdboption 'yourdb','publish','true'

-- clear that replicaton marker (yourdb should be selected)
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

-- unpublish database
sp_replicationdboption 'yourdb','publish','false'

Yes, you simply enable replication long enough to clear the marker. This only takes a few seconds as it doesn’t actually generate a new snapshot or anything expensive like that. Now you’re free to truncate the log!

OR

Instant File Initialization Speeds SQL Server

http://www.bradmcgehee.com/2010/07/instant-file-initialization-speeds-sql-server/

OR

Sometimes, its just the smallest of details that can make all the difference. For example, on my test system (see the end of this posting for a description), I created a new 50GB database. The database creation process took about 5 minutes and 50 seconds to complete.



Next, I populated the database with over 61 million rows of data, which virtually occupied all of the available space within the newly created database. After that, I backed up the database using SSMS, and then I deleted the original database.

At this point, I restored the database from the backup using SSMS. Below, you see the typical Restore Database screen.



At the bottom, right-hand side of the screen in the Progress box, notice the “Executing (0%)” indicator. Between the time I clicked the OK button to begin the restore, and when the “Executing (0%)” counter began to move, it took about 5 minutes and 50 seconds. At that point, the counter began to increment and the database was restored.

Now I make one very small change to my SQL Server instance (I’ll describe it in just a moment), and then I repeat the above steps (after deleting the database I just restored). First, I created a new 50GB database. This time, instead of taking 5 minutes and 50 seconds to create the database, it takes just under 2 seconds, a savings of about 5 minutes and 48 seconds. Next, I populated the database with the same amount of data as before, backed it up, and then deleted the original file. When I restored the database this time around, instead of having to wait 5 minutes and 50 seconds before the backup began to restore, I only had to wait just under 2 seconds. In both of these cases, I saved a significant amount of time.

So what was the very small change that I made, and why did it radically reduce the amount of time for database creation and database restoration to occur? I turned instant file initialization on.

What is Instant File Initialization?

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.

How Do You Turn Instant File Initialization On?

Unlike most configuration features in SQL Server, there is no on/off switch for instant file initialization. Instead, you have to assign a specific user right to the SQL Server Service (mssqlserver) account. Here’s what you need to do to turn on instant file initialization.

First of all, to use instant file initialization with SQL Server in a production environment, you must be using some combination of:
•Windows Server 2003 or
•Windows Server 2008 or
•Windows Server 2008 R2

and using:
•SQL Server 2005 (any edition) or
•SQL Server 2008 (any edition) or
•SQL Server 2008 R2 (any edition)

Second, you must assign the SQL Server Service (mssqlserver) a special user right called “Perform volume maintenance tasks”. To do this, start the Local Security Policy tool (you must be a local administrator to perform this task), then drill down to Security Settings | Local Policies | User Rights Assignment | Perform volume maintenance tasks, as you see in the screenshot below.



Once you have located “Perform volume maintenance tasks”, right-click on it and select “Properties”, and the “Perform volume maintenance tasks Properties” screen appears. Click on “Add User or Group” and then proceed through the remaining screens until you select the account that is being used as the service account for SQL Server. In the screen shot below, notice that I have added the BRADMCGEHEE\sqlserverservice account to this user rights assignment. This is the user account I use on my test server to run my SQL Server instance.



Once the SQL Server service account has been assigned this user right, you will have to restart the SQL Server service (of course, only when it is not being used), and from this point forward, instant file initialization is turned on for all MDF files in your SQL Server instance.


Note: If your SQL Server service account is a member of the local administrators group, then the account already has the “Perform volume maintenance tasks” user right and you don’t need to assign it again.

Why Isn’t Instant File Initialization Turned On by Default?

When a SQL Server instance is first installed, one of the things you must enter is a SQL Server service account. If you follow the best practice and select a domain user account to be used as the SQL Server service account, the setup process automatically assigns the domain user account with only just enough rights and permissions to run SQL Server. The “Perform volume maintenance tasks” user right is not automatically assigned during installation because it is not required to run SQL Server, and because allowing the service account to have this additional user right introduces a very small security risk.

Oh no, a security risk! Well, not really much of a security risk. Here’s the possible security risk scenario. The disk that is being used to create the new database on has been used for storing data that has been previously deleted. As you may know, when data is deleted from disk by the operating system, it really is not physically deleted; the space holding the data is just marked as being available. At some point, the older data will be overwritten with new data. This occurs all the time on millions of computers throughout the world every day. And as such, any data that has been marked for deletion, but not yet overwritten, is potentially available for access if you have the right tools and know what you are doing. In fact, undelete software uses this to recover data that has been accidently deleted.

When instant file initialization is not turned on, and when SQL Server allocates space for an MDF file, each of the pages allocated for the database is zeroed out, which removes the older data, in theory, preventing it from being accessed. I say “in theory” because there are computer forensics techniques that can even recover data that has been overwritten, but that discussion is really not applicable here.

So if instant file initialization is turned on, there is a very slight risk that someone could go to the pages allocated for the new database and read any older data that still may exist there. This is essentially a non-issue in virtually every organization, other than those that require very high security. But because of this potential security issue, instant file initialization is not turned on by default.


If instant file initialization is turned on, and pages are not zeroed out when the database is initially created, SQL Server will automatically overwrite any data that might have been on those pages when SQL Server needs that space.

When Is Instant File Initialization Used?

If instant file initialization is turned on, it is used in all of these cases:
•When a database is first created
•When a an existing database’s size is manually increased
•When tempdb is recreated each time SQL Server is restarted
•When autogrowth kicks in
•When backups are restored (as the space has to be pre-allocated before a restore can occur)

Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files are circular in nature and must be zeroed out, as random data in transaction log pages can be problematic. In my earlier test, when I created a new 50 GB database, the MDF file was 50 GB and the log file was only 1 MB. If I had created a large log file (which is not uncommon), it would have taken awhile for the log to be created, although the MDF file would have been instantly created. This is also true when you manually increase the size of a log file, or when log file autogrowth occurs. In other words, don’t expect to have all of your databases (MDF and LDF files) created in less than 2 seconds like in my test. While the MDF will be created virtually instantly, the log file may take awhile to be created.


When I was working with SQL Server 2000 a few years back, which does not support instant file initialization, one of the things that annoyed me the most when restoring large databases was waiting for the database space to be allocated before the restore actually began. During emergency database restores, this wasted a lot of precious time, preventing me from getting the database back into production as fast as I would have preferred. If you aren’t using instant file initialization today, you are facing this same problem. That’s why I recommend all SQL Server 2005/2008 instances have instant file initialization turned on. The time saved when restoring databases is the best reason to use instant file initialization.

Check to See if Your SQL Server Instances Have Instant File Initialization Turned On

Hopefully, by now, you see the benefits of using instant file initialization. Assuming that you don’t already know if instant file initialization is turned on or off on the SQL Servers your manage, I challenge you to check and see, and if you find it turned off, turn it on and reap its many benefits.




Test Hardware
•Dell T610 Tower, with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one dual-port HBA (to connect to the DAS); and dual network connections. Hyper-threading turned off.
•One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives. MDF files located on RAID 10 array with 10 spindles, LDF files on RAID 10 array with 4 spindles, backup drive on a single spindle.

Copying a Database from SQL Server 2005 to SQL Server 2008

Right click the Management folder to open a contextual menu as shown.



Click on Copy Database... menu item.

This opens up the Copy Database Wizard's Welcome screen. Read the info on this screen which says that you can migrate database on your SQL 2000 and SQL 2005 servers with this tool.



Click on the Next button.

This brings up the screen where you need to choose a source of your database. This source can either be SQL 2000 server or SQL 2005 server. However (oops!) the default that comes up is SQL 2008 server as shown. Click on the Browse... button which brings up the Browse for Servers window where you expand the Database Engine and highlight the SQL Express server (Junior version of SQL 2005 Server) as shown. Note that the figure is a collage of two windows and couple of clicks.



Click on the Next button. This brings up the window where you choose a destination server showing SQLExpress as the default server (oops again, they seem to have it backwards). Use the ellipsis button to add the Hodentek2HTEK2008 server to the choose the destination server window as shown. Again as this is set up with windows authentication the default is accepted.



Click on the Next button. This takes you to the Select Transfer Method window of the Copy Database Wizard where you can use either the attach / detach method, or the method that uses the SQL DMO API by making the right choice. The second method which does not stop the server is chosen here although it may be a little slower than the other method.



Click on the Next button. This opens up the Select Databases section of the copy database wizard. Here you can pick and choose what databases will be used in this migration. Here the database pubsx on the SQL Express is chosen to be copied. Note that you can also move the database. It is good practice to refresh before you move away from this screen.



Click on the Next button. This brings up the Configure Destination Database (1 of 1) window. If a database with the same name exists on the destination, you can either stop the transfer, or you can drop the existing one before transfer by choosing the appropriate radio button at the bottom of the screen as shown. The folder locations where the transferred database files will be saved to are shown here . If you want you can choose another name for this database. Here the default is accepted.



Click on the Next button. In the screen that shows up, you can pick and choose database objects, an option that is not available in manual attach / detach procedure. Similarly you can choose what logins to migrate by clicking on the ellipsis button in this window (the right hand area). You can use the >> and << buttons to add, or remove objects that needs transferring. Here the default is accepted.



Click on the Next button. This is where an "Integration Services Package" is created whose properties will be configured here. The execution logging options can be chosen here. This can be Windows events log or, a text file that can be chosen as shown.



Click on the Next button. This brings up the window for the Schedule the Package step of the wizard which needs to be configured. It can be, Run immediately or, it can be scheduled using a screen that would pop-up if that option is chosen. Here the "Run immediately" option is chosen. This is the screen where you would use the proxy account for the SQL Server Agent you created earlier. Click on the drop-down handle and choose the proxy you created earlier.



After choosing the proxy indicated, click on the Next button. This takes you to the summary of actions taken so far in the Complete the Wizard screen as shown.



Click on the Finish button in the Complete the Wizard window. This brings up the Performing Operations section of the wizard and shows the processing of the various steps and finally displays the success of the operation. In case there is an error it will stop processing and display a hyperlink, which when clicked will show the error in more detail.



Click the Close button to close the window. You may now refresh the databases node in the SQL Server Management Studio server and verify that the database has been migrated. The two figures show the before and after migration contents of the databases folder in the SQL Server Management Studio.

Before Transfer



After Transfer



In the SQL Server Agent folder you can see the job created for this migration as shown. There are two other jobs created earlier.



If you bring up the properties of this page by right clicking the job you can also see the job history.





Summary

Keyword or Search for Data in a DB

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END


--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Query to find Columns in a Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%contact%'
ORDER BY schema_name, table_name;

Reporting Services Configuration Manager: "Invalid namespace"

http://support.microsoft.com/kb/960374/EN-US

Above will work for 2005 as well.

Method 2: Add a WMI namespace
Add a WMI namespace that corresponds to the namespace that the SQL Server 2008 Reporting Services Configuration Manager tool is trying to use. To do this, follow these steps:

Note This procedure refers to a sample instance that is named "SQL2008."

Locate the Reportingservices.mof file for the SQL Reporting Services 2008 instance that is named "SQL2008." By default, this file is located in the following folder:
C:\Progam Files\Microsoft SQL Server\MSRS10.SQL2008\Reporting Services\ReportServer\bin
Save or copy the Reportingservices.mof to a file that has a unique name, such as Reportingservicesalt.mof.
Open Reportingservicealt.mof file by using a text editor, such as Notepad.
On the Edit menu, click Replace.
In the Find what area, type the changed instance name, such as RS_SQL2008.
In the Replace with area, type the unmodified instance name, such as SQL2008.
Click Replace All.
Save the file, and then exit Notepad.
Open a command prompt, and then move to the same folder that you used in step 1.
Type mofcomp reportingservicesalt.mof, and then press ENTER.

NOTE:- it may need not to be same as file name reportingservicesalt just check and execute mofcomp reportingservicesalt.mof from folder location.


Note If the reporting services instance name contains an underscore (_), a dollar sign ($) or a number sign (#), you have to use method 1. Or you have to reinstall the instance.

Suggested Max Memory Settings for SQL Server 2005/2008

It is pretty important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.



This is for x64, on a dedicated DB server.

Physical RAM MaxMem Setting

2GB 1500

4GB 3200

6GB 4800

8GB 6700

12GB 10600

16GB 14500

24GB 22400

32GB 30000

48GB 45000

64GB 59000



This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003)

Physical RAM Target Avail RAM in Task Manager

< 4GB 512MB – 1GB

4-32GB 1GB – 2GB

32-128GB 2GB – 4GB

> 128GB > 4GB



You can set this value with Transact-SQL like this:

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure

-- Set max server memory = 2300MB for the server
EXEC sp_configure 'max server memory (MB)', 2300
GO
RECONFIGURE
GO

RESTORE DATABASE is terminating abnormally.

http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/

Workaround:-

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks
FROM DISK = 'C\:BackupAdventureworks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Data\datafile.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\logfile.ldf',
REPLACE

SQL Server Performance

http://sql-articles.com/articles/dba/performance-data-collector-part-1

http://sql-articles.com/articles/dba/performance-data-collector-part-2

SQL Server Cluster Offline

Registry replication in Microsoft Cluster Server

http://support.microsoft.com/kb/174070

How to change SQL Server parameters in a clustered environment when SQL Server is not online

http://support.microsoft.com/kb/953504

server-side protocol initialization error codes

ERROR STATE DESCRIPTION

0x03 Error starting shared memory support

0x04 All protocols disabled

0x0A Unable to initialize the TCP/IP listener

0x1C Server configured to listen on a specific IP address in a cluster environment

0x1E Duplicate IP address detected in network

0x35 Error starting named pipe support

0x36 Error starting VIA support

0x38 Error obtaining or using the Certificate for SSL

0x3A Unable to initialize the communication listeners

0x40 Unable to initialize the Shared Memory listener

0x50 Unable to initialize the Named Pipe listener

0x60 Unable to initialize the VIA listener

0x70 Unable to initialize the HTTP listenerhttp://www.blogger.com/img/blank.gif

0x80 Unable to initialize SSL support

=============================================================
http://blogs.msdn.com/b/sql_protocols/archive/2006/01/10/511330.aspx

SQL Server Resources goes to Failed state

Help on :- http://support.microsoft.com/kb/883732
http://www.blogger.com/img/blank.gif

or

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/0http://www.blogger.com/img/blank.gif1/13/sql-server-2005-resource-fails-to-come-online-on-cluster-after-changing-the-san-drive-to-add-more-disk-space.aspx

1. Change these settings on both Nodes
2. Change this for SQL Server and Agent under the cluster resources with the respective GUID of SQL Server.
3. Bring SQL online.
4. Reboot the boxes SQL has to be switched over.
5. If not Check the regedit for the settings.
6. If values are missing then go for checkpoint of cluster from CMD mode before this take a backup of registry.

More help on http://support.microsoft.com/kb/912397

For a default instance of SQL Server, run the following command:
cluster res "SQL Server (Instancename)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"


cluster . resource "SQL Server (MSSQLSERVER)/addcheckpoints:”HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL Server\SQLServerAgent”

Some Cluster Issues

Event ID 1135 — Cluster Service Startup

http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx



Event ID 1069 — Clustered Service or Application Availability

http://technet.microsoft.com/en-us/library/dd353893(WS.10).aspx



Event ID 1177 — Quorum and Connectivity Needed for Quorum


http://technet.microsoft.com/en-us/library/dd353872(WS.10).aspx



Event ID 4201 — TCP/IP Network Interface Connectivity

http://technet.microsoft.com/en-us/library/dd392958(WS.10).aspx

Some Cluster Issues

Event ID 1135 — Cluster Service Startup

http://technet.microsoft.com/en-us/library/dd353973(WS.10).aspx



Event ID 1069 — Clustered Service or Application Availability

http://technet.microsoft.com/en-us/library/dd353893(WS.10).aspx



Event ID 1177 — Quorum and Connectivity Needed for Quorum


http://technet.microsoft.com/en-us/library/dd353872(WS.10).aspx



Event ID 4201 — TCP/IP Network Interface Connectivity

http://technet.microsoft.com/en-us/library/dd392958(WS.10).aspx

Cluster Failover Alert, When SQL Agent Restarts

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Administrative Job: Custom' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Administrative Job: Custom'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Service_Restart_Notification',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Notify key users of services restart',
@category_name=N'Administrative Job: Custom',
@owner_login_name=N'Sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Email Alerts',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''DBA'',
@body = ''This is an informational message only: SQL services possibly restarted on INDBLRSQL. Please restart any dependent application services after verifying status of Databases.'',
@subject = ''SQL Services Restarted '' ;
',
@database_name=N'master',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'On Startup',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110405,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked

Maintenance Plans with “Backup Database Task” fails when the “Create a sub-directory for each database” option is checked, with error similar to the below,

“Cannot open backup device 'D... The package execution fa... The step failed.,00:00:01,0,0,,,,0”



Detailed Error Message
==================

Executed as user: MachineName\SYSTEM. ...9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:15:03 AM Progress: 2008-07-24 07:15:04.11 Source: {134957B2-5C5F-4D4F-BDB7-ECAC9C3D8E20} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-07-24 07:15:04.69 Source: Back Up Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'D:\Program F".: 100% complete End Progress Error: 2008-07-24 07:15:04.71 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [Mstest ] TO DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak' WITH NOFORMAT, NOINIT, NAME = N'MStest _backup_20080724071504', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Cannot open backup device 'D... The package execution fa... The step failed.



Analysis
=======

When the “Create a sub-directory for each database” option is selected, the Maintenance task executes the xp_create_subdir procedure to create a directory with the Database Name. If you carefully look at the Detailed Error message above, you will see that there is a space character in the Database name.
“D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MStest \Mstest _backup_200807240715.bak”


If you try to run the Backup command extracted from the above error in the Query windows it gives a more informative error ,

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \Mstest _backup_200807240715.bak'. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.



Cause:
======
The Operating System does not allow you to create a folder with trailing spaces. When xp_create_subdir creates the folder with the space, the OS creates the folder but without the trailing space.

For example you can try this command EXECUTE master.dbo.xp_create_subdir N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Mstest \'

Now when the backup command fires it fails to read the directory, since there is no directory with a space created.


Resolution
=========
Follow the below method to remove the trailing space from the Database Name

1.
Right click and rename the database to any name. (This is because Management Studio will not allow you to remove the trailing space, since it thinks there is no change made to the name and that a database already exist with the same name
2.
Now rename the database to the original name without the space.
3.
Reconfigure your Maintenance Plan

Failed to notify ''operator name'' via email?...

First Check the value for Database mail profile is NUll

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile'

If So

select 'exec master.dbo.xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'', N''DatabaseMailProfile'', N''REG_SZ'', N''' + name + ''''
from msdb.dbo.sysmail_profile
where profile_id in (Select profile_id from msdb.dbo.sysmail_principalprofile )

Copy the output and Execute, it will be as below

exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBA'

Now Restart the SQL Agent. mail will work now.


------------------------------ OR --------------------------------

Verify below Steps are configured properly or not

1. Enable database mail, create a new profile and mail account

2. Right click SQL Agent>Properties>Alerts System>Enable Mail Profile

3. Expand SQL Agent>Operators>Create New Operator

Configuring above steps will work make DB mail to work properly.

Connecting to a Remote Integration Services Server

To connect to Integration Services on a Remote Server

1.
Open SQL Server Management Studio.

2.
Select File, Connect Object Explorer to display the Connect to Server dialog box.

3.
Select Integration Services in the Server type list.

4.
Type the name of a SQL Server Integration Services server in the Server name text box.




Note
The Integration Services service is not instance-specific. You connect to the service by using the name of the computer on which the Integration Services service is running.


5.
Click Connect.







Eliminating the "Access Is Denied" Error

--------------------------------------------------------------------------------



When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

To configure rights for remote users on Windows Server 2003 or Windows XP

1.
If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

3.
Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

4.
Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

5.
Right-click on MsDtsServer and select Properties.

6.
In the MsDtsServer Properties dialog box, select the Security tab.

7.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

8.
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

9.
Click OK to close the dialog box.

10.
Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

11.
Close the MMC snap-in.

12.
Restart the Integration Services service.


To configure rights for remote users on Windows 2000 with the latest service packs

1.
Run dcomcnfg.exe at the command prompt.

2.
On the Applications page of the Distributed COM Configuration Properties dialog box, select MSDTSServer and then click Properties.

3.
Select the Security page.

4.
Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access - Access permissions include local and remote access, and Launch permissions include local and remote launch.

5.
Close the dialog boxes and dcomcnfg.exe.

6.
Restart the Integration Services service.




Connecting by using a Local Account

--------------------------------------------------------------------------------



If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.



Delegation Is Not Supported

--------------------------------------------------------------------------------



SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.








Thanks to :- http://msdn.microsoft.com/en-us/library/aa337083.aspx

Backup Log Database with truncate _only

It became deprecated Command and removed in 2008. You can achieve the same result by sending the file to the NUL blackhole

BACKUP LOG [DBNAME] TO DISK='NUL'
GO
DBCC SHRINKFILE("LOGFILE",0)


OR

alter database DBNAME set recovery simple
go
alter database DBNAME set recovery full
go
sp_helpdb 'DBNAME '
GO




use DBNAME
go
dbcc shrinkfile(LOGFILE,0)