InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:01:54 GMT20http://www.sqlservercentral.com/Forums/Topic1453939-357-1.aspxHI,ANY ONE GIVE ME SCRIPT FOR INDEX REBULID AND UPDATE STATISTICS IN SQL 2008 R2Fri, 17 May 2013 05:23:08 GMTpradeep.k 50453http://www.sqlservercentral.com/Forums/Topic1370335-357-1.aspxHi,Apologies if this has been posted before, but I keeping coming across excessive log growth on my reporting databases that are in SIMPLE recovery (can get to 80GB which is unusual for these databases). These databases are large monthly reporting databases that perform many bulk inserts (via our loader) into the database throughout the day. These databases are large in size as a result for example this month’s being around 200GB so far.Now on our 2005 server transaction logs were never a problem. However, we have recently taken the plunge and migrated to SQL 2012 with this reporting server.Ever since then we have noticed that index rebuild \ reorganise seems to take much more log space than compared to 2005 (even when disabling the loader and ensuring nothing is reading \ writing whilst this is taking place).However, what has thrown me is that on occasion, we can get log growth for which seems no reason. When looking into this when it is happening the log_reuse_wait_desc on sys.databases says either:ACTIVE_BACKUP_OR_RESTORE or LOG BACKUP. However, no backups are taking place on the server at all. And I confirm this by running:SELECT session_id, blocking_session_id, command, database_name = DB_NAME(database_id), percent_complete, estimated_completion_time, wait_type, wait_time, wait_resourceFROM sys.dm_exec_requests WHERE command IN (N'BACKUP DATABASE', N'BACKUP LOG');I also check that there are no large uncommitted transactions and find none. I’m also a bit confused why it would say LOG_BACKUP in the wait description when the database is in SIMPLE recovery.Would anyone be able to point me in right direction on what root cause could possibly be here? Or what SQL could be doing in the backend (perhaps a checkpoint of some sort?)Any help would be appreciated.(BTW the quick fix I found when this happens is to change the recovery model of the database to FULL, then back to SIMPLE and then shrink log, but I’m really no keen on doing this everytime!!)ThanksTue, 09 Oct 2012 05:40:27 GMTSam -480699http://www.sqlservercentral.com/Forums/Topic1454246-357-1.aspxBad news, our website server crashed...Luckily few hours later our backup server was up and website was live again.Hopefully tomorrow our original server will be fixed and i'm looking for the best practice to merge SQL data.this is the situation right now :Original server has backed up data till 16th 01:30AM (night.bak)Original server has data which is not backed up data till 17th 00:30AMso basically there is 23 hours missing on the night.bakI restored the night.bak to the temporary server since 17th 03:00 new data is being added to the temporary server.Tomorrow i wish to take data from the temporary server (17th 03:00 till 19th) and put it back on the original server.I believe i can't make differential backup on the temporary server and restore it on the original server because last backup on original server has timestamp of 16th 01:30AM but i don't really know so i'm asking here...My main concern is to preserve data of joined tables that share index keys Thanks for the helpers!Sat, 18 May 2013 05:20:37 GMTcrashoverrridehttp://www.sqlservercentral.com/Forums/Topic1452998-357-1.aspxHi,Can anyone please help me...I am new in SQL Server.I have scheduled the database backup---full backup-- everyday morningdifferntial backup--every 2 hourlog backup-- every 1 hour.my doubt is, log backup is running every 1 hour and disaster comes after that for example log backup is running at 12 PM after that backup done at 1 PM , suppose the disaster comes at 1:05 PM , so how can we recover the database at point in timeWed, 15 May 2013 03:46:48 GMTarooj300http://www.sqlservercentral.com/Forums/Topic1454073-357-1.aspxhello,i want to use sqlwb to make backup on bat file, someone can give the right syntaxe please , i don't that the sql managemen studio openFri, 17 May 2013 09:48:04 GMTeboucicauthttp://www.sqlservercentral.com/Forums/Topic432433-357-1.aspxI'm using sql server 2005 and I need a way to back-up 200+ stored procedures into individual files with the same names as the stored procedures (basically a back-up of all stored procedures individually) at the same time and not one by one.Please help.Wed, 12 Dec 2007 10:14:27 GMTNoman Salimhttp://www.sqlservercentral.com/Forums/Topic1452195-357-1.aspxHi FolksI am trying to understand why my differential backups are almost as big as my full backupsafter rebuilding the indexesMy full backups are at 12:30 am every night with differentials every 4 hoursOn sat at 2am I rebuild the indexesI then notice that my differentials until the next full are huge, almost as big as the fullI am under the impression that I should rebuild indexes after a full but how do I get my differential sizes down after that rebuild ?do i then need to do another full ?ThanksJimMon, 13 May 2013 10:07:21 GMTJC-3113http://www.sqlservercentral.com/Forums/Topic487613-357-1.aspxHi All,All of our database servers are doing backups and saving the .bak files out on a network share. With SQL 2005, how can I point to that share and restore the database in SQL 2005 environment?When I do 'restore database --> From Device --> File; when I click the 'ADD; button, I only see my local drives on the computer. How can I map to my network share and point to my restore file on the network?I need to do a restore for a database with the latest backup which is there on a network drive.--Sat, 19 Apr 2008 20:45:05 GMTALIF-662928http://www.sqlservercentral.com/Forums/Topic1449745-357-1.aspxI've recently taken over database duties and am trying to learn as I go. Our company uses Redgate 6.5 for backing up one of our larger databases. Here is our backup schedule...- Full once a month on the first- Deferential every day- Log every hourThis all seems to work fine. This past week we had a need to restore the database to a test database. The restore point we needed was April 18th.- I restore the FULL backup no problem (April 1st)- I restore the April 1st DIFF backup no problem- I attempt to restore the April 2nd DIFF backup and get the following error...[i]SQL error 3136: This differential backup cannot be restored because the database has not been restored to the correct earlier state.[/i]I have found that if I restore all the Log files inbetween the DIFFs I can then restore the DIFF. So...- Restore FULL- Restore April 1st DIFF- Restore All LOG backups between April 1st DIFF and April 2nd DIFF- Restore April 2nd DIFF- Restore All LOG backups between April 2nd DIFF and April 3rd DIFF- Restore April 3rd DIFF- etc...This doesn't seem logical to me or from everything I've read. What am I missing or doing wrong?Mon, 06 May 2013 08:49:54 GMTScott Costello-401147http://www.sqlservercentral.com/Forums/Topic1450696-357-1.aspxHello,We are on SQL Server 2012 but I don't think that matters a lot for my question (I'm posting to 2005 as it has the backup forum). Does anyone know what steps to take after AppAssure restores an entire SQL Server instance to a server? I have scoured the vendors very sparse documentation and there aren't any details. They have some marketing materials that say it just works but my boss wants a step-by-step document saying what needs to be done after a complete restore. I am not allowed to test a recovery so that i can learn by doing.It may sound like I'm being lazy but I really have done a lot of google searching and reading of blogs, vendor docs, etc. to try to get the answer. If you have a link to point me to that would be great. I realize that not many who read this will be using AppAssure but I'm hoping...Thanks.Wed, 08 May 2013 10:50:27 GMTpharmkittiehttp://www.sqlservercentral.com/Forums/Topic640749-357-1.aspxHi guyz! I want to create scripts that can do a backup on a database on regular basis. How can i do that? Please help. The database is in sql server 2005 express edition.Wed, 21 Jan 2009 07:58:45 GMTkerusyhttp://www.sqlservercentral.com/Forums/Topic1447157-357-1.aspxHi,Im not sure this is the right area to post this in, but it is backup related. I have SQL server 2005, which holds close to 100 databases. We do three different types of backups in a day on these databases: 1 at 7am where we do a full backup of a specific database, 1 at 6:30pm where we do a full backup of a specific database, 1 at 8:30pm where we do all the other databases(also full backups). Our vendor that hosts our server installed shadowcopy, and that does incremental backups at 9:30pm. Now, for some reason, our sql server backups seems ok but once shadowcopy start, it fails the vss writer(sqlserverwriter) to the point it cant connect, even though its setup as automatic restart if it fails, and i see a lot of errors during this time in the event log. There are different types of errors, but here is one of them:2013-04-25 21:30:57.59 spid201 BackupVirtualDeviceFile::SendFileInfoBegin: failure on backup device '{ACB91C03-3021-4581-9C95-70A4A3E775AD}95'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).our vendor thinks that our sql server backups are not closing properly(and yes our backups are marked as complete in the event log about 30 minutes before shadowcopy starts). At this point, ive tried to do a lot of research, but im at a standstill and i dont know where to look for answers regarding this issue anymore.Has anyone else had this problem?Fri, 26 Apr 2013 14:32:46 GMTjenny 12957http://www.sqlservercentral.com/Forums/Topic383932-357-1.aspxI scheduled daily backup on tape and it was working fine for two weeks.Last night the backup failed and threw the error BackupTapeFile::SkipFileMark: SetTapePosition failure on backup device '\\.\Tape0'. Operating system error 23(Data error (cyclic redundancy check).).Please give me a solutionSat, 21 Jul 2007 02:52:00 GMTRETHEESHhttp://www.sqlservercentral.com/Forums/Topic861118-357-1.aspxI'm pretty sure if you have a log chain in your commvault backups ( full, differential, incremental/Tlog backups) you don't want to also backup with Management Studio or any other "native" approach. I need to know if commvault writes sql backup files to it's own storage device in a proprietary format that cannot be used by native tools for restores.This will have a large impact on our development team. Among the concerns is who has access to the commvault GUI and will have to do all of the backup/ restore work. Commvault does offer us the potential advantage of removing hundreds of GBs of backup files from our SAN as well possibly faster backups than native sql.I went to the Commvault user forums I could find. One had no recent posts on the sql IdataAgent ( none in the last 6 months), the other had no forum dedicated to this topic.Sat, 06 Feb 2010 09:40:09 GMTIndianrockhttp://www.sqlservercentral.com/Forums/Topic719131-357-1.aspxI am currently trying to investigate why our nightly full backups have been failing. The nightly full backups were working correctly but have now stopped. I have had a look in application logs and get these following messages.Executed as user: NT AUTHORITY\SYSTEM. ...ercent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (Message 3211) 41 percent processed. [SQLSTATE 01000] (Message 3211) 50 percent processed. [SQLSTATE 01000] (Message 3211) 61 percent processed. [SQLSTATE 01000] (Message 3211) 70 percent processed. [SQLSTATE 01000] (Message 3211) 81 percent processed. [SQLSTATE 01000] (Message 3211) 90 percent processed. [SQLSTATE 01000] (Message 3211) Processed 360 pages for database 'master', file 'master' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed 2 pages for database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 362 pages in 5.107 seconds (0.580 MB/sec). [SQLSTATE 01000] (Message 3014) 10 percent processed. [SQLSTATE 01000] (Message 3211) 20 percent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (M... The step failed.The backup data at the end of "nwcscmdsdataBACK(\\brentsan\e$\Brentvmsollis-BackupwcscmdsdataBACK.BAK)" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.Error: 3266, Severity: 16, State: 1.BACKUP failed to complete the command BACKUP DATABASE NWCSCMDSDataREP. Check the backup application log for detailed messages.Error: 3041, Severity: 16, State: 1.I am not sure if this a permissions issue or disk size issue. I think it is unlikely to be a permissions issue as the Log backups are working fine. Any help would help as to what might be the cause would be much appreciated.Mon, 18 May 2009 08:39:55 GMTeseosaoregiehttp://www.sqlservercentral.com/Forums/Topic1049429-357-1.aspxI have a SQL 2005 SP3 database that has a full-text index. I do a full backup and then restore that backup with move to my SQL 2008 R2 server. The restore appears to be successful but then the log file starts to grow. I run a t-log backup and it does not truncate the log. dbcc opentran shows an index rebuld happening on that database. Confio IgniteFree points it to FT Gatherer running. An attempt to take the database offline and/or delete the database shows an error and points to proces -2.Any ideas? If I can get this database moved I can retire this 2005 server because it's my last database on there.Tue, 18 Jan 2011 08:53:33 GMTlavignehttp://www.sqlservercentral.com/Forums/Topic1444823-357-1.aspxA few weeks ago I was at an event where someone came up and asked me if it's bad to perform transaction log backups while full/differential backups are running. I said I couldn't come up with any reason why it would be "bad", but it's important to note that:- VLFs will not be marked as inactive by a log backup while a full/diff backup is running- There can be increased I/O impact due to multiple backup processes running concurrentlyThey then explained that their supervisor has handed down an edict that no transaction log backups can run while full backups are running. They asked if I could explain my thoughts to their supervisor, who was also present at the event. I tried. They blew me off and said that "I'm never going to need to use a transaction log backup that was taken while a full or differential backup was running, so why bother creating them?"I thought for a second, and my replies at this point were:- The longer you wait in-between transaction log backups, the longer they will take to create- You may find yourself needing those log backups if you have to do a point-in-time restore to a time that a full or differential backup was running.The supervisor told me I was just "making stuff up." I said I'm sorry they felt that way, and left it at that.This exchange left me feeling absolutely certain that I never want to work for that person. Ever. It also left me curious as to if I was really right or not. Does anyone have any comments either for or against my replies in this situation? I'd be interested in hearing any and all input.Sun, 21 Apr 2013 15:08:08 GMTSQL_Bobhttp://www.sqlservercentral.com/Forums/Topic1445111-357-1.aspxIntro:I have sql server installed on c drive but the logs (LDF) is stacking up and I am getting out of space.Specs:Let my Db name be Analytics and the file Is analytics.ldfCurrent settings are: recover - full , autogrowth enable but restricted by 1mb.Analytic.mdf is being used daily by me , so can I resolve this issue.Question:1)so what is best practice to make a backup of that log and data , and also reduce the space they are using.2)if Taking offline is a possible solution , need instructions to get it safely back online.3)Will my data get effected if I create new similar name files and delete previous one after backing them up?solution: Mon, 22 Apr 2013 12:39:02 GMTIce007http://www.sqlservercentral.com/Forums/Topic757972-357-1.aspxHi,Do I have to Deattach or take offline the DB to copy the .mdf and .ldf files to other machine.I can't do a back up and restore as the server doesn't have enough space to do it.-- Regards,VijiWed, 22 Jul 2009 22:40:09 GMTAnamikahttp://www.sqlservercentral.com/Forums/Topic1435285-357-1.aspxHi,In my Datawarehouse project(implemented 2 yrs back) we want to change the Recovery Model from Simple to Full.What will be the impact? How the performance will be affacted. What things I need to take care of?Thanks,AnupamTue, 26 Mar 2013 01:07:14 GMTanupamkhttp://www.sqlservercentral.com/Forums/Topic1436294-357-1.aspxhiwhen i am taking full backup and i am restoring full back up i am getting error like "the database is not accessible"please help me regards,gamitThu, 28 Mar 2013 01:48:22 GMTgamit124http://www.sqlservercentral.com/Forums/Topic866919-357-1.aspxDatabase Design - What are included columns with respect to SQL Server indexing?Wed, 17 Feb 2010 05:50:52 GMTSree Arjun Divhttp://www.sqlservercentral.com/Forums/Topic1442036-357-1.aspxHello,I have question regarding backup SQL'05.Please look ta my script :sqlcmd -S srv_name -U sa -P pass -Q "BACKUP DATABASE Database_name TO DISK = 'D:\Backup\%date%_Database_name.bak' WITH NAME = 'Database_name_backup', FORMAT"If i remove %Date% backup process will be completed successfully. But with %Date% I ahve folowing error:Cannot open backup device 'D:\Backup\%date%_Database_name.bak' Date is repleaced by system time...Please tell me what is wrong...Thx for helpmichalSat, 13 Apr 2013 09:15:05 GMTmjaroszhttp://www.sqlservercentral.com/Forums/Topic1439737-357-1.aspxI installed Redgate Backup5 on Windows 2003 + SQL Server 2005 with SP4.But I am not able to see sql backup agent service in services.msc. I am not able to register and take backup thorugh redgatepls helpMon, 08 Apr 2013 04:23:23 GMTAkkarehttp://www.sqlservercentral.com/Forums/Topic1437175-357-1.aspxDue to ERP upgrade we're upgrading our SQL Server from 2005 to 2012. Our current DB servers (Access thru Microsoft VM) are as follow:Production environment: Windows 2003 Server 32-bits, SQL Server 2005 - let call it ‘DBSrvProd’Test environment: Windows 2003 Server 32-bits, SQL Server 2005 – ‘DBSrvTest’Upgrade Test environment: [b]Windows 2008 Server R2 64-bits, SQL Server 2012 [/b]– ‘DBSrvUpg’In the Upgrade environemnt’s SSMS we created DB Engine ‘DBSrvUpg’ and we try to connect to our old SQL Server 2005 DB Engine (DBSrvProd and DBSrvTest) so that we can migrate DB to DBSrvUpg, we are able to connect DBSrvTest with no issue at all, however we are not able to connect to 'DBSrvProd'. We are getting the following error:-----------------------------------------------------------------------Cannot connect to DBSrvProd.ADDITIONAL INFORMATION:A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476-----------------------------------------------------------------------------We have compared all configurations between the Prod and Test environment and it appears to be identical, thus we're puzzle why we can connect to one but not the other. We have checked the following between the two servers:1. SQL Server Configuration Manager's TCP/IP all appears to be enabled and set to default port: 14332. SSMS instance for DBSrvProd and DBSrvTest 'Allow Remote Connections' checked3. The Firewall Windows service (ipc) are both disabled, when I enabled it and check the properties they both have similar setupAll of the three servers are login with SQL Authencation as 'sa', which belongs to sysadmin server role. Please Help!Fri, 29 Mar 2013 21:37:10 GMTTraderJoehttp://www.sqlservercentral.com/Forums/Topic1431112-357-1.aspxHi,In one of my production envirnment Maintinace Backup job is taking longer than expected.I have maintinace plan to take the full backup on weekly baisis.Datasize is around 3.5TB (174 DBs).Previousaly This job used to take around 10-11 hours and now its taking more than 24 hrous.Below is the configuration.SQL Server 2005 EE + SP4,Windows Server 2003SAN disk on 3PARI checked the disk latency and that is also below 10ms.We have seprate disk for backup.Could you Please guide me to find the root cause of this issue.Its very urgent..RegardsAjay BhardwajThu, 14 Mar 2013 10:40:54 GMTajay.bhardwaj13http://www.sqlservercentral.com/Forums/Topic1437905-357-1.aspxHi,I just started doing this and confused why the backup grows by a gig plus a day...I really don't need incrementals and it's not set as that, its just a backup of helpdesk tickets...I only want an overwrite every night. I assume its the transaction logs?I read something about adding INIT?ANy way this is what I have if anyone see's ssomething odd??ThanksJoeBACKUP DATABASE [servicedesk] TO DISK = N'D:\SQLBackup\servicedesk-nightly.bak' WITH NOFORMAT, NOINIT, NAME = N'servicedesk-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOTue, 02 Apr 2013 08:03:05 GMTjbalbohttp://www.sqlservercentral.com/Forums/Topic1435254-357-1.aspxcan we restart the sql server in single user modeif yes what will happen to serverMon, 25 Mar 2013 22:43:03 GMTNew persopnhttp://www.sqlservercentral.com/Forums/Topic1389014-357-1.aspxHi....I am new to Snapmanager for SQL jobs trying to find a solution for my issue. I run the SnapManager for SQL Backup wizard to create my job and then schedule the job thru' Sql Studio Manager. When the job runs, it fails every time, stating :-Details 'An unexpected Error occured while executing new-backup.Details:Cannot invoke this function because the current host does not implement it.Stack Trace: atSystem.Managemnt.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Objectinput, Hashtable errorResults, Boolean enumerate) at System.Management.Automation.PipelineNode.Execute(Arrayinput, Pipe outputPipe, ArrayLIST&resultList, ExecutionContext.context) at System.Management.Automation.statementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPIPE, ArrayList&resultList,ExecutionContext context)The job parameters are;new-backup -svr '<servername>' -d '<servername>\WSUS', '0' -ver -verInst '<servername>' -mp -mpdir 'D:\Program Files\NetApp\SnapManager for SQL\SnapMgrMountPoint' -retainBackups 7 -lb -bksif -RetainSnapofSnapInfo 8 -trlog -gen -mgmt dailyAny solutions would be greatfully appreciated to overcome this issue.Chiaz GuysTue, 27 Nov 2012 01:56:35 GMTStaffyhttp://www.sqlservercentral.com/Forums/Topic1434105-357-1.aspxI have restored a database on prod server from 2000 server to 2008R2. It's in simple recovery mode. The log started growing right after the restore process got finished. When I checked the dbcc opentran on the database, I got a spid number as 38s. I cannot see this spid either in sp_who or sp_who2 and I cannot kill it. It's a prod server. I cannot do a server restart. Any suggestions??Thu, 21 Mar 2013 19:06:51 GMTmuthyala_51http://www.sqlservercentral.com/Forums/Topic1420560-357-1.aspxHI All,Can we restore/recover individual Table in Microsoft SQL Server. As far as i know it is not possible Out of the box. I dont want to use any third party due to cost issue.Is there any work around to achieve it. Thanks.Regards,InderFri, 15 Feb 2013 07:57:16 GMTinderrathorehttp://www.sqlservercentral.com/Forums/Topic1415920-357-1.aspxHi,Bit of an idea to ponder:I have a server with 15 user databases ranging in size, the largest being 120GB. There is a blanket 'Backup Transaction Log' job with does all DBs and runs every 15 mins. The job generally takes about 1 minute to complete. What I have noticed however is that sometimes (when there seems to be some batch jobs running unsurpirisingly :rolleyes:) the complete tx backup job will take up to 40 mins, and some of the backup files will be as large as 40GB. This is a bit confusing but I'm unsure if I can do anything specific from a strategy point of view:1) Presumably more regular backups would keep any logging tuncated so the jobs should be a bit quicker?2) Would the knockon effect of a shorter period between backups not mean that a lot would be ignored? For instance every 15 mins means if I have one that starts at 20:00 but takes 41 mins, the next one won't kick-in till 20:45. Shortening the schedule won't be much benefit?3) Maybe the tx backups need to be split into their own jobs, rather than having one job which runs through the databases in turn? Would there be any implications to potentially multiple tx backups occuring simultaneously?4) Am I worrying about nothing?Tue, 05 Feb 2013 09:01:40 GMTrararahttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxWe have Sql Server 2005 database server in both production and development environment.I have the requirement to restore a database from production to Dev.I my production database has one mdf and one ldf files But my dev environment has 1mdf and two ldf files.I am not sure how to restore in this one ldf backup to two ldf instance.How can achieve this ?? Any suggestions/ideas/approach??Thu, 14 Mar 2013 08:48:01 GMTTripzhttp://www.sqlservercentral.com/Forums/Topic574247-357-1.aspxWhile connecting to SQL Server 2005, I am getting the following error."An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (Provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"I restarted the Server and then tried to connect to SQL Server, it is allowing me to connect. Whenever the problem comes, I am restarting the server. But I want the actual solution for this problem.Can anybody help me out in solving this issue.Tue, 23 Sep 2008 06:15:09 GMTsundaram82http://www.sqlservercentral.com/Forums/Topic1432581-357-1.aspxWe just upgrade our QA environment to SQL Server 2012 from 2008R2. Our standard process is to backup four databases from ETL server and restores each night on DB server. When we are on SQL 2008R2, the restore process was taking only 15-20min to restore four databases (total size = 130GB) but now we are facing issue where SQL Server2012 is taking same size of database almost 2:20hrs. Currently, my Dev environment and production are still on 2008R2 and my restore job via SQL script finish in 15min but I am having hard time to figure out why 2012 version is taking 2+hrs? Any help greatly appreciated. SQL Server Version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 X64> (Build 7601: Service Pack 1)Tue, 19 Mar 2013 05:39:50 GMTkeyun21http://www.sqlservercentral.com/Forums/Topic702611-357-1.aspxI have a database in SQL server 2005 (64 bit) server. The recovery mode is set to simple recovery, and the transaction get bigger and bigger every day until I perform database backup or shrink log file. Why is this happening? How can I solve this issue?Wed, 22 Apr 2009 12:21:26 GMTTonyK_Gahttp://www.sqlservercentral.com/Forums/Topic1433024-357-1.aspxI have a full recovery database used for holding logging information, typically Name, Type, Time and some other values. Each record is its own transaction and the db gets around 40 million inserts per month. The database is mirrored.We like to keep only a few months logs in the db and have a nightly job that, for hardware reasons, deletes batches of 450 records with a 2 second waitfor during a 3 hour graveyard shift window. The job will terminate if a) it reaches the monthly limit; b) it hits the end-of-shift hour; or c) the 12 GB log file gets to less than a preset amount of free space. The following step of the job performs a log backup. This job has been running for months. There are also 4 other periodic log backups during the day.The problem: about a week ago whilst I was "other duties as assigned" the log filled up - presumably by the batch job though I don't know for sure - and was not truncated by an log backup for 3 days, growing a further 6GB from logging inserts. The batch job ceased to execute as the log file was already full. This state appears to have been ended by a scheduled reboot of the server which flipped things over to the mirror.The question: why did around 15 log backups never truncate the log file? At first I thought "open transactions don't get truncated" but at either 1 or 450 rows per transaction, 18GB is *a lot* of open transactions. But what else is there? Fal.Wed, 20 Mar 2013 01:23:34 GMTFalhttp://www.sqlservercentral.com/Forums/Topic1430769-357-1.aspxHI to all, Server is getting restarted whenever we are running the SQL Backup [scheduled backup maintenance plan job and also tried manually ]. This issue has started after the storage migration activity. 1. on the daily monitoring basis i check the scheduled backup job at 10 am which is not executing nor giving any errors and in job activity monitor also job is not showing failed status and at the same time 10am daily server is rebooting no errors are identified in windows or in sqlserver logs.2. I tried to execute the backup from job activity monitor -- start job at a step 3. from data base right click --- tasks --- backup 4. management---- maintenance plan ---- execute all the possible ways i tried to take backup but after some time backup running and server is rebooting what should we do in this scenario to solve this Thu, 14 Mar 2013 00:07:09 GMTnaga.rohitkumarhttp://www.sqlservercentral.com/Forums/Topic1430655-357-1.aspxHi, I'm observing strange backup behaviour on my system. We have a large (400GB) database and one full backup job for each day of the week. Each job is set to overwrite, so while the filenames remain the same, the contents are updated. At any point in time therefore we have 7 full backups available.Now, we recently purchased HyperBac (due to backup space and time pressure) and initially the new compressed backups didn't result in smaller backup files. I realised that retaining a constant filename and overwriting the contents of the backup file meant that the file wouldn't shrink to the new backup size.So I deleted one of the files (Monday's) and waited for the new compressed, faster backup to run. And here's the odd thing. The new backup was compressed - to about 20% of the data file size (80GB). Great. Problem was, the reduction in I/O wasn't reflected in the backup time. In fact, the backup time was longer than when the file was 400GB - twice as long in fact!So now I have a situation where backups Thursday-Saturday fill a small part of an existing 400GB file and take an hour and a quarter, while the backups Monday-Wednesday extend an existing 80GB file slightly and take two and a half hours.Our storage system is an EMC SAN with 30 physical drives incl 9 SSDs as a storage pool and cache, so we should be getting better performance than that anyway, but that's another question. Every time I've used HyperBac in the past it's given me smaller, faster backups, but not this time. (This is not a HyperBac-specific question though, running the backups without compression gives the same timings - 2.5h if it has to create or grow the backup file, 1.25h if it can fit the backup into an existing file.)The processes running on the server are pretty much the same each night, so there's nothing else taking up I/O resources on some nights and not others.I've looked at spreading the backup across multiple files, changing numbers of buffers, block sizes, etc, and I may shave a couple of minutes off but nothing close to an hour and a quarter!So, it appears to me that, where there's an existing file to backup into, and that file has sufficient space to hold the backup without having to grow, then the backup will run much faster. I've not been able to find anything on the web, official or otherwise, to prove or disprove that SQL Server and the backup subsystem behaves in this way.Anyone seen anything like this?Wed, 13 Mar 2013 15:28:31 GMTtim.nyland-joneshttp://www.sqlservercentral.com/Forums/Topic1430256-357-1.aspxHWed, 13 Mar 2013 02:55:52 GMTico.kruger