Scripts to prepare SQL Server Inventory

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010

3

You never know, when you may need to rebuild a SQL sever with exact properties and configurations. Although there are dozen of 3rd party tools and software available in market to prepare enterprise level SQL Server inventory hosted in your environment like Microsoft Assessment and Planning (MAP), but keeping your own DBA script handy is big pleasure. Here is my own version of script that can bring you many hidden properties of SQL Server that are worth storing along with SQL inventory.

 

SET NOCOUNT ON
-- FIND SQL SERVER COLLATION
declare @SerCollation varchar(70)
select @SerCollation=convert(sysname, serverproperty(N'collation'))
PRINT 'SQL Server Default Collation is: '+@SerCollation
 
-- FIND SQL SERVER AND AGENT SERVICE ACCOUNT INFORMATION
PRINT 'SQL & Agent Serice Account Information: '
DECLARE @SQLserviceaccount varchar(50), @AgentServiceaccount varchar(50)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName',
@SQLserviceaccount OUTPUT, N'no_output'
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT', N'ObjectName',
@AgentServiceaccount OUTPUT, N'no_output'
SELECT @SQLserviceaccount as [SQLServer_ServiceAccount], @AgentServiceaccount as SQLAgent_ServiceAccount
 
-- FIND SQL SERVER AUTHENTICATION, AUDIT MODE & INSTALLATION PATH
PRINT 'SQL Server Authentication, Audit Mode & Installation path: '
declare @SmoLoginMode int, @SmoRoot nvarchar(512), @SmoAuditLevel int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', 
N'SQLPath', @SmoRoot OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', @SmoLoginMode OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'AuditLevel', @SmoAuditLevel OUTPUT
 
SELECT(case when @SmoLoginMode =1 then 'Windows Authentication'
else 'Mixed Mode Authentication'  END) AS [LoginMode],
(case When @SmoAuditLevel=0 then 'None.'
When @SmoAuditLevel=1 then 'Successful Logins Only'
When @SmoAuditLevel=2 then 'Failed Logins Only'
When @SmoAuditLevel=3 then 'Both Failed and Successful Logins Only'
else 'N/A' END) AS [AuditLevel],
@SmoRoot as [SQLInstallation Location]
 
-- FIND LICENSE AND PAGE FILE INFORMAITON
PRINT 'License and page file information: '
Declare @version varchar(47), @CDKey varchar(40), @PageFile varchar(50)
Select @version = @@version
create table #PageFileDetails (data varchar(500))
insert into #PageFileDetails  exec master.dbo.xp_cmdshell 'wmic pagefile list /format:list'
select @PageFile=rtrim(ltrim(data)) from #PageFileDetails where data like 'AllocatedBaseSize%'
drop table #PageFileDetails
 
