Sorts Spills in tempdb due to incorrect behavour by Query optimizer...

This one is really bothering me .I tried n tried n tried but failed to understand why is it happening ....

To give you a brief description , A query runs in 25-45 ms for x rows but the same query runs 15-20 times slower if i just add 1 row or a dozen more rows in some other caese (you will see it later)...If i see the execution plans , its totally identical .The only difference is that , the query which is slower does sorting in tempdb instead of in the user database where its been executing ...

Here we go :

create table SQLDBA (a1 int primary key clustered,a2 int,a3 char(2000))
go

begin tran
go
declare @i int
set @i=1
while @i <=200000
begin
insert into SQLDBA values (@i,rand() * 2000000,replicate('a',2000))
set @i=@i+1
end
commit tran
go

update statistics SQLDBA with fullscan
go


So , we created a table with 3 columns and inserted 200000 rows to it .
Now I will run 2 queries :

set statistics time on
go

/* Good Query */
declare @a1 int,@a2 int , @a3 char(2000)
select @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3000
order by a2
go

/* Bad Query */
declare @a1 int,@a2 int , @a3 char(2000)
select @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3079
order by a2
go

Notice that there are only 79 more rows .But if you execute them on SQL Server 2005 SP3 as I did with 2 GB RAM , the 1st query runs in approax 15-22 secs .While the second query runs in 320-410 secs.Try it yourself .You will see some difference in duration as its hardware specific.Also , in my case it was running OK till 3078 rows . Yes, the execution time when a1 <3000 or a1 <3078 is almost similar .If I increase just 1 more row it degrades the performance .

NOTE : You might have to find out this threshold limit on your machine .In my case it was 3078 .The moment I use 3079 ..Booooommmmmmmmm

So why its happening :D ......

-Setting Statistics profile to ON showed that The Subtree cost is 2.5% more ..which is fine .

-Setting Statistics IO to ON showed :

Table 'SQLDBA'. Scan count 1, logical reads 1007, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 34 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

Table 'SQLDBA'. Scan count 1, logical reads 1034, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 344 ms.


So , looks like this is Neither a CPU issue (query that runs faster is taking more CPU time) nor an IO issue as there are no physical reads.


One thing that I found when I took the XML output of the execution plan was that the good query was always taking compile time 2 and the bad query was taking 0 ....this was again strange to me.

Totaly stuck ..........

Then I searched many DMVs related to query execution and used sys.dm_exec_query_memory_grants DMV which show the row for each query that is waiting for the memory or those waited and acquired but will not show the querries that do not have to wait for the memory grant .

surprisingly , it was showing only one query in the output and that was the query taking more time .Strange but true .Why its waiting for memory when there is enough RAM on my server and max server memory is unlimited.

sys.dm_exec_query_stats also shows that bad query is doing around 100 physical reads while good one is doing none.

Then i queried sys.dm_exec_query_memory_grants:

select granted_memory_kb,used_memory_kb,max_used_memory_kb
from sys.dm_exec_query_memory_grants
where session_id in (51,52)

found that good query is using very less memory out of granted but bad query using full and more then that .....how ........

Because whatever extra memory it needs for sorting is coming form tempdb :

select num_of_reads,num_of_bytes_read,num_of_writes,num_of_bytes_written
From sys.dm_io_virtual_file_stats(db_id('tempdb'),1)
go


So looks like Optimizer is not able to allocate the right amount of memory to the bad query such that it does not go to tempdb .......NOPE i am incorrect i think ..because if you see the number of rows in where condition the amount of memory given looks directly proportional .....S oallocating memory to the query does not seems to be a problem here ..the problem is why the query is using more memory that it needs to spill to tempdb .....

Let me give you one more example that will further open your eyes :D ...

set statistics time on
GO
declare @a1 int,@a2 int , @a3 char(2000)
select top 100 @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3000
order by a2

Go

declare @a1 int,@a2 int , @a3 char(2000)
select top 101 @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3000
order by a2

Now , these queries are identical in any respect except that in the second query I am doing select top 101 ...just 1 row extra ....and see the difference ...HELL ....
And the status is same here if you go through the DMVs etc ..Even if you wrap up in a SP or use different hints ....it wont help ...

However you will see that In this case i.e. the second one 1 MB of memory is given to both the queries and nonoe of them is consuming the complete memory given ...but still the bad query is doing Sort spills to tempDB .....So its proved that granting of memory to the queries by the optimizer is Correct ...but its the way query sorts in tempdb and also the usage of memory by the query that looks incorrect here ...



So whats happening ........ :D ...Why just one extra row makes the query to spill to tempdb for sorting ....(use profiler to see if sorting in happening)..


NOW , TRY THIS ....

On my machine i have 2GB RAM .....i capped max server memory to 100MB...
And then the query was running fine top 100 and top 101 and the previous query all are running well except that the bad query takes more time once in 7-8 attampts (sometimes less or sometimes more)....

Looks like something is related to memory regions or chunks ...not sure , I may be incorrect ....Something is really wrong .....


Filing a bug ..

Happy learning ...

Creating SQL Server Alerts using WMI classes- Part 1

When we create alerts or when we try to create script we search net a lot .We get some thing but we do not get all what we look for .And then we stop pursuing it ...

I was given a task to create SQl Server alerts (as many as I can).I could find a couple of scripts ready but that did not satisfy my requirement as everyone creats the scripts as per their needs .

So I decided to do everything on my own and hence sharing my experiance with you .
To know what kind of alerts you can create go through this file



You will be amazed to see that almost everything is covered here :-) ..

Let us start now by creating an alert that will notify us whenever a new database is created .

Pre-requsites :

-> Need to be SQL Server 2005 SP2 or above or SQL Server 2008 as there is a bug in RTM or SP1 .

-> We need to execute it in master (do not change the script to be run in your user database).This is because Master captures the metadata of all the databases including itself .If you create it in anyother database then it will not work .Other scripts will also work only for that user database .

