The below code can be used to Kill all the processes to SQL Server Database. The code can be used in situations where changes to the database can not be done if any process is running on database such as renaming database .
Elmah download page: https://elmah.github.io/downloads/#elmah-12-sp2
/* ELMAH - Error Logging Modules and Handlers for ASP.NET Copyright (c) 2004-9 Atif Aziz. All rights reserved. Author(s): Atif Aziz, http://www.raboof.com Phil Haacked, http://haacked.com Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. */ -- ELMAH DDL script for Microsoft SQL Server 2000 or later. -- $Id: SQLServer.sql addb64b2f0fa 2012-03-07 18:50:16Z azizatif $ DECLARE @DBCompatibilityLevel INT DECLARE @DBCompatibilityLevelMajor INT DECLARE @DBCompatibilityLevelMinor INT SELECT @DBCompatibilityLevel = cmptlevel FROM master.dbo.sysdatabases WHERE name = DB_NAME() IF @DBCompatibilityLevel <> 80 BEGIN SELECT @DBCompatibilityLevelMajor = @DBCompatibilityLevel / 10, @DBCompatibilityLevelMinor = @DBCompatibilityLevel % 10 PRINT N' =========================================================================== WARNING! --------------------------------------------------------------------------- This script is designed for Microsoft SQL Server 2000 (8.0) but your database is set up for compatibility with version ' + CAST(@DBCompatibilityLevelMajor AS NVARCHAR(80)) + N'.' + CAST(@DBCompatibilityLevelMinor AS NVARCHAR(80)) + N'. Although the script should work with later versions of Microsoft SQL Server, you can ensure compatibility by executing the following statement: ALTER DATABASE [' + DB_NAME() + N'] SET COMPATIBILITY_LEVEL = 80 If you are hosting ELMAH in the same database as your application database and do not wish to change the compatibility option then you should create a separate database to host ELMAH where you can set the compatibility level more freely. If you continue with the current setup, please report any compatibility issues you encounter over at: http://code.google.com/p/elmah/issues/list =========================================================================== ' END GO /* ------------------------------------------------------------------------ TABLES ------------------------------------------------------------------------ */ CREATE TABLE [dbo].[ELMAH_Error] ( [ErrorId] UNIQUEIDENTIFIER NOT NULL, [Application] NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Host] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Type] NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Source] NVARCHAR(60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Message] NVARCHAR(500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [User] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [StatusCode] INT NOT NULL, [TimeUtc] DATETIME NOT NULL, [Sequence] INT IDENTITY (1, 1) NOT NULL, [AllXml] NTEXT COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[ELMAH_Error] WITH NOCHECK ADD CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY NONCLUSTERED ([ErrorId]) ON [PRIMARY] GO ALTER TABLE [dbo].[ELMAH_Error] ADD CONSTRAINT [DF_ELMAH_Error_ErrorId] DEFAULT (NEWID()) FOR [ErrorId] GO CREATE NONCLUSTERED INDEX [IX_ELMAH_Error_App_Time_Seq] ON [dbo].[ELMAH_Error] ( [Application] ASC, [TimeUtc] DESC, [Sequence] DESC ) ON [PRIMARY] GO /* ------------------------------------------------------------------------ STORED PROCEDURES ------------------------------------------------------------------------ */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE [dbo].[ELMAH_GetErrorXml] ( @Application NVARCHAR(60), @ErrorId UNIQUEIDENTIFIER ) AS SET NOCOUNT ON SELECT [AllXml] FROM [ELMAH_Error] WHERE [ErrorId] = @ErrorId AND [Application] = @Application GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE [dbo].[ELMAH_GetErrorsXml] ( @Application NVARCHAR(60), @PageIndex INT = 0, @PageSize INT = 15, @TotalCount INT OUTPUT ) AS SET NOCOUNT ON DECLARE @FirstTimeUTC DATETIME DECLARE @FirstSequence INT DECLARE @StartRow INT DECLARE @StartRowIndex INT SELECT @TotalCount = COUNT(1) FROM [ELMAH_Error] WHERE [Application] = @Application -- Get the ID of the first error for the requested page SET @StartRowIndex = @PageIndex * @PageSize + 1 IF @StartRowIndex <= @TotalCount BEGIN SET ROWCOUNT @StartRowIndex SELECT @FirstTimeUTC = [TimeUtc], @FirstSequence = [Sequence] FROM [ELMAH_Error] WHERE [Application] = @Application ORDER BY [TimeUtc] DESC, [Sequence] DESC END ELSE BEGIN SET @PageSize = 0 END -- Now set the row count to the requested page size and get -- all records below it for the pertaining application. SET ROWCOUNT @PageSize SELECT errorId = [ErrorId], application = [Application], host = [Host], type = [Type], source = [Source], message = [Message], [user] = [User], statusCode = [StatusCode], time = CONVERT(VARCHAR(50), [TimeUtc], 126) + 'Z' FROM [ELMAH_Error] error WHERE [Application] = @Application AND [TimeUtc] <= @FirstTimeUTC AND [Sequence] <= @FirstSequence ORDER BY [TimeUtc] DESC, [Sequence] DESC FOR XML AUTO GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE [dbo].[ELMAH_LogError] ( @ErrorId UNIQUEIDENTIFIER, @Application NVARCHAR(60), @Host NVARCHAR(30), @Type NVARCHAR(100), @Source NVARCHAR(60), @Message NVARCHAR(500), @User NVARCHAR(50), @AllXml NTEXT, @StatusCode INT, @TimeUtc DATETIME ) AS SET NOCOUNT ON INSERT INTO [ELMAH_Error] ( [ErrorId], [Application], [Host], [Type], [Source], [Message], [User], [AllXml], [StatusCode], [TimeUtc] ) VALUES ( @ErrorId, @Application, @Host, @Type, @Source, @Message, @User, @AllXml, @StatusCode, @TimeUtc ) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:
- Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
- Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
To view users who currently have VIEW SERVER STATE, execute the following query:
SELECT who.name AS [Principal Name], who.type_desc AS [Principal Type], who.is_disabled AS [Principal Is Disabled], what.state_desc AS [Permission State], what.permission_name AS [Permission Name] FROM sys.server_permissions what INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id WHERE what.permission_name = 'View server state' AND who.name NOT LIKE '##MS%##' AND who.type_desc <> 'SERVER_ROLE' ORDER BY who.name
To grant permission:
GRANT VIEW SERVER STATE TO [UserId]
The DBA is not going to have time to scrutinize every change made to a stored procedure. Learning to do basic tuning might save you from reworking code late in the game.
Performance tuning is not easy and there aren’t any silver bullets, but you can go a surprisingly long way with a few basic guidelines.
Database Optimization for Developers:
- If your application stops working suddenly, it may not be a database issue. For example, maybe you have a network problem. Investigate a bit before you accuse a DBA!
- Even if you’re a ninja SQL data modeler, ask a DBA to help you with your relational diagram. They have a lot to share and offer.
- DBAs don’t like rapid changes. This is natural: they need to analyze the database as a whole and examine the impact of any changes from all angles. A simple change in a column can take a week to be implemented—but that’s because an error could materialize as huge losses for the company. Be patient!
- Do not ask SQL DBAs to make data changes in a production environment. If you want access to the production database, you have to be responsible for all your own changes.
Database Optimization for SQL Server DBAs:
- If you don’t like people asking you about the database, give them a real-time status panel. Developers are always suspicious of a database’s status, and such a panel could save everyone time and energy.
- Help developers in a test/quality assurance environment. Make it easy to simulate a production server with simple tests on real-world data. This will be a significant time-saver for others as well as yourself.
- Developers spend all day on systems with frequently-changed business logic. Try to understand this world being more flexible, and be able to break some rules in a critical moment.
- SQL databases evolve. The day will come when you have to migrate your data to a new version. Developers count on significant new functionality with each new version. Instead of refusing to accept their changes, plan ahead and be ready for the migration.
In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server’s overall performance by only maintaining useful indexes – but finding the ones you don’t need can be quite a manual process.
If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index.
Remember that the data from these DMVs is reset when SQL Server is restarted, so make sure you have collected data for a long enough period of time to determine which indexes may be good candidates to be dropped.
Run this in SQL Server:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 AND S.database_id = DB_ID()
Here we can see seeks, scans, lookups and updates.
The seeks refer to how many times an index seek occurred for that index. A seek is the fastest way to access the data, so this is good.
The scans refers to how many times an index scan occurred for that index. A scan is when multiple rows of data had to be searched to find the data. Scans are something you want to try to avoid.
The lookups refer to how many times the query required data to be pulled from the clustered index or the heap (does not have a clustered index). Lookups are also something you want to try to avoid.
The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.
To find the ones that can be safely removed, run this:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 AND OBJECT_NAME(S.[OBJECT_ID]) = '[your table name]' AND S.database_id = DB_ID() AND (USER_SEEKS = 0 AND USER_SCANS =0 AND USER_LOOKUPS=0)
You can then delete the unused indexes.