?
Solved

SQL Database Backup Error - VERY STRANGE

Posted on 2005-03-05
76
Medium Priority
?
20,232 Views
Last Modified: 2011-09-05
Running MS Small Bus. Svr with SQL 2k, both with latest non-beta svc packs.  Have about 8 databases.  ALL show status as NORMAL.

ONE of the database will not complete a good backup - all others will.

Have tried numerous times (with Ent. Manager) to backup this database & verify the backup.  I get the Backup Progress dialog but the blue PROGRESS BAR NEVER APPEARS.  Dialog just sits there for about 20 seconds (which is not long enough to backup this database - it's about 2 gb), then it SAYS "Backup Completed Successfully".  Then this error dialog appears:

Title bar of error dialog: "Miscrosft SQL-DMO (ODBC SQL State:42000)"

and this text in the detail box:

"Cannot open backup device (filename of my file).  Device error or device off-line.  See the SQL error log for more details.  Verify database is terminating abnormally."

Here are the errors in the SQL error log:

"BACKUP failed to complete the command BACKUP DATABASE [GCF] TO  DISK = N'D:\!Backups\SQL Backups\Manual\Databases\GCF' WITH  NOINIT ,  NOUNLOAD ,  NAME = N'GCF backup',  NOSKIP ,  STATS = 10,  NOFORMAT"

and.....

"BackupDiskFile::OpenMedia: Backup device 'D:\!Backups\SQL Backups\Manual\Databases\GCF' failed to open. Operating system error = 2(The system cannot find the file specified.)."


It DOES create a file in the folder I specified, but it shows 0 kb (in Windows Explorer).  I can right-click the file, choose Properties, & at first the properties window shows it has a size of 1.01 gb.  I close the properties window, REFRESH the explorer window multiple times, it still shows filesize of "0k".  I can go back to Explorer a minute or so later, & it NOW shows a filesize of 2.1 gb.  I RClick the file & the NOW Properties window shows size now of 2.6 gb.

I went to Query Analyzer & did a manual backup (BACKUP DATABASE GCF TO DISK = 'c:\GCF.bak'), and got this result:

***************
Processed 270672 pages for database 'GCF', file 'GPSGCFDat.mdf' on file 1.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 9 pages for database 'GCF', file 'GPSGCFLog.ldf' on file 1.

Connection Broken
***************

I have backed this database up MANY times before with no problems at all, both manually & with Maint. Plan (which is failing now).

Pease help!!!
0
Comment
Question by:cricketman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 25
  • 25
  • 11
  • +4
76 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13467749
0
 
LVL 34

Expert Comment

by:arbert
ID: 13469088
Agree, run a checkdb....Make sure you put the database in single user mode first--even though you can run CHECKDB in multiuser mode, you can get some strange errors.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cricketman
ID: 13470395
Ran CHECKDB before posting & it said database was fine - no errors.  I will run again & post result.

EugeneZ - if I uninstall SQL server & reinstall as per the lionk you sent me to, will my databases be wiped out in the process?  I don't have a good backup of this particular one.

Disk space is not an issue - almost 30 gig free.

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13470951
It must be ok  but to be sure:
run select * from sysfiles
--to see where are data and log files - for each user db
then stop MSSql server service
and copy to safe locatons (e.gnew folder -all the databases files)
0
 
LVL 8

Expert Comment

by:Julianva
ID: 13474648
When SQL returns a SQLstate  of 42000 this is a syntax error or an access violation.  An ODBC API error

Please check if you have appropriate permissions to backup this database.


NOINIT - Please check

If the volume contains a valid media header, verify the media password* and append the backup set, preserving all existing backup sets.

If the volume does not contain a valid media header, an error occurs.
0
 

Author Comment

by:cricketman
ID: 13479976
It's MONDAY... which means I haven't been able to do very much with this issue as users are busy working away in the database in question, but I am still having the problem and need help!

I did manage to run DBCC again (could not put the database in single user mode however since users are working in it).  Got LOTS of result lines that all look OK (nothing says ERROR).  Here are the bottom few lines from the query result of the DBCC CHECKDB run:

*******************************************
DBCC results for 'GL00105'.
There are 286 rows in 9 pages for object 'GL00105'.
DBCC results for 'eExpUVH'.
There are 0 rows in 1 pages for object 'eExpUVH'.
DBCC results for 'UPR40301'.
There are 7 rows in 1 pages for object 'UPR40301'.
DBCC results for 'IV40100'.
There are 1 rows in 1 pages for object 'IV40100'.
DBCC results for 'GL00200'.
There are 0 rows in 1 pages for object 'GL00200'.
DBCC results for 'RM50104'.
There are 0 rows in 0 pages for object 'RM50104'.
DBCC results for 'SY02300'.
There are 44 rows in 1 pages for object 'SY02300'.
DBCC results for 'RM30401'.
There are 0 rows in 0 pages for object 'RM30401'.
DBCC results for 'SOP10105'.
There are 1474 rows in 107 pages for object 'SOP10105'.
DBCC results for 'UPR00500'.
There are 113 rows in 5 pages for object 'UPR00500'.
DBCC results for 'UPR00113'.
There are 0 rows in 0 pages for object 'UPR00113'.
DBCC results for 'UPR40500'.
There are 107 rows in 1 pages for object 'UPR40500'.
DBCC results for 'UPR10208'.
There are 0 rows in 1 pages for object 'UPR10208'.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'GCF'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*******************************************

There were many more result lines for tables but I'm guessing yoou don't want me to post them all.

Other things that may be noteworthy:
- the T Log file had gotten very big (7 gig, and the database is only about 2.5 gig) so I was able to successfully shrink it yesterday, now it shows size of 8,880 kb

- I just now successfully SHRUNK the database (using Ent Mgr)

- logging in to W2k Server as "Administrator" and into SQL Server 2k as "sa" so should have full rights to everything.  Checked to folder the backup is aimed at just to be sure & YES Administrator does have Full Control to it.  The user "sa" is not listed at all but it's actually "system" that's running SQL so that's OK, right?  This is the way it's always been as far as I know.

- PROPERTIES of the database in question:
--- Status: Normal
--- Owner: sa
--- Size: 2588 mb
--- # of users: 13
--- Last DB backup: 3/2/05
--- Last Trans Log backup: 3/7/05 (this was a scheduled backup maintainence plan)
--- Collation name: SQL_Latin1_General_CP1_CI_AS
--- Data file & T Log both set to Automatically grow file, Unrestricted Growth
--- Recovery Model: FULL RECOVERY

SQL Server Properties
- Dynamically COnfigure Memeor (Min 256, Max 384 (machine has 512))

- SQL Server & Windows Authentication

- SQL Server starts with the SYSTEM account
 
What about Named Pipes vs TCP/IP connection to the database?  It is currently set to have BOTH.  Did I read somewhere that Named Pipes are not good??

No users report any problems.  Great Plains accounting is the application that we are running that uses this database - everything is working fine, it just will not backup properly!

When I try to B/U the database in Ent Mgr, I never get a blue progress bar in the backup progress dialog, and after about 10 seconds the dialog box goes away & it says "Backup completed successfully".  But I know this database should take at least a minute to 90 seconds to actually backup.  After this process I check the file generated in the backup location, and it HAS changed in size (gotten larger - the backup was set to "append to media").  But database properties in Ent Manager still show last backup on 3/2/05.

The last backup listed on 3/2 was made after I re-installed SQL service pack 3 (which I had seen in a PAQ as a possible solution to this problem).  And at that time it DID solve the problem.  I was able to do a good backup of the database on 3/2.  BUT... due to another issue (Active DIrectory somehow got scrambled) I had to Restore System State from a tape backup.  It solved the AD issue, but brought back the database backup issue.  No problem I thought, I'll just re-install SQL sp3 again & it'll fix it.  Well - didn't work.  Installed SQL sp3 Sat afternoon, restarted couple of times since then, & the database no-backup issue remains.

AAARRRGGGHHHH!!!!!!!
0
 

Author Comment

by:cricketman
ID: 13485579
OK all you SQL gurus - this afternoon I will have an opportunity to work on this problem again, so would really appreciate any advice that comes forth today!!!

Who wants these 500 points?!

Thanks to all....

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13486039
>Recovery Model: FULL RECOVERY
do you have Trans log backup?
--------------------
did you get same error?
Did you See the SQL error log for more details.
----------------------------------

>But database properties in Ent Manager still show last backup on 3/2/05.
did you refresh view of EM?
BTW: you can query msdb tables like backupset ...
---------------------------
did you see c:\winnt\sqlsp.log to be sure that SQL sp was installed succesfully (I hopy you restarted server after installation)?

--------------------------
Try to install last MDAC
--------------------------
What can you see in event viewer System and apps?
0
 

Author Comment

by:cricketman
ID: 13486814
EugeneZ - thanks for the quick response!

YES, there ARE transaction log backups.  I have a maintainence plan in place that's supposed to BU the Tlogs every 2 hours which it DOES appear to be doing.  Have them going back to 3/3/05.  Ones for 3/3 & 3/4 are BIG files (170 mb on 3/3 & 240 mb on 3/4).  Then, on 3/5 I truncated the log file.  Ones after that started off very small but grew quickly.  On 3/7 they were as follows (every 2 hours): 143 kb, 7 mb, 8.5 mb, 10.5 mb, and 17 mb at 5:00p that afternoon.  Monday is a busy day with lots of transactions being entered, so that makes sense to me.

SQL error log this morning shows that 2 of the 7 databases on the Maint Plan did not backup properly - the other 5 did BU fine.  DBCC was run on ALL 7 before the BU process began, and all 7 say "DBCC CHECKDB (database name) executed by sa found 0 errors and repaired 0 errors.


Here are the errors from the SQL Error Log in the order they occurred:
*********
BACKUP failed to complete the command BACKUP DATABASE [G2000] TO  DISK = N'D:\!Backups\SQL Backups\Scheduled\Databases\G2000\G2000_db_200503080103.BAK' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT

*********
BackupDiskFile::OpenMedia: Backup device 'D:\!Backups\SQL Backups\Scheduled\Databases\G2000\G2000_db_200503080103.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).

*********
BackupDiskFile::OpenMedia: Backup device 'D:\!Backups\SQL Backups\Scheduled\Databases\GCF\GCF_db_200503080103.BAK' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).