-> Make sure the SQL Server Agent is started and make sure that "REPLACE TOKENS FOR ALL JOB RESPONSES TO ALERTS" option is checked under Agent properties -> Alert system .

-> Read the code a few times and understand it .You might have to change it as per your need .

CODE :
Part 1:

/*******************************************************************************************
* This script will create an Alert to Monitor Create DATABASE event
* The alert will run a job and the job will enter data in a table.
*******************************************************************************************/


/* Step 1: creating the table to capture the Event information */

USE Master
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Create_database]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Create_database]
GO

CREATE TABLE [dbo].[Create_database] (
[PostTime] [datetime] NOT NULL ,
[Instance] varchar(20),
[DatabaseName] varchar(20),
[computerName] Varchar(20),
[Loginname] Varchar(20),
[TSQLCommand] varchar (1000),
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_Create_database_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

CREATE INDEX [Create_database_IDX01] ON [dbo].[Create_database]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the table created above*/
/*Service account and sql operator option are optional and hence commented*/

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Create_Database Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Create_Database Event', @delete_unused_schedule=1

GO
--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Create_Database Event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to Create_database events',
@category_name=N'[Uncategorized (Local)]',
--@owner_login_name=@ServiceAccount,
--@notify_email_operator_name=@SQLOperator,
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO Create_database (
PostTime,
Instance,
DatabaseName,
Computername,
LoginName,
TSQLCommand
)

VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
N''$(ESCAPE_NONE(WMI(DatabaseName)))'',
N''$(ESCAPE_NONE(WMI(ComputerName)))'',
N''$(ESCAPE_NONE(WMI(Loginname)))'',
N''$(ESCAPE_NONE(WMI(TSQLCommand)))''
)',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/*Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Create_database')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Create_database'

GO

DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to Create_database',
@enabled=1,
@notification_message=N'Your Message',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM Create_database',
@job_name='Capture Create_Database Event' ;

EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to Create_database', @operator_name=N'Test', @notification_method = 1
GO



--select * from master..Create_database


How it works :

As soon as the database is created WMI class create_database will generate an event and trigger the alert through SQL Server agent .The alert will in turn execute the job that will insert the event information to the table we have created .


Part 2:
This part will send a mail to the desired user that an alert has been fired .
This can be achieved by :

1) Using Database mail (including creating an operator)and choosing the option in the job itself for notifying the operator .

2) Using custom based CDO message via VBscript and execute it using XP_cmdshell as a job step.

Mail.vbs :
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "abhay.chaudhary@in.ibm.com"
objEmail.To = "abhay.chaudhary@in.ibm.com"
objEmail.Subject = "Mail for you using CDO :-) "
objEmail.Textbody = "Hi sir this mail has been sent without using SQL Server and from WMI script."
objEmail.AddAttachment "C:\Script repository\Alerts\final Alerts\readme.txt"
objEmail.Send


3) Using the Stored procedure below :
/* Step 5: Create a stored proc for sending the [Create_database] information as .CSV file */

Create proc [dbo].[Create_database_rpt]
as
DECLARE @SQL varchar(2000)
DECLARE @date varchar (2000)
DECLARE @File varchar(1000)
select @date= convert(date,GETDATE())
SET @SQL = 'select * from [Create_database] where flag = 0'
SET @File = 'Deadlock report'+@date+'.csv'

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'your email.com',
@subject = '[Create_database] report',
@body = '***URGENT***Attached please find the Create Database report',
@query =@SQL ,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file,
@query_result_header = 1,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_width = 32767

/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
update dbo.[Create_database] set flag = 1 where flag = 0
go

Like this Try your hands on different alerts by going through the classes copied above .You can also go through the classes at MSDN :http://msdn.microsoft.com/en-us/library/ms186449(SQL.90).aspx

Hope this helps .I am also learning ropes on WMI and will post some good stuff as soon as I am ready .Keep reading as there is much more to come .

Happy learning .

Msg 3132, Level 16, State 1, Line 1

Today as soon as i reached office in the morning , an issue came up which was related to restoring of SQL Server database .The issue was that we were not able to restore one of the user database backups taken on some remote server .

The error we were getting was :

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

There was some .bak say test.bak file, which we were trying to restore and getting the error above.

This error says that there are 2 media families and only 1 has been provided .
Media families here means a Disk or a tape .If we take it as disk , then it means that "The backup was taken on two backup devices or say backup files (in case you have not created disk devices)" . This kind of backup is called as a srtipped backup where both the backup devices have some part of backup but not complete.

But the guy who took the backp says that he did nothing special that made the stripped backup i.e. he did not take the stripped backup at all .

Then how come the stripped backup took place on it own ....this was pretty strange.

I checked this issue through Bing and google and most of the people were saying the same that they never did intend to take the stripped backup .

Now, i had to prove the client that we are missing one backup device here or we are missing one .bak file , I did a small repro :


USE master
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'rest_issue',
@physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rest_issue.bak'
GO

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'rest_issue_1',
@physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rest_issue_1.bak'
GO

backup database testdba to rest_issue,rest_issue1
restore database testdba from rest_issue <-- used only one mediaset

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


restore database testdba from rest_issue,rest_issue1 <-- used both mediaset

Restore was successful..
So the error was genuine and working fine .

Further ,If you think that you will restore only 1 file i.e. data file and leave the log file using option partial , even that will not work as the backup for data file is also stripped.

Restore filelistonly will tell you the files in the backup set

Restore headeronly will give us the MediasetID number and other information

Restore labelonly will give us the BindingID number and other information

Restore verifyonly will fail with the same error if you use it on one file as it needs both the files


Solution :There is no solution to this issue you need to find out another device else it will not work (use restore headeronly and labelonly ,backupmediafamily, backupset for assistance) and use both the files to restore .Once you have it , you can issue this

