Elmah database scripts

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

Advertisements

Retrieve Users who have VIEW SERVER STATE Permission

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

viewState

To grant permission:

GRANT VIEW SERVER STATE TO [UserId]

Update Statistics in All databases on a server

Simply run this script in SQL Server Management Studio to obtain the script to run for the current server. Copy-paste the results in a new window and press F5.

DECLARE @SQL VARCHAR(1000) 
DECLARE @DB sysname

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR 
SELECT [name] 
FROM master..sysdatabases 
WHERE [name] NOT IN ('model', 'tempdb') 
ORDER BY [name] 

OPEN curDB 
FETCH NEXT FROM curDB INTO @DB 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13) 
PRINT @SQL 
FETCH NEXT FROM curDB INTO @DB 
END 

CLOSE curDB 
DEALLOCATE curDB

Performance tuning tips for database developers

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.

Continue reading “Performance tuning tips for database developers”

Database optimization tips for Developers and SQL Server DBAs

Database Optimization for Developers:

  1. 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!
  2. 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.
  3. 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!
  4. 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.

Continue reading “Database optimization tips for Developers and SQL Server DBAs”

Deleting unused indexes from SQL server to free up space and improve performance

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.