*********
BACKUP failed to complete the command BACKUP DATABASE [GCF] TO  DISK = N'D:\!Backups\SQL Backups\Scheduled\Databases\GCF\GCF_db_200503080103.BAK' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT
***************************************************

Then these two more from the SQL Log this morning:
*********
BACKUP failed to complete the command BACKUP LOG [GCF] TO  DISK = N'D:\!Backups\SQL Backups\Scheduled\Log Files\GCF\GCF_tlog_200503080903.TRN' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT

*********
BackupDiskFile::OpenMedia: Backup device 'D:\!Backups\SQL Backups\Scheduled\Log Files\GCF\GCF_tlog_200503080903.TRN' failed to open. Operating system error = 2(The system cannot find the file specified.).

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> did you refresh view of EM?
YES.

>BTW: you can query msdb tables like backupset ...
Not sure what you mean or how to do that....

> did you see c:\winnt\sqlsp.log to be sure that SQL sp was installed succesfully (I hopy you restarted server after installation)?
YES, I definitely did re-start the server after sp3 install.
The sqlsp.log file did show this at the very end: "Installation Completed with Errors (number of nonfatal errors: 1)".. Can't really determine what the error was.  No errors displayed on the screen during the install process..... and a search of the sp.log file for the word "error" shows only these 2 lines:

12:07:27 Begin : SdErrorReportingDlg
12:07:30 End: SdErrorReportingDlg

The stuff just prior to that does not appear to have any errors.  The file is long so I doubt if you want it posted - want me to email it to you?


>Try to install last MDAC
It looks like it installed as part of the sp3 install:

12:07:54 Setup is installing Microsoft Data Access Components (MDAC) ...
12:07:54 C:\sql2ksp3\x86\Other\sqlredis.exe /q:a /C:"setupre.exe MDACQFE=0 WARN=1 -s -SMS"
12:08:08 ExitCode: 0
12:08:08 End:  InstallPkgs(ID_INSTALLMDAC)



> What can you see in event viewer System and apps?
System log is clean, there ARE some SQL related errors in APP log (occurred in this order):

THESE at 1:03 last night (Maint Plan scheduled run which included BU of DB & TLogs):
***********************************************
Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (2)
Event ID:      17055
Date:            3/8/2005
Time:            1:03:20 AM
User:            N/A
Computer:      CRICKET
Description:
18204 :
BackupDiskFile::OpenMedia: Backup device 'D:\!Backups\SQL Backups\Scheduled\Databases\G2000\G2000_db_200503080103.BAK' failed to open. Operating system error = 2(The system cannot find the file specified.).
 
Data:
0000: 1c 47 00 00 10 00 00 00   .G......
0008: 08 00 00 00 43 00 52 00   ....C.R.
0010: 49 00 43 00 4b 00 45 00   I.C.K.E.
0018: 54 00 00 00 00 00 00 00   T.......

************
Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (6)
Event ID:      17055
Date:            3/8/2005
Time:            1:03:20 AM
User:            N/A
Computer:      CRICKET
Description:
3041 :
BACKUP failed to complete the command BACKUP DATABASE [G2000] TO  DISK = N'D:\!Backups\SQL Backups\Scheduled\Databases\G2000\G2000_db_200503080103.BAK' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT
 
Data:
0000: e1 0b 00 00 10 00 00 00   á.......
0008: 08 00 00 00 43 00 52 00   ....C.R.
0010: 49 00 43 00 4b 00 45 00   I.C.K.E.
0018: 54 00 00 00 00 00 00 00   T.......

************
Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (2)
Event ID:      17055
Date:            3/8/2005
Time:            1:03:31 AM
User:            N/A
Computer:      CRICKET
Description:
18204 :
BackupDiskFile::OpenMedia: Backup device 'D:\!Backups\SQL Backups\Scheduled\Databases\GCF\GCF_db_200503080103.BAK' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).
 
Data:
0000: 1c 47 00 00 10 00 00 00   .G......
0008: 08 00 00 00 43 00 52 00   ....C.R.
0010: 49 00 43 00 4b 00 45 00   I.C.K.E.
0018: 54 00 00 00 00 00 00 00   T.......