If charindex('2000',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration',
@value_name='CD_KEY', @Value=@CDKey OUTPUT
SELECT 'SQL 2000' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
 
Else If charindex('2008',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
 
Else If charindex('2008 R2',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\150\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008 R2' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
Else
SELECT @version AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses

Setup a low disk space alert for SQL Server drives

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010

2

This is the pretty easy and clean script that you may use to check periodically your database server drive space. You can create a job to run on hourly basis to execute this code, along with a database mail code to send you alert if  any of drive  has less than 15% or 20% threshold value.

-- Create a global temp table
CREATE TABLE ##space( dletter varchar(3), tspace BIGINT, fspace int, percentfree numeric(5,2))
-- Insert drive details
INSERT INTO ##space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
-- Declare variables
DECLARE   @oFSO   INT, @oDrive INT, @drsize VARCHAR(255), @ret   INT
-- invoke OACreate
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
DECLARE @dletter VARCHAR(3), @fspace INT, @tspace BIGINT
while (select count(*) from  ##space where tspace is null)>0
begin
   select top 1 @dletter = dletter  + ':\',@fspace = fspace from ##space where tspace is null
   EXEC   @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
   EXEC   @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
   UPDATE   ##space SET   tspace = CAST(@drsize AS BIGINT) WHERE   lower(dletter) + ':\'   = lower(@dletter)
   EXEC master.dbo.sp_OADestroy @oDrive
end
EXEC master.dbo.sp_OADestroy @oFSO
update   ##space set   percentfree = fspace/((tspace/1024.0)/1024.0)*100 
-- Select your data
select [Drive] = dletter ,
         [Total Space GB]= convert(numeric(10,3), (tspace/1024.0)/1024.0/1024) ,
         [Free Space GB]=convert(numeric(10,3),fspace/1024.0) ,
         [% Free]= percentfree 
         from   ##space
-- Drop temporary table
drop table ##space

What you ‘Currently’ don’t get with SQL Azure?

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 21-12-2010

1

image
SQL Azure is cloud based relational database platform built on SQL Server technology; it allows storing structured, semi-structured and unstructured data over the cloud. Behind the scene databases are hosted on cluster SQL servers, and are load balanced plus replicated for scalability & high availability purposes. Each logical Server consists of one master and N number of user databases based on Wed or Business Edition.

SQL Azure Standard Consumption Pricing (check offers available with MSDN subscription and Microsoft Partner Network)
Web Edition Database
•    $9.99 per database up to 1GB per month
•    $49.99 per database up to 5GB per month

Business Edition Database
•    $99.99 per database up to 10GB per month
•    $199.98 per database up to 20GB per month
•    $299.97 per database up to 30GB per month
•    $399.96 per database up to 40GB per month
•    $499.95 per database up to 50GB per month

How to connect to SQL Azure database:  You may use SQL Server management studio or

image

Alternatively you can choose SQLCMD to connect to SQL Azure database:
sqlcmd -U <ProvideLogin@Server> -P <ProvidePassword> -S <ProvideServerName> -d master

How to get started?
Once connected to SQL Azure instance. If you pass a very basic database creation command “CREATE DATABASE SQLKnoweldge”, it will create default web edition database of maxsize 1GB. You can specify maxsize as like:

CREATE DATABASE SQLKnoweldge (EDITION=’WEB’, MAXSIZE=5GB)

Following command will create a database with default Maxsize of 10GB that you can increase up to 50GB.

CREATE DATABASE SQLKnoweldge (EDITION=’BUSINESS’)

What if you do not specify edition? It will check database size and bases value it will determine edition. Say, if MAXSIZE is set to 10, 20, 30, 40 or 50GB then its business edition. Here is database creation syntax for SQL Azure.

CREATE DATABASE database_name 
{
   (<edition_options> [, ...n]) 
}
<edition_options> ::= 
{
   (MAXSIZE = {1 | 5 | 10 | 20 | 30 | 40 | 50} GB) 
    |(EDITION = {'web' | 'business'})
}[;]
To copy a database:
CREATE DATABASE destination_database_name
     AS COPY OF [source_server_name.] source_database_name [;] 

What you ‘Currently’ don’t get with SQL Azure?
In addition to SQL Profiler, Perfmon, Trace Flags, Global Temporary Tables, Distributed Transactions, SQL Azure does not support heap tables, a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table. That is the reason ‘select into’ statement fails. Read committed Snapshot Isolation (RCSI) is default instead Read Committed in SQL server; Connection timeout is 30 minutes plus long running transactions can be terminated without your knowledge. Here is the MS list for more details:
SQL Server 2008 R2 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2008 R2 are not supported by SQL Azure:
•    SQL Server Utility
•    SQL Server PowerShell Provider. PowerShell scripts can be run on an on-premise computer, however, and connect to SQL Azure using supported objects (such as System Management Objects or Data-tier Applications Framework).
•    Master Data Services
SQL Server 2008 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2008 are not supported by SQL Azure:
•    Change Data Capture
•    Data Auditing
•    Data Compression
•    Extended Events
•    Extension of spatial types and methods through Common Language Runtime (CLR)
•    External Key Management / Extensible Key Management
•    FILESTREAM Data
•    Integrated Full-Text Search
•    Large User-Defined Aggregates (UDAs)
•    Large User-Defined Types (UDTs)
•    Performance Data Collection (Data Collector)
•    Policy-Based Management
•    Resource Governor
•    Sparse Columns
•    SQL Server Replication
•    Transparent Data Encryption
SQL Server 2005 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2005 are not supported by SQL Azure:
•    Common Language Runtime (CLR) and CLR User-Defined Types
•    Database Mirroring
•    Service Broker
•    Table Partitioning
•    Typed XML and XML indexing is not supported. The XML data type is supported by SQL Azure.
Other SQL Server Features Not Supported by SQL Azure: The following features from earlier versions of SQL Server are not supported by SQL Azure:
•    Backup and Restore
•    Replication
•    Extended Stored Procedures
•    SQL Server Agent/Jobs

Microsoft SQL Server 2005 Service Pack 4 RTM

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 20-12-2010

0

Service Pack 4 (SP4) for Microsoft SQL Server 2005 is now available for download. SQL Server 2005 service packs are cumulative, and this service pack upgrades all service levels of SQL Server 2005 to SP4 . You can use these packages to upgrade any of the following SQL Server 2005 editions:

Enterprise
Enterprise Evaluation
Developer
Standard
Workgroup

SQL Server 2005 SP4 includes SQL Server 2005 SP3 cumulative update 1 to 11, customer requested fixes, along with instances of the SQL Server 2005 SP4 database Engine support for DAC operations.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b953e84f-9307-405e-bceb-47bd345baece&displaylang=en

SQL Server Masters Certification Goes Global

Posted by Deepak Kumar | Posted in SQL DBA | Posted on 30-11-2010

2

Microsoft has recently announced some changes for MCM certification. SQL Server masters certification will now be available in 9 countries! Here is quick comparison:

OLD Pattern: Cost $18,500; 4 exams, 3 weeks Training Session
NEW Pattern: Cost $2,500 without training; 2 Exams (4 hours knowledge exams+ 6 hours lab exam)
 
Prerequisite certifications: Candidates must hold the following two prerequisite Microsoft Certifications in order to be awarded an MCM for Microsoft SQL Server 2008:
MCITP: Database Administrator SQL Server 2008
MCITP: Database Developer SQL Server 2008
 
Requirements for achieving an MCM certification: a candidate must pass following two MCM exams:
Exam 88-970: SQL Server 2008 Microsoft Certified Master: Knowledge Exam
Exam 88-971: SQL Server 2008 Microsoft Certified Master: Lab Exam
 
Pricing:
Microsoft SQL Server 2008 Microsoft Certified Master: Knowledge Exam: US$500
Microsoft SQL Server 2008 Microsoft Certified Master: Lab Exam: US$2,000
 
Retake Candidate need to wait 90 days before retaking exam and can retake as many time require to pass.
 
More details: Read more at MS, Microsoft, Videos, SQLSkills

SQL 2011 – Denali CTP1

Posted by Deepak Kumar | Posted in SQL DBA, SQL Dev | Posted on 30-11-2010

0

Cheers group, we got SQL 2011 to test, learn and experience new things in SQL once again. I can’t wait to download and install it. Share your experiences!

Here are few quick links to begin with, that you may want to check..

  • Microsoft(r) SQL Server(r) code-named ‘Denali’ – Community Technology Preview 1 (CTP1) Download Link
  • What’s New in Denali : Link
  • “HADR” : high-availability and disaster recovery solution : Link
  • Columnstore Indexes for Fast Data Warehouse Query Processing in SQL Server 11.0 : Link

Get Notification as soon as SQL Services are restarted

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev | Posted on 17-09-2010

1

Being production DBA and responsible for a dozen to 100+ SQL Servers running in your organization or datacenter, Sometimes, you must be wondering that how you can make sure you are fully aware what is going when you are not at work. There is an easy way to stay in touch with your SQL Server(s), you can create a SQL job on the server, relax and hope you never get such alarm. yup, as soon as SQL Services will restart, the first thing its going to do is send you an email for restart notification.

 

DECLARE @UpTimeDays int
DECLARE @SQLSvcStarted varchar(20)
DECLARE @rc int
DECLARE @msg varchar(1000)
SET @UpTimeDays=(select DateDiff(D, CrDate, GetDate()) from master..sysdatabases where name='tempdb')
IF @UpTimeDays = 0
BEGIN
SET @SQLSvcStarted = (select convert(varchar(20), CrDate, 113) 
from master..sysdatabases where name = 'tempdb')
SET @msg='The SQL Services on <b>'+@@SERVERNAME+'</b> was restarted on <b>'+@SQLSvcStarted +'</b>'
EXEC @rc = msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfileName',
@recipients = 'dk@sqlknowledge.com',
@importance = 'high',
@subject = 'SQL Server Restart Notification!',
@body_format = 'html',
@body = @msg,
@exclude_query_output = 1
IF @rc = 1 RAISERROR('xp_smtp_sendmail Failed', 16, 1)
END

Script to find used and free space in Database files

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 02-08-2010

2

In an Enterprise world, you may be responsible to monitor 1000+ databases hosted on hundreds of SQL Server instances. On a lazy afternoon, suddenly low disk space alarm hit your inbox. What will you do, storage can not be added or expended on the fly. Here is the tested script to find used and free space from SQL Server database’s data files that you can shrink to easy the current situation.

-- Find Log file information and save in temp file using dbcc sqlperf logspace
CREATE TABLE #LogSpace (DatabaseName varchar(40), LogSize int, LogSpace int, status bit)
INSERT #LogSpace exec ('dbcc sqlperf(logspace) WITH NO_INFOMSGS') 
 
-- Find data file information using cursor and dbcc showfilestats
Declare @DatabaseName varchar(500)
create table #DBSpace(FielId tinyint, Filegroup tinyint, TotalSpace int, 
Used_Space int, Name1 varchar(25),NameofFile Varchar(900) )
Declare curDB cursor for select name from master..sysdatabases 
open curDB
fetch curDB into @DatabaseName
while @@fetch_status = 0
begin
    if databasepropertyex(@DatabaseName,'Status') = 'ONLINE'
    begin
    insert into #DBSpace exec ('USE [' + @DatabaseName + ']  DBCC SHOWFILESTATS WITH NO_INFOMSGS')
    end
    fetch curDB into @DatabaseName
end
close curDB
deallocate curDB 
 
-- Select data in tabular format with proper headings & order by
select left(sd.name,30) AS 'DatabaseName',
(ff.[DataFileSpace(MB)])+ls.LogSize as 'DatabaseSize(MB)',ff.[DataFileSpace(MB)],
ff.[DataFileUsedSpace(MB)], ff.[DataFileFreeSpace(MB)],
ls.LogSize as 'LogFileSize(MB)', ls.LogSpace as'LogFileSpaceUsedIn(%)'
from #DBSpace dbs join master..sysdatabases sd on sd.filename=dbs.NameofFile
join #LogSpace ls on sd.name=ls.DatabaseName
join
(select  sf.dbid, sum(dbss.TotalSpace/16) as 'DataFileSpace(MB)', 
sum(dbss.Used_Space/16) as 'DataFileUsedSpace(MB)', 
(sum(dbss.TotalSpace/16)- sum(dbss.Used_Space/16)) 
as 'DataFileFreeSpace(MB)' from #DBSpace dbss
join master.sys.sysaltfiles sf on rtrim(sf.filename)= rtrim(dbss.NameofFile)
group by sf.dbid) ff on ff.dbid=sd.dbid
order by 'DataFileFreeSpace(MB)' desc 
 
-- Drop temporary tables manually
Drop table #DBSpace
DROP TABLE #LogSpace 

Hot topics to work on in SQL 2008

Posted by Deepak Kumar | Posted in SQL DBA | Posted on 02-07-2010

0

    I am working on some of following topics that are considerably new in SQL 2008. Leave a comment, if you have more topics like these or experience to share with us?

  • Prepare a Audit script using SQL 2008 feature for High, Moderate or Low category servers

  • Setup a Central Management Servers

  • Standardize & implement Data Collector and Management Data Warehouse for High category SQL Servers

  • Implement Data Compression to reduce the size of your VLDB

  • Apply policy based management for all DB Servers in your organization

  • Distribute your high volume application’s workload, T-SQL streams using resource governor

  • Use TDE in your environment

Audit databases – Using DDL triggers

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, Uncategorized | Posted on 05-06-2010

3

As a DBA, do you ever wonder what is going inside your production database? Who is changing database schema during production time without informing you. Now, you need not to worry, you can setup DDL triggers to easily records all changes in table; like what was changed, when and by whom etc. Given is two set of commands that you need to use and leave all worries aside. Also, you can prepare customized email alerts on top of data table to notify you as soon as an event occurs.

Create a table that will store all records. For example I am using MSDB database to store this table.
a DDL trigger need to created inside database; for that you want to enable audit. This trigger will capture all DDL events and save in table. Test and check process by creating test procedure, tables etc.

1. Script to create table

USE [msdb] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[DatabaseChangeLog]( 
    [LogId] [int] IDENTITY(1,1) NOT NULL, 
    [DatabaseName] [varchar](256) NOT NULL, 
    [EventType] [varchar](50) NOT NULL, 
    [ObjectName] [varchar](256) NOT NULL, 
    [ObjectType] [varchar](25) NOT NULL, 
    [SqlCommand] [varchar](max) NOT NULL, 
    [EventDate] [datetime] NOT NULL CONSTRAINT [DF_DBChangeLog_EventDate]  DEFAULT (getdate()), 
    [LoginName] [varchar](256) NOT NULL 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
 

clip_image002[4]

 
2. Script to create database trigger

USE [SQLKnowledge] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE trigger [TrgDBChangeLog] 
on database 
for DDL_DATABASE_LEVEL_EVENTS 
as 
set nocount on 
If Object_ID('msdb.dbo.DatabaseChangeLog') IS NOT NULL 
BEGIN 
declare @data xml 
set @data = EVENTDATA() 
insert into msdb.dbo.DatabaseChangeLog(databasename, eventtype, objectname, objecttype, sqlcommand, loginname) 
values( 
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)') 
) 
END 
GO 
SET ANSI_NULLS OFF 
GO 
SET QUOTED_IDENTIFIER OFF 
GO 
ENABLE TRIGGER [TrgDBChangeLog] ON DATABASE 

 

clip_image004

3. Script to test process and check Audit records

-- Test table creation 
use SQLKnowledge 
create table test (fname varchar(10), lname varchar(10)) 
 
-- Test procedure creation 
create proc test1 
as 
select * from msdb.dbo.DatabaseChangeLog 
 
-- Check table records in msdb database 
select * from msdb.dbo.DatabaseChangeLog 
 

clip_image006

Please leave a comment, if you have questions or need suggestions

© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com