SQL SERVER – FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup

Check if autogrowth option is turned on for the database.

Make sure there is enough space on the drive, where the database resides

msdb Database

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. The sizes of msdb files may vary slightly for different editions of SQL Server Database Engine.

The following operations cannot be performed on the msdb database:

• Changing collation. The default collation is the server collation.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the primary filegroup to READ_ONLY.

To know more about other System Databases Click Here

Wait Stats Cont..

For more information on the wait conditions firstly please refer this post

The below mentioned are basic explanations of each of the major wait types. Appearance of any of these wait types high up will certainly help direct your subsequent investigations.

CXPACKET

Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem, associated with one of the other high value wait types in the instance

SOS_SCHEDULER_YIELD

The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure

THREADPOOL

A task had to wait to have a worker bound to it, in order to execute. This could be a sign of worker thread starvation, requiring an increase in the number of CPUs in the server, to handle a highly concurrent workload, or it can be a sign of blocking, resulting in a large number of parallel tasks consuming the worker threads for long periods.

LCK_*

These wait types signify that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using the information in the sys.dm_db_index_operational_stats

PAGEIOLATCH_*, IO_COMPLETION, WRITELOG

These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server. PAGEIOLATCH_* waits are specifically associated with delays in being able to read or write data from the database files. WRITELOG waits are related to issues with writing to log files. These waits should be evaluated in conjunction with the virtual file statistics as well as Physical Disk performance counters, to determine if the problem is specific to a single database, file, or disk, or is instance wide.

PAGELATCH_*

Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.

LATCH_*

These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.

ASYNC_NETWORK_IO

This wait is often incorrectly attributed to a network bottleneck. In fact, the most common cause of this wait is a client application that is performing row-by-row processing of the data being streamed from SQL Server as a result set (client accepts one row, processes, accepts next row, and so on). Correcting this wait type generally requires changing the client-side code so that it reads the result set as fast as possible, and then performs processing.

Good article on Performance Tuning SQL Database Performance Tuning for Developers

Another good article for Developers  MetaDapper: Data Mapping and Conversion Made Easy With the Right Tools

System Databases in SQL Server

System database in SQL Server are crucial, and you should leave them alone most of the time, The only exception to that rule is model database, which allows you to deploy changes such as stored procedures to any new database created. If a system database is tampered with or corrupted, you run the risk that SQL Server will not start. It contains all the stored procedures and tables needed for SQL Server to remain online.

master Database  – Records all the system-level information for an instance of SQL Server.

msdb Database – Is used by SQL Server Agent for scheduling alerts and jobs.

model Database – Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

Resource Database – Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

tempdb Database – Is a workspace for holding temporary objects or intermediate result sets.

Alert me when SQL Server Drive Space is low

Keeping a watch on drive space on all Servers that you handle is a very integral part of DBA work. Once SQL Server runs out of disk on the underlying host, then everything obviously comes to a crashing halt. While proactively sizing data and log file sizes is the best technique to use when it comes to database sizing, there are still plenty of situations where some databases might need to be allowed to auto grow in many cases. As such, the last thing you want is for databases to grow to such a point that they run out of disk.

Below is a script that is very handy to avoid such catastrophic situations.  The below script has to be run in master database and a job has to be created to schedule running this procedure in  whatever frequency that is acceptable to you.

This script checks for drive space that is below 5% and fires an alert to the specified email address if the drive space is below 5%.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[up_diskspace] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- EXEC up_diskspace
CREATE PROCEDURE [dbo].[up_diskspace] 
AS 
SET NOCOUNT ON 
DECLARE @emailBody NVARCHAR(MAX)
 , @emailSubject VARCHAR(50)
 , @emailDate VARCHAR(10)
 , @serverName VARCHAR(100)
 , @runStatusChar VARCHAR(20)
 , @OK_serverList NVARCHAR(MAX)
 , @serverOK VARCHAR(100)
 , @sendEmail INT
 , @jobRunDateChar CHAR(8)
 , @listStart TINYINT
 , @hr int 
 , @fso int 
 , @drive char(1) 
 , @odrive int 
 , @TotalSize varchar(20) 
 , @MB bigint 
 , @freeSpace VARCHAR(10); 