************
Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (6)
Event ID:      17055
Date:            3/8/2005
Time:            1:03:31 AM
User:            N/A
Computer:      CRICKET
Description:
3041 :
BACKUP failed to complete the command BACKUP DATABASE [GCF] TO  DISK = N'D:\!Backups\SQL Backups\Scheduled\Databases\GCF\GCF_db_200503080103.BAK' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT
 
Data:
0000: e1 0b 00 00 10 00 00 00   á.......
0008: 08 00 00 00 43 00 52 00   ....C.R.
0010: 49 00 43 00 4b 00 45 00   I.C.K.E.
0018: 54 00 00 00 00 00 00 00   T.......
***********************************************



Then THESE this morning (Maint Plan scheduled run which included BU of TLogs ONLY):

************
Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (6)
Event ID:      17055
Date:            3/8/2005
Time:            9:03:37 AM
User:            N/A
Computer:      CRICKET
Description:
3041 :
BACKUP failed to complete the command BACKUP LOG [GCF] TO  DISK = N'D:\!Backups\SQL Backups\Scheduled\Log Files\GCF\GCF_tlog_200503080903.TRN' WITH  INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT
 
Data:
0000: e1 0b 00 00 10 00 00 00   á.......
0008: 08 00 00 00 43 00 52 00   ....C.R.
0010: 49 00 43 00 4b 00 45 00   I.C.K.E.
0018: 54 00 00 00 00 00 00 00   T.......

************
Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (2)
Event ID:      17055
Date:            3/8/2005
Time:            9:03:37 AM
User:            N/A
Computer:      CRICKET
Description:
18204 :
BackupDiskFile::OpenMedia: Backup device 'D:\!Backups\SQL Backups\Scheduled\Log Files\GCF\GCF_tlog_200503080903.TRN' failed to open. Operating system error = 2(The system cannot find the file specified.).
 
Data:
0000: 1c 47 00 00 10 00 00 00   .G......
0008: 08 00 00 00 43 00 52 00   ....C.R.
0010: 49 00 43 00 4b 00 45 00   I.C.K.E.
0018: 54 00 00 00 00 00 00 00   T.......

************
Event Type:      Warning
Event Source:      SQLSERVERAGENT
Event Category:      Job Engine
Event ID:      208
Date:            3/8/2005
Time:            9:03:38 AM
User:            N/A
Computer:      CRICKET
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'' (0x12C2231857E5AA45A816135024699511) - Status: Failed - Invoked on: 2005-03-08 09:00:00 - Message: The job failed.  The Job was invoked by Schedule 14 (Schedule 1).  The last step to run was step 1 (Step 1).
***********************************************


