Comprehensive data protection for all workloads
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
VP, Product Management
Posts: 6009
Liked: 2842 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: SQL Log truncation

Post by tsightler » 1 person likes this post

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
VP, Product Management
Posts: 6009
Liked: 2842 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: SQL Log truncation

Post by tsightler » 2 people like this post

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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 » 1 person likes this post

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
VP, Product Management
Posts: 6009
Liked: 2842 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: SQL Log truncation

Post by tsightler » 2 people like this post

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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
VP, Product Management
Posts: 6009
Liked: 2842 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: SQL Log truncation

Post by tsightler » 2 people like this post

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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
VP, Product Management
Posts: 6009
Liked: 2842 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: SQL Log truncation

Post by tsightler » 1 person likes this post

zak2011 wrote: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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 »

Thanks Tom for those articles. They were very useful. I will now consider increasing the thread count on this server.
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by foggy »

zak2011 wrote: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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 »

Yes, that was what I meant.
tsightler
VP, Product Management
Posts: 6009
Liked: 2842 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: SQL Log truncation

Post by 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
VP, Product Management
Posts: 6009
Liked: 2842 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: SQL Log truncation

Post by tsightler » 1 person likes this post

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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: SQL Log truncation

Post by Vitaliy S. » 2 people like this post

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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 »

Thanks for the answer, Vitaliy
zak2011
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Log truncation

Post by 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
Veteran
Posts: 367
Liked: 41 times
Joined: May 15, 2012 2:21 pm
Full Name: Arun
Contact:

Re: SQL Log truncation

Post by zak2011 »

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

Thanks
Post Reply

Who is online

Users browsing this forum: Google [Bot] and 172 guests