command :
Restore database ibmdba from anoop_issue,anoop_issue1 with replace
OR Restore database ibmdba from disk = , disk = with replace

Why Hhis happens :)

This will happen if :

1) You have created two(or more) backup devices and taken backup on 2
(or more) devices or you can say stripped backup and while restoring yo uare using only one .

2) You are using GUI to take the backup without seeing that the backup is going to more than 1 device or files .If you select both the devices/files it will take stripped backups .If you insert 2 devices / files but select NONE of them then also it will happen (That should not happen and should give us the error "Please select a device") .And thats where the bug is .

So the BUG is :
-------------------------

If a user do not select a device in the backup set when more than one devices are existing , by default SQL Server selects all the devices and not giving the error "please select the device"....

If this default behaviour is by design then its not the correct design and should be changed .


Repro of this issue through GUI :
-> right click adventure works database > Tasks > backup
-> Under Destination remove anything if its mentioned by default.
-> Click on Add > a new box will pop up > select backup device you have created
-> you will get the device name > click on OK.
-> Again click on Add >a new box will pop up > select another backup device .
-> You will see that both the backup devices are unselected now (",).
-> If now you click ok it will take the backup .Ideally it should give the error that "please select the device".

I have filed a bug with MS through connect...

Happy learning

Error 14262:The specified server xxxxx019 does not exist

I got this error when I was trying to setup logshipping .I was strange
I checked the select * from sys.servers output and i was getting the entry of the server there .......whats wrong then .

I tried to ping that server using the server name .But it was timed out .I then tried to use the IP of that server while pinging with -a option and guess what ...
I found the different servername there ..... :D

this was the issue .Some admin changed the Server name which SQL Server did not pick u automatically .

i used sp_addserver and sp_drop server to add the right server and everything was on track ...

Regards
Abhay

MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)

I was installing SP2 on SQL Server 2005 x64 RTM ...Everything went fine but it got stuck at Notification Services and Client Tools .

The error SNIP is :
Error : Failed to install and configure assemblies D:\Program Files\Microsoft SQL Server (x86)\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087

Error message : Unknown error 0x80131501
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)

So , there was some issue with MSDTC ....and I am no tthe MSDTC guy ......
If you do not install Notification services,reporting services and client tools , MSDTC errror will not pop up ....

Moreover there is an option to retry on the error window .

-> I checked and found that MSDTC service was running fine .
-> I also checked if my account has all the priveleges at the domain level bla bla bla ..

-> Finally , i uninstalled and reinstalled MSDTC [DOS >> msdtc -uninstall and then MSDTC -install >> start MSDTC if its not].

once done , I clicked on retry and it worked just fine ...
We had the similar issue on another server and we resolved it in similar way ...

Hope it helps you too ..

Regards
Abhay

Getting SQL Server inventory for all the instances of SQL Server 2005 and above

This one really ate my head but finally came up with something .We (thanks to Sethi who an equal partner in taking the pain) have completely automated it but due to a bug in Excel or Microsoft SQL Server I could not export all the data to one Excel sheet .Result is that you need to copy the output and paste it to the excel sheet .mail me any changeso you are looking for or if you have issue swith the output (hint : check the query tab in SSMS and change ot appropriately :D).

You need to have the CMS servers (SQL Server 2008 Express) and all the instances registered .


-> This script will run on 2000,2005 and 2008 and will be using SQL Server express CMS as well.
-> We can live without SQL Server 2008 CMS but then the code will be 364 lines x numbers of server .
-> This Script will first check if SQL Server is 2000 or above and will only execute the code based on that check .
-> We can also print the output in to excel sheet using openrowset and opendatasource but its throwing a bug when multiple instances try to update the excel sheet using distributed transactions .
-> So we have decided to get the output all together in Query Analyzer and paste it to the excel Sheet.
-> Let us know if you need that code as well and we will add it as comment.
-> The CMS should have connectivity to all the servers (It has to be the Central Server)





Part 1:
-------

-- Install SQL Server 2008 Express Edition and open the management studio (Start -> Run -> SSMS)
-- Go to View --> Registered servers
-- Right click on the Central Management Servers and select Register Central Management Servers
-- Select the Express instance and register it .
-- open the test.regsrvr file in SSMS .
-- You will see few values inside various TAGS starting with <> and ending with .
-- If you see line 48 you will see the server group name .In our case its "test":
/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test
-- Do a CTRL+ F and also choose replace .You can give any name instead of test and replace it or keep the same name .
-- Now search for the tag here you will see three instances registered .Its Line 72.
-- You can remove or add the instance names here .
-- All you need to do is copy the below code -> change the instance name -> and paste it back in the code just before the TAG:

/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test/RegisteredServer/abchaudh\ASLAN

-- Repeat the activity for as many instances as you want .
-- Now go to line 96 and you will see the individual instance information .The code will be like this :




/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test/RegisteredServer/abchaudh\ASLAN







/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test


abchaudh\ASLAN

abchaudh\ASLAN
DatabaseEngine
server=abchaudh\ASLAN;integrated security=true
None




-- Just change the instance name with your instance and paste it just before line 167 i.e. before the tag
-- Repeat this step i.e. add as many instances as needed be .
-- Thats it .Save this file and close it .
-- Go back to the CMS tab --> right click the CMS instance under Central management servers --> tasks --> Import
-- Import this test.regsrvr file .
-- You will see the instances registered under the 2008 Express instance.
-- Click on the Express instance and open a new Query .
-- Paste the code below in Part 2 and execute it .

NOTE : Make 2 different groups . One for SQL Server 2000 instances and another for 2005/2008 instances .Use the same test.regsrvr file .





Part 2:
--------
--Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table prodver
create table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))
insert into prodver exec xp_msver 'ProductVersion'
if (select substring(Charcater_Value,1,1)from prodver)!=8
begin


-- Step 2: This code will be used if the instance is Not SQL Server 2000

