SP to find the Drive Space,free and percentage Using T-SQL

use master

go

CREATE PROCEDURE sp_diskspace

AS

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char(1)

DECLARE @odrive int

DECLARE @TotalSize varchar(20)

DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives

SET TotalSize=@TotalSize/@MB

WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,

FreeSpace as 'Free(MB)',

TotalSize as 'Total(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'

FROM #drives

ORDER BY drive

DROP TABLE #drives

RETURN

go

You will find output like

Grant Execute Permission on all SP for a database

Following method can be used to create statement to grant Execute permission on all SP for a Database.

Use DatabaseName

select 'Grant Execute on [' + name + '] to [LoginName]'

from sysobjects where xtype in ('P')

Create a user/assign role for a Single Database

We DBA have a great responsibility to avoid access to unauthorized access to users. So we can give access to users for Required Databases only. Following are the method to create/assign used for a single DB.

USE [DatabaseName]

GO

CREATE USER [LoginName] FOR LOGIN [LoginName]

GO

USE [DatabaseName]

GO

ALTER USER [LoginName] WITH DEFAULT_SCHEMA=[dbo]

GO

USE [DatabaseName]

GO

EXEC sp_addrolemember N'db_owner', N'LoginName'

GO

Rename Database from T-SQL

Rename Database

Sometime we rename database as per business Logic.

USE master

ALTER DATABASE DatabaseName

SET single_user

GO

ALTER DATABASE DatabaseName

MODIFY NAME = NewDatabaseName

GO

USE master

ALTER DATABASE NewDatabaseName

SET multi_user

GO

Backup & Restore from TSQL

Database Backup and Restore from TSQL

For Native Backup

Database MYDatabase has full backup DatabaseName.bak. The database can be restored using following two steps.


Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY

FROM DISK = 'D:\Backup\DatabaseName.bak'

GO

Step 2: Use the values in the LogicalName Column in following Step.
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE MYDatabase
FROM DISK = 'D:\Backup\DatabaseName.bak'
WITH MOVE 'MDF_LogicalName' TO 'D:\MSSQL\Data\DBFileName_Data.mdf',
MOVE 'LDF_LogicalName' TO 'D:\MSSQL\Log\DBFileName_Log.ldf'

ALTER DATABASE YourDB SET MULTI_USER
GO

To backup database

Backup database Database_Name to Disk='D:\Backup\DatabaseName.bak' with stats=10
===============================================================
For LiteSpeed

--To Check the logical name of the file

exec master.dbo.xp_restore_filelistonly @filename = 'path of the database backup.bak'

i.e logical name like- Database_Data, Database_Log

--To Find the path where mdf and ndf resides for the said database.

--select * from sysaltfiles

sp_helpdb 'DatabaseName'

--To Restore database from given backup file

exec master.dbo.xp_restore_database @database = N'DatabaseName',

@filename = N'path of the database backup.bak',

@with = N'RECOVERY', @with = N'NOUNLOAD', @with = N'STATS = 10',

@with = N'MOVE N'' Database_Data '' TO N''N:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DatabaseName.mdf''',

@with = N'MOVE N'' Database_Log '' TO N''K:\Microsoft SQL Server\MSSQL\Logs\ DatabaseName.ldf'''

===============================================================
For SQL Safe

Backup Database

exec master..xp_ss_backup

@database = 'DatabaseName', @filename = 'D:\Backup\DatabaseName_FUll_Date.safe', @init = 1
, @compressionlevel = 'isize', @threads = 3

To verify backup file
xp_ss_listfiles @filename='D:\DB_Backup\DatabaseName_FUll_Date.safe'

Restore Database

exec master..xp_ss_restore
@database = 'Database_Name',
@filename='D:\DB_Backup\DatabaseName_FUll_Date.safe',
@withmove = 'DB_MDF_LOgicalName D:\MS SQL Server\Data\DBName.mdf'
,@withmove = 'DB_LDF_logicalName_Log F:\MS SQL Server\Logs\DBName.ldf'
,@withmove = 'DB_LDF_logicalName_Log1 F:\MS SQL Server\Logs\DBName1.ldf'
,@withmove = 'DB_Catalog_FT E:\MS SQL Server\FText\DB_Catalog_Production_FT'
,@replace = 1

