Database is Recovery Pending in sql server

If the Database LOG file is  missing in SQL Server then Database is going to be RECOVERY PENDING mode.

First run the sql statement below check to find the name of the database with status

ALTER DATABASE [AdventureWorks] SET MULTI_USER

Msg 5120, Level 16, State 101, Line 24

Unable to open the physical file "D:\MSSQL\DATA\AdventureWorks_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5181, Level 16, State 5, Line 24

Could not restart database "AdventureWorks". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 24

ALTER DATABASE statement failed.

-- Database LOG file is  missing

AdventureWorks_log D:\MSSQL\DATA\AdventureWorks_log.ldf

Use master

go

ALTER DATABASE [AdventureWorks] REBUILD LOG ON(

NAME ='AdventureWorks_log' ,

FILENAME='D:\MSSQL\DATA\AdventureWorks_log.LDF' )

Warning: The log for database 'AdventureWorks' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

-- Now Database is in Restricted_user mode change to Multiuser

ALTER DATABASE [AdventureWorks] SET MULTI_USER

sp_helpdb [AdventureWorks]

Pages and Extents in SQL Server

In SQL server data stored on page. Disk I/O operations are performed at the page level. A single page is consist of 8KB (8192 Bytes) and Eight physical contiguous pages are one Extents.

One Data Page (8KB) = 8192 Bytes

Page Header       =   96 Bytes
Data Rows         = 8060 Bytes
Row Offset Array  =   36 Bytes

Total Bytes per page is 8192 Bytes



If you want to read more about page and extents please check out this below Microsoft links
http://msdn.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
Source : Microsoft

How to create a Snapshot database

sp_helpdb MyTest
go

-- The following script is easiest way to create a Snapshot database. 

CREATE DATABASE MyTest_snapshot_db ON
( NAME = MyTest,
FILENAME = 'D:\MSSQL\DATA\MyTest_snapshot_db.ss' )
AS SNAPSHOT OF MyTest;
go

Note: Remember you can't create a snapshot database using SSMS but you can delete snapshot database using ssms.

How to attach a data file MDF without log file (LDF)

USE master
go
CREATE DATABASE AdventureWorks2008R2
ON (FILENAME = 'C:\MSSQL\Data\AdventureWorks2008R2_Data.mdf') -- change the drive and file path
FOR ATTACH_REBUILD_LOG ;

How to Rename a Database in SQL Server

In sql 2005/2008 you can use ALTER DATABASE statment easily modify database name.

ALTER DATABASE Northwind MODIFY NAME = Northwind _Test

--The database name 'Northwind _Test' has been set.

 Back up the master database after you rename any database.

Restore the Database from the Backup file

-- PART 1
-- Find the Backup file details like Machinename, softwareBuild, collation, backupsize, BackupStartDate
USE MASTER
go
RESTORE HEADERONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE FILELISTONLY
FROM DISK = H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE VERIFYONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Restore the database from the backup file
-- eg. Development database refresh from production copy
RESTORE DATABASE AdventuresDb
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY, REPLACE, STATS = 10

------------------------------------------------------------------ PART 2
-- Find the Backup file orignal File path and Logical filename, Physical filename
USE MASTER
go
RESTORE FILELISTONLY
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
-- Create/Restore the Database from the backup file with move to different locations
-- eg. Development database refresh from production copy

RESTORE DATABASE AdventuresDb
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY,
MOVE 'AdventuresDb_Data' TO 'D:\MSSQL\Data\AdventuresDb.MDF',  MOVE 'AdventuresDb_Log' TO 'E:\MSSQL\Log\AdventuresDb_log.LDF',
STATS = 10

-- sp_helpdb AdventuresDb

Find available space on Data & Log file on sql server database

-- Find free data space on master database
use master

go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

-- Find free data space on database
use AdventureWorks2008 -- ( yourdbname )
go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

-- Find free log space on database
DBCC SQLPERF(LOGSPACE);

How to move SQL Server Databases to new location

