Archive
Check out SQLSERVER-Performance-Tuning blog
Hi All,
Check out below Link for all latest trends and news on SQL SERVER Performance
tuning ,its an unique efforts in GULF and MIDDLE EAST ,
do join the subscription list and stay updated with latest blog-post ,,, ENJOY 🙂
http://sqlserver-performance-tuning.net/
SQL Server Performance Tuning is a specialized SQL Server community fully of several rich blogs ,videos tutorials , offline and online event for widely concerned topics on Microsoft SQL Server like T-SQL performance optimization , Data Warehousing solutions, Replication Solutions , DMV library ,new SQL Server 2012 features ,index tuning , heavy locks and deadlocks ..etc
Stay motivated !!!!!!!!!!!!
We often loose sight of our goals and tend to follow the trends or life style lived
and embraced by closed and dearest friends .. below Video boost your nerves to stay focused
on what you want … AS “WHAY you FOCUS on ……GROWS”
so stay focused on what you want .. and see the effect of conscious /deliberate thoughts ..
taking you closer to what you want ENJOY 🙂
Related articles
- Help! Motivation needed! (bpinks3.wordpress.com)
- How To Self Motivate (projecteve.com)
free SQL server books for download
download some of the BEST SQL server books ALL for FREEE … books on more then 10 topics …
available for free download ..
1> execution plan
2> profiler
3> security
4> SQL hardware and many more ..
JUST click on —– Download eBook (PDF): Free —— beside the book you are interested in ………….
http://www.sqlservercentral.com/books/
ENJOY 🙂
what the service pack of my SQL server
Hi all,
you may required to check your SQL service packs .. use below template to compare the same as of JULY 2013 …
use query as
1> select @@version
output ::
Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
2>
SELECT SERVERPROPERTY(‘productversion’)‘productversion’,SERVERPROPERTY(‘productlevel’)‘Servicepack’,SERVERPROPERTY(‘edition’)‘Product Edition’
use below chart for your reference
chart referred from –> http://sqlserverbuilds.blogspot.in/
Enjoy 🙂
SQL SERVER – 2014 Announced and SQL Server 2014 Datasheet
SQL SERVER – 2014 announced .. check an list of abstract level new features present in it .. ENJOY:)
bangalore-user-group-meeting
Hey guys check out cool coverage by Balmukund on bangalore-user-group-meeting
Enjoy 🙂
User account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or policy restriction has been enforced
Error :
User account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or policy restriction has been enforced
First and foremost ::
please check whether PASSWORD for the ACCOUNT is expired ? if so please reset it and try again (before going ahead with below steps)
Solution::
1. Click the Start button and type gpedit.msc in the Search programs and files bar and hit enter.
2. At the left pane, go to Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options
3. Look for “Accounts: Limit local account use of blank passwords to console logon only” and double click on it.
4. By default the Enable option is selected and all you need to do is select “Disable” and click OK.
Now you can connect to the computer using Remote Desktop and login to the user account that don’t have a password. Some Windows such as the XP home doesn’t have gpedit.msc and you will have to make the changes from registry.
1. Type regedit at the Search programs and files bar and hit enter.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Look for LimitBlankPasswordUse name on the right pane, double click on it and set the value data to 0.REF :: http://www.top-password.com/blog/how-to-fix-logon-failure-user-account-restriction/
OR
go to run type regedit.exe
then go here HKLM\System\CurrentControlSet\Control\Lsa\limitblankpassworduse
change from 1 to 0ENjoy:)
set SQL error log retention period through T-SQL
we all may need to tweak the SQL error log retention period on a single instance or list of SQL server VIA CMS (central management server)
below 2 commands are quite useful for achieving the same
READ the current setting of sql error log retention
/* USE [master] GO EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’ GO */
you may receive below error on few server .. you may want to go ahead with the update command and come back to see the results of xp_instance_regread
possible error message for some server ..
RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’ servername(username): Msg 22001, Level 1, State 1
set the LOG retention sql error to 20 days
/* USE [master] GO EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 20 GO */
above command will set the retention to 20 days and you may rerun EXEC xp_instance_regread to confirm the same setting
what are my privileges or rights on an SQL instance
“Boot down pill” / Biohacks / SML.20130205.IPH5.10051 (Photo credit: See-ming Lee 李思明 SML)
whoops!!! it happens we are given certain rights on SQL instance .. we tend to ask “WHAT are MY RIGHTS on THIS instance ??? ”
and you may be said “WELL FIND IT OUT yourSELF”
then we search for a PILL .. which could help us on the spot ..
fn_my_permissions is similar to the PILL .. that could work wonders for you ..
the results you get after running this will vary according to your access level on an instance
select * from fn_my_permissions (NULL,’SERVER’) — gives access details at server level
select * from fn_my_permissions (NULL,’Database”) — gives access details at DB level
list of privileges if your are sysadmin on a SQL server instance
select * from fn_my_permissions (NULL,’SERVER’)
| entity_name | subentity_name | permission_name |
| server | CONNECT SQL | |
| server | SHUTDOWN | |
| server | CREATE ENDPOINT | |
| server | CREATE ANY DATABASE | |
| server | ALTER ANY LOGIN | |
| server | ALTER ANY CREDENTIAL | |
| server | ALTER ANY ENDPOINT | |
| server | ALTER ANY LINKED SERVER | |
| server | ALTER ANY CONNECTION | |
| server | ALTER ANY DATABASE | |
| server | ALTER RESOURCES | |
| server | ALTER SETTINGS | |
| server | ALTER TRACE | |
| server | ADMINISTER BULK OPERATIONS | |
| server | AUTHENTICATE SERVER | |
| server | EXTERNAL ACCESS ASSEMBLY | |
| server | VIEW ANY DATABASE | |
| server | VIEW ANY DEFINITION | |
| server | VIEW SERVER STATE | |
| server | CREATE DDL EVENT NOTIFICATION | |
| server | CREATE TRACE EVENT NOTIFICATION | |
| server | ALTER ANY EVENT NOTIFICATION | |
| server | ALTER SERVER STATE | |
| server | UNSAFE ASSEMBLY | |
| server | ALTER ANY SERVER AUDIT | |
| server | CONTROL SERVER |
select * from fn_my_permissions (NULL,’DATABASE’)
| entity_name | subentity_name | permission_name |
| server | CONNECT SQL | |
| server | SHUTDOWN | |
| server | CREATE ENDPOINT | |
| server | CREATE ANY DATABASE | |
| server | ALTER ANY LOGIN | |
| server | ALTER ANY CREDENTIAL | |
| server | ALTER ANY ENDPOINT | |
| server | ALTER ANY LINKED SERVER | |
| server | ALTER ANY CONNECTION | |
| server | ALTER ANY DATABASE | |
| server | ALTER RESOURCES | |
| server | ALTER SETTINGS | |
| server | ALTER TRACE | |
| server | ADMINISTER BULK OPERATIONS | |
| server | AUTHENTICATE SERVER | |
| server | EXTERNAL ACCESS ASSEMBLY | |
| server | VIEW ANY DATABASE | |
| server | VIEW ANY DEFINITION | |
| server | VIEW SERVER STATE | |
| server | CREATE DDL EVENT NOTIFICATION | |
| server | CREATE TRACE EVENT NOTIFICATION | |
| server | ALTER ANY EVENT NOTIFICATION | |
| server | ALTER SERVER STATE | |
| server | UNSAFE ASSEMBLY | |
| server | ALTER ANY SERVER AUDIT | |
| server | CONTROL SERVER |
Enjoy 🙂
how to add yourself to sysadmin role
one of the best thing about attending SQL Saturday’s is the people you meet and tricks you learn .. (hmmm ok .. even the food included)
i come across similar webcast were i learned below 2 methods to add yourself as sysadmin on any SQL instance ..
example : if the SQL installation was done by someone else and OS admins were never given sysadmins priviliges on SQL instance
so after you get yourself added to OS admin .. you can follow any of the below methods to achieve the same …
NOTE: both methods cause SQL restart .. so take a note of the same …
SQL Saturday 119 Lunch 017 (Photo credit: Michael Kappel)
International Bibliography of Periodical Literature (Photo credit: Wikipedia)
Option 1: http://archive.msdn.microsoft.com/addselftosqlsysadmin/ (the logged in user gets added in sysadmin group)
download the cmd file from above link and save it onto the server .. where you want to add yourself to sysadmin ..
double click the cmd file and it will ask for instance name .. default is taken as the SQL express edition instance …
only enter the instance name (example for instance node-1\SQL2012_INST …. we will just enter SQL2012_INST .. not the network name)
below messages will appear in the cmd window .. (Note : it will cause SQL to restart as it puts instance in single-user mode and restarts it twice )
Adding ‘NODE-1\test’ to the ‘sysadmin’ role on SQL Server instance ‘SQL2012_INST
1’.
Verify the ‘MSSQL$SQL2012_INST1’ service exists …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping.
The SQL Server (SQL2012_INST1) service was stopped successfully.
Start the ‘MSSQL$SQL2012_INST1’ service in maintenance mode …
Add ‘NODE-1\test’ to the ‘sysadmin’ role …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping…
The SQL Server (SQL2012_INST1) service was stopped successfully.
The SQL Server (SQL2012_INST1) service is starting.
The SQL Server (SQL2012_INST1) service was started successfully.
‘NODE-1\test’ was successfully added to the ‘sysadmin’ role.
Press any key to continue . . .
(you can explicitly choose which user/group is to be given sysadmin role, after you get yourself added in OS admin group)
Above is a manual method .. which has the same steps .. ie. Putting instance in single user and then adding a group or user to sysadmin role ..
enjoy 🙂
SQL DBA learning curve 