Here is the log generated by the Maintainence Plan last night:
******************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'CRICKET' as 'sa' (non-trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 3/8/2005 1:00:07 AM
[1] Database CGH: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 7 secs **

[2] Database DYNAMICS: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 3 secs **

[3] Database G2000: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 9 secs **

[4] Database GCF: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 2 mins, 22 secs **

[5] Database GCFPR: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 8 secs **

[6] Database GLC: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 7 secs **

[7] Database IXMetaGP: Check Data and Index Linkage...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[8] Database CGH: Database Backup...
    Destination: [D:\!Backups\SQL Backups\Scheduled\Databases\CGH\CGH_db_200503080103.BAK]
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 4035: [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
[Microsoft][ODBC SQL Server Driver][Shared Memory]General network error. Check your network documentation.
[Microsoft][ODBC SQL Server Driver][SQL Server]Processed 2 pages for database 'CGH', file 'GPSCGHLog.ldf' on file 1.
[9] Database DYNAMICS: Database Backup...
    Destination: [D:\!Backups\SQL Backups\Scheduled\Databases\DYNAMICS\DYNAMICS_db_200503080103.BAK]

    ** Execution Time: 0 hrs, 0 mins, 3 secs **

[10] Database DYNAMICS: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[11] Database G2000: Database Backup...
    Destination: [D:\!Backups\SQL Backups\Scheduled\Databases\G2000\G2000_db_200503080103.BAK]

    ** Execution Time: 0 hrs, 0 mins, 5 secs **

[12] Database G2000: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'D:\!Backups\SQL Backups\Scheduled\Databases\G2000\G2000_db_200503080103.BAK'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally.
[13] Database GCF: Database Backup...
    Destination: [D:\!Backups\SQL Backups\Scheduled\Databases\GCF\GCF_db_200503080103.BAK]

    ** Execution Time: 0 hrs, 0 mins, 11 secs **

[14] Database GCF: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'D:\!Backups\SQL Backups\Scheduled\Databases\GCF\GCF_db_200503080103.BAK'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally.
[15] Database GCFPR: Database Backup...
    Destination: [D:\!Backups\SQL Backups\Scheduled\Databases\GCFPR\GCFPR_db_200503080103.BAK]

    ** Execution Time: 0 hrs, 0 mins, 5 secs **

[16] Database GCFPR: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 2 secs **

[17] Database GLC: Database Backup...
    Destination: [D:\!Backups\SQL Backups\Scheduled\Databases\GLC\GLC_db_200503080103.BAK]

    ** Execution Time: 0 hrs, 0 mins, 4 secs **

[18] Database GLC: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 2 secs **

[19] Database IXMetaGP: Database Backup...
    Destination: [D:\!Backups\SQL Backups\Scheduled\Databases\IXMetaGP\IXMetaGP_db_200503080103.BAK]

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[20] Database IXMetaGP: Verifying Backup...

    ** Execution Time: 0 hrs, 0 mins, 1 secs **

Deleting old text reports...    1 file(s) deleted.

End of maintenance plan 'DB Maintenance Plan1' on 3/8/2005 1:03:46 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
******************************************

WHEW! Hope this helps, and again, THANKS for your attention to my question!!

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13487366
ok


 try to create new maintainence plan just for the Database and create for testing new folder
e.g. D:\Backups and point to this new folder
 post result
-------------------------
if there were error during SQL Sp installation - it is not good
Try again.
lates MDAC 2.8 was Published:
 8/4/2003 after sp3a
---------------------
0
 

Author Comment

by:cricketman
ID: 13487703
Have already tried delete & recreate maint plan but it was for all DB's & TL's - will do again just for this one DB & NO TLog (I think that's what you mean).

I will install sp3a again this afternoon when I can get all users out, and will get the newer MDAC & install after that.

What about having both TCP/IP & Named Pipes active for data connections - is this OK or should I have only 1 or the other?

More to come later this evening.

Again... many thanks.

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13487944
it is OK
0
 
LVL 34

Expert Comment

by:arbert
ID: 13491061
It's interesting the errors you posted above--one says the operating system cannot find the file/path and the other errors says it's in use.....Are you running any Antivirus software on this server?

"I had to Restore System State from a tape backup.  It solved the AD issue, but brought back the database backup issue.  No problem I thought, I'll just re-install SQL sp3 again & it'll fix it.  Well - didn't work.  Installed SQL sp3 Sat afternoon, restarted couple of times since then, & the database no-backup issue remains."

Ouch--what backup software are you running--system state can be a tricky thing to backup and restore....
0
 

Author Comment

by:cricketman
ID: 13491604
Arbert - YES... running Symantec AV Corp Edition v 9.0.  Think that could be part of the issue?  Why would it only affect that one DB?  Or, it was actually 2 DB's that gave errors this last time I think.  Think I'll stop the AV service & see what happens.

>Ouch--what backup software are you running--system state can be a tricky thing to backup and restore....

Tapeware.  Was a piece of cake to restore.  Booted into "Recovery Mode" (I think that's what SBS2k called it), stuck in a tape, chose the proper items, rebooted again & voila - smooth as silk.

Good thing I didn't know in advance that it was tricky or I'd probably have screwed it up!

Just about to go back to work on this prob (within the next hour or so).....

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13491762
did you try another backup folder from new M plan?
0
 
LVL 34

Expert Comment

by:arbert
ID: 13491890
You should never run AV on a database server!  Even if you exclude file extensions, it's not a good idea.....
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13492058
>You should never run AV on a database server!
arbert:
where from did you get such recommendation?

Usually exlude SQL server database files from AV scan is working fine..
0
 
LVL 34

Expert Comment

by:arbert
ID: 13492258
"where from did you get such recommendation?"

Surely in your years as a MCDBA, you've seen the recomendation from microsoft (as well as many others).    The stance that MS takes now isn't as strong as it use to be (basically they tell you to decide--they use to simply say DON'T DO IT).  Here are a couple of good references:    http://www.microsoft.com/technet/security/topics/serversecurity/avdind_3.mspx#EEAA    and  http://support.microsoft.com/?kbid=309422



Excluding the extension is great, but what happens when someone creates a DBfile with a non-standard extension?  It's no longer excluded....Even with the locking issue aside, it creates a huge bottleneck on the server.    There usually isn't an issue once the DB starts up, but before the DB opens, the AV can get a lock on the file and force it into suspect mode....

Brett
0
 

Author Comment

by:cricketman
ID: 13492845
OK... here's where I am now (besides TIRED!):

- deleted my Maintainence Plan

- uninstalled Symantec AV from the server & rebooted.  Upon shutdown, got a dialog box that said there was a Terminal
Server User still logged in to this machine.  I DO have TS running to give a programmer in another city access, but I don't think he was in at the time.  Checked in TS Manager & it only showed ADMINISTRATOR as being "active" - & I am the one who was logged in as Admin.  Is this relevant?

- deleted all old backup files for this DB

- created new folder for new test BU

- tried to manually BU thru Ent Mgr (w/o TLog - just the DB, & w/o Verify) - no dice.  Backup dialog open for 10 seconds with NO progress bar, then it goes away & says backup complete.  I go to the file location, there's a file with "0 kb" as the file size.  I refresh - file size goes to about 200 mb.  Refresh again (several times over 30 seconds) & file size climbs to about 700 mb, then finally 1.03 GB (too small for the entire DB I think).

- set the DB to single user mode

- ran dbcc checkdb - no errors at all.

- went to the folder where the BU file was located... and the 1.03 GB file it created is GONE!  Just not there at all!!!!

- manually BU the DB again as before (w/o TLog - just the DB, & w/o Verify)) - same results.  Backup dialog open for 9 seconds (I counted), NO progress bar, says backup complete.  I go to the file location, there's a file with "0 kb" as before.  I refresh several times over 10 seconds, size display never changes. I flop back over here to write some more notes, then go back to look, now file size is 2.2 GB which seems about right.

AM I LOSING MY MIND!  YES I AM!

Could it be there's some sort of extreme disc caching going on that backs this DB up in 9 seconds and it takes Windows a couple minutes to realize what;s going on???  Disk system is a RAID-5 array if that matters.

It has never been this way before.  I have BU this thing 100 times & I always watched the progress bar move along as normal, & it took about 60 seconds or more for it to complete (plus time to verify).

- tried AGAIN (I AM persistent) - this time chose same (now existing) file location, told it to APPEND & Verify.  BU dialog open same 9 seconds, no progress bar, Verify dialog open for 45 seconds, says BU & Verification completed successfully.  Went to the file location, it's same size as it was before that BU (2.1 gb).

Went to Restore DB (in ENt Mgr) to see what the BU file really has in it.  It shows only ONE BU - the 1st one I did - NOT the 2nd one that I told to append to the existing file.

I am at my wits end.  Just about ready to uninstall SQL Server & start over!!!!!

ANY MORE IDEAS!?!???
0
 
LVL 34

Expert Comment

by:arbert
ID: 13493031
Is D: a local drive?  Above you said you tried to "manually" run the backup, but then you said the dialog box came up.  So, I assume you're "manually" running it from enterprise manager.  What happens if you try the backup from Query Analyzer--try several different tests:

backup database gcf to disk=c:\test.bak

backup databse gcf to disk=d:\test.bak
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13495201
arbert:
read carefully my post without turn on personal level as you like to do on the forum
when you are not right, then calm down and post here if you disagree-
Otherwise ignore my post.

Understood? It is just friendly talk no more no less …
BTW: still have problems to pass MSDBA exams?  
Maybe next time you’ll be lucky and of course: learn hard all your free time
----------------------------------------------------
Now relax and read:
--------------
did you read the Microsoft articles that you posted?
if so - the Microsoft did not say anything like 'DO NOT USE AV ON SQL SERVERs'

it is your interpretation that  readers must to uninstall AV from production SQL SERVERS!!!!
In the articles told - about right 'Antivirus Configurations' nothing about do not use.
Be careful in your wishes and online suggestions
The Microsoft recommends to use AV for servers on server not AV for PC on servers!!
 See recommended AV by the MICROSOFT:

Arbert you are wrong to give such  recommendations!
In real life - during testing or troubleshooting – administrator can stop or temporary disable AV but uninstall !!
Only 1 problem can be –
wrong selection of an AV SW for specific system ,
not proper configuration and usage!!


http://www.microsoft.com/windows/catalog/server/default.aspx?subID=22&xslt=search&pgn=223dbfef-1a4d-418e-b6b5-a72e0c3ab915&maxrows=0&sortcol=win2003&sortdir=descending&qu=antivirus&scope=1

This article provides general guidelines to help you decide which type of antivirus software to run on the computers that are running SQL Server in your environment.
Microsoft strongly recommends that you individually assess the security risk for each computer that is running SQL Server in your environment and that you select the tools that are appropriate for the security risk level of each computer that is running SQL Server. Additionally, Microsoft recommends that before you roll out any virus protection project, test the whole system under a full load to measure any changes to stability and performance.

Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine if there is performance impact to the computer that is running SQL Server.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13495272
My recommendation never name users stored procedures with prefix sp - to avoid confusion
it is system sp prefix -  instead, for example, use usp (like User Stored Procedures)
similar for other system objects (like systables – e.g. sysobject – try to avoid to name like sysappsusers, etc..)
During DRP you can forget about such objects…
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13495779
igore last post - wrong place
0
 

Author Comment

by:cricketman
ID: 13497207
Yes, D is a local drive.

Yes, when I say "manually" I meant NOT thru a Main. Plan - I was still doing it in EM by right-clicking on the DB > All Tasks > Backup DB.

Just tried using QA as last suggested (to different local drive), by executing:

backup database gcf to disk = 'c:\test.bak'

after about 2 mins got this error:

*********************************
Processed 276632 pages for database 'gcf', file 'GPSGCFDat.mdf' on file 1.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 2 pages for database 'gcf', file 'GPSGCFLog.ldf' on file 1.

Connection Broken
*********************************

Other ideas?

Again, THANKS to all who are helping.

I AIN'T GONNA LET THIS BEAT ME!!!!!!!!!!

cag

0
 

Author Comment

by:cricketman
ID: 13497860
Also just tried backing up SEVERAL other DB's in the same way qith QA (backup database cgh to disk = 'c:\cgh.bak').

SAME ERROR on ALL of them:

*******************************
Processed 9984 pages for database 'cgh', file 'GPSCGHDat.mdf' on file 1.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 1 pages for database 'cgh', file 'GPSCGHLog.ldf' on file 1.

Connection Broken
*******************************

In a PAQ I found suggestions that this error could possibly be caused by a RAID controller trying to go bad.  I'm now downloading a RAID diagnostic & Array Manager tool from Dell.com (my system is a Dell PowerEdge 2400, with a PERC RAID Controller).  Will experiment with that after lunch.

Going to lunch now.  May eat some arsenic.......

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13498653
reboot server if you can
see Event viewer errors  (system apps).

try to run from QA exec master..xp_cmdshell 'dir D:\!Backups\SQL Backups\Scheduled\Databases\GCF\*'
post results
0
 

Author Comment

by:cricketman
ID: 13499325
OK....

- rebooted server
- in the SUSTEM LOG - three errors that (I think) are totally unrelated to this issue (they are due to a botched install of MS Exchange which I removed but it apparently left a few service calls). Here they are anyway:

#1.........................
Event Type:      Error
Event Source:      Server
Event Category:      None
Event ID:      2511
Date:            3/9/2005
Time:            1:38:05 PM
User:            N/A
Computer:      CRICKET
Description:
The server service was unable to recreate the share Address because the directory C:\Program Files\Exchsrvr\address no longer exists.

#2.........................
Event Type:      Error
Event Source:      Server
Event Category:      None
Event ID:      2511
Date:            3/9/2005
Time:            1:38:05 PM
User:            N/A
Computer:      CRICKET
Description:
The server service was unable to recreate the share Resources$ because the directory C:\Program Files\Exchsrvr\res no longer exists.

#3.........................
Event Type:      Error
Event Source:      Server
Event Category:      None
Event ID:      2511
Date:            3/9/2005
Time:            1:38:05 PM
User:            N/A
Computer:      CRICKET
Description:
The server service was unable to recreate the share CRICKET.log because the directory C:\Program Files\Exchsrvr\CRICKET.log no longer exists.
.......................................................................

The warning (occur after the errors above).....................................
Event Type:      Warning
Event Source:      RemoteAccess
Event Category:      None
Event ID:      20192
Date:            3/9/2005
Time:            1:39:31 PM
User:            N/A
Computer:      CRICKET
Description:
A certificate could not be found. Connections that use the L2TP protocol over IPSec  require the installation of a machine certificate, also known as a computer  certificate. No L2TP calls will be accepted.


- in the APP LOG - one error due to last night's uninstall of Sym,antec AV Server.  The Quarantine piece is still installed (although I stopped ALL Symantec services last night when attempting BU's).  Think I'll also go ahead & uninstall ALL Symantec related programs now.  Here's the error:

Event Type:      Error
Event Source:      Symantec Quarantine Agent
Event Category:      (257)
Event ID:      6
Date:            3/9/2005
Time:            1:44:18 PM
User:            N/A
Computer:      CRICKET
Description:
Exception caught in JobPackager constructor [Exception of type string exception caught in JobPackager constructor [IcePack timing out after DefCast initialization failure]]. IcePack shutting down
*************************************************************

Ran the query you posted, results:

The system cannot find the path specified.
NULL


The directory DOES exist, & has a DB backup in it -  I can browse to it in Explorer.

Also ran "exec master..xp_cmdshell 'dir D:\!Backups\SQL Backups\Manual\Databases\*'" and got same result - cannot find it.  Again, the folder does exist & has numerous BU files in it.

cag

Thanks for sticking this out with me........
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13499634
what about just ?
exec master..xp_cmdshell 'dir D:\*'
0
 

Author Comment

by:cricketman
ID: 13500610
>>what about just ?
exec master..xp_cmdshell 'dir D:\*'

Gets this:

 Volume in drive D is D: (Programs and Data)
 Volume Serial Number is D4D9-F43B
NULL
 Directory of D:\
NULL
11/24/2004  02:43p      <DIR>          !Backups
03/09/2005  02:42p      <DIR>          Ghann Data
02/12/2005  11:19a      <DIR>          Ghann Programs
12/03/2003  03:27a      <DIR>          Ghann User Data
11/01/2004  09:13a      <DIR>          Temp
03/04/2004  05:10p      <DIR>          WUTemp
               0 File(s)              0 bytes
               6 Dir(s)  27,006,242,816 bytes free
NULL
0
 

Author Comment

by:cricketman
ID: 13500782
I tried backing off that query one folder at a time.  Gets the error that it can't find it until I get back to just the Backups folder:

NOTE - Just before running this I quadruple-checked share-point permissions & file/folder permissions on the entire D:\Backups folder & everything beneath it.  I RESET all permissions to EVERYONE having FULL CONTROL and made sure it was set to replace permissions on all child objects as well.

THEN..... running this> exec master..xp_cmdshell 'dir D:\!Backups\*'

gets this:

 Volume in drive D is D: (Programs and Data)
 Volume Serial Number is D4D9-F43B
NULL
 Directory of D:\!Backups
NULL
11/24/2004  02:43p      <DIR>          .
11/24/2004  02:43p      <DIR>          ..
12/02/2003  10:55p       3,320,392,704 03dec03.bkf
11/24/2004  02:27p      <DIR>          Bill
11/24/2004  02:44p      <DIR>          CAG
11/24/2004  02:41p      <DIR>          CBG
12/13/2003  03:04p      <DIR>          Downloads
12/13/2003  02:56p      <DIR>          FGP
12/13/2003  03:01p      <DIR>          FOXPRO2
11/24/2004  02:44p      <DIR>          Images
11/24/2004  02:46p      <DIR>          MISC
12/13/2003  02:58p      <DIR>          Old Databases
12/13/2003  02:58p      <DIR>          SBT
12/13/2003  02:56p      <DIR>          Spreadsheets
03/08/2005  05:03p      <DIR>          SQL Backups
08/03/2004  12:53p      <DIR>          Workstation Registry Files
12/13/2003  02:56p      <DIR>          XLR Reports
               1 File(s)  3,320,392,704 bytes
              16 Dir(s)  27,006,304,256 bytes free
NULL
****************************

It shows the folder "SQL Backups" in the results, but if I add that part of the path to the query line it can't see it.


cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13501121
what about to ttry to backup to Backups fldr?
0
 

Author Comment

by:cricketman
ID: 13501173
Same thing - 10 seconds (way too fast), no progress bar, creates 0k file that finally "grows" only after r-clicking & viewing Properties, then hitting F5 to refresh.

What about any disc caching, or delayed writes???
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13501179
all it sounds like Registry problems ...
-----------------------------
I'd ran AV at first - to be sure ...
-----------
If nothing works:
Try to share the folder and point  to \\server\share ...
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13501194
but did you get good backups in the Backups folder or not?
Did you try to restore (another name ) for test?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13501218
try
exec master..xp_cmdshell 'dir "D:\!Backups\allyou Backup path"' -- put path in double qts"
0
 

Author Comment

by:cricketman
ID: 13501393
AHHH.... putting the path in dbl quotes DID get QA to see the folder:

exec master..xp_cmdshell 'dir "D:\!Backups\SQL Backups\Scheduled\Databases\GCF\*"'

yields this:

 Volume in drive D is D: (Programs and Data)
 Volume Serial Number is D4D9-F43B
NULL
 Directory of D:\!Backups\SQL Backups\Scheduled\Databases\GCF
NULL
03/09/2005  01:54p      <DIR>          .
03/09/2005  01:54p      <DIR>          ..
03/09/2005  01:51p       2,266,733,056 GCF
               1 File(s)  2,266,733,056 bytes
               2 Dir(s)  24,622,219,264 bytes free
NULL
*****************************************************

I will try restoring the BU file to another name to see what happens.  Meanwhile, I am on the phone with Dell getting instructions on how to update firmware for system & RAID controller.

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13501442
good luck
0
 
LVL 34

Expert Comment

by:arbert
ID: 13501846
Eugenez, in response to "did you read the Microsoft articles that you posted?
if so - the Microsoft did not say anything like 'DO NOT USE AV ON SQL SERVERs'
", did you read what I posted along with the links?  Apparently not.  As far as MCDBA, I've had no problems passing exams--you shouldn't take it as a personal attack, simply stating what is usually passed on to us MCDBAs.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13501894
What do you try to say? are you MCDBA? if so - my congrets
BTW: I told you just if you calm down answer  -Otherwise ignore my post.
0
 

Author Comment

by:cricketman
ID: 13502083
OK.....

All system and RAID controller firmware updated.  Also downloaded an MDAC checking tool from MS which indicates I am on MDAC 2.8RTM (which I think is the latest release??).

Have 16 databases total.  Tried backing them up ONE AT A TIME with EM.  All backed up fine except:

1) the DAMN GCF one that started all this crap!  (sorry...)
2) tempdb (EM said I'm not allowed to BU this one)
3) "TEST1" which is a basically copy the GCF database (created it few days ago & restored a BU of the GCF DB onto it).  It has GOT to be something about this GCF DB in particular!  Especially since TEST1 is the samd thing & it failed also.

Next, tried doing a BU of the trans log only of GCF (again thru EM) & it worked fine.  Created a small file of 228kb.  This process should truncate/clear the log file, right??

Then tried the GCF DB again (to same folder I had backed up all others successfully (which is a DIFF folder on a DIFF local drive than I have been trying previously)) - and it did the same junk - 10 seconds (way too fast), no progress bar, creates a file with size = 0 kb that finally "grows" only after either r-clicking & viewing Properties on it then hitting F5 to refresh, OR alt-tab over to a different app & then come back in a minute - then the file says its 2.2GB.

I will try to RESTORE it to a diff location & name now.

cag
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13502090
I agree with arbert here.  You should not be running any anti-virus software on SQL Server (and most servers for that matter).  Besides the antivirus software is not going to be able to scan your database, so it is not only pointless iit is worse than useless.

Having said that, I suspect some hardware problem here.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13502120
Have you considered (if possible):

1. Detach the database.
2. Copy it to another SQL Server.
3. Re-attach to the original server.
4. Attach to the new server.
5. Backup on that new server.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13502138
acperkins:
what about system files, etc.
sql server is not just database files as you know - how so many companies have AV on sql servers that sometimes protect from viruses and big problems - I have seen a lot of fun loves and  another viruses that attacted sql servers
- after server hardering...
As I told - if you do not know how to drive car - do not drive but do not blame for this car manufacture
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13502148
acperkins: in may post above of course is nothing personal
0
 
LVL 34

Expert Comment

by:arbert
ID: 13502159
Ok, for giggles (since I know you're laughing at this point), what happens if you create a DTS package and try to export the objects to another database?  Usually, this will give you an indication if it's structure related or not.  I have seen instances where CHECKDB doesn't report errors and it should...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13502186
EugeneZ,

The correct way to protect SQL Server is by keeping it behind a good firewall.  To continue your bad analogies: Using an anti-virus is like going to bed with a prostitute and thinking you are protected because you are wearing a condom.

>>acperkins: in may post above of course is nothing personal <<
I never take anything you say personal.  In fact, most times I don't even bother to read it.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13502310
acperkins:
thank you very much for reading my post
- it is going on very negative  personal level - that I personally do not like
I do not know about you - but I am here  in my free time for free
to try to help and do not get any of your or arbert negative energy -
I think you went to far ....
-----------------------------------------------------------------------------
Probably we are working in different sql server environments -> in my - servers with installed sql servers have from av protection all what they can protect: AV properly configured and used (e.g. data files excluded)
additional to firewall and all of MS recommendation
---------------------------------------------------------------------------
you can contact some the av companies
McAfee Inc. ,Trend Micro, Inc.
and  tell them what do you think about it
and Microsoft too
- then post here answer and recommendations what to do with 1000th viruses around


or any of the below:

http://www.microsoft.com/security/partners/antivirus.asp 


---------------------------

cricketman:
sorry for our 'friendly' talk with 'co-workers'
Just remember - you are who makes  Decisions!


0
 

Author Comment

by:cricketman
ID: 13502635
"Friendly talk w/co-workers" understood.  HEY... I've been here so long with this issue I kinda feel like I know you guys too!

LATEST ACTIVITY
Tried to restore a BU of the TEST1 DB with Ent Mgr (which again, is a very recent copy of the GCF one causing the probs).  Told it to restore as DB "TRASH1".  It did restore, but Trash1 is hung in "Loading" state.  I even stopped SQL svc & re-started - still says "Loading" after 45 mins.

acperkins - I did the DETACH & re-ATTACH on the TEST1 DB as I'm a little nervous about killing the GCF DB at this point (it's the one that has the live data & it's crictcal to my operation) as you suggested using QA (having read other posts that frown on doing so with EM) .

After detaching, I moved the TLog file to diff folder & re-attached using single file method so would create a new TLog (thinking my TLog may be part of the problem).  It did all this fine, now shows up in EM with a shiny new small TLog file.  Tried to backup with fingers crossed - no dice.  Same issue - 10 seconds (way too fast), no progress bar, creates a file with size = 0 kb that finally "grows" only after either r-clicking & viewing Properties on it then hitting F5 to refresh yadda yadda yadda.....

OK... to help myself & maybe some of you, let me backup (it hurt to even say that word) & review some facts from the beginning:

1) running SQL Server 2000 on MS Small Business Svr 2000, with a RAID 5 array
2) plenty of HD space on both of 2 local drives (C: 16 gb & D: 20 gb)
3) have multiple DB's, all backup fine except 2 of them (GCF & TEST1 which is a restored copy of GCF)
4) they had ALL been backing up fine thru a Maint Plan, but recently started getting errors that the backup file locations couldn't be opened (see early posts in this LONG thread)
5) all DB's will BU fine with Ent Mgr except these two (basically two copies of one) - it's also the biggest one (2.2 gb)
6) installed latest SQL sp3a (several times)
7) updated firmware, BIOS, & RAID drivers on the server
8) all other apps on this server & network are running fine
9) I removed Symantec AV from the server (hope that doesn't start a long digression again)
10) I have truncated the log, shrunk the DB couple times

