Header

Thursday, December 4, 2014

Microsoft Campus Days 2014

Links to slides and democode from the session "Performance Tuning for the Accidental DBA" held at Microsoft Campus Days 2014 can be found here: http://www.it-craft.dk/sqlevents/

Wednesday, April 30, 2014

Monday, March 31, 2014

VSS default settings prevent Integrity Check on large databases

Recently we encountered a customer, who got the following error while doing DBCC Checkdb /Integrity check on his large database.

Error: 17053, Severity: 16, State: 1.
"Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered"
OR 
Error: 17053, Severity: 16, State: 1.
"The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001447cd6000 in file 'X:\SqlDataFiles\LargeDB.ndf:MSSQL_DBCC45'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online."
The error in the dbcc checkdb is due to a default limitation in VSS, which DBCC is using to complete a full database consistency check in the background.
By default a VSS snapshop may not be larger than 10% of the size of the drive. This can be changed be going to Computer Management -> Disk Management -> Select Properties on all the drives that contains the database -> Select the pane Shadow Copies" -> Select Settings -> Select the redio button "No limit"



This removes the VSS limitation that causes the Error 655 

Wednesday, March 26, 2014

Monitoring if new databases are created

Want an email when somebody is creating a new database on your SQL Server ?

--- CREATE TRIGGER ON DATABASE CREATION
CREATE TRIGGER DDL_TRIG_CREATE_DATABASE
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
 DECLARE @ServerName nvarchar(255)
 DECLARE @Cmd nvarchar(max)
 DECLARE @DBName nvarchar(255)
 DECLARE @Subj nvarchar(2000)
 DECLARE @XML XML
 DECLARE @Recipients nvarchar(255)
 DECLARE @MailProfile nvarchar(255)

 SET @Recipients = 'you@yourcomapny.dk'
 SET @XML = EVENTDATA()
 SET @Cmd = CAST(@XML as nvarchar(MAX))
 SET @ServerName = @@SERVERNAME
 SET @DBName = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(200)') 
 SET @Subj = 'New database "' + @DBName + '" created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
 
 -- Find the database mail profile name
 create table #dbmail_profile
 (
  profileid int null,
  profilename varchar(125) null,
  accountid int null,
  accountname varchar(125) null,
  sequencenumber int null
 )
 insert #dbmail_profile
 EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

 select @MailProfile = profilename
 from #dbmail_profile
 where sequencenumber = 1

 drop table #dbmail_profile

 EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @MailProfile,
  @recipients = @Recipients,
  @subject = @Subj,
  @body_format = 'TEXT',
  @body = @Cmd;

Wednesday, February 26, 2014

Which databases is this login used in ?

When moving databases and logins, or just cleaning the server of unused logins this script can help you test if a single login is mapped to any databases.

-- =============================================
-- Author: Rasmus Glibstrup, SQLGuyDK
-- Company: it-Craft
-- Create date: 26. Feb. 2014
--
-- Description:   
-- Which databases are a single login mapped to ?
--
-- When moving databases and logins, or just cleaning
-- the logins of the server this script can show you
-- which databases a specific login is mapped to.
--
--
-- DISCLAIMER !
-- THIS CODE-SAMPLE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED  
-- OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR  
-- FITNESS FOR A PARTICULAR PURPOSE. 
-- =============================================

-- Change SQLGuy to the login you need to find
DECLARE @LoginName nvarchar(1000) = 'SQLGuy'


DECLARE @SqlStm nvarchar(1000)
Create table #LoginTable(LoginMappedInDatabase nvarchar(1000),LoginName nvarchar(1000),DatabaseUser nvarchar(1000))

DECLARE @DBName nvarchar(1000)
DECLARE dbCursor CURSOR 
FOR SELECT name from sys.databases
Where state_desc = 'online'

OPEN dbCursor

