Saturday, May 11, 2013

Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

Saturday, May 11, 2013  Nareshkar Pakanati  No comments

Error:  Unable to open physical file - Operating system error 5: 5(error not found) Microsoft SQL Server: Error 5120

Reason of getting this Error: During attaching the database usually got this kind of error.

 Work Around:
First we need to find out the service account which sql server runs under, here is the couple options for finding service account.

 Option 1: Open SQL Server Configuration manager from start menu, and select sql server services, now you can see service account name on "Log On As" option below.

Read More
Read more »

Saturday, March 30, 2013

Database 'distribution' does not exist.

Saturday, March 30, 2013  Nareshkar Pakanati  No comments

When I try to configure replication, I get the error "Database 'distribution' does not exist. Make sure that the name is entered correctly."

 I’m not able to use the distribution database because it doesn't exist, and I even can't create the distribution database because it already exists.

 USE master
Go
Alter Database distribution Set Offline;
GO
Drop Database distribution;

Above script help you to remove distributor and then recreate them.
Could not locate entry in sysdatabases for database 'distribution'. No entry found with that name. Make sure that the name is entered correctly, does not exist in the database, sql server 2012, sql server 2008 R2, sql server 2005, sql server 2000, Msg 922, Level 14, State 1, Line 1, Database 'distribution' is being recovered. Waiting until recovery is finished. Msg 3148, Level 16, State 3, Line 1, This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified. Msg 3013, Level 16, State 1, Line 1, RESTORE DATABASE is terminating abnormally. distribution database cann't be dropped. Dropped Distribution database. Distribution database exists. unable to disable publishing and distribution‎ . Determining size of Distribution database‎ . How to clean Distribution database. Database 'distribution' cannot be opened due to inaccessible files or insufficient memory or disk space. Large Distribution Database. Drop Distribution DB. Publications. Subscriptions. jobs. agents.

Read More

Deprecated Feature - PIN and UNPIN a Table From Memory

Saturday, March 30, 2013  Nareshkar Pakanati  No comments

How to PIN a Table into Memory?
The result will be that pinned Table pages will not be flushed out from memory, whatever comes in, will stay in memory. If the table is used frequently, the result is improved performance.

 use AdventureWorks
GO
DECLARE @db_id int, @tbl_id int
SET @db_id = DB_ID('AdventureWorks')
SET @tbl_id = OBJECT_ID('AdventureWorks.Person.Address')
DBCC PINTABLE (@db_id, @tbl_id)


 How to UNPIN a Table from Memory?
Very frequently used tables should be pinned. Performance improvement should be monitored. If no gain/improvement, pinning should be discontinued or else it will overhead to the memory

 use AdventureWorks
GO
DECLARE @db_id int, @tbl_id int
SET @db_id = DB_ID('AdventureWorks')
SET @tbl_id = OBJECT_ID('AdventureWorks.Person.Address')
DBCC UNPINTABLE (@db_id, @tbl_id)


NOTE: This feature has been deprecated from 2005 onward, DBCC PINTABLE does nothing anymore.
Query Performance Tuning, SQL Server Query Performance Tuning, sql server query performance tuning tips, DBCC PINTABLE, SQL Server 2005 "Pin" data in Memory, Is there a way to force a table into memory, Keeping data available in the SQL Server data cache with PINTABLE, SQL Server Pin Table into Memory, sql server pin table in memory, dbcc buffer, dbcc dropcleanbuffers, SQL Server, Pinning Tables, DBCC PINTABLE UNPINTABLE, Store a table in a memory, Useful SQL Server DBCC Commands,

Read More

Saturday, March 2, 2013

Differences Between SQL Server Lower Version Vs Higher Version Installer Files

Saturday, March 02, 2013  Nareshkar Pakanati  No comments

I found some Intresting differences between lower version SQL Server with Higher version SQL Server while installing sql server 2012, see below.

No 1. Installation center was not there till SQL Server 2005, from 2008 on-words this installation Center was introduced and you know how easy to install or add new feature with this tool. 

No 2. Till 2008 R2 we can see IA64 platform file on installer but from 2012 on-words it was not supporting, you can't find this file anymore, see below picture.

