I have a question that keeps my mind buys and I wasn't able to find answer on that.
I have a full backup recovery plan and I am backing up my Log files in every 15 minutes. At the same time My log files are shrunk in every 3 hours so Are shrinking the log files correct approach? If somebody enlighten me on this issue I appreciate it. Thanks LS :confused:
I've been having some trouble keeping my log file size under control due to some off-hours data loads. Currently, while updating some certain tables, the data base is still in "Full" mode, which of course makes the log file grow accordingly.
Today I figured out that, given the current scenario, in order to shrink the log file back down (after the table update), I have to (1) backup the log file, (2) shrink the log file, (3) backup the log file again, and (4) shrink the log file again. My question is: why do I have to do two iterations of backup / shrink? Why does it not work with one iteration? I just want to better understand the architecture and functionality.
Also, would there be a better way for keeping the log file size under control? If I switched the database to "Simple" mode to load the data, would that work any better?
In the database properties screen, there are four tabs: General, Transaction Log, Options, Permissions. In the General Tab, it lists four columns: File name, Location, Space Allocated, File Group. The string in the File name column has a value such as MY_DATABASE_DAT, whereas the Location column has a value like D:mssql7dataMY_DATABASE.MDF. The Location value is clearly the Windows file name. The "File name" (which I have seen called the "logical file") is a mystery to me. What is it used for? How can it be changed? Is there a problem if two different databases have the same "logical file" name? (For example, if you do sp_detach_db, copy the file to another place and then sp_attach_db the new file to another database name, you have two different databases with the same "logical file" name.) Much thanks to anyone who can shed light on this.
Hi everyone, I have a database (xyz) one Machine A. On the same machine I want to create a copy of the (xyz)database with different name(xyz_1). When I restore with move option it will restore but the thing is logical file names of xyz and xyz_1 are same. I know I can change these file names by manupulating the system table. My question is If I didn't change the logical file names of database xyz_1(new). Is there any problems or issues may arise.
I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:
use test DBCC SHRINKFILE(test_log, TRUNCATEONLY) --or use DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)
I get the following message:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.
I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.
I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.
I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.
I cannot find the answer to this: how do you update the logical file name for a database? Restore database "Alpha" over "Beta" specifying WITH MOVE parms allows "Alpha"'s physical files to overlay "Beta"'s, but now "Beta"'s logical file names are "Alpha_Data" and "Alpha_Log".
Updating sysfiles directly gives back a 270; "Get outta here!"
Hi Every one One thing i found out that my 2 databases in server A Has same logical file name . I have try to change the name but it is not allowing me to change. I had refer BOL it says that we should have unique logical file name in a server for each database. Question i have is does it going harm me i don't know this if any one u know please suggest me. Problem I already started getting is I do backup 5 small databases of 10 to 20 mb in one tape only. The 2 database which has same logical file name out of which one is getting copied & another is not ,All other database it backup perfecly. But at the same time in a hard disk if i take backup of this database in a same device it works perfecly so i don't understand where the problem is If any has any idea please suggest me Thanks Nirmal.
I'm moving a database (XYZtest) from the test server to the production server via sp_detach/sp_attach. I want the logical file names to be XYZ_data, rather than XYZtest_data, etc. I can easily rename the disk files, but how do I rename the logical file names?
Is there a way to rename the logical file for a database. For example, if I am moving a development database into production, I can use backup - but the backup takes the logical file names of the database and puts it into my production server. Now I have a production database with "dev_data1" for a logical file.....Can I change that name....?
Is there a way to rename the logical file names? I'm not talkingspecifically about the physical files, because those can be changedduring a restore, but the values immediately to the left of those inEnterprise Manager such as DBName_Data and DBName_log. EnterpriseManager lets me change them during a restore, but when I do it gives anerror. Any ideas?
Hi,I am planning to automate a nighty restore of a DB on another servercan someone point me in the right direction with the SQL script tomodify the logical file names to the correct path and not the onescarried over with the DB??i.e the database is to be renamed on the new serverany help much appreciatedMany thanks in advance
Is there any danger with renaming the LOGICAL file names behind the database?
There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.
On a database with a log file that has an unrestricted file growth, the file size exceeds 1 GB. Since this excessive was caused by a badly written update statement, I want to reduce the size to about 200MB. After reading the BOL I was convinced that I only need to take two actions: truncate the log file (to create some free space in the log file)and shrink it. These are the statements I executed:
backup log ODS with truncate_only dbcc shrinkfile (ODS_Log, truncateonly)
After I executed these statements - BTW, there were no errors - the file size was still the same. Can somebody tell me why?
I have Disk Xtender 2000 which was made by OTG Software , Legato and now EMC. I have an NT 4.0 PC with Microsoft SQL 2000. I have a drive space problem and need to shrink a 38 gig .ldf file called OTG03.ldf I also have a 2 gig .mdf file called OTG03.mdf How can I shrink this .ldf file. I'm not a DBA so being specific is greatly appreciated.
My DB's recover model is SIMPLE. Is it OK to schedule a SHRINK FILE only on the log files regularly? Any GOOD vs BAD about my plan? I want to do this because the log files keeps on increasing. Right now, the log file s on ENABLE AUTOGROWTH, FILE GROWTH = 10%, RESTICTED FILE GROWTH = 2,097,152.
I am geting growth alerts and need to shrink a log file that is 99% full, but it won't let me. Here is the message I get. The transaction log for database 'SOM_System' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
What can I do in order to shrink this log file?? Thanks
Hello,Database log file on MSsql2000sp3 is 27gb when database itsself 305mb.I attempted to shrink the log file with Enterprise manager,but it wantsto use a minimum of 26.xxx MB,approximatley 27gb of disk space.when running the dbcc shrinkfile (file_name) message returned is "allvirtual logs are in use'Any ideas how to reduce the log file?Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
First off, I'm normally not one to shrink the hell out of data files (for obvious reasons), but this is a special case. We are setting up a mini test environment in preparation for migrating one of our systems to SQL Server 2005 (among other things).
The problem I have is that the test SQL Server I have to use has only about 50GB of disk space that I can allocate to databases, when the databases that I need to fit in there sit at around 130GB total. I've got 13 total databases that I have to fit in there. There are several logging tables in each, and lots of binary data that's really not needed for our testing. The first thing I did on my testing copies was to change the recovery model to simple, which chopped the log file.
So I've been truncating the logging tables and have been stripping out the unneeded data. I then have been running a script that I wrote to reindex, then shrink the individual files, then the database as a whole. Probably overkill, but it has worked on all of the DBs with the exception of one.
This particular DB is approx 21GB in total size (it's already come down from about 55GB), but when you look at the free space, it's showing 75% free inside the mdf file. I don't really care much about performance at this point, I just need to get the file size down and can't figure out how.
set @dbname = ltrim(rtrim(db_name())) set @logfilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.ldf%'))) set @datafilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.mdf%')))
/* Reindex Tables */ declare tablecursor cursor for select '[' + table_schema + '].[' + table_name + ']' from information_schema.tables where table_type = 'base table'
open tablecursor fetch next from tablecursor into @tablename while @@fetch_status = 0 begin print 'Reindexing ' + @tablename dbcc dbreindex(@tablename,' ',90) fetch next from tablecursor into @tablename end close tablecursor deallocate tablecursor
/*Shrink the crap out of the DB*/ set @sql = 'BACKUP LOG [' + @dbname + '] WITH TRUNCATE_ONLY' print @sql exec sp_executesql @sql