FETCH NEXT FROM dbCursor into @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SqlStm ='INSERT INTO #LoginTable Select ''' + @DBName + ''' as LoginMappedInDatabase,SP.name as LoginName, DP.name as DatabaseUser from sys.server_principals SP inner join [' + @DBName + '].sys.database_principals DP ON SP.sid = DP.sid Where SP.Name=''' + @LoginName + ''''
 
 exec (@SqlStm)
 FETCH NEXT FROM dbCursor into @DBName
END

CLOSE dbCursor
DEALLOCATE dbCursor

Select *
FROM #LoginTable
Drop table #LoginTable

Thursday, February 20, 2014

Monitoring if my databases are online

Usually my customers are monitoring if their servers are online, but in the case of SQL Server, monitoring if the databases are online is just as vital.

This little script checks if the databases are not online and alert you via email.



IF EXISTS (
   Select Name,state_desc 
   From Sys.databases 
   where state_desc <> 'ONLINE'
    )
BEGIN
 DECLARE @tableHTML  NVARCHAR(MAX) ;

 SET @tableHTML =
  N'<H1>OFFLINE Databases</H1>' +
  N'<table border="1">' +
  N'<tr><th>Database Name</th><th>State</th></tr>' +
  CAST ( ( SELECT td = Name, ' ',
      td = state_desc, ' '
      FROM Sys.databases 
      Where state_desc <> 'ONLINE'
      FOR XML PATH('tr'), TYPE 
  ) AS NVARCHAR(MAX) ) +
  N'</table>' ;

 EXEC msdb.dbo.sp_send_dbmail @recipients='you@yourcompany.com',
  @subject = 'OFFLINE Databases',
  @body = @tableHTML,
  @body_format = 'HTML' ;
END



Create a simple job that runs the above script a often as you want - eg. every 10 minutes.

Wednesday, February 19, 2014

Who is using my databases

Due to Microsofts change in licensing with SQL Server 2012, alot of our customers is currently doing consolidation projects or migrating databases from older servers so these can be closed.

This also brings up that simple question: "Are these databases being used - and who are using them?"

To solve this we have made this simple script that tracks the activity of the server, using the same basic views as the activity monitor does.


 
-- =============================================
-- Author: Rasmus Glibstrup, SQLGuyDK
-- Company: it-Craft
-- Create date: 03-Jun-2013
--
-- PART 01 - Create table

-- Description:   
/*
SQL Access Monitor script
Script that captures activity on the SQL server
and saves it to a table.

The Script is based on the Activity Monitor
queries is SSMS
*/
-- =============================================
Create Database [SQLGuyDK_SQLAccessMonitor]
GO

USE [SQLGuyDK_SQLAccessMonitor]
GO

IF EXISTS(Select object_id  from sys.tables where name ='SQLGuyDK_SQLAccessMonitor')
BEGIN
    DROP TABLE [dbo].[SQLGuyDK_SQLAccessMonitor]
END
GO

CREATE TABLE [dbo].[SQLGuyDK_SQLAccessMonitor](
    [SQLSERVER] [varchar](128) NOT NULL,
    [Login] [varchar](128) NOT NULL,
    [Database] [varchar](128) NOT NULL,
    [Application] [varchar](255) NULL,
    [LoginTime] [datetime] NULL,
    [LastRequest] [datetime] NULL,
    [HostName] [varchar](128) NOT NULL,
    [NetAddress] [varchar](48) NULL
) ON [PRIMARY]
GO

-- =============================================
-- PART 02 - Script to capture activity
-- =============================================
Use SQLGuyDK_SQLAccessMonitor

GO
Create procedure dbo.[usp_SQLGuyDK_SQLAccessMonitor]
@RefreshIntervalInSeconds int = 10
as
SET NOCOUNT ON

Declare @DelayTime varchar(10)

SET @Delaytime = SUBSTRING(CONVERT(varchar, DATEADD(ms, @RefreshIntervalInSeconds * 1000, 0), 114),1,8)

WHILE 1=1
BEGIN
    WAITFOR DELAY @Delaytime

    MERGE [dbo].[SQLGuyDK_SQLAccessMonitor] as TARGET
    USING (
            SELECT
               [SQLServer]        = @@Servername              
               ,[Login]         = s.login_name  
               ,[Database]      = ISNULL(db_name(sp.dbid), N'')
               ,[Last Request Start Time] = MAX(s.last_request_start_time)
               ,[Host Name]     = ISNULL(s.host_name, N'')
               ,[Net Address]   = ISNULL(c.client_net_address, N'')
            FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
            LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
            LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
            LEFT OUTER JOIN
            (
                SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
                FROM sys.dm_os_waiting_tasks
            ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
            LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)
            LEFT OUTER JOIN sys.sysprocesses sp ON (s.session_id = sp.spid)
            --Filter out only user processes
            Where s.is_user_process = 1
            -- do not include the query we are running
            AND s.session_Id NOT IN (@@SPID) 
       
            Group by  s.login_name,sp.dbid,s.host_name,c.client_net_address

        ) AS SOURCE ([SQLServer],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address])
        ON SOURCE.[Login] = TARGET.[Login]
        AND SOURCE.[SQLServer] = TARGET.SQLSERVER
        AND SOURCE.[Database]  = TARGET.[Database]
        AND SOURCE.[Host Name] = TARGET.[HostName]

    WHEN MATCHED THEN
        UPDATE SET TARGET.[LastRequest]=SOURCE.[Last Request Start Time]

    WHEN NOT MATCHED THEN
        INSERT ([SQLSERVER],[Login],[Database],[LastRequest],[HostName],[NetAddress])
        VALUES ([SQLSERVER],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address])
    ;

END

SET NOCOUNT OFF
GO

-- =============================================
-- PART 03 - Job to run script
-- =============================================

-- Create a job that is scheduled to run on sql server agent startup, and make it run this one command 

exec SQLGuyDK_SQLAccessMonitor.dbo.[usp_SQLGuyDK_SQLAccessMonitor]


-- =============================================
-- PART 04 - Which databases are not being used?
-- =============================================

Select name
From Sys.databases 
where name not in (
Select [Database]
from [dbo].[SQLGuyDK_SQLAccessMonitor])
And database_id > 4

Tuesday, February 18, 2014

Gothas on changing the Recovery model from Simple to Full

While teaching traditional Backup and Recovery of SQL Server databases this week I made a simple example to prove a point on changing the Recovery model.

When changing the database from Full to Simple mode, no problems, except that you cannot do log backup.

BUT when changing back from Simple to Full recovery mode - BE AWARE the database does not change the recovery model until you make a Full backup after the change.

You can reproduce using the code below:  
 
--Create the test database
Create database TestRecoveryModelChange
GO

--Check the recovery model of the test database
Select name,recovery_model_desc
from sys.databases
Where Name = 'TestRecoveryModelChange'
GO

--Create a Full backup of the test database
Backup database [TestRecoveryModelChange] to disk='E:\Backup\TestRecoveryModelChange.bak'
GO

--Change Recovery model to Simple
USE [master]
GO
ALTER DATABASE [TestRecoveryModelChange] SET RECOVERY SIMPLE WITH NO_WAIT
GO


--Create a change
USE [TestRecoveryModelChange]
Create table Test(Col1 int)
Insert into Test Values(1)
GO

--Change the recovery model back to FULL
USE [master]
GO
ALTER DATABASE [TestRecoveryModelChange] SET RECOVERY FULL WITH NO_WAIT
GO

--Try doing a log backup
Backup Log [TestRecoveryModelChange] to disk='E:\Backup\TestRecoveryModelChange_Log.trn'


--Uupps !
Msg 4214, Level 16, State 1, Line 3
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

Monday, February 17, 2014

Using DNS to virtualize SQL Server naming

One very common problem is when you want to move you databases to a new SQL Server or create a new SQL Server for your datatabases: All the current connection strings, .ini files, ODBC links and so on, in the often many applications scattered throughout the organization needs to be updated with the new SQL Server name. Eg. Going from SQLSERVEROLD1 to SQLSERVERNEW1.

How to change SQL Server connections centrally

The simple solution to this problem is using DNS. Create a CNAME record in your DNS for each application that uses SQL Server. Point the CNAME record to the OLD server and start changing your connection strings, .ini files and so on for the last time.

Eg. your current SQL Server is named SQLSERVEROLD1. You have a CRM application that have one or more databases on the server.

You then create a CNAME record in DNS for the CRM application. A proposed naming could be: SQL-CONN-CRM, that points to SQLSERVEROLD1.

You then change the connection information in the CRM application from SQLSERVEROLD1 to SQL-CONN-CRM - and the next time you move the CRM databases, or upgrade the server - you just change the CNAME record to point at the new SQL Server - no application change needed.

What about named instances?
The CNAME trick will only work for the server part of a named instance - Eg. SQLSERVEROLD1\INSTANCE =>  SQL-CONN-CRM\INSTANCE.

If you would like the SQL-CONN-CRM\INSTANCE to virtualize instance naming as well, you will need to use SQL Server Aliases. (Another blog post to come)