Software Development,SQL Tips, Information Technology Technical tutorials

Asp.net,SQL tips-Tricks for error resolution version 2014,2012,2008,2005,Procedural language,Stored Procedure, computer , excel macro programming,Film,Musical notations,Indian ,Western Music.

November 13, 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 procedure sp_resetstatus to reset the status of a suspect database. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online. For SQL Server 6.0 and 6.5, if you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For SQL Server 7.0 and later, this procedure is created at installation by the inscat.sql script, found in the Mssql\Install directory.
3. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.
4. Execute sp_resetstatus in the master database for the suspect database:
use master
go
exec sp_resetstatus your_database_name

5. Stop and restart SQL Server.
6. Verify that the database was recovered and is available.
7. Run DBCC NEWALLOC, DBCC TEXTALL and DBCC CHECKDB.

use master
go
sp_configure 'allow updates', 1
reconfigure with override
go

select status from sysdatabases where name = 'Testdb'
4194329
update sysdatabases set status= 32768 where name = 'Testdb'

dbcc rebuild_log ('your database name', 'full path to a new transaction log file')

dbcc rebuild_log ('Testdb', 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\testdb.ldf')

use Testdb
go
sp_dboption 'Testdb, 'single_user', true
go
dbcc checkdb ('Testdb', repair_allow_data_loss)

update sysdatabases set status= 0 where name = 'Testdb'

DBCC CHECKALLOC ('Testdb')
and
DBCC CHECKDB ('Testdb')

sp_dboption 'Testdb', 'single_user', 'false'

use master
go
sp_configure 'allow updates', 0
go

use master
go
exec sp_resetstatus Testdb

DBCC NEWALLOC
DBCC TEXTALL
DBCC CHECKDB

November 7, 2014

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.

November 3, 2014

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.

November 1, 2014

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.

October 26, 2014

Total IPs Required for fail-over clustering in SQL

How many Total ip's required build win&SQL Failover clustering Active/Active & Active/Passive?

Ans.In a 2 node cluster each ip for each node and one ip for win cluster and one for msdtc and one for each cluster group .. If u use private network for heartbeat then 2 more ip .. So total of 5-7

In a two node cluster 7ip's are needed two private ip's for connecting to quarum. one for node A and other for node B, two public ip's one for node A and other one For node B and one for msdic and one for msclust and one ip for switch for connecting to san drives in which data is stored. 

Total 7ip's are needed.

Script to apply any user permission to the all databases of SQL

/* It is a new feature and it has ability to create user defined server roles
and assign server level/scope permissions. */
/*
- Following actions are performed to implement this new feature:
- Created Server role
- Created Login and made member of server role
- Granted Standard view permission
After completion above steps the login has ability to make connection any database with data reader
permission.
*/

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
CREATE LOGIN [DBREADER] WITH PASSWORD=N'123',
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE ALLDBREADER ADD MEMBER [DBREADER]
GO
GRANT CONNECT SQL TO ALLDBREADER
GRANT VIEW ANY DATABASE TO ALLDBREADER
GRANT VIEW ANY DEFINITION TO ALLDBREADER
GRANT VIEW SERVER STATE to ALLDBREADER
GRANT CONTROL SERVER TO ALLDBREADER
DENY SHUTDOWN TO ALLDBREADER
Go

USE master
GO
CREATE SERVER ROLE ALLDBREADER
Go
GRANT CONTROL SERVER TO ALLDBREADER
DENY ALTER ANY DATABASE TO ALLDBREADER
Go

October 7, 2014

Script or query for FAILED JOBS Details with error Messages in SQL

You can use following Script or query for FAILED JOBS Details with error Messages in SQL. I hope it will help you.

USE Msdb
Declare @lastRunDate int =replace(CAST(getdate()as date),'-','')
SELECT Sj.name as JobName ,CASE SJH.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
END AS JobStatus,SJH.message from sysjobhistory SJH inner join sysjobs SJ
ON SJH.job_id = SJ.job_id
where SJH.step_id = 0
and sjh.run_date =@lastRunDate
and SJH.run_status =0

September 30, 2014

What is SQL Server Profile

SQL Server Profiler. It is a graphical based tool provided by SQL Server. It enables you to trace a query. For example.you have a SP which you wish to trace. you can execute the SP and start a profiler trace on it. It will show you row by row execution of the SP

SQL Server Profiler is used to trace and analyze the SQL Server running processes and queries in the cases of performance issue.

It is taken high memory so not used in main server.

It is have so many events for trace. so your select desired event and filter it.

It can use to find the blocking by applying trace and catch everythng runing on the server

How to Get historical data of blocked process in SQL


WITH [Blocking]AS (SELECT w.[session_id]
,s.[original_login_name]
,s.[login_name]
,w.[wait_duration_ms]
,r.[scheduler_id]
,w.[wait_type]
,r.[status]
,r.[wait_resource]
,w.[resource_description]
,s.[program_name]
,w.[blocking_session_id]
,s.[host_name]
,r.[command]
,r.[percent_complete]
,r.[cpu_time]
,r.[total_elapsed_time]
,r.[reads]
,r.[writes]
,r.[logical_reads]
,r.[row_count]
,q.[text]
,q.[dbid]
,p.[query_plan]
,r.[plan_handle]
FROM [sys].[dm_os_waiting_tasks] w
INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
WHERE w.[session_id] > 50
)
Insert into Blocking_v1 ([session_id]
,[blocking_session_id]
,[WaitingUserSessionLogin]
,[BlockingUserSessionLogin]
,[scheduler_id]
,[WaitingUserConnectionLogin]
,[BlockingSessionConnectionLogin]
,[WaitDuration]
,[WaitType]
,[WaitRequestMode]
,[WaitingProcessStatus]
,[BlockingSessionStatus]
,[WaitResource]
,[WaitResourceType]
,[WaitResourceDatabaseID]
,[WaitResourceDatabaseName]
,[WaitResourceDescription]
,[WaitingSessionProgramName]
,[BlockingSessionProgramName]
,[WaitingHost]
,[BlockingHost]
,[WaitingCommandType]
,[WaitingCommandText]
,[WaitingCommandRowCount]
,[WaitingCommandPercentComplete]
,[WaitingCommandCPUTime]
,[WaitingCommandTotalElapsedTime]
,[WaitingCommandReads]
,[WaitingCommandWrites]
,[WaitingCommandLogicalReads]
,[WaitingCommandQueryPlan]
,[WaitingCommandPlanHandle])
SELECT b.[session_id] AS [WaitingSessionID]
,b.[blocking_session_id] AS [BlockingSessionID]
,b.[login_name] AS [WaitingUserSessionLogin]
,s1.[login_name] AS [BlockingUserSessionLogin]
,b.scheduler_id
,b.[original_login_name] AS [WaitingUserConnectionLogin]
,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
,b.[wait_duration_ms] AS [WaitDuration]
,b.[wait_type] AS [WaitType]
,t.[request_mode] AS [WaitRequestMode]
,UPPER(b.[status]) AS [WaitingProcessStatus]
,UPPER(s1.[status]) AS [BlockingSessionStatus]
,b.[wait_resource] AS [WaitResource]
,t.[resource_type] AS [WaitResourceType]
,t.[resource_database_id] AS [WaitResourceDatabaseID]
,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
,b.[resource_description] AS [WaitResourceDescription]
,b.[program_name] AS [WaitingSessionProgramName]
,s1.[program_name] AS [BlockingSessionProgramName]
,b.[host_name] AS [WaitingHost]
,s1.[host_name] AS [BlockingHost]
,b.[command] AS [WaitingCommandType]
,b.[text] AS [WaitingCommandText]
,b.[row_count] AS [WaitingCommandRowCount]
,b.[percent_complete] AS [WaitingCommandPercentComplete]
,b.[cpu_time] AS [WaitingCommandCPUTime]
,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
,b.[reads] AS [WaitingCommandReads]
,b.[writes] AS [WaitingCommandWrites]
,b.[logical_reads] AS [WaitingCommandLogicalReads]
,b.[query_plan] AS [WaitingCommandQueryPlan]
,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO