Skip to main content

Posts

Database Administrator daily tasks

By
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
Read more

Index rebuilding in SQL

By
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
Post a Comment
Read more

SQL DBA Checklist

By
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.
Post a Comment
Read more

Some Light on Roleback and commit transaction in sql recovery mode

By
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 .
Post a Comment
Read more

Way to find differential backup for SQL Database

By
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 &
Post a Comment
Read more