Database With Fun (While 1 = 1)

All processes that are currently running on the instance- SELECT * FROM sys.dm_exec_requests a OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b WHERE session_id > 50 and session_id <> @@spid If nothing is currently running on the server. Open sql profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output) RPC: Completed (Under stored procedures) SQL: BatchCompleted (Under TSQL) Profiling should help identify the bottleneck. You will need to look for rows which have a high cpu value. 1.Find disk delays for a particular database- select DB_NAME(database_id) DB_NAME, di.file_id,df.name,io_stall_read_ms ,num_of_reads ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms' ,io_stall_write_ms,num_of_writes ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms' ,io_stall_read_ms + io_stall_write_ms as io_stalls ,num_of_reads + ...

1. Conventional one-file backup- BACKUP DATABASE [hhhh]  TO  DISK =  N'D:\DBA-HIMALAYA-PC$SQL2008R2\AAA\hhhh.bak'  GO 2. let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files. BACKUP DATABASE [hhhh] TO DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak' GO RESTORE DATABASE [hhhh] FROM DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\A.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\B.bak', DISK = 'C:\DBA-HIMALAYA-PC$SQL2008R2\BBB\C.bak' GO It's a common practice to create an exact copy of the backup and store it to several places. There is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. T...

Example 1 – A query is slow because the response time is greater than 10 seconds and normally it’s 2 seconds Example 2  -  Client User requests are queing up and we notice CPU usage at 100% , whereas the normal level is 25% In Example 1, we’re using the response time  as the unit of measure for the task i.e query In Example 2 , CPU usage is the unit of measure for the client user requests Once the problem is quantified in this manner , break the quantities down into smaller parts. It may be session IDs (SPID), execution plan data etc. Each profile has it’s own data requirements. The key message is : attempt to gather performance data matching the problem Creating the profile at the correct level of detail requires relevant data. SQL Server offers different methods of collecting data e.g DMV, Traces , Custom queries.   It’s important to collect data at the right range. If analysing a problem at a user level , then collect data at the user level. ...

Your two main enemies are developers and SAN admins #youmightbeaDBA When your girlfriend has to lean around the laptop to kiss you goodnight #youmightbeadba You always plan an exit strategy, even when entering a McDonald's #youmightbeaDBA  You can't explain to your family what you really do for a living #youmightbeaDBA  You have at least one set of scripts you won't share #youmightbeaDBA  You and the Oracle DBA would happily fight off a developer together #youmightbeaDBA  You've sent a vendor suggestions on improving their database design or code (and been ignored) #youmightbeaDBA  You've sent a vendor suggestions on improving their database design or code (and been ignored) #youmightbeaDBA  You refer to clothes as "Data Abstractions" #youmightbeaDBA  Your girlfriend knows what "ETL" means #youmightbeaDBA  You check your server logs before you check your e-mail in the morning so you can reply "Yeah...

It is quite common to get the SQL Server version details from following query. SELECT @@VERSION VersionInfo GO Recently I have been using following SP to get version details as it also provides me few more information about the server where the SQL Server is installed. EXEC xp_msver GO But what good is this command if your sql server has expired and you can't even open management studio. For this situation, use the following option- When you click on the link below, SQL Server will be started to discover the SQL Server features. The Installation Center can be launched from the Start Menu, under the SQL Server 2008 entry: Click on the “Installed SQL Server features discovery report” Here is the report: The SQL Server Discovery Report is saved to %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<last Setup Session> Options: You can also generate the Discovery report through the command line. Run “Setup.exe /Ac...

In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc freeproccache” or “dbcc freesystemcache” will clear the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and cause a sudden, temporary excessive CPU usage and decrease in query performance. The following operations cause the plan cache flushing When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. ALTER DATABASE [dbName] SET ONLINE ALTER DATABASE [dbName] SET OFFLINE ALTER DATABASE [dbName] SET READ_ONLY ALTER DATABASE [dbName] SET READ_WRITE ALTER DATABASE [dbName] MODIFY NAME = [SomeDB_Name] ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 DEFAULT ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_WRITE ALTER DATABASE [dbName] MODIFY FILEGROUP Test1FG1 READ_ONLY ALTER DATABASE [dbName] COL...

USE [master] GO CREATE PROCEDURE [dbo].[SP_AutoFixOrphanUsers] AS SET NOCOUNT ON ---Declare Variables DECLARE @iDBSUCCEED           INT,             @iDBFAIL          INT,             @iAutoID        INT,             @iMaxAutoID     INT,             @iRetryAttempts TINYINT,         @cUserName      NVARCHAR(128) --Variable to hold current UserName to process       DECLARE @OrphanUsers TABLE         (AutoID     INT IDENTITY(1,1),          UserName    NVARCHAR(128)) -- Initialize Variables -- SET   @iDBSUCCEED = 0 SET   @iDBFAIL    = -100 SET @iAutoID    = 1 SET @iRetryAttempts = 0 BEGIN TRY     -- Add Orphan Us...

