Archive for the ‘SQL 2008’ Category

Know-How to Perform SQL Server Disaster Recovery

September 25, 2019 Leave a comment

Summary: Many times database users have to face SQL server disaster situations due to virus attack, power failure, hardware failure. So in this post, we have discussed SQL Server disaster prevention tips. Also, the user can read this post to perform SQL Server disaster recovery. 

Microsoft SQL Server is a full-featured relational database management system. Due to its advanced features and functionality, this application is used to store and retrieve data used by other applications. This application stores the SQL database in MDF also called Master database file or LDF which is the log database file. A database can also have the secondary database file with NDF extension. But sometimes the SQL users have to face disastrous situations such as database corruption issues, accidental deletion of the data.

SQL server disaster is an event due which the users SQL users has to face data loss situation. But if the user has a proper disaster recovery plan then the user can easily overcome the data loss after the disaster. So this problem tackling blog will discuss how to perform SQL Server disaster recovery with the help of SQL Database recovery software.

When this SQL Server application works properly then everything will go smoothly. But if there is something wrong with the primary database file then the user SQL users have to face problems in accessing the database.

Pre – Disaster Prevention Tips to Avoid Database Corruption Issues 

Here are some tips which help the users to prevent database corruption problem.

1. Regular Clear Virus and Malware – By regular clearing of Virus and other malware with the help of antivirus software will protect the SQL database from corruption issues.

2. Try to Take the backups of SQL Server Database Regularly: This will also help you to resolve SQL database corruption problem. In case if the user has proper backup then the user can easily restore the corrupted database with the help of backups.

3. Avoid Frequently updatation of the SQL Server or Windows Server – Frequently updatation of the SQL Server will cause compatibility issues. If your current operating system and SQL Server are running fine then don’t upgrade the SQL server or the Windows server.

Note: The user can also read another post to Restore SQL database easily

From the above write up we have discussed the tips to prevent SQL database from corruption. But what to do in case if the disaster has already happened. Like if the user has accidentally deleted the crucial records from the SQL database or in case your entire database is corrupted then I have the best SQL Server Disaster Recovery technique for you.

Perform SQL Server Disaster Recovery By Using Expert Solution

To overcome the data loss the user can take the help of SQL Database Recovery Software. This is a standalone application to recover the SQL database easily. With the help of SQL file recovery the user can easily restore the database objects such as SQL table, triggers, views, indexes, functions, etc, or in case if you have accidentally deleted your SQL database records then also this tool will you to recover accidentally deleted data. The remarkable feature of this utility is that it helps to preview the deleted SQL table records in red color.

Follow The Steps to Perform SQL Server Disaster Recovery Easily 

1. Launch the SQL Recovery Software and Add the corrupted MDF file.

2. Select the Scan option after that select the corrupted MDF file from your system. The user can also check the option to preview deleted SQL table records in red color.

3. Preview the SQL database objects such as a table, stored procedure, functions, etc.

4. Click on Export button to export the SQL database.

Final Words 

In this article, we have discussed how to prevent your SQL database from corruption. Also in case if the if you are facing any disastrous situation related to corruption of SQL database then the user can take the help of SQL Recovery software to perform SQL server disaster recovery easily.

Simple Way to Find Transaction Log Activities in SQL Server

“I am using SQL Server 2005. When I was doing modification to my database, I found that some of the data has already been modified. I want to find out the person who did changes to my database. Kindly help me in finding how to check transaction log in sql server 2005. I want to know the name of the person who did the changes along with the transaction date and time.”

There are many people who got stuck when comes to finding a transaction in a log for a particular user. In this case, SQL log file help user to examine all the transaction activity done in the SQL Server. In this blog we will discuss how to find transaction log in SQL Server.

Understanding Transaction Log in SQL Server

SQL Server database consists of transaction log that records all transaction activity like transaction time, transaction name, table name in LDF file. It also records each and every database modifications made by person. So, when a person did any modification in the database, then it becomes easy to identify the person who did changes in a log file. Reading logs in SQL Server is not an easy task, so here we will be discussing an easy & quick way to find out the transaction in SQL Server.

Quick way to Check Transaction log in SQL Server

Many business decision-makers face problem in connection with database due to many reasons such as sudden system shutdown, delay in troubleshooting, control audits or changes in employees. In such case, sometimes, they need to recover accidentally deleted data, track unwanted changes done in the database and find out the name of the user that has changed the data. To keep in mind all the things, one easy & quick solution to track down the transaction in SQL Server is by using SQL transaction log reader. It analyze all the transaction details like transaction name, transaction time, table name, query in SQL Server.The tool also offers many advanced features like fetch and view SQL database records from the live database, read & analyze all transactions like insert, delete, update, etc.

How to Check Transaction Log in SQL Server

You can easily find out the transaction for a particular user from LDF file with the help of SQL Log Analyzer tool. However, it works in both online and offline environment.

  1. Install & open SQL Log Analyzer tool.
  2. Then, click on the Open button to add .ldf file.
  3. Now, you will get two options:
    • Online DB Option
    • Offline DB Option

    If you have selected Online database option, then you have to provide all server details.

  4. After that, the software will preview all transaction activities. From here, you will get the transaction log for a particular user.
  5. Conclusion

    In this write up, we have discussed an easy solution on how to read transaction logs in SQL Server. One can analyze and read all the transaction activities done in the logs by using this professional third party utility.

SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 Released!

September 29, 2014 2 comments

SQL Server 2008 SP4 (link) and SQL Server 2008 R2 SP3 (link) have been released.  Please note these are the final service packs for these products is now in Extended Support cycle of Product Support Life Cycles.

