Posts

Database File Size Stats

--Using original Query:   select DB_NAME ( database_id ) as dbname , m . name , m . physical_name , m . type_desc , m . size / 128.0 "Initial Size (MB)" , d . size / 128.0 "current Size (MB)" , ( fileproperty ( m . name , 'spaceused' ))/ 128.0 "Used (MB)" from sys . master_files m , sys . database_files d where database_id = DB_ID() and m . file_id = d . file_id --Using sp_MSforeachdb: exec sp_MSforeachdb N'USE ? select DB_NAME(database_id) as dbname, m.name, m.physical_name, m.type_desc, m.size / 128.0 "Initial Size (MB)" , d.size / 128.0 "current Size (MB)", (fileproperty(m.name, ''spaceused''))/128.0 "Used (MB)" from sys.master_files m, sys.database_files d where database_id = DB_ID() and m.file_id = d.file_id' --Using Cursor:   IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable;  PRINT'check for user database fi
Post a Comment
Read more

Mirroring: Fixing/Removing/Recreating/Pausing/Querying

Start/Stop Mirroring EndPoints: ALTER ENDPOINT Mirroring STATE = STOPPED ALTER ENDPOINT Mirroring STATE = STARTED select * from sys.endpoints; select * from sys.database_mirroring_endpoints; SELECT    d.name as 'td','',    mirroring_state_desc as 'td','',    mirroring_role_desc as 'td','',    mirroring_partner_instance as 'td','',    mirroring_role_sequence as 'td','',    case (m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'       else m.mirroring_witness_state_desc end as 'td','' ,    case(m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'       else m.mirroring_witness_name end  as  'td','',    log_reuse_wait_desc  as 'td','' FROM   sys.database_mirroring M inner join SYS.DATABASES d   on m.database_id = d.database_id WH
Post a Comment
Read more

Some useful windows command for SQL DBAs

Finding Uptime systeminfo | find /i "Boot Time" System Boot Time:          14/12/2017, 7:48:04 AM Cluster Command cluster resource /status cluster node /status cluster group /status Finding Processor Info for the total number of cores echo %NUMBER_OF_PROCESSORS% Go to RUN --> msinfo32 Finding Memory Info Windows Cmd: wmic ComputerSystem get TotalPhysicalMemory && wmic OS get FreePhysicalMemory,TotalVirtualMemorySize,FreeVirtualMemory Results will be in bytes Powershell Cmd: (Get-WMIObject Win32_PhysicalMemory |  Measure-Object Capacity -Sum).sum/1GB For Remoting: For ($x in serverlist.txt) { $colItems = get-wmiobject -class "Win32_ComputerSystem" -computername $x foreach ($objItem in $colItems){ $displayGB = [math]::round($objItem.TotalPhysicalMemory/1024/1024/1024, 0) write-host "Total Physical Memory: " $displayGB "GB" write-host "Model: " $objItem.Model } $colItems = get-wmiobje
Post a Comment
Read more

Joining DB to AOAG

Steps: 1. Disable backup jobs 2. Go to Primary node. Take a Full and TLog backup. Copy it over to the secondary. 3. Restore the full with Recovery option in secondary. Then change the owner to 'SA'. Restore the DB again. It will not change the owner any more. Another way to do it - login as 'SA' in the secondary node and then restore it. 4. Restore the TLog after that in No Recovery Mode. 5. Go to the Primary again. Right click on the AOAG name and select add databases in the AOAG. 6. In the AOAG config GUI select join only and proceed. 7. The new databases should be joined now. Things to remember: 1. Check the owner in both nodes. it should be 'SA'. If not it needs to be changed. Another approach is to restore with command Execute as login = 'sa'; GO USE master; GO -- First determine the number and names of the files in the backup. -- AdventureWorks2012_Backup is the name of the backup device. RESTORE FILELISTONLY FROM Ad
Post a Comment
Read more

From Warm-Standby to normal Read-Only mode

use master go -- take the user database in single_user mode first alter database [db_name] set single_user with rollback immediate ; --Check and Kill user sessions connected to that database --Restoring the database restore database [db_name] with recovery ;   It may fail if no .tuf is there for this database. restore   database  [db_name]  with   recovery, continue_with_error ;  Please do a DBCC CHECKDB after this and see any error is seen. Think twice before put it in Production DB.  --Reverting back to MULTI USER mode alter database [db_name] set multi_user with rollback immediate ; --making the db READ ONLY alter database [db_name] set read_only with no_wait ; T UF file stands for Transaction Undo File in SQL Server In log shipping scenario if we choose “Standby” option for the secondary server then the transaction log backup file would be restored in standby mode. As discussed earlier, this would need a TUF fil
Post a Comment
Read more

Backup and Restore DB in multiple channels

Image
Before taking the backup please check the database size CREATE TABLE #databases ( DATABASE_NAME VARCHAR ( 50 ), DATABASE_SIZE FLOAT , REMARKS VARCHAR ( 100 ) ) INSERT #Databases EXEC ( 'EXEC sp_databases' ); SELECT @@SERVERNAME AS SERVER_NAME ,        DATABASE_NAME ,        SYSMFM . source_file_name_main ,        SYSMFM . physical_name_main ,        SYSMFL . source_file_name_log ,        SYSMFL . physical_name_log ,        DATABASE_SIZE AS '(KB)' ,        ROUND ( DATABASE_SIZE / 1024 , 2 ) AS '(MB)' ,        ROUND (( DATABASE_SIZE / 1024 ) / 1024 , 2 ) AS '(GB)' ,        SYSDB . compatibility_level ,        SYSDB . create_date ,        SYSDB . database_id ,        SYSDB . collation_name ,        SYSDB . recovery_model ,        SYSDB . recovery_model_desc ,        SYSDB . user_access ,        SYSDB . user_access_desc ,        SYSDB . state ,        SYSDB . state_desc   FROM #da
Post a Comment
Read more

Log is not getting shipped in SQL Server Logshipping

Image
Well Problem could be of different types. But common issues are few. Cause Inside the LSRestore job history, you can find out two kind of messages  :  – Restore job skipping the logs on secondary server  Skipped log backup file. Secondary DB: ‘logshippingSecondary’, File: ‘\\myDB\logshipping\logshippingPrimary_20090808173803.trn’ – Backup log older is missing *** Error 4305: The file ‘\\myDB\logshipping\logshippingPrimary_20090808174201.trn’ is too recent to apply to the secondary database ‘logshippingSecondary’. **** Error : The log in this backup set begins at LSN 18000000005000001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000004900001 can be restored. Transaction Log backups can only be restored if they are in a sequence. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for tran
Post a Comment
Read more