==================================================================
Below are method to find orphan User and Way to fix it
--To Verify User SID matches system SID

sp_change_users_login 'report'

--If you find any Orphen user with SYSTEM SID, then we can fix with Following Command

sp_change_users_login 'update_one','username','username'

==================================================================

Query to find out Estimated Time, Percentage, Elapsed time of a RESTORE DATABASE/BACKUP DATABASE

SELECT r.session_id ,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]

,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2

,CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))FROM sys.dm_exec_requests r

WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Move master database from one drive to another

Sometime you may need to move master database from one drive then you may help from given below info

Open SQL Server Configuration manager from program files. In SQL Services Nodes, right clieck on SQL Server(i.e.MSSQLSERVER) and choose Properties and go to Advanced TAB and Edit Startup Parameters values to point to planned location for the master database data abd log files and then click on ok.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf


If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

stop the instance by right click and choose stop and move the master.mdf and master.ldf to new location.

and restart the instance.

Verify the file change for the master database by running the following query.

CopySELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO

Move TempDB from one drive to another drive

Sometime you may face some problem related to Tempdb like Tempdb is full or it's affecting drive then that we need to move tempdb from one drive to another.
Please follow following steps for that.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

after executing above command. Please restart the SQL server Servvices.

To varify the location of the tempdb please execute following commands

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

Split Function in Separated by delimeter in a Strings of a column

Some time we need to extract the values from a string that time we use to do a lots of RND that time we can use given below function. This function is similar to the VB split function. It takes a nvarchar delimeted list and delimeter and returns a table with the values split on delimeter.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (Data varchar(8000))

as

begin

declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1>

while @idx!= 0

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0

set @slice = left(@String,@idx - 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)

if len(@String) = 0 break

end

return

end

How to use this function?

Ok Let me explain

we have column jobids in a table TABLE1 which is a string. We have to extract the data separated with comma.

select * from Table1

Result

300-35,300-34

Now we have to separate the delimited by comma.

Declare @jobids as varchar(50)

select @jobids=jobids from Table1

select * from dbo.split(@jobids,',')

Result-

Data

300-35

300-34

Restoring Differential backup to database

To restore a differential backup, you must first restore a full backup while ensuring that the databases is NOT recovered. The most recent differential backup is then applied to the database.

RESTORE DATABASE PUBS FROM DISK='C:\DEMO\BACKUP\PUBSFULL.BAK' WITH NORECOVERY

RESTORE DATABASE PUBS FROM DISK='C:\DEMO\BACKUP\PUBSDIFF.BAK' RECOVERY

The first command restore Full backup, leaving the database unrecovered. The second command applies a differential backup and then recovers from database.

List all unused indexes

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName
FROM sys.indexes I
WHERE -- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- find all indexes that exists but are NOT used
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- limit our query only for the current db
AND database_id = DB_ID())
ORDER BY SchemaName, ObjectName, IndexName

Find Size all Database with Path

Find the Size of Ldf File
Total Size of All LDF File
SELECT sum(size*(8192/(1024.*1024)))/1024 FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =2
Total Size of Each LDF Files
SELECT NAME,((size*(8192/(1024.*1024))) /1024)*100, FILENAME FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =2
Find the Size of MDFFile
Total Size of All MDF File
SELECT sum(size*(8192/(1024.*1024)))/1024 FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =1
Total Size of Each MDF Files
SELECT NAME,((size*(8192/(1024.*1024))) /1024)*100, FILENAME FROM master..sysaltfiles WHERE master..sysaltfiles.name NOT IN ('master','mastlog','modellog','MSDBData','tempdev') AND master..sysaltfiles.fileid =1

Create a User AND assign Permision