DECLARE @MinExecutions int; SET @MinExecutions = 5 SELECT A.total_worker_time AS TotalWorkerTime       ,A.total_logical_reads + A.total_logical_writes AS TotalLogicalIO       ,A.execution_count As ExeCnt       ,A.last_execution_time AS LastUsage       ,A.total_worker_time / A.execution_count as AvgCPUTimeMiS       ,(A.total_logical_reads + A.total_logical_writes) / A.execution_count        AS AvgLogicalIO       ,DB.name AS DatabaseName       ,SUBSTRING(B.text                 ,1 + A.statement_start_offset / 2                 ,(CASE WHEN A.statement_end_offset = -1                        THEN LEN(convert(nvarchar(max), B.text)) * 2                       ...

Launch the command line as administrator Run the following command net start MSSQL$INSTanceName /m Stop the SQL service InstanceName using Net Stop

In this Example we will configure SQL Server Agent to transmit an alert if an error of severity level 17 occurs and alert the operator Operator_admin when it happens ·         In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert. ·         In the New Alert dialog box, add a new alert with the properties in the following table. ·         Configure the alert as below : Name: Sql Server Resource alert Type: Sql server event alert Database Name: MyuserDB Severity: 017-Insufficient Resources Raise alert when Message Contains: Cleared ·         In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator ·         On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: An error occurred in MyuserDB database, and the...

set nocount on declare @spid varchar(10) declare @blkby varchar(10) declare @stmt varchar(1000)    create table #temp ( spid integer, status_1 varchar(100), login_1 varchar(50), hostname varchar(25), blkby varchar(10),                         dbname varchar(100), command varchar(100), cputime integer, diskio integer, lastbatch varchar(25),                         programname varchar(255), spid2 integer, Requestid varchar(10) ) insert into #temp exec sp_who2 declare curs cursor for select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%' open curs fetch next from curs into @spid, @blkby while @@fetch_status = 0 begin    set @stmt = 'dbcc inputbuffer(' + @blkby + ')'    raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait    exec (@stmt)    rai...

create table #temp (Databasename varchar(100),name varchar(1000),fileid int , filename varchar(500),filegroup varchar(100) ,                     size varchar(200),maxsize varchar(300),growth varchar(100),usage varchar(100)) declare @database varchar(100) Declare c cursor for select name  from master..sysdatabases where dbid not in (13,14) open c fetch next from c into @database while @@fetch_status=0 begin exec('use '+@database +' insert into #temp(name ,fileid , filename,filegroup ,                     size ,maxsize ,growth ,usage )  exec sp_helpfile') update #temp set Databasename=@database where Databasename is null fetch next from c into @database end close c deallocate c update #temp set size=replace(size,'KB','') select * from #temp select 'Sql Server',rtrim(convert(varchar(100),a.Filename))+'&'+left(replace(replace(b.filename,'d:\Program Files\M...

we will configure SQL Server Agent to transmit an alert if the number of connections is above 50 and alert the operator Operator_admin when it happens  In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert. In the New Alert dialog box, add a new alert with the properties in the following table. Configure the alert as below : Name: Sql Server Performance alert Type: Sql server Performance Condition alert Object: Sql server General Statistics Counter: User connections Alert if counter rises above: 50 In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: The number of user connections has exceeded 50 on the instance NamedInstance, and then click OK.

          In this example, we will create an alert in sql server to be notified when a CPU average workload is 90% within a minute In Object Explorer, expand SQL Server Agent, right-click Alerts, and then click New Alert. In the New Alert dialog box, add a new alert with the properties in the following table. Configure the alert as below : Name: SQL Performance CPU alert Type: WMI event Alert Namespace: \\.\ROOT\CIMV2 Query: SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA “Win32_Processor” AND TargetInstance.LoadPercentage > 90 In The Response Panel select the Notify operators check box, and then select the E-mail check box for the Operator_Admin operator On the Options page, select the E-mail check box, in the Additional notification message to send box, type Warning: The Average CPU workload has exceeded 90% on the instance NamedInstance, and then click OK

SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID) FROM SYS.INDEXES WHERE INDEX_ID = 0 AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 ORDER BY [TABLE]

<#-------------------------------------------------------------------------- .SYNOPSIS Script for  running T-SQL files in MS SQL Server Andy Mishechkin .DESCRIPTION runsql.ps1 has a next command prompt format: .\runsql.ps1 -server MSSQLServerInstance -dbname ExecContextDB -file MyTSQL.sql [-go] [-u SQLUser] [-p SQLPassword] Mandatory parameters: -server - name of Microsoft SQL Server instance -dbname - database name for  T-SQL execution context (use the '-dbname master' for  creation of new database) -file - name of .sql file, which contain T-SQL code for  execution Optional parameters: -go - parameter-switch, which must be, if  T-SQL code is contains 'GO'  statements. If you will use the -go switch for T-SQL script, which is not contains 'GO'-statements - this  script will not execute -u - the user name if  using Microsoft SQL Server authentication -p - the password  if  using Microsoft SQL Server authentication ...

SQL-DMO (Data Management Objects) is not the best choice, but has regardless been adopted in one of our little applications. Previously, it has always (to my knowledge) installed perfectly fine on many client machines. However, after having taken over the project (for some other improvements) I come to find myself having major deployment problems with the SQLDMO.dll file. The file is used in a setup Custom Action to allow the user to configure database details on install. Any attempts to register this file fail (via regsvr32). There are various answers to how to get it registered; I tried a few and none worked. LoadLibrary (snip) failed - The specified module could not be found. To cut a long story short, you need to install "Microsoft SQL Server 2005 Backward Compatibility Components" (which contains SQL-DMO): http://www.microsoft.com/en-us/download/details.aspx?id=15748 Unfortunately, there are no standalone redistributables just for SQL-DMO....

One day a DBA guy called and ask advise in very critical situation. Comunication of us as follows… Guy called “Hi Gitesh, I am in very critical condition.”“What is happening?” I asked “My database is down, Kindly help what to do” DBA guy told “Explain me situation” I asked more details to advise him ” System datafile is corrupt and giving error to perform media recovery” He explained. “Then what are you looking for? Just restore backup and enable media recovery” I told him. “But I didn’t take backup yesterday?” He murmuring. “Does your database is on archivelog mode?” I tried to solve his problem. “No it is running on noarchivelog mode” He answered. ” When you took last backup?” I asked. “One month back, after that backup gets failed due to space problem” He disclosed and asked again “Please give me advise to resolve and get rid of this critical problem” “Update your resume and start applying” I advised

SET NOCOUNT ON declare @db_list table( row_no smallint identity (1,1), db varchar(200)) INSERT into @db_list select name from master..sysdatabases declare @first smallint declare @last smallint declare @db varchar(200) declare @sql varchar(500) select @first = min(row_no) from @db_list select @last = max(row_no) from @db_list create table #db_file_list ( db_name varchar(100), Filename varchar(200), file_location varchar(500)) while @first <= @last BEGIN select @db = db from @db_list where row_no = @first SET @sql = 'INSERT INTO #db_file_list select '+ CHAR(39) + @db + CHAR(39)+' ,name,filename from '+ @db+'..sysfiles ' --print (@sql) exec (@sql) SET @first = @first + 1 END --select * from #db_file_list where file_location like '%N:%' select * from #db_file_list drop table #db_file_list SET NOCOUNT OFF

I created a monitoring server and one of the things I want to know if all the servers that I look after are up and running. So I created this proc to do just that. I even have a linked server that is an Oracle box and I pinged that one to ensure I can still get a connection to it. I wanted to only know when any of the linked servers where having issues so this is how this was created. I added parameters and logic to handle the situation where you want to know the the job was run and what it's findings were. I will proably add the the sql to write this out to a table so we can record system uptime over time and automatically calculate database availability % and report on that SLA .    To execute from a SSMS EXAMPLE USE 1  DECLARE @return_value int  EXEC @return_value = [dbo].[usp_lsping]  @nf = N'Y',  @wtn = N'E'  SELECT 'Return Value' = @return_value I set this up as a job to run at regular intervals ie dev/...

If you have multiple backup database backups on a single SQL Server 2008 R2 backup device, this script will help you restore a database from the LATEST full and differential backups residing on that backup device. If this script is to be used for restoring a SQL Server 2005 database, please remove "CompressedBackupSize" from the CREATE temporary table statement. /* Script to restore the LATEST full and differential backups from a SQL Server 2008 R2 backup device. If this script is to be used for restoring a SQL Server 2005 database, please remove "CompressedBackupSize" from the temporary table */ ---------------------------------------------------------------------------- --1. Create a temporary table for holding backup header information ---------------------------------------------------------------------------- IF OBJECT_ID('TempDB..#RestoreHeaderOnlyData') IS NOT NULL DROP TABLE #RestoreHeaderOnlyData GO CREATE TABLE #Res...