Declare @image_path nvarchar(100)
Declare @startup_type int
Declare @startuptype nvarchar(100)
Declare @start_username nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @log_directory nvarchar(100)
Declare @key nvarchar(1000)
Declare @registry_key nvarchar(100)
Declare @registry_key1 nvarchar(300)
Declare @registry_key2 nvarchar(300)
Declare @IpAddress nvarchar(20)
Declare @domain nvarchar(50)
Declare @cluster int
Declare @instance_name1 nvarchar(100)
-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name!='MSSQLSERVER'
Set @instance_name=@instance_name

Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name1!='MSSQLSERVER'
Set @instance_name1='MSSQL$'+@instance_name1
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
If @registry_key is NULL
set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';
SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT

Set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)

--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.

declare @Out nvarchar(400)
SELECT @Out = COALESCE(@Out+'' ,'') + Nodename
from sys.dm_os_cluster_nodes

-- Step 5: printing Server details

SELECT
@domain as 'Domain',
serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName',
CPU_COUNT as 'CPUCount',
(physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB',
@Ipaddress as 'IP_Address',
@instance_name1 as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@out as 'ClusterNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From sys.dm_os_sys_info


-- Step 6: Printing database details

SELECT
serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine'
,@instance_name1 as InstanceName,
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
, d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
--, d.is_published as 'Publisher'
--, d.is_subscribed as 'Subscriber'
--, d.is_distributor as 'Distributor'
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication'
, m.mirroring_state as 'MirroringState'
--INTO master.[dbo].[databasedetails]
FROM
sys.sysdatabases d INNER JOIN sys.sysaltfiles s
ON
d.dbid=s.dbid
INNER JOIN sys.database_mirroring m
ON
d.dbid=m.database_id
ORDER BY
d.name





--Step 7 :printing Backup details

Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.database_id 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
--INTO [dbo].[backupdetails]
from sys.databases d inner join msdb.dbo.backupset b
On b.database_name =d.name


End
else

begin



--Step 8: If the instance is 2000 this code will be used.

declare @registry_key4 nvarchar(100)
declare @Host_Name varchar(100)
declare @CPU varchar(3)
declare @nodes nvarchar(400)
set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
declare @mirroring varchar(15)
set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
Declare @reg_node1 varchar(100)
Declare @reg_node2 varchar(100)
Declare @reg_node3 varchar(100)
Declare @reg_node4 varchar(100)

SET @reg_node1 = N'Cluster\Nodes\1'
SET @reg_node2 = N'Cluster\Nodes\2'
SET @reg_node3 = N'Cluster\Nodes\3'
SET @reg_node4 = N'Cluster\Nodes\4'

Declare @image_path1 varchar(100)
Declare @image_path2 varchar(100)
Declare @image_path3 varchar(100)
Declare @image_path4 varchar(100)

set @image_path1=null
set @image_path2=null
set @image_path3=null
set @image_path4=null


Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table nodes
Create table nodes (name varchar (20))
insert into nodes values (@image_path1)
insert into nodes values (@image_path2)
insert into nodes values (@image_path3)
insert into nodes values (@image_path4)
--declare @Out nvarchar(400)
--declare @value nvarchar (20)
SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null

-- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
IF @instance_name!='MSSQLSERVER'

BEGIN
set @system_instance_name=@instance_name
set @instance_name='MSSQL$'+@instance_name

SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'


EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT


END

IF @instance_name='MSSQLSERVER'
BEGIN
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'



EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
--EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT

END
set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)

--Step 10 : Using ipconfig and xp_msver to get physical memory and IP

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tmp
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))
insert into tmp([index],name,internal_value,character_value) exec xp_msver PhysicalMemory

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table ipadd
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
insert into ipadd (IP)exec xp_cmdshell 'ipconfig'
delete from ipadd where ip not like '%IP Address.%' or IP is null


-- Step 11 : Getting the Server details

SELECT top 1
@domain as 'Domain',
serverproperty('Machinename') as 'MachineName',
@CPU as 'CPUCount',
cast (t.internal_value as bigint) as PhysicalMemoryMB,
cast(substring ( I.IP , 44,41) as nvarchar(20))as IP_Address,
serverproperty('Instancename') as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@Out as 'ClustreNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From tmp t inner join IPAdd I
on t.server = I.server

-- Step 12 : Getting the instance details

SELECT
serverproperty ('Machinename') as 'Machine',
serverproperty ('Instancename') as 'InstanceName',
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
,d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication',
@Mirroring as 'MirroringState'
FROM
sysdatabases d INNER JOIN sysaltfiles s
ON
d.dbid=s.dbid
ORDER BY
d.name

-- Step 13 : Getting backup details

Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.dbid 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
from sysdatabases d inner join msdb.dbo.backupset b
On b.database_name =d.name


-- Step 14: Dropping the table we created for IP and Physical memory

Drop Table TMP
Drop Table IPADD
drop table Nodes

end
go

-- Step 15 : Setting Nulls and Quoted identifier back to Off

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


Happy learning
Abhay
hi_abhay78@yaoo.co.in

what if yo udo not know the servers to whic hlogins belong......

Request :
I was give a text file with a list of logins to be removed from SQL Servers as those employees were no more existing .I asked if they know the server names where the logins exist .The answer was an expected NO ....made my task more dificult .But I had to do it . I came out with a script that can be used to find the instance name where the logins exist .I have not created the script to remove those logins as its pretty simple ,but have explained in brief what can be done .So my juniors will do it :) ...

Requirements :
-> SQL Server 2008 Express edition [Central management Servers]
We will be using it to execute queries on a group of servers and getting the consolidated result .

-> .regsrvr file
We will modify this file and add steps to add more servers instead of registering instances manually one by one .

-> login_info view
This view will keep the information about all the logins having users and schemas associated with them .If there is a login which has no user associated with it , it will not show up in this view.
-> Instances table
This will keep the list of all the 2005 instances in a domain .
-> Del_logins table
This will keep the list of all the logins that have to be searched for deletion.

