Archive

Archive for April, 2013

set SQL error log retention period through T-SQL

April 18, 2013 Leave a comment

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

CentralManagementServer

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

Advertisements

Property Size is not available for Database ‘DBNAME’. This property may not exist for this object

April 16, 2013 2 comments

Roscoe .. i will ask again .. let go !! ... Hi...

recently i wanted to change the recovery model of a DB to simple recovery model VIA GUI ..

as i right clicked the Database –> property  .. i received a strange error as below ..

TITLE: Microsoft SQL Server Management Studio

——————————

Cannot show requested dialog.

——————————
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

——————————

Property Size is not available for Database ‘DBNAME’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1125+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Size&LinkId=20476

——————————
BUTTONS:

OK
——————————

  • i was sysadmin on the sql instance .. so it should not have been related to permission
  • i checked the list of DB on the server with sp_helpdb command and noticed the owner for the concerned DB was set to NULL
  • so i tried assigning the DB ownership to one of the valid logins using below command
  • sp_changedbowner [ @loginame = ] ‘login’
                                 [ , [ @map= ] remap_alias_flag ]

above method resolved the issue and i was able to access the property tab ..

  • other consideration –> i did hear someone receiving similar error when the DB ownership was set to a valid login
  • i will try to post some workaround for the same ..

ENJOY 🙂

  •  

how to add yourself to sysadmin role

April 9, 2013 1 comment

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

SQL Saturday 119 Lunch 017 (Photo credit: Michael Kappel)

International Bibliography of Periodical Liter...

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 . . .

Option: 2 http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

(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 🙂

 

Categories: General, T-sql

Jack Lalanne Video

April 3, 2013 Leave a comment

Great stuff from Jack … Truth to be shared …

enjoy 🙂

Categories: LOA

Powershell Crash course part – 1

April 1, 2013 Leave a comment
Windows PowerShell exists?

Windows PowerShell exists? (Photo credit: crazytales562)

Hi All,

in this efforts , I will cover some PS basics to get myself and you started on Powershell..

Powershell helps DBA’s to achieve some complex functionality which are not possible by t-sql and command prompt

PS 1 had around 130 commands ,PS2 and PS3 have far more commands now

InstallingPS ..

PS wont get install on windows 2000 or older version then that ..

Newer OS like windows 7 and higher have Powershell preinstalled in that ..

for windows XP,windows 2003 etc go to http://www.microsoft.com/download to get your setup (check whether you need 32 or 64 bit)

to check whether PS is already available for your OS .. either enter powershell.exe in the START–> run prompt

OR go to add/remove program winmdows componets and add Powershell component ..

customizing the SHELL ..

you can right click on the edge of the prompt to customize the size of the window to suite ur preference …

getting Started :: (ur script may not run for that you have to set your execution policy to > set – executionpolicy unrestricted (refer this link for details PowerShell Basics – Execution Policy and Code Signing Part 1 (pauldotcom.com) ))

all the commands (also called as command-lets or cmdlets) in PS are in the form of VERB-noun form

even if the command returns multiple results the noun is singular ..

for example type get-process on the PS prompt (this will list all the process currently running on system )

PS D:\powershell\myscript> get-process

Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
——- —— —– —– —– —— — ———–
194 4 3632 652 46 1.67 4140 AcroRd32
349 9 55876 29360 162 109.86 4700 AcroRd32
277 8 5296 712 61 5.59 3452 AdobeARM

below is my default prompt .. that i have set my powrshell to start in ..

PS D:\powershell\myscript> 

you will most probably start in in PS C:\document and setting\yourID >

to read the content of a file example text file use  :: get-content

PS D:powershell\myscript> get-content myfile.txt

all commandlets take parameters to acheive different funalities and parameters are positional … meaning you may skip typing parameter name if you passing the actual value of parameter

PS D:powershell\myscript> get-content myfile.txt

is same as PS D:powershell\myscript> get-content -path myfile.txt

where –path was the parameter

in PS if a articular path has a space in it use double quotes for it

PS D:powershell\myscript> get-content “C:\test files\myfile.txt”

ALIASES :: to make all administrators (*nix,cmd.exe) switch to PS an easier curve .. PS has all *nix working in PS too .. so you already many PS commands without knowing it 🙂

example :: cd ,dir,mkdir,del,ren,remdir will all work in PS without any issues ..

as all of them are aliases to PS under hood commands

PS D:powershell\myscript> get-alias  CD
CommandType Name Definition
———– —- ———-
Alias cd Set-Location

so CD is alias of PS command named SET-LOCATION

to get a list of all alias just type –> get-alias

creating a new alias (it will be available only in your session : to make it persistent you need to save it in ur profile )

PS D:powershell\myscript> new-alias N notepad

PS D:powershell\myscript> N 

above command will open up new notepad window .. you can create aliases of ur preferences ..

Thanks for reading .. will continue in part -2