It was Terrific and felt Happy after facing that...
1. Brief me about your self?
2. Ways to rename Database with TSQL Code?
sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'
USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO
3. When Log file is corrupted, how do you get the Database online ( you do not have any backups)?
Ans: SP_RESETSTATUS 'DBNAME'
If you have backup
restore recent backup with replace
Not getting much into theoretical part, you can read more about features in details from BOL or Search it on the web, let’s start with the issue and resolution.
Issue:
SQL Server shutdown unexpectedly (not clean shutdown) or Server Crashed or disk Crashed. On recovering the system or on starting the SQL Server services Database state changes to suspect. On checking the error you found
Message1:|
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF’. Diagnose and correct the operating system error, and retry the operation.
Message2:|
File activation failure. The physical file name “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF” may be incorrect.
Message3:|
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Messag4:|
Backup detected log corruption in database system. Context is FirstSector. LogFile: 2 “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF “’ VLF SeqNo: x2572 VLFBase: x4a60000 LogBlockOffset: x4ae3200 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x2043 LogBlock.StartLsn.Blk: x200000 Size: x0 PrevSize: x0
Message5:|
Msg 945, Level 14, State 2, Line 1
Database ‘Database Name’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Resolution
In case u lost the Log file (.Ldf)(Disk corruption, file deleted or SQL Server not able access the log file due to latency or disk issue) then the transaction which are not committed or not written to the data pages (hardened) will be lost (Might be possibility that u may see some data consistency issue).
Check the status of the Database by querying the system tables or views
4. When Log file is full and SQL Server got Hung. we tried all the ways below but could not perform such as Log Backup, Should not restart the server, Running Check Points, Adding Log file, Log File Shrinking. How do you free the Log Space and get the Server back to normal?
Backing up the log.
Freeing disk space so that the log can automatically grow.
Moving the log file to a disk drive with sufficient space.
Increasing the size of a log file.
Adding a log file on a different disk.
Completing or killing a long-running transaction.
Note: Factors, such as a long-running transaction, that keep virtual log files active for an extended period can restrict log shrinkage or even prevent the log from shrinking at all. For information about factors that can delay log truncation, see The Transaction Log (SQL Server).
Shrinking a log file removes one or more virtual log files that do not hold any part of the logical log (that is, inactive virtual log files). When a transaction log file is shrunk, enough inactive virtual log files are removed from the end of the log file to reduce the log to approximately the target size.
5. What type of Problems you faced so far in your company lately and how do you fix them?
6. In Cluster Environment, after maintenance activity I rebooted server from services. All the Databases are up and Running fine except one. The one server is in ' IN RECOVERY' mode, what might be the reason?
How do you check why DB is in 'IN RECOVERY' mode and how to know how much time it takes to DB to come to ONLINE state?
7. Tail Log backup Vs Transaction Log backup?
8. Log File is Full, how do you resolve this issue?
9. DB size is 1.5 TB, I have two SANs, one for Dedicated Data and another for Log. DB is in Full Recovery M ` odel. All Applications are hanged and user can not access. I can't increase the Log SAN. We can not create log file in Data SAN. How do you resolve the issue? Log Backup Failed, Check Point Did not solve, Can not restart the production server. Log File Shrinking also No Luck.
10. When Log file is full, change the recovery model to Simple and go back to free the Log Space.
will it release space?
11. What happens to LSN when you change the Recovery Model from FULL to SIMPLE and Vice Versa? LSN will reset or it'll continue form last LSN. What happens to the Backup strategy chain?
Ex: If we did the above on Monday, and server crashed on Wednesday, how do you recover from that? Is it possible to get the data till Wednesday or we can get only till Monday?
Backup Strategy: Sunday Full, Every Day: Differential Every 1 Hour: Transactional backup
Ans: Resotre in order given below.
Full Backup> Todays Differential> Last Hour Transactional>Tail Log if available.
12. How do you check why DB is in 'IN RECOVERY' mode and how to know how much time it
takes to DB to come to ONLINE state?
13. I use SA User to connect to DB only using Mixed Authentication Mode. My Login got disabled/forgot the SA Password. How can you enable it?
14. LDF corrupted and MDF is working fine. I don't have any backup. how do you get DB Online?
SP_RESETSTATUS 'DBNAME'
When Log file is corrupted, you can not access Database Properties.
15. Management Studio (SSMS) was not installed in my Machine, how do you connect to your Database?
ANS: SQLCMD,
16. What kind of High Availabilities you are using currently? What kind of DR you are using?
17. Important Things you need to know in replication?
18. Type of Replication in SQL 2008R2?
19. What is Index and type of Indexes available?
20. Clustered Vs Non Clustered?
21. Rebuild and Re Org?
22. pre requisite to rebuild an index?
23. Online Vs Offline Rebuilding Indexes?
24. What is Fill Factor?
25. What type of Extents will be created, if a Non-Clustered index is created?
26. What is Heap?
27. How many clustered and Non-Clustered Indexes can be created in 2008R2?
28. Primary Vs unique Key?
29. Char Vs VarChar Vs nVarChar?
30. can we use Truncate key in Foreign Key Table and why?
31. What is the use of DBCC commands? what is DBCC?
32. Tell about DBCC Command?
33. Trace Flags to trace DeadLocks?
Ans: 1222, 1204
24. What are the trace logs to enable Single user mode?
25. What is View?
26 What is Stored Procedure?
27. Stored Procedure Vs Trigger?
28. Where will be usernames and password get saved?
SysLogins Table
29. Use of Hints and explain?
query, we recommend that <join_hint>, <query_hint>, and <table_hint>
30. What are the Main services will be available once installed SQL Server?
SQL Active Directory Helper Service (Enables integration with Active Directories)
SQL Full-text Filter Daemon Launcher (Performs document filtering and word breaking for SQL Server full-text search)
SQL Server
SQL Server Agent
SSAS
SQL Server Browser
SSIS
SSRS
SQL Server VSS Writer (Provide the interface to backup/restore Microsoft SQL server through the windows VSS Infrastructure) Volume Shadow Copy Service
31. Pre requisites for DB Main?
32. What are the problems you faced with installations and how do you resolve with Examples?
33. I was able to setup Log Shipping with two servers, but when I try with another secondary server to add, I was unable to do it. what might be the reason?
34. How to apply patch in all modes like Clustering Nodes, Mirror Servers, Log Shipping Servers, Replication Servers on Physical and Virtual Environments?
35. Can we truncate data in a table with Foreign Key reference?
1. Brief me about your self?
2. Ways to rename Database with TSQL Code?
sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'
USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO
- In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
- Make sure that no one is using the database, and then set the database to single-user mode.
- Expand Databases, right-click the database to rename, and then click Rename.
- Enter the new database name, and then click OK.
3. When Log file is corrupted, how do you get the Database online ( you do not have any backups)?
Ans: SP_RESETSTATUS 'DBNAME'
If you have backup
restore recent backup with replace
Not getting much into theoretical part, you can read more about features in details from BOL or Search it on the web, let’s start with the issue and resolution.
Issue:
SQL Server shutdown unexpectedly (not clean shutdown) or Server Crashed or disk Crashed. On recovering the system or on starting the SQL Server services Database state changes to suspect. On checking the error you found
Message1:|
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF’. Diagnose and correct the operating system error, and retry the operation.
Message2:|
File activation failure. The physical file name “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF” may be incorrect.
Message3:|
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Messag4:|
Backup detected log corruption in database system. Context is FirstSector. LogFile: 2 “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Database_LogFile.LDF “’ VLF SeqNo: x2572 VLFBase: x4a60000 LogBlockOffset: x4ae3200 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x2043 LogBlock.StartLsn.Blk: x200000 Size: x0 PrevSize: x0
Message5:|
Msg 945, Level 14, State 2, Line 1
Database ‘Database Name’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Resolution
In case u lost the Log file (.Ldf)(Disk corruption, file deleted or SQL Server not able access the log file due to latency or disk issue) then the transaction which are not committed or not written to the data pages (hardened) will be lost (Might be possibility that u may see some data consistency issue).
Check the status of the Database by querying the system tables or views
select name,state,state_desc from sys.databases where name=’XYZ’
The only possible way to bring the database online is rebuilding the log file.
Name State State_desc Database 3 RECOVERY_PENDING
Set the database in Emergency Mode
ALTER DATABASE XYZ SET EMERGENCY
select name,state,state_desc from sys.databases where name=’XYZ’
Set the database in single user mode with rollback immediate mode
Name State State_desc Database 5 EMERGENCY
ALTER DATABASE XYZ SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Rebuild the log file
DBCC CHECKDB(‘XYZ’,REPAIR_ALLOW_DATA_LOSS)
Result MessageFile activation failure. The physical file name “D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\XYZ_log.LDF” may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Warning: The log for database ‘XYZ’ has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Check the database status
select name,state,state_desc from sys.databases where name=’XYZ’
The Database is still in single user mode, set it to multi user mode
Name State State_desc Database 0 Online
ALTER DATABASE XYZ SET MULTI_USER
4. When Log file is full and SQL Server got Hung. we tried all the ways below but could not perform such as Log Backup, Should not restart the server, Running Check Points, Adding Log file, Log File Shrinking. How do you free the Log Space and get the Server back to normal?
Backing up the log.
Freeing disk space so that the log can automatically grow.
Moving the log file to a disk drive with sufficient space.
Increasing the size of a log file.
Adding a log file on a different disk.
Completing or killing a long-running transaction.
Note: Factors, such as a long-running transaction, that keep virtual log files active for an extended period can restrict log shrinkage or even prevent the log from shrinking at all. For information about factors that can delay log truncation, see The Transaction Log (SQL Server).
Shrinking a log file removes one or more virtual log files that do not hold any part of the logical log (that is, inactive virtual log files). When a transaction log file is shrunk, enough inactive virtual log files are removed from the end of the log file to reduce the log to approximately the target size.
5. What type of Problems you faced so far in your company lately and how do you fix them?
6. In Cluster Environment, after maintenance activity I rebooted server from services. All the Databases are up and Running fine except one. The one server is in ' IN RECOVERY' mode, what might be the reason?
How do you check why DB is in 'IN RECOVERY' mode and how to know how much time it takes to DB to come to ONLINE state?
7. Tail Log backup Vs Transaction Log backup?
8. Log File is Full, how do you resolve this issue?
9. DB size is 1.5 TB, I have two SANs, one for Dedicated Data and another for Log. DB is in Full Recovery M ` odel. All Applications are hanged and user can not access. I can't increase the Log SAN. We can not create log file in Data SAN. How do you resolve the issue? Log Backup Failed, Check Point Did not solve, Can not restart the production server. Log File Shrinking also No Luck.
10. When Log file is full, change the recovery model to Simple and go back to free the Log Space.
will it release space?
11. What happens to LSN when you change the Recovery Model from FULL to SIMPLE and Vice Versa? LSN will reset or it'll continue form last LSN. What happens to the Backup strategy chain?
Ex: If we did the above on Monday, and server crashed on Wednesday, how do you recover from that? Is it possible to get the data till Wednesday or we can get only till Monday?
Backup Strategy: Sunday Full, Every Day: Differential Every 1 Hour: Transactional backup
Ans: Resotre in order given below.
Full Backup> Todays Differential> Last Hour Transactional>Tail Log if available.
12. How do you check why DB is in 'IN RECOVERY' mode and how to know how much time it
takes to DB to come to ONLINE state?
13. I use SA User to connect to DB only using Mixed Authentication Mode. My Login got disabled/forgot the SA Password. How can you enable it?
14. LDF corrupted and MDF is working fine. I don't have any backup. how do you get DB Online?
SP_RESETSTATUS 'DBNAME'
When Log file is corrupted, you can not access Database Properties.
15. Management Studio (SSMS) was not installed in my Machine, how do you connect to your Database?
ANS: SQLCMD,
16. What kind of High Availabilities you are using currently? What kind of DR you are using?
17. Important Things you need to know in replication?
18. Type of Replication in SQL 2008R2?
19. What is Index and type of Indexes available?
20. Clustered Vs Non Clustered?
21. Rebuild and Re Org?
22. pre requisite to rebuild an index?
23. Online Vs Offline Rebuilding Indexes?
24. What is Fill Factor?
25. What type of Extents will be created, if a Non-Clustered index is created?
26. What is Heap?
27. How many clustered and Non-Clustered Indexes can be created in 2008R2?
28. Primary Vs unique Key?
29. Char Vs VarChar Vs nVarChar?
30. can we use Truncate key in Foreign Key Table and why?
31. What is the use of DBCC commands? what is DBCC?
32. Tell about DBCC Command?
33. Trace Flags to trace DeadLocks?
Ans: 1222, 1204
24. What are the trace logs to enable Single user mode?
25. What is View?
26 What is Stored Procedure?
27. Stored Procedure Vs Trigger?
28. Where will be usernames and password get saved?
SysLogins Table
29. Use of Hints and explain?
Hints are options or strategies specified for enforcement by the SQL Server query processor on
SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the
query optimizer might select for a query.
Because the SQL Server query optimizer typically selects the best execution plan for a
query, we recommend that <join_hint>, <query_hint>, and <table_hint>
30. What are the Main services will be available once installed SQL Server?
SQL Active Directory Helper Service (Enables integration with Active Directories)
SQL Full-text Filter Daemon Launcher (Performs document filtering and word breaking for SQL Server full-text search)
SQL Server
SQL Server Agent
SSAS
SQL Server Browser
SSIS
SSRS
SQL Server VSS Writer (Provide the interface to backup/restore Microsoft SQL server through the windows VSS Infrastructure) Volume Shadow Copy Service
31. Pre requisites for DB Main?
32. What are the problems you faced with installations and how do you resolve with Examples?
33. I was able to setup Log Shipping with two servers, but when I try with another secondary server to add, I was unable to do it. what might be the reason?
34. How to apply patch in all modes like Clustering Nodes, Mirror Servers, Log Shipping Servers, Replication Servers on Physical and Virtual Environments?
35. Can we truncate data in a table with Foreign Key reference?
No comments:
Post a Comment