SQL Log truncation

[zak2011]

To upgrade this server would be justifiable had the vss backups not been working entirely . Unfortunately in this case it would be difficult for me to justify the upgrade as the same server with the same version of SQL works with Backupexe agent 2012 using Microsoft VSS.

[tsightler]

As stated, agent backups use component level VSS, Veeam backups use volume level VSS. An agent based backup will perform a VSS freeze of each DB as it backs it up, Veeam freezes EVERY DB at the same time and takes a snapshot. With that many databases, it may simply be timing out attempting to accomplish this. One question, with so many databases, how many volumes does this system have?

[tsightler]

BTW, you may be able to work around this. I'm thinking you might be hitting the maximum thread limit on your SQL box. In SQL 2005, Microsoft no longer has a fixed limit, but calculates the limit based on the number of CPU/cores. For 64-bit systems with <=4 CPUs the default maximum thread count is 512. You can run the following SQL commands to determine the currently configured maximum on your box, and the number currently consumed.

Code: Select all

select max_workers_count From sys.dm_os_sys_info
select count(*) from sys.dm_os_threads

Now here's the kicker, when a product performs a volume level VSS freeze (as opposed to component level like an agent backup), each database frozen requires 3 threads, so performing a backup of a volume with 100 databases would consume 300 additional threads immediately. If there's a 512 limit, well, that wouldn't leave you much headroom. It should be possible to manually override the max SQL threads if that is indeed the problem, and set it to a larger value. I'm still not sure if that would actually solve the problem, but I'd bet this is the root cause of the problem.

[zak2011]

Thanks Tom for that information. This system has 2 volumes. After running the query, i got the max workers count as 256.
What was stated in your post makes a lot of sense now. How should i override this, Tom?

Thanks!

[tsightler]

Assuming this is the problem (I think it's very likely), you would need to increase the maximum worker threads from the default. Since you report that the query above shows 256, I have to assume that this is 32-bit SQL 2005, as 64-bit versions should default to no less than 512 worker threads. You can increase the maximum to any value up to 32767, however, Microsoft has a recommendation of no more than 1024 for a 32-bit server. The process to increase it is pretty simple and is documented in this MSDN article. Please note that you must restart the server for the new setting to take effect.

Also, if you want to read more about this limit and how it impacts other backup products, here's a great article about the issue right on the MSDN website.

[zak2011]

Hi Tom,

Thanks for that info. Sorry this may not make sense and may be not correct but after running the above query, my sql server has been acting very strange and people are calling that they are not able to log in to the databases. Veeam is not able to backup this server and so does backup exec.
I get the following error from veeam
Failed to prepare guest for hot backup. Error: VSSControl: Failed to prepare guest for freeze, wait timeout 900 sec
and from windows
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 17189
Date: 04.08.2013
Time: 06:56:07
User: N/A
Computer: SQLSRV01
Description:
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: 10.0.4.35]

I have had to restart the sql server twice now. Is this something that keeps running every time?
I am getting the following error messages,
Has this got to do with running the query .
Once i restart it works, but after a while the same thing happened.
select max_workers_count From sys.dm_os_sys_info
select count(*) from sys.dm_os_threads

Thanks

[zak2011]

Also i get the following on the SQl Server application log

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 04.08.2013
Time: 08:54:17
User: N/A
Computer: SQLSRV01
Description:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.'

[tsightler]

Are you saying you made a change to max threads, or that you just ran that query? The query itself is just that, a query (i.e. a select). It doesn't change anything. If you're saying that it happens after the VSS freeze error, it's certainly possible that VSS is exhausting your available threads. BTW, there are actually two queries, the first:

select max_workers_count From sys.dm_os_sys_info

Simply displays the current value of the max_workers_count setting, and the second:

select count(*) from sys.dm_os_threads

Simply displays the current threads in use on the server by counting the number of rows in the internal dm_os_threads so that you know how close you are to the limit. There's no way that I can see that simply running these two queries could possibly have any impact on the running environment.

[zak2011]

I have just run the query and did not make a change to max threads. You are absolutely right that there is no possiblity that running a query can make such a impact. Its just that it happened a few hours after I reported the results of the query so had no clue whats going on.
The main error that kept coming was "SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems".

I noticed that running incremental backups from Veeam and Backup Exec did not cause any problem. But both times the SQL server login failures occured, the full backup from Backup Exec was running. So it could have been very likely the VSS freeze was exhausting the threads.

[tsightler]

But both times the SQL server login failures occured, the full backup from Backup Exec was running. So it could have been very likely the VSS freeze was exhausting the threads.

Yep, I'd bet that you're already so close to running out of threads that BE, while being able to start, leaves very few threads remaining for servicing clients. It sounds like you really need to consider increasing your thread count on this server.

[zak2011]

Thanks Tom for those articles. They were very useful. I will now consider increasing the thread count on this server.

