How to optimize SQL Server Performance Video

This video tutorial will help you to understand :

• Identify the root cause of bottlenecks that hinder performance of your SQL Server environment
• Diagnose and fix T-SQL errors in development before code goes into production
• Learn new best practices for project planning and formatting your code
• Ensure that your code is scalable, optimized and validated
• Resolve 60-80% of the performance bottlenecks attributed to poorly written SQL
  
  
  
  
  

How to Move SQL Server database files to other drive ?

This question is being asked by one of the training participants. As per the his scenario, Initially this database server was set up and all database data files are being hosted on drive G:\  and transaction log was on F:\.
Now the drive, F:\ is full and there are some IO contentions as well, so he wanted move a particular database from a existing drive to a new drive.
I told the steps but he asked me the step by step procedure, so I promised him that I am going to write a short note on this.
Moving databases could be required because of

• Running out of space in the current disk drive.
• Moving to a faster disk (e.g. moving from SATA to SSD).
• Replacing the current disk drive and many more

Here is step by step procedure, how you can move you database / database files from a exiting drive to a new drive.
Step 1. Run the following script to get the database file list

USE TEST;  -- please replace "TEST" with your database name  
    GO
    SELECT
        DB_NAME(database_id) AS Database_Name,
        name AS Logical_Name,
        physical_name AS Physical_Name,
        type_desc AS Type_Description
    FROM sys.master_files WHERE database_id = DB_ID()
    GO

Step 2. Run the following script to take the database offline

USE master;
    GO
    ALTER DATABASE TEST SET OFFLINE;   -- please replace "TEST" with your database name  
    GO

Step 3. Manually move the database file(s) listed in Step 1 to the new location
Step 4. Run the following script for each file that was moved

USE master;
    GO
    ALTER DATABASE TEST MODIFY FILE (NAME = TEST_Data, FILENAME = 'C:\newlocation\TEST_Data.mdf';  -- please replace AdventureWorks2008R2_Data with your database file name, and replace "C:\newlocation\TEST_Data.mdf" with the new file location in your environment.  
    GO 

Step 5. Run the following script to take the database online.

USE master;
    GO
    ALTER DATABASE TEST SET ONLINE;  -- please replace AdventureWorks2008R2 with your database name.  
    GO

Step 6. Run the script in Step 1 again to verify the database file list  

SQL Script to check space used by all log files

Following script helps us to get

1. Current total Log File size occupied by every database
2. How much space is free inside a log file

-- ***********************************************************************************/
-- Description  :    Script to check space used by all log files  
--                        space used for all log files
-- Compatibility  :     7.0+
-- This Script should be executed in master
-- ***********************************************************************************/
use master
go
Dbcc sqlperf(logspace)
go

SQL Script to Find SQL Server Error Log location

-- ******************************************** */
-- Description                                  */
-- ============                                 */
-- Script to find SQL Server Error Log file location
-- This Script will help you to find currently used SQL Server Error Log file.
-- =============================================*/
-- Compatibility  :     2000+                   */
-- ******************************************** */

SELECT SERVERPROPERTY('ErrorLogFileName')