Software Applications Programming Errors and solutions.

What is Full quorum in mirroring in SQL Server? Ans:Full quorum forms when both the partners with witness are connected and performing their roles. “full quorum” means that all three roles are available and all is going swimmingly.

What is system configuration of your cluster servers in SQL? Ans:If I have around 250gb physical and 30 gb allocated forsql , win 2008 sp2 ,they are physical. You should know about this,System configuration means RAM Size and How many core/processor are in CPU.Apart from this what is the OS version

To upgrade for Active & Active cluster server Active to active means you will install SQL server on each node so they will be active to active. First transfer both sql server service on node A and upgrade Node B then transfer both services on node B and upgrade node A and then split the service on separate nodes(Node A & B)

SQL Error:  Database-Unavailable database  :testdb is unavailable(suspect,offline,recovering etc) 1.If the database is offline, the approach should be to bring the database online by either from GUI or from query : ALTER DATABASE <database_name> SET ONLINE and if it still doesn't come up..we should check the logs for the reasons..and based on the outcome we have to proceed accordingly. 2.If the Database is offline, then try to make the database online using the script ALTER DATABASE <database name> SET ONLINE. If it is not recover the database to online, then check in event viewer for possible error, if error is 17204--then data file has damaged,if error is 17207--- then log file has damaged,If you see this error then the database is there in suspect state. To Recover the suspect state database u need to follow steps as below: If data file damaged: 1. Take the tile log back up. 2.Restore Full back up with no recovery. 3.Restore Differential & Log Backups

To Shrink TempDB in Sql Server below ideas can help you to handle the issue. If it is data file then Instead of shrinking you should check the open transactions causing tempdb to get full..if it is not critical you can kill the process or else should add secondary data files. Shrinking log whereas is safe witch you can do by dbcc shrinkfile command. 1)If mdf file is full we can shrink that but sometimes we can’t shrink mdf file the reasons are: (i)check if any locks are there through (sys.dm_tran_locks)or sp_locks (ii)check for sys.dm_db_session_space_usage which will allows to check for page allocation and deallocation for each session on that instance (iii)check for temporary objects in tempdb using (select * from sys.all_objects where is_ms_shipped=0)-ser created temp objects. These are called as cached objects which are residing in procedure cache which helps stored procedure to make use of the query plan. But now it is not getting shrinked we have to perform DBCC FREEPROCCACHE wh

How to change time to be 1:30:00 am/pm Instead of 13:30:00 ? You can try below queries. SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2) declare @time time = '13:30:00' SELECT convert(varchar, @time, 22)

If the database is in single user mode then You can bring that database to normal state following way. ALTER DATABASE Database_Name SET MULTI_USER Go to ssms ->right click on database properties--> select options then scroll down to "Restrict Access" then change it MULTI_USER

Service Pack, Cumulative Update and Hot Fix are all patches to the bugs faced by Users/Vendors. Severity and No. Of Users facing problem will flow down from Higher to Lower i.e, Service Pack (Higher) --> CU (Medium) --> Hot Fix (Lower).

These are the task list that must be done by a DBA on daily basis. 1) Check System Event Logs and SQL Server Error Logs for unusual events. 2) Verify that all scheduled jobs have run successfully. 3) Confirm that backups have been made and successfully and moved to the backup storage or secure location. 4) Monitor disk space to make sure SQL Servers will not run out of disk space. 5) Periodically monitor performance of database and system using both System Monitor and SQL Server Profiler. 6) Monitor and identify blocking issues. 7) Keep a log of any changes you make to servers, instance setting, database setting and monitoring script. 8) Create SQL Server alerts to notify you of problems, and e-mailed to you. Take action as  needed. 9) Regularly restore backups to a test server in order to verify that you can restore them. Summary: Installing DB software Creating databases Performing upgrades of the database and software to new release levels Starting u

When index are rebuild that means the old indexes on the table are dropped and new indexes on the table are created. Due to the creation of the indexes on the table all the data is organised in the leaf level (cluster index) and organised simultaneously. Thus maximum the number of rows in your table , maximum will be the page count and maximum will be the time taken in rebuilding the index. Indexing is nothing but ordering of pages, thus it depends on page count

Data center is used to store all our servers and need to maintain (power supply,network,connectivity,AC and others) those servers from the DC in a specified safe environment with all the facilities.. without proper approvals even CEO will also not allowed into the DC's for some security reasons.

Following can be SQL DBA Checklist on daily,weekly and monthly basis. 1.SQL DBA daily checklist can include Database backup status, Database integrity checks, failed jobs. 2.Weekly checklist can include index reorganization, update stats etc . 3.Monthly checklist can include Restoration drill for critical databases to ensure backup's availability in case of any unusual happening etc.these are general points.although it depends on current environment and issues how you define your checklist as a DBA.

If log truncation is performed when your transaction is still running then it will truncate only committed transactions and there is no impact on the current running transaction. if your transaction is completed and after that log truncation is performed it will clear your transaction. SQLServer internally maintain some lsn or modified pages details in one page so if you rebooted the server, if transaction is completed then changes applied into mdf files and there is no change in the DB and it will online after reboot. But if you rebooted the server in the middle of transaction then after server is online , database went into recovery mode and it will rollback all changes as per the changes after all the rollback completes DB will come into online .

To  change authentication mode in SQL Server for mixed mode or windows authentication mode you can try as below. Mixed Mode (SQL Server Properties --> Security --> Server Authentication --> SQL Server and Windows Authentication Mode--> check the Radio button

To set PLE(page life expectancy) in Sql server you can get help from following way..PLE is used to faster access if data who pages are in buffer. sp_reconfigure 'page life expectancy' newvalue

To convert excel sheet data into SQL Table you can use import and export wizard Sql server management studio ->choose the Database and right click on Database->Tasks->Import Data/Export Data will be avvailable on All sql server version then you can do import/export process

1.Check MSDB backup history for that database select * from msdb.dbo.backupset where name='dbname' and type='i' order by backup_finish_date desc 2.dbo.backupset: provides information concerning the most-granular details of the backup process dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets dbo.backupfile: this system view provides the most-granular information for the physical backup files USE AdventureWorks GO SELECT TOP 100 s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN &

To change log file auto growth restricted growth to unrestricted growth in SQL you can try option below. I hope it will help you. Right click on "dbname" ->Properties ->Click on files ->you will  find options there

1>data  2>index  3>text/image  4>GAM/SGAM  5>page free space 6>IAM  7>BCM  8>DCM

These queries can help you  To Identify The Long Running Queries in SQL. 1.SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where total_elapsed_time >= 800000000 ORDER BY total_elapsed_time / execution_count DESC; 2.DBCC opentran(This command will display the longest running transactions on the database) and select * from sys.dm_tran_active_snapshot_database_transactions(this command will display the transactions about replication and mirroring and snapshot isolated related transactions)