SQL Server Frequent mirror failovers, think beyond DBA skills

As you are a DBA you might have observed the primary databases configured in mirror gets failed over frequently / unexpectedly which causes downtime to the users.   So how do you start troubleshoot.... probably you could see the database status and once you find this was failed over to secondary, then how do you verify as you have all connectivity in place from secondary otherwise bring it back to the primary server to make sure the application connectivity remains to the users. 

But how do you troubleshoot to fix that unexpected errors, so that it will not happen again.

The first step is to start analyzing the SQL Server logs ....  you would get lot of information from it ;

SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf] in database [msdb] (4). The OS file handle is 0x000000000000

 The mirroring connection to "TCP://xxxx_mir.xxx.isyntax.net:5022" has timed out for database "xxxxx" after 10 seconds without a response. Check the service and network connections.

And check in Windows Event logs as well where you can find information for hardware & OS related.

So, one of these scenarios how do you troubleshoot further and fix the issue. 

1) You could change the timeout increased to a higher value, so that if there is no response from the IO or network you can avoid frequent failovers.
2) Analyze the IO errors working with storage admin then take up the action accordingly.
3) If you observe much load happening it requires system to be tuned.
4) See any disk related errors locally and sufficient disk space available.

In my case I have observed the server is a shared database node in VMware cluster and the SAN which is hosted the storage allocated to the database node is used for the other nodes as well.    So it looks like recently there were few more nodes added which kept more load on it, hence the IOPs is not happening with the desired rate.   

Hence SQL server is getting timed out as there is no response when it writes the data on the data file.   I worked with storage admin and move one of the other node to different VMcluster and that is connected to an another SAN together.  So reduced the IO load.

Hope this info helps you troubleshooting the mirror failover issues.
 

SQL Code/Procedure development suggestions

Most of you familiar writing stored procedures in SQL Server, but if you follow the development standards (in my experience)  which would help debugging the code (or) in case code modifications required it is easy to point out. 

Hope the following SQL coding rules help you while writing the SQL procedures & functions.

• Header part of the script should be updated reflecting the current changes to the script with the version details.  Suppose it is a new procedure you could mention as initial draft/release.  Later it can be added with all subsequent changes.

• Alignment of the script content should be proper.  Proper alignment shall help programmer to read the code easily.

• SNAPShot isolation helps reduce the locks, If the DB isolation is Snapshot and the SNAPSHOT isolation is supposed to be used in the scripts then verify that 'SET SNAPSHOT ISOLATION' is mentioned in the script. If in doubt confirm with the team which has sent the script for review and if 'SNAPSHOT ISOLATION' is mentioned, verify that SNAPSHOT ISOLATION is enabled at the database level too

• 'NOLOCK' can be used in the code when the diry reads are allowed which is less lock operation.

• 'SET NOCOUNT ON' statement should be mentioned in the beginning of each procedure. If the scripts require the counts and do not make use of SET NOCOUNT ON, make sure that it uses @@ROWCOUNT server variables instead, for fetching the counts.

• It is good to assign input parameter variables to the local variables then use it in procedure code.

• Fixed length columns in the table should use CHAR datatype all the times.

• TOP Statement might be required/might be present in the scripts. But verify if the TOP statement mentioned in the script is intentional/required.

• Any/All temp tables created in the procedure should be dropped at the end of the procedure

• SELECT statements using temp tables do not require NOLOCK as locks for the temp table maintained internally by SQL Server.

• If any other database references are present in the script, verify that the database is present, always use if exists clause to validate these cases.

• If LINKED Server references are present in the script, verify that the correct LINKED server are referenced and the LINKED Server is present in the appropriate environment.  Better to use If exists clause.

• Instead of multiple SET statements, a single SELECT statement can be used to assign value to variables that avoid confusion.

• Avoid using 'SELECT *' instead, use specific column name(s) in the SELECT statement.

•  Avoid using table variable if data stored in the table is too high.

• Avoid using CTE's if data stored in the corresponding CTE is too high.

• Avoid using co-related queries. See if the same can be resolved using subqueries instead.

• Avoid using CURSOR's if possible, try while instead.  If CURSOR is needed & used, check the CURSOR is CLOSED & DEALLOCATED before end of the procedure.

• Trigger should not be created on any table. If it is absolutely necessary see if an alternative approach can be used to eliminate the trigger.

• Check for Unused variable declarations in the procedures, this can be removed to have only required code.  Dead code / commented code should not be there in the procedure.

• Proper error handling should be done in the code, explaining the error handling in comments would help review the code.  

• Execution Plan of the procedure could help validating the logic and query optimization if needed.

• Validating the performance of the procedure can help figure out the performance bottlenecks.  Execute the procedure using parameters supplied in test environment atleast.

Drop database doesn't work when database configured for replication

I incurred this situation when one of the my test server configured for Transnational replication some time before and now I wanted to restore a fresh database copy on top of it, it failed with error saying 'Database is in use'.

Then I tried working to drop the database.  Since it is involved on replication it says 'database cannot be dropped unless you remove the replication'

Then tried removing the replication it says  'the object used for the replication owner is not DBO or you don't have access to remove the replication', though I am trying with sysadmin privileges.


Finally I used one of the system proc 'sp removedbreplication' and passed the Subscriber and Publisher database names as parameters then it got dropped both the subscriber and publisher under Replication Folder.


sp_removedbreplication @publisher
sp_removedbreplication @subscriber

Hope this information helps to remove the replication when it gives you an error !!!!

SQL Server database corrupted and in SUSPECT mode, how to fix it

