Header

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;