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.
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
One of my favorite DBCC Command is DBCC SQLPERF(logspace)
This will give you log space usage overview quickly.
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.
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.
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’
Here is a wonderful demo of SQL Server 2012 integrated with Microsoft Kinect.Use your hands and legs working with databases !
Dont you think this is a cool stuff.
Great news! By the end of the year SQL Azure will support 150GB databases.Current limit is 50GB.
You can easily find that using
SELECT LOGINPROPERTY(‘TestUser’, ‘IsLocked’)
You can unlock the login using
ALTER LOGIN [XYZ] WITH PASSWORD = ‘****’ UNLOCK ;
Today I came across an error
Property Owner is not available for Database ‘[DBName]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
Situation - You right-click on the database and choose properties. Now, instead of the database properties opening up, you get the message.
Cause – This issue can happen if the owner of the database is dropped from the Security Logins of the SQL Server.
sp_changedbowner [ @loginame = ] ‘login’
sp_changedbowner @loginame = ‘sa’