Wednesday, October 21, 2009

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.

Friday, October 2, 2009

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.

Tuesday, September 29, 2009

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.