I was having interesting discussion on estimation of log file with a fellow collegue who happens to be quite knowledgable as well.
He told me if we identify the most frequently hit tables for a database and then (sum their sizes * 1.5) for OLAP we get rough estimate for disk space to be allocated for log file.
How to estimate the size of datafile while creating the database?
View 2 Replies View RelatedI have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
Getting details of below
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
Hello,how can I estimate the size (KB) of a temptable?Thank Yousilas
View 7 Replies View RelatedI have a table like below,
CREATE TABLE Student
(
Id BIGINT not null
,Name NCHAR(20) not Null
,Branch NVARCHAR (64) null
)
The table contains : 100000 rows .
1)Number of rows in a data page
2)Total number of pages required for the table
3)Total Table size in KB or MB
4)Total file size in Kb or MB
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.
Here are my scenarios:
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
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 RelatedHow SQL calculates the estimated row count when < or > are used in the WHERE clause of a statement. For example:-
WHERE Created_Datetime_utc > CONVERT(DATETIME,'2014-10-14 10:00:00',102)
I know how the estimated number of rows are calculated when an = is used but I've been googling and cannot find anything about < and >.
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 Relatedfinding the database size from the backup file.I have SQL 2012 backup file, is there any way to find the estimated database size from the backup.I tried restoring , i got an error saying " no space need additional xxx bytes " ...does this error gives the exact space needed to restore ?
One more question....one of the backup file size is 7.2 GB, when i try to restore it ....it throws error saying it needs 292GB extra space while only 100 Gb is available. How come 392 Gb sized database becomes 7.2 Gb .bak file ?
I have a Problem like the Following ..
On 24th my Mdf size was 10GB,when i checked now the Mdf size was increased suddenly to 30GB.
solution to decrease the Size and as well as where can i check the reasons behind that..
I need to increase the file size for a mirrored database. I am new to using mirroring for replication. Will increasing the file size break the mirror?
View 2 Replies View Related
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 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
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 RelatedWhat 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 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
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.
how long does it take to convert a DTS package to ssis package?
View 9 Replies View RelatedI have a ordersystem with repeating orders. Each order has a orderline
with a frequency and a intervaltype (day, week, month and so on) for
indicating how often a task should be done.
When an task/job is done, an equivalent receipt with receiptlines is
generated.
When a new job/task should be suggested, it has to calculate the
shortest next intervallength for each repeating orderline. This
intervallength must then be added to the date of the last receiptline.
I want to to this all in a SPROC. My result should be the one next
orderline that should be carried out with a date for when this is.
RESULT
OrderID TaskID TaskDesct EstDate
5000 GETGROCERIES Pick up groceries for miss Lama 19.12.2003
************************************************************************
Is this possible to do all in a SPROC and not having to do any in .NET
(which this is developed in), and can you help me with some ideas?
************************************************************************
To se my starting point, se the diagram at:
http://www.promotion.no/div/diagram.gif
My mind is stuck and this is so far I've reached:
ALTER PROCEDURE dbo.GetNextOrders
AS
CREATE TABLE #Interval
(
ivID int IDENTITY(1,1),
ivTaskID char(20),
ivTaskDescr nvarchar(200),
ivHour int
)
INSERT INTO #TemporaryTable
SELECT RepID, RepIntName
FROM RepeatInterval
SELECT InvoiceDetails.iID, InvoiceDetails.ReceiptID,
InvoiceDetails.OrderID, InvoiceDetails.TaskID,
InvoiceDetails.TaskDescr, InvoiceDetails.DateRegistered
FROM InvoiceDetails LEFT OUTER JOIN
Orders ON InvoiceDetails.OrderID = Orders.OrderID
WHERE (Orders.DateStart < GETDATE()) AND
(Orders.DateEnd > GETDATE() OR Orders.DateEnd IS NULL)
to do a database repair on SQL server v7.
This is what I did
dbcc checkdb ('mydatabase', REPAIR_REBUILD) with estimateonly
It gave me error ....
estimateonly is not an option.
What did I do wrong? Thank you for your help!!!!!!!!!!
I would like to ask some help for estimating necessary hardware for a database, I can tell you about usage profile the following:
- 2 tables, approximately 100.000 records, the tables joined in 1:1 relation, one record is approximately 500 bytes long
- typical scenario:
- select what returns 1 or zero record
- if it returned 1 record an update performed on this record
- the event logged in a third table (one insert)
- this typical scenario needs to run 100-300 times in every second
Can you tell me any advice on sizing of hardware for this? We can rely on a well designed database structure (proper indexes, etc...)
Thanx in advance
Hi, I've read that a SQL Server Express database is limited to GB. If I have a database with about 120 tables which store only simple datatypes (int, money, datetimes, varchar, etc.), no blobs or large files, how can I estimate the amount of data or number of rows it can hold before it runs out?
View 11 Replies View RelatedHow to find max pool size in sql server 2012?
View 2 Replies View RelatedI am working on Sql Server 2012. and I have multiple databases there. Out of those, i want to move one of my databases to other SQL server 2012, For that i was trying to get approximate size of my database on current server. As i don't have the admin rights, so i can't get that. Can i get the approximate size by right clicking on database and by using the size property Under Database category to get the size idea?
View 4 Replies View RelatedI have a 2.5TB database that we need to run checkdb on. Is there a formula to figure how log it would take with no options? Doing research, it appears the we are stuck with doing the PHYSICAL_ONLY due to time constraints.
View 7 Replies View RelatedI have a table that contains 1.6 million rows, and is about 2.6GB in size.One of the columns is ntext and contained some xml.Realistically, I don't need the xml for anything older than about 2 months, however, i'd like to keep some of the data in the other fields.I decided that if I updated the xml to blank, I would see some considerable space savings, however that doesn't appear to be the case.This is the output of sp_spaceused for my table
name rows reserved data index unused
CommitmentsForPosting1660979 2740336 KB 1857104 KB312 KB882920 KB
After I updated the table to remove the xml, the output of sp_spaceused remained the same.My first thought was that it was probably statistics, so I updated statistics for the table, and nothing changed.I then updated statistics for everything in the database, still no change.I then ran DBCC CLEANTABLE for that table. I didn't really expect it to make a difference, and no surprise, it didn't.
The index on the table is a clustered index on just a GUID column.I rebuilt the index and again it still made no difference (that's a bit of a lie - index_size changed by a few hundred KB)my next test was to run 'select * into XXX from YYY' to create a copy of this table with the same data and data types. I also created the same clustered index from the original table onto the new one.If I then run sp_spaceused on this new copy of the table, I see what I expect to see in sp_spaceused - a table using approximately 256MB of space
name rows reserved data index unused
SPS_TEST 1660979 266400 KB 266400 KB 304 KB56 KB
To be honest, this isn't hugely critical, but I'm just curious as to why the original table is still reporting 2.6GB size, when I think it should probably be nearer 256MB.
We can find free space on disks with 'xp_fixeddrives'.
I need script to find all disk size(total sizecapacity) in the servers?
I backed up my transaction log file using this:
BACKUP LOG iTest2_AELetters TO DISK = N'nul'(yes I know this breaks the log chain - it's expected in this intance)
But after I run it my t log is exact same size. I have refreshed many times.
I am using SQL Always On on this DB.
I have a 50gb database, with 3 files at the primary filegroup, each one of those has around 16gb I truncated 2 tables releasing 33gb, so the database should have around 17gb now, but when I check at the properties it says that each file doesn't have any empty space
this is on a MSSQL 2012 SP2 CU1