As a routine DBA issue, sometimes the Database goes to suspect mode and not accessible to any of  the users.  When we verfiy the SQL Server logs it says the database consistency issue  which needs to be repaired. So how do one fix the database issue......

The possibilities are the database pages might have corrupted due to inconsistency where
the particular table resides.  We can identify these tables by using various methods;

Refer MSDB database and suspect_pages table for the list of the corrupted pages.
use MSDB
Go
select * from suspect_pages
Go

Alternatively run the below command for identify the corrupted object details

DBCC CHECKDB (DB_NAME) WITH ALL_ERRORMSGS, NO_INFOMSGS;
Error :  Table error : Object ID 0, Index ID -1, Partition ID 0 ..........
CheckDB found 0 allocation errors and 1 consistency errors not associated with any single
object.
Repair_Allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
(db_name)

Repair Method :
You can follow the below method for getting the database online by removing the corrupted
portion, but make sure you have proper approvals before doing so.  Because it removes the
corrupted pages completely from the databases.
use MASTER
GO
alter database db_name set single_user ;
Go
DBCC CHECKDB (db_name,'repair_allow_data_loss')
Go

this will give the results something like below, once it is repaired then you can make
the database into multi_user to give it to the users with proper backups being taken.

GO
alter database db_name set multi_user ;

DBCC results :
DBCC results for 'TEST'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 870 rows in 12 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 124 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysclones'.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for 'sys.sysallocunits'.
There are 138 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.sysseobjvalues'.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysdbfrag'.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for 'sys.sysfgfrag'.
There are 0 rows in 0 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.syspru'.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for 'sys.sysbrickfiles'.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysdbreg'.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for 'sys.sysprivs'.
There are 136 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 2180 rows in 29 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 694 rows in 11 pages for object "sys.syscolpars".
DBCC results for 'sys.sysxlgns'.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for 'sys.sysxsrvs'.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for 'sys  ...................   So on

How to read SQL Server Error log using Query Analyzer

Reading SQL Error log typically we do by going through the Management  then SQL Server error log and open respective one .  But it is little difficult to trace it out only the particular information out of the bunch of records if you activated all kind of log info.   So alternatively it could be done using SSMS by executing extended system procedures.  

xp_readerrorlog         returns the latest error log
xp_readerrorlog 1      returns the latest archived error log
xp_readerrorlog 2      returns the previous archived error log
 ..........  and so on.

You can try inserting the log records into temp table then filter only the information that you might required to analyze the issue.  I hope this would help and save the time instead of checking complete error log manually.
      

Understand the index usage details in Sql server

Understand the index usage from DMVs  in Sql server :  The below will retrieve the indexes performed look ups, scans, seeks by the user with the utilized dates.  If we see any unused indexes, could be deleted to improve the performance for Insert,update,delete statements.

select object_name(a.object_id) TABLE_NAME, a.index_id,b.name IndexName,
b.type_desc,a.last_user_seek,a.last_user_scan,a.last_user_lookup,a.last_user_update
from sys.dm_db_index_usage_stats a inner join sys.indexes b
on a.object_id = b.object_id and a.index_id = b.index_id
where a.database_id =6 and a.object_id in (object_id('xxx'), object_id ('xxx'))
order by A.OBJECT_ID,a.index_id

Find Orphan users from all databases exists in the SQL server

It is a typical requirement to find and fix the Orphan users in restored database to access the database with its underlying privileges.  In order to achieve this I have prepared a small script to find the Orphan users in all the databses exists in server.  Hope this helps to all, who are looking for this info in MS Sql server.

Script :

Declare @dbs table (id int identity(1,1), db varchar(100))

insert into @dbs (db)
select name from master.sys.databases where database_id > 4 order by name

--select * from @dbs


Declare @findOrphans table (db varchar(100), Orpuser varchar (100),USID varchar(200))
Declare @fixorphans table (db varchar(100), Script varchar (500))


Declare @a varchar(1000)
Declare @b tinyint
Declare @c tinyint
Declare @d varchar(100)

set @b = 1
select @c = MAX (id) from @dbs

while @b < @c
Begin
select @d = db from @dbs where id=@b
select @a = db+'.dbo.sp_change_users_login report ' from @dbs where id = @b
insert into @findorphans (Orpuser, UsID) exec (@a)
Update @findOrphans set db = @d where db is null
set @b = @b+1
end

select * from @findorphans

Slipstream Installation in Sql server 2008

Sql server installation has ability called slipstream which performs the service pack installation along with the software installation.  This saves time, pleas refer below link.

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

Findout SQL server Job notification details

How to findout all the notification details which are configured in the server, like there are some jobs which are not sending notifications for successive / failure actions, so we need to fix that. By identifying each and everyone manually it takes a long time when there are no. of jobs, so following query will help to identify the notification details.

select a.name, case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_email = 1 then 'Job success' when notify_level_email = 2 then 'Job failure' end NotifyEmail, b.name
from sysjobs a left outer join (Select * from Sysoperators ) b on a.notify_email_operator_id = b.id
union all
select a.name,case when a.enabled =1 then 'Enabled' else 'Disabled' end Status, case when notify_level_page = 1 then 'Job success' when notify_level_page = 2 then 'Job failure' end NotifyPage, c.name
from sysjobs a left outer join (Select * from Sysoperators ) c on a.notify_page_operator_id = c.id order by a.name

How to fix Orphan users in Sqlserver

The orphan users can be fixed by executing sp_change_users_login procedure. The syntax would be

