Wednesday, August 27, 2014

Total user Database size in GB's




--- To fetch the User databases  Size in GBs use below query

SELECT    
DB_NAME(db.database_id) DatabaseName,    
(CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeGB
FROM sys.databases db    
LEFT JOIN (SELECT database_id,
                  SUM(size) RowSize
            FROM sys.master_files
            WHERE type = 0
            GROUP BY database_id, type) mfrows
    ON mfrows.database_id = db.database_id    
LEFT JOIN (SELECT database_id,
                  SUM(size) LogSize
            FROM sys.master_files
            WHERE type = 1
            GROUP BY database_id, type) mflog
    ON mflog.database_id = db.database_id    
        where db.database_id > 5
ORDER BY 1 DESC


---- Results are as below

Operating System Requirements to install SQL Server 2012




SQL Server 2012 edition
Operating system
SQL Server 2012 x64 Enterprise,
Business Intelligence, and Web
Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard, and Web or above
Windows Server 2008 SP2 Datacenter, Enterprise, Standard, and Web or above
SQL Server 2012 x86 Enterprise,
Business Intelligence, and Web
Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard, and Web or above
Windows Server 2008 (x64 and x86) SP2 Datacenter, Enterprise,Standard, and Web or above
SQL Server 2012 x64 Standard
Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above
Windows Server 2008 SP2 Datacenter, Enterprise, Standard, and Web or above
Windows 7 SP1 x64 Ultimate, Enterprise, and Professional
Windows Vista SP2 x64 Ultimate, Enterprise, and Business  
SQL Server 2012 x86 Standard
Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above
Windows Server 2008 (x64 and x86) SP2 Datacenter, Enterprise,Standard, and Web or above
Windows 7 SP1 (x64 and x86) Ultimate, Enterprise, and Professional
Windows Vista SP2 (x64 and x86) Ultimate, Enterprise, and Business
SQL Server 2012 x64 Developer,
Express, Express with Tools, and
Express with Advanced Services
Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above
Windows Server 2008 (x64) SP2 Datacenter, Enterprise, Standard,and Web or above
Windows 7 SP1 (x64) Ultimate, Enterprise, Professional, Home Premium, and Home Basic
Windows Vista SP2 (x64)
SQL Server 2012 x86 Developer,
Express, Express with Tools, and
Express with Advanced Services
Windows Server 2008 R2 SP1 Datacenter, Enterprise, Standard,Foundation, and Web or above
Windows Server 2008 (x64 and x86) SP2 Datacenter, Enterprise,Standard, and Web or above
Windows 7 SP1 (x64 and x86) Ultimate, Enterprise, Professional, Home Premium, and Home Basic
Windows Vista SP2 (x64 and x86) Ultimate, Enterprise, Business, Home Premium, and Home Basic



Tuesday, August 26, 2014

Restoration of N number of backup files at a time

-- Using below script you can restore all databases backup file in single shot backup files should be in C:\Backupfiles location. And backup file stored in below format
--- C:\Backupfiles\Database1\Database1_Timestamp.bak  
--- this script consider Database1 as database name.


-- C:\Backupfiles  : Backup location
--- Backup file location should be like C:\Backupfiles\Database1\Database1_Timestamp.bak

Declare @MinVal Int
Declare @MaxVal   Int
Declare @InLp_MinVal   Int
DECLARE @whichFile VARCHAR(1000);
Declare @backupfilename Varchar(100)
Declare @cmdfolder Varchar(100)
Declare @cmdfilename Varchar(100)
Declare @databasename  Varchar(100)
Declare @SqlString nvarchar(4000)
Declare @restorefile varchar(100)

CREATE TABLE #DBNameAndBakFile (SLNo Int Identity, DBName VARCHAR(100) , BackupFileName Varchar(100) );
Create table #Dboutput (SLNo Int Identity,Bkupfilename varchar(100));
create table #DatabaseRestored (Databasename Varchar(100))

Set NoCount On;
                                
Select @MinVal =1,
                                @MaxVal = 1,
                                @InLp_MinVal = 1
                               
                Select @cmdfolder = 'dir "C:\Backupfiles" /ad /b' --- This is the backups locaiton
                  
                INSERT into #DBNameAndBakFile (DBName) 
                EXEC   master.dbo.xp_cmdshell @cmdfolder  -- This will store the DATABASE NAMES into temp table #DBNameAndBakFile
               
                Select @MaxVal = MAX(SlNo) from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
               
                While @MinVal <= @MaxVal
                                Begin
                                               
                                                Select @databasename = Dbname from #DBNameAndBakFile where Slno = @MinVal ;
                                                Select @cmdfilename = 'dir ' + '"C:\Backupfiles\' + @databasename +'" /b /o:gn | sort' 
                                    
                                    Insert into #Dboutput 
                                    EXEC master.dbo.xp_cmdshell @cmdfilename
                                    
                                    
                                   Update A set BackupFileName = B.Bkupfilename 
                                   From #DBNameAndBakFile  A, #Dboutput B
                                   Where (A.SlNo  =  B.Slno Or A.SLNo = @MinVal)  And (B.Bkupfilename Is Not Null Or  B.Bkupfilename<>'')
           
           Delete from #Dboutput
          
            
                                   select @MinVal = Min(SlNo) from #DBNameAndBakFile Where SlNo > @MinVal ;
                                  
                                End 
                
                                -- Select * from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
               
                                Print '<Restoration Scripts Starts Here......>'
                               
                                Declare @Rst_MinVal Int
                                Declare @Rst_MaxVal Int
               
                                Select @Rst_MinVal = Min(SlNo) from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
                                Select @Rst_MaxVal = MAX(SlNo) from #DBNameAndBakFile Where ( Dbname <>'' Or Dbname Is Not Null)
               
                                
                
                                While @Rst_MinVal <= @Rst_MaxVal
                                Begin
                                               
                                                Select @databasename = Dbname, @restorefile =BackupFileName  from #DBNameAndBakFile where Slno = @Rst_MinVal ;
                                                --Select  @databasename as Dbnaes, @restorefile as Restorefile
                                                Print '<Restoration for the database ......>'
                                                Print @databasename
                                                Insert into #DatabaseRestored values(@databasename )
                                                                               
                                                Select @SqlString  = 'RESTORE DATABASE ['+ @databasename + '] FROM 
                                                DISK = N'''+ 'C:\Backupfiles\'+ @databasename +'\' + @restorefile
                                                + ''' WITH  FILE = 1,  NOUNLOAD,REPLACE,  STATS = 10'
                                               
                                                Exec sp_executesql        @SqlString
                                                --Print @SqlString

                                   select @Rst_MinVal = Min(SlNo) from #DBNameAndBakFile Where SlNo > @Rst_MinVal ;
                                  
                                End 
                                
                                
                
                Drop table #DBNameAndBakFile
                Drop table #Dboutput
                Select * from #DatabaseRestored
                Drop table #DatabaseRestored
               

Wednesday, August 13, 2014

Shrink Database Log file in SQL Server

 

Method 1 : This script will shrink the all log files of specific database by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

  

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where sb.name = 'TEST2008'  AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END

 

Method 2: This script will shrink the all databases log files, depends on the size of log file by  change the recovery modle to simple  and again it rever back the recovery model

 

DECLARE @tbl TABLE

(

      ID INT IDENTITY(1,1),

      DBNAME NVARCHAR(1000),

      [FileName] NVARCHAR(1000)

)

INSERT INTO @tbl

SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles  ST INNER JOIN Sys.databases SB on  ST.dbid = sb.database_id where Size > 100000 AND FileName LIKE '%ldf' and sb.state = 0

 

DECLARE @MinID INT , @MaxID INT,@DBName NVARCHAR(1000),@FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@SQL NVARCHAR(MAX)

 

SELECT @MinID = MIN(ID),@MaxID = MAX(ID) FROM @tbl

 

 

WHILE(@MinID <=@MaxID)

BEGIN

      SELECT @DBName = DBNAME,@FileName=[FileName] FROM @tbl where ID = @MinID

 

      SELECT      @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DBName

 

            SELECT      @SQL= N'USE ['+ @DBName+']'+CHAR(10)+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT'ELSE N''END+CHAR(10)+

                  +N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)

                  +CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DBName+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END

                  +CHAR(10)

      PRINT @SQL

      EXEC SP_EXECUTESQL @SQL

     

      SELECT @MinID = @MinID +1

