Hi,
I have some doubts about Shrinking databases or files.
while shrinking a file, I learned that we are alllowed to shrink more than the minimum size of the file, does not it bring damage to the data in that file ??
I have a database that is 9 Gig in size and I am no longer needing the information that was stored in this database.
So I deleted the User tables that I created and deleted the User stored procedures.
I performed the Shrinking database feature and it only shrunk my database to 2 Gig.
How come it did not shrink the database to at least 10 MB since I no longer have data stored in the database?
Ideally it seems to me that it would be best to only shrink databases when they
are not in use.
However, it seems this may not be practical, therefore can a Database
be shrunk while it is actively being used?
Thanks !!!
We are currently running sharepoint with SQL 2012 database. The consultant who configured Sharepoint for us is advising/insisting that we setup a daily maintenance job to shrink the data and log files. He is insisting that the lack of maintenance job to shrink the files daily is the reason we are running out of disk space.
View 4 Replies View RelatedHi again
Damn, people, how to shrink log file. I've tried several times with
DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY)
and
DBCC SHRINKFILE (log_name, final_MB, TRUNCATEONLY)
and the log still is _untouched_
I have got big table, about 190 mln records (15 GB) and after simple update the log is very big but unnessesery.
Michal Pajkowski
What is the best way to Shrink a Data file and Log Files.
View 1 Replies View RelatedHello,
we are running on sql server 2005 and in some of our production databases the log files are twice as bigger than the data files. we are planning to reduce the log file size. I saw the option on the management studio to shrink the log file. I just have some questions on this.
as long as we are doing on the production server can we do that while the database is online or do we need to take the database offline? any help would be appreciated. Thank you!
.
Hello everybody.
I have SQL2000 sp3 standard
with 50 db's
All db set for full recovery
and autoshrink
Backup done with Tivoli
full backup once a week
log backup done every 12 hrs
Problem .. shrinking logs
every 20 min I run job
DBCC SHRINKFILE (My_db_logFile) for every db
70% of the time I am getting
message similar to
-------------
Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
--------------
1. I checked with sp_who2
The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."
Why i getting "Cannot shrink ..." ?
even if job runs right after backup of the log files ,I still have messages.
Thank you
Alex
I have a database file LEAR_Index(yes, it hold index data) from a havehave recently removed a bunch of data.It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink thefile to lean 30-40Gb.I´ve been trying this:dbcc shrinkfile('LEAR_Index',40000)But to no apparent avail; the file did not shrink.I´ve tried using enterprise manager for this but it consistenlycrashes when performing this operation.#Any thoughs, idear as to what i might be doing wrong?
View 6 Replies View RelatedHi,
We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.
thanks
I've been tasked with taking over the support for a client's SQL Serverdatabase. I'm not a DBA by profession, I'm a software developer whouses SQL Server as a database designer.The clients have reported that the server is running out of disk spaceand examination shows that the log files for several of the databasesare at 5Gb or more.After reading around the subject I suggested the following sequence ofoperations:-- Select the name of the database you want to shrinkUSE MyDB-- Dump unwanted transactionsdump tran MyDB with truncate_only-- Get the name of the logfileSELECT * from sysfiles-- Having examined the rows returned by this use the log file....-- Shrink the file to required size (in MB)DBCC SHRINKFILE('MyDB_log', 10)Is this a reasonable approach? Please bear in mind that I'm pretty newto this, and I have many other tasks to do besides manage the server.A previous DBA has set up good maintenance plans etc. so everything isbeing properly backed up (well, I think it is)If this IS a good approach, would it be reasonable to do this on, say,a monthly basis as a scheduled job? Obviously the stepSELECT * from sysfileswhich gives us the physical name of the log file would be removed andthe job would operate explicitly on each log file for each database inturn.Many thanks for reading.William Balmer.
View 2 Replies View RelatedI have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?
View 7 Replies View RelatedI have a no of databases in full recovery model whose files are many times their datafiles. It is because these databases were copied from the development servers and in the development servers they were not taking the transactional log backups although once in the production server it is ensured that a transactional log is taken once in a day atleast. I plan to shrink the logfiles using the dbcc commands. However I am afraid that it may lead to severe defragmentation and performance hits.
We are using Sql Server 2008R2 enterprise edition which is clustered.
In this context my questions are:-
1)What is the best course to do the shrinking of log with out defragmentation?
2)Can I do the shrinking when the database is in use or is online in production?
3)Will the shrinking of the logfile improve the performance in any manner like that of the i/o operations or paging?
4)Can I do the shrinking of the log files alone without the shrinking of the corresponding data files?
I am using Sql Server 2012 in always on configuration with multi subnet failover clustering. Size of data file has suddenly increased, i dropped all the unnecessary table from database three days back. day before yesterday i tried shrinking data file using DBCCÂ command but it is taking too much time. is there any other option for deallocating the space.
View 7 Replies View RelatedI have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file.Â
Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?
Hi All,
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
Thanks in Advance
Gaurav Matkar
I like to move my database from a directory to another (on different drives foir exemple).
Is there an easy way to do this kind of job ?
Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:
1- Drop the table - OK
2- Drop the filegroup - OK
3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.
When I query a catalog view with this query:
Select * From Sys.Database_Files
I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.
This is the code I use:
Use [TESTING ]
Go
-- Add a FileGroup to the Database
Alter Database TESTING Add FileGroup FG01
Go
-- Add a file to a FileGroup
Alter Database TESTING Add File (
NAME = TESTING_DATA01,
FILENAME = 'D:Sql ServerDataTesting_Data01.ndf',
SIZE = 1 MB,
MAXSIZE = 10 MB,
FILEGROWTH = 1 MB
) To FileGroup FG01
Go
-- Create the table using a specific FileGroup
Create Table TABLE1 (
Id Int Not Null,
FirstName VarChar(30) Not Null,
LastName VarChar(30) Not Null,
BirthDate SmallDateTime
)
On FG01
So far everything works ok, but in the next code there is an error:
-- Delete the table
Drop Table TABLE1
Go
-- Remove a FileGroup from the Database
Alter Database TESTING Remove FileGroup FG01
Go
-- Try to remove the file
Alter Database TESTING Remove File TESTING_DATA01
When I try to remove the file there is an error:
"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"
Is there some sort of command that can tell me which mdf and ldf files are associated to which databases?
Thanks,
Ninel
I recently was given control of an SQL server that stop working.
We had no past backups of the database and the only file we had was the .mdf
I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the
C:program filesmicrosoft sql servermssqldata
when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??
I even tried
sp_attach_single_file_db @dbname = 'SWATraining',
@physname = 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf'
but reason this error
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
Hi,
I'm trying to list all database locations with SQL command, but getting lost here :(
So, anyone any idea how to get databasefile locations from server?
All I need is like: 'C:Program FilesMicrosoft SQL ServerMSSQLDatamaster.mdf' and others.
Thanks.
We have two db's. One live and one test.When I right click on the live one in SQL Enterprise Manager andselect properties -> Data Files ->File Name is LIVE.MDFLocation is F:DataLIVE.MDFWhen I right click on the test one in SQL Enterprise Manager andselect properties -> Data Files ->File Name is LIVE.MDFLocation is F:DataTEST.MDFSame thing applies to Transaction log files too.My concern is File Name is same in both the above cases even thoughthe location is different. What are the consequences of this.Thanks for your helpGVV
View 1 Replies View Related
Hello,
I need to move all log files for my SQL 2005 databases to another drive. I don't wish to shrink the files, I need to move the logs to another drive spindle. I did find an article (Article ID: 224071) that describes moving both the database and logs using sp_detach and then sp_attach. What is the best way just to move the logs to another drive on the same server, and that keeps the databases in their original location?
Thanks.
Hello there,
I've been told that it is good practice to keep mdf and ldf files in another location... We have it in place for all our user databases, however mdf and ldf files for our system dbs are still at the same location. I was wondering what is the right way of splitting those should be?
I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
Hi,
Hopefully someone can help me with this.
When I downloaded the files and after burning the DVD to create the DVD to setup the 7031A - Upgrading to Microsoft SQL 2005 course, all of the executable files (BCBase.exe, Upgrade.exe, InPlace.exe, SideBySide.exe and SQL7Upgrade.exe) are not in the Setup/Drives folder...the only item in that folder is a file called "placeholder.txt".
Please, if anyone knows how to get these exectuables let me know.
Thanks,
David - EduCorp Training & Consulting
We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?
We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).
Hi, I am trying to open or create a sqlexpress database within Visual Studio 2005 Professional in the App_Data folder. If I attempt either method, I get the following dialog box:Required Components MissingConnections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkID=49251 The link above just takes you to the download page for Sql Server Express 2005. I have both SQL Server 2005 Developer and SQL Server 2005 Expression instances running. The existing database will work properly in my web application, however I cannot access it through Visual Studio.As background, I did have a problem connecting to the database via the web application, receiving a "Failed to generate a User Instance of SQL Server" error. But I was able to fix that by renaming my SQLEXPRESS folder in C:Documents and SettingsuserLocal SettingsApplication DataMicrosoftMicrosoft SQL Server Data. When the web page ran, it created another SQLEXPRESS folder.However, I cannot figure out why I am having the other issue.Thanks in advancePatrick
View 1 Replies View RelatedI am trying to restore multiple .bak backup SQL database files onto a new server. However, I have found that it will not allow me to restore multiple databases at once. Is there a way to do this so that I do not have to manually upload one at a time? I tried adding all the .bak files at once to the backup device window but it only did the first one listed. It would be so much easier to restore them all at once so that I do not have to continue this manual process. I am restoring them via device.
View 13 Replies View Related1) Does shrinking a db have any side affects ? Or this is pretty much a normal
operation ?
2) Also in db options, is it recommended to have auto shrink checked ?
Thanks for your help.
I am new to SQL server.
We have many databases for which log files have grown much bigger. The one I need to Shrink immediately has 16 MB .mdf file and 12 GB of .ldf file.
I will very much appreciate, if somebody can help me with step by step process to shrink the database/log file (some way). We are in a crunch situation
Thanks
Hi all,I've deleted a lot of albums but the size of personal.mdf isn't shrinking - how do i go about acheiving this? I've tried to shrink it in sql management express but the file is read only..
please help!
thanks
I have a webhost where it seems my control of my database is fairly restricted. I cannot backup the database because I don't have the necessary permissions. I cannot perform a DBCC SHRINKFILE (permissions) - and many other DBCC commands. I ran into a problem where my log file filled up during the middle of the day and impacted my operations - data was lost.
I found some TSQL for shrinking the log file, but the statement:
SET @TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY'; EXEC (@TruncLog)
will not execute because I cannot execute the BACKUP LOG command (permissions)...
Is there anything else I can do, or am I to far up the creek (w/o my paddle)??
Thanx
Jerry