Skip to main content

Posts

Showing posts from November, 2014

Suggestions to get rid of from recovery mode of SQL database

These are just some Suggestions to get rid of from recovery mode of SQL database. Mostly suspension of database made due to any long process and long suspended query which generating deadlock and infinite loop for your database. Try these option. A. Alter database dbname set emergency Alter database dbname set single_user dbcc checkdb(‘dbname’,repair_allow_Data_loss) alter database dbname set multi_user B. we have to run dbcc checkdb only in the third step,because User does not want to loose the data. If that don't works, then we have to use allow data loss. but first option is better to restore with available backup, if back up is not there then we have to use this method. C.First find which file is corrupted, if Data file corruption immediately take Tail log backup then restore full backup followed by differential , log backups with no recovery finally restore tail log backup with recovery. D. 1. Ensure that the device file is actually available. 2. Use the supplemental stored

Finding Reasons for Slow SQL Server database performance

If you found your SQL Database performance is very slow then you can check below points. 1.Check if any blocking. 2- Check execution plan for any costly operator. 3- We can run DTA and take help from that but keep in mind it's not necessary to implement all suggestions provided by DTA. 4. Index fragmentation . 5. Missing indexes. 6. Stale update stats 7. Blocking & Deadlocks of queries. 8. Excessive compilation & recompilation. 9.Check long queries using trace of suspended records on high working time of transactions. 10.Check RAM utilization at SQL Server.

What is VLF in SQL?

VLF  stand for virtual log file. Its the internal structure of log file. Virtual log file means while running (DBCC loginfo)it will shows the status 0 indicates Recoverable 1 indicates Active 2 indicates unused 3.indicates reused. The Tlog file internally divided into virtual log files. You can see how many vlfs you have in your log file can be find out by running dbcc loginfo. Too many vlfs will might slow down the db recovery. Each vlf will be having status 0 to 9. These status number indicates the current vlf's status i.e 0 reusable 1 waiting for log backup 2 Active and so on.

What is a minimal logged in SQL

1.Minimal information it writes in the log file, when databse in bulk model. 2.If transaction is minimally logged only enough information is logged in transaction log to rollback the transaction no information is logged to roll it forward. This is why you loose point in time recovery if operation is minimally logged.

What is difference between Single user mode and Offline mode om SQL

1.When database is in offline users cant connect we need to bring our database in to emergency mode to connect to db for this we will try to bring in single user mode so that we can able to connect with single user. 2.Single user mode means access only SA admin,offline mode means no one user can not access. 3.To bring the database online from offline,there is no need of putting it in emergency mode. 4.If  database  in single user mode it will allow only one connection, it is in offline we can't do any thing.