No 3. Till SQL Server 2008 R2 the Business Intelligence was not included with installer, but from 2012 on-words both engine and business Intelligence tool is coming together.

 Difference Between Sql server Editions. Difference between SQL Server 2012 SP1 Express. Differences Between SQL Server Compact and SQL Server. Difference between sql server 2008 and sql server 2012. ifference between SQL Server Compact edition and SQL Server Express edition. difference between sql server 2005 and 2008 pdf. difference between sql server 2008 and 2012. difference between sql server standard and enterprise edition. difference between sql server and oracle. difference between sql server 2008 r2 and 2012. difference between sql server 2000 and 2005. difference between sql server 2008 and sql server 2008 r2. difference between sql server and mysql.

Read More

Fix: Msg 208 level 16 state 1 line 1 invalid object name

Saturday, March 02, 2013  Nareshkar Pakanati  No comments

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dbo.tablename’.

Workaround:
1.) Verify the name of the database and table name used in the query.
2.) Recheck which database name is selected, or use database name.
3.) Check the table's owner, if it's not dbo then mention the schema name before table name.
4.) Check any typo error.
invalid object name how to solve it, Msg 208, Level 16, State 1, Line 1. Invalid object name, Invalid object,msg 208,level 16, Why do I get 'object could not be found' or 'invalid object name', invalid object name sql server 2012, invalid object name sql server 2015, invalid object name sql server 2005, invalid object name sql server 2008 R2, server msg 208 level 16 state 1 line 1, sql server msg 208 level 16 state 1 line 1, msg 4104 level 16 state 1 line 1 the multi-part identifier could not be bound, msg 2714 level 16 state 6 line 1
msg 208 level 16 state 6, sql server message 4104, msg 15110

Read More

Monday, February 25, 2013

Get the List of Failed Jobs

Monday, February 25, 2013  Nareshkar Pakanati  No comments

Here is the simple script for finding all failed jobs in the server. For Daily monitoring this script is very handy for all DBA's who all are working on multi server administration.

 use msdb
GO
SELECT name,
date_created,
last_outcome_message,
last_run_date,
last_run_duration
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0
GO



Failed SQL Server Agent Jobs, Set Job Step Success or Failure Flow, Querying SQL Server Agent Job Information, How to obtain failed jobs from sql server agent through script , Script to Show All Failed Jobs in Specified Period, Find failed SQL Agent jobs using Transact-SQL script, Get failed SQL Server agent job, Getting failed jobs in the last hour, How to get the failed jobs list in sql server 2012, How to get the failed jobs list in sql server 2008 R2, List Failed Jobs for MSSQL 2005, 

Read More

Thursday, February 21, 2013

SQL Server Locking Wait Types

Thursday, February 21, 2013  Nareshkar Pakanati  No comments

Like other RDBMS, Microsoft’s SQL Server use locks to prevent multiple users from making conflicting changes and deletion to a set of data. When a set of data is locked by any user, then no other users can modify that same set of data until the first user finishes modifying the data and releases the lock.

LCK_M_BU   :   Occurs when a task is waiting to acquire a Bulk Update (BU) lock.

 LCK_M_IS      :    Occurs when a task is waiting to acquire an Intent Shared (IS) lock.

 LCK_M_IU      :    Occurs when a task is waiting to acquire an Intent Update (IU) lock.

 LCK_M_IX      :    Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

 LCK_M_S        :    Occurs when a task is waiting to acquire a Shared lock.

 LCK_M_SCH_M      :    Occurs when a task is waiting to acquire a Schema Modify lock.

 LCK_M_SCH_S       :    Occurs when a task is waiting to acquire a Schema Share lock.

 LCK_M_SIU     :    Occurs when a task is waiting to acquire a Shared With Intent Update lock.

 LCK_M_SIX     :    Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.

 LCK_M_U        :    Occurs when a task is waiting to acquire an Update lock.

 LCK_M_UIX     :    Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.

 LCK_M_X         :     Occurs when a task is waiting to acquire an Exclusive lock.
Understanding Locking in SQL Server. Lock Modes. different types of locks available in sql server. SQL Server Wait Types - Locking. Lock Types and Compatibility. Understanding SQL Server Locking. SQL Server Lock Types. sql server lock type tab. lock in sql server 2008. lock in sql server 2012. exclusive lock sql server 2008 R2. sql server read lock. transactions and locks in sql server. types of locks in sql server 2005. types of locks in sql server 2012. Introduction to Locking in SQL Server. SQL Server Lock Types. Locking in SQL Server. SQL Server Locking Mechanism. SQL Server. Locks Object.

Read More