Sunday, 4 December 2011

SQL Server-Shrink log file

SQL Server-Shrink log file 


In SQL Server 2005, a shrink operation (DBCC SHRINKFILE) tries to shrink the specified transaction log file to the requested size immediately. For Shrinking SQL Server log files manually under the full recovery model, first back up the transaction log file. Then, use the DBCC SHRINKFILE statement to shrink the transaction log file.

When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size. This log backup operation is in addition to the three steps that you perform to shrink the transaction log file in SQL Server 2000. To shrink a transaction log file that has little free space in SQL Server 2005, follow these steps.




1.) Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.


BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'


2.) Shrink the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.


DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS


3.) If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive.


4.) Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be similar to the target size.


In summary, the log manager's algorithm for picking up the next virtual log file changed in SQL Server 2005. Therefore, shrinking the transaction log file in SQL Server 2005 may differ from shrinking the transaction log file in SQL Server 2000:
a) If a log file has lots of free space, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000.
b) If a log file has no free space, shrinking the transaction log file in SQL Server 2005 is the same as shrinking the transaction log file in SQL Server 2000.
c) If a log file has little free space, you may have to perform an additional log backup operation in SQL Server 2005 than you have to perform in SQL Server 2000.

Hence we have seen Shrinking SQL Log files can be done to reclaim the space.


Wednesday, 23 November 2011

SQL Server Database Migration

SQL Server Database Migration


SQL Server Database Migration is a task which every SQL DBA comes around often. We have two ways of migrating the databases; the first one is Side by Side and the other one is In-place. Most of the DBAs prefer Side by Side method of Migration since you have your DBs running on the Source Server until and unless you don't stop them manually. Following are the steps we should follow while migrating the Databases( Side by Side method).


Pre-Migration Steps: 



On the Source Server: This is the Server where your DB already exists.


1) Document the required details like the Source and Destination Server details which include Server Name, Database Name, Port number, DB size etc.


2) Once you have everything ready...You are all set to start. Login to your Source Server and have your DB(which has to be migrated). For eg


BACKUP DATABASE DATABASE_NAME TO DISK='D:\MIGRATION\DB_NAME.BAK' WITH STATS=10'


3) Once you have your DB backed up, you need to check for backing up Logins. We have various steps for scripting out the logins such as scripting the logins by packages, create script wizard etc. We also have sp_help_revlogin stored procedure which reads the system tables to generate the script for each logins and users with the passwords in encrypted form. sp_help_revlogin script is available on internet. This can be installed on the Server and can be used. This SP makes your life easier. The syntax will be:


SP_HELP_REVLOGIN 'DATABASE_NAME'


N.B.: For those who don't have Sp_help_revlogin installed on your server, click here to get the code:


4) Once you are done with scripting out the users and login its now time to script out Jobs. You can simply right click to the job and script it out to a text pad or file.


      Once you are done with all the above steps you are now ready to actually Migrate your DB/DBs.


Migration Steps:



On the Destination Server: This is the Server where your DB will be migrated.


1) Restore the Backup of the DB which you have taken from the Source Server. Syntax will be.


2) Once Restoration is done you now need to check for orphaned users in your newly migrated DB. You can run the below Stored Procedure.


SP_CHANGE_USERS_LOGIN  'REPORT'


This will give you a list of all the orphaned user in the database which  can be fixed by:


-- If you already have a login id and password for this user,
-- fix it by doing:

EXEC SP_CHANGE_USERS_LOGIN  'AUTO_FIX', 'USER'

-- If you want to create a new login id and password for
-- this user, fix it by doing:

EXEC SP_CHANGE_USERS_LOGIN 'AUTO_FIX', 'USER', 'LOGIN', 'PASSWORD'

3) Once you have fixed the Orphaned users its now time to restore jobs and packages which you have scripted out from Source Server.


4) Post restoration you should re-index and update the stats. The below SPs will help you re-indexing and updating stats for all the tables in a Database.


USE DATABASE_NAME
GO
EXEC SP_MSFOREACHTABLE@command1="PRINT '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC SP_UPDATESTATS
GO



This completes your SQL Server Database Migration.