--Create a User
--Set DatabaseName as the current database
Use DatabaseName
Go
--Changed database context to 'DatabaseName'.
---- Create a new server login name: Amit
CREATE LOGIN Amit WITH PASSWORD=’password’
Go
-- Create a new database user linked to the login name
CREATE USER Amit FOR LOGIN Amit;
-- Grant database ALTER permision to the user
GRANT ALTER To Amit;
Go
-- Grant database CONTROL permision to the user
GRANT CONTROL To Amit;
Go

Grant EXECUTE Permissions on all Stored Procedures to a Single User

-- I've updated this with Aaron Bertrand's suggestions from the comments.
-- Thanks to Aaron for helping make this better!
DECLARE @sql AS NVARCHAR(MAX);
DECLARE @newline AS NVARCHAR(2);
DECLARE @user_name AS NVARCHAR(100);
DECLARE @sproc_name_pattern AS NVARCHAR(10);
SET @sql = N''
SET @newline = NCHAR(13) + NCHAR(10);
SET @user_name = N'jeremiah';
-- escaping _ prevents it from matching any single character
-- including the wildcard makes this much more portable between DBs
SET @sproc_name_pattern = N'sproc[_]%';
-- using QUOTENAME will properly escape any object names with spaces
-- or other funky characters
SELECT @sql = @sql
+ N'GRANT EXECUTE ON '
+ QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.'
+ QUOTENAME([name])
+ N' TO '
+ QUOTENAME(@user_name)
+ N';'
+ @newline
+ @newline
FROM sys.procedures
WHERE [name] LIKE @sproc_name_pattern;
-- this is my version of debug code, I usually run it once with the PRINT intact
-- before I actually use sp_executesql
--PRINT @sql;
EXEC sp_executesql @sql;

Reindex all database

declare objcur cursor for select name from sys.objects where type = 'u' order by name
declare @obj sysname
open objcur
fetch next from objcur into @obj
while @@fetch_status = 0begin print @obj dbcc dbreindex( @obj )
fetch next from objcur into @obj
end
deallocate objcur

SQL Server Architecture

Last month i was taking interview for the post of DBA. I think, DBA should have knowledge of SQL Server Architecture. I had asked so many questions related to SQL Server Architecture but none of the DBAs had given me proper answer. That is why i want to explore SQL Server Architecture information to new DBAs and Developers.

SQL Server Architecture

We can divide SQL Server into three parts.

1. External Protocols
2. Database Engine
3. SQLOS API

External Protocols

There are four parts of External protocols of SQL Server

1. Shared Memory
2. Named Pipes
3. TCP/IP
4. Virtual Interface Adapter(VIA)

Database Engine

We can divide Database engine into two parts

1. Storage Engine
2. Relational Engine(Query Processor)

Storage Engine

The Storage Engine contains some managers to manger storage engine as well as some Utilities and Access Methods

Mangers

1. Transaction Services-
2. File Manager-
3. Buffer Manager-The buffer manager manages disk I/O functions for bringing data and index pages into the data cache so data can be shared among users.
4. Lock Manager- To under stand Lock Manager. We need to understand the SQL Server Lock.

Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce unnecessary deadlocks and blocking. New lock requests where the requestor does not yet own a lock on the resource become blocked if the requested mode is incompatible with the union of granted requests and the modes of pending requests.

A conversion request becomes blocked only if the requested mode is incompatible with the union of all granted modes, excluding the mode in which the conversion request itself was originally granted. A couple exceptions apply to these rules; these exceptions involve internal transactions that are marked as compatible with some other transaction.

Requests by transactions that are compatible with another transaction exclude the modes held by the transactions with which they are compatible from the unions just described. The exclusion for compatible transactions means that it is possible to see what look like conflicting locks on the same resource (for example, two X locks held by different transactions).

The FIFO grant algorithm was significantly relaxed in SQL Server 2005 compared to SQL Server 2000. This relaxation affected requests that are compatible with all held modes and all pending modes. In these cases, the new lock could be granted immediately by passing any pending requests. Because it is compatible with all pending requests, the newly requested mode would not result in starvation. In SQL Server 2000, the new request would not be granted, because, under its stricter FIFO implementation, new requests could not be granted until all previously made requests had been granted. In the following example, connections 1 and 3 would be granted when run against SQL Server 2005 in the specified order. In SQL Server 2000, only connection 1 would be granted:
Utilities