END

 

Kill all user SPIDs in a SQL Server Database

Using this script you can kill all the SPID connected to TEST2008 database 

 

 

 

DECLARE @DB SYSNAME 

SET @DB = 'Test2008'  -- Here you need to give the database name where you need to kill all connection

 

 

DECLARE @SPID VARCHAR(4), @cmdSQL VARCHAR(10)

 

IF OBJECT_ID('tempdb..#KilledSpidsInfo ') IS NOT NULL

BEGIN

DROP TABLE  #KilledSpidsInfo

END

 

SELECT * into #KilledSpidsInfo FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

select * from #KilledSpidsInfo

 

DECLARE cCursor CURSOR

FOR SELECT CAST(SPID AS VARCHAR(4)) FROM master.dbo.sysprocesses

WHERE SPID > 50 

AND SPID != @@SPID 

AND DBID = DB_ID(@DB) 

 

OPEN cCursor

 

FETCH NEXT FROM cCursor INTO @SPID

 

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmdSQL = 'KILL ' + @SPID

 

 EXEC (@cmdSQL)

FETCH NEXT FROM cCursor INTO @SPID

END

 

CLOSE cCURSOR

 

DEALLOCATE cCURSOR

 

Monday, August 11, 2014

change server level collation for a SQL Server Instance

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales


The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation.  It is not mandatory that we change the default server level collation, because you can specify a different collation level when you create users databases, but you need to remember to specify this when creating user databases.

To change the default SQL Server collation you can simply rebuild the system databases. When you rebuild the master, the model, msdb and tempdb system database are actually dropped and recreated in their original location. If a new collation is specified in the rebuild statement the system databases are rebuilt using that collation setting. Any user modifications to these databases will be lost, so it is important to backup any of this information you wish to retain

Step 1

First check the existing SQL Server collation setting of your instance. Run the command below to get the collation value of your SQL Server instance.
SELECT SERVERPROPERTY('Collation')
Step 2
Make sure to record all server level settings before rebuilding the system databases to ensure that you can restore the system databases to their current settings.  


Step 3

Take backup of all user and server databases.

Take the backup of all jobs, maintenance plans, logins and their access levels
To take all jobs backup in OBJECT EXPLORER window select job folder & in right side OBJECT EXPLORE DETAIL window it displays all jobs, select all jobs and right click on your selection then choose the SCRIPT AS option to create the script for all jobs.

Next is to secure your logins, passwords and their access levels. You can use sp_help_revlogin stored procedure to create a script for all logins so they can be recreated easily. And take server level permissions using script

Step 4

Detach all user databases before rebuilding your system databases. If you leave databases attached they will be detached and will be found in the database folder.

Step 5

Now its time to rebuild your system databases. This operation will recreate your master database and all existing settings will be reset. Run the below command from a Windows command prompt. Make sure to run this command from the directory where you have placed your SQL Server setup files. Once you press enter, a separate window will appear to show you the progress bar. Once the rebuild is done, that window will disappear.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MANVENDRA /SQLSYSADMINACCOUNTS=domain\adminUser /SAPWD= SaPWD@SqlRebuild /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

* SQL_Latin1_General_CP1_CI_AI –this is the new collation need to change

Once the rebuild operation is complete, check the server collation to verify whether this change is successful or not. As we can see in the screenshot below, the server collation has changed to SQL_Latin1_General_CP1_CI_AI. At this point we cannot restore any of the system databases, because doing so will revert back to the previous collation setting. So we will need to use the scripts that were created to recreate logins, jobs

Step 6

Attach all user databases which were detached in Step 4.

Step 7

Now change the collation settings of all user databases. It's not necessary to change the collation settings for the user databases, it totally depends on your requirement.

Run the commands below to change the collation settings of your user databases.

ALTER DATABASE CollationChangeDBName collate SQL_Latin1_General_CP1_CI_AI
Sometimes the command fails to execute and you get this error:

Msg 5075, Level 16, State 1, Line 1 The object 'PK_xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
In that case you may need to export all data and recreate the database with the new collation settings.

Step 8

Now run all of the scripts which were created in Step 3 to restore jobs, alerts, logins, operators, etc...  Also don't forget to change the server level configuration settings which were captured in Step 2.

Now your instance is ready to use the new server level collation.