Syntax :
sp_change_users_login 'update_one', 'Username','UserName'

SQL SERVER 2008 R2 FEATURES (CONSOLIDATED)

A Document
on
Sql Server 2008 R2 Features
Microsoft SQL Server 2008 R2 is the latest release of SQL Server. This documentation explains about SQL Server 2008 R2 and its features. The “R2” tag indicates that this is an immediate release of SQL Server. The Sql server 2008 R2 having features which helps to Developer and DBAs both. In addition to new features, there are two new editions as well, SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse.

Following are the new features, connected features and enhancements in existing features introduced in Sql Server 2008 R2.

Sql server 2008 R2 StreamInsight

New in SQL Server 2008 R2 is component called StreamInsight. This interesting component allows streaming data to be analyzed on the fly. Meaning the data is processed directly from the source stream prior to being saved in a SQL Server table. This could be extremely handy if you’re running a real time system and need to analyze data but can’t afford the latency of a committed write to a table first. Examples usually cited for this application include stock trading streams, click stream web analytics, and industrial process controls. Multiple input streams can be simultaneously monitored.

Sql server 2008 R2 Master Data Services

Master Data Services (MDS) is both a concept and a product. The concept of a Master Data Service is that there is a central data gate keeper of core business data. Data items such as customer billing addresses, employee/customer names, and product names should be centrally managed so that all consuming applications have the same information. The Microsoft example given is a company that has a customer address record in the customer table but a different address in the mailing table. A Master Data Service application would ensure that all tables would have only one correct address. While an MDS can be a homegrown application, SQL Server 2008 R2 includes an application and an interface to manage the central data.

SQL Server Report Builder 3.0 for SQL Server 2008 R2
Report Builder 3.0 introduces additional visualizations including maps, sparklines and databars which can help produce new insights well beyond what can be achieved with standard tables and charts. The Report Part Gallery is also included in this release - taking self-service reporting to new heights by enabling users to re-use existing report parts as building blocks for creating new reports in a matter of minutes with a “grab and go” experience. Additionally, users will experience significant performance improvements with enhancements to the ability to use Report Builder in server mode. This allows for much faster report processing with caching of datasets on the report server when toggling between design and preview modes.

SQL Server 2008 R2 Reporting Services Add-in for SharePoint Technologies 2010 The Microsoft SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint Technologies 2010 allows you to integrate your reporting environment with SharePoint to experience the benefits of using the collaborative environment provided by SharePoint. Once you install the Reporting Services Add-in and configure your servers for integration, you can publish Reporting Services content to a SharePoint library and then view and manage those documents directly from a SharePoint site.

SQL Server 2008 R2 Policies Microsoft SQL Server 2008 R2 Policies are examples of how you can take advantage of Policy Based Management. These policies will help you follow some of the SQL Server best practices and avoid common pitfalls. For more information, please see Administering Servers by Using Policy Based Management in SQL Server 2008 R2 Books Online.

Sql server 2008 R2 Data-Tier Application
A Data-Tier Application (abbreviated as DAC –no idea what the C stands for, and not to be confused with the Windows Data Access Components also abbreviated as DAC ) is an object that stores all the needed database information for a project, such as login, tables, and procedures into one package that can be consumed by Visual Studio. By creating a Data-Tier Application, a SQL Server package version could be saved with each Visual Studio build of your application. This would allow application code builds to be married to a database build in an easily managed way.

Unicode Compression in Sql server 2008 R2
SQL Server 2008 R2 uses a new algorithm known as Simple Compression Scheme for Unicode storage. This reduces the amount of disk spaced used by Unicode characters. This new format happens automatically and is managed by the SQL Server engine so no programming changes are required of the DBA.

SQL Server Utility in Sql server 2008 R2
The new SQL Server Utility is a repository object for centrally controlling multiple SQL Server instances. Performance data and configuration policies can be stored in a single Utility. The Utility also includes an Explorer tool where multi-server dashboards can be created.

Sql server 2008 R2 Multi Server Dashboards
While the SQL Server Management Studio could always connection to multiple servers, each was managed independently with no central view of all of them. Now with SQL Server 2008 R2, Dashboards showing combined server data can be created.

SQL Server Compact 3.5 SP2 SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server Compact 3.5 SP2 For Windows Mobile SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server Compact 3.5 SP2 Server Tools SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server Compact 3.5 SP2 Books On-line SQL Server Compact 3.5 SP2 is an embedded database that allows developers to build robust applications for Windows desktops and mobile devices. The download contains the files for installing SQL Server Compact 3.5 SP 2 on Windows desktop.

SQL Server JDBC Driver 3.0 In our continued commitment to interoperability, Microsoft has released a new Java Database Connectivity (JDBC) driver. The SQL Server JDBC Driver 3.0 download is available to all SQL Server users at no additional charge, and provides access to SQL Server 2008 R2 , SQL Server 2008, SQL Server 2005 and SQL Server 2000 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5. This release of the JDBC Driver is JDBC 4.0 compliant and runs on the Java Development Kit (JDK) version 5.0 or later. It has been tested against major application servers including IBM WebSphere, and SAP NetWeaver.

Connector 1.1 for SAP BW for SQL Server 2008 R2 The Microsoft Connector for SAP BW is a set of managed components for transferring data to or from an SAP NetWeaver BW version 7.0 system. The component is designed to be used with the Enterprise and Developer editions of SQL Server 2008 R2 Integration Services. To install the component, run the platform-specific installer for x86, x64, or Itanium computers respectively. For more information see the Readme and the installation topic in the Help file.

