Error 3254 – The volume on device BACKUP_PATH is empty

The SQL Server message Error 3254 – the volume on device BACKUP_PATH is empty appear appeared in the application logs . What is it ? and how can I analyse?

 Different reason can cause the Error 3254 message to appear. Some suggestions

1) Check for a valid backup set. Use RESTORE VERIFYONLY which checks for

 a)  backup set is present   and header fields of pages

 b) Are volumes readable?

 c)  checksum (if enabled , BACKUP by default does not use CHECKSUM)

 d) destination drive – space check.

For backup header and list of files use Backup file information needed for RESTORE SQL database

 

2) Check for communication failures – which may invalidate the copy.

 

3) Check in Event Viewer for any DNS issues, if file is being backed up to tape or another server

 

4) If the file is being copied – check the source file and target file. If the source file is valid , and the target file is invalid – check communication links, storage devices and other related errors

 

5) Use the Display sql backup history for a single database - SQL Server DBA  to analyse any clues on the backup file

 

Read More

SQL Server faster restores with instant file initialisation

SQL Server – DBCC CHECKDB FAQ - SQL Server DBA

Database stuck RESTORING - SQL Server DBA

Restricted user on database after restore - SQL Server DBA

SQL auto close - Keep it turned off

Should the sql auto close database option be set to True or False?

SQL Server Books Online defines   Auto Close as  “Specify whether the database shuts down cleanly and frees resources after the last user exits. Possible values are True and False. When True, the database is shut down cleanly and its resources are freed after the last user logs off”

SQL Server Books Online doesn’t give  advice on sql auto close .  It doesn’t expand on whether it’s a good or bad idea. This leads to misunderstandings amongst DBAs and system administrators  causing performance issues.

SQL Auto Close DBA notes

1)  Auto Close is set on a database level.  There are no server level configurations. Assuming the “model” database is set at Auto Close = False , then every database created on the SQL Server instance will have auto close = False.   Watch out for databases restored from other sources , which may already have Auto Close = True.  Include Auto Close as part of a SQL Server – Daily Health Check Script with Powershell

2) Resource overhead. If Auto Close is turned on , once all connections to a database are closed , the database will proceed through the Auto Close process. When a new connection is requested , data is loaded back into buffer and statements are recompiled . Why add this resource overhead to data requests?  Read more on SQL Cachestore flush

3) Auto Close on Dev servers ? .I have worked in environments where DBAs maintain Auto Close = True on Dev boxes, based on the principle of releasing resources and maximising limited resources. . I don’t think it’s worth the management overhead . If managed correctly , SQL Server is excellent at managing memory consumption  and gives accurate signals  on memory pressure.

4) Is it worth having Auto Close = True? There are probably some cases where Auto Close = True. Before making the decision consider the sql server performance impact  and test thoroughly  

How to identify the  SQL Auto Close status on a database

  

--Method 1 
 SELECT DATABASEPROPERTY(‘dbname’,’IsAutoClose’)

--Method 2 
SELECT name,is_auto_close_on  FROM sys.databases where is_auto_close_on = 1

 Method 3

 

To disable Auto Close

 

ALTER DATABASE dbname
SET AUTO_CLOSE  OFF

Read More 

SQL Server Performance Checklist - SQL Server DBA

SQL Server – How to Protect against problems

SQL Cachestore flush - SQL Server DBA

Windows Pagefile size for dedicated SQL Servers

Windows Pagefile  - what maximum size to set for a dedicated SQL Server?

This is the second part of SQL Server - Windows Virtual memory and paging file location - pagefile.sys  dealing with location of the Windows paging file.

Factors for deciding on Windows Pagefile size on dedicated SQL Server

1)  In the event of a severe SQL Server crash – a paging file size of 2 GB is useful to capture a crash dump file.

2)  200 MB is required for a Kernel dump

3)  The “traditional” recommendation for the paging file is 1.5 x the Physical RAM – but is this relevant for a dedicated server with SQL Server installed?

A dedicated SQL Server makes the calculation simple. For a start if SQL Server is using the paging file – you’re in trouble!

Let’s say you had a dedicated SQL Server with 128 GB RAM . If you also had a paging file of 192 GB – and there were enough programs open to use all the available memory – the response time would be slow.

Massive paging file are not necessary on dedicated SQL Server servers.

 4) If you need a formula – the one I use is –

 Minimum = SUM(peak private bytes of every process)  - Physical RAM

Maximum = double the value from Minimum

5) Start cautiously with SQL Server Max Memory. Set the SQL Server maximum memory at a conservative level , I normally 15GB – 18 GB for the OS on a large server and monitor.  Focus on the Available Mbytes counter.  If the level of Available Mbytes is consistently high , you can think about dedicated some extra meory to SQL Server

 Read More

SQL Server - Windows Virtual memory and paging file location - pagefile.sys

SQL Server – Memory: Pages sec, memory pressure and thrashing

SQL Server Buffer Cache Hit Ratio and memory pressure

SQL Database Corruption

SQL Database Corruption - What steps should I follow to prepare for database corruption?

SQL Database corruption errors can occur during the read – write process, transaction processing , storing data leading to data integrity issues.  A SQL suspect database can strike fear into any DBA, but there are steps to which can lead to decreased stress levels.

It is a major part of the DBA job to protect the data and establish countermeasures to the threat of database corruption.

What steps are you taking to protect SQL Server data?

1) What maintenance operations are you running to identify and resolve database corruption?

2) Do you have a list of best practises or a policy document detailing steps to take if corruption is suspected?

3) Are you and your team aware of common practises  that make the problem worse, once corruption is identified?

4) Do you documented  steps , recovery methods and necessary emergency contacts ?

5) Do you have a rock-solid backup strategy reflecting the critical recovery points of the organization? This is common DBA mistake - read more Top 10 DBA mistakes

 Read More

SQL Server Database SUSPECT state - SQL Server DBA

DBCC CHECKDB - SQL Server DBA

 

max server memory and sql server memory – why is total memory greater than max memory?

Why does total memory used by SQL Server – go over the max server memory  upper limit?

Max Server memory sets only the buffer pool . The Buffer Pool is only one memory allocation  in SQL Server.  The max server memory doesn’t include SQLOS and other internals.

To view the memory used by SQL Server use the SQLServer:Memory Manager\Total Server Memory counter in Performance Monitor

Don’t rely on Task Manager for the correct memory usage – read Task Manager not showing correct SQL Server memory usage

Use the Sys.dm_os_memory_clerks and AWE memory allocation numbers to troubleshoot internal memory pressure.

How to view the Max Server Memory (MB) set for the SQL Server Instance.

 

sp_configure 'max server memory'

 Read More

MEMORYCLERK_SQLGENERAL and sql memory memory usage

Memory objects allocated by SQL Server

SQL Server - How much resource is a SQL database using - memory, disk, IOPS,CPU

SQL Server – Memory: Pages sec, memory pressure and thrashing