Kategoriarkiv: SQL Server – scripts
How to prevent lock escalation
There are several ways to reduce the lock footprint og expensive queries so that the lock escalation thresholds are not exceeded. Beneath I have described how I do it most of the times. Other ways can be read in this … Læs resten
How to move an object from one schema to another schema
If you have created a view|Procedure|Table in the wrong schema in SQL Server, you need to run this script. ALTER SCHEMA [NewSchemaName] TRANSFER [OLDSCHEMA].[ObjectName] If you have created more than one objects in the wrong schema, you can join the … Læs resten
How to retrieve the active queries from An SQL Server
If you are wondering what the SQL server is doing, try to fire this statement, it shows the following. – SPID – The system process ID. – Status – The status of the process (e.g., RUNNABLE, RUNNING, SLEEPING). – Login … Læs resten
How to extract files from SQL Server blob fields into files.
— Write all database files (pdf) to file. ——— ——— ——— ——— ——— ——— ——— DECLARE CURSOR_Id CURSOR FOR (SELECT [RecId] FROM [dbo].[SYSOUTGOINGEMAILDATA]) DECLARE @RecId BIGINT; OPEN CURSOR_Id FETCH NEXT FROM CURSOR_Id INTO @RecId WHILE (@@FETCH_STATUS -1) BEGIN DECLARE @Data … Læs resten
Search for a text inside all the stored procedures in a database.
SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id WHERE m.definition Like ‘%%’;
Create statistics
— Create the statistics for all tables and columns in the database that don’t — already exist. This is much easier than using CREATE STATISTICS on each — table, as you can only do 16 columns at a time, and … Læs resten
Finding missing indexes in SQL Server 2012
This is a nice query using sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats and sys.dm_db_missing_index_groups to find out which indexes is missing. SELECT CAST(SERVERPROPERTY(‘ServerName’) AS [nvarchar](256)) AS [SQLServer], db.[database_id] AS [DatabaseID], db.[name] AS [DatabaseName], id.[object_id] AS [ObjectID], id.[statement] AS [FullyQualifiedObjectName], id.[equality_columns] AS [EqualityColumns], id.[inequality_columns] AS … Læs resten
Rebuild indexes ONLINE = OFF, and update statistics.
Do you have a maintenanceplan that rebuilds the indexes during the weekend, but monday morning all clients complaints that the system is saying “Not responding”. When you look at the database you can see that the Rebuild indexes hasn’t finished … Læs resten
How to create a temporary table in SQL Server
When you are creating SQL scripts you will sometimes need to create a temporary file. This can be done in 2 different ways in SQL Server. — The scope of this temporary table is only the SPID that created the … Læs resten
Track down escalation locks
— You can use sys.dm_db_index_operational_stats to — track how many attempts were made to escalate — to table locks (index_lock_promotion_attempt_count), — as well as how many times escalations actually — succeeded (index_lock_promotion_count). — The following query shows the top 5 … Læs resten