Increased Backup Size For Unknown Reason
May 25, 2004
Does any one know what could be making the size of a backup get so big in SQL server?
I noticed if you back-up the same database in Enterprise Manager over and over (without making any changes to the database), the size of the backup gets bigger and bigger. To get around this I simply erase the backup and create a new one.
Now I'm experiencing the same kind of problem, different situation. I decided to make very few changes to my database. If anything, I shrunk the size of the tables and stored procedures.... Now all of a sudden my database backup is 7 times larger.
What could be increasing the size so much, if I haven't increased the amount of tables or stored procedures?
What is the log file about? Mine is huge? Is there a way to reset it or clear it?
any help would be great.
Thank you,
Alec
View 1 Replies
ADVERTISEMENT
Oct 25, 2006
I have been working on an app for several weeks. My database size went from 9MB to about 12MB. Sometime in the last 2 days it jumped up to 242 MB. I don't know why. Now I cannot open it with management Studio Express. It says another process is using it. Usually this means I have a aspnet_wp.exe process running (from Visual Studio) and I just end the process, but Wndows Task Manager will not end the process. It does knock its size way down to 2.9 MB but won't kill it. Rebooting won't kill it either.Are these two symptoms related? How do I recover?Thank you.
View 1 Replies
View Related
Jul 20, 2005
Hello....Can someone help me please?I had a mdf file with 48 Mb...and suddenly, the size of it increasedto 690 Mb (without any action or movement of data)...Why could it be done?....is there any solution for thar?Thank you.Tony
View 1 Replies
View Related
Jun 3, 2008
How to decrease msdbdat.mdf size? This is a bit urgent and it would be great if anyone can reply on this.
Gaurav Arora
View 11 Replies
View Related
Apr 14, 2015
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.
View 0 Replies
View Related
May 28, 2008
Database size increased from 600GB to 1000 GB in 4 hours. There was no process running. I am not able to understand why would database size increase in 4 hours.
Does anyone know how should I trouble shoot this?
View 2 Replies
View Related
Feb 20, 2008
Hi,
I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.
When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?
I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.
Any reasons?
View 6 Replies
View Related
Aug 9, 2006
I have a SQL Server 2005 server with no maintenance plans configured and no SQL agent jobs. The SQL logs are indicating that every night around midnight the databases I have created are all taken offline and backed up. How do I determine what is triggering these backups?
View 1 Replies
View Related
May 1, 2008
Hello everyone.
First off, I know little to nothing about SQL. I run the IIS web server and the programmers run the SQL server. Recently the programmers found a backup running everynight at midnight and its messing up the Differential backups. By that I mean, since the full backup runs, the differential isnt backing anything up since it is set to only backup from the last full backup.
The backup seems to be backing up to a "Virtual Device" and I have no idea what that is. I am unable to see any user associated with the backup and I'm at a total loss as to where the data is going. Can someone please help me resolve this issue. I searched but was unable to find anything as I have no idea what to search for to find where this backup is coming from.
Here is a log from said backup...
Database backed up. Database: dbname, creation date(time): 2008/03/10(03:40:58), pages dumped: 1, first LSN: 288:523:56, last LSN: 288:547:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{B9A0EFAB-8894-4BBC-BC77-82DA1B58E2D4}28'}). This is an informational message only. No user action is required.
Lastly... The programmers do have backups running.. full once a week and differentials inbetween. the problem is, those logs look like this...
Log was backed up. Database: dbname, creation date(time): 2008/04/09(03:32:53), first LSN: 135:305:1, last LSN: 135:305:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:ackup imeanddate.BAK'}). This is an informational message only. No user action is required.
Thank you in advance!
View 10 Replies
View Related
May 1, 2007
Hi,
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
Thanks
View 10 Replies
View Related
Jul 20, 2005
This is what I want to do:1. Delete all tables in database with table names that ends with anumber.2. Leave all other tables in tact.3. Table names are unknown.4. Numbers attached to table names are unknown.5. Unknown number of tables in database.For example:(Tables in database)AccountAccount1Account2BinderBinder1Binder2Binder3.......I want to delete all the tables in the database with the exceptionof Account and Binder.I know that there are no wildcards in the "Drop Table tablename"syntax. Does anyone have any suggestions on how to write this sqlstatement?Note: I am executing this statement in MS Access with the"DoCmd.RunSQL sql_statement" command.Thanks for any help!
View 2 Replies
View Related
Oct 30, 2007
Why is it that lately our full backup (.bak) file size is more than 2x the size of the actual database file size (.mdb)?
View 9 Replies
View Related
Feb 13, 2008
My database is of 10 gb, when backup is taken the size of .bak file is less than 10 gb.
Why?
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 25, 1999
I need to know how big SQL Server 7 backup files are in comparison to the database size. For example if I have a database that is 300 Mb and I do a complete backup to disk with SQL Server 7 will the backup file be about 300 Mb?
Thanks,
Mike
View 2 Replies
View Related
Feb 4, 2004
Hi all,
How can I see what is exact size of SQL server backup file while backup is running. The process is running for more than 2 hours, it is chewing up disk space (it already took about 20G), but the size of backup file is still showing as 0.
Thanks in advance
View 7 Replies
View Related
Dec 12, 2007
what will be size of backup file(FULL) for 1TB database. Just wanna make sure backup is done right.I did a full backup of 1TB database and backup file came like 507GB. I am using GUI for backup.But it completed 100% but didn't give me the screen'The backup is completed successfully' .it says executing---0%
View 5 Replies
View Related
Jul 20, 2005
I have a MS SQL 2000 dB that is 3GB, the transaction log is about 2GB.I do a full dB backup every night. My question is, shouldn't thetransaction log shrink down to next to nothing after a full dB backup?D.p8oust7eh+
View 2 Replies
View Related
Jul 20, 2005
Hi,I want to setup one of the fields in a table so it incrementssequentially(int data type). i.e the first record should be record 1and the second one should be 2 and so on. This field will also be thekey field. I am new to SQL and don't know how to do this.I am using SQL server 2000.Thanks for the help in advance.-S
View 9 Replies
View Related
Jan 8, 2001
I recently started using Differential backups. They are working but are growing in size a lot quicker than I expected.
The backups are growing by 2.5GB every day although the total size of all
transaction backups is under 350MB. I would have imagined that the total transaction log backups would be a good indicator of total database changes and therefore the differential backups would approach this figure.
Please explain!
View 2 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
Feb 13, 2004
Hi all,
please show me the way how to calculate the size of a database in SQL 2000. Whith this size we have already calculated, how much space need to make a backup file for this database.
thanks.
View 5 Replies
View Related
Apr 18, 2008
I have installed the sql server management studio express for my express edition of studio 2005 for backup purposes.
I need to automate backing up my database.
I created a backup script and have successfully got sched tasks to backup each night. the problem is it is not overwriting the previous data. My first backup was 1 gig, the next night the file was 2 gig, the next it was 3 and so on.
This is not due to that much data being added to the db.
When I go to restore, I have the option to restore any of the 3 backups. I would like my backup to be a single image of the database at the last backup time. How can I do this?
View 4 Replies
View Related
Jan 9, 2008
hello guys,
Please bare with my english.
Is there a way to compute for the backup file size of the database.
Example.
DatabaseName
TEST_DB
*in MB
DataSize DataSize_Used DataSize_UnUsed
150.00 81.00 70.00
LogSize LogSize_Used LogSize_UnUsed
30.00 1.00 29.00
When I run a backup for TEST_DB ,
*base on this figure - can we compute the the estimated backup file size for TEST_DB?
View 2 Replies
View Related
Jun 28, 2006
Has anyone come across a more accurate method than sp_spaceused to estimate the size of a full database backup for SQL Server 2000 ?
I have found this to have too great a variance (even after running updateusage) to rely on any accuracy for it. I have also looked at perhaps using the ALLOCATED Pages indicated in the GAM pages but this also seems to be pretty inaccurate.
I have a number of servers where space can be limited and backups using Maintenance Plans have occasionally failed because they delete the old backups AFTER they do the latest one. I am writing a script which can check the space remaining and adjust the backup accordingly but the variance I have observed so far with sp_spaceused is too great.
Any ideas welcomed.
View 4 Replies
View Related
Oct 15, 2007
Dear All,
I am using Append to media backup option in 2000 Version. The size of backup is growing. how can I best create the maintenace plan to clear the history or clear the old files in BACKUP (.bak) file but still be able to restore point in time from same physical file. I
Is there any side effect of doing that.
Thanks,
View 10 Replies
View Related
Jul 20, 2005
Hi, recently we increased our internal memory from 2 to 3.5 GB.Nevertheless, SQL Server does not seem to use this extra memory.Do we have to do something extra ???Arno de Jong,The Netherlands.
View 1 Replies
View Related
Aug 14, 2015
Yesterday night, I rebuilded all indexes one a table since they were having high percentage of fragmentation . After rebuilding, I immediately checked the fragmentation percentage and it got reduced and I felt happy. However, when i saw the percentage of fragmentation after 6 hours, it increased from 1% to 48%. How to find what caused the increase and how to fix it.
View 36 Replies
View Related
Apr 18, 2000
I have got SQLv6.5 SP5a with SMS1.2 SP4 on seperate Alpha boxes. I have automated the backups so they are scheduled for after hours. SMS gets backed up first and TEMPDB shortly afterwards. However, since a back log in SMS MIFS has happened, the TEMPDB backup displays of 100,000pages backed up. When you back it up on its own, it only shows 170+ pages.
The SMS DB is 600MB in size, the Log is 210MB, Open objects is 5000, and TEMPDB is set 210MB on its own device.
Any ideas
View 1 Replies
View Related
Jan 3, 2007
I'm having a problem. When I use the SQL query to make a backup of the database, it worked fine. But everytime I use it, the backed-up file's size kept growing in size. Say I have the file, test.bak whose filesize is 450 MB then I run a new backup to overwrite the existing test.bak file, it just end up as 900 MB. If I run it again, it become 1350 MB and so on.
Is there a way to prevent that from happening?
View 1 Replies
View Related
Apr 21, 2014
finding 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 ?
View 5 Replies
View Related
Jul 13, 2007
I have a question regarding FUll and differential backup.
We we take full or diff back up, does it create lot of logs ie. Does full or diff backup has any impact on log size?
Thanks
View 5 Replies
View Related
Jan 9, 2004
i have a field Count that i want it to be automatically increased by 1 whenever a SELECT stament is called. i come out with the following sql query and it works on SQL Query Analyzer:
declare @count int
select @count = count+1 from pictures_posts where itemtype='3'
update pictures_posts set count=@count where itemtype='3'
select postID, color, count from pictures_posts where itemtype='3'
go
but i am thinking it must be a much better to do this. can you guys help?
View 5 Replies
View Related
