Script To find files used space in SQL Server

The following script will give you the space used for all files in server:

create  TABLE  #DBInfo
(  
DatabaseName VARCHAR(100), 
FileSizeMB INT, 
LogicalFileName sysname, 
PhysicalFileName NVARCHAR(520), 
FreeSpaceMB INT, 
FreeSpacePct VARCHAR(7), 
FreeSpacePages INT, 
PollDate datetime) 


DECLARE @command VARCHAR(5000) 
SELECT @command = 'Use [' + '?' + '] SELECT 

' + '''' + '?' + '''' + ' AS DatabaseName, 
CAST(sysfiles.size/128.0 AS int) AS FileSize, 
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, 
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, 
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, 
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) 
AS decimal(4,2))) AS varchar(8)) + ' + '''' +  '''' + ' AS FreeSpacePct, 
GETDATE() as PollDate FROM dbo.sysfiles' 
INSERT INTO #DBInfo 
   ( 
   DatabaseName, 
   FileSizeMB, 
   LogicalFileName, 
   PhysicalFileName, 
     FreeSpaceMB, 
   FreeSpacePct, 
   PollDate) 
EXEC sp_MSForEachDB @command 

SELECT 

   DatabaseName, 
   FileSizeMB, 
   LogicalFileName, 
   PhysicalFileName, 
    FreeSpaceMB, 
   FreeSpacePct, 
   PollDate 
FROM #DBInfo 
ORDER BY 

   DatabaseName 

drop table #DBInfo 

Script to get the ETA for the Backup & Restore

 Hi Friends, Below script will give you the estimate completion time of Backup, Restore, Index Rebuild e.t.c.

SELECT command,

            s.text,

            start_time,

            percent_complete,

            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(S), '

                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

            CAST((estimated_completion_time/3600000) as varchar) + ' hour(S), '

                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

File & File Group Fill Strategy

Hi, Recently I faced one situation, i.e., my database is having two data files and each data file is there is different drives and each drives are having almost same space. On week end application team did some transaction on my database so it started growing, and I can see only one drive is getting full but another drive didn't, it still have lot more free space than drive one. I was surprised, two data files are placed on each drive but only one drive is getting full. Later I realized this was because of initial size of a data file was set to two different sizes.

• File groups use proportional fill strategy across all the files within each file group.
• As data is written into the file group, SQL Server database engine writes an amount proportional to the free space in the file to each file with in the file group, Instead of writting all the data to first file until full, It then writes to the next file.
• For ex, if file f1 has 100mb free and file f2 has 200mb free space, Once extent is allocated for f1, two extents for f2 and so on.
• In this way both files became full at about the same time.
• As soon as all the file in the file group are full, the database engine automatically expands on file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically.
• For ex. a file group is made up of three files, all set to automatically grow.
• When space in  all files in the file group is exhausted, only the first file is expanded.
• When the first file is full and no more data can be written to the file group, the second file will expand
• When the second file is full and no more data can be written to the file group, the 3rd file is expanded. If 3rd file is full and no more data can be written to the file group, the first file is expanded again, and so on.

Hope this info is useful to you...

Auto Restore Script

Hi, Today I am going to share you  most useful script for restoring a database from backup file.

Suppose if your database having more number of data & log files it will be bit tough to restore using with MOVE option. Below script will generate the restoration script.

use [ReportServer]  --current database
go
declare @dbname varchar(100)
declare @filepath varchar(500)
--declare @islightspeed int
set @dbname ='ReportServer'  --databasename
set @filepath ='\\mhcalbq34clu1bu\BACKUP\DAILY_BACKUP\AANGSQL\QNXT_REPORTDB_WA_DIFF_DB.BAK'  --backup file path
--set @islightspeed =1  -- =1 lightspeedscript else SQL script
--if @islightspeed =0
begin
select 'restore database '+@dbname +' from disk ='+''''+@filepath +'''  with'
 union all
select 'move' +''''+name +'''' +' to ' +''''+physical_name + ''',' from sys.database_files
union all
select 'replace,stats=10'
end
-- else
-- begin
-- select 'exec master.dbo.xp_restore_database @database =N'+''''+@dbname +''''+',
--'+ '@filename = N'+''''+@filepath +''','
--union all
--select '@filenumber = 1,
--@with = N''REPLACE'',
--@with = N''STATS = 10'','
--union all
--select  '@with = N''MOVE N''''' + name +'''''' +' To N''''' + physical_name +''''''',' from sys.database_files
--union all
--select '@affinity = 0,
--@logging = 0'
--end

Blocking In SQL Server

Let say one session has placed a lock which will not allow other sessions to access the same resource (table or row or page). The other session query waits for unlimited time till the previous session releases the lock. This scenario is called blocking.

When a transaction is trying to place any type of lock (shared, exclusive, update) on a resource on which already exclusive lock is placed, then it results into blocking.

Certain amount of blocking is normal and unavoidable but regular blocking or blocking which causes the applications or users to wait for unlimited times should be consider. We have to minimize blocking in order to reduce the performance bottlenecks. Blocking causes timeout problems for applications.

Example
 

In the above figure check that session1 has placed the exclusive lock on the row with pid=1 and second session is trying to access the same row. The second session is currently executing and waiting for the first session to release the lock. Hence session2 is blocked by session1.

SELECT command causes any blocking?

                When select command is executed it places Shared Locks and restricts other queries to perform manipulations till the lock is released. But this lock does not cause too much blocking. But locks placed by insert or update or delete as we have seen in the previous example causes excessive blocking.

Scenarios where sessions experience with excessive blocking

1.       Long running queries which places Shared or Exclusive locks.

2.       Queries which are cancelled and not rollback.

3.       In some cases, lock escalation also one reason for blocking because SQL has escalated locks, and so locked the entire table.

4.       When the process of index rebuilding is running.

5.       Snapshot agent is generating fresh snapshot in case of Snapshot of transactional replication.

FAQ: - While tracing server activities using profiler, any blocking occurs?

                While using tuning template in profiler causes blocking.

Monitoring blocking

                SQL Server supports stored procedures and DMVs (Dynamic Management Views) to monitor and troubleshoot blocking issues. Using DMVs as well as event notifications, and the blocked_

process_report and lock_escalation events, we can automate notification when a

blocking event occurs.

            To find blocking we can use sp_who or sp_who2.

 

In the above picture, we can check that session 53 is blocking session 55.  

 

Alternatively we can use sysprocesses of master database.

  

Steps to reduce blocking

1.       Keep the workload small as possible

a.       Avoid INSERTing, UPDATEing, or DELETEing large numbers of records in a single transaction.

2.       Ensure that your tables have appropriate indexes

a.       For the above indexes specially clustered we can set Online Index Processing to true.

3.       Use NOLOCK hint or read uncommitted isolation level

a.       We can make use of NOLOCK option to read the rows and provide more concurrency.

Using SNAPSHOT ISOLATION LEVEL