Racking my brain for other things that have taken place in the last couple weeks, & just now thought of something that else that happened....

I am running Tapeware backup software.  Recently realized it wasn't backing up the SQL DB's b/c it saw them as being "in use" - it required a SQL add-in that wasn't loaded to get at them.  I loaded it.  As part of the setup I had to either 1) create a new instance of SQL server or 2) a new ODBC connection within Tapeware to my existing instance (I'm not sure now - brain is pretty fuzzy from all this).  One thing I KNOW it required was that I connect to my running instance via named pipes (rather than TCP/IP) which I did.

Once configured it properly backed up all DB's to the tape thru it's scheduled plan for a week or so.  THEN, due to tapes being almost slap full (I am overwriting them, but each night's BU almost fills a complete tape), I decided to go back to backing up the DB's to hard disk thru an Ent Mgr Maint Plan & only copy a couple of the DB backup files to tape.  So I UNINSTALLED the SQL add-in for Tapeware.

I think it MAY have been around that time when all this began.

Tapeware itself is still installed but the SQL Agent has been removed, and I stopped the TW service few days ago when all this just began & still couldn't get correct behavior with it stopped.

Would it make any sense to delete my one instance of SQL server (it's called "Local"), and re-register a new one?  Or just completely UNINSTALL SQL Server & reinstall (been trying to avoid that).  What would happen to my DB's in the process?  Would they simply appear in the new instance?  Or do I need to have good backups (HA!) before doing that?

I think I'm drowning here.................................  Somebody shoot me.......
0
 
LVL 34

Expert Comment

by:arbert
ID: 13502897
" It did restore, but Trash1 is hung in "Loading" state.  I even stopped SQL svc & re-started - still says "Loading" after 45 mins."

Did you specify WITH RECOVERY on the restore?  If not, it will always show in the "loading...." status....


"- it is going on very negative  personal level - that I personally do not like "

Sorry if you take the comments personal--never meant that way.  I've worked in MANY large corporate environments, can't think of one that every use AV on a database server--it's just experience and what we've been taught all along by microsoft AND oracle.
0
 
LVL 8

Expert Comment

by:Julianva
ID: 13504192
Please try

Instead of using Mutiprotocol - try using TCp/ip in you net library and make sure that you port is 1433 - also check   IPC$ (inter process communication) if you have permissions  sharing - sometimes network admins stop sharing IPC$for security reasons. TCP/IP relies on this

to do this
go to my computer - right click - manage - click on shared folders - click on shares - you will see IPC$ - right click if stop sharing is an option then it is shared
already. if not sharing will be an option - click on sharing and try you back up

Like i said before SQLSATE 42000 is a syntax error or access violation.

If you can - take the database off  line or detach and do an NT backup of the data files
I have done this before because my database could not fit on to one tape and SQL 2000 backup does not support this - Multiple tapes Backup and restore.

OR Maybe you need to call Microsoft help centre.

0
 

Author Comment

by:cricketman
ID: 13505200
> Did you specify WITH RECOVERY on the restore?  If not, it will always show in the "loading...." status....

Did the RESTORE thru Ent Mgr not QA.  What default parameters does the Restore thru EM use?

What is really the proper procedure for restoring?  I guess there are two scenarios in which I would want to do a restore: 1) rstore same databads, and 2) restore to a diff database.  Again, doing both with EM.

