SQL Server 2000 sp3 Enterprise (8.00.818) running on W2K Adv Server cluster.
DB's and backups resided on a fibre SAN attached IBM FAStT 500 storage controller.
I had problems with random restore failures, getting errors such as "3270 Internal Consistency Error" on log restores or on full restores after reporting problems with some of the files. This was fixed (we thought) by disabling the read caching on the FAStT 500 storage controller, per Microsoft's recommendation.
We have a custom-written log shipping solution that maintains a 2nd copy of our user DB on the same system, so there's 700+ backups/restores of various types in the course of a week. After two months of error-free operation, we just got another Internal Consistency Error, but this time on a differential restore, which was a first. Looking back, we realized we were running a DBCC INDEXDEFRAG command on the source DB at the time the differential backup was running. Taking a new differential and using it in the restore sequence worked fine, so clearly the problem was in the first diff backup file.
Anybody else noticed any problems with running DBCC INDEXDEFRAG at the same time as a differntial backup?
Can i restore a diff. backup alone without a complete backup?
(what i am trying is this.....there are two different servers at two diff. places....i need to have both servers in sync. at all times.modifications will take place in one server and the modifications have to be reflected in the other server.i could not go for replication as the servers cannot be connected.sending complete backups daily will be a overhead .hence planning to take complete backup once and send diff. backups alone on subsequent days to the other server. how can i achieve this?)
Dear all I am new to the MS SQL, my problem is as follows. I am having a online database on sql 2k. every 15 days we have to give payout from our system, so we have a offline server in our office, we take the complete backup of that day & restore the same on the offline server, & start the payout process. The problem is that the full backup is a big file & take a lot of time for downloading from online server. is it possible that we take on diff. backup of that day & will restore the same on the offline server so the file will take less time to download. but my offline backup is 15 days old, will that update all the records or not?
In playing with differential backups, i have taken a full last night, 17GB, took my first diff today 16.5GB, took another diff right after, it was still 16.5GB, took another diff right after 16.5 GB. This database has 0 activity during the day, the import takes place @ night, and select's are done to it during the day. Any help would be greatly appreciated
I have a handful of jobs that I need to troubleshoot, but this one will hopefully give me enough insight to do the rest myself. I need to figure out why a Maintenance Plan that performs a Diff Backup on 4 databases, and then a transaction log back up on the 4 databases is failing. I just get the following error message, and I cannot tell what failed.
Executed as user: <SERVER_NAME>SYSTEM. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:20:05 AM Progress: 2008-01-03 11:20:05.95 Source: {SOME_STUFF_WAS_HERE} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-01-03 11:20:06.35 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.35 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.37 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.37 Source: Back Up Database (Differential) ... The package execution fa... The step failed.
How can I find out how to fix this problem? And please let me know if I am doing something wrong. (For instance, perhaps those two tasks shouldn't be together.)
I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:...." directory.
Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:...." directory only, as it has no partitions.
This restore process gives me error :
Restore failed for Server 'HSVMMICROFIT'. Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicroFitHealthStar v6.0 Server EditionDataMICROFIT_HealthStar_Data.mdf'.
What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ??????????????
We lost (Couldn't find ) the full backup on a Tape we were supposed to use for restore, but seems like they lost it. All we have is a Diff backup. Wondering if that is any good at all .
I need to copy the last differential backup file from the production server directory to a data warehouse server directory. There are 3 differential backup files on the directory and I need to grab the latest one. I have the following syntax which work from a batch file but it does not work when I put it into a job step as a cmd even though I remove the double %% to only one %.
echo @off set path1="192.29.305.213$SqlserverProductionBa ckupsKBR_PROD" set path2=K:SqlserverDatawarehouseBackupPROD for /f "tokens=*" %%a in ('dir /b /a-d /o-d "%path1%DIFF*.bak"') do copy "%path1%\%%~a" "%path2%" & goto nextstep :nextstep Echo File Copied.
Even when I change it to (only one % for the variable),
for /f "tokens=*" %a in ('dir /b /a-d /o-d "%path1%DIFF*.bak"') do copy "%path1%\%~a" "%path2%"
It does not work..........It runs successfully but no file is copied or I get
Message Executed as user: PROD23Sqladm024. The process could not be created for step 1 of job 0xEAAF943771FF304A9E7AD8ADAC24F96C (reason: The system cannot find the file specified). The step failed.
I know that the file is there. It works with batch file. Poweshell script can be OK. No SSIS - Not installed
Hello, My master database (MS SQL 2005) has simple recovery model, however, when performing diff backup of "all databases", I am getting the following error:
Executing the query "BACKUP DATABASE [master] TO DISK = N'X:\Database Backups\diff backups\master\master_backup_200803251235.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'master_backup_20080325123514', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I choose to back up only "All user databases", then it performs just fine. Why I cannot run diff backup of master?
Normal] From: XXX "(DEFAULT)" Time: XXX SQL statement: BACKUP DATABASE [msdb] TO VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14" WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536; [Warning] From: XXX "(DEFAULT)" Time: XXX Error has occurred while executing a SQL statement. Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'
Ive tried to do a full followed straight after by a diff but doent help.
I need to write a 'select' statement to fetch data from different tables, which are located on different servers. Can any one help in writing this 'select' statement with out moving the tables on to same server.
I am running INDEXDEFRAG on one the tables. It has a Clustered index and 4 non clustered. Should I run the INDEXDEFRAG only for clustered, or for all the 5 indices?
I pretty much understand the differences between DBCC DBREINDEX and DBCC INDEXDEFRAG. However, I need the forums help to understand a few specific issues relating to clustered/non-clustered indexes and the advantages/disadvantages of running the DBCC DBREINDEX/INDEXDEFRAG against the table or against each specific index...
"If a table has a clustered index, it's only necessary to re-index the clustered index because any non-clustered indexes on that table will be automatically re-indexed as well."
I think the above statement is true for DBCC DBREINDEX but is the following statement true for DBCC INDEXDEFRAG:-
"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."
Following on from the above, is there any advantage with an index maintenance strategy to individually running DBCC DBREINDEX against each specific index as opposed to running it against the table and letting SQL sort out the underlying indexes? Does the same apply to DBCC INDEXDEFRAG?
I want to run DBCC INDEXDEFRAG(Db_name, Tab, Idx) for many of the databases . Number is huge and it is near impossible to go to each server and do a manual run. Can someone provide me a scrip to generate the above syntex for all the tables in a db?
I have a quite big database (150GB) that is running 24/24 7/7 with each day 2 windows of about 2 hours in which people are working little. So doing a complete indexrebuild during the weekend is not possible. The only option is the rebuild/defrag indexes is during those windows. Problem is:I can't do it on all tables because it takes too much time. So I was thinking about creating several SQL scripts and each of those is doing some tables/indexes. Problem is that I have a few hundreds of tables and if I want to move an index from 1 window to another, I have to change those scripts manually. Quite a time costing and error-prone procedure.
So I was hoping there is some tool that shows me all existing indexes and give me the possibility to add them to a script and once I have done that, generate the scripts to be run. I have launched some searches on the forum but I didn't find any mentioning of the existence of such a tool. Does someone know of such a tool or have a better idea how to do this?
Thanks, Alain Krikilion
IF Debugging = removing bugs from program THEN programming := putting bugs in program;
Microsoft states that dbcc DBREINDEX automatically updates statistics but INDEXDEFRAG does not. If this is the case, does MS mean that only the affected statistics are updated or all statistics? Also, is it a good idea to run 'Update Statistics' after doing INDEXDEFRAG?
I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1Syntax error converting date time from character string". can someone please help.SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime), CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime)) AS StopMinutes, BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA, BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME, OQTCOD, BAADES, PCS, WGT, Tractor, Driver FROM dbo.JCI_Delivery_Report
Hi, i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs. so i want to get the difference of start and end datetime=Diag_Hrs. here i am using the below stored proc. but i am getting only the hours or minutes or seconds. so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds. for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs. so how we'll get this. pl help me out asap. Thanx reddy
select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table --where Gpc_no=@GPC_no
--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table -- where Gpc_no=@GPC_no
select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table where Gpc_no=@GPC_no
I have 2 databases: 1 from production and 1 from development. None of the developers kept a changelog so i need to know what has changed (or what is different between the 2).
Hey guys, m new to SQL i mean really new so i appreciate all the help i can get on this as soon as possible. Is it possible to extract 3 foreign keys from a single table into one record/row on a different problem ? Thnx guys hope to hear soon
I wanted to know the difference between SQL Server 2005 ( RTM) Version and the regular SQL Server 2005 SP2. And does the RTM version have any service packs.
Are there any tools/utilties that can diff 2 sql server databases. I know the DB Pro Edition of VSTS can do it, but its hard to justify a jump from my $800 copy of VS2005 Pro to a $5k-6k version of VS just to get database diff. The tablediff looks promising but I need it to do SPROCS and SFUNCS too. Any helps or recommendations would be appreciated.
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Hi All, What is the difference betwen real and float datatype of SQL server. Please one exapmle for each. The real datatype is like a float except that real can only store numbers.What does it mean: float can store even characters. Thanks Abdul
When I execute the following sql statement in SQL 2000 DTS or query analyzer, I received the following error message. I used [ ] because my field names have spaces in between.
The same sql statement ran perfect in SQL 7. What could be wrong? Please advise. Thanks a million.
Update SAPvsSQL set [Sales Organisation] = sales_org, [Value Out By] = val_diff, [Qty Out By] = qty_diff
Error message:
Server: Msg 16882, Level 11, State 1, Procedure sp_runwebtask, Line ....
SQL Web Assistant: Web task not found. Verify the name of the name for possible errors.
Hi, I need to know the difference between Numeric and Decimal datatypes. In which we case we use numeric and in which case we use decimal? I searched in some sql related websites and came to know that both are same. Then what is the need of these two datatypes? Either Numeric or Decimal is alone enough? Please explain me in detail .... Awaiting your replies...