System CLR Types for SQL Server 2008 R2 The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server 2008 R2. This component can be installed separately from the server to allow client applications to use these types outside of the server.

SQL Server 2008 R2 Remote Blob Store The SQL Server Remote Blob Store is a method for storing blobs of unstructured data in an external Content Addressable data store. The component consists of a client-side DLL that is linked into a user application, as well as a set of stored procedures to be installed on SQL Server. Run the self-extracting download package to create an installation folder. The setup program contained there will install RBS on X86, X64, and Itanium-based computers.

SQL Server 2008 R2 Books On-line Microsoft SQL Server 2008 R2 Books Online is the primary documentation for SQL Server. Visit the SQL Server 2008 Books Online page on the Microsoft Download Center.

SQL Server 2008 R2 Upgrade Advisor Microsoft SQL Server 2008 R2 Upgrade Advisor analyzes instances of SQL Server 2000, SQL Server 2005 and SQL Server 2008 in preparation for upgrading to SQL Server 2008 R2. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.

SQL Server 2008 R2 Native Client Microsoft SQL Server 2008 R2 Native Client (SQL Server Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Server Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 R2 features. This redistributable installer for SQL Server Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 R2 features, and optionally installs the header files needed to develop an application that uses the SQL Server Native Client API.

OLEDB Provider for DB2 The Microsoft OLE DB Provider for DB2 Version 3.0 offers a set of technologies and tools for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2008 R2 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the data provider with Integration Services, Analysis Services, Replication, Reporting Services, and DistributedQuery Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the Version 3.0 provider and tools on x86, x64, and IA64 computers. Read the installation guide and release notes for more information.

SQL Server 2008 R2 Command Line Utilities The SQLCMD utility allows users to connect to, send Transact-SQL batches from, and output rowset information from SQL Server 7.0, SQL Server 2000, SQL Server 2005, and SQL Server 2008 and 2008 R2 instances. The bcp utility bulk copies data between an instance of Microsoft SQL Server 2008 R2 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

SQL Server Service Broker External Activator for SQL Server 2008 R2 The Microsoft SQL Server 2008 R2 Service Broker External Activator is an extension of the internal activation feature in SQL Server 2008 R2 that lets you move the logic for receiving and processing Service Broker messages from the Database Engine service to an application executable that runs outside SQL Server. By doing this, cpu-intensive or long-duration tasks can be offloaded out of SQL Server to an application executable, possibly in another computer. The application executable can also run under a different Windows account from the Database Engine process. This gives administrators additional control over the resources that the application can access. Run the self-extracting download package to create an installation folder. Read Books Online for more information. The single setup program will install the service on x86, x64, and IA64 computers. Read the documentation for more information

Windows PowerShell Extensions for SQL Server 2008 R2 The Microsoft Windows PowerShell Extensions for SQL Server2008 R2 includes a provider and a set of cmdlets that enable administrators and developers to build PowerShell scripts for managing instances of SQL Server. The SQL Server PowerShell Provider delivers a simple mechanism for navigating SQL Server instances that is similar to file system paths. PowerShell scripts can then use the SQL Server Management Objects to administer the instances. The SQL Server cmdlets support operations such as executing Transact-SQL scripts or evaluating SQL Server policies.

SQL Server 2008 R2 Shared Management Objects The SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services. This object model will work with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.
Note: Microsoft SQL Server 2008 R2 Management Objects Collection requires Microsoft Core XML Services (MSXML) 6.0, Microsoft SQL Server Native Client, and Microsoft SQL Server System CLR Types. These are available on this page.


SQL Server 2008 R2 ADOMD.NET ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 R2 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.


Analysis Services OLE DB Provider for SQL Server 2008 R2 The Analysis Services OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2008 R2 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.


SQL Server 2008 R2 Analysis Management Objects Analysis Management Objects (AMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer Analysis Services objects.

SQL Server Driver for PHP 1.1 The SQL Server Driver for PHP 1.1 is a PHP extension that allows for accessing data in all Editions of SQL Server 2005, SQL Server 2008,and SQL Server 2008 R2 (including Express Editions) from within PHP scripts. The driver provides a procedural interface for accessing data and makes use of PHP features, including PHP streams to read and write large objects. The SQL Server Driver for PHP relies on the Microsoft SQL Server Native Client to communicate with SQL Server.SQL Server Native Client can be downloaded on this Feature Pack page.

SQL Server Migration Assistant Microsoft SQL Server Migration Assistant (SSMA) is a family of tools that dramatically cut the effort, cost, and risk of migrating from Oracle, Sybase, MySQL or Access to any edition of SQL Server 2008 R2 or SQL Server 2008 or SQL Server 2005. SSMA for MySQL and SSMA for Access products also support simple and direct migration to SQL Azure. SSMA provides an assessment of migration efforts as well as automates schema and data migration.

SQL Server 2008 R2 Best Practices Analyzer SQL Server 2008 R2 Best Practices Analyzer is an analysis tool that validates your system configuration and execution against a recommended set of best practices developed by SQL Server Engineering and Customer Support.

Optimize Hardware ResourcesThis is a great new feature for database administrators as it will provide a real time insight into Server Utilization, Policy Violations etc. This feature will help organizations to strictly apply organization wide policies across servers thereby helping them maintain a healthy system.

Manage Efficiently at Scale
This feature will help database administrator to gain insight into growing applications and databases thereby helping them to ensure better management of database servers.

Enhance Collaboration Across Development and IT
Database Application development will be more closely integrated with Visual Studio 2010 which will help to ensure higher quality during the application development along with easier deployments and better handling of changes over time.

Build Robust Analytical Applications
Using Microsoft Office Excel 2010 you can build robust analytical applications which will allow in-memory, column oriented processing engine to allow users to interactively explore and perform complex calculations on millions of data at lightening speeds. Using Microsoft Excel 2010 you can easily integrate data from multiple sources such as corporate databases, spreadsheets and external data sources.

Support for Geospatial Visualization
Microsoft SQL Server 2008 R2 will provide support for geospatial visualization including mapping, routing, and custom shapes. It will also support SQL Spatial and will also provide integration with Microsoft Virtual Earth tiles.



Sql server 2008 R2 Edition wise Information :
Standard Edition: Now with Backup Compression
SQL Server 2008 introduced backup compression, but it was only available in Enterprise Edition. At the time, Enterprise Edition cost around $20,000 more per processor than Standard Edition, so companies couldn’t justify upgrading to Enterprise Edition just to get backup compression. Companies had to need Enterprise for multiple features in order to stomach the price. If all a DBA needed was compression, they could buy backup compression software much cheaper than the price of Enterprise Edition.
In SQL 2008 R2, even Standard Edition gets backup compression. That’s a game-changer, and I’d expect to see smaller companies that do backup compression – and nothing else – to start falling by the wayside.
In addition, Standard can now be a managed instance – it can be managed by some of the slick multi-server-management tools coming down the pike like the Utility Control Point (read my SQL 2008 R2 Utility review). It can’t be the management server itself – it can’t be a Utility Control Point – but at least we can manage Standard. It’s good to see that Microsoft recognizes all servers need to be managed, not just the expensive ones. Big thumbs up there.

Enterprise Edition: CPU LimitsIn Enterprise, Microsoft giveth and Microsoft taketh away. SQL 2008 R2′s BI tools include a new Master Data Services tool. It’s targeted at enterprises with data warehouses that need to manage incoming data from lots of different sources, and that data isn’t always clean or correct. MDS helps make sure data follows business rules. This isn’t a common need for OLTP systems, so it’s only included in Enterprise, not Standard. Makes sense.
A little less easy to stomach, however, is a new set of caps on Enterprise Edition. The current SQL 2008 comparison page shows that Enterprise has no licensing limit on memory or the number of CPU sockets. SQL 2008 R2 Enterprise Edition is capped at 8 CPU sockets, and there’s a memory cap as well, but I haven’t been able to track down a public page showing the cap. The only hint is the SQL 2008 R2 edition comparison page, which notes that Datacenter Edition (more on that in a second) is licensed for “memory limits up to OS maximum.” If that wasn’t a unique selling point, it shouldn’t be included in the feature list.
The more expensive Enterprise can act as the management server (Utility Control Point) for up to 25 instances. However, that doesn’t mean you need to buy one Enterprise per 24-25 Standard servers, and then manage them in pools – there’s an app edition for that.

Datacenter Edition: For, Well, Datacenters
The new Datacenter Edition picks up where Enterprise now runs out of gas. It supports more than 8 sockets, up to 256 cores, and all the memory you can afford. Or can’t afford, for that matter.
If you’re going to manage over 25 instances with the Utility Control Point stuff, Datacenter Edition can manage “more than 25 instances” according to Microsoft’s edition comparison page. I like how they worded that – they didn’t say “unlimited instances,” because there will be performance impacts associated with using Utility Control Points. The performance data collections gather a lot of data, and storing it for hundreds of instances will take some pretty high performance hardware.

Parallel Data Warehouse Edition: Sold with Hardware Only
The big new fella in town getting all the press is the artist formerly known as Project Madison, formerly known as DATAllegro. It’s a scale-out data warehouse appliance, but you won’t find this appliance at Home Depot. This version of SQL Server is sold in reference architecture hardware packages from Bull, Dell, HP, EMC, and IBM. Write one check, and you get a complete soup-to-nuts data warehouse storage engine that includes everything from the servers, SAN, configuration, and training.
I had the chance to talk with Microsoft’s Val Fontama, and I’ll post more details of that interview next week, but I have to share one quick snippet. I asked what happens when a Parallel Data Warehouse system starts to have performance issues, and he explained that the DBA will need to call in specialized Parallel engineers. You won’t be popping open this rack and installing another drawer of hard drives yourself or adding additional commodity hardware boxes to scale out your datacenter. It’s more of a sealed solution than something you have to build yourself.
I have mixed feelings about this – as a guy who loves hardware, I want to dive under the hood. However, as a guy who’s managed data warehouses, I know it’s one heck of an ugly skillset to learn on the job, and when data gets into the 5-10 terabyte range, you can’t afford to make configuration mistakes.

New Index features in Sql server 2005 & 2008

1. Indexes in SQL Server 2005: Index features:

INCLUDE (column [,... n ] )

 Specifies the nonkey columns to be added to the leaf level of the nonclustered index.
 The maximum number of included nonkey columns is 1,023 columns; the minimum number is 1 column.
 Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and nonkey columns.
 All data types are allowed except text, ntext, and image.

Index arguments

ONLINE = {ON|OFF}
 Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation.
 The default is OFF.
 Online index operations are available only in SQL Server 2005 Enterprise Edition.

ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.


ALLOW_ROW_LOCKS = {ON | OFF}

 Specifies whether row locks are allowed.
 The default is ON.

ON

 Row locks are allowed when accessing the index.
 The Database Engine determines when row locks are used.

OFF
 Row locks are not used.

ALLOW_PAGE_LOCKS = {ON | OFF}

 Specifies whether page locks are allowed.
 The default is ON.

ON
 Page locks are allowed when accessing the index.
 The Database Engine determines when page locks are used.

OFF
 Page locks are not used.

MAXDOP = max_degree_of_parallelism

 Overrides the max degree of parallelism configuration option for the duration of the index operation.
 Use MAXDOP to limit the number of processors used in a parallel plan execution.
 The maximum is 64 processors.
 The below table shows the possible max_degree_of_parallelism and its description.

Max_degree_of_parallelism Description
1 Suppresses parallel plan generation.
>1 Restricts the max.no of processors used in a parallel index operation to the specified number
0(Default) Uses the actual number of processors






2. Indexes in SQL Server 2008: Index features:

WHERE

 Creates a filtered index by specifying which rows to include in the index.
 The filtered index must be a nonclustered index on a table.
 Filtered indexes do not apply to XML indexes and full-text indexes.
 Filtered indexes do not allow the IGNORE_DUP_KEY option.
 The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators.

FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | NULL}

 Specifies the placement of FILESTREAM data for the table when a clustered index is created.
 The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.
 filestream_filegroup_name is the name of a FILESTREAM filegroup.
 The filegroup must have one file defined for the filegroup otherwise an error is raised.
 If the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. Otherwise, an error is raised.
 If the table is not partitioned, the FILESTREAM column cannot be partitioned. FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.
 FILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.

Index arguments

DATA_COMPRESSION
 Specifies the data compression option for the specified index, partition number, or range of partitions. The options are as follows:
NONE
 Index or specified partitions are not compressed.
ROW
 Index or specified partitions are compressed by using row compression.
PAGE
 Index or specified partitions are compressed by using page compression.

FIND LIST OF JOBS AND THEIR SCHEDULE WITH STEP WISE

SELECT SJ.NAME, SJS.STEP_ID,SJS.STEP_NAME,SJS.COMMAND,
SJ.DESCRIPTION,
(SELECT NAME FROM MASTER.DBO.SYSLOGINS WHERE SID IN (SJ.OWNER_SID)) JOB_OWNER,
SJ.DATE_CREATED,SJ.DATE_MODIFIED, SJS.DATABASE_NAME, case when d.freq_type = 8 then 'Weekly' when d.freq_type = 4 then 'Daily' else 'None' end Schedule,
d.Freq_Subday_Interval Interval_in_Minutes,
case when len(d.active_start_time) = 5 then convert(varchar,left(d.active_start_time,1)) + ':' + convert(varchar,left(right(d.active_start_time,4),2)) + ':' + convert(varchar,right (d.active_start_time,2))
when len(d.active_start_time) = 4 then '00' + ':'+convert(varchar,left(d.active_start_time,2)) + ':'+convert(varchar,right(d.active_start_time,2))
when len(d.active_start_time) = 3 then '00:0' + convert(varchar,left(d.active_start_time,1)) + ':'+convert(varchar,right(d.active_start_time,2))
when len(d.active_start_time) = 2 then '00:00:' + convert(varchar,left(d.active_start_time,2))
when len(d.active_start_time) = 1 then '00:00:0' + convert(varchar,left(d.active_start_time,1))
else
convert(varchar,left(d.active_start_time,2)) + ':' + convert(varchar,left(right(d.active_start_time,4),2)) + ':' + convert(varchar,right (d.active_start_time,2)) End [Scheduled_Time]
FROM SYSJOBS SJ INNER JOIN SYSJOBSTEPS SJS
ON SJ.JOB_ID = SJS.JOB_ID
LEFT OUTER JOIN SYSJOBSCHEDULES SJSC ON SJ.JOB_ID = SJSC.JOB_ID
LEFT OUTER JOIN SYSSCHEDULES d ON SJSC.SCHEDULE_ID = d.SCHEDULE_ID
ORDER BY NAME

Sql Monitoring - Job Failure information

A common requiremnet in DBA Environment to findout what are the jobs got failure in the servers. The jobs use to perform for various activities like Mainteance, Performance Related, Projects related, Backups/restore, making Disaster recovery techniques etc. If the job got failure and no solution has been provided for that, there could be some problem in regular activities.

Following query helps to findout what are the jobs got failure and by getting the information solution can be provided. If required this can be created as procedure in MSDB to simplify the task.

----------------------------------------------------------------------------------
Create Proc Pr_MonitorFailure as
Begin
select b.name Job,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End [Last Run Datetime],
--run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Last Run Duration], Message,
case when run_status = 0 then 'Fail' else 'Other Reason' end run_status from
Sysjobhistory a inner join Sysjobs b on a.job_id = b.job_id
where run_status <> 1 and message not like '%The Job was invoked%'
End
----------------------------------------------------------------------------------

