Hi All,
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.
Can you please tell me how to reduce this size?
Thanks in advance,
Anand Rajagopal
Hi
There are some complex reports in my report server, so I want use scheduled snapshot to improve the performance. But I don't want the generation of snapshot effect other report. Is it possible I create another report server, and use the same reportservertempdb database but the reportserver database is different. Then I can configure the second server create the snapshot automatically. In other words, I want to configure 2 report server share the same snapshot or cache. Is it possible?
And another question is if I can configure the parameter value which will be used in snapshot generation.
Thanks and regards
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?
View 3 Replies View RelatedI 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?'
Thank you,
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
use ABC
sp_spaceused
database_name database_size unallocated space
------------ ------------------ ------------------
ABC 506.75 MB -0.51 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
2248 KB 1288 KB 544 KB 416 KB
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?
thanks
-D
hello
i know how to do shrinking DBCC SHRINKDATABASE( XXX,10).
But my question is how we know minumum shrinking Database value through stroed procedure.
please help me i need to shrink Database.
thanx
kiran
Hello,
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?
Any suggestions would be greatley appreciated.
Many thanks.
Hello anybody Help me?
I am new for working Database side?
MyDatabase name is 'DS'
i need to shrink my Database and files through stored procedure.
How to shirnk my Database.
can anybody help me?
thanx
kiran
Hi,
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?
Hi all,
SQL 2005 Report server TempDB log file size is 5GB.How can I clear/Delete the log file.I am using win2003 with service pack 1. Here little info about my SQL server.
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools2005.090.1399.00
Microsoft Data Access Components (MDAC)2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework2.0.50727.42
Operating System5.2.3790
What are the precautions I need to take.Sorry I am not a DBA.Please advice.Thanks in advance
I have a db server that had served both my application data and reporting services. I have since installed a second db server to be the reporting services server; the primary box still stores the application data. I am keeping reporting services installed and set up on the primary box, since I would like it to act as a warm standby for the reporting services function.
However, the ReportServerTempDB on my primary box is still quite large. The ChunkData table is quite close to 10 gig. The CleanupCycleMinutes property is set to the default of 10. Clearly any data that might be in this table is far older than that.
Is there some recommended method of clearing this data? Can I just delete the rows or do a truncate on the table? I would prefer not to uninstall/reinstall reporting services, as this machine is actively serving my application data.
Thanks.
The ReportServerTempDB.mdf file is getting bigger and I have limited space on disk; is there any way to clear or reduce the size of the ReportServerTempDB.mdf and ReportServerTempDB_log.LDF files. Thanks.
OK. Here's a good one.
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.
chris.
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?
Thanks for your info
mipo
I want to shrink database:
1. backup log ABC with truncate_only
2. dbcc shrinkdatabase(ABC, 10)
Cannot shrink log file 2 (ABC_Log) because requested size (113792KB) is larger than the start of the last logical log file.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
7 2 14432 12800 14432 12800
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
Morning,
Is there a specific command/clause in the DBCC SHRINKDATABASE command where I can force a database to release free space to the operating system ?
Cheers,
Duncan
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)
Any help apreciated...
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.
There is a table need truncate and then insert a huge data (more than million rows) every day. Does database grow up every day?
View 2 Replies View Relatedhi 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.
View 4 Replies View RelatedHi 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
View 12 Replies View Related
Hi Everyone!
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?
Thanks for any tips!
Hi SSRS Experts
We encounter the problem, that a reportservertempdb grows extremly large... the sessiondata and snapshotdata tables are about 10gb each at the moment and keep growing and growing...
The CleanupCycleMinutes Configuration is set to default=10. We are not taking snapshots of our reports.
Shouldnt those tables be cleaned up every 10minutes with this setting?
What can we do to stop this database growing? is truncating those tables on a weekly base a solution?
We are using SQL Server Reporting Services 2005 SP2 with Cumulative Hotfix Package 5 (Build 3215).
Thanks for your answers...
Kind Regards,
Roger
Hi all,
Is it possible to cleanup ReportServer DB and ReportServerTempDB by setting the time interval ? For example, I want data in these two dbs to be deleted automatically after 5 or 10 minutes. How can I do that because as new reports are added and executed data goes on increasing in very short time and hurts performance.
Thanks,
Prashant
My ReportServerTempDB is full regularly .I had to shirink the database on regular interval though i schedule for shirinking on 30 minutes.But as soon there is any exception due to scripting the log is full and my all report manaer throws error.
After shirinking the ReportServerTempDB the reports starts working fine.
Please Help me to remove this frequent error.
As we have heavy reports.and due to large space occupied by reportservertempdb.ldf we have to manually detach the ldf every time in few days..how can i programitically minimize the space generating in reportservertempdb.ldf..
Give me some tips that i sud take care of for this issue..
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.
Bob
Not a downstroke, fistpicker
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.
Hello guys,
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.
Thanks.
Best regards,
Fernando Costa
I am having DB with 4 ndf and 1 Mdf, In properties
1.MDF- Intial Size 5GB and Autogrowth 5GB
2. 5 NDF- Intial Size 20GB and Autogrowth 5GB.
So we are using the template and creating DB's.S o we have more than 30DB on same size. But Sometime actual data was very low not even 15 GB.
is it good if i shrink the DB?
Hi guys
when we do manual Database shrink at normal office hours does it make any issues
or best way to control it
1. Does it lock database and runs the shrink statement.
2. at time of shrink users allowed or not.
3. transactions can be committed.
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?
Thank you.
Can we do database shrink in volume not full to manage spaces. Only few gb spaces are available, so i want to shrink the database. How to do that.
DB size-775 Gb
Free Dis size-156 Gb out of 931 GB
How to shrink database partially?