Database With Fun (While 1 = 1)

It profiler / trace data is already saved in a .trc file, then we can read the trace file using function name ::fn_trace_gettable() TSQL query to open a trace file in SQL Server Management Studio (SSMS) SELECT * FROM ::fn_trace_gettable ('<<SQL Server Trace File Name>>', default) this will display the data in result pane, so now if we want to save this in a SQL Server table then we can easily add a INTO clause in this statement and save data in SQL table as below. SELECT * into table_name_in_which_we_want_save_data FROM ::fn_trace_gettable ('<<SQL Server Trace File Name>>', default)

This was the issues that I faced roughly 2 months after I started my DBA career. When I was learning through the pages, about the basics, I was surrounded by this issue. Every now and again our live production sql server’s CPU’s would rise to 100%. There was no consistency in when it would happen, could be first thing in the morning, mid-afternoon or late evening. Instinct told me that CPU’s at 100% will be an issue with a big process that probably shouldn’t be running during peak times table scans or the likes, a query with possibly a lot of big table / index scans or hash joins. From task manager I could see that the biggest CPU hogger was sql server itself, my primary focus was to investigate the procedures currently running. I started simple with a sp_who2 and sp_lock. These commands themselves took up to 30seconds to return any output! When they did the number of processes running was about normal but the one thing that did stand out was the number of blocked processes...

Been into configuring this feature lately. Though this feature is as easy setting up as starting the management studio, there are still some issues related to it that might kill your time. Today i’m providing a list of Database Mail scripts I use when troubleshooting issues.  Firstly, a good place to start is to check the status of your Database Mail: 1. EXEC msdb.dbo.sysmail_help_status_sp it may be possible that it isn’t sending mail because it hasn’t been started  Following on from this, if you discover that Database Mail has in fact stopped then you can start it again with: 2. EXEC msdb.dbo.sysmail_start_sp NOTE: It can also be stopped by simply running 3. EXEC msdb.dbo.sysmail_stop_sp If Database Mail is running then it may be that Service Broker has not been enabled: 4. SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'; Another technique I use when troubleshooting Mail issues (if its running and the settings are cor...

This script will automatically reindex all indexes the tables in a selected database. When DBCC DBREINDEX is used to rebuild indexes, bear in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users. USE DatabaseName DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’ OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,’ ‘,90) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor the same can be done for all available schemas in database- USE DatabaseName DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT table_schema+'.'+table_name as table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' OPEN TableCursor FETCH NEXT FROM TableCursor INTO...

----------------------------------------------------------------------------------------- -- Comparing data in SQL Server Databases ----------------------------------------------------------------------------------------- -- 1. Data comparison itself. -- 1.1 Get information about unique keys in the tables. If there are unique keys then one of them -- (PK is a highest priority candidate for this role) will be used to specify rows with -- different data. -- 1.2 Get information about all data columns in the table and form predicates that will be -- used to compare data. -- 1.3 Compare data with the criteria: -- a. if some unique keys from the table from first database do not exist in second db (only -- for tables with a unique key) -- b. if some unique keys from the table from second database do not exist in first db (only -- for tables with a unique key) -- c. if there are rows with the same values of unique keys and different data in other -- columns (only for tables wi...

@name varchar(120), @type varchar(5) as begin declare @Sql varchar(500) set @Sql = 'select * from sysobjects where name like ''%' + @name +'%'' and type = '''+@type+''' order by name' print @sql Exec (@sql)

SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND (SC.Text LIKE '%UPDATE%' OR SC.Text LIKE '%INSERT%') ORDER BY SO.Name but a different variation can also be tried- select SPECIFIC_NAME from INFORMATION_SCHEMA.ROUTINES r where (r.ROUTINE_DEFINITION like '%UPDATE%' or r.ROUTINE_DEFINITION like '%INSERT%') order by SPECIFIC_NAME

I have a question regarding profiler, What exactly are reads? and what is the correlation between cpu, reads and duration? is that correlation direct? Events I typically start by tracing the SQL:BatchCompleted event and the RPC:CompletedEvent event. These two events should capture performance for all your SQL batches and stored procedures. The SQL:BatchCompleted event is generated whenever a batch of SQL statements is completed. If you run code in SQL Server Management Studio (or Query Analyzer) it will generate SQL:BatchCompleted events. If you create a .NET SqlCommand object and give it a CommandType of "Text" that will also generate SQL:BatchCompleted events. If your SqlCommand object has a CommandType of "StoredProcedure" that will generate RPC:Completed events. Data Columns I normally capture the following data columns for the two events above: 1. EventClass. This identifies the event. It must be selected. 2. TextData. This is the text of the SQL...

use sp_configure to configure the server instance to create compressed backups by default. USE AdventureWorks2012; GO EXEC sp_configure 'backup compression default', 1 ; RECONFIGURE WITH OVERRIDE ; GO By this, all further backups that you perform will be done with compression by default. Then there is a very good backup scheme that I've developed. Here it is- DECLARE @dbName varchar(100); DECLARE @backupPath varchar(100); DECLARE @backupQuery varchar(500); set @dbName = N'database_name' EXECUTE master.dbo.xp_create_subdir N'\\your\path\to\the \folder\database_name' set @backupPath = '\\your\path\to\the \folder\database_name\' set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak''' print @backupQuery EXEC (@backupQuery)

--Purpose: Find db_dbreader roles with permissions other than Select or Connect for each database --Recommendations: Run this in a non-production environment first, the script uses dynamic SQL and an --                 undocumented stored procedure EXEC sp_MSforeachdb @command1='USE [?] DECLARE @Roles varchar(200) SET @Roles = ''db_datareader, Init_Role, Viewer'' DECLARE @sqlcmd1 nvarchar(500) CREATE TABLE #temp_helprotect(Owner varchar(50), Object varchar(500), Grantee varchar(50), Grantor varchar(50), ProtectType varchar(50), Action varchar(50), RefColumn varchar(1000)); SET @sqlcmd1 = ''EXEC [?]..sp_helprotect''; INSERT INTO #temp_helprotect EXECUTE(@sqlcmd1); DECLARE @sqlcmd2 nvarchar(200); CREATE TABLE #temp_rolemember(DbRole varchar(50), MemberName varchar(100), MemberSID nvarchar(1000)); SET @sqlcmd2 = ''EXEC [?]..sp_helprolemember''; INSERT INTO #temp_rolemember EXECUTE(@sqlcmd2); SELECT DbRole, Mem...

DECLARE @SPID varchar(8000) DECLARE @Conntions_Killed smallint DECLARE @DBName varchar(100) SET @SPID = '' SET @Conntions_Killed = 0; SET @DBName = 'test' -- Pass the Database Name. SELECT @SPID=coalesce(@spid,',' )+'KILL '+convert(varchar, spid)+ '; ' FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName); Print @SPID; IF LEN(@SPID) > 0 BEGIN EXEC(@SPID); SELECT @Conntions_Killed = COUNT(1) FROM master..sysprocesses WHERE dbid=db_id(@DBName) END