When you plan to move user databases files to new location please follow the steps.

1. Make sure users are disconnected and planned down time

2. Make sure to take a Full backup of the databases

3. Find the data files and log files names and current location

use  master
go
SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('My_dbname')

4. Set the database you want to work with offline

ALTER DATABASE My_dbname SET OFFLINE

(OR)  

ALTER DATABASE My_dbname SET OFFLINE WITH ROLLBACK IMMEDIATE
go

 5. Move one file at a time to the new location
ALTER DATABASE My_dbname
MODIFY FILE ( NAME = My_Data, FILENAME = 'E:\MSSQL\Data\My_Data.mdf')
go

 6. You move  the data   (MDF) to new Drive/Location 


7. Set the database online
ALTER DATABASE My_dbname SET ONLINE
go

Data and Log Files configuration

1. There are two types of data files in SQL Server  Primary and Secondary

2. You can have one Data Primary file and 32766 Data Secondary file in SQL 2005
3. Transaction log records database modification you can create multiple log files per database to facilitate faster recovery
4. Filegroup is a logical structure that lets DBAs group data files and mange them as a logical unit
5. Filegroup contains data only, you can not create log file
6. Configuring database files with RAID(Redundant array of inexpensive disks) for better peformance
7. RAID 1 known as disk mirroring, provides a redundant copy of the selected disk, RAID1 improves read performance but can degrade the performance of write operations
8. RAID5 stripes the data across the disk of the RAID but it also adds parity information to provide fault tolerance.
9. RAID10 offer better availability and performance than RAID5
10. RAID10 is recommended RAID system for Transaction log, Data files and Index files
11. Always place Transaction log in RAID10 and Data and Index files on RAID5
12. Do not put Data files on the same drive that contains OS files
13. Put the Transaction log file(LDF) on separate drive from data files
14. Put the Tempdb database on a separate drive possible preferably on RAID 10 or RAID 5

Managing Data Files and Transaction Log files

a. If you have more than one Data Files( MDF) make sure to keep same size of all data files e.g( AdvenDb_Data1.MDF, AdvenDb_Data.NDF)

b. Place the Transaction log file(LDF) on separate Drive

c. Disable auto shrink option

d. Alter database to increase size to make more space for data files

e. Auto growth should be configured correctly

f. Enable Auto Update Statistics &  Auto Create Statistics

g. Monitor disk space on Data file and Transaction Log file Drive

h. Monitor database size and Transaction Log file size set threshold level to get notification

i. Page Verify option set to CHECKSUM

Database Recovery Model and Compatibility Level

Find the Recovery model and compatibility level for all databases on instance.

SELECT [name], recovery_model_desc, [compatibility_level]
FROM sys.databases;

Find Database collation

-- List SQL Server default collation
sp_helpsort

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

select SERVERPROPERTY('Collation')
SQL_Latin1_General_CP1_CI_AS



-- Find a collation name of users database
SELECT CONVERT (varchar, DATABASEPROPERTYEX('database_name','collation'))
eg:
-- find master database collation
SELECT CONVERT (varchar, DATABASEPROPERTYEX('master','collation'))


--List out all Collations
SELECT * from ::fn_helpcollations()

Creating Database Snapshot on SQL Server 2005

When you Creating Database Snapshot on SQL Server 2005 Standard Edtion you will receive the following error message. This feature will available on SQL Server Enterprise Edition only.

--Error
Msg 1844, Level 16, State 1, Line 1
Database Snapshot is not supported on Standard Edition.


-- Script to Create Database Snapshot
CREATE DATABASE NorthWindb_test_Snapshot
ON ( NAME = N'NorthWindb_test_datafile',
FILENAME = N'C:\MSSQL\Data\NorthWindb_test_Snapshot.snap' ) AS SNAPSHOT OF NorthWindb_test;

How to check Transaction log space used in SQL Server Databases?

Displays Transaction Logspace information for all databases in the current instance.

dbcc sqlperf(logspace)
go