Say I want to baasically make a COPY of GCF DB and call it NEW.  Should I first create a DB with the name "NEW", then restore GCF on top of it?  Or Just do a Restore telling EM to restore GCF *AS* NEW and have it create the NEW DB in the process?

Julianva - I will try what you suggest today.  Thanks.

Does anyone think it's possible that since this DB is (what I consider) fairly large (2.2 GB), somehow the BU is just being performed in memory faster than the disk can accept it, and the symptom of the slowly increasing filesize in Win Explorer is just the disk accepting the info from RAM after the BU is really complete?  I haven't really thought this made sense, since it always backed up normally (I used to see the blue progress bar move along in the BU dialog).

I recently adjusted the memory settings for SQL giving it a little more memory.  Was set to MIN-64mb / MAX-256mb and I changed it to MIN-256 / MAX-384 (machine has 512mb).  Is that relevant?

Thanks to all.  I'M STILL TRYING!!!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13505291




cricketman:
looks like 1..2.. things left:
1. check if you can do:
 stop MSSQLserver service -
 (copy your"bad" database to another sql server and attach it,
then try backup again)
2. stop MSSQLserver service -copy all databases (just in case in safe place)
and reinstall sql server and SP
3. BTW: what softwer do you have on your server?
--------------------------------------------------------------
-------------------------------------------------------------
 

arbert:
I have been working in big companies too - and only have issue with AV on. cluster server on rest of sql server - just excluded big files and sql server folders - that gave us additional protection from viruses but we have used expensive enterprise editions
Trend Inc. Network Associated Technology.
I’m monitoring daily health of the servers and do not see any negative effects on performances or backups…
As I told before – there are so many different environments, rules, standards
So everything is possible..


Socrates told:
I am the wisest man alive, for I know one thing, and that is that I know nothing.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13506983
EugeneZ,

<offtopic>
>>I have been working in big companies too<<
Give it a rest and lets all move on.
</offtopic>
0
 

Author Comment

by:cricketman
ID: 13511617
OK... everyone is now off the network so I'm about to begin hitting at this again.  I will try detaching all db's, deleting my instance of SQL server, re-create & re-register a new instance & re-attach db's.

ALSO, in thinking what is different about this one db than any of the others I have, the only real thing is that it's the largest (2.2gb).

I'd like to create a totally different new db & grow it to that size, try to BU & see how it behaves.  Can someone write me a couple lines of SQL to just plug random data (a bunch of zeros, text strings, etc) into thousands of records records in a table just to make a db large?

Thanks,

cag
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13511770
You really need to test it on a different SQL Server not the same one.

>>Can someone write me a couple lines of SQL to just plug random data <<
Not a couple of lines, but it should do the trick:

Declare @Counter integer

Create Table Table1(
            ID integer IDENTITY(1,1) Not Null,
            Col uniqueidentifier)

SET NOCOUNT ON
Set @Counter = 0
While @Counter < 1000000
   Begin
      Insert Table1 (Col) Values (NEWID())
      Set @Counter = @Counter + 1
   End

Print 'Done!'

I trust you will be able to re-attach the database ...
0
 

Author Comment

by:cricketman
ID: 13512029
acperkins,

Thanks.

Just ran your query - COOL!  Resulted in 45mb database, so I'm running it again (took out the create table part) - and will next time just set counter to go to 20 or 30M.

And yes, I *think* I will be able to re-attach the db's.  Will do thru Ent Mgr... is it dependable?

My plan (please advise if wrong):

1) detach all db's 1 x 1
2) BACKUP (copy) all mdf & ldf files to another folder (probably also to another HD on the network)
3) stop all SQL services
4) delete my SQL Server registration
5) delete my SQL Server Group
6) Pray.....
7) create a new group with EM
8) register a new server
9) attach the db's 1 x 1 with EM

Am I on track??
0
 

Author Comment

by:cricketman
ID: 13512147
OK... just started the query running 40 million reps which if I did the math right will give me a db around 2 gb!  Going to grocery store & will return in 45 mins or so.

Thanks for the help!

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13512222
>4) delete my SQL Server registration
what are you trying to do?
if uninstall\install ->control panel ->add\remove program..etc
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13512510
>>Will do thru Ent Mgr... is it dependable?<<
I really cannot say for sure, but I have noticed that Scott (aka ScottPletcher) recommends using SQL Query Analyzer as opposed to Enterprise Manager.  Unlike BACKUP/RESTORE the sp_attach_db and sp_detach_db stored procedures are fairly straight forward commands with few options, so I would follow his advice.

>>Am I on track??<<
I think so.  Especially point 6. There is no Service Pack for that. <g>
0
 

Author Comment

by:cricketman
ID: 13512713
OK... back from the store.  40M query still running.

acperkins,

>4) delete my SQL Server registration
>what are you trying to do?
>if uninstall\install ->control panel ->add\remove program..etc

I've thrown so many darts at this thing...... I'm wanting to just wipe the slate clean to some extent & start again.  I guess I'm kinda working backwards from what I think would be the worst case scenario & deciding what I want to do tonight:

ULTIMATE FIX > reformat RAID array & reinstall OS & everything (not there yet!)
BACK 1 STEP > uninstall SQL Server completely (with SBS setup routine or thru Ctrl Panel) & reinstall (rather not)
BACK 1 MORE STEP > "Delete SQL Server Registration" in EM & re-register (easier - willing to do now)

I guesss this is really just throwing a few more darts before the ULTIMATE FIX.

I will review the detach/attach commands on BOL & maybe do them that way.

OH... and somewhere someone asked what all programs we have on the server:

Small Business Server 2k
SQL Server 2k Standard Edition
Goldmine contact mgr
Great Plains Accounting
HAD Symantec AV Corp Edition 9 (but removed now)
Tapeware Tape BU software
couple of Print Serrver management tools (Linksys, etc)
Atom Time98 (time sync program)

That's about it.

I'd LIKE to put Symantec AV back b/c the Goldmine mail store is on the server drive, & we get lots of spam with viruses attached.  Not sure if I'd feel comfortable with no protection on that box.  (Maybe that's said from a position of ignorance?)?...

cag
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13513349
>>acperkins,

>4) delete my SQL Server registration
>what are you trying to do?
>if uninstall\install ->control panel ->add\remove program..etc<<

That was EugeneZ, not me.

>>I'd LIKE to put Symantec AV back b/c the Goldmine mail store is on the server drive<<
Fair enough.  Typically SQL Server is recommended that it is installed standalone, in other words it does not have to compete for resources with any other application, hence my advice about not installing antivirus software.
0
 

Author Comment

by:cricketman
ID: 13524732
OK all.... buckle your seat belts - this is about to get REALLY STRANGE!

To test the hypothesis that the BU probs I'm experiencing may be due to the SIZE of the DB rather than the specific DB itself, I decided to create a brand new DB called JUNK1, and with a little script (provided by acperkins - thanks!) I filled it with random data to make it as large as my GCF DB (one that's giving me probs).

Guess what - got the SAME BEHAVIOR when trying to BU this new DB - BU dialog for 10 seconds (way too fast), no progress bar, creates a file that when viewed with Windows Explorer, shows a filesize of 0 kb, and "grows" only after r-clicking & viewing Properties on it then hitting F5 to refresh yadda yadda yadda.....  EXACTLY the same as when backing up my GCF DB.

Now... here's where it gets REALLY wierd - This morning I browsed to the folder where the JUNK1 BU was sent last night, and the file is GONE.  Just not there.  I'm thinking OK... maybe I deleted it last night before I went to bed.  SO... I just did another BU of the JUNK1 DB.  Same deal (0 kb file, etc), but this time I decided NOT to r-click on it to "make" it show a large filesize, but instead I waited to see if after a few minutes it would finally show some size on its own.  While looking at the 0 kb file in Win Explorer, I hit F5 to refresh the view about a dozen times over about a one minute period.  No change - JUNK1 = 0 kb.  I ALT-TAB over to SQL EM to browse around about a minute, then ALT-TAB back to Win Explorer... the JUNK1 BU file has DISAPPEARED!  It's just not there.

I do another BU of JUNK1, go to Win Explorer, see the BU file created as JUNK1 = 0 kb.  This time I r-click, view Properties, it says 640 mb.  I close the Properties dialog, in WIn Expl, JUNK1 still = 0 kb.  I hit F5 to refresh, now JUNK1 = 633 mb.  I go brush my teeth, come back, now JUNK1 = 3,372,440 kb.

It's like I HAVE to view the file Properties in Win Explorer to prevent the BU file from "evaporating"!!

Is there an award for the strangest, most difficult problem in the history of Experts Exchange?  Because if so I think I should get it.

Does anyone have ANY idea what could be causing this kind of crazy behavior?

At this point I am willing to consider ANYTHING!!!!

cag
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13524814
call the Microsoft PPC support
0
 

Author Comment

by:cricketman
ID: 13525285
Yep... I think at this point that will be my next step - I am going to compile a document with all the info on this problem & email to MS Support.

If anyone has anoy other ideas PLEASE continue to post to this question.

EE ADMINS - I have not reached a SOLUTION on this issue, but I HAVE received lots of help nonetheless.  What is the protocol (concerning awarding points) in a case like this?

cag
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13526178
>>What is the protocol (concerning awarding points) in a case like this?<<

 What are my choices?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67

My recommendation: Request the question be PAQ'd and your points refunded.
0
 

Author Comment

by:cricketman
ID: 13538561
Well, not sure if it should be deleted or PAQ'd.  I'd like for it to STAY OPEN for another week or 10 days, and if no other info surfaces, at that time I will request a refund of the points & let a moderator decide if a delete or PAQ is appropriate.

Thanks to all who tried to help me find the solution, and please keep this issue in mind.

I will check back daily over the next week or 10 days.

Thanks,

cag
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13539048
Fair enough.
0
 

Author Comment

by:cricketman
ID: 13610935
OK... looks like no more info is coming.  Sorry to say I have NOT resolved this problem.  As a workaround I'm having my Tapeware tape backup software backup the DB.  Some have said that tape backup is not the preferred method for backing up live SQL data, but at this point I have no other choice.  Tapeware SEEMS to get it fine, but I have not tried restoring from it yet.

ADMIN - I am ready to close this question, and based on recommendtions of others I am requesting the points be refunded to me, and the question either deleted or PAQ'd.  Even though it was not resolved, perhaps a PAQ would be beneficial to anyone else having similar issues???

You decide & let me know.

Thanks to all who helped - I really do appreciate it.

cag
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13612302
>>ADMIN - I am ready to close this question<<
This is how it works here:
Nobody answered my question. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13617938
Fine with me.
0
 

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 13640120
Question Closed, 500 points refunded.
PAQ_Man
Community Support Moderator
0
 
LVL 1

Expert Comment

by:HeitmanProgrammers
ID: 14696168
If you are still looking for a permenant solution, here you are. I ran into the same issue and was able to resolve my issue with just trying the WorkAround method mentioned in the article below.

http://support.microsoft.com/default.aspx?scid=kb;en-us;827452

good luck
0
 
LVL 1

Expert Comment

by:HeitmanProgrammers
ID: 14696321
also what you could do is, on the server go to Client Network Utility (click on the Alias tab) and see if ther server is in there with connection type as Named Pipes. If it is the case then change the connection to TCP/IP and specify the port number.

I am sharing as I learn therefore apologize for two separate posts.
0
 
LVL 34

Expert Comment

by:arbert
ID: 14716251
This question is closed...
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question