Declare @DatabaseName Varchar(100) Set @DatabaseName='TEST_DB' -- Put the database name here ----- Show Data select 'BEFORE -->',spid, HOSTNAME, Loginame, DB_NAME(dbid) AS Database_Name, USER_NAME(uid) AS UserName, Status, [program_name] CMD from master.sys.sysprocesses Where dbid=DB_ID(@DatabaseName) order by Status,DB_NAME(dbid) desc,Hostname,spid,uid ------ Declare @SessionInfo Table (id int identity(1,1),Session_ID varchar(10)) Declare @count int Declare @max int declare @SqlString nvarchar(50) select @count=1,@max=0 --insert into @SessionInfo(Session_ID) select spid from master.sys.sysprocesses Where dbid=DB_ID(@DatabaseName) and Status<>'runnable' return select @max=MAX(id) from @SessionInfo while (@count<=@max) BEGIN     set @SqlString=''     select @SqlString='KILL '+Session_ID from @SessionInfo where id=@count     EXEC sp_executesql @SqlString Set @count=@count+1; END select 'AFTE...

It is a good practice to have all the scripts you usually use in one single container. The best place is a small database, DBAdmin, which could be deployed in every SQL server instances in the company. This is my effort to make a "standard" toolbox for sql server dba and share it with the community, in the hope that it will be more contributions from others. This script is for SQL2008 and SQL2008R2. For other versions modifications will be needed since the RESTORE HEADERONLY and FILELISTONLY have different outputs. EDIT: Changed from ALTER PROC to CREATE PROC. USE [DBAdmin] GO /****** Object: StoredProcedure [dbo].[admsp_RestoreDatabase]  ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /**************************************************** * Last Changed 2011-06-10 * * Description:     *    Restore database and log backup file from all backup files in a directory *    to the latest transaction backed up. *     * - Error-resilient against non-ba...

It is a DBA responsibility to verify that database backups can be successfully restored but many DBAs (the majority ?) fail to do that. It is understandable that in a enterprise environment with hundreds of databases, manually restoring every one every day or week is impossible. But from now on - the moment that you get this set of scripts, there is no excuse any more for not regularly restoring and dbcc checking your database backup files. You can of course just ignore it at your own risk. It is easy to set up and configure the verification station. For this you need a dedicated SQL server with highest SQL version in the enterprise. This version of script are for SQL Server 2008R2. The server should have enough disk space to restore the largest database. The service account of the database engine must have read access to all the backup files. I hope you have all the backups in a central network share, not in every SQL servers. Objects used: admsp_VerifyBackups: main procedure...