-> Instances.txt
This will keep the list of the instances that will be BCPed in to instances table

-> logins.txt
This will have the list of all the logins that have to be deleted.This wil be BCPed to del_login table
-> BCP
We will be using BCP in ,to import values from instances.txt and logins.txt.

-> :connect
We wil be using :connect to connect to instances and and doing BCP in.

-> WMI
We will use WMI to copy files to different servers .



Important :
Before doing anything you need to register the servers in the CMS .
register a couple of servers and then export the .regsrvr file somewhere.
open that file in SQl Server QA .You can easily see the pattern of instance registration .Just copy the same as many time as there are the instances and change the instance names .Import it back and you have the instances under the server group name .



--Step 1: create login_info view on all servers using CMS.

use master
go
create view login_info as
select d.name as [user_name],d.type_desc as [user_type],s.name as[login_name],s.type_desc as [login_type],d.default_schema_name as [schema_name]
from sys.database_principals d right outer join sys.server_principals s
on d.sid =s.sid
where s.type not in ('R')


--Step 2:Copy the logins.txt that the client gave to all the 2005 servers in the common drive .
--this is a VBS file which will copy test.doc to different servers listed in computers.txt.

Const ForReading = 1
Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Computers.txt")

Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine
strRemoteFile = "\\" & strComputer & "\C$\Scripts\LOGINS.TXT"
objFSO.CopyFile "C:\Scripts\Test.doc", strRemoteFile, OverwriteExisting
Loop




-- step 3: Create a text file instances.txt with 2005 instance names on any one server with SQL Server installed .
-- you can copy it from the inventory or try importing it with xp_cmdshell using OSQL -L and inporting it to a table
/*
sp_configure 'show advanced options',1
reconfigure with override
sp_configure 'xp_cmdshell',1
reconfigure with override

create table test (ins varchar(30))
go

insert into test exec xp_cmdshell 'osql -L'

Then delete few unnecessary rows or you need to modify the output by >> Query >> Query options

*/

-- Step 4: Create the instances table in the 2005 instance server you copied the file above.

use master
go
Create table instances (iname sysname)



-- Step 5: BCP in the instance names from the instances.txt files to instances table .
xp_cmdshell 'bcp master.dbo.instances in c:\temp\instances.txt -T -c -S

-- sTEP 6: create del_logins in all the instances using CMS
create table master.dbo.del_logins (lname varchar(50))

-- Step 7: BCP in values from the text file to the del_login table (run this command on the same server where you have instances table .Copy the output to the CMS and execute)

Create table #inst (iname sysname,flag int identity (1,1) )
Insert into #inst select iname from instances
Declare @@flag1 int
Select @@flag1 =max (flag) from #inst
While @@flag1 >0
Begin
Declare @string nvarchar (100)
DECLARE @string1 nvarchar(50)
Declare @iname sysname
Select @iname =iname from #inst where flag=@@flag1
Set @string ='xp_cmdshell '+ '''bcp master.dbo.del_logins in c:\temp\logins.txt -T -c -S' +@iname+''''
set @string1= ':connect' +@iname
print @string1
print @string
print 'GO'
Set @@flag1 =@@flag1 -1
End
Drop table #inst

-- Step 8: We need to run an intersect query in CMS

select login_name from master.dbo.login_info
intersect
select lname from master.dbo.del_logins

Result :
----------

This output will give you the logins that exist on the different servers .And then you can drop those logins one by one .

Other way to drop the logins is to create a table with servername , login name and a flag default to an identity starting with 1.

create the SP around the intesect output and then insert it to this table .
run it in the while loop and drop the logins .

You can also create the scrip to change the schema to DBO before dropping the users and logins.

mail me for any clarification at : hi_abhay78@yahoo.co.in

Happy learning..
Abhay

SQL 2008 setup failes : This Process does not possess the SeSecurity privilege' privilege which is required for this operation.

Just got off this case on an idle Saturday :) ....let me share it .

SQL Server 2008 on windows server 2008 .........I just hate this combination ...sorry but i really do ...

Actually , i hate windows server 2008 because of its too many security restrictions ..

anyway lets come straigh to the topic .

The error is :This Process does not possess the SeSecurity privilege' privilege which is required for this operation

This is Manage Auditing and Security Log right which can be seen in the local security settings (start >> run >> secpol.msc >> local policies >> User rights assignments). for more information see : http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/bb530716(VS.85).aspx.

Access to the security event log is governed by SeSecurityPrivilege.Holders of the privilege have Read, Clear, and Backup permission. Holders of SeAuditPrivilege can write to the log via internal LSA APIs only.LocalSystem can write to the security event log via the ReportEvent API due to permission granted via the log ACL.

By default, these are the privilege assignments:

SeSecurityPrivilege Administrators, LocalSystem
SeAuditPrivilege LocalSystem

So , you need to add your domain account or the domain group to which you belong in to this policy ......and yes you need to reboot the server.

After reboot you will see SWEET Success...

Hope this helps ..

Regards
Abhay

No connection could be made because the target machine actively refused it.

I faced this situation yesterday .
We were using Tivoli to connect to SQL Server 2005 (Clustered instance) to take backups.

But it was failing with the error (not printing the complee error here):
No connection could be made because the target machine actively refused it.

I asked myself why the server is refusing the client request ??
Since the connection was failing , this issue had to do with connectivity .

I started checking few things :

1) Port on which SQL is listening
2) named or default instance
3) Protocols enabled @ server level and @ client level .

It was a default instance on some port other than 1433 (as hackers can catch it easily).We found that it was SQL Server that was using this port ...how ??
By connecting to SQL Server through management studio using

tcp:servername\instancename , portname

and it connected .We also did netstat -aon and or -aonb(this takes a lot of time as it also finds the exe name) and confirmed that only SQL Server is using this port.

