MSSQL 7 Log File Resizing
Jul 14, 2000
Here's my question, hopefully one of you can help me:
When you allow your log file to grow on its own, eventually it uses up all your diskspace . . so you truncate it. However, when you truncate it, it doesn't *resize* the log file, so your still out of disk space, it simply clears up room within the allocated "block" or what-not so that the db can continue to make log file entries.
So, does anyone know of a way to resize that log file without losing it's data? For example, we were thinking about truncating it, backing it up, dropping the original, and then restore the log file from the "truncated backup." Anyways, there must be an easier solution (if that one even works!) . . so please, if you have any hints, let me know.
Thanks,
David P.
View 3 Replies
ADVERTISEMENT
Nov 27, 2000
The size of log is around 600MB and only 20MB is used...
I tried truncating the log using dbcc shrinkfile..but it doesn't truncate
Any help would be appreciated....
Thanks.
View 3 Replies
View Related
Jun 25, 1998
We are looking into sql 7.0 and would like to know how it handles excessively sized databases. If we restore a 6.5 database into a db on 7.0 will it automatically resize to free up disk space or will it only work for new databases which have install scripts run on it.
View 2 Replies
View Related
Nov 26, 2000
Hi all, hope someone can give me some guidence on this.
We have a database on SQL 6.5 sp5a that is 25GB in size. The transaction log is 10GB in size. We do transaction log dumps every hour throughout the day. On a very heavy day the transaction log backup device will be just over 2GB in size. Around 8GB wasted because no-one knew how much space would be used.
Is there an easy way to resize the transaction log so we can reclaim some space. I was thinking of creating a new smaller database device, then using sp_logdevice to use the smaller device as the log and get rid of the 10GB device.
Any thoughts, ideas would be great.
Thanks
Phill
View 1 Replies
View Related
May 25, 1999
My transaction log has grown to 2 Gig. But when I do a truncate transaction log. It is only 18Mb. How can I resize the physical disk space of the transaction log. I want it only to be 100 Mb. It is just wasting the disk space. Please help!
View 1 Replies
View Related
Dec 7, 2007
i have a report with several rectangles. How do I make the size of the rectangles fixed so that they do not resize automatically?
They appear fixed until i put a table inside. once I do that it automatically resizes
View 4 Replies
View Related
Sep 2, 1998
I have a database which is quickly filling up its allotted data space (the database is 500MB, and the device is also 500MB). I wanted to know whether it was an acceptable procedure to:
-Dump the database
-Create a new 600MB (or more) device
-Create a 600MB database for load on that device
-Restore the dump to it.
Are there data integrity/corruption/fragmenting issues with this method? The alternatives are simply expanding the database across another device (or on the same device using DISK RESIZE), or using scripts and bcp to recreate the database from scratch. These do not seem ideal. Any opinions or comments would be greatly appreciated.
MR
View 2 Replies
View Related
May 3, 1999
Is there a way to shrink database devices in SQL Server 6.5 Other than dropping and restoring?
View 1 Replies
View Related
Oct 23, 2015
Have a SQL2008R2 instance on a VM where the single .mdf for the tempDb database is located on a high contention disk. I've managed to get another 60GB disk and thought it would be a good time to move the .mdf and also increase it's size and number of files.
The server has 12 cores and after a bit of reading I've decided that it would be best just to have four files for this database as the 1 file per core (-1) seems to be disputed.
-- Move the existing file to the new disk and rename it.
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:SQLData empdb0.mdf');
-- Change the size to 1GB
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE= 1048576KB, FILEGROWTH=5%);
-- Add three new files, all with the same size & growth
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'E:SQLData empdb1.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'E:SQLData empdb2.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'E:SQLData empdb3.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
-- Now restart the instance.
Also, what are peoples thoughts on percentage growth for tempDb? I've read that it's not recommend and yet it seems to be the norm.
View 4 Replies
View Related
Jul 9, 2015
I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
View 6 Replies
View Related
Oct 5, 2015
We have a vendor created database with 9000+ tables, one of which has about 6 billion rows. The vendor redesigned the database recently and ever since we've had terrible performance.
What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.
Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.
Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.
The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).
There is no additional data, just wider columns. Why we are unable to create the index?
What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?
View 3 Replies
View Related
May 1, 2007
I got an Assertion failed error while resizing a vector in a plug-in algorithm.
In order to isolate the problem I created a simple model class in Navigator.h file as shown below:
//========================= begin code =======================
class CStateStats
{
public:
DOUBLE m_dblSum;
DOUBLE m_dblSqrSum;
public:
CStateStats()
{
m_dblSum = 0.0;
m_dblSqrSum = 0.0;
}
};
class CAttStats : public DMHALLOC
{
public:
dmh_vector<CStateStats> vstatestats;
public:
CAttStats() : vstatestats (*this)
{
}
};
//========================= end code =======================
The access to DMHALLOC is provided in that class and in Navigator class as shown below:
//========================= begin code =======================
class ATL_NO_VTABLE NAVIGATOR :
public DMHALLOC,
public CComObjectRootEx<CComMultiThreadModel>,
public CComCoClass<NAVIGATOR, &CLSID_NAVIGATOR>,
public ISupportErrorInfo,
public IDMAlgorithmNavigation
{
public:
NAVIGATOR() : _viAttributeOutput(*this), _vCAttStats(*this)
//========================= end code =======================
I succeded making room for _vCAttStats vector, but when I tried providing room for the vectors of the vector I got an Assertion failed error (file dmhallocator.h Line:56 Expression assert(_dmhalloc._spidmmemoryallocator != NULL)). Please, see the code below, included in NAVIGATOR::GetNodeArrayProperty function:
//========================= begin code =======================
_vCAttStats.resize (2); // <<<<< succeeded here!
// make space for the states
_vCAttStats[0].vstatestats.resize(ulStates); // <<<<<<< assertion failed here!
//========================= end code =======================
I tried using a vector-of-vector approach and I also succeeded.
But I have to use that kind of structure: a vector of class with a vector inside.
I think I must provide a similar approach of vector-of-vector existing in DmhVector.h but I don't know how to do it.
I would apreciate any help.
View 3 Replies
View Related
Dec 26, 2006
As we know, MySQL have function to output its data into Text File using "Select * into outfile 'C:/mytext.txt".
Does MSSQL has "Select into Outfile" function??? If yes, what is the function??
Thanks in advance :)
Anderson
View 4 Replies
View Related
Oct 13, 2007
Hi Everyone,How to retrieve a XML file from MSSQL using a stored procedure in asp.net ?Thanks,may
View 1 Replies
View Related
Oct 19, 2007
Hello, I am using Visual Studio Web Developer 2005 Express Edition with MSSQL 2005. I need to import my db into the db in Godaddy.com web server. How do you know export MSSQL db in VSWD to csv file? I was trying Teratrax. But I am not sure how to browse for the database on my local machine.My local machine runs on XP Home Edition and I write my ASP.NET app with Visual Studio Web Developer Express Edition. How do I find out what my server name is so that I can connect to the database of my application? I don't think it is localhost because I don't have IIS running on my machine. From my understand VSWD has its own web server. But I am not sure how to connect to my data baswe so that I can export the database to CSV file using Teratrax.ThanksJacthemanus
View 4 Replies
View Related
May 15, 2008
Hi
How can I export a MSSQL DB (table, SP, View, data) to a single csv file? I can export them to a sql file through Publishing Wizard, but how can I export it to a csv file?
Thanks for help
View 1 Replies
View Related
Oct 12, 2001
Hi there,
I wanted to know if there is a possiblity of running a scheduled VBScript file under MSSQL 7.0 using the - Management->Sql Server Agent->Jobs.
When I create a Step, there are these given options:
1. Active Script:
I understand that the configuration would allow VBScript or Java Script or Others.... but the command could only be as long as 3200 characters. My script is longer than the mentioned limit hence I will have to use an external .vbs file.
I don't know how the third option of others to be used.
2.Operating System Command(CmdExec):
I can only use .BAT, .CMD, .EXE, .COM extentions in this. Again, I would have to convert my .VBS file to a .EXE and then schedule it. It has a lot of overheads to it, which I consider is not apt.
Could anyone suggest me how to go about scheduling a .vbs file on MSSQL 7.0 with set frequency.
Regards,
Raj
View 1 Replies
View Related
Aug 25, 2004
I have backup.sql created by mysql. How can I let mssql server to import such file?
View 3 Replies
View Related
May 15, 2007
I have a report that (for purposes of this example) displays States and the Cities in a state.
Now the State is the first group which I want to drill down into by expanding and then show the Cities as the detail line.
What I would expect to happen (and what does happen if I use the wizard instead of creating the report manually) is that I would click on a State and then the page would expand to make room for all the cities listed below.
(That is to say if I am showing only states, not expanded, then all 50 states fit on a single page. Then if I expand California all the states below that shift onto other pages to make room for the 400+ cities in California)
However, what happens is that when I preview the report instead of being 1 page long it is many pages in length with empty white space between the States (white space that would be occupied by the cities if I were to expand the state).
Can someone tell me what setting I need to adjust to get the report to dynamically resize when I expand/collapse groups?
Thank you.
View 1 Replies
View Related
Jun 16, 2007
Hi,
I need an application with asp.net that let me restore database by getting .bak file and database name and database user and user password, my database is SQL Server 2005.
View 1 Replies
View Related
Apr 29, 2005
Hi.. When I go to my MSSQL 2000 database-->
properties-->Databse size is 1500MG and the space
available is 305MB
But I don't understand how space available come from.
As I had put my Database file and Transaction log file
as unrestricted file grow. And put automatically file
grow by 10% for both file and log
Why is the limit come from ?
Besides, I have a database which indicate space
available as 0M in the database properties but it is
still functioning. why?
View 1 Replies
View Related
Oct 11, 2007
Hello, we are running Microsoft SQL 2005 Express edition (9.0.32).
Recently I just noticed that the database log file of our main database is HUGE. The database data file is only 50MB and the log file is 210GB.
Any idea what is causing this? Seems to be getting bigger with time, in the last 7 days seems to have grown by 100GB. I noticed the following settings under the database:
Autogrowth: By 15 percent, unrestricted growth
Does that seem right? Thanks.
View 13 Replies
View Related
Dec 23, 2005
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in text
no space, no symbol, no line break between each record
i must use the length of char to define it.
Please help me how to import into mssql
View 1 Replies
View Related
Sep 19, 2007
Hello experts,
I need to create a File DSN on AIX Unix server which can connect to MS SQL server 2005 on a windows machine.Can anyone please give me step by step procedure how to do it.Its an urgent requirement. Please help me.
Regards
Arvind L
View 3 Replies
View Related
Feb 4, 2005
i've some records in the mssql server which are actually files or images. They're stored in either binary fields or image fields.
thus how do i display these files out? or let people download them???how do i write such aspx? thanks
View 2 Replies
View Related
Feb 29, 2000
Hi All,
I had a problem today where BCP would not read the format file I created giving the following error:
I/O error while reading BCP format file.
I searched the archives here at swynk.com and found a thread titled exactly the same as the one I am now creating. There were numerous responses to this thread but none of them seemed to solve the problem for me (short of the one that suggested using BCP to create the format file, which I didn't want to do).
I used BCP to create a format file for me (which looked identical to the format file that I created) and it worked fine. With my suspicions aroused, I used a binary file viewer to look at the contents of each file and highlight the differences. Apart from the usual variations in whitespace I noticed that the my file did not have any carriage returnline feed at the end of the last row definition.
My format file ended at the end of the last row definition line. It did not have any carriage returns and line feeds. I matched the one generated by BCP and added the following "
" to the end of the file, tested my format file and it worked perfectly.
The moral of the story is, make sure when you create a format file, you have an empty line at the end of the file (ie: after your last "Server Column Name", make sure you have "
" sans quotes).
Hope this saves someone a few hours :-)
Cheers,
Dan.
View 1 Replies
View Related
Aug 30, 2006
Hi.. We have a MSSQL application and the DB file (not the log file) seems getting bigger over this few year and right now you are running almost out of space. May I know how does the other company deal with this kind of situation?
i am sure other company data is getting bigger as well and it has been longer time than ours. How to deal with it ?
View 3 Replies
View Related
Oct 4, 2005
Hi.
I've following problem:
- Customer played with his logical drives in a CPQ smart array
- He played too much: he lost the drive with the LDF-File
- Now the SQL-Server is in a kind of wired mode; you can
start and stop it, but you cannot connect to (neiter 'sa' nor
any other user available before)
In fact the mode is not wired: what shall he do other when
most important file is missing... :o
- The problem for me now is:
> How can I get the Enterprise Manager or Query Analyzer
up and running again to start a restore?
PS: The restore is to be done from a tape-library running
Veritas Backup Exec + SQL-Agent
PPS: Error-log says (what I already know):
2005-10-04 17:18:02.69 spid11 Device activation error. The physical file name 'H:DEVLOG1DEVLOG1.ldf' may be incorrect.
Thanks in advance for assistance.
Regards,
Carsten Loeffler
View 8 Replies
View Related
Jul 23, 2005
Hi all...I would like to know if SQL SERVER can load a tab delimited text file.If yes, how?A search on the web did not return me the "load data" command as mysqlor other.Thank you all.
View 5 Replies
View Related
Mar 30, 2007
Hi ,
we have a requirement to create a database of our application in client's system. and we assume that already MS Sql server is installed.
we have a .mdf file of our database. Is there any way I can attach .mdf file to the database using command line. so that I can write a batch file and run that batch to create the database in our client's system.
-regards
GRK
View 4 Replies
View Related
Dec 6, 2006
Hi... We have application that connected to MSSQL 2000 database. The database file is getting bigger and bigger over the years. Recently the performance of the database and application is getting slower and slower, my senior oracle DBA told me that I should archive the MSSQL 2000 data file and export the old record to the archive DB. So that the number of record will be less and it will be faster.
I would like to know whether MSSQL 2000 support archiving of the database file ? If yes, what is the way to do it ? I could not find it in Enterprise manager option at all.
View 2 Replies
View Related
Dec 24, 2005
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in db
no space, no symbol, no line break between each record
----------------------------------------------------------
I try bcp method, but some error happens. Please give me suggestions. thx
I run the following code in query analyzer.
BULK INSERT Chain.dbo.POLL59
FROM 'D:POLL59.DWN'
WITH (FORMATFILE = 'D:cp.fmt')
?????
it shows the error
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:cp.fmt'.
***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >
bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""
!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.
View 6 Replies
View Related
Jun 11, 2007
Hello All,I am trying to create a DTS package.I have two tables tbl_A and tbl_B with similar data/rows but noprimary keys.tbl_A is master.I would like this package to query tbl_A and tbl_B and find1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_Athat are not present in tbl_B and3)all rows in tbl_B that are not present in tbl_A, and then just showthose rows.Can this be done with a simple UNION?Perhaps this could produce a temp Table that can be dropped once theDTS package exists successfully.The 2nd part after all the above rows are retrieved is that I wouldlike to add an addional Column to the retrieved data called STATUSwhich has 3 possible values(letters) at the end of each row...M (modified) means that row exists in tbl_B but has 1 or moredifferent columnsA (add) means this row exists in tbl_A but not in tbl_BD (delete) means this row exists in tbl_B but not in tbl_AI'm hopping this DTS package would output a nice comma seperated TXTfile with only...1) rows from tbl_A that are different in tbl_B (STATUS M)2) rows from tbl_A that are not present in tbl_B (STATUS A)3) rows from tbl_B that are not present in tbl_A (STATUS D)Can a DTS package in MS SQL be used to perfom all of the above tasks?I would very much appreciate any help or any advise.Thanks in advance :-)
View 7 Replies
View Related