Basic information about Joins in Sql server

Joins are really important for writing queries in any database language. If you take SQL Server there are few joins available which can help to construct sql statemetns. The Joins are combination of keywords Intersect, Union, Union all which we have learnt during our X standards. Join can be matched to 2 or more tables (usually), you can match single table also using join i.e., called Self join.

In Sql server we have mainly
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join
Self Join

For best understanding create following tables and insert the data in sample database. So that you can understand very easily how the join condition results.

----------------------------
Create table Table1 (id int, name varchar(10))
Create table Table2 (id int, Name varchar(10))

insert into table1 (id, name)
values (1,'A')
insert into table1 (id, name)
values (2,'AB')
insert into table1 (id, name)
values (3,'ABC')

insert into table2 (id, name)
values (1,'A')
insert into table2 (id, name)
values (2,'AB')

----------------------------
JOINS :
Inner join : A Intersect B; it means the data results which are available in both the tables.
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A inner join Table2 B on a.id = b.id

Left outer Join : A union B : Whatever the data available in A table and the matching data from B Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Left outer join Table2 B on a.id = b.id

Right outer Join : A union B : Whatever the data available in B table and the matching data from A Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Right outer join Table2 B on a.id = b.id

Full outer Join : All the data from A and B tables
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Full outer join Table2 B on a.id = b.id

