SQL Server's Fascinating Stories

As huge a database grows, that tedious are regular DBA activities become. Besides, a warehouse database, can grow with no bounds to challenge.  There was a patching on one of our databases, SP3 for SQL Server 2008 R2, which was failing due to the error below. As the error says, MASTER the heart of SQL Server  “Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 2627, state 1, severity 14. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. Cannot recover the master database. SQL Server is unable to run. Restore master from a full ...

There are many times, I felt the need to capture those processes running and consuming CPU on an SQL Server. It comes to a boiling point when that dearth is elevated to make me feel doing a labor while the CPU utilization has peaked due to SQL Server. As a result, my first question to any SQL Server expert was the same, as to why there is no direct way to capture the detail. As a result, my learning lead me to WMI, a treasure for an admin in automation mind. (Trivia: Windows Task Manager works on the same WMI principle used in this code). This is a POLL based CPU Usage reporting, as there is no direct triggering mechanism provided by Windows by any other means to use them appropriately.  Below query can be put into an SQL Agent job, with a Powershell step, which can be called every 1m (Job completion takes ~53sec per my test). This job mails you the most CPU consuming processes on a server whenever CPU usage breaches 90%(Can be changed per your need). If yo...

Revisiting the blogger to finally start again blogging about the SQL Server after a long long time.  This post is on analyzing and digging into what is causing a job significant time to complete. This will be a basic level of approach and I wish, I could have done better. But, its was enough in my case to get to a conclusion, with the knowledge I equip.   Issue : A job that's scheduled to run every three hours, was running for more than 48hours. Average job run time, 1.5hrs.  Analysis : First and foremost thing to do, is to find out on what session id is the job running on the instance. You may use the below query to fetch the session id of the job from the job name. declare @Var1 varchar(36) select @Var1=substring(REPLACE(CAST(job_id AS VARCHAR(36)),'-',''),17,16) from sysjobs where name =' JOB_NAME ' select * from sys.sysprocesses where program_name like '%'+@Var1+'%' You may get a single row or multiple rows with ...

Following query can be used to search through all the job steps for any Keyword. USE [msdb] GO SELECT j.job_id, j.name, js.step_id, js.command, j.enabled FROM dbo.sysjobs j JOIN dbo.sysjobsteps js ON js.job_id = j.job_id WHERE js.command LIKE '%Keyword_Search%' GO

The below script can be used to search a keyword in any of the existing Stored Procedures or Functions on any database. SELECT SPECIFIC_NAME, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%KEYWORD_SEARCH%'

You might see the following error while a package was newly imported and is set to be called from an SQL Agent through a job. The job would fail reporting the below error. Source:        Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error This is mainly because, the package protection level, which is by default set to  "EncryptWithSensitiveKey" Resolution: Set the package protection level to "DontSaveSensitive". But, if in case you know the password for the key, you could use it. How to change the SSIS Package Protection: In Business Intelligence Development Studio, open the Integration Services project that contains the package. Open the package in the SSIS designer. If the Properties window d...

Please check the link for a list of Error States explained in detail for the Logon Errors 18456 on SQL Server. http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx These are for now, and will add to this if there are any more useful found.

The following script was made as a requirement to check if there are databases that are subscribing in an instance.  This can be executed on any database.  Create table #TempTable ( DB varchar(100), Is_Subscriber varchar(10)); EXECUTE sp_MSforeachdb 'USE [?]; if exists (SELECT name FROM sys.sysobjects WHERE name = ''MSreplication_subscriptions' ')         begin insert into #TempTable values (db_name(), ''YES''); END' select * from #temptable drop table  #TempTable

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a...