Then we checked SQL Server Client network utility (cliconfg) and found that no protocols were enabled .We enabled named pipes and TCP/IP (not sure how it got disabled).

Tried to connect tivoli which failed again .Finally we forcibly made tivoli to connect to SQL Server usin gthat port by creating a TCP/IP alias ...
This worked and our issue got resolved ..

In past I found that the port SQL Server is using is either blocked or being used by other application .I that case stop that application and create the alias .

Hope this helps !!!

Regards
Abhay

Recreating Builtin/administrator account in SQL Server 2005

Someone at client's site removed the builtin admin from SQL Server as it was one of the risks mentioned in the agreement .Everything was fine .

However,during maintenance SQL server did not come up after the server reboot .They tried it with SA but they also forgot the SA pasword :-) ....awesome ...
They were about to uninstall and reinstall SQL server when we finally did the steps below .It took me a lot of time but one of the options worked (with my previous MS experience ofcourse :) ).Below are the repro steps and the solution .




Repro of issue :

1) Delete the Builtin/administrator account .
2) tested it through sqlcmd and got the error 18456 Level 14 State 1.
3) assume that i have forgotten the SA password as well.


Solution:

1) Stop SQL Server service and start it with -m
2) go to C:\Program Files\Microsoft SQL Server\90\Tools\Binn through cmd prompt
3) type sqlcmd -E and hit enter .If its named instance then sqlcmd -
-SServer\instance -E and hit enter.
4) you will get > sign
5) commands you need to use
use master
go
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=
[master], DEFAULT_LANGUAGE=[us_english]
go

6) You are done.Exit out of it .
7) restart you SQL Server service without -m parameter.

Happy learning .....

Script to copy errors form error log and get it through mail .

This tim ei was asked to create job/SP that is capable to send a daily mail to DBAs with selected errors form error logs. Below are the steps to create a stored procedure to copy the errors from errorlogs to a table and then send the table as an attachment to our inbox .You might have to modify it as per your need .Or you can use WMI for SQL Server errorlogs ..I did not try that yet ...but will post it once done ...

for mailing you can also use CDO...will post that stuff soon :)






Steps included are :



1) Creating database mail account



2)Creating database mail profile



3) increasing the errorlogs to a default of 50 from 7



4) creating a table to store xp_readerrorlogs values



5) filtering the errors though a query



5) using database mail to send the output as an attachment to inbox



6) Finally recycling the errorlog











USE [msdb]

GO



/*Step 1:Creating a database mail account */

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Test1',

@description = 'Test account for Database Mail',

@email_address = 'your_email.com',

@display_name = 'Test1',

@mailserver_name = 'smtp..com'



/*Step 2:Creating a database mail profile */

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'TestProfile',

@description = 'Test Profile for database mail'







/*Step 3: adding database mail account to database profile created earlier*/

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'TestProfile',

@account_name = 'Test1',

@sequence_number = 1



/*Step 4:To make the TestProfile we created a default public profile */



EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'TestProfile',

@principal_name = 'default',

@is_default = 1 ;







/* To increase the number of error logs to 50 :

1: regedit >> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer

2: create a REG_DWORD key 'NumErrorlogs' and set the value to 50

*/



/* Step 6:create a table for keeping the error log values */



USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO



CREATE TABLE [dbo].[errorlog](

[logdate] [date] NULL,

[processinfo] [varchar](20) NULL,

[line] [varchar](1000) NULL

) ON [PRIMARY]



GO



SET ANSI_PADDING OFF

GO







/* Step 7: Create a stored proc for :

-> Truncating the table we created above .

-> Inserting the values from error log into the table we created above

-> Sending the mail .

-> cycling the errorlog

*/



Create proc [dbo].[ErrorlogEmail]

as

truncate table errorlog

insert into errorlog exec xp_readerrorlog

DECLARE @SQL varchar(2000)

DECLARE @date varchar (2000)

DECLARE @File varchar(1000)

select @date= convert(date,GETDATE())

SET @SQL = 'select * from errorlog where line like (''%Severity%'') or line like (''15 seconds'') or line like (''%latch%'')or line like (''%BEGIN STACK DUMP%'')or line like (''%time-out%'')and logdate = convert(date,GETDATE())'

SET @File = 'Errorlog report'+@date+'.csv'



EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'test',

@recipients = 'your email.com',

@subject = 'Errorlog report',

@body = 'Attached please find the Daily errorlog report',

@query =@SQL ,

@attach_query_result_as_file = 1,

@query_attachment_filename = @file,

@query_result_header = 1,

@query_result_separator = ' ',

@query_result_no_padding = 1,

@query_result_width = 32767



/* Step 8: recycle errorlog */

EXEC master.sys.sp_cycle_errorlog

print 'Error log recycled'



Hope this helps ...

Happy Learning ...

WMI alert that can detect any alteration to the SP [SQL 2005 and above]

This is the WMI alert that can detect any alteration to the Stored Procedure .Its going to tell you the date , login name , user name , object name .
Like this we can create the alert on anything you want .As I said in my previous post ,WMI is very flexible , uses less resources and much faster .


/* Step 1: creating the table to capture the Alter Proc information */

USE adventureworks
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Alterprocevents]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Alterprocevents]
GO