• Bulk Load
• DBCC
• Backup/ Restore

Access Methods

• ROWS
• INDEXES
• VERSIONS
• PAGES
• ALLOCATIONS

Relation Engine(

Query Processor)

In simple words,

It is used to parse, compiles and optimized the Query.

SQL statements are the only commands sent from applications to SQL Server 2005. All the work performed by an instance of SQL Server is the result of accepting, interpreting, and executing SQL statements. The processes by which SQL statements are executed by SQL Server include the following:

• Single SQL Statement Processing
• Stored Procedure and Trigger Execution
• Execution Plan Caching and Reuse
• Parallel Query Processing

Relational Engine(Query Processor) further divided in to five parts.

• Parser, Optimizer-If we execute any query then there are various steps involved in executing the query such as Step1-

  Lexical phase - Here Code break up will happen at this stage. All the code breaks up as individual tokens. like int a. it will get divided into "int" and "a" Step-2>

  Syntax analysis- Here my code gets checked for its syntax.Step 3->

  Semantic phase - Here the semantics will be checked. Step4->

  Intermediate code generation - Its the fourth stage where an intermediate code will be generated by the compiler its like converting the high level code to machine level code.Step5->

  Code Optimization - My compiler will holds some optimization technique to optimize the code so that we can achieve effective execution of our code. Step6->

  Optimized code generation - After code optimization a final code will be generated which can be used to retrieve the data. In sql server this optimized code will be stored and used frequently to fetch the data from the database.Code execution - The optimized code gets executed against the database engine.
• SQL Manager-
• Database Manager
• Query Executer

SQLOS API

We can devide SQLOS API into two parts

1. SQLOS
2. External Components(Hosting API)

SQL OS

There are seven major components of SQLOS

1. Lock Manager
2. Synchronization Services
3. Thread Scheduler
4. Buffer Pool
5. Memory Manager
6. I/O Manger
7. Workers Threads

Buffer Pool-All memory not used by another memory component remains in the buffer pool to be used as a data cache for pages read in from the database files on disk. The buffer manager manages disk I/O functions. When other components require memory, they can request a buffer from the buffer pool. A buffer is a page in memory that's the same size as a data or index page. You can think of it as a page frame that can hold one page from a database. Most of the buffers taken from the buffer pool for other memory components go to other kinds of memory caches, the largest of which is typically the cache for procedure and query plans, which is usually called the procedure cache.

Top 10 SQL Server 2008 Features for the Database Administrator

• Activity Monitor-. SQL Server 2008 Activity Monitor consolidates information about what processes are executing and where is the problem by detailing running and recently executed processes

• [SQL Server] Audit- SQL Server Audit provides automation that allows the DBA and others to enable, store, and view audits on various server and database components

• Backup Compression

• Central Management Servers-Central Management Servers allow the DBA to register a group of servers and apply functionality to the servers

• Data Collector and Management Data Warehouse

• Data Compression-The ability to easily manage a database can greatly enhance the opportunity for DBAs to accomplish their regular task lists.

• Policy-Based Management-Policy-Based Management (PBM) provides DBAs with a wide variety of options in managing their environment. Policies can be created and checked for compliance.

• Predictable Performance and Concurrency-SQL Server 2008 provides a few feature changes that can help provide more predictable performance.

• Resource Governor-SQL Server 2008 introduces a new feature called Resource Governor, which helps address this issue by enabling users to differentiate workloads and allocate resources as they are requested.

• Transparent Data Encryption (TDE)-

  SQL Server 2008 offers a solution to this problem by way of transparent data encryption (TDE). TDE performs real-time I/O encryption and decryption of the data and log files by using a database encryption key (DEK). The DEK is a symmetric key secured by using a certificate stored in the master database of the server, or an asymmetric key protected by an Extensible Key Management (EKM) module.