Monitoring & Setting Alerts for SQL Server Critical Errors

Posted by Deepak Kumar | Posted in Scripts, SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 20-01-2011

10

As a Proactive DBA, you must monitor All SQL Server errors having severity level between 17 to 25. Any errors from level 20 to 25 are serious in nature however for 17 to 19 a DBA involvement is required for resolution. Here is self explanatory MS description of error levels, steps to get automatic alert as soon as any of error occurs and list of error messages.

SQL Server Query for Capacity Planning – SQL 2005 & 2008

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

14

As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. However being busy on daily DBA tasks, its not always possible to prepare scripts or manually extract data from system for documentations & trend reporting purposes. I faced the similar challenge while working with large DB systems and came up with a T-SQL script that can do the needful without worries..

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

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 
© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com