SET @MB = 1048576
SET @listStart = 1
SET @emailSubject = 'UAT - Low Drive Space Alert!'
SET @serverName = @@serverName
SET @sendEmail = 0
CREATE TABLE #drives (drive char(1) PRIMARY KEY, 
 FreeSpace int NULL, 
 TotalSize int NULL) 
INSERT #drives(drive,FreeSpace) 
EXEC master.dbo.xp_fixeddrives 
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso 
DECLARE dcur CURSOR LOCAL FAST_FORWARD 
FOR SELECT drive from #drives 
ORDER by drive 
OPEN dcur 
FETCH NEXT FROM dcur INTO @drive 
WHILE @@FETCH_STATUS=0 
BEGIN 
 EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive 
 IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso 
 EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT 
 IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive 
 UPDATE #drives 
 SET TotalSize=@TotalSize/@MB 
 WHERE drive=@drive 
 FETCH NEXT FROM dcur INTO @drive 
END 
CLOSE dcur 
DEALLOCATE dcur 
EXEC @hr=sp_OADestroy @fso 
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso 
SELECT drive, 
 FreeSpace as 'Free(MB)', 
 TotalSize as 'Total(MB)', 
 CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)' 
INTO #freeSpace
FROM #drives
ORDER BY drive 
--SELECT * FROM #freeSpace
DELETE #freeSpace
WHERE [Free(%)] > 5
SET @sendEmail = (SELECT count(*) FROM #freeSpace)
IF @sendEmail > 0 
BEGIN
 --GET DRIVES WITH LESS THAN 5% DISK FREE SPACE
 DECLARE l CURSOR FORWARD_ONLY
 FOR SELECT drive, [Free(MB)] FROM #freeSpace
 OPEN l -- The keyset is copied to tempdb
 FETCH l INTO @drive, @freeSpace
 WHILE (@@FETCH_STATUS = 0) 
 BEGIN
 IF @listStart = 1
 BEGIN
 SET @OK_serverList = @drive + ' (' + @freeSpace + ' MB)'
 SET @listStart = 0
 END
 ELSE
 SET @OK_serverList = @OK_serverList + ', ' + @drive + ' (' + @freeSpace + ' MB)'
 FETCH l INTO @drive, @freeSpace
 END
 CLOSE l
 DEALLOCATE l
 -- RESET @listStart
 SET @listStart = 1
 SET @OK_serverList = @OK_serverList + '.'
 -- PRINT LIST
 SET @emailBody = CHAR(13) + CHAR(13) + 'The following drive(s) on ' + @serverName + ' are below 5% free space: ' + ISNULL(@OK_serverList, '')
 --- EMAIL REPORT
 EXEC msdb.dbo.sp_send_dbmail 
 @recipients = 'SANTHOSHVISWANATHAN@GMAIL.COM'
 , @body = @emailBody
 , @subject = @emailSubject
 , @importance = 'HIGH'
END
DROP TABLE #drives 
DROP TABLE #freeSpace
RETURN

Please check the link for Mail alerts for new added record.

Cycle SQL Server Error Logs

The SQL Server error log is notorious for growing really large when certain things happen; like repeated login failures, stack dumps or other errors. In cases when trace flag 3226 is not turned on and there are hundreds of databases being backed up the error log can also grow really big.  When the log gets big, it makes it difficult to find real issues, much slower and it can fill up your disk drive.

By default a SQL Server instance will keep up to 6 error logs on hand—so if you’re dealing with highly sensitive information or an environment where auditing is very important you might want to push this number up.

Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on. sp_cycle_errorlog enables you to cycle the error log files without stopping and starting the server.  Execute permissions for sp_cycle_errorlog are restricted to members of the sysadmin fixed server role.

Once you’ve determined an interval at which to regularly cycle your logs, cycling them is actually quite easy. To tackle this need I just set up a SQL Server Agent Job that I run (typically) weekly, and which uses the following to cycle the error log:

-- Error Log:
USE master;
GO
EXEC master.sys.sp_cycle_errorlog;
GO

You can also use a similar technique to cycle the SQL Server Agent Log as well:

-- SQL Server Agent Error Log:
USE msdb;
GO
EXEC dbo.sp_cycle_agent_errorlog;
GO

Fixing Orphaned Users in SQL Server

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a SID that does not match.

First, to detect orphaned users. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, you can do either of the below.

EXEC sp_change_users_login 'Auto_Fix', 'user'

EXEC sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

 

 

Understanding “login failed” – Error 18456 in SQL Server

We are looking at the “login failed” error in SQL Server today.

Msg 18456, Level 14, State X, Server <server name>, Line 1
Login failed for user ‘<user name>’

It’s a fairly common error we receive while logging into. If you do some research on this, you will find that your connection request was successfully received by the server you specified , but the server is not able to grant you access for a number of reasons and throws error: 18456.

If Error 18456 occurs, it will have an error state number that can help determine the cause. Obtaining information on the error state can make troubleshooting significantly easier.

The Error state descriptions are as follows:

State 2 and State 5 – Invalid USERID was used to log in.
State 6 – Attempt to use a Windows login name with SQL Authentication
State 7 – Login is disabled and password could be mismatched.
State 8 – Password mismatch has occurred.
State 9 – Invalid password.
State 11 and 12 – Valid login but server access failure
State 13 – SQL Server service is paused.
State 18 – Change of password is required.

Handy Daily Check scripts for SQL Server

—DbStatus
SELECT name, database_id, state_desc, recovery_model_desc
FROM SYS.DATABASES where state_desc <> ‘ONLINE’

—Uptime
Select CONVERT(CHAR(25),login_time,100), CONVERT(CHAR(25), DATEDIFF(DAY, login_time, GETDATE()))
FROM master..sysprocesses
WHERE spid = 1

—Backup
Select SUBSTRING(s.name,1,40) AS ‘Database’,
CAST(b.backup_start_date AS datetime) AS ‘LastBackupDate’, CASE Type WHEN ‘D’ THEN ‘Full’ WHEN ‘L’ THEN ‘Transaction Log’ WHEN ‘I’ THEN ‘Differential’ WHEN ‘F’ THEN ‘FileGroup’ END AS BackupType
FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset WHERE database_name = b.database_name and s.name <> ‘tempdb’)
order by LastBackupDate

