tag:blogger.com,1999:blog-44589650292920173182024-02-18T21:26:43.780-08:00Manvendra's blogManvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.comBlogger33125tag:blogger.com,1999:blog-4458965029292017318.post-4251186648033962462019-10-03T04:55:00.002-07:002019-10-03T04:58:42.161-07:00<div dir="ltr" style="text-align: left;" trbidi="on">
<h1>
Resolve SQL Server Database Stuck in Suspect Mode Problem Easily </h1>
<b>Summary:</b> Many times SQL Database administrators encounters SQL server database stuck in suspect mode issue. Due to inaccessibility of the database objects, all the DBA’s try to bring the database online as soon as possible because. So this problem tackling blog will discuss the steps by step process to resolve SQL Database Marked as Suspect issue. <br />
<br />
Suspect Mode in SQL Server database means the recovery process has started but not finished successfully. The user will not be able to connect to the database nor the user can recover it. All the database objects will be inaccessible. This issue will clearly reflect the risk of data loss.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU2HCE8Iq_afAiuTsrUbA-UoNeELJA56OH-QMdrNbxLyzE0eVw_JKZcSclw6PwEYEWNX07cePNXIJznz8OkOUa_QZFJ5BScWokNppz33uygfWcSN0T8euw2Nt1XhWbC3jYUi7aj4oE0Ww/s1600/suspect-db-mode-recovery.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="233" data-original-width="244" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU2HCE8Iq_afAiuTsrUbA-UoNeELJA56OH-QMdrNbxLyzE0eVw_JKZcSclw6PwEYEWNX07cePNXIJznz8OkOUa_QZFJ5BScWokNppz33uygfWcSN0T8euw2Nt1XhWbC3jYUi7aj4oE0Ww/s1600/suspect-db-mode-recovery.png" /></a></div>
<br />
<br />
<i>“Urgent Please! When I woke up in the morning and found my company SQL database is marked as suspect. I do not have the backup and I want to bring the database online as soon as possible. Please help me to resolve this problem.”</i><br />
<br />
<h2>
Why SQL Server Database Stuck in Suspect Mode? Know the Reasons</h2>
Before Proceeding to solution part we have to discuss various causes for this problem. Here are the reason responsible for SQL Database Marked as Suspect mode.<br />
<ol>
<li>Improper or forcefully shutdown of the SQL Server.</li>
<li>In case if the system failed to open the device where the data or the log file resides.</li>
<li>Due to lack of disk space may be the reason for this problem.</li>
<li>In case of SQL Server crash issue, or if the database files are used by the third party backup software.</li>
<li>If SQL database cannot complete roll forward or rollback operation. </li>
</ol>
<div>
<b>Note: </b>The user can also read the another post to <b><a href="https://www.systoolsgroup.com/how-to/restore-sql-server-database-from-mdf-file/" target="_blank">Restore SQL Server database</a></b> from MDF file. </div>
<h2>
Solve SQL Server Database Stuck in Suspect Mode Problem Manually</h2>
<b>Follow the steps below to resolve this problem manually</b><br />
<b><br /></b>
1. First the user has to Switch the database to Emergency Mode. For this Launch the SQL Server Management studio and then connect to your database. After that select the New Query option.<br />
<br />
Execute This Command <br />
<b><br /></b>
<b>EXEC sp_resetstatus 'your database name';</b><br />
<b> ALTER DATABASE db_name SET EMERGENCY
</b><br />
<br />
This will turn of the suspect flag and switch off to Emergency Mode.<br />
<br />
2. Now Perform the Consistency check<br />
<b>DBCC CHECKDB (‘Your databasename’)</b><br />
<br />
<br />
3. Bring the database into Single User Mode<br />
<b>ALTER DATABASE your database name SET SINGLE_USER WITH ROLLBACK IMMEDIATE</b><br />
<b><br /></b>
4. Backup your database because the next command can cause data loss situation.<br />
<br />
5. Now Execute the following command<br />
<b>DBCC CHECKDB ('Your database name', REPAIR_ALLOW_DATA_LOSS)
</b><br />
<br />
6. After this Change the database into Multi User Mode.<br />
<b>ALTER DATABASE database_name SET MULTI_USER</b><br />
<br />
Finally the user to refresh the SQL serve database and check the connectivity issues.<br />
<br />
<b>Note: </b>The user can try the above steps to resolve SQL server database stuck in suspect mode. But in this problem there is a high risk of data loss. So in that case the user can try SQL database recovery tool to resolve this issue.
SQL database Recovery Tool is capable to repair the database from suspect mode. The user can easily<b> <a href="https://www.systoolsgroup.com/sql-recovery.html" target="_blank">Recover SQL Database</a></b><a href="https://www.systoolsgroup.com/sql-recovery.html" target="_blank"> </a>objects such as SQL tables, stored procedure, functions, triggers, indexes. Also this application shows the preview of deleted SQL table records in red color.
<br />
<h3>
Final Verdict </h3>
In this article, we have discussed the problem SQL server database stuck in suspect mode. We have provided you the reason for this problem. The user can try the above methods to resolve this issue. But in case if you are facing any problem while performing the manual methods. Then I suggest you take the help of SQL database recovery tool to resolve this issue.<br />
<br /></div>
Andrew Jacksonhttp://www.blogger.com/profile/13681691598848401466noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-79312299815166215182016-10-13T02:52:00.000-07:002016-10-13T02:52:13.062-07:00Decrypt SQL Server Stored Procedure via Different Methods<p>SQL Server user makes the frequent use of stored procedures in the database in order to maintain the integrity and consistency of the data. Simply, a block of SQL query is embedded into the stored procedure, which can be called at any time in the program. Since, these procedures are associated in the encrypted form with the database application, one finds it quite difficult to view the code behind a particular stored procedure. Only the permitted users are allowed to view the source code behind the stored procedure. More importantly, the developers who wish to update the already included stored procedures in an application, finds it much necessary to decrypt encrypted SQL server stored procedure so that they can easily work on the code and may update the behavior of the procedure. Keeping in mind the need of decryption, we have attempted to provides way to decrypt SQL Server stored procedure using different methods.</p>
<h2>Manual Approach to Decrypt SQL Server Stored Procedure</h2>
<p>Any stored procedure can be decrypted by using the DAC(Dedicated Administrator Connection) with SQL Server.</p>
<p><u>To connect to DAC:</u> The DAC can be connected to SQL Server Management Studio by prefixing <strong>ADMIN</strong> to the server name in query editor.</p>
<p>Once a connection is established with DAC, the decryption can be performed using the following steps:</p>
<ol>
<li>Using DAC connection, fetch the encrypted values of the stored procedure</li>
<li>Obtain the encrypted value for the blank procedure having ‘-’ character in its definition</li>
<li>Obtain the plain text blank procedure statement in unencrypted form</li>
<li>Now, XOR the results of step 2 and step 3 to get the final decrypted value of the procedure</li>
</ol>
<p>Consider the following script that allows to decrypt encrypted stored procedure named ‘dbo.MyDecryption’ using DAC. The scripts needs to be executed carefully, otherwise if any of the parameter is missing, it will generate an error. </p>
<p>SET NOCOUNT ON <br />
GO<br />
<br />
ALTER PROCEDURE dbo.MyDecryption WITH ENCRYPTION AS <br />
BEGIN <br />
PRINT 'This is the decrypted text preview..' <br />
END <br />
GO <br />
<br />
DECLARE @encrypted NVARCHAR(MAX) <br />
SET @encrypted =( <br />
SELECT imageval <br />
FROM sys.sysobjvalues <br />
WHERE OBJECT_NAME(objid) = 'MyDecryption’) <br />
DECLARE @encryptedLength INT <br />
SET @encryptedLength = DATALENGTH(@encrypted) / 2 <br />
<br />
DECLARE @procedureHeader NVARCHAR(MAX) <br />
SET @procedureHeader = N'ALTER PROCEDURE dbo.MyDecryption WITH ENCRYPTION AS ' <br />
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader))) <br />
EXEC sp_executesql @procedureHeader <br />
DECLARE @blankEncrypted NVARCHAR(MAX) <br />
SET @blankEncrypted = ( <br />
SELECT imageval <br />
FROM sys.sysobjvalues <br />
WHERE OBJECT_NAME(objid) = 'MyDecryption’) <br />
<br />
SET @procedureHeader = N'CREATE PROCEDURE dbo.MysDecryption WITH ENCRYPTION AS ' <br />
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader))) <br />
<br />
DECLARE @cnt SMALLINT <br />
DECLARE @decryptedChar NCHAR(1) <br />
DECLARE @decryptedMessage NVARCHAR(MAX) <br />
SET @decryptedMessage = '' <br />
SET @cnt = 1 <br />
WHILE @cnt <> @encryptedLength <br />
BEGIN <br />
SET @decryptedChar = <br />
NCHAR( <br />
UNICODE(SUBSTRING( <br />
@encrypted, @cnt, 1)) ^ <br />
UNICODE(SUBSTRING( <br />
@procedureHeader, @cnt, 1)) ^ <br />
UNICODE(SUBSTRING( <br />
@blankEncrypted, @cnt, 1)) <br />
) <br />
SET @decryptedMessage = @decryptedMessage + @decryptedChar <br />
SET @cnt = @cnt + 1 <br />
END <br />
SELECT @decryptedMessage</code></p>
<h2>Decrypt Encrypted SQL Server Stored Procedure using Third Party Tool</h2>
<p>Various third party tools for decrypting SQL Server database objects are available in the industry, which cater the demand to easily decrypt the encrypted database objects. One such efficient tool is <strong>SQL Decryptor</strong>. The tool carries out decryption in the following easy steps:</p>
<ol>
<li>Enter all database credentials such as server name, login mode, user credentials(name & password) to connect to server</li>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-wGsGhNOJMNEbT3-jn4mjxPOGaivpZI3nJU5kp4LSslyAtN5n0D9chEjTujvmVs4yJGQZXp8DU6O_-XRDX3TWu_q2ehhQBlxDHyDtogCq2Urxrq-OxZKgynKNOWXxEitthadl5g2bB1M/s1600/decryptor-1.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-wGsGhNOJMNEbT3-jn4mjxPOGaivpZI3nJU5kp4LSslyAtN5n0D9chEjTujvmVs4yJGQZXp8DU6O_-XRDX3TWu_q2ehhQBlxDHyDtogCq2Urxrq-OxZKgynKNOWXxEitthadl5g2bB1M/s400/decryptor-1.png" width="400" height="299" /></a>
<li>On the preview page, choose the required stored procedure and preview the decrypted script in the text box.</li>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSHXdoYuOdz-Oy1Kniqhn4F0HeqCjGA7JhgJRD_npr2fpBWWmsGOV-qwADkiVRxWOvpJzoCcFnR3yq4_9ZtG6atxW2OxxqwMoWxkrsmIuMDU62lxTtNq4X9o-bdnpkVqyvAOFCnn2GO1o/s1600/decryptor-2.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSHXdoYuOdz-Oy1Kniqhn4F0HeqCjGA7JhgJRD_npr2fpBWWmsGOV-qwADkiVRxWOvpJzoCcFnR3yq4_9ZtG6atxW2OxxqwMoWxkrsmIuMDU62lxTtNq4X9o-bdnpkVqyvAOFCnn2GO1o/s400/decryptor-2.png" width="400" height="300" /></a>
<li>Under the export options, choose to export the decrypted file to SQL Server directly or export as SQL Compatible Script.</li>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwhGQCB4WsK7XnAovIjvlxWuCL9HBd8jnPkLgPueVL1AnXYGQAN_G0FWlw40FYwqzUfvKDeihyphenhyphenUS7zkOKR-RKxIaCtECJW7X8LQUSrhDGsMvkUkbCSYhTqJeTIonFhwD5OA7oa5qsXyGA/s1600/decryptor-3.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwhGQCB4WsK7XnAovIjvlxWuCL9HBd8jnPkLgPueVL1AnXYGQAN_G0FWlw40FYwqzUfvKDeihyphenhyphenUS7zkOKR-RKxIaCtECJW7X8LQUSrhDGsMvkUkbCSYhTqJeTIonFhwD5OA7oa5qsXyGA/s400/decryptor-3.png" width="400" height="301" /></a>
</ol>
<p>So, the users can make the use of automated decryption tool to decrypt the stored procedures without running any complicated queries.</p>
<h3>Conclusion</h3>
<p>Considering user’s requirement to decrypt various database objects like the stored procedures, we have tried to explain the method to decrypt encrypted SQL Server stored procedure using manual methods and third party software. Users are advised to run the SQL script carefully to completely decrypt the stored procedure. However, to get rid of the complicated queries the user can also go for the third party <a href="http://www.sqldecryptor.repair-sql.net/" target="_blank">SQL Decryptor software</a>. It can efficiently decrypt the database objects and the decrypted script can also be exported using the tool.</p>Andrew Jacksonhttp://www.blogger.com/profile/13681691598848401466noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-77577055689648101392016-07-20T06:14:00.002-07:002016-07-20T06:16:21.033-07:00Resolve Error “SQL Database is in Use” Using Manual Steps<h2>Problem while Working On Database:</h2>
<p>While working on SQL Server users perform various actions such as Rename, Detach, Restore, and Drop on SQL Server. Due to which an error occurs “SQL Database is in use” with different error numbers while performing such actions on SQL database.</p>
<h2>How to Tackle this Problem ? </h2>
<p>Some scenarios are discussed below, which helps users to resolve the occurrence of an error while executing various actions on SQL database. </p>
<h3>Scenario 1: While Restoring the Database</h3>
<p>When the user tries to restore the database and other users are accessing the same database at that time. Then, they face SQL error message 3101, which is due to SQL database is in use. There are some solutions, which helps users to remove the error occurrence.</p>
<p><b>Exclusive Access</b></p>
<p>User can get the exclusive access by dropping all the other connections to restore the database. There is another way to check the connections usage of the database, which user needs to restore, i.e. <b>sp_who2</b> and <b>SSMS</b>.</p>
<p><b>Using KILL Command</b></p>
<p>After getting exclusive access, user can use KILL command that helps to kill every connection that is using the database. Before this, user must be aware that which connections are killed as the rollback issue may be required. </p>
<p><b>ALTER DATABASE</b></p>
<p>There is another option, in which user can put the database in single user mode after that they can restore. It also rollback depends on the option user use, but it will all do connections at once. </p>
<h3>Scenario 2: Renaming the Database</h3>
<p>While <a href="http://www.sqlserverlogexplorer.com/how-to-rename-a-database/" target="_blank">renaming the database</a> user receives SQL error 5030. This error occurs when the database is in Multi User mode where various users are utilizing the database at same time. To resolve this issue, user first need to set the database to single user mode and then rename the database and after that set it to Multi user mode again. User can follow the steps given below to perform the process.</p>
<ul>
<li>Firstly set the database to single user mode,</li>
ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<li>Now rename the database</li>
ALTER database AdventureWorks MODIFY NAME = NewAdventureWorks
<li>Set database to Multiuser mode</li>
ALTER DATABASE AdventureWorks
SET MULTI_USER WITH ROLLBACK IMMEDIATE</ul>
<h3>Scenario 3: While Dropping the Database</h3>
<p>When the user tries to drop a database when users are connected to SQL Server database, then they will receive SQL error message 3702. User can follow the mentioned steps to remove the error occurrence by getting the exclusive excess to drop database in Server.</p>
<ol>
<li>Connect to SSMS and expand the database node.</li>
<li>Right click on the databases that are required to drop.</li>
<li>Choose the delete option from drop down list to view the delete object.</li>
<li>Now choose the checkbox “close existing connections” to drop existing connections before dropping the SQL Server database.</li>
<li>Select option “Delete backup and restore history information for database,” user will be able to remove the database backup and restore history that is stored in database.</li>
</ol>
<p><b>Using TSQL Query</b></p>
<p>User can execute the TSQL code to drop the database in SQL Server by using TSQL Query as mentioned below:</p>
<pre><code>EXEC msdb.dbo.sp_delete_database_bakuphistory @database_name = N‘AdventureWorks’
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Drop DATABASE [AdventureWorks]
GO</code>
</pre>
<p><b>Scenario 4: Detaching the Database</b></p>
<p>When the users detach the database, they face SQL Server error 3703. This error is due to the reason that multi users are using the same database at the time. To resolve this, user can kill the respective accessing DB processes; sometimes it does not work. For this, user needs to force DB delete/detach by ending the existing connections by leaving the DB in single user mode.</p>
<pre>
Select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d name like ‘%mydb%’
go
kill 53
go</pre>
<p>Users need to fix the DB offline with ‘SET OFFLINE WITH ROLLBACK IMMEDIATE’ setting so that the DB will be turn offline immediately and after that detach DB will work correctly. When the user needs to attach the DB they can make the DB online.</p>
<pre><code>ALTER DATABASE <AdventureWorks> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Database Name] SET ONLINE</code></pre>
<p><b>Conclusion</b></p>
<p>With the help of the discussed solution, user can easily resolve an error “SQL Database is in Use” to perform various actions such as restore, rename, detach, and drop on the SQL server database.</p>Andrew Jacksonhttp://www.blogger.com/profile/13681691598848401466noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-85719082073147718402013-07-11T12:13:00.002-07:002013-07-11T12:13:44.427-07:00Property Owner is not available for Database SSMS error<div dir="ltr" style="text-align: left;" trbidi="on">
When you try to launch the database mirroring GUI or some other database property window in SSMS you get this error:<br />
<br />
<b><i>
</i><div class="codediv">
<i>Cannot show requested dialog.</i><br /><br /><i>Additional information:</i><br /><i> Cannot show requested dialog.(SqlMgmt)</i><br /><i> Property Owner is not available for Database'[XXXX]'. This property may not exist for this </i><br /><i> object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)</i></div>
<div class="codediv">
</div>
</b>
I saw this issue during our DR (Disaster Recovery) exercise. We were
using the SSMS GUI and we successfully failed over one database from the
principle server to the mirror server, but when we did a failback via
the GUI we got this error.Visit below link to troubleshoot this issue:<br />
<br />
<h2 style="text-align: left;">
<b><a href="http://www.mssqltips.com/sqlservertip/2477/property-owner-is-not-available-for-database-ssms-error/" target="_blank">Property Owner is not available for Database SSMS error</a></b></h2>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Visit my author profile to read my other articles: <b><a href="http://www.mssqltips.com/sqlserverauthor/67/manvendra-singh/" target="_blank">Manvendra Deo Singh</a></b></div>
</div>
Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com1tag:blogger.com,1999:blog-4458965029292017318.post-7781158613190983902013-07-11T12:08:00.000-07:002013-07-11T12:08:09.921-07:00SQL Server 2008 R2 Upgrade Failure due to Security Group upgrade rule<div dir="ltr" style="text-align: left;" trbidi="on">
Last month I was working on a SQL Server 2005 to SQL Server 2008 R2 <a href="http://www.mssqltips.com/sql-server-tip-category/24/upgrades-and-migrations/">upgrade</a>.
Unfortunately, we were not able to complete our upgrade because of a
failing a upgrade rule named "Security Group SID (Security
Identifier)". The SQL Server Upgrade Setup Wizard did not enable the
'Next' button in order to proceed with the upgrade until this failure
was resolve. In this tip, I will describe the reason for this failure
and how I fixed the issue in order to have a successful upgrade. Read my article published on <a href="http://mssqltips.com/" target="_blank">MSSQL Tips</a> through below link:-<br />
<br />
<h2 style="text-align: left;">
<span style="font-family: inherit;"><span style="font-size: large;"><a href="http://www.mssqltips.com/sqlservertip/2569/sql-server-2008-r2-upgrade-failure-due-to-security-group-upgrade-rule/" target="_blank">SQL Server 2008 R2 Upgrade Failure due to Security Group upgrade rule</a> </span></span></h2>
<h2 style="text-align: left;">
<span style="font-family: inherit;"><span style="font-size: large;"> </span></span></h2>
<div style="text-align: left;">
<span style="font-family: inherit;"><span style="font-size: large;"><span style="font-size: small;">Visit my author profile to read my other articles. <b><a href="http://www.mssqltips.com/sqlserverauthor/67/manvendra-singh/" target="_blank">Manvendra Deo Singh</a></b></span> </span></span></div>
</div>
Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-58167019327555934912013-03-17T08:27:00.000-07:002013-03-17T08:27:06.858-07:00How to protect My Stored Procedure Code.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
When deploying applications to a client's server(s) or to a shared SQL Server, there is often a concern that other people might peek at your business logic. Since often the code in a stored procedure can be proprietary, it is understandable that we might want to protect our T-SQL work. Use "<strong>WITH ENCRYPTION" </strong>option to protect your SQL code as shown below.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
CREATE PROCEDURE dbo.Manvendra</div>
<div style="text-align: left;">
<strong>WITH ENCRYPTION </strong></div>
<div style="text-align: left;">
AS </div>
<div style="text-align: left;">
BEGIN </div>
<div style="text-align: left;">
SELECT 'SQL statements' </div>
<div style="text-align: left;">
END</div>
<div style="text-align: left;">
<div style="text-align: left;">
Now when you will try to run sp_helptext to see the code, below error will appear.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
"The text for object 'Manvendra' is encrypted"</div>
</div>
</div>
Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-8015359647255722242011-12-10T08:34:00.000-08:002011-12-10T09:49:14.181-08:00How to find out how much CPU a SQL Server process is really using<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-family: inherit;">Have you ever think about kpid in SQL Server when you look into sysprocesses system table.Its very useful coloumn when you are dealing with CPU pressure.When you look into the database server you see </span><a href="http://www.mssqltips.com/sqlservertip/2316/how-to-identify-sql-server-cpu-bottlenecks/"><span style="font-family: inherit;">CPU utilization is very high</span></a><span style="font-family: inherit;"> and the SQL Server process is consuming most of the CPU. You launch </span><a href="http://www.mssqltips.com/category.asp?catid=52"><span style="font-family: inherit;">SSMS</span></a><span style="font-family: inherit;"> and run </span><a href="http://www.mssqltips.com/sqlservertip/1029/sql-server-command-line-tools-to-manage-your-server/"><span style="font-family: inherit;">sp_who2</span></a><span style="font-family: inherit;"> and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure.</span><br />
<span style="font-family: inherit;">At the server level you can only see the overall SQL Server process, but within SQL Server you can see each individual query that is running. Is there a way to tell how much CPU each SQL Server process is consuming? In this article I explain how this can be done.</span><br />
<span style="font-family: inherit;">Find the below tip to get step by step process to identify a particular sql server process which is responsible for CPU pressure.</span><br />
<br />
<span style="font-family: inherit;"><strong><span style="font-size: large;">Tip:</span></strong> </span><a href="http://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/" target="_blank"><span style="font-family: inherit;"><strong><em>How to find out how much CPU a SQL Server process is really using</em></strong></span></a></div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com1tag:blogger.com,1999:blog-4458965029292017318.post-67062632396562233472011-12-10T08:18:00.000-08:002011-12-10T09:53:26.517-08:00Enable Powershell Remoting on SQL Server Instances<div dir="ltr" style="text-align: left;" trbidi="on">Logging on to each SQL Server instance for daily DBA Administrative tasks can be quite lengthy. Are there any options available in PowerShell to reduce the effort and complexity of managing a SQL Server environment? Yes It is..Find this <a href="http://www.mssqltips.com/sqlservertip/2516/enable-powershell-remoting-on-sql-server-instances/" target="_blank">MSSQLTips</a> for step by step process to enable Powershell Remoting and access SQL Server Instances remotly.<br />
<br />
<span style="font-size: large;"><strong>Tip</strong></span>:- <a href="http://www.mssqltips.com/sqlservertip/2516/enable-powershell-remoting-on-sql-server-instances/" target="_blank"><strong><em>Enable Powershell Remoting on SQL Server Instances</em></strong></a></div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-17690232768930384392011-05-24T02:54:00.000-07:002011-05-24T02:54:10.525-07:00ASYNC_IO_COMPLETION Wait type<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-size: small;">Normally </span><span style="font-size: small;">this wait type can be seen in backup and restore transactions.and whenever you will see this wait type your backup/restore process will be in suspended state most of the time because the process is waiting to get IO resource to proceed its operation and it will wait till certain time period then moved in suspended state. In that case your process will take more than its usual time to complete or most of the time it will hung or will showing in executing state for unknown time duration.</span><br />
<br />
<br />
<div class="Text"><span style="font-size: small;">This wait type occurs when a task is waiting for asynchronous I/Os to finish. This wait type is normally seen with few other wait types like BACKUPBUFFER,BUCKIO etc. This is clear indication of DISK IO issue.You can also get the Average disk queue length or current disk queue length value at the same time when you are getting this wait type.Compare both counters and if these counters have high value then you should look into your storage subsystem. Identify disk bottlenecks, by using Perfmon Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN.</span></div><div class="Text"><span style="font-size: small;">Any of the following reduces these waits:</span></div><div class="NumberedList1"><span style="font-size: small;">1.<span style="font-family: "Times New Roman"; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span>Adding additional IO bandwidth.</span></div><div class="NumberedList1"><span style="font-size: small;">2.<span style="font-family: "Times New Roman"; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span>Balancing IO across other drives.</span></div><div class="NumberedList1"><span style="font-size: small;">3.<span style="font-family: "Times New Roman"; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span>Reducing IO with appropriate indexing. </span></div><div class="NumberedList1" style="font-family: inherit;"><span style="font-size: small;">4.<span style="font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: normal; line-height: normal;"> </span>Check for bad query plans.</span></div><span style="font-family: inherit; font-size: small;">5. Check for memory pressure</span><br />
<br />
<div class="Text" style="font-family: inherit;"><span style="font-size: small;"> We can also corelate this wait type between Memory pressure and Disk IO subsystem issues.</span></div></div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-47503723375429206022011-05-06T13:01:00.000-07:002011-07-05T05:18:15.242-07:00BACKUPBUFFER Wait Type<div dir="ltr" style="text-align: left;" trbidi="on"><div style="color: black; font-family: Verdana,sans-serif;"><span style="font-size: small;">Today I was working on an issue in which backup job was showing in executing state since last 2 days.Initially my observation was either it was hung in middle of its process or it was processing very slow.Here i decided to see the lock wait type for this process.when i gather this info i see the wait type was BACKUPBUFFER wait type.</span></div><div style="color: black; font-family: Verdana,sans-serif;"><br />
</div><div style="color: black; font-family: Verdana,sans-serif;"><span style="font-size: small;">BACKUPBUFFER Wait Type:-This </span><span class="Apple-style-span" style="font-size: small; line-height: 19px;">wait type Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount. </span><span class="Apple-style-span" style="font-size: small; line-height: 19px;">This wait stats will occur when you are taking the backup on the tape or any other extremely slow backup system.This wait type can be seen with one more wait type i.e. ASYNC_IO_COMPLETION. </span></div><div style="color: black; font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="http://www.amazon.com/Seagate-Expansion-Portable-External-ST905004EXA101-RK/dp/B001UHWHO4?ie=UTF8&tag=sql031-20&link_code=btl&camp=213689&creative=392969" target="_blank"></a></span></div><div style="color: black; font-family: Verdana,sans-serif;"><br />
</div><div style="color: black; font-family: Verdana,sans-serif;"><span style="font-size: small;"></span></div><div style="color: black; font-family: Verdana,sans-serif;"><span style="font-size: small;"></span></div><div style="color: black; font-family: Verdana,sans-serif;"><span style="font-size: small;"><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sql031-20&l=btl&camp=213689&creative=392969&o=1&a=B001UHWHO4" style="border: medium none ! important; margin: 0px ! important; padding: 0px ! important;" width="1" /></span></div><div style="color: black; font-family: Verdana,sans-serif;"><span class="Apple-style-span" style="font-size: small; line-height: 19px;">These both wait type indicates that the issue is with storage/disk subsystem.So solution is to either ask your storage team to look in this or change your disk/storage subsystem and run your backup on a healthy and fast storage subsystem.</span></div><div style="color: black; font-family: Verdana,sans-serif;"><br />
</div><div style="color: black; font-family: Verdana,sans-serif;"><span class="Apple-style-span" style="font-size: small; line-height: 19px;"><a href="http://www.amazon.com/Apple-touch-Generation-NEWEST-MODEL/dp/B001FA1O18?ie=UTF8&tag=sql031-20&link_code=btl&camp=213689&creative=392969" target="_blank">Apple iPod touch 32 GB (4th Generation) NEWEST MODEL</a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sql031-20&l=btl&camp=213689&creative=392969&o=1&a=B001FA1O18" style="border: medium none ! important; margin: 0px ! important; padding: 0px ! important;" width="1" /></span> </div></div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com1tag:blogger.com,1999:blog-4458965029292017318.post-47090852247746108942011-04-14T00:52:00.000-07:002011-04-14T01:01:04.767-07:00RESOURCE_SEMAPHORE Wait Type<div dir="ltr" style="text-align: left;" trbidi="on">RESOURCE_SEMAPHORE waits occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.<br />
<br />
High waits on RESOURCE_SEMAPHORE usually result in poor response times for all database users, and need to be addressed.<br />
<br />
It is also useful to correlate high waits on RESOURCE_SEMAPHORE with the Memory Grants Pending and Memory Grants Outstanding SQL Memory Mgr performance counters. Higher values for these counters indicate a definite memory problem especially a non-zero value for Memory Grants Pending.<br />
<br />
The root cause of this type of memory problem is when memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory. The solution would be to tune the offending queries, or manage their workload so that they are executed at less busy times.<br />
<br />
You can also get another article about sql server Wait type here:<i><a href="http://manvendradeosingh.blogspot.com/2011/04/what-is-sql-server-wait-type-or-how-to.html" style="color: white;">Sql server Wait Type</a></i></div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-71784432301114254622011-04-14T00:50:00.000-07:002011-04-14T00:56:40.766-07:00What is SQL Server Wait Type? or How to get wait type info in sql server?<div dir="ltr" style="text-align: left;" trbidi="on">In sql server 2005 and later version we can get this info from various DMVs.One of the most useful DMV is sys.dm_qs_wait_stats.This DMV gives you the detail report of all wait types and you can look into those wait types which are causing issue or waiting from lot much time.You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.Following columns will come in output when you run this DMV:<br />
<br />
<ul style="text-align: left;"><li><b>wait_type :-</b> Name of the wait type.<u><br />
</u><b>waiting_tasks_count:-</b>Number of waits on this wait type. This counter is incremented at the start of each wait.</li>
<li><b>wait_time_ms:-</b>Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.</li>
<li><b>max_wait_time_ms:-</b> Maximum wait time on this wait type.</li>
<li><b>signal_wait_time_ms :-</b>Difference between the time that the waiting thread was signaled and when it started running. </li>
</ul><br />
In general there are three categories of waits that could affect any given request: <br />
<ul><li> <b>Resource waits</b> are caused by a particular resource, perhaps a specific lock that is unavailable when the requested is submitted. Resource waits are the ones you should focus on for troubleshooting the large majority of performance issues. </li>
<li> <b>External waits</b> occur when SQL Server worker thread is waiting on an external process, such as extended stored procedure to be completed. External wait does not necessarily mean that the connection is idle; rather it might mean that SQL Server is executing an external code which it cannot control. Finally the queue waits occur if a worker thread is idle and is waiting for work to be assigned to it. </li>
<li> <b>Queue waits</b> normally apply to internal background tasks, such as ghost cleanup, which physically removes records that have been previously deleted. Normally you don't have to worry about any performance degradation due to queue waits. </li>
</ul><br />
<br />
sys.dm_os_wait_stats shows the time for waits that have completed. This dynamic management view does not show current waits.If you want to see current wait type then you should use another system table sys.sysprocesses.Run below cmd:<br />
<br />
<b>Select * from sys.sysprocesses</b><br />
<br />
The output describes you the wait time and wait type for each process id. So here you can get which process is pending since how much time and which type of wait is that.Once you have the process id then you can run below cmd to get the codes behind that SP id:<br />
<br />
<b>DBCC inputbuffer(SP ID)</b><br />
<br />
You can also compare DMV (sys.dm_os_wait_stats)output to this sysprocess table output and you can better analyze the exact issue.<br />
<br />
A SQL Server worker thread is not considered to be waiting if any of the following is true:<br />
<br />
* A resource becomes available.<br />
* A queue is nonempty.<br />
* An external process finishes.<br />
<br />
Although the thread is no longer waiting, the thread does not have to start running immediately. This is because such a thread is first put on the queue of runnable workers and must wait for a quantum to run on the scheduler.<br />
<br />
Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance. For example, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.<br />
<br />
The contents of this dynamic management view can be reset by running the following command:<br />
Copy<br />
<br />
<b>DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);<br />
GO</b><br />
<br />
This command resets all counters to 0. </div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-83612960153653028002011-03-01T23:55:00.000-08:002011-03-01T23:58:42.856-08:00How to know db version or build no from a backup file? or How to get the exact version on which master db was backed up?<div dir="ltr" style="text-align: left;" trbidi="on">Generally we don't need this info for user db but when you have to recover or restore master db then you should have exact db version or build no else you cannot recover your db like i am giving you a scenario suppose you instance is corrupted now you are not able to access your sql server instance and even you don't have exact db version like 9.00.**** In this case you need exact build no else you can not recover your sql server Instance.To get this info you need a master db backup file.You can run below cmd to get this info:<br />
<br />
<b>Restore headeronly from disk='D:\MSSQL\Backup\master.bak'</b><br />
<br />
Now output will show you lot much info like user name,LSNs,device type,server name etc but you should look into below three coloumns to get exact version no of sql server.As per this output the version on which master db was backed up is 10.50.1600.Now you got the exact build so install sql server till this build no and restore master db easily.<br />
<br />
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild<br />
10 50 1600<br />
<br />
<br />
<br />
<br />
You can get another article regarding how to restore master db in this blog as well.Kindly find the link:<a href="http://manvendradeosingh.blogspot.com/2010/01/rebuild-and-restore-master-database.html" style="background-color: blue; color: white;">Restore master db</a></div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-15578862914221535152011-03-01T22:46:00.000-08:002011-03-01T22:46:22.085-08:00How to recover or repair a suspect database in sql serverA database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.<br />
<br />
You can run below cmd to get about all errors due to whihc db went in suspect mode:<br />
<br />
<b>DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS</b><br />
<br />
Once you have enough info about errors then your next step should be Repair.If error is showing datafiles missing then check your datafile drive connectivity from storage side if its missing then you dont need to do anythig just contact someone from storage or one who manages storage in your env and ask them to check data file drive is healthy or not.<br />
<br />
And if there is another issue then you can repair your database from below cmds:<br />
<br />
<b>SP_RESETSTATUS [DBNAME]<br />
go<br />
ALTER DATABASE [DBNAME] SET EMERGENCY<br />
go<br />
ALTER DATABASE [DBNAME] SET SINGLE_USER;<br />
go<br />
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; <br />
go<br />
ALTER DATABASE [DBNAME] SET Multi_USER;</b><br />
<br />
Put your db name in place of [DBNAME] in above script.<br />
<br />
<b>Note:-</b>REPAIR_ALLOW_DATA_LOSS is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-55509251382139064452011-02-18T06:35:00.000-08:002011-02-18T06:35:08.404-08:00"Error 14258: Cannot perform this operation while SQL ServerAgent is starting. Try again later."<div dir="ltr" style="text-align: left;" trbidi="on">I saw this error today while i was diagnosing my log shipping issue in which primary and standby servers was not in sync.i checked log but i didn't find anything even Sql agent was running and all jobs was showing as successful.<br />
<br />
Then i tried to bounce Agent service on primary node then i got below error:<br />
<br />
"Error 14258: Cannot perform this operation while SQL Server Agent is starting. Try again later."<br />
<br />
To fix this issue, I tried stopping and then restarting just the Agent<br />
service and it won't start, reporting the same error then my guess was that SQL Server was running in lightweight pooling mode. I set it back to the default of<br />
thread mode.<br />
<br />
sp_configure 'allow updates', 1<br />
go<br />
reconfigure with override<br />
go<br />
sp_configure 'lightweight pooling', 0<br />
go<br />
reconfigure with override<br />
<br />
<br />
And after this change it worked for me..</div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-66689383948452022562011-02-09T03:25:00.000-08:002011-02-09T03:25:51.093-08:00How to disable Auto Commit in SQL ServerYou can turn auto commit OFF by setting implicit_transactions ON:<br />
<br />
SET IMPLICIT_TRANSACTIONS ON<br />
<br />
When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually.<br />
And when you need to enable it just run above cmd with OFF clause.<br />
<br />
SET IMPLICIT_TRANSACTIONS OFF<br />
<br />
autocommit is the default for Sql Server 2000 and up.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-75496410916687095392011-01-25T04:10:00.000-08:002011-01-25T04:10:32.336-08:00Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'ADsDSOObject'. OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e14].Whenever you get this issue make sure your security context is correctly supplied.In that case you have to use domian user acount in sp_addlinkedserver stored procedure whihc has access rights to Active Directory.The domain name is required in the Security settings for the remote login. So in the Security settings I entered Remote login: domain\user; With password: password.Find below dummy example.<br />
<br />
Step1:-EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'LINLDP2'<br />
<br />
Output : Success<br />
<br />
Step2: EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false', @rmtuser='Domainname\username', @rmtpassword='#Security@SWIM#'<br />
<br />
Also,Make sure your SQL Server Service is running under the account which has access right to Active Directory.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-79851175644852852112011-01-21T06:07:00.000-08:002011-01-21T06:08:52.755-08:00How to get, when your db was restored last time?Suppose you have to findout when you db was restored last time and by whihc user.To get this info you have to look into a system table 'restorehistory' whihc exist in system database msdb.Run below cmds to get this info:<br />
<br />
use msdb<br />
go<br />
select Destination_database_name AS [DB Name],user_name AS [User] ,restore_date As [Last Restore Date] <br />
from restorehistory<br />
where Destination_database_name like ('qa%')<br />
<br />
Once you will run this you will get below output.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFSo_2BBiNX2bN0Pab2JFNTed51wdUDNsmrZrysonNq4YwCmA7-chOxI0vfXGwujetZ-eBoAxCvNsZ1SiUEJWT6jB-62S3U4jWZsymWn9vBk8dyKJuWT-VLNXTN_zFMpllGPX9yNuhNko/s1600/image.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="66" width="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfFSo_2BBiNX2bN0Pab2JFNTed51wdUDNsmrZrysonNq4YwCmA7-chOxI0vfXGwujetZ-eBoAxCvNsZ1SiUEJWT6jB-62S3U4jWZsymWn9vBk8dyKJuWT-VLNXTN_zFMpllGPX9yNuhNko/s320/image.jpg" /></a></div>Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com3tag:blogger.com,1999:blog-4458965029292017318.post-46858845393447586882011-01-21T05:55:00.000-08:002011-01-21T05:55:02.166-08:00To find out no of traces running on your db instanceSuppose you want to find out how many traces are running on your instance?<br />
No idea???here is a solution to find out no of traces, also you can stop them by below script.<br />
<br />
<b>To Find out the number of traces running use the below query<br />
</b><br />
SELECT * FROM fn_trace_getinfo(default);<br />
GO<br />
You will get below output:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgd5xtt0fN7dG67A4MF7UWlYrB1vCbsNy44WtILdw84DpjYDsGV-7HzLR4WdNzluVcQCMmW_wLkzxFcLO3ExJ8ZMcSjFPNdCCPWNRBzE8IZyQrdrnSZmdf6CPjLdPiYBbYjCgSTUqDa88Kr/s1600/trace.jpg" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="50" width="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgd5xtt0fN7dG67A4MF7UWlYrB1vCbsNy44WtILdw84DpjYDsGV-7HzLR4WdNzluVcQCMmW_wLkzxFcLO3ExJ8ZMcSjFPNdCCPWNRBzE8IZyQrdrnSZmdf6CPjLdPiYBbYjCgSTUqDa88Kr/s320/trace.jpg" /></a></div><br />
You can find out the no of traces through trace ID coloumn/count.Now suppose you have to stop them then run below script:<br />
<br />
DECLARE @TraceID int<br />
SET @TraceID = 2<br />
EXEC sp_trace_setstatus @TraceID, 0<br />
EXEC sp_trace_setstatus @TraceID, 2<br />
<br />
Replace the trace id value with one which has to be stopped.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-43154400505099827792011-01-05T05:47:00.000-08:002011-01-05T05:49:39.528-08:00Error Msg 1813, Level 16, State 2, Line 1, Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.Fix/Solution/Workaround:<br />
<br />
Follow below steps:-<br />
1. Move mdf and ndf files to another directory (Data_old)<br />
2. Create a database with the same name and same file names and locations as the original databases. (this only applies to the mdf and ndf files the log file can go anywhere)<br />
3. Stop the SQL Server service.<br />
4. Overwrite new mdf and ndf files with the original ones.<br />
5. Start SQL Server.<br />
6. Run this script (Set the @DB variable to the name of your database before running):<br />
Declare @DB sysname;<br />
set @DB = ‘DBName’;<br />
– Put the database in emergency mode<br />
EXEC(‘ALTER DATABASE [' + @DB + '] SET EMERGENCY’);<br />
– Set single user mode<br />
exec sp_dboption @DB, ‘single user’, ‘TRUE’;<br />
or<br />
– Repair database<br />
DBCC checkdb (@DB, repair_allow_data_loss);<br />
– Turn off single user mode<br />
exec sp_dboption @DB, ‘single user’, ‘FALSE’;<br />
<br />
If you are not able to connect after single user then run below cmd in one go.<br />
<br />
Alter database dbname set single_user with roll back immediate<br />
go<br />
Run your DBCC checkdb command<br />
go<br />
Alter database dbname set multi_user<br />
<br />
I got an error stating that the log file did not match the data file. You can ignore this as we are rebuilding the log file.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com2tag:blogger.com,1999:blog-4458965029292017318.post-15651530340957993672011-01-04T02:54:00.000-08:002011-01-04T02:54:43.073-08:00BIDS is not working after SQL Server Installation or BIDS is not installed properly.First point is to make sure that Visual Studio is installed or not. If not previously have Visual Studio installed then first install it from SQL Server setup or run below msi or exe file:<br />
<br />
*.\Tools\Setup\vs_setup.msi<br />
<br />
This will install the VS Shell. After this is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory: <br />
<br />
start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS<br />
<br />
If above cmd gives any error then run sqlRun_tools.msi (Root\tools\setup\sqlRun_tools.msi))setup before running above cmd.Once this will complete then proceed once again with above cmd.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com2tag:blogger.com,1999:blog-4458965029292017318.post-43051018797888276702010-09-18T05:17:00.000-07:002010-09-18T05:34:22.903-07:00Sql Server Perfmon counters for MemoryHere there are few sql server perfmon counters which will tell you about memory bottleneck for your sql server instance.Monitor these counters and compare your value with below given values.<br /><br />1- Buffer cache hit ratio--<br /><br />-Indicates how often sql server can get data from the buffer rather than disk.<br />-Buffer cache hit ratio > 90% for OLAP<br />-Buffer cache hit ratio > 95% for OLTP<br /><br />2-Free list stalls/sec--<br /><br />-The frequeny that requests for database buffer pages are suspended because there's no buffer available.<br />-Free list stalls/sec < 2<br />-if value is high that means memory shoud be increase.<br /><br />3-Free pages--<br /><br />-The total no of 8k data pages on all free lists<br />-Free pages > 640<br /><br />4-Lazy writes/sec--<br /><br />-The no of times per second that lazy writer moves dirty pages from buffer to disk to free buffer space.<br />-Lazy writes/sec < 20<br />-greater value will indicate memory bottleneck.<br /><br /><br />5-Page Life Expectancy--<br /><br />-No of seconds a data page stays in the buffer.<br />-Page Life Expectancy > 300,otherwise memory pressure is at play.<br /><br />6-Page Lookups/Sec--<br /><br />-No of requests to find a page in the buffer.<br />-Page lookups/sec)/(Batch request/sec)<100<br /><br />7-Page Reads/sec and Page writes/sec--<br /><br />-no of physical db page reads and writes issued,respectly<br />-Value should be < 90Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-28083734066609870932010-09-15T00:55:00.000-07:002010-09-15T04:49:04.327-07:00HOWTO: Change Microsoft SQL Server Authentication mode from windows to Mixed ModeThere are two method to change the authentication mode from windows to mixed.<br /><br />1-GUI<br />2-Registry<br /><br />1-GUI Method-<br /><br />Connect to Instance then go to Instance property.Choose security option from left side,here you can change from windows to Mixed mode.after changing restart the sql server services.<br /><br />2-Registry Method-<br /><br />For SQL Server 2005 Express Edition,You need to go it manually. The first step is to change the login-mode.<br /><br />Open registry editor (launch application %WINDIR%\regedit.exe) and go to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in the tree on the left.<br /><br />On the right, look for an entry named LoginMode. The default value, when installed is 1. Update it to 2. The next step is to restart the service.<br /><br />Launch your Service Manager (Start -> Run -> Type services.msc) and look for a service named MSSQL Server (SQLEXPRESS). Restart the service.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-8226767473159136022010-09-11T23:21:00.000-07:002010-09-12T01:41:14.707-07:00How to move a table to a different filegroupWe can change a table from one filegroup to any other filegroup through droping and recreting the clustered indexes.below is given ALTER cmnds which will move your whole table from filegroup1 to filegroup2.<br /><br />Cmnds are-<br /><br />ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)<br />GO<br />ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)<br />GO<br /><br />where TAB1 is table name PK_TAB1 is clustered index key and TEST_DATA_2 is target filegroup.Manvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0tag:blogger.com,1999:blog-4458965029292017318.post-43028972058537455692010-09-11T23:02:00.000-07:002010-09-11T23:17:54.608-07:00Point in Time Recoverysometimes detrimental command will probably be issued against one of your databases and you will need to recover the lost data. There are several actions that you might be able to take to recover the lost data, but what option makes the most sense. One option that SQL Server offers is the ability to do point in time restores of your data in order to restore your database back to the point right before that detrimental command was issued.<br /><br />Point-in-Time Restore:-<br /><br />1-This topic is relevant only for databases that are using full or bulk-logged recovery mode. Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.<br />2-Restore the last full database backup and, if any, the last differential database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).<br />3-Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT=time, RECOVERY). <br />4-The basic syntax is:<br /><br />RESTORE LOG database_name FROM <backup_device> WITH STOPAT =time, RECOVERY…<br /><br /><br />Cmnds are:<br /><br />-- Restore the full database backup.<br />RESTORE DATABASE AdventureWorks<br /> FROM disk='backup file location' <br /> WITH NORECOVERY;<br />GO<br />RESTORE LOG AdventureWorks<br /> FROM disk='backup file location' <br /> WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM';<br />GOManvendrahttp://www.blogger.com/profile/12825509883676416752noreply@blogger.com0