SQL Server's Fascinating Stories

I recently had a quaint thinking, as to how to know the hostname of a machine given its IP. Its easy. Just give the following in command prompt. C:\> nbtstat -A xxx.xxx.xxx.xxx where xxx.xxx.xxx.xxx represents an IP address.

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.

Buffer Management of SQL Server: SQL Server uses buffer pages for any changes or requests for the data on the database. These buffer pages are 8KB pages on RAM that minimizes disc Input/Output. Any 8KB page can be buffered in memory, and set of all pages currently buffered is called as buffer cache. The amount of memory available to SQL Servers decides how many pages will be cached in memory. The buffer cache is managed by Buffer Manager. Either reading from or Writing to any page on the disc happens at two levels. The data that should be either read/Written is first copied to the buffer cache and the read/write operation is carried out on the data that is currently on the Buffer Cache. The edited data is updated on the disc by the buffer manager only if the in-memory cache has not been referenced for some time. While writing pages back to the disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/

Hi People, check out this link. It discusses about searching the error log from the query editor which allows us to be more flexible in spotting out the exact error we need. http://www.mssqltips.com/tip.asp?tip=1476

The following query can be used to drop the locks on Database objects. It should be used carefully as the false killing of the processes would lead to disastrous issues which would come as chain of issues. Create Table #Tmp ( spid smallint, ecid smallint, status nchar(30), loginame nchar(128), hostname nchar(128), blk char(5), dbname nchar(128), cmd nchar(16) ) Create Table #TmpLocks ( spid smallint, dbid smallint, ObjId int, IndId smallint, Type nchar(4), Resource nchar(16), Mode nvarchar(8), Status nvarchar(28) ) Insert Into #Tmp Exec sp_who Insert Into #TmpLocks Exec sp_lock If(Select Count(*) From #Tmp T Join #TmpLocks TL On T.spid = TL.spid Where /*This is for tempdb*/ dbid = 2 And objid In (1, 2, 3)) > 0 Then you can kill the concerned spid with the command : Kill — The concerned spid Drop Table #Tmp Drop Table #TmpLocks