Software Applications Programming Errors and solutions.

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)

To  add multiple foreign keys in a table from different reference tables in SQL you can try below methods in sql query editor. 1. Alter table table_name add constraint constraint_name foreign key(col_name) references table_name(col_name) GO. 2.Create foreign constraint WITH NOCHECK first then CHECK it with another alter statement.. Make sure to put GO in between each statement.. Just to safe side, SET ANSI PADDING ON on the top and OFF at the button..