Hi
I have a SQL2k db ABC with only 2mb data but around 500 log. I did the followings
----------------------------------
backup log ABC with truncate_only
dbcc shrinkdatabase(ABC, 10)
Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 58768 128 58768 128
sp_helpdb ABC
name size maxsize growth usage
-------------- ------------------ ------------------ ------ ---------
ABC_Data 1728 KB Unlimited 10% data only
ABC_Log 517184 KB Unlimited 10% log only
question:
1. what is the negative -0.51 MB for unallocated space?
2. I can't kill all the process logins, is there other way to shrink the database log side?
A few of the databases I have are take up a lot of space, but when I look at the actual space that's being used, it's very small. I've tried shrinking them but they still remain at this bloated size. Is there any other way to reduce their size?
I am in the process of copying database from large size to smaller size one. The actual data in the database is very less. When I try to shrink the database, I am getting message I can't shrink the database.
Can any one suggest on 'How to copy this database?'
I have SQL Server Server Man Studio Express 2005, currently having a problem with an auto populated field.
Basically I have a webpage that when I create a new item it populates a new ID, which is the primary key and read only when I go into the database backend so i cannot manually change this.
When I have been doing some testing adding fields to the table and deleting this has obviously given me new ID's such as 113, 114 etc. But on my live site I need these ID's to be sequential, so on my live site the last ID is 108, but now when I add another item the ID is 115 because the number between this and 108 have been taken up.
So my question is really do I need to shrink my database or files to take this down to 109, if so is database shrink or file the best to do? Also i have had someone add a new live item and this has been assigned 113, would I need to delete this, shrink and then re-enter?
Does database shrinking require all requisites that are necessary for compacting (like additional free space and closed database) ? And is there any programmatic way to estimate optimized database size regarding current database size, like it was done by special tool in SQL Management Studio?
I wrote a query that caused a HUGE amount of stuff to be written to the transaction log. Since I set the database up before I had enough coffee yesterday, I didn't turn on a "Restrict Filegrowth" on the log. So the transaction ran until it filled up the available space on the drive (my local workstation, so it grew to about 6 GB) and then it rolled back. (BTW: Microsoft finally figured out that rolling back a transaction shouldn't be a blocking operation. ISQLW tells you that the transaction failed as soon as it fails, and then releases the connection to you, so you can go on with your life while SQL Server cleans up. Good one!)
OK. So that done, I figured I'd just truncate the transaction log and do the nifty new "DBCC SHRINKFILE()" thing. So I truncate the log and do DBCC SHRINKFILE. Nothing happens. Enterprise Mangler (OOPS Manager. I really mean Manager) shows that only 43 MB of the 6 GB file is in use. DBCC SHRINKFILE reports that the minimum size is 128 pages, the current size is 697,256 pages, and 697,256 pages are used.
Great. So I can't shrink the file.
Step 2: I dump (OOPS, sorry, BACKUP) the database, delete the database, make sure all the files are gone, and then restore the database. It re-creates the 6 GB file, which, by the way takes a very long time. What's funny about that is the query timer in ISQLW reports that the query took 30 minutes, but the return from the restore command shows that it took about 300 and some seconds (about 5 minutes) because the restore command doesn't count the amount of time it took to build the files (I'm guessing). After I figure out that it rebuilt the 6 GB file, I screamed, and started downloading PostGreSQL for my Linux box, and got on to other projects.
This morning I came in and started reading Books Online to figure out what's going on. It says something about "Virtual Log Files" and how a log can't be shrunk past that point. Great. MS basically defines a virtual log file as "the point past which you can't shrink a log". So I have a 5 GB virutal log file, and I can't truncate it, shrink it, or make it go away.
So I have a stroke of genius and decide to build a new log file in the database, and then use the DBCC SHRINKFILE command with the EMPTYFILE option, and then use ALTER DATABASE to remove the file.
Then I get this really cool error that says: Server: Msg 5020, Level 16, State 1, Line 1 The primary data or log file cannot be removed from a database.
OK. Last time I checked, a log file doesn't belong to a primary filegroup, so there's something else going on here. Basically, it looks like the first file that gets created is the "Primary" file and can never be removed.
So, new policy, every "first" file in a database is going to be a 2 MB file, with a 2 MB growth limit, so we can remove it later. That's a load of....fertilizer.
It looks like the AutoShrink for logs is just a myth. Auto-Grow seems to work almost too well, though. I'm picturing one of those Access newbies using the Export function in Access to put data into SQL Server on one of our pre-production boxes, and having a 180 GB log that can't be shrunk. That'll be a good time.
The moral of the story: Always set growth restrictions, especially on log files.
The questions: 1. Anybody got any bright ideas on how I can get my disk space back WITHOUT using BCP (or DTS, or similar methods)?
2. Anybody know how a different file can be set as a "PRIMARY" file?
3. Anybody know why MS decided to fill the Transact-SQL help in ISQLW with "You can't get there from here" messages that reference Books Online?
Thankfully, this isn't anywhere in our production system, and if the quality continues this way, it won't ever be in our production system.
What does the shrink command exactly? I start the shrink command in the Enterprise Manager of SQL7/2000. Does it include re-indexing of all tables? And is it the same command as auto-shrink can be selected in the db properties?
there is no transaction in this ABC database. Is there way to shrink it? Not quite sure about "because requested size (113792KB) is larger than the start of the last logical log file.". The current log size is 113 mb. thanks -D
When i try to shrink the database, i am getting the following error: Cannot use the PAGE granularity hint on the table "dbo.salary" because locking at the specified granularity is inhibited. DBCC SHRINKDATABASE: Heap page 1:422421 could not be moved. The statement has been terminated. DBCC execution completed. If DBCC printed error messages, contact your system administrator. (.Net SqlClient Data Provider)
First of all thanks for taking interest in this thread.
I have database in Microsoft SQL Server and its size is appx. 427 GB. It consists of data from 2006. Everyday I expire data of 2-3 days and then try to shrink database as much as possible.
But it frees only 2-3 MB after 7-8 hours process. And in that database there some files which do not shrink even I put one of them on shrinking for whole day.
And as per company policy, I am only allowed to expire data which are 15 months older.
I am facing very much problem as my daily import and publish activity some times got failed for that reason.
If anyone knows the solution please tell me the solution.
hi all,This will be a easy question for all out here.I have a database of 28GB.having 3 Data Files 22 GB, 3.58 Gb and 2.70 GB respectively.and a Transaction Log file of 156 mb.When i executed DBCC Shrinkdatabase(databasename),it reducedsize of datafile but the LOG file had gone up to 5 Gb from 156mb.I want to know why this happened and how should i shrink Log File orany other option.One more doubt how does Dbcc ShrinkDatabase help in performance.A kick to a right direction will be helpfull to me.Thanks in advancetvpsI also used DBCC SHRINKFILE(database_log)but their was no changein size of log file.
Hi all,I posted messages before about trying to purge many records (about 35%)of a 200Gig database. The responses gave me a lot to think about,especially regarding the indexes. But due to the short windows that Ihave to run in, manipulating the indexes is not an option.But this leads to another question. When all of this is done, we willneed to shrink the db to reclaim the space. We will also need torebuild the indexes, but this can be done one table at a time, so thatmight be ok. What I am looking for is advice on how to get through ashink of a 200G db on a fairly slow machine. Are there any 'tricks ofthe trade' that will help me get through it? I believe one of the DBAssaid that they have not been able to shrink the db in years because ittakes longer than the longest available window.Thanks In Advance
A customer installed a SQL 2005 production server and due to a configuration error chose a minimum master db size of about 1GB! The master database resides on the system partition and causes problems with a service pack installation, so it should be shrinked and moved. Until now I was unable to find a way to shrink the master db, although 99% of its space is unused (as one might expect.) Is there a way to deflate the master db without doing a reinstall?
I need to shrink database of reportserverTempdb since this db takes i.e MDF file has 6.11 GB and LDF file has 35.GB. When i take backup of this db it is getting created more than 23 GB.
Hello all, thanks in advance for any advice here. My question is, what's the effect of the Enterprise Manager > Tasks > Shrink Database function? It seems to reduce the used space on the device. Testing it on some dev machines, I've seem to have gotten back as much as 20g of space. I know it should grow back to that, but the time it took was minimal, and it didn't seem to affect my developers. They tend to add alot and delete alot during testing. What negative effects of running this should I look out for? Will it affect the DB long term? Is is preferable to schedule once a month or so? Is this done on Production DB's? Thanks for any guidence on the usage of this.
hi i have shrink log file of database use dbcc command
Message Executed as user: sa. Cannot shrink log file 2 (abc.ldf) because all logical log files are in use. [SQLSTATE 01000] (Message 9008) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded.
I need help, because i'm executing a shrink database file (file with 189 GBytes of size), and i need to stop before it finish. If I stop the shrink process exist any possible that data corruption occurred.
Is it possible to truncate Transaction Log and Shrink DATABASE while the database is being used by users or the database becomes unuvailable during this operations?
I have a database with 2 almost identical tables. Each one is about 1.2 Billion rows, 0.5 Tb each. I've truncated one of them and started SHRINK DB WITH REORGANIZE PAGES It is running already for 3 days. When should I start to worry :)
I have deployed a vb.net application to a few laptops that holds the SQL Express database as a local datastore. What is the best way to compress or shrink the database and log files? I would like a way that this can be done automatically or unattended if possible...
I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.
I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.
Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.
I would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
Help,I have a database that has a data file of 2GB and a log file of 31GB.In enterprise manager, when I choose shrink it says there is 30GB ofunused space. When I shrink the database, it does not shrink,(however it says it has completed).I've done a complete backup, tried shrink again, no dice. I thenbacked up the database (which the backup was 1.9GB), deleted thedatabase and made a new database with 2,048MB for the data and samefor the log file.When I restore, the log file jumps up to 31GB again. When I check thespace when I use the shrink, it again says I have 30GB of unusedspace.How on earth do I get this file to shrink?I've been able to shrink other databases, but not this one.TIARob