[zak2011]

I am a bit uncertain about increasing the worker threads now because I got a mail from technical support with the foillowing link
http://support.microsoft.com/kb/943471/en-us.
In the article it says that it is not recommended to increase the number of threads in a 32 bit installation of SQL.
So, does this mean I have the option of only splitting the databases and not increasing the worker threads.

Thanks.

[foggy]

According to this article it is not also recommended to create a snapshot backup of more than 35 databases at the same time, so you are already violating the recommendations.

[zak2011]

I was hoping to increase the thread count actually, but now to reduce the databases on each instannce is a bit of a challenge after speaking to the sql developers.
Is there any way to create a snapshot backup of 35 databases from Veeam? If not then, i think i will be stuck using Backup Exec to back the databases on this server.

[foggy]

Is there any way to create a snapshot backup of 35 databases from Veeam? If not then, i think i will be stuck using Backup Exec to back the databases on this server.

If you are talking about selective processing of SQL Server and excluding a number databases from the snapshot, then no, there's no such functionality.

[zak2011]

Yes, that was what I meant.

[tsightler]

Other Microsoft articles state not to increase max threads beyond 1024 for 32-bit systems. It's hard to understand not being able to increase them at all since simply adding additional processors will increase them. Still, that's a call you'll have to make.

[tsightler]

BTW, in the MSDN documentation on SQL 2005 the recommended max for a 32-bit server is 1024. The MSDN documentation is generally considered quite authoritative, so it's confusion that there's a KB article that contradicts this. As the MSDN documentation shows, if you were running with 8 or more CPUs the default would already be greater than 256, so that implies that the KB article is saying that a default install of 32-bit SQL on an 8 CPU system is already "not recommended", which seems quite silly. Here's the link to the MSDN architecture:

http://technet.microsoft.com/en-us/libr ... l.90).aspx

[zak2011]

Thanks Tom for that info. It is quite clear in the MSDN about the worker threads.
I will try to increase the worker threads.

[zak2011]

A colleague of mine was asking whether we should turn to backups using SQL management studio using scripts vs using Veeam backups as previously we had some issues with VSS and the SQL writer.
I am not for backing up databases using SQL Managment studio and believe that backing up and recovering using Veeam was more reliable using Application aware backups.
Are there any other key differences between SQL Management studio backups and Veeam backups, other than the Application aware image Processing feature of Veeam backup?

Thanks

[Vitaliy S.]

The key differentiation is that Veeam is doing an image based backup of the VM containing your SQL Server, so all you have to do when the disaster strikes is spin this VM up from the backup file. As to SQL Management Studio backups, then if you lose the VM, you will have to install SQL Server first and then restore the database you've been backing up.

[zak2011]

Thanks for the answer, Vitaliy

[zak2011]

Hi,
I have increased the worker threads to 1024, however now the Veeam backups for this server does not seem to be working. It fails With the error " Unable to release guest. Error: Unfreeze error: [Backup job failed.
Cannot create a shadow copy of the volumes containing writer's data.
A VSS critical writer has failed. Writer name: [SqlServerWriter]. Class ID: [{a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}]. Instance ID: [{3badafa3-bc41-47c6-964e-51fba8dc3052}]. Writer's state: [VSS_WS_FAILED_AT_PREPARE_SNAPSHOT]. Error code: [0x800423f4].]"
The server was rebooted after this also.

[foggy]

So we are back to the SQL VSS writer issues... Are there any related messages in the Windows Event Log? Have you already discussed this with technical support?

Another option for you could be to backup this server without VSS and truncate SQL Server logs manually via SQL Management Studio. It’s more than enough in case of SQL server, since its databases (in contrast to other VSS aware applications) can be successfully recovered from such backup in 99% of cases.

[zak2011]

The error Message I get in the Windows Event logs are Event Type: Error
Event Source: SQLWRITER
Event Category: None
Event ID: 24583
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft SQL Native Client
Error message: BACKUP DATABASE is terminating abnormally.
SQLSTATE: 42000, Native Error: 3271
Error state: 1, Severity: 16
Source: Microsoft SQL Native Client
Error message: A nonrecoverable I/O error occurred on file "{8385EC2E-AA0E-450E-8C5E-E86F01900315}21:" 995(The I/O operation has been aborted because of either a thread exit or an application request.).

I have disussed this With Technical support. There has been some misunderstanding over this because they said that according to the MS KB article increasing working threads is recommended only for 64bit SQL.
But I have forwarded the link to them which Tom had already mentioned in the post above.

As I have over 100 databases on this instance, how do you recommend truncating the logs manually?

Thanks,
Arun

[foggy]

Searching for the error message I've come across this blog post discussing various SQL VSS Writer issues. Not sure but probably it will give you some hints.

[zak2011]

Thanks for the post. Should i revert back the settings for the worker threads or leave it as it is?

Thanks