Force a table to be stored in the buffer pool permanently!

There was a question from one of the users on buffer storage usage.The question was something like this

‘I want to make a table into buffer storage and keep as long as I want.Any query’s can scan this table in buffer straightly?’

This was indeed an interesting question and unfortunately the answer to the question is NO.There is no way you can achieve this in newer versions of SQL Server,ie from SQL Server 2005.

Paul S Randal whom I consider as one of the best person to talk about SQL Server had talked about a DBCC PINTABLE command which actually was used in SQL Server 2000 which will pin the pages from a heavily used.

He has confirmed that this code will does nothing in SQL 2005 or above as these newer version does buffer pool management very efficiently.


Useful new DMV’s in SQL Server 2012 and SQL Server 2008 R2 SP1

There are couple of new DMV’s which came to my attention today and I would like to share them with you all.

sys.dm_os_windows_info: This DMV will get you OS related information of the Server.

Sample result is –

windows_release	  windows_service_pack_level	windows_sku	os_language_version
5.1	          Service Pack 3	        NULL	        1033

sys.dm_server_registry: This DMV will get you information on registry keys which are used by the server.

Sample result is(I have trimmed the data) –

registry_key	                                        value_name	value_data
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP	ObjectName	LocalSystem
HKLM\SYSTEM\CurrentControlSet\Services\MSSQL$DASANUP	ImagePath	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP

sys.dm_server_memory_dumps : This DMV will get you information on dumps which are generated by the server.

Sample result  is –

filename	                                                                        creation_time	                        size_in_bytes
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0001.mdmp	2011-08-29 14:39:31.2129902 -07:00	6015847
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0002.mdmp	2011-08-29 14:44:06.7232483 -07:00	6526667
C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\LOG\SQLDump0003.mdmp	2011-08-29 14:47:11.7133910 -07:00	6429912

sys.dm_server_services: This DMV will get you information on services and startup’s.

Sample result is –

servicename	         startup_type	 startup_type_desc status status_desc	process_id  last_startup_time	                service_account	filename	                                                                                is_clustered	cluster_nodename
SQL Server (DASANUP)	        3	 Manual	             4	  Running	 2252	    2011-10-27 15:53:26.3520221 -07:00	LocalSystem	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\sqlservr.exe" -sDASANUP	N	        NULL
SQL Server Agent (DASANUP)	2	 Automatic           4	  Running	 4308	    2011-10-27 15:54:01.0705499 -07:00	LocalSystem	"C:\Program Files\Microsoft SQL Server\MSSQL10_50.DASANUP\MSSQL\Binn\SQLAGENT.EXE" -i DASANUP	N	        NULL



Backup detected log corruption,what is the best way to recover the database

Recently I was asked this question by a user.His backup detected log corruption and he was looking for the safest way to recover from the error.

As the error was related to disk,he should move the log file to a different disk which is in good condition.

Solution to the problem is –

As there is corrupted data in the log mostly because of disk errors,we will need to throw that data away.

We have to analyze why there was a disk error and for this we can check windows event logs and any hardware logs.

Immediate fix will be –

Disconnect all users.

Switch to the SIMPLE recovery model.

Add a file to a new drive.

Switch to the FULL recovery model.

Take Full database backup.

Start log backups.


Avoid a server reboot while installing SQL Server 2008 R2?

If you are in a situation that you need not want server to be rebooted after SQL Server 2008 R2 install,then follow the below steps

You can bypass the “Restart computer” rule when installing SQL Server 2008 r2 and work around this by following these steps:
1. Open Registry Editor. Click Start –> Run, input “regedit” and press Enter.
2. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager, and delete any value in “PendingFileRenameOperations” key.


Revoke all privileges from table in database

Recently I was asked this interesting question – I need to revoke all privileges from a single table in the database,how can I do that?

Solution is simple – Using TSQL you can easily achieve this task.For Select permissions you can have

REVOKE SELECT ON dbo.tablename FROM user.

You can list the permissions granted to an object using sp_helprotect ‘dbo.table’