CREATE TABLE [dbo].[Alterprocevents] (
[AlertTime] [datetime] NOT NULL ,
[Object_name] varchar(100),
[Login_Name] varchar(100),
[user_name] varchar(100),
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_AlterprocEvents_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

--CREATE INDEX [Alterproc_IDX01] ON [dbo].[Alterprocevents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]
--GO

/*Step 2 : Creating the Job that will enter values into the Alterprocevents table created above*/
/*Error handling is also added and we are running it in a transaction*/
USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Alter proc event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Alter proc event', @delete_unused_schedule=1

GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Alter proc event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to Capture Alter proc event events',
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO [dbo].[Alterprocevents](
AlertTime,
object_name,
login_name,
user_name
)
VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(objectname)))'',
N''$(ESCAPE_NONE(WMI(loginname)))'',
N''$(ESCAPE_NONE(WMI(username)))''
)',
@database_name=N'adventureworks',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/* Step 4: Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to alterproc_change')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to alterproc_change'

GO

DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to alterproc_change',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DDL_PROCEDURE_EVENTS',
@job_name='Capture Alter proc event' ;
GO


Hope you like it .
Regards
Abhay

WMI alert for deadlocking (no need to configure trace flags 1222) : For SQL 2005 and above only

Before I proceed forward , I just wanted to say that scriting through WMI is amazing ..You will take some time in learning it and might get bored with syntax ...But believe me it will catch your interest as anything is posible wih it ..

Here is the WMI script to trace the deadlock .Its only for SQL Server 2005 and above as I have to see if SQL Server 2000 supports WMI namespace by default.
This also has the mailing script , which we can use for all kinds of alerts .WMI scrips are faster and less resource consuming .I have also added error handling and kept everything important in a transaction , so that it rollsback completely .This is for one database but we can configure for as many by only adding more alerts which will be using the same job.

IMP : Its not going to work on RTM as there is a Bug where the job fails giving the incorrect syntax error (actualy it parses successfully).I applied SP3 and it worked .So not sure if it works on SP1 and SP2 .

Part 1 :

1) Creating the table to capture the deadlock information.
2) Creating the Job that will enter values into the Deadlockevents table created above.
3) Insert graph into LogEvents.
4) Creating the alert and associating it with the Job to be fired.
5) Create a stored proc for sending the deadlock information as .CSV file through the mail.
6) Changing the flag to 1 so that next time this information is not sent.


/* Step 1: creating the table to capture the deadlock information */

USE
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DeadlockEvents]
GO

CREATE TABLE [dbo].[DeadlockEvents] (
[AlertTime] [datetime] NOT NULL ,
[DeadlockGraph] [xml],
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_DeadlockEvents_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

CREATE INDEX [DeadlockEvents_IDX01] ON [dbo].[DeadlockEvents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the Deadlockevents table created above*/
/*Service account and sql operator option are optional*/
/*Error handling is also added and we are running it in a transaction*/
USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Deadlock Graph')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Deadlock Graph', @delete_unused_schedule=1

GO
DECLARE @ServiceAccount varchar(128)
SET @ServiceAccount = N''
DECLARE @SQLOperator varchar(128)
SET @SQLOperator = N''

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to DEADLOCK_GRAPH events',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@ServiceAccount,
@notify_email_operator_name=@SQLOperator, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO DeadlockEvents (
AlertTime,
DeadlockGraph
)

VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(TextData)))''
)',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/*Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to DEADLOCK_GRAPH')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to DEADLOCK_GRAPH'

GO

DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Capture Deadlock Graph' ;
GO


/* Step 5: Create a stored proc for sending the deadlock information as .CSV file */

Create proc [dbo].[Deadlock_rpt]
as
DECLARE @SQL varchar(2000)
DECLARE @date varchar (2000)
DECLARE @File varchar(1000)
select @date= convert(date,GETDATE())
SET @SQL = 'select * from deadlockevents where flag = 0'
SET @File = 'Deadlock report'+@date+'.csv'

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'your email.com',
@subject = 'Deadlock report',
@body = '***URGENT***Attached please find the deadlock report',
@query =@SQL ,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file,
@query_result_header = 1,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_width = 32767


/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
update dbo.DeadlockEvents set flag = 1 where flag = 0
go


Part 2:

1) Creating the DbMmail account
2) Creating a DbMail profile.
3) Adding account to profile.
4) making the profile as public.


USE [msdb]
GO

/*Step 1:Creating a database mail account */
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test1',
@description = 'Test account for Database Mail',
@email_address = 'your_email.com',
@display_name = 'Test1',
@mailserver_name = 'smtp..com'

/*Step 2:Creating a database mail profile */
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestProfile',
@description = 'Test Profile for database mail'



/*Step 3: adding database mail account to database profile created earlier*/
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestProfile',
@account_name = 'Test1',
@sequence_number = 1

/*Step 4:To make the TestProfile we created a default public profile */
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestProfile',
@principal_name = 'default',
@is_default = 1 ;

How to drop all the user databases in one GO

It was Saturday Morning and I had a task @ hand .
One of my collegues wanted me to write a script to drop all the user databases at one go ..

I was initially reluctant as I thought any DDL can not take variables .But then I tried with some other stuff which did not use variables with DMLs.

Initially I came up with the script below :

Create table drop_db(name sysname)
Insert into drop_db select name from sysdatabases where dbid >4
Select 'drop database ' + name from db_name

Copy the output and execute.You canuse the same trick for other purposes as well .

But then I wanted to give it some more try and came up with the final script .

Drop table #db_name
Go
Create table #db_drop (name sysname,flag int identity (1,1) )
Insert into #db_drop select name from sysdatabases where dbid>4
Declare @@flag1 int
Select @@flag1 =max (flag) from #db_drop

While @@flag1 >0
Begin
Declare @string nvarchar (30)
Declare @dbname sysname
Select @dbname =name from #db_drop where flag=@@flag1
Print @dbname + ' is being deleted'
Set @string =' drop database ' + @dbname
Execute sp_executesql @string
Set @@flag1 =@@flag1 +1
End
Drop table #db_drop

Useful tools from Sysinternals

I was searching a tool capable of showing the Server enviornment on the desktop ..
Well its indeed BigInfo :) .You will find it on Sysinternal site .
Infact there are a lot of other useful tools that you can use ..or simply download the complete suit ..
Here you go ..

