I have a database whose log file size is 4 time greater then data file size, and its continuously growing day by day. Recently face limited disk related issue.
Is there any way to truncate log file???
What is impact on db if i truncate log file???
Is there any way to prevent this file continuously growing???
Hi,
i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns
dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB.
i try to write this a got stuck.
select sysdb.database_id,sysdb.name,sysdb.compatibility_level,
sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster
where sysdb.database_id = sysmaster.database_id
can anyone help me with this script?
THX
I have a log file that is approximately 50 GIG. I backed up just the log and the file size of the .bak is 192 GIG . Why is this? Shouldn't it be closer to the 50 GIG.
Normally I wouldn't let log grow this much. But we are in process of getting new server up and running and don't have backups going yet. They are working on getting that up and running this week.
So I did a log backup to give me back some log space for now but was concerned when I saw the size of the .bak file.
When I view media contents of the backup device it shows one tranaction log back up and size of 192 GIG.
What is up with this. I know in SQL 2000 the log backup files where never this big. they were about the size of the log itself.
Any ideas?
Stacy
SELECT size_in_mb,used_size_in_mb,size_in_mb-used_size_in_mb as free_in_mb FROM (
SELECT cntr_value/1024 size_in_mb ,
(SELECT cntr_value/1024 FROM master..sysperfinfo WHERE counter_name='Log File(s) Used Size (KB)' AND instance_name='mydb') used_size_in_mb
FROM master..sysperfinfO WHERE counter_name='Log File(s) Size (KB)' AND INSTANCE_NAME='mydb'
) a
I need to store totalsize,usedsize,freesize of the datafiles in a table to get an average of how much my datafile has increased over a week.
The above query i am using is for logfile size. Can any one help me with datafile size plz.
I've checked sp_helpfile, sysfiles but couldn't find what i am lookin for(used and free space). EM in taskpad view for a database shows the statistics for the datafile. I've tried a trace to find out a stored procedure but couldn't!!!
May be i am unaware of a simple stored-procedure that can do this for me.
Howdy!
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
how can i change the initial size of the data and log file size ??? in my database properties it shows that my data file size is 81 mb and log file size is 985 mb! but my database only contains some tables and stored procedures with few rows of data in each tableand i checked that the actual mdf and ldf files are really that big... i tried to change it but it didn't work...can someone please teach me how to change it thanks!
View 2 Replies View RelatedHello Guys,
Just wondering if someone can help me decrease the size of mdf and ldf files. In the past production database "NewUniverse" had been allocated space of 100 GB for mdf file and 8 GB of ldf file. However the data file has only used 30 GB of data. But now due to disk space related reason, I tried to decrease the datafile size from 100 GB to 40 GB. But I am not able to do it.
Any help in this regard would be appreciated.
Hi All,
Is it possible to increase the size of data file in SQL Server 2005 Express Edition.
I think the licensed limit is 4096MB whcih i am unable to increase.
Could anyone let if is it possible to increase the data file size and if yes then how?
Thanks,
Varun
I'm new to the DBA world, and have no one else in the company to look up to. Does anyone know what I might need to check out or do when the Data File Size is 204% full? Or is this not necessarily a bad thing?
I'm getting this from a Diagnostic tool I have.
The number of tables is 148
Data file size 35,941 MB
Data Size 26,549.92 MB
Index Size 177,130.02 MB
Log File Size 5.05 MB
Thanks,
I am looking to automate monitoring space used for each file in eachdatabase on a SQL Server 2000. Does anybody have any SQL Scripts to dothis or to find the space used?
View 1 Replies View RelatedHi All,
Is it possible to increase the data file size in SQL Server 2005 Express edition?
If yes then how?
Thanks,
Varun
What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy
ONÂ
PRIMARY
( NAME = Dummy_data,
  FILENAME = 'D:....DATADummy.mdf',
  SIZE = 250MB,
  FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
  FILENAME = 'D:....DATADummy_log.ldf',
  SIZE = 50MB,
  FILEGROWTH = 5MB ) ;
GO
I've set up an alert to email me whenever the database is over a certain size. The amount is calculated by taking 80% of the total data file size. The problem I'm having is that it keeps generating a false positive alert because MS SQL seems to treat the currently used value as the total allocated space for the database data file. For example, the data file is 100MB, it's currently using 60MB, and if I enter 80MB in the alert, it generates an email alert claiming the current size is 100MB. Could it be because the data file size is set to 100MB (since autogrowth is disabled)?
View 4 Replies View RelatedMy primary (and only) data file has reached the point where it is auto growing. I would like to grow this file in one big chunk at an off peak time. I can't seem to find the code I need to make the file grow when I want it to?
View 1 Replies View RelatedI encounter one weird problem, I have a database with around 7 GB ...when I delete a bunch of data from it, it suppose to reduce thedatabase file size, but weirdly, the file size increase to 8 GB.Wondering why. Is it suppose to be like that?Is it the architecture is designed to work like that?Is there any way for me to reduce the database file size?Thanks.Peter CCH
View 2 Replies View RelatedHi
I am trying to track the size of my SQL Server database. Could someone please tell me if the "Data File Size (KB)" counter of the "Databases" performance object of SQL Server actually gives the size of the database?
I dont know if i have posted this query in the correct category, sorry if i haven't, but could not find any suitable category for this thread.
Thanks in advance
is there limitation for size of file to store in db by filestream in sql server 2008?or it accept all sizes?
View 1 Replies View RelatedI have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space. Â This is because we have removed a large amount of old content from sharepoint. Â The log file is fine. Â I have 60GB left in my drive that host the database files. Â I would like to shrink the datafile to get disk space back. Â I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so). Â
When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping. Â So i have to kill the process. what i should do to reduce this data file.
why it keep using up all the free space in the drive when i try to shrink the data file?
We have 2 SQL Server 2k5 servers running the same build - 9.0.2047 . When I backup any database from one server and attempt to restore it to the other, the log file generally increases by 100 fold. It errors out after I try to restore a 100MB db and it tries to create a 9.8GB log file. This happens both when I use the GUI to restore and when I restore from a T-SQL script. What am I doing wrong?
Thanks in advance.
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file
How to estimate the size of datafile while creating the database?
View 2 Replies View RelatedNeed to confirm if we can add space(increase data file size) for the database which is configured for always on similar to that of mirroring or we need to follow any different procedure.
I have a requirement wherein the datafiles on both the primary and secondary replica got full, if i add space to the primary database will it automatically get added to the secondary replica or not?
I have a SSIS package that opens an xml file, puts the contents into a string, then runs a stored procedure that dumps it into an xml column in a table. One of the xml files is huge. Putting the data into a ssis string causes an error. The length of the string variable is 58,231,886. The file will only get bigger.
How else can I get this data into a SQL Server XML field.
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.Â
View 3 Replies View RelatedGreetings:
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
Regards,
Derek
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.
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 1 Replies View Relatedis there any way i can make a field in a table accomodate more than 1023 charcters? i used the 'varchar' datatype and used a length of 2500, but still, I can't fill up a field with more than 1023 characters. Is there any way to change it?
Also, is there any way to used a symbol or special character in a field? Can SQL server identify such a character? like the alpha or beta symbol...
Thank you...
I was getting the follow error message when trying to update a
table from another table within a stored procedure:
Server: Msg 511, Level 16, State 2, Line 1
Cannot create a row of size 8455 which is greater than the allowable maximum of 8060.
The statement has been terminated.
The row size did not exceed 8060......
Also, If I make it an 'Insert' instead of an 'Update' it works.
It will Insert the data into table.
Any ideas?
Thanks.
how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies View Related