Powershell: The Begining

From the past 6 months or so ,I have been trying to learn powershell because it would help me simplify a lot of activities that I do as a Database Administrator. But I was never able to read more than a page about it. (no time……nah…am lying …it was due to lack of interest)

Last week I came across this website http://midnightdba.itbookworm.com ,Created by the MVP duo- Sean and Jen “alias” Midnight DBA.

I watched Sean’s webinar about Powershell for beginners and that was really good. That video regenerated interest in me about powershell.Now I am reading more about powershell.

From now on I will be blogging about the things I have learnt and will be learning in powershell.

Thank you,Sean and Jen for the wonderful blog posts.

Script for fixing orphan users

As a follow up to my previous post for fixing orphan users,Here is the script that will allow to fix orphan users in a database

Declare @sql varchar(5000)

select @sql = coalesce (@sql ,'') + 'ALTER USER '+ name + ' WITH LOGIN = '+ name + ';'
from sysusers
where issqluser = 1
and (sid is not null and sid <> 0x0)
and (len(sid) <= 16) and suser_sname(sid) is null order by name Print @sql

Exec @sql

Fixing Orphan Users

You all might already known that if we have orphan users in the Database then using the stored Proc sp_change_users_login we can map the user to a Sql Login.
But According to Books Online, this feature will depreciated in the future.

So you guys must be wondering ,then how are we going to fix Orphan user Problem.
Well Here is the Solution.

Alter User UserName
With Login = LoginName

Replace the userName and LoginName accordingly with the orphaned user
and the existing login.

The advantage of using this method over sp_change_users_login is that
ALTER USER WITH LOGIN supports both SQL Server and Windows
logins where as sp_change_users_login works only for SQL Server
logins


I have picked up this tip from Laurentis Blog

http://blogs.msdn.com/lcris/

His Blog is an excellent resource for topics related to Sql Server Security

Track the DB Growth

Easy way to track the Db Growth.

Many times we would like to know how much the database has grown in the past N days. Though there are many ways of tracking the dbgrowth,the easiest way would be to query the backupset table in the msdb database.

This would give the full backup sizes of the database which is infact the size of the database.


Below is the query that i would use to quickly find out the growth of the database.


select
BackupDate = convert(varchar(10),backup_start_date, 111)
,SizeInGigs=floor( backup_size/1024000000)
from msdb..backupset
where
database_name = 'databasename'
and type = 'd'
order by
backup_start_date desc


I have picked this query from some other blog and now cannot retrace it back to find the name of the author. All the credit goes to the author.

cluster.log on windows 2008 ,where is it?

Today,a friend of mine asked me ,where to find the cluster.log in Windows Server 2008. I casually replied to him you can find it in
%systemroot%\Cluster\cluster.log.

But he couldn't find it there. So i did a bit of googling and found that you have to Open a Command Prompt (cmd.exe) and type the following command:

Cluster /Cluster:yourclustername log /gen /copy “C:\tmp”


The log file will be created in the specified location and you can view the contents by opening it in notepad.

Using Lock pages in memory? Be Aware

Lock Pages in Memory prevents SQL Server from paging out the memory it has allocated . It is a Windows security setting policy. Hence from a performance point of view enabling this option would be good.

But Be cautious when using this setting.

When the maximum memory value is not configured and left as default and you have a lot of load on the sql server ,then sql server will starve other applications from getting any memory because we have not placed any limit on the usage of memory for sql server. So It tries to consume as much memory as it can. Therefore all this memory will be locked and other applications will not be able to use this.



Though in sql server 2005 and 2008, it has been designed that Sql server will release some of the memory to the Operating system if it faces any memory pressure It is not that efficient


So Please configure the Sql Server Memory in such a way that they have an upperlimit and windows have sufficient memory.

Better way of finding the no of rows in a table

This is my first blog post and I wanted to keep it short and simple.

We often get requests or we ourselves would like to know the no of rows in a particular table. Doing a Count(*) on the table is not the right way to find the no of rows as it will always do an index scan or a table scan and if the table is large then it would take a lot of time .

The best way to find the count is by using the sys.partitions table.

select sum (rows) from sys.partitions where object_id = object_id('tablename') and index_id < 2