http://technet.microsoft.com/en-us/sysinternals/bb897557.aspx
Enjoy (",)

How to see the SSIS packages for the Named Instance (2005/2008) of SQL Server .

-> Go to C:\Program Files\Microsoft SQL Server\100\DTS\Binn [this is SQL Server 2008 but the path would be same for 2005 as well .instead of 100 it will be 90].
-> Find the file MsDtsSrvr.ini.xml and open it in notepad .
-> You will see 10-12 XML format lines .
-> By default you will see : .
-> Remove the DOT (.) and replace it with the server name \instance name .
-> Restart the integertion service from Services console .

Hope this helps .

Manually Add Full Text Search Resources For A SQL 2005 Failover Cluster

Reciently, I Faced this issue where the customer did not install the FT search initially and later on they came for help since it was a cluster.so I thought to share it here .Others are most welcome to Edit or Add ..

-> First you need to check if FT search service is installed (in case its there and someone deleted the resource from cluadmin)...if its not there in services or configuration manager , install it through the setup .

->In the Cluster Administrator, right-click on 'Resources' and choose New -> Resource

->In the New Resource dialog, choose 'Generic Service' for the type and give it a
name like SQL FULLTEXT. Any name is fine. Choose the desired group for the resource.

->In the next screen choose the nodes that the Full-text resource can run on.These should be the same as your SQL Server resource.

->In the next screen don't do anything. Full-text search doesn't need any dependencies.

->In the Generic Service Parameters screen type msftesql$ in the
'Service Name' box, where is the Virtual Server name for the SQL
Server. You can verify this name by opening Services.msc (Start -> Run -> type
'Services.msc' -> OK) and right-clicking on the SQL ServerFulltext Search service
and choosing properities. Us the Service Name in the properties dialog as the
Service Name in the New Resource dialog.

->In the 'Startup Parameters' box, enter the parameters shown in the service
property dialog (Start -> Run -> type 'Services.msc' -> OK -> right-click on SQL
ServerFulltext Search service -> choose 'Properties) at the end of the path in the
'Path to executable' box. For example: -s:MSSQL.2 -f:INST2

-> In the next screen don't do anything. Leave the 'Registry Replication'
information blank.

-> Click finish. The Full-text resource should be visible in your SQL Server group
in the Cluster Administrator.

-> Test by bringing the Full-text resource online and failing it over to the other
node.



Regards
Abhay

Perfmon Counter recommended values for SQL Server 2005

Below are the perfmon counter benchmarks that I generally refer to :


Memory
Available Mbytes
> 100MB

Paging File
%Usage
< 70%

Process (sqlservr)
%Privileged Time
< 30% of %Processor Time (sqlservr)

Processor
%Privileged Time
< 30% of Total %Processor Time

PhysicalDisk
Avg. Disk Sec/Read
< 8ms

PhysicalDisk
Avg. Disk sec/Write
< 8ms (non cached) < 1ms (cached)

SQLServer:Access Methods
Forwarded Records/sec
< 10 per 100 Batch Requests/Sec

SQLServer:Access Methods
FreeSpace Scans/sec
<10 per 100 Batch Requests/Sec

SQLServer:Access Methods
Full Scans / sec
(Index Searches/sec)/(Full Scans/sec) > 1000

SQLServer:Access Methods
Workfiles Created/Sec
< 20 per 100 Batch Requests/Sec

SQLServer:Access Methods
Worktables Created/Sec
< 20 per 100 Batch Requests/Sec

SQL Server:Buffer Manager
Buffer Cache hit ratio
> 90%

SQL Server:Buffer Manager
Free list stalls/sec
< 2

SQL Server:Buffer Manager
Lazy Writes/Sec
< 20

SQL Server:Buffer Manager
Page Life Expectancy
> 300

SQLServer:Buffer Manager
Page lookups/sec
(Page lookups/sec) / (Batch Requests/sec) < 100

SQL Server:Locks
Lock Requests/sec
(Lock Request/sec)/(Batch Requests/sec) < 500

SQLServer:SQL Statistics
SQL Compilations/sec
< 10% of the number of Batch Requests/Sec

SQLServer:SQL Statistics
SQL Re-Compilations/sec
< 10% of the number of SQL Compilations

How to find how Cache/Bpool is being used and what objects are using the cache/Bpool ..

1: To determine what plans are in the cache and how often they're used we can use
sys.dm_os_memory_cache_counters dm view .



SELECT TOP 6
LEFT([name], 20) as [name],
LEFT([type], 20) as [type],
[single_pages_kb] + [multi_pages_kb] AS cache_kb,
[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC



Here :

CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers.This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

You will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dmv.This will give us idea about which cache is getting filled up .

Generally, you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP(because we cannot frame everything in to SPs) , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.

You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.



2 : After this to know the querry are being cached and used , we can use
sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views.

select TOP 50
objtype,
usecounts,
p.size_in_bytes/1024 'IN KB',
LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

and then we can check the query plans(and size) for the one we have some doubt .

3: To find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.The query below can give us total currrent size of buffer pool .


select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors



After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .



SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

Setting CPU Priority level for a SQL Server (high/ low)

SQL Server by default runs at priority base 7 , which is normal .
To raise it to 13 we can use Priority boost in sp_configure .

But reciently , I got a question if we can set the CPU priority to lower than normal.There is no option for this inside SQL Server.

So there was in inderect answer to this : use affinity mask , so that SQL is restricted to particular CPU/CPUs .But thats not the correct answer .

So is there a way to do that via task manager .Well , it has the option but we cant as we get some access denied error

What you can do is :

1) Open the DOS window >>Start /Low "\Binn\sqlservr.exe"
2) This will open another window .
3) In that new window type : "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqlservr.exe"[path of sqlserver executable for an instance] -sMSSQLSERVER <--instance name [copy it from the services dialog box]
4) connect to the management studio or OSQL/ISQL.
5) cross check in task manager .You will see that SQL Server is running under low prority
6) Now from the task manager you can change the priority ...not sure why :) ...

Hope this helps .......