SQLGear

If your Azure database is experiencing deadlocks or below error Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction then you can see them by executing following query in the master database:

I often see developers assigning values to variables from the same table but different rows based on the row ID. The values are mostly from the same column. What I see is the table is queries multiple times and could impact performance. Below is the sample query and its execution plan. DECLARE @Method1 nvarchar(50); DECLARE @Method2 nvarchar(50); SET @Method1=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=1); SET @Method2=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=2); Instead, we can do it as in below query: This can also be used while fetching values from an XML column or variable based on a node.

Mostly we write dynamic queries when we try to write the stored procedure which filters data from the column selected by the user or when the user decides on which column to sort the data or when criteria to filter data are multiple as like or equals or does not equal. Below query will give an idea on how to achieve this without writing a dynamic query.

To know how many transactions are occuring on a Server instance following is the script: Make sure you run them multiple times as each time the result will be different based on the load running during the time you capture it.

A formatted T-SQL code makes it easier to understand and debug, especially when it is too lengthy. There are many T-SQL code related plugins available for SQL Server management studio. One of the free tool is Notepad++ . It is free you can download it and then add "Poor Man's T-SQL Formatter". From Plugins menu inside Notepad++ add "Poor Man's T-SQL Formatter" from Plugin Manager as shown below:   Now to format the code select the options as below: Your formatted code will now look as below:

Following is the query to list all the orphan users in a SQL Server database:

