DB Engine :: Recommended Size And File Growth For A Database And Log File?
Sep 22, 2015
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
View 3 Replies
ADVERTISEMENT
Mar 3, 2008
i have a database which has a log file size of 10 Mb. it goes into single user mode automatically . i tried to increase the size of file size of log file from 10 mb to 50 mb... but i want to make it only 20 mb ... i am unable to change since it gives a message .cannot decrease the size of the file .. is there another way to decrease the size of log file .....
View 2 Replies
View Related
Aug 10, 2007
We have a problem with the size of the tempdb.mdf file. The tempdb had grown to 25Gb and consumed all the available disk space. SQL server was restarted and the tempdb was reset back to the default size. The following day the tempdb suddenly increased in size from 200mb to 25GB within a very short space of time. There were a couple of event log entries from sqlservger regarding the lack of disk. Since then the server is running without any problems but the level of free space is virtually zero on the drive with tempdb.mdf file.
What would cause the tempdb to grow suddenly and to this size?
Can I control the size the tempdb can grow to?
SQL 2005 (x64) sp1
W2K3 R2 SP1
View 4 Replies
View Related
Nov 9, 2015
I 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?
View 10 Replies
View Related
Mar 8, 2004
Can someone point me to examples of database & file growth monitoring.
I specificially want to monitor a number of separate SQL servers (2000, 7.0)
I want to end of with statistics of any size changes on any of these over time.
Help is greatly appericated..
thanks
View 5 Replies
View Related
Jan 11, 2007
SQL Server 2000 | Transactional Replication
Suspected Problem: Distribution Database Transaction Log Not Checkpointing
I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.
I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.
SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.
Any thoughts?
Jeff
View 1 Replies
View Related
Oct 7, 2015
 I currently have a DB that is growing at a rate of 10gb per month. It is set to 1mb unrestricted growth and the log file is set to 400mb restricted growth. I take regular transaction log backups so the log file is well under under without any issue. This DB's recovery model is set to FULL as it has to be mirrored to a backup site.  Any recommendations on how to control the growth. - Is it advisable to take create a new DB with data older than 2 years and transfer that file to an external drive and if i do this, can i "attach" it back to the main server if and when required ?
View 7 Replies
View Related
Apr 22, 2014
We have a large 'History' database that is currently about 4.5TB, with most of that in a datafile that is 4.2TB. We wanted to stop growth on the one large data file and have SQL Server allocate new data to the other data files, but this throws an error when we attempt to change the MAXSIZE settings:
ALTER failed for Database 'History'
MODIFY FILE failed. Specified size is less than or equal to current size.
The SQL Server is saying we can have a max size of 2TB, and anything over that is blocked. Since this is being blocked, the file continues to grow.
Is there any way to cap the growth of the 4.2TB file and not allow any more data to be written to it?
View 1 Replies
View Related
Sep 2, 2007
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
View 3 Replies
View Related
Apr 22, 2015
Wanted to do the forecasting of disk growth for one year. How to find the database growth rate?
View 4 Replies
View Related
Apr 23, 2001
I know I've seen documentation on this but I can't find it at the time. What's the recommended file locations for a SQL install.. System and Data on a RAID drive and logs on a separate drive that's mirrored..? Oh and if anyone has links to this info let me know also.
Thx!
View 4 Replies
View Related
Sep 23, 2002
:eek: I am somewhat confused -- I have a database in production that I restored to a QA environment; upon restore, the size has grown by 200MB.
Both production and QA are running SQL2000 -- the only difference is that QA has the latest security hotfixes installed -- version 8.0.0.665 from KB article at the following link:
Q316333 (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316333&id=Q316333)
View 3 Replies
View Related
Aug 25, 2015
I am only DBA in my company and client want to know the growth rate of his SQL server DataBase which is in production. How can I get the growth rate per day?
View 3 Replies
View Related
May 31, 2005
My DB size was from 500MB to 10GB since 8/1998 to 12/2004. But now is 16GB (from 1/2005 - 5/2005), I don't why the data size growth too fast (as double) ?
View 4 Replies
View Related
Mar 11, 2004
Hi All,
Database size is not increasing automatically ,however I have set it as unlimited growth. Any idea about this ?
thanks for in advance,
Sedat Duztas
Probil
View 1 Replies
View Related
Jun 25, 2015
One of our database came to Restoring mode. I suddenly stop my SQL service and Copied only MDF files again Started SQL service ,unexpectedly i dropped the Database. Now i cant able to attach the database only with my MDF file.
I tried below Scripts. All scripts shows same error.
EXEC sp_attach_single_file_db @dbname='PhoenixPolice',
@physname=N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf'
GO
CREATE DATABASE PhoenixPolice ON
  (NAME = N'PhoenixPolice',
  FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf')
FOR ATTACH_REBUILD_LOG
GO
CREATE DATABASE phoenixPolice ON (FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf')
FOR ATTACH
All scripts shows below error
File activation failure. The physical file name "F:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAPhoenixPolice_1.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'PhoenixPolice'. CREATE DATABASE is aborted.
View 4 Replies
View Related
Apr 15, 2008
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
View 8 Replies
View Related
Jun 7, 2015
I have to perform disk maintenance on current drive - Drive 'D' where it has sql data (mdf file) and I have added new drive - Drive 'E' By the way Drive 'C' have the program files for SQL Server 2008 R2 What is the correct process to transfer sql data (mdf file) from Drive 'D' to Drive 'E' and later remove Drive 'D' from the server.
View 12 Replies
View Related
Jun 11, 2015
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC
2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original
3) Took the new database offline
4) deleted the MDF and LDF files of the new database
5) put "MyDB_0.LDF" in the place of the LDF file I just deletedÂ
6) put the database back on-line
7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)"
8) tried to do Tail Log Backup with this command
  BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak'
WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3
Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
View 13 Replies
View Related
Jul 20, 2007
I have a Database which when I Right Click and go to Properties size is 52 GB
But the Size of MDF + NDF Files is 25 + 7 = 32 GB. Log file Size is 20 GB. So I am thinking -- Properties Size of DB includes size of Log Files too -- is that correct?
But when I do a Full Backup the .bak File Size is 26 GB -- does the Full Backup Shrink a DB ?
I thot Full Backup only Shrink the Log Files and could not find anywhere in BOL where it says BACKUP shrinks the empty space in Database -- can somebody confirm this?
View 3 Replies
View Related
Sep 4, 2007
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?
View 1 Replies
View Related
Apr 23, 2015
I have a file in Fire bird Database (30 GB with .ydb extension). Â which needs to be restored to SQL Server. I Have created a linked server and done it but it is taking very long time to update the records.
View 8 Replies
View Related
Jun 30, 2015
Can we add multiple log file in existing database?
View 8 Replies
View Related
Jul 31, 2014
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.
View 1 Replies
View Related
Oct 27, 2004
How does backup database command works? I don't see size of database backup file increasing while backup is in progress OR is it locked till the backup is finished.
Thanks.
View 1 Replies
View Related
Mar 9, 2005
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 6 Replies
View Related
Aug 28, 2005
Hi,I have set the DB to auto grow by 30 %. As well I have set it tounrestricted size.... However , I see the available size continually beingreduced to now less then .54 MB... Why is there not enough available ?G
View 3 Replies
View Related
Apr 20, 2007
Hi,
I am facing problem of memory size of database file.
I am using SQL Server Express Edition for my application which will log point values after every second.
I have two table in my database
PointValues1_500 with the following columns
DateAndTime of type DATETIME with cluster index
Val1, Val2, Val3 ----- Val500 of type NUMERIC(18,6)
PointValues501-1000 with following columns
DateAndTime of type DATETIME with cluster index
Val1, Val2, Val3 ----- Val500 of type NUMERIC(18,6)
According to specification provided by microsoft DATETIME takes 8 bytes and NUMERIC(18,6) takes 9 bytes and 132 bytes per row for internal usage. So, it will take 4710 bytes approximately per row including index (if i am right).
When I put 60 rows in each table data file size is increased by 1 MB which should be 4710 * 60 (rows) * 2(tables) = 0.6 MB approx. I this scenario i am losing 0.4 MB memory that will cost me a lot if i have point values for 1 hour = 3600 rows per table.
Please give me a suggestion how to overcome this lost.
Best Regards
Haseeb Ahmad
View 2 Replies
View Related
Jul 19, 2007
Hi all,
How to derease the database file size in Primary filegroup.
Thanks,
View 5 Replies
View Related
Jun 26, 2006
Is this possible to connect do the Database Engine (on sql server 2005 on XP platorm - file *.mdf (not mobile *.sdf)) from win CE 5.0?
I tried to do this :
ConnectionStringSQLServerCE = "Data Source=WORK_STATION\SQLEXPRESS;Initial Catalog=dbMachines;Integrated Security=False;Password=Panel;User ID=Panel";
SqlCeConnectionCE = new SqlConnection(ConnectionStringSQLServerCE);
SqlCeConnectionCE.Open();
but I catch error:
catch (PlatformNotSupportedException ex)
€žPlatformNotSupportedException€?
I noticed that I see server because when I use:
ConnectionStringSQLServerCE = "Data Source=WORK_STATION\SQLEXPRESS";
zwraca błąd typu
catch (SqlException ex)
€œLogin failed for user ''. The user is not associated with a trusted SQL Server connection.€?
I used
using System.Data.SqlClient;
from Compact Framework 2.0 under Visual Studio 2005 Device Application Windows CE 5.0
can anyone help me?
View 1 Replies
View Related
Nov 10, 2015
Is there any possibility to override database audit specification file. suppose i want to change some logs forcefully . is it possible ?
View 5 Replies
View Related
May 8, 2015
I checked quickly one network  and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
View 6 Replies
View Related
Aug 18, 2014
Executed as user: S233683-AD01S233683NJ3SQL05. ...at file already exists.' [SQLSTATE 42000] (Error 22048) Â ERROR -- Could not backup database: master - BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 50000) Â xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'
View 2 Replies
View Related