—Errorlogs
exec master..xp_readerrorlog 0, 1, ‘cannot obtain a LOCK’
exec master..xp_readerrorlog 0, 1, ‘Error’
exec master..xp_readerrorlog 0, 1, ‘fail’
exec master..xp_readerrorlog 0, 1, ‘Victim Resource’
exec master..xp_readerrorlog 0, 1, ‘Expire’
exec master..xp_readerrorlog 0, 1, ‘stack’
exec master..xp_readerrorlog 0, 1, ‘CImageHelper’
exec master..xp_readerrorlog 0, 1, ‘is full’
exec master..xp_readerrorlog 0, 1, ‘cannot’
exec master..xp_readerrorlog 0, 1, ‘hung’
exec master..xp_readerrorlog 0, 1, ‘WARNING’
exec master..xp_readerrorlog 0, 1, ‘Unable’
exec master..xp_readerrorlog 0, 1, ‘Could not allocate new page’
exec master..xp_readerrorlog 0, 1, ‘attempting to unlock unowned’
exec master..xp_readerrorlog 0, 1, ‘restored’
exec master..xp_readerrorlog 0, 1, ‘kill’
exec master..xp_readerrorlog 0, 1, ‘Severity: 17’
exec master..xp_readerrorlog 0, 1, ‘Severity: 18’
exec master..xp_readerrorlog 0, 1, ‘Severity: 19’
exec master..xp_readerrorlog 0, 1, ‘Severity: 20’
exec master..xp_readerrorlog 0, 1, ‘Severity: 21’
exec master..xp_readerrorlog 0, 1, ‘Severity: 22’
exec master..xp_readerrorlog 0, 1, ‘Severity: 23’
exec master..xp_readerrorlog 0, 1, ‘Severity: 24’
exec master..xp_readerrorlog 0, 1, ‘Severity: 25’
exec master..xp_readerrorlog 0, 1, ‘Backup Failed’
exec master..xp_readerrorlog 0, 1, ‘SqlDumpExceptionHandler’
exec master..xp_readerrorlog 0, 1, ‘Table corrupt’
exec master..xp_readerrorlog 0, 1, ‘admin’
exec master..xp_readerrorlog 0, 1, ‘cannot obtain a LOCK’
exec master..xp_readerrorlog 0, 1, ‘Severity: 17’

–Jobs
SELECT name, date_modified, last_run_date, last_run_outcome
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id and enabled = 1 and last_run_outcome = 0