Cross Join : A X B ; for each row in a multiply into all rows in B. Like wise for all the rows.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A cross join Table2 B

Self Join : By using self join we can match the same table for different columns.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A join Table1 B on a.id = b.id

Reindex all tables which are highly fragmented in the database

A common database maintenance activity, we need to Reindex database tables to maintain less fragmentation. For performing this we can identify each and every object which has fragmentation reached our expectation level; instead I have designed 2 procedures to findout the fragmented tables and perform the reindex on all indexes available in identified tables.

Hope this will reduce your maintenance time of writing queries for reindexing.
You can choose MSDB to create this proc and change the content to refer actual databases


-- Identifying Fragmentation of Tables (1)

Create Proc Pr_View_Defragmentation (@database sysname, @Frag int)
as
Declare @dbid int
select @dbid = dbid from sys.sysdatabases where name = @database
select distinct xtype, name,object_name(object_id),* from sys.dm_db_index_physical_stats (@dbid,NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag

-- Perform Reindexing to decrease the Fragmentation (2)

Create Proc Pr_Alter_index (@database sysname, @frag int) as
--DECLARE @DATABASE SYSNAME
--DECLARE @FRAG INT
Declare @a int
Declare @b int
Declare @sql varchar(1000)
Declare @Table table(id int identity(1,1),Object varchar(100))
Declare @sql1 varchar(100)
--set @database = 'DATABASE'
--set @frag = 10
SEt @a = 1
select @B = count(DISTINCT object_id) from sys.dm_db_index_physical_stats (db_id(@database),NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag
insert into @table(object)
Select distinct object_name(object_id) from sys.dm_db_index_physical_stats (db_id(@database),NULL,NULL,NULL,'SAMPLED')
a inner join sys.sysobjects b on a.object_id = id where xtype = 'u' and name not like '%sys%'
and avg_fragmentation_in_percent >= @frag
while @a < @b
Begin
select @SQL1 = OBJECT FROM @TABLE where id = @a SET @SQL = 'ALTER INDEX ALL ON ' + @database + '..' + @sql1 + ' REBUILD WITH (ONLINE = ON)'
exec (@sql)
set @a = @a+1
End


exec pr_View_Defragmentation database, fragmentation percentage
exec Pr_Alter_index database,fragmentation percentage

Eg : exec pr_View_Defragmentation 'abcd', 70

Maintain all jobs with a default owner account in Sql server

This is common requirement in DBA environment, like all created jobs in the server should run with specified username. Normally in production environment user will not perform the job manually and if it is on schedule also it should run with some service account. When creating a job it would be created with the default user who creates the job and there may be chances also to forget changing the job owner.

This query will helps to perform all jobs should be under default owner account.

declare @username varchar(100)
set @username = 'sa'
update sysjobs set owner_sid =
(select sid from master.dbo.syslogins where name = @username)
where name in (job_a, job_b,...... job_n)


* you can change the parameters as per your requirement

Findout Database Restoration Details

I got confusion that have I restored my database using latest back or yet to be restored ? B'cos I have no. of databases in different environments and requires to be restored with the latest backup arrived from client dbs.

To findout these details in the database like when it was restored either full database (or) only filegroup or part of some files restored, It can be achieved in following way :
USE MSDB
GO

select BS.user_name,
destination_database_name Database_name,
restore_date Date,
BS.database_name Actual_Database,
BS.server_name,
BS.name,
physical_name,
backup_start_date,
BF.backup_size
from RestoreHistory RH inner join BackupSet BS on RH.backup_set_id = BS.backup_set_id
inner join BackupFile BF on BF.backup_set_id = BS.backup_set_id
order by RH.Restore_Date

* Database_name and Actual_Database both are same but Actual_Database refers the database which you have restored. If the database is renamed after restoration also it shows the actual name when it was restored.

How PERFMON (Windows tool) helps DBA to identify the Performance Issues and take necessary solutions

In SQL Server DBA Environment, there are several ways to improve the performance of SQL Server Database Applications such as Query Execution Plans, Sql Profiler, DTA (Database Tuning Advisor), Server Level properties, Object level improvements (Indexes, statistics, other maintenance stuff) and Windows Level Applications. I mean the database performance can be identified in 3 levels i.e., Server Level (Operating Systems, Networking Protocols); Database Level (Sql Server Databsae Engine, SQL Server) ; Object Level (Objects within the database).


So if we are facing the performance degrade then we need to check up at all levels for taking necessary actions. Currently I will discuss about the Server Level Tool PERFMON which is very useful to identify the sever levels activities, based on the identified results we can take appropriate action to improve the performance. It can be Hardware, Memory (or) System changes.


PERFMON : PERFMON is a windows inbuilt tool which can provide the workload of the resources running in the system. It can be used to find out Windows resources data as well as SQL Server resources.


Main Benefits Of The Tool :
• Understand your workload and its effect on your system's resources.
• Observe changes and trends in workloads and resource usage so you can plan for future upgrades.
• Test configuration changes or other tuning efforts by monitoring the results.


System Monitor and Performance Logs and Alerts provide detailed data about the resources used by specific components of the operating system and by programs that have been designed to collect performance data.

Choosing the data to monitor :
Start by monitoring the activity of the following components in order:
• Memory
• Processors
• Disks
• Network


Following counters can be helpful to trace the data

1:
Component : Disk
Performance aspect being monitored : Usage
Counters to monitor :
Physical Disk\Disk Reads/sec, Physical Disk\Disk Writes/sec, LogicalDisk\% Free Space, Interpret the % Disk Time counter carefully. Because the _Total instance of this counter may not accurately reflect utilization on multiple-disk systems, it is important to use the % Idle Time counter as well. Note that these counters cannot display a value exceeding 100%.


2 :
Component : Disk
Performance aspect being monitored : Hindrances
Counters to Monitor : Physical Disk\Avg. Disk Queue Length (all instances)


3:
Component : Memory
Performance aspect being monitored : Usage
Counters to Monitor : Memory\Available Bytes, Memory\Cache Bytes


4:
Component : Memory
Performance aspect being monitored : Hindrances
Counters to Monitor : Memory\Pages/sec, Memory\Page Reads/sec, Memory\Transition Faults/sec, Memory\Pool Paged Bytes, Memory\Pool Nonpaged Bytes.
Although not specifically Memory object counters, the following are also useful for memory analysis: Paging File\% Usage object (all instances), Cache\Data Map Hits %, Server\Pool Paged Bytes and Server\Pool Nonpaged Bytes


5:
Component : Network
Performance aspect being monitored : Throughput
Counters to Monitor : Protocol transmission counters (varies with networking protocol); for TCP/IP: Network Interface\Bytes total/sec, Network Interface\ Packets/sec, Server\Bytes Total/sec, or Server\Bytes Transmitted/sec and Server\Bytes Received/sec


6:
Component : Processor
Performance aspect being monitored : Usage
Counters to Monitor : Processor\% Processor Time (all instances)


7:
Component : Processor
Performance aspect being monitored : Hindrances
Counters to Monitor : System\Processor Queue Length (all instances),
Processor\ Interrupts/sec, System\Context switches/sec



How to Create and perform :
1. Go to RUN and type PERFMON then Enter
2. Double-click Performance Logs and Alerts, and then double-click Counter Logs. Any existing logs will be listed in the details pane. A green icon indicates that a log is running; a red icon indicates that a log has been stopped.
3. Right-click a blank area of the details pane, and click New Log Settings.
4. In Name, type the name of the log, and then click OK.
5. On the General tab, click Add Objects and select the performance objects you want to add, or click Add Counters to select the individual counters you want to log.


Configure the other setings as you required. It can be run for certain time period i.e, 10 hours, 1 day, 1 week to identify how the processes are running in the system. The data can be saved as .CSV or text files. When you get the data you can make a charts using Excel and it can be understandable what necessary action to be taken for improving performance.

Understand how shrinking a LOG file works :

Understand how shrinking a LOG file works :

Many of them faced while shrinking a log file in the database, after applying the DBCC
Shrinkfile option also the files size not reduced. What was the structure behind, how it works.

For an example you have a database consists of data and log files, which log file size is growing abnormally exceeding hard drive size. So you need to reduce the file to accommodate within the available space. So maximum people would perform following options to decrease the file size.

1) Either truncating log file directly then shrink
Backup log 'DATABASE' with truncate_only
DBCC shrinkdatabase ('database',10) (or)
DBCC shrinkfile ('logfile')

2) or Applying the shrinkdatabase option directly on the database.

