MSSQL DB File Getting Bigger, Can Split Or Reduce?
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 Complete Forum Thread with Replies
Related Forum Messages:
Index File Grown Bigger Than Data File Day By Day
Hi all, I have a serious problem: I created 1 database with 2 file group : 1 primary and 1 index. - Primary file group includes 1 data file (*.mdf): store all tables - Index file group includes 1 index file (*.ndf): store all indexes Most of indexes are non-cluster indexes After a short time using, data file is 2GB but index file is 12 GB. I do not know what problem happened in my database. I have some questions: 1/ How so I reduce size of index file ? 2/ How to know what is stored in index file? 3/ How to trace all impact to index file? 4/ How to limit size growing of index file? Thanks in advanced Thi Nguyen
View Replies !
Log File Size Not Reduce
Database log of my DB is around 2GB.The database is using FULL recovery option.I want to reduce the file size of the log cause it takes up a lot ofspace.I'd do a full database backup, then backup the transaction log as well.... both backup performed with a check on the option "clear inactiveentries from transaction log".But after I backup, the database log is still 2GB.What should I do to reduce the database log file size?Should I use?:==============================Dump Tran databaseName with no_logDBCC shrinkdatabase(databaseName, 30)==============================Is that safe to be used in production server?Peter CCH
View Replies !
How To Reduce The Transaction Log File
Hi all, I know this topic has been discussed in the past, but I still don't quite get it. So please be patient with me database size created with automatically growth of 10% with unrestricted file growth. database size = 5 Gb used 4.5 GB (taskpad) transaction log=8GB used 54MB (taskpad) 7.5 GB free database run in FULL mode full backup nightly, transaction log backup every 30 min What should I do to free up the space that are not used in the transaction log. Thanks for your help.
View Replies !
How Do I Reduce The Size Of The .LDF File?
I am using SQL 7, SP1 / NT 4. The .LDF file has grown to 1.1GIG; I ran a DBCC SQLPerf(LogSpace), the used portion of the log is 2%. When I run a DBCC Shrinkdatabase and DBCC Shrinkfile, the log file does not reduce in size. How do I get the virtual log files that are not active released back to the system? Is there a way to tell if all the virtual log files are active, therefore, not reducing the size of the file? Any help is greatly appreciated.......
View Replies !
Reduce File Groups
I had a database that’s comprised of different file groups and log files spread out among different hard drives. I have recently upgraded the database to SQL 7.0 on a RAID 10 volume. I would like to consolidate all the file groups and files as well as various log files into one primary datafile and logfile. How do I do that? Thanks in advance.
View Replies !
How To Reduce Database File Size.
We have a database which was created with an initial file size of 10 gig. Currently it is only using 2 gigs. We have developers that want to have a copy of the database on their desktops, but do not have 10 gigs free space. What is the best way to get them a copy of the database while reducing the footprint?
View Replies !
Reduce The Transaction Log File Size
hi, i'm a newbie in SQL Server and have recently setup a test SQL 7 server. I used all the defaults at the beginning, and now the MDB file is about 500MB and the LDF file is of similar size. i'm still trying to figure out how to reduce the size of the transaction log file. Currently I only have full backup of the database once a week, and there is no backup for transaction log. as of this moment, the transaction log is of not much use to me, but I really want to get it reduced as i'm running out of disk space. and i'd also greatly appreciate if someone could suggest a good DBA practice on the proper setup/handling of transaction logs (how to balance the disk-space usage AND be able to use the transacton logs for proper roll-over during a recovery process). i'd soon be setting up a SQL 7 server where about 10 active users are expected at any one time. I've read that the transaction log file should be about 40% to 50% of the estimated size of the database file, and should be allowed "auto-grow". So what happens if the more space is required by the transaction logs? Does a full-backup purge the transaction logs (like the way they do in Exchange Server)? Thanks!
View Replies !
Unable To Reduce Transaction Log File Size
HELP !!! I have a 1.2GB transaction log that I cannot reduce. I have tried SHRINKFILE, SHRINKDATABASE, restoring the file into a new data base, etc. The log file is only using 15MB and will not release the remainder to the OS Any and all suggestions would be helpful Thanks, Chad
View Replies !
Split The Existing MDF File Into Mutliple Files As A File Group?
I have a huge MDF File - 120 GB File (Had setup as 1 MDF initially) -- Did not anticipate that the DB would grow to that size!! Anyways.. I heard that the general performance woul grow if i had them as "File Groups".. Is there anyway - to split the existing MDF file into Mutliple files as a File Group? Where should i start? Can someone please direct me..
View Replies !
Split Data File Into Multiple File Groups..!
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
View Replies !
Split The File
my ssis package downloades the text file from Ftp. iT downloades for ex 5 files. I want to split the file to smaller file after downloades. If the size of file is more then 600 mB then I want to split it into 6 files. please suggest if any task in SSIS can perform this or any other way.
View Replies !
Split Txt File Into Multiple
How can I split this incoming file into separate txts. I want to cut out each Header/detail row section into a new txt. What I mean by header/detail row: incoming txt file: http://www.webfound.net/split.txt basically want to cut out each section like this: http://www.webfound.net/what_to_cut.txt http://www.webfound.net/rows.jpg and a kicker...each new txt name must use a certain field (based on x numbers in header row) followed by another field whcih is the date form the header row. somethign like this: SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 59,4) == "HD" + SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 1,8) == "HD" + .txt I need some hand holding here, it's my first time trying to do something so complicated in SSIS 2005. If I can first just get the txt split into multiple, that would be a big help.
View Replies !
Split Single File Over Two Tables.
I have an input file with fixed-width columns that I want to import into two tables.. 5 of the input columns go to 1 table and the remaining 15 go to another table. What's a good way to do this in SSIS? TIA, Barkingdog
View Replies !
Conditional Split On Field In Csv File
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually. Thanks
View Replies !
Can I Split A Long .sql File Into Multiple Files?
I have one really long .sql file I'm working on. It's actually a data conversion type script. It's gotten really cumbersome to work on as long as it is. I would like to split up various logical parts of script into their own .sql file.How can I have one file .bat, .sql or whatever call each .sql file in the order I specify? Hoping this is easy. Thanks
View Replies !
How To Split One Report Into Many File Based On Volume.
Do anyone have an idea of how to split one report (Report subscribed for automatic delivery) into many file based on the volume of the data retrieved (records1-50 first file, 51-100 second file ect.,). Say for example I have an employee and department table. The report is designed to provide a list of employees for a given department. If the department contains more than 50 employees then the report is exported individual file for every 50 employees. Can anyone suggest a way to do this€¦ Regards, Krishna
View Replies !
Reduce The File Size Less Then Original Size In SQL 2000
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
View Replies !
Delete Blank Row In Flat File Destination From Conditional Split
Hello everybody I have one question about deleting blank row on flat file destination from conditional split. I create an SSIS package to filter data from Flat file source. On flat file source, it is Ragged right format and header row delimeter in {CR}{LF} the coulums are devided manulaay using markers. I use only 2 columns divided and send the source into conditional split task and the conditions are given to filter data, when the output from conditional split is placed on flat file destination, i notice blank rows on the output. I want to delete the blank rows so the result data can be displayed continuously in rows. anybody has any idea for this? I know the script task will work but hope to avoid to use script task. Thank you in advance for all the help.
View Replies !
Log Bigger Than The Data???
idp data: 55% full (Total: 4600 MB Used: 2515 MB Free: 2085 MB) idp log: 88% full (Total: 3500 MB Used: 3073 MB Free: 427 MB) Normally the "data" is bigger than the "log" would you say this is a problem and how can one reverse the problem?
View Replies !
How To Set A Bigger Rollback Segment?
Hi, I´m trying to transfer a large amount of data from Oracle 8 to Sql Server. For each table, I have an OleDBSource with DataAccessMode Command (select with one inner join), Data Conversion and OleDBDestination. Sometimes I´m getting an ORA-01555 snapshot too old error, so I´d like to set a bigger rollback segment, but don´t know how. Please, can anybody help me? Thanks Janca
View Replies !
Case Statement With Bigger Than?
Hi How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error. Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish. Code Snippet SELECT Weight.Weight, Height.Height, (Weight.Weight/(Height.Height*Height.Height)) AS BMI, CASE BMI WHEN (BMI < 18) THEN 'Under Weight' WHEN (BMI < 25) THEN 'Healthy Weight' END AS 'BMI Grouping' Any Help will be greatly appreciated Kind Regards Carel Greaves
View Replies !
How To Make The Pie Chart Size Bigger
Hi, anyone can help me on adjust the pie chart in reporting service become bigger? let say the chart area is 5in x 5in, then the pie diameter i need around 4in. but the system only can show around 2~3in regards terence chua
View Replies !
How To Make The Pie Chart Size Bigger
Hi, anyone can help or guide me to enlarge my pie chart? when Layout time i set the chart size around 5in,5in(width and height). and the size of the pie chart at the time is big enough. but once in the Preview mode, the chart become very small (diameter around 2in~3in only). this make many small % of the data can not be differential. i using sql server 2000, RS sp2, .Net 2003. did i need to set certain thing to make sure the pie chart can show at Preview around 4in~4.5in(diameter)(the max chart width and height is 5in x 5in)? regards Terence Chua
View Replies !
Row Is Bigger Than Maximum Size (1962 Bytes)
Microsoft OLE DB Provider for ODBC Drivers error ' 80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Updated or inserted row is bigger than maximum size (1962 bytes) allowed for this table. database:microsoft 6.5 SQL How can I solve this problem thanks,shay
View Replies !
How To Send Variables Bigger Than 256 Characters To A COM Object?
Hi, I am tying to send a string from a stored procedure to a COM object. The string is a SQL statement and is larger than 256 characters (see bellow). When I debug my COM object the SQL has been truncated to 256 characters. Please help! -Carl SQL code: DECLARE @sqlStr as varchar (800) SELECT @sqlStr = "SHAPE {exec spSBSB " + @PRPR_ID + "} as Subscriber APPEND ({exec spSubHomeAdr } as HomeAddress RELATE SBSB_CK TO SBSB_CK) as SbsbHomeAdr,((SHAPE {exec spMeme} APPEND ({exec spMemeHomeAdr} as MemHomeAddress RELATE MEME_CK TO MEME_CK) as MemeHomeAddr,({exec spMemeMedicalEligEvent} as MemeMedEligEvent RELATE MEME_CK TO MEME_CK) as MemeMedEligEvnt,({exec spMemeDentalEligEvent} as MemeDenEligEvent RELATE MEME_CK TO MEME_CK) as MemeDenEligEvnt,({exec spMemeProv} as MemProvider RELATE MEME_CK TO MEME_CK) as MemeProv, ({exec spMemeCob} as MemCob RELATE MEME_CK TO MEME_CK) as MemeCob) as Member RELATE SBSB_CK TO SBSB_CK) as Member" COM code: Public Function getData(ByVal sqlStr As String) As Integer ...
View Replies !
Log Size Balloons During Import, Bigger Then Table
I'm importing about 15 million rows of data from an access file to an MSSQL database. Some of the fields in the Access file are of DataType "text". The destination fields in the SQL DB are of type varchar(50), and none of the text fields in the access file actually use anything other then English characters. I put in a "data conversion" item to handle the switch from "text" (which usually trys conversion to nvarchar by default) to varchar. The import works, and the resulting table ends up weighing about 1.2 gigs. HOWEVER, the log itself is a crazy 7-8 gigs heavy. I have no idea why the log size bloats this much. I can backup/shrink later in the package, but this 8 gig could easily push the hard drive over its limit at some point before completion and I'm looking for a better alternative. Database is on "simple" recovery mode. The combined size of the db, before the operation, log + data, is maybe around 5 meg. Incidentally, I tried with out the intermediate data conversion step, and a similar thing happened - log finishes up about 7 gig, table is 1.2 Seems ridiculous that the log should grow faster then the table. Any ideas why?? --------------------------------------------------------- SSRS Kills Kittens.
View Replies !
Export MSSQL Db To Csv File?
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 Replies !
MSSQL To Text File?? How?? Thanks...
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 Replies !
MSSQL 7 Log File Resizing
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 Replies !
Scheduling A .vbs File Under MSSQL 7.0
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 Replies !
Make Subtotal Column Widths Bigger Than Normal Columns?
The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.
View Replies !
Restore MSSQL BAK File By TSQL
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 Replies !
MSSQL Database File Question
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 Replies !
Import Text File Into Mssql
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 Replies !
MSSQL Log File Is 210GB And Growing
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 Replies !
FILE DSN On UNIX To Connect To MSSQL
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 Replies !
|
TRACKER
