Unable to Shrink Tempdb Data and Log files

Unable to Shrink Tempdb Data files have grown large.

You may receive this error message.

File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty Continue reading

SQL Server – Lock Pages in Memory

This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM). Continue reading

SQL Server : How to move or add Tempdb data and log files

Problem:

– TempDB has grown big and the current disk drive does not have enough space.
– Adding TempDB data file to another physical drive helps to improve performance, as they can be read simultaneously.

Solution : Here I have tried to explain how can we move tempdb data files from one place to another. Continue reading

SQL Server 2008 R2 : LOG SHIPPING – A VERY LARGE DATABASE

Scenario:  A couple of days back, when we were setting up a DR plan for one of our most mission critical DB server which was hosting around 10 databases having size of 10,23,5,7.4,1.8,6.2,7.9,5.53,8.7 and 125 GB respectively. I decided to implement Log Shipping as a part of the DR strategy because the DR server (Secondary Server) was never going to be used for Reporting purpose; therefore Log Shipping was the best choice. Implementing Log Shipping for small databases is relatively Simple as one can easily do it using the GUI but for large database GUI is not a good option as the database on the destination server will be always out of sync. So a question arises on “How do we accomplish this task?”. In this article, I am going to explain you’ll the detailed steps which we followed to implement the same. Continue reading

Collection of useful Commands for SQL Server DBA

In this article I have tried to give as much as I can from my real time experience. You may try these commands on one of your test/pre-production server.
Continue reading

Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

How can we insert data from one table to another table without using import/export wizard?

How will you create one table (new table) from another table (source table). The new table should be empty after creation?
Continue reading

Quick Way to Create Single Table Backup

There are several ways to create backup for a single table in SQL Server database. In SQL Server 2008, now you can create insert statements for selected tables. Beside this Export wizard is commonly used to create a flat file backup for a single table. Both methods are reliable but still time consuming. And when I searched for quickest method to take backup of a single table I found following code by SQL Expert Fred. Continue reading