When ever you are renaming a database object you want to make sure you have changed all the code referencing that object. Following is the T-SQL to find a phrase of word in all the database object codes: DECLARE @Text AS NVARCHAR(50) SET @Text = 'DBObjectName' /*Set the variable with the DB Object name, Linked Server name, Database name or just a word string to be searched*/ SELECT DISTINCT sysobjects.NAME ,sysobjects.xtype ,SUBSTRING(syscomments.TEXT, CHARINDEX(@Text, syscomments.TEXT) - ( CASE WHEN CHARINDEX(@Text, syscomments.TEXT) It will give you the name of the object referencing it. Type of the object and also extract of the code around the name of the database object for your reference.

Credits unicellular If you are taking backup of a database through wizard or through t-sql script you can get the progress or percent of process completed. This helps when you wait for a backup job to finish before doing your changes to the database. Mostly you plan your changes in prod just after the backup job finishes, and this is obvious option if your database is too big. But the backup job for a database executed by a schedule does not show the progress of it. At this moment you wished how much more you have to wait. Well sys.dm_exec_requests is the DMV for that if you know the session id of the job. SELECT percent_complete FROM sys.dm_exec_requests WHERE session_id = 64 Following script will give you the minutes remaining for the process to be completed. SELECT percent_complete,DateDiff(mi, start_time, GETDATE()) As MinutesTillNow, ((100-percent_complete)*(DateDiff(mi, start_time, GETDATE())))/percent_complete As [MinutesRemaining] FROM sys.dm_exec_requests WHERE session...

Recently one of the developers sent me following error: Message: SQL Error [Microsoft OLE DB Provider for SQL Server: There is insufficient memory available in the buffer pool. SQL State: 42000    Native Error: 802 State: 20     Severity: 17 SQL Server Message: There is insufficient memory available in the buffer pool. I checked the memory usage by components under Memory Consumption server report. Following is the screenshot of the report: I found that CACHESTORE_SQLCP was allocated most memory. This type of memory clerk indicates memory consumption by plans of SQL statements or batches not found in any stored procedures, triggers or functions. I just wanted to clear pool for CACHESTORE_SQLCP. The name for this memory clerk is "SQL Plans". Which I found by querying sys.dm_os_memory_clerks dynamic management view. I then executed the following command to clear the pool for CACHESTORE_SQLCP. DBCC FREESYSTEMCACHE('SQL Plans') The memory a...

Sometimes you may need to find number of rows in each (or few) tables. Following query will determine the number of rows in each partition of each table in each schema.

You might have got requirements to script out the database object and give it to developers. Often developers need codes from current production environment. Here is how to generate scripts from database for all or selected database objects: Right click the database and select Tasks and under that select Generate Scripts...    Select next if the welcome screen appears and then in the next scree select the database for which you want to generate the scripts and press Next. In the Choose Script Option widow select how you wish to script. Do you wish to include a drop statement before or include IF NOT EXISTS. Select your options and press Next. In Choose Object Type window select the object types you want to script out and press Next. For the example I have selected Stored procedures below. In the next few (Based on numbers of object types you have selected) windows select the objects you want to script out. Below is the screen shot and I have hide the names ...

Following is the T-SQL script to monitor the transactional log file. It specifies the size of log file and how much is being used out of it and also calculates the percentage of use. Also gives information on log file growht and size limit and mentions what it is waiting to reuse the log file. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- DECLARE @LogFilePercentUsed INT SET @LogFilePercentUsed = 60 --//Modify the value here to set the threshold SELECT db.NAME ,db.log_reuse_wait_desc ,ls.cntr_value / 1024 AS SizeMB ,lu.cntr_value / 1024 AS UsedMB ,(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)) * 100 AS UsedPercent ,@LogFilePercentUsed AS Threshold ,CASE WHEN (CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)) * 100 > @LogFilePercentUsed THEN CASE WHEN db.NAME = 'tempdb' AND log_reuse_wait_desc NOT IN ( 'CHECKPOINT' ...

Making sure the databases are being backed up is the first check every DBA needs to do. Following is the script to check the the last date of the database backup on current server or list out the databases which were not not backed up within last X number of days. It will also include databases which were never backed up on current server. It works with SQL Server version 2005 and above. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- /* Finding last backup date for all the databases OR find the databases not backed up within last X number of days. */ DECLARE @Days AS INT SET @Days = 0 --//If Set to 0 it will show you the last backup dates for the databases SELECT 'Database ' + d.NAME + ' was ' + CASE MAX(ISNULL(b.backup_finish_date, '1900-01-01 00:00:00.000')) WHEN '1900-01-01 00:00:00.000' THEN ' never backedup.' ELSE 'last backed up on ...

When you are migrating the all the databases on a server to another then you might have used sp_help_revlogin to script out the logins. That was easy! But if your SQL instance from where you are moving the databases is holding databases for multiple application and application owner do not agree on migrating databases on same date. Or say you want to move different databases to different target servers. In this case you need to script out logins for particular database only. You can use following query, after setting the results to text, to get login creation script for logins associated with perticular database only. Step 1. Setting the results to text: Step 2. Execute the following query in the database for which logins are to be scripted: USE DatabaseName; GO SELECT 'EXEC [dbo].[sp_help_revlogin] @login_name = [' + l.NAME + ']' FROM sys.sysusers u INNER JOIN sys.syslogins l ON l.sid = u.sid You will get the output as follows: Step 3. Copy the outpu...

One of our developers raised an issue mentioning that he tried to connect to the database but and he seemed database was corrupt\disabled. He mentioned that the day before they did some work on that database which made the database grow considerably. Following is the message he faced: Database 'abc' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945) When I connected to the SQL server instance I found that I was not able to expand the database abc and when I checked the shutdown property of database I found that it was shutdown. Following is the screenshot. I executed following commands to make the database offline. Make sure you do this from the server itself and not from any client machine. USE master; GO ALTER DATABASE abc SET offline; GO Then executed the following command to make the database online again. USE master; GO ALTER DATABASE abc SET online; GO This b...

Credits: David Fulmer Getting access to a production database should have a process where security team should also involved. But someone might give access to a user bypassing the process. If this sort of unauthorized users are found having access to the database during your audit reports you surly want to know about who and when. If a user was created on the database wrongly, and you want to know who created it then following script will help: It will give you HostName from where "Create User ..." was executed and LoginName of the user who executed it and Time when the user was created. USE [DatabaseName]; GO DECLARE @trace_id INT ,@filename NVARCHAR(4000); SELECT @trace_id = id FROM sys.traces WHERE is_default = 1; SELECT @filename = CONVERT(NVARCHAR(4000), value) FROM sys.fn_trace_getinfo(@trace_id) WHERE property = 2; SELECT HostName ,LoginName ,StartTime FROM sys.fn_trace_gettable(@filename, DEFAULT) WHERE EventClass = 109 AND DatabaseName = N'Databas...

Credits: Jared Tarbell Cursors could be resource consuming. If there is a way to avoid cursor I would go by that first. Following is the simple query showing how to avoid cursors. This query backs up all the user databases to a specified location. It loops to every database in the sysdatabases table. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- DECLARE @name VARCHAR(50) --// database name DECLARE @path VARCHAR(256) --// path for backup files DECLARE @fileName VARCHAR(256) --// filename for backup DECLARE @fileDate VARCHAR(20) --// used for file name DECLARE @Min INT ,@Max INT ,@SQL NVARCHAR(MAX) SET @path = 'D:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) SELECT @Min = MIN(dbid) ,@Max = MAX(dbid) FROM MASTER.dbo.sysdatabases WHERE NAME NOT IN ( 'master' ,'model' ,'msdb' ,'tempdb' ) WHILE @Max >= @M...

For you SQL Server inventory you need to have number of CPUs on the host. You can do it by running MSINFO32.exe through the command prompt which will give you information as below: From this you have to read the required information and save it where required. If you want to use a T-SQL query to get the CPU count information then following is the query which uses sys.dm_os_sys_info DMV to count logical and physical CPUs on the SQL Server host: SELECT cpu_count AS LOGICAL,cpu_count / hyperthread_ratio AS PYSICAL FROM sys.dm_os_sys_info It will give you output as follows:

Credits CarbonNYC Ever wanted to set order on multiple triggers. You can always do so. By default they will be executed in undefined order. If you want to prioritize them then you can only do so for the first and the last trigger through the stored procedure sp_settriggerorder. It lets you set the order in which a trigger will execute. So if you have to executed multiple triggers in a predefined order then you can have max three triggers. Set order for first and last through sp_settriggerorder and the remaining one will be executed in the middle. If you have more than three then in that case the triggers except the first and last will be executed in random order. Just make sure that the same trigger name is not use to set both first and last.

Credits Greyhood Ever wanted to check if a database user or custom database role belongs to what other database roles or has what access to the database. Following is the query: DECLARE @Rolename CHAR(15) SET @Rolename = 'Custom DB Role' --//Set the variable value in above line to the database role or user for which membership needs to be checked SELECT @@ServerName [Server] ,DB_NAME() AS [Database] ,@Rolename [DBRole/DBRole] ,CASE IS_ROLEMEMBER(NAME, @Rolename) WHEN 1 THEN 'Is member of ' + NAME WHEN 0 THEN 'Is not member of ' + NAME END AS [Membership] FROM sysUsers WHERE NAME @Rolename AND issqlrole = 1 AND IS_ROLEMEMBER(NAME, @Rolename) = 1 --//comment the above line if you also want to see what the database role (or user) is not member of