Sharing a folder through CMD script: This might possibly be a need while automating a share on a specific condition. Here in, our requirement was to start a share of the folder  for multiple users based on SQL Agent Start. This can be done by the following steps. The approach is to have a .bat file with the command that shares the folder. Later, the bat file can be called from SQL Agent Job Step. The command is: NET SHARE XXX$=E:\MyFolder /GRANT:SQLServer2005SQLAgentUser,FULL /GRANT:SQLServerSQLUser,FULL  /GRANT:"Database Administrators",FULL /GRANT:EVERYONE,READ For Help in Command Prompt: NET SHARE /help Note: Share resource names that end in a $ character do not appear when you browse the local computer from a remote computer. The .bat file can be created by pasting the command above in any text editor and the file can be saved by the extension .bat. The .bat file can be called from the agent job step as Choose the Job Step Type as OperatingSystem (Cmd...

Rule "Reporting Services Catalog Database File Existence" failed error: ------------ Solution: Check for the existence of Reporting Services database files in the default location or the location where the database files are set to go. Delete the database files. Then the retry would solve the problem.

This is quite a prevalant scenario for any DBA that would use begin tran to update a column as per requirement. If there isn't an instruction to the Storage Engine to either commit the transaction or roll it back, it would lead to a situation that would swamp the transaction log file which would slow down the system that would access the database. If that is the case, one can check for open transactions through the below command dbcc opentran This would show the process id if any transaction is open. Then the details related to open transaction can be fetched from one of the below system objects. exec sp_who or select * from sys.sysprocesses A column called sqlhandle can be useful to know the query being executed by the process. If the results produced through any updates are favourable, its better commit as soon as possible and if not roll it back.

·          I have SQL Server 2005 log shipping setup with primary/secondary configuration.  I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message: Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'.  The statement has been terminated. There is nothing special about the configuration.  Any ideas? ·          I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration.  There are some scenarios where this can happen and it causes the problem you reported in your first post.  We are working on correcting this in a future release.  As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constr...

Knowing the SPID of the current session we are using. select @@SPID To know the database name from the dbid select DB_Name (DBID) and viceversa, select db_ID (DBname) We can get the query being executed on any session using the below query. DECLARE @Handle varbinary(64); SELECT @Handle = sql_handle FROM sys.sysprocesses WHERE spid = 62; SELECT text FROM ::fn_get_sql(@Handle)

I recently had a quaint thinking, as to how to know the hostname of a machine given its IP. Its easy. Just give the following in command prompt. C:\> nbtstat -A xxx.xxx.xxx.xxx where xxx.xxx.xxx.xxx represents an IP address.

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.

Buffer Management of SQL Server: SQL Server uses buffer pages for any changes or requests for the data on the database. These buffer pages are 8KB pages on RAM that minimizes disc Input/Output. Any 8KB page can be buffered in memory, and set of all pages currently buffered is called as buffer cache. The amount of memory available to SQL Servers decides how many pages will be cached in memory. The buffer cache is managed by Buffer Manager. Either reading from or Writing to any page on the disc happens at two levels. The data that should be either read/Written is first copied to the buffer cache and the read/write operation is carried out on the data that is currently on the Buffer Cache. The edited data is updated on the disc by the buffer manager only if the in-memory cache has not been referenced for some time. While writing pages back to the disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/...

Hi People, check out this link. It discusses about searching the error log from the query editor which allows us to be more flexible in spotting out the exact error we need. http://www.mssqltips.com/tip.asp?tip=1476

The following query can be used to drop the locks on Database objects. It should be used carefully as the false killing of the processes would lead to disastrous issues which would come as chain of issues. Create Table #Tmp ( spid smallint, ecid smallint, status nchar(30), loginame nchar(128), hostname nchar(128), blk char(5), dbname nchar(128), cmd nchar(16) ) Create Table #TmpLocks ( spid smallint, dbid smallint, ObjId int, IndId smallint, Type nchar(4), Resource nchar(16), Mode nvarchar(8), Status nvarchar(28) ) Insert Into #Tmp Exec sp_who Insert Into #TmpLocks Exec sp_lock If(Select Count(*) From #Tmp T Join #TmpLocks TL On T.spid = TL.spid Where /*This is for tempdb*/ dbid = 2 And objid In (1, 2, 3)) > 0 Then you can kill the concerned spid with the command : Kill — The concerned spid Drop Table #Tmp Drop Table #TmpLocks

Its often to have issues on transaction log getting full with an error: The Transaction Log for the database ABC is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in the SYS.DATABASES System catalog view. If we execute the following query in the query editor, select log_reuse_wait_desc from sys.databases where name = 'ABC'; we get one of the follwing possibilities: Reuse of transaction log space is currently waiting on one of the following: 0 = Nothing Currently there are one or more reusable virtual log files. 1 = Checkpoint No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models). 2 = Log backup A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). Note: Log Backup do not prevent truncation 3 = Active backup or restore data backup or a restore is in progress (all recovery...