Hi
I want to know what is command that i add to the Query Analyzer until
i tell to the database that it Shrink my DB every 1 day
I know it can be done from all Tasks > Shrink Database ..
But i want to know the Code commands for that for using in Query Analyzer
Thanks
Hi
I want to know what is command that i add to the Query Analyzer until
i tell to the database that it Shrink my DB every 1 day
I know it can be done from all Tasks > Shrink Database ..
But i want to know the Code commands for that for using in Query Analyzer
Thanks
I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).
View 4 Replies View Related
Hello to all members.
I am using SQL Server 2005 std edition 32 bit
The database is configured in compatibility mode to sql server 2000
The backup mode for the database is configured as simple
With SQL Server Agent I am running schedule task of the backup script
The question is:
I would like to save only the last 7 days in repository.
This means that when the 8th days has become then the 1st day backup is deleted.
in the 9th day the 2nd day backup file is deleted.
and so on
I am using the the T-SQL BACKUP DATABASE command with the following arguments:
RETAINDAYS = 7
NOFORMAT
NOINIT
SKIP
I am in situation that the command above only append to the backup file and without deleting the expired backup days.
What I need to change in the parameters that the script will delete the expired backup days.
Thanking in advance everyone who assist.
Hello to all members.
I am using SQL Server 2005 std edition 32 bit
The database is configured in compatibility mode to sql server 2000
The backup mode for the database is configured as simple
With SQL Server Agent I am running schedule task of the backup script
The question is:
I would like to save only the last 7 days in repository.
This means that when the 8th days has become then the 1st day backup is deleted.
in the 9th day the 2nd day backup file is deleted.
and so on
I am using the the T-SQL BACKUP DATABASE command with the following arguments:
RETAINDAYS = 7
NOFORMAT
NOINIT
SKIP
I am in situation that the command above only append to the backup file and without deleting the expired backup days.
What I need to change in the parameters that the script will delete the expired backup days.
It is too much work when working with Database Maintenance Wizard.
lets take a scenario that I have 10 database.
I would like that each database will backup into seperate file.
I need to create 10 * 7 Maintenance plans.
I have created a directory with the name SDBbackup.
Now under the directory SDBbackup I have created 7 directories (1,2,3,4,5,6,7).
Each directory is for Sunday=1 , Monday=2 , and etc.
It's very much work if I need to accomplish it with Database Maintenance Wizard.
This is why I am asking a specific question regarding T-SQL.
Thanking in advance everyone who assist.
himy self avicurrently i am developing one application in vb 6 and back end assqlserver 7 which is used on lan.i have some problem. like in my database i have one table salesvoucherwhich has 'voucherno' field. when 2-3 user will work on salesform at atime [since the softwrae will run on lan] then the same voucherno willsave for all users data which is wrong i need to save different vouchernofor each no. is there any way in sqlserver to apply some condition ondatabase or to set some its property[tables] so that at a time only oneusers data will save depend on firstcome first serve base or is there anyway to make changes in programme.plz help me regarding this i need it very badly.since its my first lan based software plz give me some books nameregarding this software for vb6 and sqlserver7
View 2 Replies View RelatedI am currently looking to buy or build software that enables my firm to:
Run a set of scripts to create a base database - empty database with minimum amounts of data (i.e. lookup data and seed data). So that when a new development project begins all that is needed to build the database is to run these scripts.
The challenges we face is managing these scripts. It would be easier from a manual perspective to only have 5-6 scripts, so that we do not need to manually open and execute each script (representing an object). The problem with this is when you modify a stored procedure, you modify it in your version control platform (which is single object based) and then you need to find and replace the object in your script.
What we are looking to do is buy or build an application to manage these files for us. I was wondering if anyone has purchased a solution to accomodate this?
We currently have ApexSQL Diff - which works well to compare contents of databases (structurally it does well, seems to have a problem comparing data but that is another discussion). What it does not do well is script out an entire database, and if it were to do this, it would put it all in one file.
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?
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 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
I am creating a product details page that is passing the string from my products database table. I am just wondering what is the best way to filter out a column depending on the information that is in the Product_Category Column For example my database products table has the following...ProductIDProduct_NameProduct_NumberProduct_CategoryProduct_InstructionsProduct_Costetc...I want to make it so if the Product_Category = BooksThen TABLEROW with the ID of "ProductInstructions" will have a visibility of "False"
View 3 Replies View RelatedHello 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?