Monday, May 20, 2013

[MS SQL Server] Backup failed - no SQL agent job

[MS SQL Server] Backup failed - no SQL agent job

Backup failed - no SQL agent job

Posted: 09 Apr 2013 09:36 AM PDT

We recently found there are quite a few error logs in sql log:MessageBackupVirtualDeviceFile::PrepareToFreeze: failure on backup device '{4FEEA406-DD73-47D8-BC10-0ADAD6A44D6D}1'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).BACKUP failed to complete the command BACKUP DATABASE ADWDB. Check the backup application log for detailed messages.We didn't schedule any SQL agent job backup the databases. And as a dba, I don't think we have any other places scheduled SQL jobs.But our network side [u]do[/u] have Unitrends to backup the whole server system.We are not sure if this error is from there. Any clues?Thanks,

Speed up updates by disabling indices

Posted: 20 May 2013 03:23 AM PDT

Hi I need to update several tables which have the same field its a shopid.e.g.Update orders set Shopid =300 where shopid =3Shopid is part of indices.Will it make a big difference to disable the indices first.I'm concerned about log file getting too big for the disk.Thanks

Replication - High memory usage when running Snapshot

Posted: 22 Dec 2011 09:44 PM PST

If there are any replication gurus out there - I need your help!!!First the background ... a client of mine has transactional replication set up against a 15GB database. The bulk of the 15GB is contained within one BLOB column on a table. The distributor was SQL Server 2008 SP1 and sits on a box that's got 3.5GB of available RAM - spotted the problem yet?Anyway, when the snapshot agent runs, available memory on the box drops like a lead balloon. I have a strong suspicion that the issue is related to this bug - [url=http://support.microsoft.com/kb/2606301]http://support.microsoft.com/kb/2606301[/url] and that it's because the snapshot process uses memory mapped data to do the data transfer. As such I've applied SP3 CU1, but memory still drops and eventually runs out, rendering the server inaccessible.I've run a number of tests so I know it's this BLOB data causing the problem. This includes excluding the column from the publication, and also filtering the publication so the amount of BLOB data coming across does not exceed the amount of physical memory available on the distributor. In both these cases the snapshot gets generated successfully.Any thoughts?Thanks (and Merry Christmas!)Phil

tempdb getting full

Posted: 19 May 2013 10:50 PM PDT

My tempdb is getting full within 2 hours , there are some executing SP that are using temporary tables, cursors and joins , How can I troubleshoot the same, please help.thanx in advance.

Server Configurations - SQL 2008 R2

Posted: 19 May 2013 08:50 PM PDT

Hi All,I am in the process of putting together a proposal to upgrade our Production Server and wanted to ask a couple of questions of the experts on this site.Basic Specification is ;Windows Server 2008 OSSQL 2008 R2 - Single Default InstanceERP System with 6 Databases of around 6 gig each ( 36 gig total ... this is 4 years of data )Daily / Hourly tasks runningFull Backup @ 2amT-Log backup every 2 hours from 6am - 10pmProcessor - is a given that it will be high specMemory - 16gb or 32gbI am looking at the following for storage;PARTITION 1 - RAID 1 - OS ( 2 Drives 300gb each ) or 500gb depends at time of purchasePARTITION 2 - RAID 1 - SQL Databases and Indexes ( 2 Drives 1 tB each )PARTITION 3 - RAID 1 - SQL Logfiles ( 2 Drives 1 tB each )PARTITION 4 - RAID 1 - TempDB ( 2 Drives 1 tB each )PARTITION 5 - RAID 5 - Backups and software storage/updates ( 3 Drives 1 tB each )Thoughts and suggestions on configuration please

Moving System Databases

Posted: 19 May 2013 01:25 PM PDT

Is it safe to move the system databases (excl TEMPDB in this question) to another location from the default install location.This being done either during the initial install or during an outage?I can remember getting burnt a few years ago on SQL 2000 when an HotFix kept on failing because it was looking for the System databases to be in a certain location (default) and I had moved them.This was a while ago and may have been a one off and / or not applicable now.thanks

system state backup failed on SQL server 2008 box

Posted: 11 Apr 2012 06:27 PM PDT

Hi,Server os - win 2008 Enterprise editionDatabase - SQL 2008 Enterprise editionEvent viewerLog Name: ApplicationSource: Microsoft-Windows-BackupDate: 4/5/2012 11:16:14 AMEvent ID: 517Task Category: NoneLevel: ErrorKeywords: User: SYSTEMComputer: server nameDescription:The backup operation that started at '‎2012‎-‎04‎-‎05T05:43:34.427178200Z' has failed with following error code '2155347997' (The operation ended before completion.). Please review the event details for a solution, and then rerun the backup operation once the issue is resolved.Could you tell me, have anyone facing this type of error when starting system state backup on SQL server 2008 box?thanksananda

Production release

Posted: 19 May 2013 03:21 PM PDT

Hi Friends,I have a question about Production releasewe are using SQL server 2005 enterprise edition physical production server, now I built 2008r2 standard edition on Virtual server.here my question is I am moving production server 2005 to 2008 r2. what are the steps for production release and rollback(something goes bad)in advance thank you.:-)

Automated audit of all SQL Servers over multiple domains

Posted: 19 May 2013 07:07 AM PDT

I recently joined a company that has merged with another. Both companies had Enterpise licensing and seem to have set up loads of SQL databases without question. Since the companies have merged IT department have decided to create a new windows domain and started setting up even more SQL servers there to hopefully migrate all systems over.So at present I have three domains where I am discovering more and more SQL servers crawling out of the woodwork almost daily. Some I have discovered are still SQL 2000 databases and I know they are already starting installation of SQL2012 databases.I would like to audit all databases but due to the size of the organisation, I think it would be best to setup an automated task to audit all the SQL Server daily centrally from one SQL ServerThe task should determine the following1. All the Servers that have SQL Server installed on them2. All the instances of each SQL Server3. Version of Each Instance4. List all databases on each instance, its files, sizes, free space etc.5. List all failed jobs in the last 24 hours6. List of where backups are stored, how many there are7. Write all this information into a relational database8. Set up automated reports to8.1 Warn of servers that are no longer available8.2 Warn of new servers that have become available8.2 Report on all Versions of SQL so we can start upgrade project8.3 Report on databases that are no longer used8.4 Report on Databases using large amounts of disk space that could be freed up8.5 Report on database/table growth and show a graph on growth or declineI realise SQL Server 2008 has made an attempt to do this but this does not work for older versions so I thought it might be worth writing this as a training exersize. At the same time I would like to forward plan a bit to ensure it will be compatible with 2012The first thing I need to do is to run sqlcmd -L and have this execute on each of the servers to detect what other servers (or/and instances) are out there. Since I have seen that this does not always render all the SQL instances for some reason, I would like set this to be run on each of the servers that my Audit server is aware of..What I have so far is (sorry I know its not much, but it is a start)/* Start */EXEC sp_configure 'xp_cmdshell', 1 GOEXEC sp_configure 'show advanced options', 1GORECONFIGUREif exists (select name from sysobjects where name ='temp_audit_file') drop table temp_audit_filecreate table temp_audit_file (all_instances varchar(200))insert temp_audit_fileexec xp_cmdshell 'sqlcmd -L'select * from temp_audit_filewhere Upper(all_instances) not in ('Servers:','')order by 1drop table temp_audit_file/* End */
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog