I have a database I need to copy from a Prod server to a Dev server. There is not enough space on the Dev server. In looking at the size of the files on the Prod server, the Initial Size property for the transaction log on the Prod server is set to 100,000 MB though the log is using nowhere near that.
This is a mirrored database so the recovery model is "full". I know that to change the initial log size, I have to put the database in 'simple" recovery model. Is this possible? Can I just:
1. Pause the mirror 2. Switch recovery model to simple 3. Change the initial size property to something smaller. 4. Shrink the transaction log 5. Change the recovery model back to full and resume the mirror?
I honestly don't know if the transaction log is needed on the Dev server. Meaning I may just be able to restore the transaction log to a different location on the server and delete it so that new one is created.
I have a 13 Gig Log File with only 121 Mgs of space used. I have run the DBCCSHRINKFILE command and it has shrunk it by about 100 Mgs. Why can't I get it to shrink to a reasonable size.
Hi, is there any other way to shrink the log file other than truncating every time it gets bigger... I have a 800MGs database and the log is getting as big as 11Gigs... any suggestions other than truncating it? Is there a way to set it off?
DECLARE @i int SELECT @i = 0 WHILE (@i < 300) begin INSERT t2 values ('a') SELECT @i = @i +1 END DROP TABLE t2
BACKUP LOG PBDSSTEST TO PBDSSTESTLOGBACKUP
Before I ran this the used portion of the log was 1795.12 MB, the free was 1570.50 MB for a total of 3365.62 MB.
After completing this the used portion was 25.62 MB, the free space was 3263.50 for a total of 3289.12 MB. It only srhunk about 26 MB. Why won't it shrink it anymore. There was plenty of data in the used at the time of the shrinkfile command so I would have thought it would have shrunk it a lot more. Please help. I don't understand why it won't shrink it anymore than it did.
I have a problem with shrinking a log file on SQL 2000. The log file has grown to 25gb (the db is 30gb) . The DB was in simple recover mode but I have changed it to full so that the log file can be backed up. I have run the dbcc shrinkfile on the log file several times (plus log file backups) and it has increased the logfile size. Each time I have run shrinkfile I get €˜Cannot shrink log file 2 (db_Log) because all logical log files are in use€™ message. I have used the dbcc loginfo command and this shows that all the virtual log files are in use. The db also publishes 4 snapshot replications. Any suggestion for how I can easily shrink the log file?
I have a database that was loaded with large amounts of data before today. I backup up the db and trans log. presently the DB file is 328 while the trans log show 428 meg with only 28 meg being used. If I try to shrink either the database or the log files, the log file wil not shrink.. It tells me to set it to a minimum of 28 meg but still not shirnk. I realize this was a problem in 7, any quick fixes, in a hurry. Running SQL 2000 SP1
I'd like to create a stored procedure to automatically shrink the transaction log file on a certain database monthly. As I am not the DB admin (it's a web-based db on a remote server), I'd like to put together a script to create the procedure so I can just send it to the DB admin to run. It seems like it should be pretty easy, but I'm still pretty new to SQL server. Is anyone able to give me some pointers?
I have a relatively high usage 2005 database whose transaction log grows every day.
Untill now I have been manually shrinking the log files .. but I would like to automate this process.
In the maintenance schedule that runs every night
1 Do a full backup to disk 2. Do a transaction log backup to disk 3. run the following TSQL
USE [MyDB]; GO ALTER DATABASE MyDB SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (MyDB_log, 3000) GO ALTER DATABASE MyDB SET RECOVERY FULL; GO
4. Backup disk files to tape.
however it doesn't work !! the TLOG file does not shrink, even though its the same as the manual job that I run?
There is nothing in the SQL logs .. but in the event log I get
Event Type: Error Event Source: SQLISPackage Event Category: None Event ID: 12291 Date: 13/12/2007 Time: 00:50:10 User: NT AUTHORITYSYSTEM Computer: ALGSQL1 Description: Package "MyDB_FULL_BACKUP" failed. For more information, see Help and Support Center
please advice me what does .ldf file consist of and can i shrink the .ldf , is it adviceble to shrink after the backup and how frequently it can be done on a production db
please advice me ,can i shrink the .mdf , it is adviceble to shrink after the backup and how frequently it can be done on a production db
(SQL 2005) I have a few databases that are currently hovering at around 150GB. (The following only concerns the .mdf) Of this total size, only about 90GB is real data. The other 60-70 is data from unecessary audit tables. Recently, we've been having some space issues on a 1.7TB server. We've decided that deleting these audit tables and reclaiming the 60-70GB per database is the route we're going to take.
What we did: DELETE <table> (per audit table). Now, it shows that the database size is ~90GB and there is the 60-70GB of unallocated space.
Then, we ran a DBCC SHRINKDATABASE (db_name) overnight. Checked space the next morning and only about 10GB had been reclaimed. We've tried a number of other alternatives of which, none have been successful.
(DBCC SHRINKFILE(file, space), drop and recreate tables, etc.)
Can anyone think of any reason why these commands would not be working? If I can provide any additional information, please let me know.
I have an interesting problem here. I am running SQL Server 2005 and have a large database. After running some scalability tests over the weekend my MDF file size went up to 25GB and LDF file size went up to 27GB filling up all available disk space. I didn't do any backups, so it is understandable why the LDF file size is so large.
Now I am trying to shrink the LDF file. However, DBCC LOGINFO indicated that all LVFs within the LDF are active (Status = 2). I detached the database to make sure no active connections exist and ran CHECKPOINT, but still DBCC LOGINFO lists all LVFs with status 2. Since all LVFs are up to date I can't shrink my LDF file.
Why are all LVFs still active after detach/attach and checkpoint? How can I shrink the LDF file?
Is there a fairly quick and dirty way to shrink the file size allowedfor a DB on SQL server after the DB is in use?Our old DBA allowed 3 gb for log files and we don't need nearly thatmuch. However, the space is still being used and reserved. Can Ishrink that "maximum" and clear up the space?I don't want to just copy all the tables and primary keys and open anew one with the new data, though that is plan A right now.I cant just change sysfiles, it yells at me.
I've been searching and reading for best practices on how to maintain a reasonable log file size while engaging in Log Shipping. I have found plenty of information on these subjects on their own, but not on how best to shrink your log size without breaking your log shipping integrity.
On a SQL Server 7.0 database I support, I've been unsuccessful trying to shrink a data file using dbcc shrinkfile (datafile_logical_name, 0). This worked fine for shrinking the log, but of the 4 datafiles that were created 2 shrank successfully and 2 remain unchanged. Unless the information on the General Tab on Enterprise Manager is incorrect, of the 15000MB allocated for one of the files, only 700 MB are used.
I have inherited a SQL 2000 database ( (I am new to SQL DBA) and I found this when I was checking the db properites . The transaction log has grown bigger than the actual data file, I thought transaction log backups would truncate the inactive portion of the log file and shrink the transaction log, but it was not the case it seems, may be it was truncating the inactive portion of the log, but not shrinking it. This site does not have a job for truncating the data/log files periodically. What is the best method to deal this situation, how can I shrink the Transaction log quickly?,
My intention is to include a sql job to schedule for shrinking log files to half of its original log file size (if half of their original size is greater than 5MB)
The code is below. But while executing the code it is keeps on running indefinitely. can somebody throw some light what cause it to go into an indefinite loop.
Thanks Babu
code: ==== DECLARE db_file_name CURSOR FOR SELECT name , ceiling(size/128.0) * 0.5 FROM master..sysaltfiles Where rtrim(lower(filename)) like '%.ldf'
DECLARE @f_name varchar(128), @new_f_size int
OPEN db_file_name FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size WHILE @@FETCH_STATUS = 0 BEGIN If ( @new_f_size > 5) BEGIN DBCC SHRINKFILE ( @f_name, @new_f_size ) FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size END END CLOSE db_file_name deallocate db_file_name
We are using Sql server 2012. One of our production database has hight no of vlfs. We are planning to shrink the logfile to reduce the no of VLFs but the database is configured for logshipping.
What is the effect of logshipping when you shrink the logfile?
I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.
I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.
Hi! I have a database where I add some new Filegroups and moved some Gb's of tables and indexes to new file groups. Now I would like to shrink the source of these tables/indexes: the mdf file. This means there should be much empty space now in the mdf file.
I tried to use dbcc shrinkfile, but it run for more than one day. Are there some good strategies about shrinking the mdf file? I had to restart the instanse because got problem using the db while dbcc runned...
Hi all:I restored one backup database (7.9 GB mdf) on two diffrent servers. Ishrunk them by clicking "Move pages to beginning of file beforeshrinking".After shrinking, one mdf file is 6.7 GB, and the other is 4.2GB. Ishunk again and again:1. the 6.7GB become 5.9GB, 5.2GB, 4.7GB and 4.2Gb (four times)2. the 4.2Gb become 4.0GB (just one more time)It is wired, I am wondering the mdf will be smaller and smaller if Icontinue to shrink them? What is the reason?ThanksWJ
Hello:I have 2 MS SQL Servers 2000 Developer edition.Both have same settings and databases. I'd like to have them as mirroredservers - all transaction on one server must be performed on the otherautomatically. More than that I'd like to get possible if something happenedto one server, all transactions will be performed on another one.Please, give me a hint how to do that.Thanks,GB
I use different udls for a website which is partly asp pages and partly aspx pages. Every database is mirrored, so each udl indicates a "failover partner". However we decided recently that one of the database would not be mirrored anymore. For this database we still use in the udl a "failover partner" to redirect the connection to the second database in case of a failover. For the asp pages the failover works fine even if the database is not mirrored, but it doesn't work for the aspx pages.
It would be great if anyone had an idea why the behaviour is not the same for the aspx pages, and if we can still use a "failover partner" in udls even if the database is not mirrored.. if not, what should we use to connect to another database?
SQL Configuration: Two 2003 Servers (OS Std ver) with SQL 2005 (Std ver) configured for db mirroring. (The servers aren't clustered.) Web.config configuration string is using "ServerA". If we fail Server A, then Server B will change roles to "primary" in about 20 seconds (we have confirmed this via SQL Mgt Console). BUT...our web app is still pointing to Server A and doesn't seem to know there is a fail over. SO - how can I make the web app aware of the failure? Server A and Server B aren't in a cluster - and I understand a cluster's virtual server would be referenced in the connection string. Is there a way to make the web app automatically switch to Server B (without a cluster configuration)? From what I understand, the cluster configuration would require Ent Ed. of OS and SQL and a big fat check!! Thanks. -Kevin
hi.I've seen ways to delete duplicate rows.Can someone give me some sql to do this?I have a table with varchar table_name_start, varchar column_name,varchar table_name_end;it has rows like this:table1 col1 table2table1 col2 table 3table2 col1 table1I'd lke to delete the rows if they exist with the names swappedaround, i.e. like above since the first and third share a column nameand the table_name_start/end matches the others table_name_end/start,I'd like to delete one and leave the other.I'm scratching my head trying to figure this out.thanks
