November 22, 2012 at 1:06 pm
Hi,
I'm with a problem with an database in Sql Server 2008 with compatibility level 80.. scary the compatibility level.. but my developer ERP requires it.
My database has a total size: 649gb
with recovery model model == full...
when I try to do backup, the backup always fails after 10000seconds, or, 2h 46m and 40 seconds.
Already configured the server from management studio --> Tools --> Options --> Query Execution --> Execution Time-ou to 0 (zero), restarted management studio, sql server engine, server, and so on...
Checked the option: Tools --> Options --> Query Execution -->Advanced --> Set Lock timeout == -1, so.. ok too.
I also tried change the backup command adding the parameters:
buffercount = 2200, blocksize = 65536, maxtransfersize=2097152
to get the maximum throughput...
nothing...
unfortunately I cant change the compatibility level from my database, and try to do a compressed backup, and don't no what more I can do...
has also changed the recovery mode to simple, and execute shrink...
... index defrag... inserting a new and better hdd... a lot of things... but nothing worked and gave me back the backup
Some one know how to resolve this error?
Error:
Msg 3204, Level 16, State 1, Line 1
The backup or restore was aborted.
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Thanks
November 22, 2012 at 2:25 pm
Having to maintain databases with a compat level of 80 has been bothersome to me in the past because some of my DBA scripts will not run against those databases (e.g. the ones that use MERGE or other newer language features) but a compat level of 80 will not affect database backups. All the compat level setting controls is which syntax parser the query engine uses. It has nothing to do with the underlying database, or even the internal execution of the queries themselves. You can assume that once the language parsing portion of query execution is done that the compat level is no longer a factor in anything.
The fact that you are seeing Msg -2 is odd and it counds vaguely familiar but I may be thinking of SPIDs with negative numbers. Are you using native SQL Server backup technology or a third-party backup tool?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 22, 2012 at 3:09 pm
I'm using native SQL Server backup technology...
It's really strange the result....I turned on some traces too.. but not cleared my mind.
I not yet installed the sql 2k8 r2 to try the compressed backup.
Because, I don't know if the compatibility level - which implies only in parse engine, also interferes with the ability to compress a backup.
Thanks
November 22, 2012 at 3:36 pm
the first thing that comes to mind is to check the sql logs and to check the database integrity, and the review the outcome
No signature available.
November 22, 2012 at 4:45 pm
I already checked the integrity... everything ok...
I called some traces to execute checkpoint...
then I'll checking the log...
change the recovery model from full to simple in first test, was just to reorganize the database pages, because the shrink do not reduces space...
All that I'd tryed until now, is just to make backup more fast to reduces the time limit.. but.. nothing seems to make effects
November 22, 2012 at 7:48 pm
The number 10000 sounds arbitrary enough to be a setting built into a third-party backup software (which is why I asked), a firewall or some other network-point like a router or switch. SQL Server does not impose command timeouts for running queries from the server-side. Any timeouts involved in a call will be due to client software settings (e.g. .NET CommandTimeout present in SSMS which you checked) or something between the client and the server.
Also note that compat level will not interfere with backup compression on 2008 R2 which is happening at a very low level during the backup before it is written to disk. Only worry about compat level as it relates to query parsing.
When you run the backup statement have you checked to see if another SPID is blocking the SPID running the backup? 10,000s = 2.78h. VLDBs may have a chance to take that long to backup under specific circumstances, but not a lot of databases in the world should take that long when backing up to a local or local-LAN resource.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 22, 2012 at 9:20 pm
I agree with you. Only clients could interrompe a query. The Sql Server must allow your query runs, until she is naturally terminated....
The backup's command is sent, using management studio. Now, theres a backup query in execution... in parallel I run processor monitor, to try find out what finished my query.
Sorry by my english, I'm from Brazil..
and thanks for your help..
If I find something, I'll post the solution.
And each process performed, even if not signify a solution.
November 23, 2012 at 6:09 am
No problem. I understood all your comments. Thank you for posting back. I look forward to hearing more as you work towards a solution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 23, 2012 at 7:26 am
maybe a firewall dropping the connection
November 23, 2012 at 10:19 am
Hello all,
first thing.. thanks for your support, time and suggestions... always is important have the community support.
I disabled firewall, antivirus and others.. I resolved the problem, but, not in the desired way...
In my last try, and that solved the problem, I did:
Consulting the log_reuse_wait_desc column in sys.databases, the returned value was: REPLICATION;
There's no replication configured;
Interview with older employees (I.T. only) - The common sense was "The server never has been configured with replication";
Again checked the results for "dbcc loginfo" - Many rows with status iqual to 2;
"dbcc logperf" - database log space used == 100%;
"dbcc opentran" - Result:
Transaction information for database 'MyDatabase'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (66639:17310:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator
Hum... The Sql Server uses the wal protocol to warranty that after a commit, one transaction is never lost... ok..
Theorically theres no backup full, because the recovery model was changed to simple, and full, an all lsn sequence not makes sense anymore.
Does it make sense to keep the SQL Server transaction log if they simply can not be recovered in case of failure? No. No it no make sense.
So, what f* are that rows with status 2 in my log file?
Honestly? I don't know.
So, I executed the sp_removereplication (to remove all the garbage remaining of old settings related to replication)
After, I checked again the log with "dbcc loginfo"... just one row with status == 2.. ok..
Recovery model to full;
defrag indexes;
shrinkdatabase to reorganize pages;
create a new raid 6 with 6 disks;
disk alignment to mdf file;
backup full in 1:18 minutes;
backup trn..
changes recovery model to simple;
dbcc shrinkfile (id ldf file, 980mb);
resize the ldf file to 100gb;
changes recovery model to full;
backup full in less than 9 minutes;
backup trn.. everithing ok again.
Tip: always check what have in ldf file, he can talk much more than we expected.
November 23, 2012 at 9:50 pm
Great, you are back on track!
Theorically theres no backup full, because the recovery model was changed to simple, and full, an all lsn sequence not makes sense anymore.
Does it make sense to keep the SQL Server transaction log if they simply can not be recovered in case of failure? No. No it no make sense.
So, what f* are that rows with status 2 in my log file?
Honestly? I don't know.
It may not make sense for recovery but it does for replication. If a database in simple recovery is setup as a publisher replication then SQL Server cannot mark a VLF for reuse until all eligible transactions have been replicated.
So, I executed the sp_removereplication (to remove all the garbage remaining of old settings related to replication)
After, I checked again the log with "dbcc loginfo"... just one row with status == 2.. ok..
Makes sense. As soon as you removed replication SQL Server was able to immediately mark those VLFs available for reuse.
Maybe I missed it but did you ever figure out what was causing the backup operation to fail exactly at 10,000 seconds?
One more comment, from these tasks:
Recovery model to full;
defrag indexes;
shrinkdatabase to reorganize pages;
create a new raid 6 with 6 disks;
disk alignment to mdf file;
backup full in 1:18 minutes;
backup trn..
changes recovery model to simple;
dbcc shrinkfile (id ldf file, 980mb);
resize the ldf file to 100gb;
changes recovery model to full;
backup full in less than 9 minutes;
backup trn.. everithing ok again.
SHIRNKDATABASE would have re-fragmented your indexes so make sure you re-run your index defrag routines one more time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply