ODBC Driver Error - Timeout Expired

Very common error but wired scenario. Every client machine get this error in morning. VB application works fine until evening but when everyone goes home after shut down the machine and they come back again in morning and try to run the application they get this error. Application runs fine, It can access data, pull data, view data but It can not write any data. (Other words can not enter any data).

Application again starts working fine after I copy database on different SQL server. For temporary solution I swap database from one SQL Server to other one day and back to original SQL server next day. Every morning it takes about 2 hours to copy database. Im doing this from last few days as working solution. FYI, I have 2 different VB application, each has their own database. One working fine and other started giving me problem, the one I described above.

Few thing I want to let you know:

Recently I changed the SQL server. After I changed I started having this problem. But other application working fine. So I dont think that could be a problem. (Both application basically same in terms of development and tools they use. VB and SQL Server, ODBC connection, Crystal Reports).

In old SQL server both database had daily backup on third party backup built on different server using Client Network Backup. After I changed the SQL server I never modify backup setting. So after I moved SQL server , every night backup was trying to connect to old SQL server and but It couldnt take the backup cause I changed the machine. Again if thats the problem both application should not work but one working fine other is giving me problem.

One more thing I want to mention here is I started having this problem when I left the SQL server copying database overnight. Means, I started copying database and I left the machine ON when I came in the morning copying database was done and I just click on the OK and close the window. Basically It has finished copying database in around 2 hours after I started and I close the window when I came back next morning.

Thats the few things Im thinking about but I dont know what kind of database setting this might have changed and how to reset again. Any help will appreciated.

Dose any one know how to combine .mdf (Primary data file) and .ndf (secondary data file) ?To answer your direct question, use DBCC SHRINKFILE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp) to empty the NDF file, then use ALTER DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp) to deactivate the NDF file. At that point, you can then simply delete the NDF file because it is no longer part of your database.

As a separate issue, I'd suspect that the third party backup is somehow causing your problem with users being unable to write data. I'm not sure how it is interfering. There are many third party backup solutions, with varying degrees of compatibility with SQL Server. Almost all of them work under their ideal conditions, almost all of them break down under other conditions.

Just as a point of curiousity, does the problem persist if you reboot NT on the machine that runs SQL Server? IF there is some problem with file locking, etc. that should clear up the problem for you a lot more quickly and simply than having to copy the database to another server. It would also give you some valuable information for trying to locate the real source of your problem.

-PatP|||Thanks for .ndf tip.

No, It wouldnt clear the problem if restart the NT machine. Just today I tried another solution, I dont know if its going to work but what I did is create the new database and import the data from the original database which already solved my .ndf file problem, now I have only one data file. I dont know if its going to work. I will find out tomorrow morning.

I also noticed one thing today someone created (there is couple of people have access to server) one more filegroup which is Primary_1. There was not any file in that group so I tried to delete that group but It would not delete It just kept freezing.

One question, If I created the new database with same name and import data using SQL server Import function where I choose Import all Objects. Is it going to have any problem in terms of data. I mean tables and views are all imported. It just changed 2 data file to one data file and also got rid of that Primary_1 file group. Just wondering if its going to make any difference in data. I mean I'm going to loose some data or anything like that. Application functions fine.

Thanks.sql