3) or Keeping Full / Log backup jobs in the server with some time intervals and shrinking the database.

Whether these above options are sufficient to perform the maintenance of Log file in the server. No, these can be manageable upto some extent only, if you can follow the above activities with slight changes you can expect 100% results from it.


What are the drawbacks in above processes :

1) If you truncate directly your log file then shrink the database, you can have reduced log file, but if you need to restore your database using the log backup.......... no backup available

2) Applying shrink option directly doesn't give you much impact

3) Keeping Log backup and perform shrink option will give you the good result but it should happend sequentially.


The Process behind log backup and shrink :
1) I have database consists of log file size 100Mb and Used space in the log file is 94 MB.
This can be find out using DBCC sqlperf(logspace).
2) I have performed DBCC shrinkfile ('log file') for size decrease ; but it increased instead of decrease. Why bcos the log file having the logs and for shrinking it written on the top of it, it increased. Use DBCC sqlperf(logspace) to check the details.
3) Now I have performed backup log file then performed the shrink option. It reduced to the initial size and my drive is free now.


Additional options to findout :

A) Run following query
DBCC loginfo
If the status in the below table is 2 then it is waiting for the backup
else it is 0 then it can be shrinked

result :
Field FileSize Startoffset FseqNo STatus Parity CreateLSN
2 2555904 8192 98682 2 64 0
2 2555904 2564096 98683 2 128 0
2 2555904 5120000 98685 2 64 0
2 2809856 7675904 98684 2 128 0
2 253952 10485760 98686 2 64 98685000000407400016
2 253952 10739712 98687 2 64 98685000000407400016
2 253952 10993664 98688 2 64 98685000000407400016



b) Run following query whether the backup is pending or not
select log_reuse_wait_desc from sys.databases where name = 'database'

if the result is 'LOG_BACKUP' then waiting for backup
if the result if 'NOTHING' then it can be shrinked


Note : In Sql Server 2008 there is no truncating log backup option, instead you can alter the recovery mode into simple then shrink. Again change the mode into normal.