I have a database which is having a small log file and a huge mdf file which around 3gb, i tried to shrink through the enterprise manager options but no luck. Even through taskpad i find the table sizes are less than 1 gb , kindly help me in solving this problem
View 1 RepliesOn one of my servers, the log file mysteriously grew to 38Gb. It completely filled the HDD, so I created another log file, took the database offline, deleted the very large log file, and then renamed the new log file to be the same name as the one I deleted. When I try to bring the database back online, I get a message saying that the log file is not a primary log file, and the database stays offline. What can I do to get this database back online?
View 12 Replies View RelatedIs it possible to restore SQL2000 backup to SQL2005? Or I have to restore the DB to SQL2000 and then upgrade it to SQL2005?
Can someone provide me ways to upgrade SQL2000 DB to SQL2005?
Thanks,
Hiten
Hi!
i can't understand how to perform file attachment to SQL2000:
<connectionStrings> <add name="SimpleListsConnectionString" connectionString="Data Source=local;AttachDbFilename=SimpleLists.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings>
Server return error:
error 602: could not find row in sysindexes for database
run dbcc checktable on sysindexes
Can any body help?
I know there is a command xp_delete_file for SQL 2005 but if I want to delete a file from a 2000 database is there a command to do the same.
View 4 Replies View RelatedI administer an application that runs on SQL 2000, without being an SQL expert myself. I have observed that if I create a maintenance plan then the log file gets trimmed, but otherwise the log file keeps growing. All was going well at one site, but recently the log file has become huge. The server is also running out of disk space. Is that the problem? I'm not sure how to control this. Can I delete a log and then it will re-create? Two smaller databases share the same maintenance plan and these logs are small. Thank you.
View 6 Replies View RelatedI have a test db in SQL Server Express 2005 with a 65MB dat file and a 1.1GB log file!! The production db has a slightly larger dat file and only a 6MB log file. I haven't updated by test db from the production db in a couple of months.
I tried Shrink Database and Shrink File in the Management Studio Express, but the log file size hasn't changed.
How did the log file get so large and how do I fix it?
Thanks for any help.
Admin is kind of new to me. Could someone recommend how to reduce these file sizes?
In Dev, my MDF is 16 MB, LDF .5 MB.
I run a script to recreate the schema and insert rows on the Host.
When downloading a db backup from the Host and restoring it to Dev, my MDF is 24 MB, LDF 25 MB.
The same dataset grew by ~35 MB!!!
Any advice is welcome. Thank you. Rick
I am getting the follwoing error when running my package.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80004005 Description: "File c:docume~1sortizlocals~1 emp 0003589001o.tmp is too large.".
I use a select statement to join to large foxpro tables. The data contains over a million records.
Any help is greatly appreciated.
Thanks
Hi to all
My database's log file is getting very large in a few days. There are only 2 users and they do not do a lot of work. But it gets larger and larger every day.
Autogrow is enabled. File Growth is 'in percent' and Maximum file Size 'Restricted File Growth-500MB'
Is there a way to stop the log file growing huge?
Thank you
Hi
I have new bought the SQL server 2005 enterprise, but it have 2 CDs, so what are the differences between CD1 and CD2? and so which one should i install first? or is it necessary to install both two or just need to install one of them?
And about my original sql 2000 database, can i just attach it's MDF file into the sql 2005 engine, or which import wizard can load the sql 2000 MDF into sql 2005? or do i need to keep the sql 2000 engine before do this?
thx
My log file size is of 5 GB, I just wanna reduce this to some extent without adopting shrinking method. So is there any way to do the same ?
View 1 Replies View RelatedHow can I send large file by sql male
Thanks
Hi,
I have a problem where I want to import a 1.6 GB XML file with SSIS into a SQL Server database. My hunch is that SSIS is not very good with handling such large amount of XML data. My test shows that SSIS tries to read all of the file into memory.
Does anyone know if there is any solution of solving this memory problem. My problem is that I want to take this source XML file import it into a database, make some transformations on it (eliminate duplicates etc) then produce a NEW XML file as output in a different XSD-format.
Is really SSIS the right tool for this operation?
The source XML file also have mixed content on Complex Types which seems to be a problem for SSIS as well.
Best regs,
//Patrick
I can't find the answer to these problems. I'm using SQL Server 2005 Developer Edition. I have a report with 15,036 rows and about 15 columns. It utilizes a table and a report header and footer. One column has data containing hyperlinks to another report.
1) When I export this report to Excel, the result is 14.2MB file. Why is this file so huge, and how can I make it smaller?
2) Is there anything I can do to prevent the inclusion of the hyperlinks in the exported Excel file?
Thanks,
Rosie
When we do a full backup the .bak file is 700MB but in the meanwhile our differential backup has grown to a size of 20GB. The backup set expires in 1 day and if the backup file already exists we "append" to it.
What we basically want is a differential backup for one day only. I realize that in SQL Server 2005 you could add a "Clean up" task but that is to delete files but because we only have 1 .bak file for the differential backup this is not an option.
If we set to overwrite the backup file if it already exists what does this mean? Assume that we run a differential backup every hour, does this mean that the differential backup file will be overwritten every hour?
How can we make sure that we have a differential backup every hour and keep only the differential backups for the last 24 hours?
I have a SQL Server Agent job set up that has 3 steps. The first one, type
CmdExec, runs a .BAT file. (It logs into our mainfram ftp server and downloads the files I need.) This step works fine.
Step 2, also type CmdExec, is supposed to run an .EXE file which further manipulates the FTPed data.
A third step, DTS, proceeds if given the chance...
Unfortunately, Step 2, does not run the EXE file. It sits forever. The EXE file can be run from a cmd window, or in the Windows directory window OK. The BAT files run properly from anywhere, including SQL JOB steps.
So I can`t figure out is what's causing my step 2 (run EXE) to fail.
When using DTS (in SQL 7) to export via OLE DB a large varchar to a text file, it clips it at 255 chars.
No other data access drivers seem to work, either. This is lame! I cannot use bcp as a work
around, because i want to use quoted comma-delimited, which it doesn't support, and I
am using query-based export, where the query calls a stored proc, which bcp also doesn't
support.
Are there any new versions of MDAC that fix this? Anyone know a workaround? My current hack fix
is to split my field into 2, but this is a grubby fix that hassles my reciptients.
This is a pretty fundamental limitation to a major product!
dn
Gurus,
I have inherited a SQL 2000 database ( (I am new to SQL DBA) and I found this when I was checking the db properites . The transaction log has grown bigger than the actual data file, I thought transaction log backups would truncate the inactive portion of the log file and shrink the transaction log, but it was not the case it seems, may be it was truncating the inactive portion of the log, but not shrinking it. This site does not have a job for truncating the data/log files periodically. What is the best method to deal this situation, how can I shrink the Transaction log quickly?,
All your suggestions are welcome.
TIA,
-Jay
We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?
The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.
Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?
Like I said a rough estimate is fine.
Many thanks,
Scott
Hi everybody,
On executing the RESTORE command of SQL Server to restore from a backup of 78.3 MB, the "Server Application Unavailable" error message comes up.The error message in the Application log is as follows:aspnet_wp.exe (PID: 2184) was recycled because memory consumption exceeded the 152 MB (60 percent of available RAM).
However using Query Analyser of SQL Server I am able to restore the database.
What is the solution to this problem?
hi..
I want to store a RMVB file to SQL SERVER 2000 ,and read from it,iwant to play the RMVB file in web,the size of the RMVB file is more than 300MB less 1G.the SQL Field Image can include it.
Now My Quesstion is How can i Store and Read the RMVB file from SQL Server2000?
I used SqlInsertCommand.ExecuteNoquery() in my program,but it Too slow,ao make a unknown error.
Thank you for your help.
Hi
I want to store large files like pdf file,Html page,audio file in Sql Server database.How can i do it?
if somebody know then tell me as soon as possible.
Thanks in advance.
Bye
Hi :
I want to transfer large data from access db into sql server compact , how can I do ? thanks a lot!
We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.
Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can I go about splitting the data from 1 MDF files into multiple data files? How about moving indexes ?
SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) - Enterprise Edition (64-bit)
Hi ,
Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?
Regards,
Prash
I have a large fact table about 500 million rows, and I am using 2008 r2, thus I am having the file system error, I have browsed online and tried all the fix , but I am still having the error . I tried taking only about year data (which was still around 200 million records) and I was still having the error.
View 11 Replies View RelatedI have several databases that have grown to 300 GB and would like to distribute the data into multiple files across multiple drives. Can I create a new database that is spread across the new drives and use a full backup to restore or am I stuck with unloading the data table by table?
View 3 Replies View RelatedI have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.
A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies View RelatedIn my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.