Update Sept. 30, 2014: Sorry for mistake, for SQL Server 2008 it would be SP4.

Online Index Rebuild can Cause Fragmentation

June 9, 2014 Leave a comment

As Microsoft SQL Server PFE, I am always playing with SQL Server.  I generally spend most of my time on the engine side of world; so I am somewhat of a newbie when it comes to BI.  So I understand when I see something in BI world and am a bit lost.  However when I ran into this, rebuilding index online caused fragmentation.  I was some what baffled. You can try it if you wish, I tested it on SQL 2008, SQL 2008 R2, and SQL 2012.

-- Create table for testing.
CREATE TABLE Test (Col1 INT IDENTITY(1,1), Col2 CHAR(50), Col3 CHAR(50), Col4 Char(50)

-- Insert test data.
INSERT INTO dbo.Test (Col2, Col3, Col4)
FROM sys.all_objects O1
CROSS JOIN sys.all_objects O2

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

-- Rebuild Index online using MAXDOP
WITH (Online=ON, MAXDOP=8)

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

Some of you might noticed an interesting property.  ALLOW_PAGE_LOCK.

So turns out this is by design.  If ALLOW_PAGE_LOCK is turned off, we are building a index using parallelism, and building it online; it can cause fragmentation (Ref 1 & Ref 2).

Well lesson learned for future and no, it will not be last one.

SQL Server 2008 SP3 CU8 Released

November 20, 2012 Leave a comment

Multiple components of SQL Server were fixed in this.  Fixes for SSRS, Replication, Locking Hints, Security issues, etc.

Few interesting ones for me…

  1. Added support for varchar(max), nvarchar(max), and varbinary(max) data types in OLD DB Providers.
  2. This is interesting one, I always say no memory leak in SQL Server.  This shows SQL is not beyond that, “FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC”.  Although its rare I see customers using this, but please note this issue happened after SP3 (as per KB2778088)  But it is an important issue identified and fixed.

Download the patch from here.

Microsoft Support policy for SQL Server in Windows 8 and Windows 2012

November 9, 2012 Leave a comment

Now that Windows 8 (which btw ROCKS!) and Windows 2012 is out, Microsoft has released a KB highlighting the support for SQL Server in respect to these operating systems.

In KB2681562, lists the support requirements, issues with install, etc. So a one-stop-shop or so… 🙂

CREATE FILE encountered operating system error 5(Access is denied.)

April 23, 2010 7 comments

When trying to attach the database to SQL Server with an account other then yours you might get following error.

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click:


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)

For help, click:



If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?).  With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders.  I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file.  So why the permission denied?  File was located in same folder as rest of mdf/ldf files…

Looking into it I saw the file had a small lock icon on it like “Lock Icon“. Looking into the permissions on the file I see it was only accessible by my user name.  I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic).  I edited the permission properties on the file to inhert form parent folder.

Reattached the database with no issues.

Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database.

April 23, 2010 2 comments

When attaching a database in SQL Server 2008, a user might get the following error.

 TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)



The default solution that is listed on the sites [1] & [2] is use a user a user with sysadmin.  It does resolve the issue; but why?

Digging into it a bit, I found when attaching a database SQL Server executes few extended stored procedures.  These stored procedures can only be executed by sysadmin role, therefore when someone with just dbcreator permissions try to attach the database it fails. The actual error is this …

Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, ‘Access is denied.’

So I decided to dive into books online to confirm I am not getting forgetful, Books Online state for sp_attach_db “sysadmin and dbcreator fixed server roles can execute this procedure’.So I decided to execute following command:

EXEC sp_attach_db    @dbname = 'DBName',

It attached successfully, so it seems like an issue in GUI only.

I have tried to look for KB article to see if there is a fix for this; also on Microsoft connect with no luck.  So I have a Microsoft Connect article here.

[1] How to change SQL Server login default database through SQL Script. Jeff Widmer’s Blog. Link.
[2] Unable to attach database using Management Studio. MSDN Forums Online. Link.

Get listing of Databases user has access to …

March 30, 2009 Leave a comment

You can right click on each user to find out what database he or she has access to; which can be very time consuming. I have wrote the script below that does similar thing for one user or multiple users.

Please note this procedure will take a while to complete depending on the number of databases in your system. You can submit three types of searches:

-- Check Single User [DEFAULT]
EXEC up_CheckUsersAccessInDB 'Test'

-- Check Multiple Users
EXEC up_CheckUsersAccessInDB 'Test1,Test2', 1

-- Check All Users
EXEC up_CheckUsersAccessInDB '%', 2

Stored Procedure: Link.

If any errors or issues please feel free to mail me, mohitkgupta at msn dot com.

Categories: Security, SQL 2008

Surface Area Configuration Manager *R.I.P.*

March 25, 2009 Leave a comment

All who liked the SACM raise your hands …

Right that’s what I thought no one heh; okay it wasn’t that bad. But non the less it was discontinued in SQL 2008.

So how do you lower the Surface Area?

Answer: Use SQL Server Configuration Manager to manage services and you can use Facets to configure Surface Area Configuration.

Right Click on Server -> Click on Facets -> Click on Drop down list select Surface Area Configuration

Change settings you need and click OK.

Following settings can be adjusted here:

  • AdHoc Remote Queries
  • CLR Integration
  • Database Mail
  • OLE Automation
  • Remote DAC
  • Service Broker Endpoint
  • Soap Endpoints
  • SQL Mail
  • Web Assistant
  • XP CmdShell

Ref #1:
Ref #2:

Categories: SQL 2008
%d bloggers like this: